2017年4月27日 星期四

Excel-利用下拉式清單動態計算合於起迄年月的總和(SUMPRODUCT,VLOOKUP,DATE,MID)

在 Excel 中有一個日期和數值的資料清單,如果想要建立動態的小計查詢作業,該如何處理?
以下圖為例,希望使用下拉式清單根據起迄年月來查詢某個日期區間的小計。
Excel-利用下拉式清單動態計算合於起迄年月的總和(SUMPRODUCT,VLOOKUP,DATE,MID)
註:儲存格F2公式=G2&"/"&H2&"-"&I2&"/"&J2

【公式設計與解析】
先來建立下拉式選單:
選取儲存格D2,開啟資料驗證對話框,在[設定]標籤中設定資料驗證準則:
儲存格內允許:清單;來源:=$F$2:$F$11
Excel-利用下拉式清單動態計算合於起迄年月的總和(SUMPRODUCT,VLOOKUP,DATE,MID)
接著要定義名稱,選取儲存格A1:B200,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、數值。
儲存格D10:=SUMPRODUCT((日期>=DATE(VLOOKUP(D2,F2:J11,2,FALSE),
VLOOKUP(D2,F2:J11,3,FALSE),1))*(日期<=DATE(VLOOKUP(D2,F2:J11,4,FALSE),
VLOOKUP(D2,F2:J11,5,FALSE)+1,1)-1)*數值)
公式很長挺嚇人的!看看以下的解析:
(1) 查詢起年:VLOOKUP(D2,F2:J11,2,FALSE)
透過 VLOOKUP 函數在起迄年月的清單查詢。
(2) 查詢起月:VLOOKUP(D2,F2:J11,3,FALSE)
(3) 建立起年月的第一日:DATE(第(1)式,第(2)式,1)
(4) 查詢迄年:VLOOKUP(D2,F2:J11,4,FALSE)
(5) 查詢迄月:VLOOKUP(D2,F2:J11,5,FALSE)
(6) 建立迄年月的最後一日:DATE(第(3)式,第(4)式+1,1)-1
(7) SUMPRODUCT((日期>=第(3)式)*(日期<=第(6)式)*數值)
條件一:日期>=第(3)式;條件二:日期<=第(6)式
在 SUMPRODUCT 函數將雙條件再乘以數值,即可利用乘積和來建立小計。

可能簡化公式?如果將項目改成如下圖的格式,其內容中每個項目的格式是固定的:
Excel-利用下拉式清單動態計算合於起迄年月的總和(SUMPRODUCT,VLOOKUP,DATE,MID)
儲存格D10:=SUMPRODUCT((日期>=DATE(MID(D2,1,4),MID(D2,6,2),1))*
(日期<=DATE(MID(D2,9,4),MID(D2,14,2)+1,1)-1)*數值)
利用 MID 函數取出起迄的年月,即可代入公式運算。完全用不到查表清單!
你要做的是妥善建立項目的內容,透過巧妙安排資料的呈現,也能簡化公式!

沒有留言:

張貼留言

好康東東