透视表轻松统计考勤与迟到早退

本文详细介绍了如何利用Excel数据透视表高效完成考勤统计与迟到早退汇总，从原始打卡数据清洗（通过Power Query转为标准一维格式）、构建透视表自动提取每日最早/最晚打卡时间，到添加智能公式判断迟到、早退、缺勤等异常状态，再到用条件格式直观高亮问题记录——整套方法无需编程，操作清晰、可复用性强，特别适合HR或行政人员快速实现结构化、可视化、可筛选的考勤分析，大幅提升日常考勤管理效率与准确性。

如果您拥有员工每日打卡原始数据，但需要快速汇总迟到、早退、缺勤等异常情况，则可借助Excel数据透视表实现结构化统计。以下是完成该任务的具体步骤：

一、准备标准化一维打卡数据表

透视表要求数据为“扁平化”结构，即每人每次打卡占一行，含员工姓名、日期、打卡时间三列。若原始数据为多列时间（如早/晚各一列），需先转为一维格式。

1、选中原始打卡区域（含姓名、日期、打卡时间列），点击【数据】→【从表格/区域】，勾选“表包含标题”，创建Power Query连接。

2、在Power Query编辑器中，选中打卡时间列，点击【转换】→【拆分列】→【按分隔符】，选择空格或制表符分离日期与时间。

3、保留姓名、日期、时间三列，其余列删除；全选→【主页】→【删除重复项】确保无冗余记录。

4、点击【主页】→【关闭并上载】，将清洗后的一维数据载入新工作表。

二、构建基础透视表并提取上下班时间

通过透视表自动识别每位员工每日最早与最晚打卡时间，分别对应上班与下班记录，为后续判断提供依据。

1、选中清洗后的一维数据表任意单元格，点击【插入】→【数据透视表】→选择新工作表。

2、将“姓名”拖至【行】区域，“日期”也拖至【行】区域（置于“姓名”下方）。

3、将“打卡时间”字段拖入【值】区域两次；双击第一个“打卡时间”字段，在弹出窗口中将【值字段设置】→【汇总方式】改为“最小值”，【数字格式】设为“时间”。

4、双击第二个“打卡时间”字段，将【汇总方式】改为“最大值”，【数字格式】同样设为“时间”。

5、右键透视表任意时间值→【值字段设置】→重命名：第一个为最早打卡，第二个为最晚打卡。

三、添加计算列判断迟到早退状态

在透视表源数据表（非透视表本身）中新增辅助列，使用逻辑公式标记每条记录的异常类型，再将其纳入透视分析。

1、在清洗后的一维数据表右侧空白列（如D列）输入列标题“异常类型”。

2、在D2单元格输入公式：=IF(AND(A2=A1,C2"",C1""), "", IF(C2TIME(17,30,0), "早退", "")))（假设标准上班时间为8:30，下班时间为17:30）。

3、向下填充该公式至全部打卡记录行；对重复姓名同日多条记录，仅首条有效，其余留空以避免重复计数。

4、将“异常类型”列拖入透视表【值】区域，汇总方式设为“计数”；再拖入【筛选器】区域以便按异常类型动态筛选。

四、设置条件格式高亮异常值

在透视表结果中直接可视化呈现关键问题，便于行政人员快速定位高频异常人员或时段。

1、选中透视表中“最早打卡”和“最晚打卡”两列数值区域（不含标题行）。

2、点击【开始】→【条件格式】→【新建规则】→【使用公式确定要设置格式的单元格】。

3、输入公式：=$C2>TIME(8,30,0)，设置格式为红色背景+白色字体，应用于“最早打卡”列对应区域。

4、再次新建规则，输入公式：=$D2，设置相同格式，应用于“最晚打卡”列对应区域。

五、生成部门级迟到早退汇总报表

将统计维度从个人扩展至组织层级，满足行政管理中跨部门对比分析需求。

1、在原始一维数据表中新增“部门”列，确保每条打卡记录均关联所属部门（可从员工主表VLOOKUP补全）。

2、新建一个透视表，将“部门”拖至【行】区域，“异常类型”拖至【列】区域，“姓名”拖至【值】区域并设为“计数”。

3、右键透视表任意数值单元格→【值显示方式】→【显示值为】→【%列汇总】，即可获得各部门迟到、早退占本部门总异常的比例。

4、将“日期”字段拖入【筛选器】区域，可按月、周灵活筛选统计周期，输出当月各部门迟到率TOP3等管理看板指标。

理论要掌握，实操不能落！以上关于《透视表轻松统计考勤与迟到早退》的详细介绍，大家都掌握了吧！如果想要继续提升自己的能力，那么就来关注golang学习网公众号吧！