今天是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,陣列公式)

【參考資料】

 FILTER 函數參考微軟提供的說明網頁:

arrow
arrow
    創作者介紹
    創作者 vincent 的頭像
    vincent

    學不完.教不停.用不盡

    vincent 發表在 痞客邦 留言(1) 人氣()