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

以下のように、日付と売上金額のリストを含むデータ範囲があり、指定された年ごとの合計金額を求めたいとします。Excel では、通常の SUMIFS 関数や SUMPRODUCT 関数を使えば、このタスクを素早く簡単にこなせます。このチュートリアルでは、これらの2 つの関数の使い方を詳しく解説します。

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

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

指定された年ごとに合計値を求めるには、まず DATE 関数でその年の開始日と終了日を設定し、次に SUMIFS 関数を使ってその期間内のすべての値を合計します。一般的な構文は次のとおりです。

• sum_range：合計する値の範囲です。
• date_range：日付の検証対象範囲を、特定の年を基準として設定します。
• year：値を集計する基準となる特定の年です。（)年,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：これは「sum_range」で、合計したい値が含まれる範囲です。
• $B$2:$B$12,「>=」&DATE(E2,1,1)：範囲 B2:B12 内の日付が、セル E2 で指定された年の 1 月 1 日（例：2020/01/01）以降かどうかを判定します。
• $B$2:$B$12,「<=」&DATE(E2,12,31)：範囲 B2:B12 内の日付が、セル E2 で指定された年の年末（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 関数を使ったもう一つのシンプルな数式も役立ちます。一般的な構文は次のとおりです。

• date_range：特定の年に基づいて検証する日付の範囲です。
• year：合計の基準となる特定の年です。
• sum_range：合計する値の範囲です。

以下の数式を空白セルにコピーまたは入力し、他のセルにも結果を表示させるには、その数式を下方向にドラッグしてください。

この数式の説明：

=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 を返します。

使用される関連関数：

関連記事：