二维码 购物车
部落窝在线教育欢迎您!

由打卡记录一键生成考勤汇总表,加班迟到早退全统计好!(上)

 

作者:ITFANS来源:部落窝教育发布时间:2022-11-30 16:53:31点击:16623

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

如何快速将考勤打卡机导出的打卡记录表整理汇总成考勤统计表,让每位员工的出勤天数、迟到、早退、加班情况一目了然?你需要设计制作一个考勤汇总模板文档——有了它就可以一键生成需要的汇总统计表。本篇介绍如何绘制汇总表、如何整理打卡数据,位自动统计打下基础。

 

每到月底,让人抓狂的事情就很多……

比如每个月底最让小青头疼的就是公司的考勤表汇总。她是一家小公司的HR,每个月都要制作考勤汇总表。

公司使用的是指纹考勤机,月底虽然可以从机器里导出员工打卡记录文件(xlsx格式,每天一张工作表),但是机器导出的数据只有员工编号和对应的打卡时间。

原始数据:打卡记录

 

小青需要将每日打卡记录工作表中的原始数据,整理并汇总生成如下考勤汇总表,直接反映出员工的迟到早退、加班金额:


汇总表效果

对比打卡记录表和汇总表,小青需要做的事包括:

1.绘制每月的考勤汇总表

2.整理每张打卡记录,使其满足汇总表的需要

1)将员工编号与姓名对应,便于按姓名汇总;

2)根据打卡时间并结合公司考勤规则判断考勤状态:正常、迟到/早退、加班;将打卡时间与考勤状态组合成一个综合考勤值,如“08:56:39(正常)”;

3)判断打卡时间的归属时段:9点段(开始上班)、18点段(开始下班)、19点段(开始加班);

4)将每个人每天的综合考勤值,如08:56:39(正常),按打卡时间分别归属到对应的时段(9:0018:0019:00)下;

5)计算当日的出勤奖罚合计金额:迟到/早退罚款+加班奖励。(如果您的公司在汇总阶段不合计两者,可以分别列出迟到/早退罚款、加班奖励。)

 

 

3.将整理后的30张打卡记录数据汇总到汇总表中

 

因为每个月导出打卡记录数据非常多,又没有模板可用,所以月底时小青总会通宵加班干这事!

下面我们来设计汇总模板实现一键汇总,彻底告别加班。

汇总表绘制

Step1:新建并保存为xlsm格式

新建一个Excel文件,将sheet 1工作表重命名为“汇总表”。保存文件,命名为“汇总表模板”,格式为xlsmExcel启用宏的工作簿)。

之所以选择启用宏的格式,是因为后续我们要使用VBA代码。

 

Step2:自动填充日期数据

A1单元格输入“202211月考勤汇总表”字样,选中A1:G1,设置对齐方式为“跨列居中”,这样可以在视觉上实现合并单元格的效果。注意:202211月考勤汇总表”中年必须是四位数月必须是两位数,不足位数的在前面加0占位,如2月,就写成02月。

 

D2单元格输入公式“=MID($A$1,1,4)&MID($A$1,6,2)&"01"”生成与打卡记录表名称一样的日期。将公式向右填充到G2单元格,完成第一天日期数据的填充。

 

 

公式解析:

MID函数分别提取A1中前四位数字“2022”,第67位数字“11”,并使用“&”符号将字符“01”和它们连在一起组成“20221101”,表示第一天。

 

重要提醒:

汇总表中的日期与打卡记录表名称尽可能(非必须)保持一致!可能的话,应根据导出的打卡记录表名称样式来写公式生成日期。

 

 

譬如,打卡记录表名称是“2021-11-01”,则D2单元格公式=MID($A$1,1,4)&"-"&MID($A$1,6,2)&"-"&"01"

 

定位到H2单元格输入公式“=D2+1”,向右拖至K2单元格,完成“20221102”的填充。选中H2:K2区域,向右拖直到生成四个“20221131”为止。生成“20221131”是为了满足所有月的需要。

 

 

通过当前公式设置,如果A1中的年份和月份修改了,则第二行的日期会自动更改。

 

Step3:填充星期数据

下面根据第二行的日期,在第三行加上对应的星期数据。

D3输入公式“=TEXT(DATE(MID(D2,1,4),MID(D2,5,2),MID(D2,7,2)),"aaaa")”,向右填充。

 

 

公式解析:

MID函数分别从D2单元格中提取3组数字,然后用DATE函数将其转化为标准的年月日形式的日期,接着在外层套用TEXT函数获得日期对应的星期数。

 

Step4:填充第四行

第四行内容包括姓名、编号、当月扣款/加班、当日扣款/加班,以及三个时段9:0018:0019:00

 

 

Step5:输入员工姓名和编号

根据花名册录入员工姓名和编号。(后续各月可以根据员工离入职实际,增删、修改姓名和编号。)

 

 

