在日常工作中，我們每個(gè)月都要對(duì)員工進(jìn)行值班安排，且涉及到周末和節(jié)假日，那么如何用EXCEL制作一個(gè)簡(jiǎn)易方便的排班系統(tǒng)呢？

下圖為排班表的最終效果，當(dāng)然還可以對(duì)表格進(jìn)行美化，只需提前設(shè)置好員工基本信息、節(jié)假日等數(shù)據(jù)，點(diǎn)點(diǎn)選選即可快速排班。

圖1

系統(tǒng)共有3個(gè)表冊(cè)，第一個(gè)表為“節(jié)假日和參數(shù)設(shè)置”，主要設(shè)置年度節(jié)假日，用函數(shù)判斷是否需要上班；第二個(gè)表為“員工基本信息”，主要內(nèi)容有員工姓名、部門(mén)、職務(wù)、聯(lián)系方式等；第三個(gè)表為“排班表”，主要設(shè)置一個(gè)月的排班，休息日用紅色字體自動(dòng)顯示（如圖1）。

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

一、節(jié)假日和參數(shù)設(shè)置

我們指定一個(gè)單元格作為年度的調(diào)用數(shù)據(jù)單元格，然后根據(jù)年度生成日期，如圖2，我們指定D2單元格輸入年度，然后在A4單元格用時(shí)間函數(shù)DATE調(diào)用D2單元格的年度生成每日的日期。

圖2

DATE函數(shù)：DATE(年,月,日)，表示返回表示特定日期的連續(xù)序列號(hào)。

我們?cè)趩卧裰苯虞斎牒瘮?shù)如：=DATE(2021,9,1)，那么顯示的內(nèi)容即為2021-9-1，利用DATE函數(shù)特性，我們可以生成一整年甚至更多的日期，下面我們以生成一年日期為例。

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

D2表示調(diào)用2021年度（可根據(jù)需要寫(xiě)不同的年度），1表示表示一年中1月至12月的1月份開(kāi)始往下填充，ROW為行，表示一個(gè)月中的1日到30日的各天，用A1表示第一行，往下拖動(dòng)會(huì)變成A2 A3 A4…依次加1，到下月1日的時(shí)候會(huì)自動(dòng)變化填充。在第一個(gè)日期第一個(gè)輸入函數(shù)回車(chē)后，往下填充一年的數(shù)據(jù)即可。同時(shí)，我們需要知道哪天是星期幾，只需要在B4單元格輸入函數(shù)=TEXT(A4,"aaaa")即可顯示星期，如圖3。

圖3

“節(jié)假日及節(jié)氣”這一列可有可無(wú)，設(shè)置該列的目的是方便看是否國(guó)家法定節(jié)假日，由此判斷是休息還是上班，需要根據(jù)每年的實(shí)際情況手動(dòng)錄入。因?yàn)閲?guó)家法定節(jié)假日可能包含周末和周末補(bǔ)班的問(wèn)題，所以設(shè)置“休息和上班”列，主要目的是便于判斷是否上班，然后在“排班表”引用，我們?cè)诰S護(hù)數(shù)據(jù)時(shí)，只需要維護(hù)節(jié)假日休息和周末補(bǔ)班即可。以上信息全部維護(hù)結(jié)束后，需要用IF函數(shù)判斷是否上班，在“排班表”調(diào)用后用條件格式顯示休息日為紅色（或其他顏色，根據(jù)喜好設(shè)置）字體。

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

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

圖4

到此，“節(jié)假日和參數(shù)設(shè)置”表的設(shè)置就完成了。部分細(xì)心的朋友會(huì)發(fā)現(xiàn)該表中還有一個(gè)“日期設(shè)置”，主要是方便“排班表”調(diào)用選擇。

二、基本信息

這個(gè)表就容易理解了，主要是領(lǐng)導(dǎo)和員工的基本信息，最后增加一列輔助列，把領(lǐng)導(dǎo)姓名和聯(lián)系電話合并便于調(diào)用。

圖5

在“姓名電話合并”列用連接符&把姓名和聯(lián)系電話合并起來(lái)即可，方法很簡(jiǎn)單，在單元格錄入：=B3&E3，回車(chē)后往下復(fù)制填充就可以了，當(dāng)有新員工的時(shí)候，只需要復(fù)制最后一行，粘貼修改數(shù)據(jù)進(jìn)行增加。

