學不完．教不停．用不盡

（網友提問）在 Excel 的工作表中有一個日期清單，並且每個日期對應一個數值。如何找出同一欄最後一個指定數字對應的日期？

參考下圖，例如：最後一個一個『8』所對應的日期是2017/10/19。

 

【公式設計與解析】

選取儲存格B1:B25，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：數值。

儲存格E2：=OFFSET($A$1,SUMPRODUCT(LARGE((數值=D2)*ROW(數值),1))-1,0)

(1) (數值=D2)*ROW(數值)

在 SUMPRODUCT 函數中判斷數值陣列的每一個儲存格是否和儲存格D2相同，並且傳回 TRUE／FALSE 陣列。再將TRUE／FALSE 陣列乘以數值陣列中的每一個儲存格列號。

(2) LARGE((數值=D2)*ROW(數值),1)

利用 LARGE 函數找出第(1)式傳回值的最大值。

(3) SUMPRODUCT(LARGE((數值=D2)*ROW(數值),1))

在此的 SUMPRODUCT 函數讓儲存格參數可以執行陣列運算。

(4) OFFSET($A$1,SUMPRODUCT(LARGE((數值=D2)*ROW(數值),1))-1,0)

將第(3)式的傳回值代入 OFFSET 函數，可以求得儲存格A1起始所對應的日期。

 

【不同解法】

儲存格E2：{=OFFSET($A$1,LARGE(IF(數值=D2,ROW(數值),""),1)-1,0)}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。