有朋友提到说小编之前制作的考勤表很实用,能不能制作一份排班计划表?小编在这里分享一个简单的制作,如有不妥,欢迎指正!
一、设置排班表的动态标题
①定义名称:选择月份的区域——点击公式——名称管理器——新建——输入名称点击确定
②设置数据(月份)有效性:点击数据——有效性——允许下拉为序列——在来源处输入=月份(上步定义的名称)
PS:年份的有效性也可以手动输入或者是通过开发工具下的控件来设置
③转化日期格式:在E3中输入=TEXT(DATE($F$2,$I$2,COLUMN(A1)),"e年m月")
二、输入动态的日期和星期
1、 在F3中输入日期填充公式
=IF(COLUMN()-5<=DAY(EOMONTH($E$3,0)),DATE(YEAR($E$3),MONTH($E$3),COLUMN()-5),"")
2、 在F4中输入星期公式
=IF(F3="","",TEXT(WEEKDAY(F3,1),"aaa"))
三、 正式排班:以四班三倒为例
1、 在F5中输入A班的公式=IF(F3="","",INDEX($A$2:$A$9,MOD(F3+7,8)+1,1))
B班的公式=IF(F3="","",INDEX($A$2:$A$9,MOD(F3+9,8)+1,1))
C班公式=IF(F3="","",INDEX($A$2:$A$9,MOD(F3+11,8)+1,1))
D班公式=IF(F3="","",INDEX($A$2:$A$9,MOD(F3+13,8)+1,1))
解释:首先是index函数语法=index(区域,行,列)A班中MOD(F3+7,8)+1:日期加上7天,mod对8的求余数,再加一天返回A列的行数1:是A列中固定的一列
2、 统计各个班次的实际应出勤情况(如果有考勤数据可以和应到出勤作对比)
在AK5中输入早班出勤公式=COUNTIF($F5:$AJ5,AK$4)输完公式填充即可
需要注意的是:各个单元格的引用方式,使用F4键进行切换就可以
3、 设置更醒目的颜色
①选择F3:AJ4区域,点击条件格式——新建规则——使用公式确定单元格的规则——输入公式=OR(F$4="六",F$4="日")后再点击格式——选择一个颜色——确定即可
②排班区域内的格式设置如上,可参考如下动图演示
四、转化日历排版表简单的日历制作
1、在D2中输入公式
=IFERROR(IF($J$1="A班",VLOOKUP($J$1,A班,ROW(A2),),IF($J$1="B班",VLOOKUP($J$1,B班,ROW(A2),0),IF($J$1="C班",VLOOKUP($J$1,C班,ROW(A2),),VLOOKUP($J$1,D班,ROW(A2),)))),"")
解释:①首先判断J1单元格是哪个班次的,再使用查找函数,需要注意的是:每到新月份的时候就要更改vlookup函数的第三参数:返回列数
②公式中的A/B/C/D班是小编自定义的名称,作为VLOOKUP函数的第二参数,即查找区域
2、日历表中引用排班:在H5中输入=IFERROR(VLOOKUP(H4,$B:$E,4,0),"")填充即可