行政每月总有那么几天——
从钉钉导出的打卡数据，格式混乱、时间不统一、缺卡记录一堆……
全靠肉眼核对、手动筛选，1个小时打底，还容易出错。
今天教大家3种方法，把「钉钉原始打卡表」一键转成「规范考勤报表」，出勤小时、加班时长、打卡异常自动归类，10分钟搞定一个月的数据。
---
场景痛点
你是不是也遇到过？
·• 钉钉导出的打卡时间是一串文本："2024-03-15 09:23:45"，无法直接计算
·• 一天打两次卡，需要手动算上班时长
·• 缺卡、迟到、早退没有标注，全靠眼睛找
·• 加班时间统计要逐行相减，工作量大
> 一句话：钉钉数据"有"，但不是"能用"的格式，需要大量清洗。
---
效果对比
▲ 图：钉钉导出的原始打卡表 vs 清洗后的规范考勤报表
---
方法一：Power Query（适合数据量大的月度汇总）
为什么选它
Power Query 是 Excel 内置的 ETL 工具，专门处理「脏数据」，一次清洗模板建好，以后每月只需刷新。
步骤
第一步：导入钉钉数据
1. Excel 中点击「数据」→「获取数据」→「从文件」→「从工作簿」
2. 选择钉钉导出的 `.xlsx` 文件
3. 选中打卡数据所在 sheet，点击「转换数据」进入 Power Query 编辑器
▲ 图：Power Query 编辑器界面
第二步：拆分日期和时间
1. 选中打卡时间列
2. 点击「拆分列」→「按分隔符」（分隔符输入空格）
3. 自动拆成「日期列」和「时间列」两列
第三步：转换时间格式
1. 选中时间列 → 右键 →「更改类型」→「使用区域设置」
2. 选择「时间」，格式选 `hh:mm:ss`
第四步：设置刷新频率
1. 关闭 Power Query 编辑器，点击「关闭并加载」
2. 以后每月新数据替换原文件后，右键表格 →「刷新」即可
公式
Power Query 不依赖公式，它靠的是步骤记录。核心逻辑是「拆分-清洗-合并」。
适用场景
·• 每月固定格式的重复性工作
·• 数据量超过1000行的
·• 需要保留原始数据仅生成报表的
---
方法二：Excel 函数公式（适合快速单次处理）
适用情况
数据量不大（几百行以内），不想学 Power Query，直接用公式搞定。
步骤
第一步：提取小时数
假设钉钉打卡时间在 A 列，格式为 "2024/3/15 9:23:45"
在 B 列输入公式：
=TEXT(A2,"HH")*1
这会把时间部分的「小时」提取出来，并转为数值。
第二步：计算上班时长（假设9点上班，18点下班，中间休息1小时）
=IF(OR(B2="缺卡",B2=""),"异常",
IF((18-B2-1)>=8,"正常",(18-B2-1)&"小时加班"))
▲ 图：B列公式下拉填充效果
第三步：统计加班时长
=MAX(0,18-B2-1-8)
如果当天工作超过8小时，自动计算加班部分。
第四步：异常标注（条件格式）
1. 选中数据区域
2. 点击「开始」→「条件格式」→「新建规则」
3. 选择「使用公式确定要设置格式的单元格」
4. 输入公式：`=ISERROR(TIMEVALUE(B2))`
5. 设置填充色为红色
完整公式包
| 统计项 | 公式 |
|--------|------|
| 提取小时 | `=TEXT(A2,"HH")*1` |
| 提取分钟 | `=TEXT(A2,"MM")*1` |
| 上班时长 | `=18-B2-1` |
| 是否加班 | `=IF((18-B2-1)>8,(18-B2-1)-8,0)` |
| 缺卡判断 | `=IF(A2="","缺卡","正常")` |
适用场景
·• 单次快速处理
·• 需要在原表上直接操作的
·• 小数据量（<500行）
---
方法三：辅助列+数据透视表（适合多维度汇总统计）
为什么选它
如果你的最终目标是统计「每个人、每天、每月」的出勤/加班汇总，用数据透视表最省事。
步骤
第一步：在原始数据旁新增辅助列
| 辅助列 | 公式 | 作用 |
|--------|------|------|
| 日期 | `=TEXT(A2,"yyyy/mm/dd")` | 统一日期格式 |
| 上班小时 | `=HOUR(A2)` | 提取小时 |
| 出勤标志 | `=IF(AND(B2>=9,B2<=18),1,0)` | 标记是否在岗 |
第二步：生成透视表
1. 选中数据 →「插入」→「数据透视表」
2. 拖拽字段：
- 行：姓名/日期
- 值：出勤标志（求和）、加班时长（求和）
第三步：设置计算字段（可选）
如果透视表中没有加班时长列：
1. 点击透视表 →「分析」→「字段、项目和集」→「计算字段」
2. 名称输入「加班时长」，公式：`=SUM(上班时长)-8*SUM(出勤天数)`
▲ 图：数据透视表字段设置界面
公式
=IF(AND(HOUR(A2)>=9,HOUR(A2)<=18),1,0) // 出勤标志
=SUMIF(B:B,"张三",C:C) // 统计张三的加班总时长
适用场景
·• 需要多人、多天汇总对比的
·• 月度考勤报表需要多维度分析的
·• 行政/HR 需要生成多份统计表的
---
常见问题
Q1：钉钉导出的时间格式不一致怎么办？
> A：用 `=DATEVALUE(TEXT(A2,"yyyy/mm/dd"))` 先统一转日期格式，再处理。
Q2：一天打4次卡（上班/下班/加班/加班结束）怎么算？
> A：用 `MAX()` 和 `MIN()` 分别取当天最大和最小时间点，相减即可。例如：`=MAX(B:B)-MIN(B:B)`
Q3：透视表刷新后公式列消失了
> A：辅助列写在原表区域外（如Z列以后），或者把辅助列公式转成数值粘贴，避免被刷新覆盖。
Q4：如何批量处理多个钉钉导出文件？
> A：Power Query 中「追加查询」功能可以把多个文件合并后统一清洗，一步到位。
Q5：员工姓名有空格/错别字，怎么统一？
> A：用 `=TRIM(A2)` 去空格，配合 `=SUBSTITUTE(A2,"某","某")` 批量替换错别字。
---
总结
| 方法 | 优点 | 缺点 | 推荐指数 |
|------|------|------|----------|
| Power Query | 一劳永逸，月月可复用 | 需要学基础操作 | ⭐⭐⭐⭐⭐ |
| 函数公式 | 快速灵活，零门槛 | 数据大了卡顿 | ⭐⭐⭐⭐ |
| 透视表 | 汇总方便，可视化强 | 需要辅助列配合 | ⭐⭐⭐⭐ |
核心思路就一句话：
> 先把「文本时间」变成「可计算时间」，再让公式帮你算加班、标注异常。
钉钉打卡数据清洗这事，方法选对，10分钟干完原来1小时的活。
---