學不完．教不停．用不盡

在 Excel 中有一個日期和數值的資料清單，如果想要建立動態的小計查詢作業，該如何處理？

以下圖為例，希望使用下拉式清單根據起迄年月來查詢某個日期區間的小計。

註：儲存格F2公式=G2&"/"&H2&"-"&I2&"/"&J2

【公式設計與解析】

先來建立下拉式選單：

選取儲存格D2，開啟資料驗證對話框，在[設定]標籤中設定資料驗證準則：

儲存格內允許：清單；來源：=$F$2:$F$11

接著要定義名稱，選取儲存格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 函數將雙條件再乘以數值，即可利用乘積和來建立小計。

可能簡化公式？如果將項目改成如下圖的格式，其內容中每個項目的格式是固定的：

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

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