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

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

本次教學內容: 如何自行設計「自動化排班表」，包含自動 ... Mr.Lazy愛偷懶先生 跳到主文 Hello,之前是名稽核員，現在除工作外，不斷學習#Excel,#Word,#Google試算表等...。

這些都是為了提供自己在工作上的效率。

如果喜歡我寫的文章的話，留言是給我最大的鼓勵。

謝謝。

#excel#愛偷懶#稽核員#googlesheets E-mail:[email protected] 部落格全站分類：數位生活 相簿 部落格 留言 名片 Jul09Fri202118:24 115.Excel教學-2022年自動化月週排班表神器自動顯示放假日期 DearAll, 如果覺得每月/週都要手動修改排班表很麻煩的話，可以下載練習檔，來解決您的困擾。

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

練習檔下載：請點我。

使用版本：Excel2019。

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

使用函數： 分類 函數 時間函數 DATE、MONTH 邏輯函數 RIGHT、OR、IF、IFERROR 文字函數 TEXT 查閱/參照函數 MATCH      教學步驟   Step1:先將表格設計出來 Step2:插入微調按鈕 1.位置:開發人員→插入→表單控制項→微調按鈕。

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

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

名稱 說明 數值 最大值 年份最大數值 2021 最小值 年份最小數值 2040 儲存格連結 顯示年分位置 $C$1   4.關於「月份」，使用相同方式，數值範圍為1~12。

  Step3:設定「日期」公式 1.C3儲存格 =DATE($C$1,$I$1,1) 2.說　　明:=DATE(年份,月份,日期) DATE函數 C3儲存格 說明 年份 $C$1 微調按鈕之年份 月份 $I$1 微調按鈕之月份 日期 1 手動輸入數值   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函數。

這樣子就可以避免出現上述錯誤值。

(第三版圖)     Step4:設定「星期」公式。

C4儲存格=RIGHT(TEXT(C3,"aaa"),1) 說　　明:將C3儲存格數值格式「2/1」，轉成文字格式「週一」後，擷取從右至左第1個字元。

最終，顯示結果如下:   更多關於DATE、TEXT函數介紹，請參閱:  52.EXCEL教學_自動民國轉西元年,西元轉民國年(TEXT,DATE,MID) 96.Excel教學-月份自動顯示星期幾(TEXT,RIGHT) 時間/日期格式，請參閱: 104.Excel教學-HR人資打卡計算實際加班工時,常見時間格式/代碼   Step5:設定遇到「週六、週日」時，顯示顏色 此Step主要使用「設定格式化的條件」。

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

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

3.公式輸入:=OR(C$4="六",C$4="日") 說　　明:在 Step4 階段，已將「星期」欄列的儲存格，設定成只顯示一、二、…、六、日之方式，只要當該儲存格顯示「六」或「日」時，就會顯示設定的格式(填滿墨綠色)。

關於 OR 函數等相關介紹，請參閱 : 11.EXCEL教學：IF函數應用可以更好(下)｜IF函數/AND/OR/NOT/複製格式   Step6:設定遇到「特定假日」時，顯示顏色 1.建立「特定假日清單」 (下圖為 2022 年放假清單，可查閱練習檔)。

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

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

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

MATCH函數 說明 儲存格 範例 搜尋值 想要搜尋的數值(文字) C$3 排班表日期 搜尋範圍 搜尋的範圍 (水平or垂直欄位) '2022年放假清單'!$A:$A, 放假清單 搜尋類型 類型:0、1、2 0 完全符合     *備註:關於'2022年放假清單'!的$A:$A，是整欄的意思，為了方便之後直接新增特定假日。

更多關於MATCH函數介紹，請參閱: 21.EXCEL教學：最強拍檔MATCH、INDEX函數/附練習檔 22.EXCEL教學：MATCH函數，在表格中搜尋指定項目位置｜附練習檔   Step7:新增下拉式選單 1.框選填寫班表的範圍後(C5~AG16範圍)。

2.點選資料→資料工具→資料驗證→選擇「清單」→選擇資料來源→確定。

文章標籤 排班神器 排班表excel 班表格式 放假日 excel日期 自動顯示放假 2022行事曆 2022排班表 全站熱搜 創作者介紹 愛偷懶先生 Mr.Lazy愛偷懶先生 愛偷懶先生發表在痞客邦留言(3)人氣() E-mail轉寄 全站分類：數位生活個人分類：Excel-函數此分類上一篇：114.Excel教學-讓中獎人不重複中獎,超簡單抽獎小程式(VLOOKUP,RANK) 此分類下一篇：117.Excel教學-靠vlookup函數神器,自動請假更新排班表系統 上一篇：114.Excel教學-讓中獎人不重複中獎,超簡單抽獎小程式(VLOOKUP,RANK) 下一篇：116.Excel教學-讓Google表單與MSExcel連線,自動抓取網頁資料,回傳並更新 ▲top 留言列表 發表留言 參觀人氣 本日人氣： 累積人氣： 熱門文章 文章分類 SEO教學(2) GoogleAnalytics(2)總章節(3) Google(3) Gmail郵件(1)Google試算表(6)Google表單(4) 出差經驗(1) 飯店入住(2) Microsoft(4) Excel-圖表(22)Excel-功能(43)Excel-函數(114)MS-PowerPoint(2) Window教學(3)應用程式/網站(13)未分類文章(11) 文章彙整 文章彙整 2022七月(1) 2022六月(1) 2022五月(4) 2022四月(20) 2022三月(20) 2022二月(15) 2022一月(6) 2021十二月(4) 2021十一月(6) 2021十月(7) 2021九月(3) 2021八月(3) 2021七月(9) 2021六月(5) 2021五月(5) 2021四月(6) 2021三月(3) 2021二月(1) 2021一月(4) 2020十二月(10) 2020十一月(8) 2020十月(3) 2020九月(2) 2020八月(1) 2020七月(4) 2020六月(9) 2020五月(14) 2019十二月(3) 2019十一月(23) 2019十月(25) 2019九月(1) 所有文章列表 部落格文章搜尋 贊助商連結 贊助商連結 回到頁首 回到主文 免費註冊 客服中心 痞客邦首頁 ©2003-2022PIXNET 關閉視窗 PIXNET Facebook Yahoo! Google MSN {{guestName}} (登出) 您尚未登入，將以訪客身份留言。

亦可以上方服務帳號登入留言 請輸入暱稱(最多顯示6個中文字元) 請輸入標題(最多顯示9個中文字元) 請輸入內容(最多140個中文字元) 請輸入左方認證碼： 看不懂,換張圖 請輸入驗證碼 送出留言