编按：在上一期的文章《由打卡记录一键生成考勤汇总表，加班迟到早退全统计好！（上）》中，主要介绍了如何对打卡记录表的数据进行整理。在这一期中，我们介绍如何在汇总表模板中添加公式、如何运用模板，以便实现自动汇总。

哈喽，大家好。

今天继续来给大家讲解：如何在汇总表模板中添加公式、运用模板，最后实现自动汇总。

一、汇总表设置

Step1：引用打卡表中归类好的数据

打开“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，让我们一起对加班说“不”！

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

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

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

相关推荐：

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

优秀员工组别查找？INDEX、OFFSET、LOOKUP……我有100个函数可以解决这个问题

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

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

版权申明：

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