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

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

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

1）将员工编号与姓名对应，便于按姓名汇总；

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

3）判断打卡时间的归属时段：9点段（开始上班）、18点段（开始下班）、19点段（开始加班）；

4）将每个人每天的综合考勤值，如08:56:39（正常），按打卡时间分别归属到对应的时段（9:00、18:00、19:00）下；

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

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

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

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

汇总表绘制

Step1：新建并保存为xlsm格式

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

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

Step2：自动填充日期数据

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

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

公式解析：

用MID函数分别提取A1中前四位数字“2022”，第6、7位数字“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:00、18:00、19: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：打卡时段归类

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