2014年5月8日 星期四

Excel-在日期清單中分年分月計算小計(SUMPRODUCT)

有網友問到,在一個 Excel 的資料表中含有日期和數量的清單,如何分年分月的統計加總結果?(參考下圖)

【準備工作】

選取A欄和B欄中含有資料的儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、數量。

【輸入公式】

本例可使用 SUMPRODUCT 函數執行多條件 AND 結果的加總運算。

儲存格E2:=SUMPRODUCT((YEAR(日期)=E$1)*(MONTH(日期)=ROW(1:1))*數量)

YEAR(日期)=E$1:條件一,判斷日期陣列中每個日期的「年份」是否和儲存格E1相同,結果傳回 TRUE/FALSE 陣列。

MONTH(日期)=ROW(1:1):條件二,判斷日期陣列中每個日期的「月份」是否和ROW(1:1)(=1)相同,即為一月。如果公式往下複製時,ROW(1:1)=1 → ROW(2:2)=2 → ROW(2:3)=3 …,如此可以判斷每一個月。

將上述二個條件的 TRUE/FALSE 的結果相乘,再乘以「數量」的陣量,然後加總結果,即為所求。

複製儲存格E2,貼至儲存格E2:J13。

沒有留言:

張貼留言

好康東東