2011年9月23日 星期五

Excel-找出每年的第一個星期幾是那一天

在 Excel 中如果要找某一天為星期幾只要透過 WEEKDAY 函數即可求得,如果要求每年的第一個星期幾是那一天,該如何處理?

以2011年為例第一個星期一為1/3,第一個星期二為1/4,…,第一個星期日為1/2。(參考下圖)

儲存格B2:{=SUM(IF(WEEKDAY(ROW(INDIRECT(DATEVALUE($B$1&"/1/1")&":"&DATEVALUE($B$1&"/1/7"))),2)=ROW(1:1),ROW(INDIRECT(DATEVALUE($B$1&"/1/1")&":"&DATEVALUE($B$1&"/1/7")))))}

此為陣列公式,輸入完成請按 Ctrl+Shift+Enter 鍵。複製儲存格B2,貼至儲存格B2:B8。

此公式的關鍵為將日期轉換為數字,例如:2011年1月1日=40544、2011年1月2日=40545、…。

DATEVALUE($B$1&"/1/1"):取出儲存格B1的年度,組成該年度的第1天的數值。

INDIRECT(DATEVALUE($B$1&"/1/1")&":"&DATEVALUE($B$1&"/1/7")):轉換為一段位址範圍,例如:「40544:40550」

ROW(INDIRECT(DATEVALUE($B$1&"/1/1")&":"&DATEVALUE($B$1&"/1/7"))):將一段位址範圍轉換為列的範圍,例如:「ROW(40544:40550)」,即第40544列至40550列。

透過 WEEKDAY 函數將這段列的範圍透過陣列公式,得到1~7的數字陣列,其中星期一得到1、星期二得到2、…、星期日得到7。

整個公式即可得到一段由1~7組成的陣列(其順序為當年的星期順序)對應到1/1~1/7的陣列,參考上圖右。

公式中的 SUM 函數,其實只是要 IF 函數所得的陣列,例如:{40544, False, False, False, False, False, False},取出數值部分,該數值對應到一個日期。

沒有留言:

張貼留言

好康東東