2017年1月13日 星期五

Excel-列出近五年13號星期五的日期(SMALL,ROW,WEEKDEY,陣列公式)

今天是2017年1月13日星期五,有人覺得這是個特殊的日子。要如何利用 Excel 來找出近五年中,恰好是13日星期五的日子?
Excel-列出近五年13號星期五的日期(SMALL,ROW,WEEKDEY,陣列公式)

【公式設計與解析】
首先,把近五年的起訖日期(2017/1/1~2021/12/31)所代表的數值找出來。
儲存格B2:=N(A2);傳回第一天日期代表的數值:42736。
儲存格B3:=N(A3);傳回最末天日期代表的數值:44561。
接著,找出13日星期五的日期,設定公式:
儲存格D2:{=SMALL(IF((DAY(ROW(42736:44561))=13)*(WEEKDAY(ROW
(42736:44561),2)=5),ROW(42736:44561),99999),ROW(1:1))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格D2,貼至儲存格D2:D12。
(1) ROW(42736:44561)
在陣列公式中可以代表一個日期區間(2017/1/1~2021/12/31)。
(2) DAY(ROW(42736:44561))=13
條件一:判斷每個日期中的『日』數是否為『13』,傳回 TRUE/FALSE 陣列。
(3) WEEKDAY(ROW(42736:44561),2)=5
條件一:判斷每個日期是否為『星期五』,傳回 TRUE/FALSE 陣列。
利用 WEEKDAY 函數,其中的參數『2』,表示傳回值 1~7 對應星期一~星期日。
Excel-列出近五年13號星期五的日期(SMALL,ROW,WEEKDEY,陣列公式)
(4) IF((條件一)*(條件二),ROW(42736:44561),99999)
『*』運算相當於執行邏輯 AND 運算,如果符合二個條件者(既是13日,也是星期五),傳回 ROW(42736:44561) 對應的數值,否則,傳回 999999(這只是一個大於44561的任意數)。
(5) SMALL(第(4)式,ROW(1:1))
當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。再利用 SMALL 函數由小到大依序取出第(4)式中傳回的數值。
(6) 將儲存格D2:D12設定日期格式為『yyyy/mm/dd』,大功告成。
Excel-列出近五年13號星期五的日期(SMALL,ROW,WEEKDEY,陣列公式)

沒有留言:

張貼留言

檢視其他文章

好康東東