三、排班表

以上兩個(gè)表格都維護(hù)好以后，就需要在“排班表”里進(jìn)行調(diào)用，以下是效果圖。

圖6

從上圖可以看到，表格左側(cè)有設(shè)置是否上班和日期設(shè)置，這里為輔助列，方便設(shè)置日數(shù)和顏色顯示，只需要設(shè)置“排班表”打印區(qū)域即可。

第一步：設(shè)置日期

在第一列第一個(gè)日期單元格輸入函數(shù)：=DATE($P$3,$P$4,ROW(A1))，然后往下拖動(dòng)到需要的位置，在第二列第一個(gè)日期單元格輸入函數(shù)：=DATE($P$3,$P$4,ROW(A17))，拖動(dòng)到合適的位置，這里值得注意的是，ROW(A17)表示從第17個(gè)日期開(kāi)始，因?yàn)槲覀兊谝涣凶詈笠粋€(gè)日期是16日。這時(shí)我們發(fā)現(xiàn)單元格顯示了完整日期，我們只想顯示到單日，選中需要設(shè)置的單元格，單擊鼠標(biāo)右鍵，打開(kāi)“設(shè)置單元格格式”對(duì)話框，在“數(shù)字”功能組找到“自定義”，在右側(cè)“類(lèi)型”下方文本框輸入“d日”，表示天數(shù)，確定后就得到我們想要的效果。如下圖：

圖7

關(guān)于星期的函數(shù)，上述已經(jīng)講過(guò)，用函數(shù)=TEXT(A3,"aaaa")就能顯示星期。

第二步：數(shù)據(jù)有效性

設(shè)置好日期后，我們需要把值班人員信息和帶班領(lǐng)導(dǎo)、駕駛員的信息調(diào)用過(guò)來(lái)，這時(shí)候就該數(shù)據(jù)有效性出場(chǎng)了。選擇“值班人及電話”列需要調(diào)用數(shù)據(jù)的單元格，在“數(shù)據(jù)”選項(xiàng)卡下找到“有效性”，打開(kāi)對(duì)話框，在“設(shè)置”功能組“有效性條件”—“允許”選擇“序列”，其他默認(rèn)，在“來(lái)源”下方輸入：=基本信息!$F$10:$F$20（表示需要值班的人員區(qū)域），或者點(diǎn)擊右邊圖標(biāo)選擇區(qū)域，然后確定，帶班領(lǐng)導(dǎo)和駕駛員設(shè)置方法一樣，完善后就可以選擇排班了。

圖8

第三步：調(diào)用是否上班數(shù)據(jù)

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

圖9

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

我們都知道，瑞年的2月有28天，平年的2月有29天，此時(shí)就需要增加輔助列判斷當(dāng)月有多少天，然后用條件格式把字體顯示為白色（因?yàn)楸尘盀榘咨O(shè)置字體為白色后就看不到內(nèi)容）。

在對(duì)應(yīng)每個(gè)月29日的單元格輸入函數(shù)：

=IF(MONTH(F15)<>$P$4,"不同月","")，用MONTH(F15)計(jì)算當(dāng)月的月份數(shù)，然后用IF函數(shù)判斷是否和當(dāng)前月一致，如果不一樣則顯示“不同月”，否則留空，然后往下拖動(dòng)復(fù)制到31日的位置。

圖10

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

選擇1日單元格，在“開(kāi)始”選項(xiàng)卡下找到“條件格式”—“新建規(guī)則”—“使用公式確定要設(shè)置格式的單元格”，在下方文本框輸入函數(shù)：=$K3="否"，點(diǎn)擊“格式”—“字體”—“顏色”，選擇紅色（或其他顏色），然后點(diǎn)擊“確定”返回。保持選中1日單元格，單擊“格式刷”，然后按住鼠標(biāo)左鍵拖動(dòng)到“值班駕駛員”這一列最后一行后松開(kāi)，即可設(shè)置休息日紅色顯示。

17日后面的設(shè)置方法和上面方法一樣，在剛才的文本框里輸入=$L3="否"。

最后，在29日單元格按照上面的方法設(shè)置字體顏色為白色，函數(shù)為：=$M15="不同月"

圖11

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