【excel怎么算排班】用excel创建公司调度系统

在日常工作中，我们每月都安排员工值班，包括周末和休息日，那么如何使用EXCEL创建简单方便的分配系统？(大卫亚设)。

下图是日程表的最终效果。当然可以美化表格。只需预设员工基本信息、公共假日等数据，只需单击一下即可快速安排。

图1

系统共有三个表，第一个表是“设置假日和参数”，主要设置年度节假日，并使用函数确定是否需要上班。第二张表是“员工基本信息”，以员工姓名、部门、职务、联系方式等为主要内容。第三张表是“日程”，主要设置一个月的日程，休息日以红色字体自动显示(图1)。

让我依次说明每个表格的用法。

一、假期和参数设置

将储存格指定为年度的呼叫资料储存格，然后根据年度产生日期，如图2所示。通过指定单元格D2输入年，然后使用DATE时间函数在单元格A4调用单元格D2的年中每天生成日期。

图2

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日为止。第一个日期第一个输入函数回车后，向下填充一年的数据即可。同时，我们要知道哪一天是星期几，如图3所示，在单元格B4中输入函数=TEXT(A4，' aaaa ')就可以表示星期。

图3

“假期和节气”列是可选的。设置此列的目的是为了便于确认是否为国家法定节假日。因此，为了判断是休息还是上班，每年都要根据实际情况手动输入。(David Assell，Northern Exposure(美国电视剧)，季节名言)国家法定假日可能包括周末和周末补班问题，因此，设置“休息和上班”栏目的主要目的是轻松判断是否上班，在“日程安排”中保持数据时，只需保持节假日休息和周末补班即可。以上信息全部维护后，使用IF函数决定是否上班，调用“日程安排”后，必须以条件格式显示休息日，显示红色(或根据不同颜色、偏好)字体。

在「是否上班」栏的储存格E4中，输入=IF(AND(OR(B4='星期六'，B4='星期日')，D4 '半')、'否'、IF，函数

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

图4

假期和参数设置表的设置现在已完成。一些细心的朋友会发现这张表上还有一个“设定日期”。主要是方便选择“日程”调用。

二、基本信息

这张票很容易理解。主要是领导和工作人员的基本信息，最后加上辅助栏，把领导的名字和联系方式加在一起，很容易调用。

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

图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

至此，我们的操作基本完成，下面设置一下条件格式显示休息日为红色就大功告成。