Mr.Lazy愛偷懶先生

在 Excel 用公式製作萬年小型行事曆

使用函數: DATEVALUE、SEQUENCE、WEEKDAY 函數。

版本: MS Excel 2021。

函數介紹

DATEVALUE 函數

將原本以日期形成呈的日期，如: ”2023/5/1”，轉換成Excel解讀日期的代碼數字。如: 2023/5/1轉代碼為45047；2023/8/1轉代碼為45139。
　公　式: =DATEVALUE(date text)
範例公式: =DATEVALUE(B1&"/"&B2&"/"&1)，結果為 45170。

• B1數值: 2023
• B2數值: 9

公式說明:

將數值套入公式後，用”/”串接起來。
= DATEVALUE(B1&"/"&B2&"/"&1) → DATEVALUE(2023/9/1) → 45170。

SEQUENCE 函數

SEQUENCE函數是一種用於生成數列排序的工具。這個函數可以用來自動產生從指定的起始數字開始，遞增或遞減指定步長的數字序列。
　公　式: = SEQUENCE (Row, Columns, Start, Step)

• Row: 列數(直式)要多少欄位。
• Columns: 欄數(橫式)要多少欄未。
• Start: 開始的數列值。
• Step: 每個序列值的遞增量

範例公式: =SEQUENCE(3,4,2,2)，產生結果如下圖。

公式說明: 公式中的數字「3」代表列數(直)要求3列；數字「4」欄數(橫)要求4欄；從「2」作為起始值；間距為「2」遞增。

WEEKDAY 函數

WEEKDAY函數是一種日期時間函數，可用來檢視日期是星期幾。公式結果以數字方式呈現，可以方便地進行日期計算和時間管理。例如，通過將函數應用於一系列日期，可以計算這些日期在一周中的分佈情況，或將日期按照星期幾進行分類。

　公　式: =WEEKDAY(Serial_number, Return_type)

• Serial_number: 判斷該日期的欄位，欄位格式必須為計算結果、通用格式、非文字格式。
• Return_type: 決定回傳數字的類型，間接設定起始星期。可選用日期如下表。

範例公式: =WEEKDAY(A1,2)，結果為6。

公式說明: 判斷A1欄位數值 2023/4/15，將星期一做節起始點，結果為6。

範例製作

綜合以上介紹，將逐步教學: 如何製作萬年行事曆。

1. 於 D3 公式: =DATEVALUE(B1&"/"&B2&"/"&1)

• 用DATEVALU函數串接日期後，變成Excel日期格式，結果為45170。

2. 將 D3 欄位格式為「通用格式」，修改為只顯示年和月。

• 單擊 D3 欄位後，按下Ctrl+1數字鍵開啟「數字儲存格設定」。
• 選擇「自訂」，輸入"yyyy/mm"，後確定。

3.點擊 D5 欄位，後輸入公式: =SEQUENCE(6,7,$D$3-WEEKDAY(D3,1)+1,1)

• 6: 設定列位數，共6列。
• 7: 設定欄位數，共7欄。
• WEEKDAY(D3,1): 計算D3欄位(2023/9/1)，回傳設定為1，為能符合行事曆由左至右，由星期日至星期六之設定。結果為 6。
• $D$3-WEEKDAY(D3,1): 2023/9/1 - 6 = 45164(通用格式) = 2023/8/26(日期格式)。
• +1: 調整格式位置，符合行事曆格式。
• 1: 遞增值為 1 。

4.完成公式結果如下:

5.框選 D5:J10 欄位，由通用格式轉化只顯示「日」。

• 框選範圍，按下Ctrl +數字1鍵。
• 選擇「自訂」，在類型輸入: "d"後，確定。

完成如下圖，此時會發現在2023/09月的行事曆裡面，包含2023/8、2023/10 的日期。

6.條件格式化設定，新增規則。

• 框選 D5:J10 欄位，點選新增規則。
• 開啟新增格式化規則 → 選擇最後一個「使用公式來決定要格式化那些儲存格」。
• 輸入公式: =MONTH(D5)<>MONTH($D$3)。
• 選擇格式 → 數值 → 自訂 → 在類型輸入: ;;; 後，確定。

上述輸入公式意思是，當D5欄位不等於$D$3時，顯示空白(設定;;;表示)

因為上述公式沒有鎖定($)，所以 D5:J10 欄位內會自動與$D$3進行比對。