在 Excel 裡,如何列出指定日期和星期幾的全年日期?

Excel-列出指定日期和星期幾的全年日期

【公式設計與解析】

儲存格D4:=LET(x,ROW(INDIRECT(DATE(A4,1,1)&":"&DATE(A4,12,31))),FILTER(x,WEEKDAY(x,2)=B4))

(1) DATE(A4,1,1)&":"&DATE(A4,12,31)

利用 DATE 函數將儲存格A4指定的年份起始日期(1/1)至結束日期(12/31)組成字串。

例如指定「2023年」傳回字串:「44927:45291」。

image

(2) INDIRECT(DATE(A4,1,1)&":"&DATE(A4,12,31))

利用 INDIRECT 函數將第(1)式的字串轉換為位址。

(3) ROW(INDIRECT(DATE(A4,1,1)&":"&DATE(A4,12,31)))

將第(2)式的位址代入 ROW  函數,並傳回數字陣列「44927:45291」

(4) LET(x,第(3)式,FILTER(x,WEEKDAY(x,2)=B4))

利用 LET 函數設 x 為公式:

ROW(INDIRECT(DATE(A4,1,1)&":"&DATE(A4,12,31)))的傳回值。

(5) FILTER(x,WEEKDAY(x,2)=B4)

將第(4)式的變數「x」代入公式:FILTER(x,WEEKDAY(x,2)=B4),以篩選方式篩出儲存格B4指定的星期幾(星期三)的日期。

如果你的 Excel 版本是 2021 版以下,則使用以下公式:

儲存格D4:=FILTER(ROW(INDIRECT(DATE(A4,1,1)&":"&DATE(A4,12,31))),WEEKDAY(ROW(INDIRECT(DATE(A4,1,1)&":"&DATE(A4,12,31))),2)=B4)

 

【參考資料】

 FILTER 函數參考微軟提供的說明網頁:FILTER 函數
 LET 函數可以參考微軟提供的說明網頁:LET 函數
 WEEKDAY 函數參考微軟提供的說明網頁:WEEKDAY 函數
 INDIRECT 函數參考微軟提供的說明網頁:INDIRECT 函數
 DATE 函數參考微軟提供的說明網頁:DATE 函數
 ROW 函數參考微軟提供的說明網頁:ROW 函數
 

學不完.教不停.用不盡文章列表

arrow
arrow
    文章標籤
    Excel 篩選
    全站熱搜

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