在日常工作中，我們每個月都要對員工進行值班安排，且涉及到周末和節假日，那麼如何用EXCEL制作一個簡易方便的排班系統呢？

下圖為排班表的最終效果，當然還可以對表格進行美化，隻需提前設置好員工基本信息、節假日等數據，點點選選即可快速排班。

圖1

系統共有3個表冊，第一個表為“節假日和參數設置”，主要設置年度節假日，用函數判斷是否需要上班；第二個表為“員工基本信息”，主要内容有員工姓名、部門、職務、聯系方式等；第三個表為“排班表”，主要設置一個月的排班，休息日用紅色字體自動顯示（如圖1）。

下面我們依次講解各個表格的用法。

一、節假日和參數設置

我們指定一個單元格作為年度的調用數據單元格，然後根據年度生成日期，如圖2，我們指定D2單元格輸入年度，然後在A4單元格用時間函數DATE調用D2單元格的年度生成每日的日期。

圖2

DATE函數：DATE(年,月,日)，表示返回表示特定日期的連續序列号。

我們在單元格直接輸入函數如：=DATE(2021,9,1)，那麼顯示的内容即為2021-9-1，利用DATE函數特性，我們可以生成一整年甚至更多的日期，下面我們以生成一年日期為例。

在A4單元格輸入函數：=DATE(D2,1,ROW(A1))

D2表示調用2021年度（可根據需要寫不同的年度），1表示表示一年中1月至12月的1月份開始往下填充，ROW為行，表示一個月中的1日到30日的各天，用A1表示第一行，往下拖動會變成A2 A3 A4...依次加1，到下月1日的時候會自動變化填充。在第一個日期第一個輸入函數回車後，往下填充一年的數據即可。同時，我們需要知道哪天是星期幾，隻需要在B4單元格輸入函數=TEXT(A4,"aaaa")即可顯示星期，如圖3。

圖3

“節假日及節氣”這一列可有可無，設置該列的目的是方便看是否國家法定節假日，由此判斷是休息還是上班，需要根據每年的實際情況手動錄入。因為國家法定節假日可能包含周末和周末補班的問題，所以設置“休息和上班”列，主要目的是便于判斷是否上班，然後在“排班表”引用，我們在維護數據時，隻需要維護節假日休息和周末補班即可。以上信息全部維護結束後，需要用IF函數判斷是否上班，在“排班表”調用後用條件格式顯示休息日為紅色（或其他顔色，根據喜好設置）字體。

在“是否上班”列的E4單元格輸入函數：=IF(AND(OR(B4="星期六",B4="星期日"),D4<>"班"),"否",IF(D4="休","否","是"))，則自動識别哪天上班，哪天休息。

IF(AND(OR(B4="星期六",B4="星期日"),D4<>"班"),"否"的意思是：如果是星期六和星期天，并且不上班，則為不上班。IF(D4="休","否","是")則是判斷是否為休息日。

圖4

到此，“節假日和參數設置”表的設置就完成了。部分細心的朋友會發現該表中還有一個“日期設置”，主要是方便“排班表”調用選擇。

二、基本信息

這個表就容易理解了，主要是領導和員工的基本信息，最後增加一列輔助列，把領導姓名和聯系電話合并便于調用。

圖5

在“姓名電話合并”列用連接符&把姓名和聯系電話合并起來即可，方法很簡單，在單元格錄入：=B3&E3，回車後往下複制填充就可以了，當有新員工的時候，隻需要複制最後一行，粘貼修改數據進行增加。

三、排班表

以上兩個表格都維護好以後，就需要在“排班表”裡進行調用，以下是效果圖。

圖6

從上圖可以看到，表格左側有設置是否上班和日期設置，這裡為輔助列，方便設置日數和顔色顯示，隻需要設置“排班表”打印區域即可。

第一步：設置日期

在第一列第一個日期單元格輸入函數：=DATE($P$3,$P$4,ROW(A1))，然後往下拖動到需要的位置，在第二列第一個日期單元格輸入函數：=DATE($P$3,$P$4,ROW(A17))，拖動到合适的位置，這裡值得注意的是，ROW(A17)表示從第17個日期開始，因為我們第一列最後一個日期是16日。這時我們發現單元格顯示了完整日期，我們隻想顯示到單日，選中需要設置的單元格，單擊鼠标右鍵，打開“設置單元格格式”對話框，在“數字”功能組找到“自定義”，在右側“類型”下方文本框輸入“d日”，表示天數，确定後就得到我們想要的效果。如下圖：

圖7

關于星期的函數，上述已經講過，用函數=TEXT(A3,"aaaa")就能顯示星期。

第二步：數據有效性

設置好日期後，我們需要把值班人員信息和帶班領導、駕駛員的信息調用過來，這時候就該數據有效性出場了。選擇“值班人及電話”列需要調用數據的單元格，在“數據”選項卡下找到“有效性”，打開對話框，在“設置”功能組“有效性條件”—“允許”選擇“序列”，其他默認，在“來源”下方輸入：=基本信息!$F$10:$F$20（表示需要值班的人員區域），或者點擊右邊圖标選擇區域，然後确定，帶班領導和駕駛員設置方法一樣，完善後就可以選擇排班了。

圖8

第三步：調用是否上班數據

在K3單元格輸入函數：=INDEX(節假日和參數設置!E:E,MATCH($A3,節假日和參數設置!A:A,))，表示從“節假日和參數設置”表中“是否上班”列提取數據，對應的兩列依次設置。

圖9

第四步：判斷是否兩個月的日期排在一張表上

我們都知道，瑞年的2月有28天，平年的2月有29天，此時就需要增加輔助列判斷當月有多少天，然後用條件格式把字體顯示為白色（因為背景為白色，設置字體為白色後就看不到内容）。

在對應每個月29日的單元格輸入函數：

=IF(MONTH(F15)<>$P$4,"不同月","")，用MONTH(F15)計算當月的月份數，然後用IF函數判斷是否和當前月一緻，如果不一樣則顯示“不同月”，否則留空，然後往下拖動複制到31日的位置。

圖10

第五步：休息日用紅色字體顯示

選擇1日單元格，在“開始”選項卡下找到“條件格式”—“新建規則”—“使用公式确定要設置格式的單元格”，在下方文本框輸入函數：=$K3="否"，點擊“格式”—“字體”—“顔色”，選擇紅色（或其他顔色），然後點擊“确定”返回。保持選中1日單元格，單擊“格式刷”，然後按住鼠标左鍵拖動到“值班駕駛員”這一列最後一行後松開，即可設置休息日紅色顯示。

17日後面的設置方法和上面方法一樣，在剛才的文本框裡輸入=$L3="否"。

最後，在29日單元格按照上面的方法設置字體顔色為白色，函數為：=$M15="不同月"

圖11

至此，我們的操作基本完成，下面設置一下條件格式顯示休息日為紅色就大功告成。

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!