在 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 函數取出起迄的年月,即可代入公式運算。完全用不到查表清單!

你要做的是妥善建立項目的內容,透過巧妙安排資料的呈現,也能簡化公式!

arrow
arrow
    創作者介紹
    創作者 vincent 的頭像
    vincent

    學不完.教不停.用不盡

    vincent 發表在 痞客邦 留言(0) 人氣()