年ごとにExcelで値を合計する

日付と売上金額のリストを含むデータ範囲があると仮定し、以下のように特定の年に基づいて合計金額を求めたいとします。Excelでは、通常のSUMIFS関数とSUMPRODUCT関数を使用してこのタスクを迅速かつ簡単に解決できます。このチュートリアルでは、これら2つの関数の使用方法を詳しく説明します。

• SUMIFSとDATE関数を使用してExcelで年ごとに値を合計する
• SUMPRODUCT関数を使用してExcelで年ごとに値を合計する

SUMIFSとDATE関数を使用してExcelで年ごとに値を合計する

指定された年に基づいて合計値を得るには、DATE関数を使用してその年の最初と最後の日付を指定し、その後SUMIFS関数を使用して指定された日付範囲内のすべての値を合計します。一般的な構文は次の通りです：

• 合計範囲: 合計される値の範囲；
• 日付範囲: 特定の年に対してテストされる日付の範囲；
• 年: 値を合計したい特定の年。（年, 1,1 はその年の最初の日を示し、 年,12,31 はその年の最後の日を示します。）

このタスクをExcelで解決するには、以下の数式を空白のセルにコピーまたは入力し、他のセルに結果を得るために数式を下にドラッグしてください：

この数式の説明：

=SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31))

• $C$2:$C$12: 合計したい値が含まれている「合計範囲」です。
• $B$2:$B$12,">="&DATE(E2,1,1): 範囲 B2:B12 の日付が、E2 セルにある年の最初の日（2020年1月1日）以上であるかどうかを確認します。
• $B$2:$B$12,"<="&DATE(E2,12,31): 範囲 B2:B12 の日付が、E2 セルにある年の最後の日（2020年12月31日）以下であるかどうかを確認します。
• SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31)): 条件を満たした後、範囲 C2:C12 内の値が見つけられ、合計されます。

SUMPRODUCT関数を使用してExcelで年ごとに値を合計する

ここでは、SUMPRODUCT関数によって作成された別のシンプルな数式も役立ちます。一般的な構文は次の通りです：

• 日付範囲: 特定の年に対してテストされる日付の範囲；
• 年: 値を合計したい特定の年；
• 合計範囲: 合計される値の範囲。

以下の数式を空白のセルにコピーまたは入力し、他のセルに結果を得るために数式を下にドラッグしてください：

この数式の説明：

=SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)

• YEAR($B$2:$B$12)=E2: YEAR関数は、範囲 B2:B12 の各日付セルから年番号を返します。この式は、E2 の年が範囲 B2:B12 の年と等しいかどうかを確認します。そして、次のような配列が得られます：{TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}。
• (YEAR($B$2:$B$12)=E2)*$C$2:$C$12={TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}*$C$2:$C$12: この式では、TRUEとFALSEはそれぞれ1と0に変換され、この配列は範囲 C2:C12 の各金額に掛けられ、次のような新しい配列が得られます：{347;0;398;430;0;379;412;0;0;420;0} 。
• SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)= SUMPRODUCT({347;0;398;430;0;379;412;0;0;420;0}): 最後に、SUMPRODUCT関数はこの配列内のすべての数字を加算し、結果として2386を返します。

関連する関数：

関連記事：