保存文件。到此已经绘制好汇总表。在完成数据整理后,我们还会返回此表中进一步设置公式实现自动汇总。

建立打卡数据整理模板

Step1:新建整理模板表

继续在当前文件中工作。

新建一个名为整理模板表的工作表。接着打开打卡记录文件“202211.xlsx”,将“20221101”表的内容全选复制,粘贴到整理模板表中。关闭“202211.xlsx”文件。


 

 

Step2:建立整理后表格样式

I:M处建立整理后的表格样式。C:H列暂时空着便于后续建立辅助列。

定位到I2单元格输入公式“=IFERROR(汇总表!A5,"")”并下拉填充(下拉填充行数应该超过当前最大员工数20%及以上,以应对今后可能的员工人数增长,增强模板的适用性),将“汇总表”中的所有员工姓名获取过来。

 

 

Step3:将编号和员工姓名对应

C列创建姓名辅助列。

位到C2单元格输入公式“=LOOKUP(1,0/(汇总表!B$5:B$200=A2),汇总表!$A$5:$A$200)&""”,下拉公式完成编号和人名的对应。

 

 

重要提醒:

公式下拉填充行数应该比平常单日考勤最大行数多20%及以上,增强模板的适用性。切记!切记!切记!譬如,日常单日最大考勤量是150行,这里就可以下拉填充到C200

文章后续提到的公式下拉填充都如此办理。

 

公式解析:

这是一个典型的LOOKUP精确查找套路公式。不理解的小伙伴可以查看文章《LOOKUP函数用法全解(上)——LOOKUP函数的5种用法

 

Step4:打卡时段归类

打卡时间需要归类在三个时间段中。(如果一天四次打卡,加上加班时段,则要归属为五个时间段。)

<18,归于9:00段(上午)

≥18点≤19点,归于18:00段(下午)

其他,归于19:00段(加班)

 

D列新建“归属时段”辅助列。

D2单元格输入公式并下拉填充:

=IFS(B2=TIME(18,0,0),B2<=TIME(19,0,0)),"18:00",B2>TIME(19,0,0),"19:00")

 

 

公式解析:

采用IFS函数进行多条件判断。函数公式=IFS(条件1, 1, [条件2, 2 ], [条件3, 3]...)。符合某个条件,就显示某个值。

如果读者的版本低不支持IFS函数,也可以使用IF函数,公式:


 

Step5:生成综合考勤值

E列建立“考勤状态”辅助列。在G列和H列建立时间段与状态对应列,方便查找。

 

 

时间段和状态对应列是根据考勤规则建立的:

0:00:009:00:00之间打卡,正常上班;

9:00:0117:59:59之间打卡, 迟到或者早退;

18:00:0019:00:00之间打卡,正常下班;

19:00:01及以后打卡,加班。

 

定位到E2单元格输入公式“=TEXT(B2,"hh:mm:ss")&"("&VLOOKUP(B2,$G$2:$H$5,2,1)&")"”,下拉公式生成打卡时间+考勤状态的综合考勤值。

 

 

公式解析:

TEXT函数将B2单元格的数值显示为“时分秒”样式的文本。使用VLOOKUP函数进行分区段模糊查找得到状态值。VLOOKUP分区段模糊查找可以看《多条件判断,劝你用VLOOKUP函数模糊查找取代IF函数的一长串公式!最后使用“&”将时间和状态以及括号连接起来。

 

Step6:计算当次打卡的扣款/加班补助

公司考勤规定:

打卡时间在19:00:01及以后的为加班;每小时加班补助10元,超过1小时不足2小时的,四舍五入到整数进行补贴。

迟到/早退则每次扣10元。(这里简化了,实际可能根据迟到或早退的时长不同,扣款金额不同。)

 

定位到F2单元格输入公式并下拉填充:

=IFS(VLOOKUP(B2,$G$2:$H$5,2,1)=$H$2,"",VLOOKUP(B2,$G$2:$H$5,2,1)=$H$3,-10,VLOOKUP(B2,$G$2:$H$5,2,1)=$H$5,ROUND((B2-TIME(19,0,1))*24,0)*10)

 

 

公式解析:

ROUND((B22-TIME(19,0,1))*24,0),用来获得四舍五入后的加班小时数。为何要乘以24?因为时间,如“18:00:02”,在Excel中的本质是一个以天数为单位的小数“0.7500231”,只有乘以24后才得到小时数。

Excel低版本读者也可以使用如下公式:

=IF(VLOOKUP(B2,$G$2:$H$5,2,1)=$H$3,-10,IF(VLOOKUP(B2,$G$2:$H$5,2,1)=$H$5,ROUND((B2-TIME(19,0,1))*24,0)*10,""))

如果公司的考勤规定迟到早退的时长不同扣款金额不同,则需要在公式中计算迟到或早退时长,然后套用Vlookup函数进行分区查找获得不同的扣款金额。

 

