Excelでの年ごとの合計値

日付と販売額のリストを含むデータの範囲があるとします。次に、以下のスクリーンショットに示すように、特定の年に基づいて合計金額を取得します。 Excelでは、通常のSUMIFS関数とSUMPRODUCT関数を使用すると、このタスクをすばやく簡単に解決できます。このチュートリアルでは、これらXNUMXつの関数の使用方法を詳しく説明します。

SUMIFSおよびDATE関数を使用したExcelでの年ごとの値の合計
SUMPRODUCT関数を使用したExcelでの年ごとの合計値

SUMIFSおよびDATE関数を使用したExcelでの年ごとの値の合計

特定の年に基づいて合計値を合計するには、DATE関数を使用してその年の最初と最後の日付を指定してから、SUMIFS関数を使用して、指定した日付内のすべての値を合計する必要があります。一般的な構文は次のとおりです。

=SUMIFS(sum_range,date_range,">="&DATE(year,1,1),date_range,"<="&DATE(year,12,31)

sum_range：合計される値の範囲。
date_range：特定の年にテストされる日付の範囲。
year：値の合計に基づく特定の年。（（年、1,1、XNUMX 年の最初の日を示し、 年、12,31、XNUMX 年の最後の日を示します。)

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

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

この式の説明：

=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）：セルE2の年の最初の日である12年01月01日以上の範囲B2020：B2の日付をチェックします。
$ B $ 2：$ B $ 12、 "<="＆DATE（E2,12,31）：セルE2の年の最後の日である12年12月31日以下の範囲B2020：B2の日付をチェックします。
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(date_range)=year)*sum_range)

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として返します。