Excel函数在排班、考勤管理中的应用

Excel函数在排班、考勤管理中的应用

徐敬轩1夏毅1彭文红1储磊2袁支胜1

徐敬轩1夏毅1彭文红1储磊2袁支胜1

（1丹阳市第二人民医院检验科江苏镇江212300）

（2丹阳市人民医院检验科江苏镇江212300）

【摘要】Excel具有丰富的公式和函数库，可以实现公式和函数的自动填充。该文通过对VLOOKUP函数、COUNTIF函数、SUMPRODUCT函数、SUMIF等函数的使用来实现排班表的数据自动专到考勤表并实现各个参数的自动计算，同时也可用于医院总的考勤管理。

【关键词】Excel；SUMPRODUCT函数；VLOOKUP函数；COUNTIF函数；排班表；考勤表

【中图分类号】R197.3【文献标识码】A【文章编号】2095-1752（2016）25-0039-03

ApplicationofExcelfunctioninscheduling,attendancemanagementXuJingxuan,XiaYi,PengWenhong,YuanZhisheng.

DepartmentofLaboratoryMedicine,TheSecondHospitalofDanyangCity,JiangsuProvince,Zhenjiang212300,China;ChuLei.DepartmentofLaboratoryMedicine,ThePeople'sHospitalofDanyangCity,JiangsuProvince,Zhenjiang212300,China

【Abstract】Excelhasrichformulaandfunctionlibrary,automaticfillingcanbeachievedformulasandfunctions.Inthispaper,byusingtheVLOOKUPfunction,COUNTIFfunction,SUMPRODUCTfunction,SUMIFfunctiontoachievetheschedulingtabledataautomaticallydesignedtocalculatetheparametersautomaticallytoachievetheattendancesheet,atthesametimecanalsobeusedforgeneralattendancemanagementinhospital.

【Keywords】Excel;SUMPRODUCTfunction;VLOOKUPfunction;COUNTIFfunction;Scheduling;Attendancesheet

本文通过排班表的制作、考勤表的计算来体验一下Excel强大功能。我们科排班是每周一排，月末汇总成考勤表送交院部，制作过程如下。

1.排班表的制作

1.1搭框架

插入15张表格依次命名为“总”、“排班”、“月统统计”、“01月”、“02月”到“12月”如图1,每个工作表中设置五个周排班表,一个汇总表如图2。为了随时把新人添到排班表中，每个周工作表可同时派84人,先排现有人，其他可以借助阿拉伯字母依次往下拉如图1，每个表格占100行。

图1图2

1.2全年日期输入

在“排班”工作表A31单元格中输入“2013”，在C31单元格中输入“月检验科排班表（一）”在C32单元格中输入“月检验科排班表（二）”依次输入，在A41-A64输入“01”、“1”、“02”、“2”直到“012”、“12”。其中“01”、“1”都代表1月。在B40-AJ40分别输入一、二、至日代表周一到周天，共输入5周。在B39输入2，C39输入3，右拉填充柄至36。在B41输入“7”代表1月的第一个星期一对应的日期，在B42输入“=DATE($A$31,A42,B41)”表示2013-1-7在Excel工作表中对应的数字。在C41输入“=B41+1”然后右拉至AJ41，在C42输入“=B42+1”然后右拉至AJ42，在1月的第四周本月31日输后本周还有三天，应接着输入2月的日期直到本周结束，在第五周就不要输入日期。其他月份也如此。如图3

1.3每月日期输入

在1月工作表的AM1输入“1”、AM2输入“01”，在2月工作表的AM1输入“2”、AM2输入“02”依次类推。同时选种12个月的工作表在B4单元格输入“=VLOOKUP($AM$1,排班!$A$41:$AJ$64,排班!B39,FALSE)”在B5单元格输入“=VLOOKUP($AM$2,排班!$A$41:$AJ$64,排班!B39,FALSE)”测出现每月的第一星期一对应的日期以及日期所对应的数字。选种这两格然后向右填充得到本周的其他日期，同样可以制作其他四周所对应的日期。在A1合并后的单元格输入=CONCATENATE(排班!A31,"年",AM2,排班!C31)则单元格出现“2013年1月检验科排班表（一）”同理可制其他四周的。第二个周的排班表在相差100行的行输入=A6，然后往下拉填充柄这样姓名都依次出现，同样处理其余的周表与汇总表。在A602输入“应出勤天数”，在A604输入“本月假期”，D602输入“天”，D604输入“天”如图2。每月的工作表有5个周工作表，但有时第5个周工作表不需要，为了不影响美观有不影响下一年用，可以使用条件格式，如果今年利用就显示出来，如果不用就显示为其他颜色。

图3图4

1.4考勤记录表的制作

在月统计中设置12个考勤记录表，一个公休汇总表，在一月记录表姓名列输入“='01月'!A6”下拉得到其他人员的姓名，在二月的表格输入“='02月'!A6”，依次做好其他表格。在1月的考勤记录表的B10到AF10分别输入1月的日期，B9输入“=DATE(排班!$A$31,1,B10)”即显示2013年1月1号1对应的数字为41275，右拉的到本月其他天对应的日期。同理可制其他几个月日期。

1.5考勤的明细

