又是月底。
HR小王打开考勤机导出的Excel，1500行打卡记录密密麻麻堆在眼前。她需要统计每个员工的迟到次数、早退次数、旷工天数、加班时长，然后汇总到一张月度考核表里。"小张这月迟到了几次？"领导发来微信。小王开始逐行翻：先找到小张的所有记录，数一数上班时间超过9:00的有多少次，再看下班时间早于18:00的有多少次……光一个人就花了5分钟，全公司40个人，加上反复核对，一下午就没了。更崩溃的是，第3天领导说："小王，你统计的张三月迟到次数，跟我看到的不一样。"小王心里一凉——手动数的，谁保证不出错？其实，这样的考勤统计，Excel完全能自动完成。 只要4个公式设置好，每月的考勤汇总表你只需要把打卡数据粘贴进去，迟到/早退/旷工/加班——一秒出结果，比手动快100倍，而且永远零出错。今天这篇，手把手教你搭建一套全自动员工考勤月度汇总表。
核心技巧
第1招：规范考勤数据录入——数据验证防误输
场景：考勤机导出的数据可能格式混乱，手动标注考勤状态更容易输错。先用数据验证给考勤状态列加下拉菜单，杜绝"迟到""矿工"这类低级错误。
步骤：
- 来源输入：
正常,迟到,早退,迟到+早退,旷工,事假,病假,加班,未签退
注意：来源里的逗号必须是英文半角逗号，中文全角逗号Excel不认。
第2招：上班迟到/下班早退自动判定——IF+TIME公式精准算
场景：打卡记录里只有时间（如"9:23""17:45"），需要自动判断员工是「正常」「迟到」还是「早退」。假设标准工时为9:00上班、18:00下班。
准备工作：先设置两个辅助列——H列用来判断迟到、I列用来判断早退。最后G列综合汇总这两个辅助列的结果。公式：
- 迟到判定（H列）：=IF(AND(D7<>"",D7>TIME(9,0,0)),"迟到","-")
- 早退判定（I列）：=IF(AND(E7<>"",E7<TIME(18,0,0)),"早退","-")
- 综合状态判定（G列）：=IF(AND(D7="",E7=""),"旷工",IF(AND(G7="迟到",H7="早退"),"迟到+早退",IF(G7="迟到","迟到",IF(I7="早退","早退","正常"))))
解读：
TIME(9,0,0) 表示9:00:00，Excel时间本质上就是小数（0.375=9:00），这就是为什么 E2>TIME(9,0,0) 能直接比较。AND(E2<>"",E2>TIME(9,0,0)) 先判断打卡记录不为空，再判是否迟到——避免空单元格被误判。- 综合状态公式的嵌套逻辑：优先判旷工（上下班都空），其次判迟到+早退同时发生，再单独判迟到或早退，最后正常。
兼容性：WPS和Excel均支持，Office 2007及以上版本可用。
踩坑：第一次做这个考勤表时，我以为只要 E2>TIME(9,0,0) 就够了，结果空行全部被判为"迟到"——因为空值在Excel里与任何数值比较都返回TRUE。加 AND(E2<>"",...) 这个条件后问题才解决。
第3招：COUNTIFS一键统计各类考勤——按人汇总月度数据
场景：打卡记录表是流水账（每天N行），需要按人汇总成一目了然的月度考勤表。
假设数据结构：
- Sheet「月度汇总」：A列姓名、B列迟到次数、C列早退次数、D列旷工天数、E列事假天数、F列病假天数、G列加班次数
- 迟到次数： ``=COUNTIFS('第2招-IF+TIME判定迟到早退'!B7:B7,A7,'第2招-IF+TIME判定迟到早退'!F7:F7,"迟到")
2. 早退次数：=COUNTIFS('第2招-IF+TIME判定迟到早退'!B7:B7,A7,'第2招-IF+TIME判定迟到早退'!F7:F7,"*早退*")
3. 旷工天数：
=COUNTIFS('第2招-IF+TIME判定迟到早退'!B7:B7,A7,'第2招-IF+TIME判定迟到早退'!F7:F7,"*旷工*")
4.事假天数：
=COUNTIFS('第2招-IF+TIME判定迟到早退'!B7:B7,A7,'第2招-IF+TIME判定迟到早退'!F7:F7,"*事假*")
- 加班次数：
=COUNTIFS('第2招-IF+TIME判定迟到早退'!B7:B7,A7,'第2招-IF+TIME判定迟到早退'!F7:F7,"*加班*")
解读：
COUNTIFS 是多条件计数，这里两个条件：①姓名=B列某人 ②状态=指定类型- 注意迟到和早退用了通配符：
"*迟到*" 中的星号 * 代表任意字符，这样既能匹配到"迟到"也能匹配到"迟到+早退"，不会漏掉同时触犯两条的员工。
兼容性：COUNTIFS要求Excel 2007+ / WPS 2016+。更早版本可用SUMPRODUCT替代。
踩坑：最开始我用了 =COUNTIFS(打卡记录!$B$2:$B$1500,A2,打卡记录!$G$2:$G$1500,"迟到")，固定范围看似"严谨"，但下月数据如果超过1500行，后面的人就统计不到了。改成整列引用 打卡记录!B:B 后，数据再多也不怕。
第4招：条件格式整行变色——异常考勤一眼看到
场景：月度汇总表里数字干巴巴的，领导扫一眼根本看不出谁有问题。用条件格式让异常数据自动标色：迟到≥3次标红、旷工≥1天标深红、全勤标绿。
步骤：
- 选中月度汇总表数据区域（A2:G50）
旷工≥1天整行标深红：
解读：
$B2 中，B列绝对引用（$B），行号相对（2）——条件格式应用到第N行时，实际判断的是 $BN，从而实现整行变色。- 公式顺序要注意：旷工规则放在迟到规则上面（条件格式规则管理器里拖拽排序），因为旷工人一般也满足迟到条件，规则按从上到下优先级执行。
踩坑：第一次设条件格式时，我写了 =$B$2>=3，结果所有行都变成红色——$B$2 锁定在第2行，所有行都在判断第2行≥3。写成 =$B2>=3（列锁行不锁）才对。这也是我在之前的文章里反复强调的条件格式核心规则。
进阶联动：考勤数据 + 筛选 + 排序，打造动态预警看板
现在你有了一个完整的考勤汇总系统：
- Sheet「打卡记录」
- Sheet「月度汇总」：COUNTIFS公式实时统计，条件格式自动变色 更进一步，你可以：
- 筛选：在月度汇总表按「旷工天数」筛选≥1的行，一键列出所有旷工人员
- 排序
- 数据验证 + 条件格式联动：在汇总表加一个"部门"列，再给部门加下拉菜单和条件格式，同部门考勤对比只消3秒
把这个Excel发给领导，他打开就能看到：红色的人要找谈话、深红色的要发警告——不用你多解释半个字。
高频场景
- HR月底做考勤汇总：把打卡机数据粘贴到「打卡记录」，5秒看到全员工考勤。
- 行政每月出考勤报表
- 新员工入职做考勤培训：用这个模板给新员工看，"迟到3次以上就是红色，自己看着办"。
避坑指南
- 空行被误判为迟到：用
AND(E2<>"",E2>TIME(9,0,0)) 先判非空再判时间，不要只用 E2>TIME(9,0,0)。 - COUNTIFS整列引用卡顿：如果打卡记录超过5万行，
B:B 整列引用会让Excel变慢。超过5万行时改用 B$2:B$65536 或升级到Excel 64位版。 - TIME函数引号问题：
TIME(9,0,0) 不是 TIME("9","0","0")，参数是数字不是文本，写成文本会报错 #VALUE!。 - 条件格式规则顺序错误：旷工规则必须放在迟到规则上面（先判断更严重的），否则旷工的人只会显示迟到色。
- 跨月数据混乱：打卡记录如果有跨月数据，COUNTIFS只按姓名+状态统计，不区分月份。建议每月新建一个打卡记录Sheet，命名如「打卡记录-5月」。
这套全自动考勤汇总表模板我已经做好了：打卡记录公式预设，月度汇总COUNTIFS公式全部到位，条件格式规则也配好了。你只需要把打卡数据粘贴进去就行。
获取方式：关注本公众号，在后台回复关键词【资料】，这套Excel考勤汇总模板直接发给你。
你们公司考勤是用考勤机自动算，还是HR手动统计？留言区聊聊，点赞最高的3条，我送你一套进阶版「全勤奖自动计算模板」～
10个月宝宝每天需要喝多少奶粉？
