2018年5月27日 星期日

Excel-在日期清單中取得日期區間裡最後一個日期對應的數值(VLOOKUP,LARGE,陣列公式)

在 Excel 中有一個日期清單(如下圖),如何在日期清單中取得指定日期區間裡最後一個日期對應的數值?
參考下圖,根據起始日期和結束日期的日期區間,想要找出最後日期所對應的數值,該如何處理?(下圖的日期已排序,由舊至新排序)
Excel-在日期清單中取得日期區間裡最後一個日期對應的數值(VLOOKUP,LARGE,陣列公式)

【公式設計與解析】
首先,定義儲存格名稱。
選取儲存格A1:A26,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期。
接著,輸入公式,儲存格D7:
{=VLOOKUP(LARGE(IF((日期>=D2)*(日期<=D4),日期),1),資料,2,FALSE)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
(1) IF((日期>=D2)*(日期<=D4),日期)
在陣列公式中判斷日期陣列中大於或等於儲存格D2並且小於或等於儲存格D4的日期者,傳回這些日期。
(2) LARGE(第(1)式,1)
根據第(1)式傳回的日期陣列,利用 LARGE 函數取出取大值者。(日期數值愈大,表示日期愈新。)
(3) VLOOKUP(第(2)式,資料,2,FALSE)}
將第(2)式傳回的日期代入 VLOOKUP 函數,以查表方式找出對應應數值。

如果日期清單是尚未排序者,套用相同公式,亦可得正確結果。
image

沒有留言:

張貼留言

檢視其他文章

好康東東