2017年4月20日 星期四

Excel-利用下拉式清單計算多個月份的小計(SUMPRODUCT,VLOOKUP)

網友問到:在 Excel 中有一個日期清單(如下圖左),如何依指定的月份計算加總(如下圖右)?
如下圖,由於想要計算的月份沒有規則,並且希望能使用下拉式清單來選取月份,直接得到小計結果,該如何處理?
Excel-利用下拉式清單計算多個月份的小計(SUMPRODUCT,VLOOKUP)

【公式設計與解析】
1. 建立下拉式清單
在儲存格G2:I7中先建立想要小計的月份,欄位有:月份名稱、起始月份、終止月份。
接著,選取儲存格D2,建立「資料驗證」,設定如下:
(1) 儲存格內允許:清單
(2) 來源:=$G$2:$G$7
Excel-利用下拉式清單計算多個月份的小計(SUMPRODUCT,VLOOKUP)

2. 設定公式
首先要設定儲存格名稱。選取儲存格A1:B26,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、數值。
儲存格E2:=SUMPRODUCT((MONTH(日期)>=VLOOKUP(D2,G2:I7,2,FALSE))*
(MONTH(日期)<=VLOOKUP(D2,G2:I7,3,FALSE))*數值)
(1) VLOOKUP(D2,G2:I7,2,FALSE))
利用 VLOOKUP 函數求得儲存格D2(下拉式清單的選項)在儲存格G2:I7範圍中,查詢得到「起始月份」。
(2) VLOOKUP(D2,G2:I7,2,FALSE))
利用 VLOOKUP 函數求得儲存格D2(下拉式清單的選項)在儲存格G2:I7範圍中,查詢得到「終止月份」。
(3) MONTH(日期)>=VLOOKUP(D2,G2:I7,2,FALSE)
判斷日期陣列中每個日期的月份是否大於或等於起始月份,傳回 TRUE/FALSE 陣列。MONTH 函數可以傳回一個日期的月份。
(4) MONTH(日期)<=VLOOKUP(D2,G2:I7,3,FALSE)
判斷日期陣列中每個日期的月份是否小於或等於終止月份,傳回 TRUE/FALSE 陣列。
(5) SUMPRODUCT(第(3)式*第(4)式*數值)
在 SUMPRODUCT 函數中計算式的『*』,相當於執行邏輯 AND 運算,會將 TRUE/FALSE 陣列轉換為 1/0 陣列。在 SUMPRODUCT 函數中執行二個條件和數值陣列的乘積和,結果即為所求。
如果如下:
Excel-利用下拉式清單計算多個月份的小計(SUMPRODUCT,VLOOKUP)

沒有留言:

張貼留言

檢視其他文章

好康東東