115.Excel教學-2022年自動化月週排班表神器自動顯示放假日期 @ Mr.Lazy愛偷懶先生

115.Excel教學 - 2022年自動化月週排班表神器自動顯示放假日期

Dear All,

如果覺得每月/週都要手動修改排班表很麻煩的話，可以下載練習檔，來解決您的困擾。

本次教學內容 : 如何自行設計「自動化排班表」，包含自動顯示六日放假、特定假日。

使用版本：Excel 2019。

使用功能：微調按鈕、設定格式化的條件。

使用函數：

教學步驟

Step 1 : 先將表格設計出來

Step 2 : 插入微調按鈕

1. 位置: 開發人員 → 插入 → 表單控制項 → 微調按鈕。

2. 點選微調按鈕，選擇「屬性」。

3. 進入「控制」頁面後，設定最大值、最小值、儲存格連結後，按下確定。

4. 關於「月份」，使用相同方式，數值範圍為1 ~ 12。

Step 3 : 設定「日期」公式

1. C3儲存格 =DATE($C$1,$I$1,1)

2. 說　　明 : =DATE(年份,月份,日期)

3. D3儲存格 =IFERROR(IF(MONTH(C3+1)=$I$1,C3+1,""),"")

此公式較複雜，演變如下:

第一版	D3 =C3+1
說明	為不顯示超過當月之日期，如: 在2月的排班表裡，顯示3月的日期。(第一版圖)。因此，需新增IF函數。
第二版	D3 =IF(MONTH(C3+1)=$I$1,C3+1,"")
說明	如果透過MONTH函數，顯示之月份等於 $I$1 的話，執行C3+1公式，否則顯示空白。但如遇到無法判斷時，顯示為錯誤。(第二版圖)
第三版	D3 =IFERROR(IF(MONTH(C3+1)=$I$1,C3+1,""),"")
說明	為必免錯誤值顯示，新增IFRROR函數。這樣子就可以避免出現上述錯誤值。(第三版圖)

Step 4 : 設定「星期」公式。

C4儲存格 =RIGHT(TEXT(C3,"aaa"),1)

說　　明 : 將C3儲存格數值格式「2/1」，轉成文字格式「週一」後，擷取從右至左第1個字元。

最終，顯示結果如下:

更多關於DATE、TEXT函數介紹，請參閱:

時間 / 日期格式，請參閱:

Step 5 : 設定遇到「週六、週日」時，顯示顏色

此Step主要使用「設定格式化的條件」。

1. 框選範圍後 (C3 ~ AG16儲存格範圍) 。

2. 選擇「設定格式化的條件」→新增規則→選擇「使用公式來格式化哪些儲存格」。

3. 公式輸入 : =OR(C$4="六",C$4="日")

說　　明 : 在 Step 4 階段，已將「星期」欄列的儲存格，設定成只顯示一、二、…、六、日之方式，只要當該儲存格顯示「六」或「日」時，就會顯示設定的格式 (填滿墨綠色)。

關於 OR 函數等相關介紹，請參閱 :

Step 6 : 設定遇到「特定假日」時，顯示顏色

1. 建立「特定假日清單」 (下圖為 2022 年放假清單，可查閱練習檔)。

2. 在排班表內，框選和 Step 5 同樣的範圍 → 選擇新增規則 → 同樣選擇最後一個。

3.公式輸入 : =MATCH(C$3,'2022年放假清單'!$A:$A,0)。

說　　明 : 框選範圍內，只要C3、D3、E3 ~ AG3欄位日期，等於2022年放假清單日期時，就會顯示設定的格式 (填滿橘色)。