學不完．教不停．用不盡

本篇想要介紹關於 INDEX 函數，用於計算指定日期前的累計金額。並會以 OFFSET 函數和 FILTER 函數予以對照求解，以增進函數的理解與應用。

為了公式易於理解，先定義儲存格名稱。選取儲存格A3:B29，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期、金額。

先觀察下圖，要注意到原始的日期的清單中，日期是會重覆出現的。

方式一：使用 OFFSET 函數

累計金額公式：=SUM(OFFSET(B4,0,0,MATCH(E4,日期,1),1))

(1) MATCH(E4,日期,1),1)

使用 MATCH 函數查詢儲存格E4在「日期」儲存格範圍裡的位置，會依位置傳回一個數值。(本例傳回：10)

注意：在公式中使用參數「1」，是因為相同的日期會重覆現。如果使用參數「0」，則只會傳回重覆的第一個日期。

(2) 將第(1)式代入 OFFSET 函數，得到從第一個儲存格至符合條件的「儲存格範圍」。(本例傳回：B4:B13)

(3) 最後由 SUM 函數對第(2)式傳回的儲存格範圍予以加總，即為所求。

方式二：使用 INDEX 函數

累計金額公式：=SUM(B4:INDEX(金額,MATCH(E4,日期,1)))

(1) MATCH(E4,日期,1),1)

使用 MATCH 函數查詢儲存格E4在「日期」儲存格範圍裡的位置，會依位置傳回一個數值。(本例傳回：10)

(2) B4:INDEX(金額,MATCH(E4,日期,1))

將第(1)式代入 INDEX 函數傳回來的不是一個數值，而是一個位址。這是因為公式裡使用了儲存格範圍的寫法：「B4:INDEX(金額,MATCH(E4,日期,1))」，INDEX 函數會傳回「B13」。

(3) 最後由 SUM 函數對第(2)式傳回的儲存格範圍予以加總，即為所求。

方式三：使用 FILTER 函數

累計金額公式：=SUM(FILTER(金額,日期<=E4))

(1) FILTER(金額,日期<=E4)

利用 FILTER 函數依條件：「日期<=E4」，傳回符合者對應的金額。在此會傳回一個動態陣列。

(2) 由 SUM 函數對第(1)式傳回的儲存格範圍予以加總，即為所求。

4. 如何製作日期的下拉式清單

日期的下拉式清單是以資料驗證方式製作，因為要製作不重覆日期的下拉式清單，

於儲存格E16，使用公式：=UNIQUE(日期)。

再於資料驗證中設定：

儲存格內允許：清單

來源：=E16#，在此「#」是指該位址為動態陣列。

【參考資料】

 UNIQUE 函數參考微軟提供的說明網頁：UNIQUE 函數

 FILTER 函數參考微軟提供的說明網頁：FILTER 函數

 OFFSET 函數參考微軟提供的說明網頁：OFFSET 函數

 MATCH 函數參考微軟提供的說明網頁：MATCH 函數

 INDEX 函數參考微軟提供的說明網頁：INDEX 函數