2017年12月18日 星期一

Excel-找出同一欄最後一個指定數字對應的日期(SUMPRODUCT,LARGE)

(網友提問)在 Excel 的工作表中有一個日期清單,並且每個日期對應一個數值。如何找出同一欄最後一個指定數字對應的日期?
參考下圖,例如:最後一個一個『8』所對應的日期是2017/10/19。
Excel-找出同一欄最後一個指定數字對應的日期(SUMPRODUCT,LARGE)

【公式設計與解析】
選取儲存格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 會自動加上「{}」。

沒有留言:

張貼留言

檢視其他文章

好康東東