Step7:合计员工当天扣款/加班补助金额

定位到J2单元格输入公式并下拉填充:

=IF(SUMIF($C$2:$C$200,I2,$F$2:$F$200)<>0,SUMIF($C$2:$C$200,I2,$F$2:$F$200),"")

 

 

公式解析:

这里使用SUMIF函数,以$C$2:$C$200为条件区域,以I2的姓名作为条件,合计F列的金额。注意公式中的数据区域大小,如果公司有500人,每天打卡两次,很显然至少$C$2:$C$1001才合适。

外层嵌套IF函数的目的是让没有扣款/加班金额的显示为空。

如果不需要显示为空,则公式更简单,=SUMIF($C$2:$C$200,I2,$F$2:$F$200)

扩展需求

如果您的公司在汇总阶段不合计两者,可以分别列出当日迟到/早退罚款、当日加班奖励。当日迟到/早退罚款公式:

=IF(SUMIFS($F$2:$F$200,$C$2:$C$200,I2,$F$2:$F$200,"<0")<>0,SUMIFS($F$2:$F$200,$C$2:$C$200,I2,$F$2:$F$200,"<0"),"")

当日加班补贴公式:

=IF(SUMIFS($F$2:$F$200,$C$2:$C$200,I2,$F$2:$F$200,">0")<>0,SUMIFS($F$2:$F$200,$C$2:$C$200,I2,$F$2:$F$200,">0"),"")

 

 

Step8:提取综合考勤值

 

下面将综合考勤值归属到三个时段下。

定位到K2单元格输入数组公式按Ctrl+Shift+Enter 结束,然后向右向下填充:

=IFERROR(INDEX($E$1:$E$200,MATCH($I2&K$1,$C$1:$C$200&$D$1:$D$200,0)),"")

 

 

公式解析:

这是一个典型的INDEX嵌套MATCH函数引用数据的公式,不明白的可以看文章《INDEX:函数中的精确制导导弹,最强大的瘸子

 

保存文件。

至此,整理模板表已经完成。只要将表中C~M列内容粘贴到其他打卡记录表中,即可自动完成整理。

为了省事,我们用VBA代码来做这种批量复制粘贴操作。

 

Step9:用VBA代码批量处理其他打卡记录表

打开打卡记录文件“202211.xlsx”,接着切换到打开的“汇总表模板xlsm,按住Ctrl键依次选中“汇总表”和“整理模板表”,右击选择“移动或复制工作表”。

在弹出的“移动或复制工作表”对话框中,工作簿选择“202211.xlsx”;位置选“20221101”,表示将上述需要移动的两个工作表放置在此表之前;勾选下方“建立副本”。

 

 

 

单击“确定”,关闭对话框后,“汇总表”和“整理模板表”附加到当前的“202211.xlsx”文档中。接着另存文件,将文档保存为启用宏的“11月汇总表.xlsm”。

关闭“汇总表模板.xlsm”文件。

 

Alt+F11打开VBA编辑器,单击“插入→模块”命令,粘贴下列代码,生成一个名为“遍历工作表”的宏。

 

Sub 遍历工作表()

  '如果工作表名称不包含

For Each sh In Workbooks("11月汇总表.xlsm").Worksheets

If Not sh.Name Like "*" & "" & "*" Then  '如果工作表名称不包含

sh.Select

For i = 3 To ActiveWorkbook.Worksheets.Count

Sheets(2).Range("C1:M200").Copy ActiveWorkbook.Worksheets(i).Range("C1:M200")

Next

End If

Next

End Sub

 

 

代码解释

代码中使用“FOR EACH”语句来遍历工作簿,并排除名称中包含“表”字的工作表(即汇总表和整理模板表)。设置变量“i”等于3,表示从排位第3的工作表(即每月第1天的工作表)开始粘贴;粘贴的内容复制自排位第2的工作表(Sheets2),即整理模板表)的“C1:M200”区域。这里的数据区域大小需要根据前面公式实际填充来修改,譬如公式填充到1000行,则修改为“C1:M1000”

 

关闭VBA编辑窗口并保存文件。

然后单击“开发工具”菜单下“宏”按钮,在弹出的“宏”对话框中选择“遍历工作表”,单击“执行”,即可完成所有打卡表的自动整理。

宏对话框

 

自动整理后的“20221101”工作表

 

保存文件。

接下来需要做的是返回到“汇总表”工作表中进行公式设置,完成整个模板的搭建。由于篇幅原因,我们下篇再来给大家讲解。

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和Excel极速贯通班》播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

别怕,VBA入门级教程来了,条件语句很简单!

优秀员工组别查找?INDEXOFFSETLOOKUP……我有100个函数可以解决这个问题

Excel教程:如何制作带有层次和透视感的图表?

八大查找函数公式,轻松搞定数据中的多条件查找

版权申明:

本文作者ITfans;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。