在日常工作中，我們每月都安排員工值班，包括周末和休息日，那么如何使用EXCEL創(chuàng)建簡單方便的分配系統(tǒng)？(大衛(wèi)亞設(shè))。

下圖是日程表的最終效果。當(dāng)然可以美化表格。只需預(yù)設(shè)員工基本信息、公共假日等數(shù)據(jù)，只需單擊一下即可快速安排。

圖1

系統(tǒng)共有三個表，第一個表是“設(shè)置假日和參數(shù)”，主要設(shè)置年度節(jié)假日，并使用函數(shù)確定是否需要上班。第二張表是“員工基本信息”，以員工姓名、部門、職務(wù)、聯(lián)系方式等為主要內(nèi)容。第三張表是“日程”，主要設(shè)置一個月的日程，休息日以紅色字體自動顯示(圖1)。

讓我依次說明每個表格的用法。

一、假期和參數(shù)設(shè)置

將儲存格指定為年度的呼叫資料儲存格，然后根據(jù)年度產(chǎn)生日期，如圖2所示。通過指定單元格D2輸入年，然后使用DATE時間函數(shù)在單元格A4調(diào)用單元格D2的年中每天生成日期。

圖2

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

直接在單元格中輸入函數(shù)(如=DATE(2021，9，1))時，顯示的內(nèi)容為2021-9-1。使用DATE函數(shù)特性，可以生成一年或一年以上的日期。以一年的日期為例。

在儲存格A4中，輸入=DATE(D2，1，ROW(A1))函數(shù)

D2是2021年調(diào)用(根據(jù)需要可以寫其他年)，1表示1月至12月1月向下填充，ROW是行，表示一個月中1日至30日的一天，A1表示第一行，向下拖動A2 A3 A4……。加1到下個月1日為止。第一個日期第一個輸入函數(shù)回車后，向下填充一年的數(shù)據(jù)即可。同時，我們要知道哪一天是星期幾，如圖3所示，在單元格B4中輸入函數(shù)=TEXT(A4，' aaaa ')就可以表示星期。

圖3

“假期和節(jié)氣”列是可選的。設(shè)置此列的目的是為了便于確認(rèn)是否為國家法定節(jié)假日。因此，為了判斷是休息還是上班，每年都要根據(jù)實(shí)際情況手動輸入。(David Assell，Northern Exposure(美國電視劇)，季節(jié)名言)國家法定假日可能包括周末和周末補(bǔ)班問題，因此，設(shè)置“休息和上班”欄目的主要目的是輕松判斷是否上班，在“日程安排”中保持?jǐn)?shù)據(jù)時，只需保持節(jié)假日休息和周末補(bǔ)班即可。以上信息全部維護(hù)后，使用IF函數(shù)決定是否上班，調(diào)用“日程安排”后，必須以條件格式顯示休息日，顯示紅色(或根據(jù)不同顏色、偏好)字體。

在「是否上班」欄的儲存格E4中，輸入=IF(AND(OR(B4='星期六'，B4='星期日')，D4 '半')、'否'、IF，函數(shù)

IF(AND(OR(B4='星期六'，B4='星期天')，D4 '班')，'否'是指星期六和星期天，不上班就不上班。IF(D4='休'，'否'，'是')是判斷是否是休息日。

圖4

假期和參數(shù)設(shè)置表的設(shè)置現(xiàn)在已完成。一些細(xì)心的朋友會發(fā)現(xiàn)這張表上還有一個“設(shè)定日期”。主要是方便選擇“日程”調(diào)用。

二、基本信息

這張票很容易理解。主要是領(lǐng)導(dǎo)和工作人員的基本信息，最后加上輔助欄，把領(lǐng)導(dǎo)的名字和聯(lián)系方式加在一起，很容易調(diào)用。

mp;_iz=31825&index=4" width="640" height="368"/>

圖5

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

三、排班表

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

圖6

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

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

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

圖7

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

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

設(shè)置好日期后，我們需要把值班人員信息和帶班領(lǐng)導(dǎo)、駕駛員的信息調(diào)用過來，這時候就該數(shù)據(jù)有效性出場了。選擇“值班人及電話”列需要調(diào)用數(shù)據(jù)的單元格，在“數(shù)據(jù)”選項卡下找到“有效性”，打開對話框，在“設(shè)置”功能組“有效性條件”—“允許”選擇“序列”，其他默認(rèn)，在“來源”下方輸入：=基本信息!$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ù)，對應(yīng)的兩列依次設(shè)置。

圖9

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

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

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

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

圖10

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

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

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

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

圖11

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