2018年7月9日 星期一

Excel-找出日期清單中每個人員最後報名日期(OFFSET,MAX,SUBSTITUTE)

參考下圖,在 Excel 中有一個報名日期和報名人員的資料表(下圖左),由於每個人員有多次報名,如何找出每一個人員的最後一次報名日期(下圖右)?
Excel-找出日期清單中每個人員最後報名日期(OFFSET,MAX,SUBSTITUTE)

【公式設計與解析】
儲存格E2:{=OFFSET($A$1,MAX((SUBSTITUTE($B$2:$B$11,D2,"")<>
$B$2:$B$11)*ROW($B$2:$B$11))-1,0)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格E2,貼至儲存格E2:E11。
(1) SUBSTITUTE($B$2:$B$11,D2,"")
陣列公式中,利用 SUBSTITUTE 函數將每一個儲存格內容,含有儲存格D2內容者,全部置換成空字串。
(2) SUBSTITUTE($B$2:$B$11,D2,"")<>$B$2:$B$11)
判斷第(1)式的傳回結果和原來儲存格陣列內容是否相符,傳回 TRUE/FALSE 陣列。
本例儲存格D2的內容為「甲」,所以傳回 FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE
(3) 第(2)式*ROW($B$2:$B$11)
將第(2)式乘上儲存格陣列中每一個儲存格的列號(例如:ROW(B2)=2、ROW(B3)=3、...、ROW(B11)=11),在運算過程中 TRUE/FALSE 陣列會轉換為 1/0 陣列。
所以,傳回的結果即為含有儲存格D2內容的列號。本例結果傳回 0, 3, 0, 0, 0, 0, 0, 0, 0, 11。
(4) MAX(第(3)式)
利用 MAX 函數將第(3)式傳回的列號取其中的最大值。
(5) OFFSET($A$1,第(4)式-1,0)
將第(4)式傳回的列號最大值,代入 OFFSET 函數求得在第A欄中對應的日期。

沒有留言:

張貼留言

檢視其他文章

好康東東