Excel では、行が別のテーブルのデータと一致し、特定の日付範囲内にある場合、列から値をフィルター処理、取得、および合計するにはどうすればよいですか?

Question

さて、コンテキストとしては、このリンクから始めましたが、これを自分のユースケースに適用しようとして行き詰まっています。 Excel - 別のテーブルの別の列と一致する列に基づいて値を合計する。

私は、作業を入力する時間ごとのログを持っています。そこには、請求対象となる日付、プロジェクト、タスク情報、作業時間の開始時間と終了時間などが含まれており、入力した時間から何時間働いたかが計算されます (15 分単位に切り上げ)。

サンプル データを使用したログ ("timeLog") は、おおよそ次のようになります。

	あ	B	Ｃ	だ	え
1	日付	プロジェクトとタスク	始まる時間	終了時間	時間
2	2023年1月1日	プロジェクト 1:タスク 1	午前8:00	午前10時	2
3	2023年1月1日	プロジェクト2:タスク1	午前10時	16:00	6
4	2023年1月6日	休日営業時間	午前8:00	16:00	8
5	2023年1月7日	病気の時間	午前8:00	午後12時	4
6	2023年2月6日	プロジェクト 1:タスク 1	午前8:00	16:00	8

   ^{[転記元この画像。}

別のシートには、すべてのプロジェクトと関連タスク (「プロジェクトとタスク ID」列) をリストした表があります。各プロジェクトには、タイプ (つまり、労働時間、休日、病欠、休暇) が関連付けられています。

プロジェクト情報テーブル (「projectTable」) は次のようになります。

	あ	B
1	プロジェクトとタスクID	タイプID
2	プロジェクト 1:タスク 1	働いた
3	プロジェクト2:タスク1	働いた
4	休日営業時間	休日
5	病気の時間	病気

      ^{[転記元この画像。}

病気休暇や休暇の累積額を計算するには、特定の請求期間に働いた時間数を取得する必要があります。ただし、病気休暇や休暇、休日を含む提出した総時間を時間単位のログに残しておく必要もあります。これは、それらを全体の FTE 計算に使用するためです。

現在、請求期間 (12/26/22-1/25/23) 内の「作業済み」タイプ ID を持つプロジェクトに関連付けられているすべての時間を合計するために使用しているのは次のとおりです。

=SUM(IFERROR(IF(COUNTIFS(projectTable[Project & Task ID],FILTER(timeLog[Project and Task],(timeLog[Date]<=DATE(2023,1,25))*(timeLog[Date]>DATE(2022,12,25)),0),projectTable[Type ID],"Worked")>0,timeLog[Hours],0),0))

まず、FILTER を使用して、指定された期間のすべてのテーブル エントリを取得します。

FILTER(timeLog[Project and Task],(timeLog[Date]<=DATE(2023,1,25))*(timeLog[Date]>DATE(2022,12,25)),0)

次に、COUNTIFS は、プロジェクトが「Worked」TypeID であるかどうかを確認します。

COUNTIFS(projectTable[Project & Task ID],FILTER(timeLog[Project and Task],(timeLog[Date]<=DATE(2023,1,25))*(timeLog[Date]>DATE(2022,12,25)),0),projectTable[Type ID],"Worked")

次に、IF は各行の時間値を返し、IFERROR は発生し続けたエラーを「修正」し、SUM は期間の合計「労働」時間を計算します。

しかし、これは最善の方法ではないようです。脆弱で、予期しないエラーが返され、全体的に間違ったやり方をしているように感じます。これを行うより良い方法は何でしょうか?

Answer 1

私はあなたの解決策を研究していませんが、一見すると、不必要に複雑であるように思われることに同意します。時間ログに6番目の列を追加して、次の内容を含めることをお勧めします。

=VLOOKUP(B2, projectTable!$A$2:$B$5, 2, FALSE)

これにより、次のように、時間別ログの「プロジェクトとタスク」がプロジェクト情報テーブルの「タイプ ID」に変換されます。

	あ	B	Ｃ	だ	え	ふ
1	日付	プロジェクトとタスク	始まる時間	終了時間	時間	タイプID
2	2023年1月1日	プロジェクト 1:タスク 1	午前8:00	午前10時	2	働いた
3	2023年1月1日	プロジェクト2:タスク1	午前10時	16:00	6	働いた
4	2023年1月6日	休日営業時間	午前8:00	16:00	8	休日
5	2023年1月7日	病気の時間	午前8:00	午後12時	4	病気
6	2023年2月6日	プロジェクト 1:タスク 1	午前8:00	16:00	8	働いた

COUNTIFSと を使うだけで済みますSUMIFS。例えば、他の場所（例えば、A8:B10）では、A8:A10の型を入力し、

=SUMIFS($E$2:$E$6, $A$2:$A$6,">"&DATE(2022,12,25), $A$2:$A$6,"<"&DATE(2023,1,23), $F$2:$F$6,A8)

B8で、下にドラッグ/塗りつぶします。

	あ	B
8	働いた	8
9	休日	8
10	病気	4

もちろん、ハードコードされた日付はセル参照に置き換えることができます。

---

私が答えたのは、似たような、しかしより複雑な質問でした。質問 8年前。