打开“11月汇总表.xlsm”,找到“汇总表”工作表。
从D5开始选择D列,设置格式为“货币”。
在D5中输入公式“=IFERROR(INDIRECT(D$2&"!J"&ROW(A2)),"")”并向下填充。
公式解析:
用“&”将D2单元格、叹号!、字母J和ROW(A2)连接起来,形成“20221001!J2”字符,作为INDIRECT函数的引用地址,实现对“20221001”工作表J2单元格数据的引用。公式中ROW(A2)函数下拉后会自动变为ROW(A3)、ROW(A4)……,可以依次引用“20221001”工作表J3、J4……单元格的数据。
在E5单元格输入公式“=IFERROR(INDIRECT(E$2&"!K"&ROW(A2)),"")”;
在F5单元格输入公式“=IFERROR(INDIRECT(F$2&"!L"&ROW(B2)),"")”;
在G5单元格输入公式“=IFERROR(INDIRECT(G$2&"!M"&ROW(E2)),"")”;
都下拉填充,实现对“20221101”工作表的K、L、M列数据的引用。
再选中D5:G28区域,向右进行填充,依次完成对“20221102”、“20221103”……工作表的数据引用。
Step2:计算员工当月扣款/加班数据
从C5单元格开始向下选中C列,设置单元格格式为“货币”。
在C5单元格输入公式“=SUMIF($D$4:$DW$4,$D$4,D5:DW5)”,然后下拉填充完成统计。
至此,完成11月考勤数据的汇总。
由于迟到/早退、加班数据敏感性高,所以有需要的话,可以使用条件格式使其突出显示。
Step3:突出显示迟到/早退、加班数据(非必须)
选中E5单元格新建两则条件格式。
在“新建格式规则”对话框中,“选择规则类型”均选择“只为包含以下内容的单元格设置格式”,然后在“编辑规则说明”中选择“特定文本”和“包含”,并分别输入“加班”“迟到”字样。最后单击“格式”设置不同的填充颜色。
第一则,针对加班的:
第二则,针对迟到早退的:
用格式刷将E5单元格的条件格式应用到其他单元格中。最终效果如下。
保存文档(但不要关闭)。
二、继续完成汇总表模板
Step1:删除多余的打卡记录表
删除“11月汇总表.xlsm”文档中除“汇总表”“整理模板表”外的所有工作表。
Step2:覆盖前方保存的汇总表模板文件
执行“文件→另存为”菜单命令,在“另存为”对话框中选择前方保存的“汇总表模板.xlsm”文件,然后单击“保存”按钮进行覆盖。
汇总表模板文件制作完成。
补充说明:
在实际工作中,考勤汇总表还会涉及到出差、旷工、请假等没有打卡的数据。这些只要根据实际情况,在汇总表上增加列项目手动添加数据即可。
三、汇总表模板的使用
模板建立好了,以后就可以利用模板快速汇总各月的考勤数据了。
以2022年12月考勤为例。
第一步:打开“汇总表模板.xlsm”和“202212.xlsx”工作簿。将“汇总表模板.xlsm”的“汇总表”和“整理模板表”添加到“202212.xlsx”工作簿,并确保它们分别排在第1、第2位。
第二步:修改“202212.xlsx”中“汇总表”A1单元格标题,把“11月”改成“12月”。
第三步:根据实际情况增删、修改“汇总表”中的员工姓名和编号。
第四步:切换到“汇总表模板.xlsm”窗口,按下Alt+F11打开VBA编辑器,复制右侧的VBA代码。
复制后,关闭“汇总表模板.xlsm”文件。
第五步:在左侧的工程对话框单击“202212.xlsx”,然后执行“插入→模块”命令,添加“模块1”,并在右侧粘贴代码。修改代码中的文件名称,将“11月汇总表.xlsm”改成“12月汇总表.xlsm”。
第六步:另存“202212.xlsx”文档,选择文件格式为“Excel启用宏的工作簿”,设置名称为“12月汇总表.xlsm”。该名称与上一步骤代码中的名称保持一致。
第七步:单击“开发工具”菜单下“宏”按钮,在弹出的“宏”对话框中选择“遍历工作表”,单击“执行”。
Ok,大功告成!保存文档完成12月考勤汇总。
怎么样?利用模板,只需7步操作,一两分钟搞定所有汇总。
您是不是也正在为汇总考勤表而烦恼?那么就好好学习一下上面的教程吧。
做Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!
别怕,VBA入门级教程来了,条件语句很简单!
优秀员工组别查找?INDEX、OFFSET、LOOKUP……我有100个函数可以解决这个问题
Excel教程:如何制作带有层次和透视感的图表?
八大查找函数公式,轻松搞定数据中的多条件查找
本文作者ITfans;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。