在 Excel 中依年份加總數值

假設您有一組包含日期與銷售金額的資料，並希望根據下圖所示的指定年份計算總金額。在 Excel 中，只要運用標準的 SUMIFS 與 SUMPRODUCT 函數，就能快速輕鬆完成這項任務。本教學將詳細說明如何使用這兩個函數。

• 在 Excel 中使用 SUMIFS 與 DATE 函數依年份加總數值
• 在 Excel 中使用 SUMPRODUCT 函數依年份加總數值

在 Excel 中使用 SUMIFS 與 DATE 函數依年份加總數值

若要根據指定年份加總總計數值，您應先使用 DATE 函數指定該年的第一天與最後一天，再使用 SUMIFS 函數對到某天範圍內的所有數值進行加總，其通用語法如下：

• sum_range：要加總的數值範圍；
• date_range：要針對特定年份進行測試的日期範圍；
• year：您要據以加總數值的特定年份。（year, 1,1表示該年的第一天，而year,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 範圍中的日期是否大於或等於 2020 年 1 月 1 日——也就是 E2 儲存格所指定年份的第一天。
• $B$2:$B$12,"<="&DATE(E2,12,31)：檢查 B2:B12 範圍中的日期是否小於或等於 2020 年 12 月 31 日——也就是 E2 儲存格所指定年份的最後一天。
• SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31))：符合條件後，系統將自動加總 C2:C12 範圍內的數值。

在 Excel 中使用 SUMPRODUCT 函數依年份加總數值

此外，以下由 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.

相關函數：

更多文章：