在“排班”的工作表设置，在第一行分别输入“下拉”、“出勤”、“休”、“加班”、“夜班”、“公休”等。在“下拉”列输入科室可能出现的排班，如果出现的班次比较少，其他单元格可输入A、B、C等字母。在其他列分别输入对应数值如“白/休”算出勤0.5天，休0.5天，其余分别为0天，这些应根据自己科室的情况输入。在I2输入2，I3输入3，下拉填充柄至26，如图4。

1.6下拉表的制作[1]

同时选重12个月的工作表在AM3单元格输入“=HLOOKUP(排班!$A$1,排班!$A$1:$A$26,排班!I2)”然后下拉填充柄，这里产生的数据与“排班”工作表中下来的数据一致并且随它的改变而改变。如：在12月的工作表中选择B5至H97表格区域，点击菜单数据-有效性,在出现的对话框内把允许下的下拉框中的任何值改为序列，点击来源下方右侧选择键，用鼠标选定AM列中填写班次的单元格后，再次点击选择键确定，或直接输入“=$AM$3:$AM$27”。完成操作后，点击B5至H97区域中任意一单元格，即可选择排班，不用手工输入。如图5。依次制作其他区域的下拉表。其他月的工作表也如此制作。

图5图6

2.考勤表的自动生成

2.1考勤的汇总

把每日每人的出勤情况汇总到“总”工作表下，在A1输入“.”在A2输入“=CONCATENATE('01月'!A6,总!$A$1,'01月'!$B$5)”在B2输入“=VLOOKUP('01月'!A6,'01月'!$A$6:$H$97,2,FALSE)”。在A2、B2中的意义是“刘邦.41281”在行为“刘邦”列为“41281”对应出勤情况为“白”选种A2B2下拉得到其他人在41281在一天对应的出勤情况，同理可得到其他人在其他天对应的出勤情况如图6。这个过程制作需要大部分时间。

2.2考勤表自动生成

如1月份的考勤表自动生成，在B11输入“=IF(ISERROR(VLOOKUP(CONCATENATE($A11,$A$1,B$9),总!$A$2:$B$3221,2,FALSE)),0,VLOOKUP(CONCATENATE($A11,$A$1,B$9),总!$A$2:$B$3221,2,FALSE))”意思是行为“刘邦”列为“41275”对应出勤情况如果找不到对应的日期就返回“0”，右拉到得到其他日期的出勤情况，然后下拉得到其他人的本月出勤情况如图7。在2月份考勤表只需要把“总!$A$2:$B$3221”换成“总!$A$1934:$B$6441”，在3月份换成“总!$A$5154:$B$9661”以后每月在上月的开始行与结束行加3220即可。

图7图8

3.排班情况的自动计算

3.1考勤明细的复制

在月AS8到BQ8分别依次输入2到26。利用HLOOKUP函数，在AS9输入“=HLOOKUP(排班!$A$1,排班!$A$1:$A$26,月统计!AS8,FALSE)”。然后向右填充，这样把“排班”工作表下拉后的内容就复制到“月统计”里，同理用VLOOKUP函数以AS9到BQ9中的内容为要搜索的值把排班!$B$2:$F$26的内容复制到“月统计”中的$AS$1:$BQ$5，如图8中的兰色区域此区域为每种排班情况所对应的“出勤”、“休”、“加班”、“夜班”、“公休”的各自天数。

3.2“应出勤天数”的计算

为了监督计算出错如图2，通过VLOOKUP函数将每月考勤汇总的的数据复制的次表格中，“总计”将“白班”天数与“休息”天数相加，如果超过本月天数为加班，如果低于次天数可能计算有误可重新检查表格，“应出勤”等于本月应出勤天数+节假上班-本月公休假。如2月份的“应出勤天数”在AH111输入=VLOOKUP($A111,'02月'!$A$505:$I$597,8,FALSE)，下拉填充柄得到其他人的，2月份每人的应出勤就到这里。“当月余休”等于“实际出勤天数”-“应出勤天数”。“累计积休”=“上月累计积休”+“当月余休”。

3.3“累计公休”的计算

通过SUMIF函数来完成，在B1211单元格中输入=SUMIF($A$11:$AR$1195,$A1211,$AQ$11:$AQ$1195)，下拉填充柄来实现其他人员的计算。

3.4每周休息的计算

为了排班方便看到每人在本周休息了几天，可以通过HLOOKUP、VLOOKUP、COUNTIF、SUMPRODUCT这四个函数来完成。具体算法就不多说了。

4.体会

4.1本表具有通用性，只需开始用时根据各科室的排班情况把图4的排班类型，以及相对应的天数填好即可不需要改动其他的公式。可用于法定节假上班的计算时，如图3把“白”换成“白1”在“加班”列输入“1”即可。也可用于额外加班的计算，如果本天白班另外加班两小时可输入“白0.25”在出勤列输入“1.25”。意思是本天算上班1.25天。在计算时会自动计算加班的天数。根据科室的需要可以计算你所需要的。从一年到下一年排班表的转换，只需要把“排班”中的年份改一下，在“每月的日期输入”中把本月的第一个星期一对应的日期输入，稍微修改即可，不需要重新设计。

【参考文献】

[1]陈皓雯.Excel在护理排班中的应用[J].中国护理管理,2010,10(9):65-67.

[2]楼许柏，楼芳.通用型电子排班表核对系统的建立[J].医疗装备,2012,25(6):12-13.