Excel 表格数据清洗与规范化

把「乱七八糟的原始表」变成「可以直接透视、统计、入库的规范表」。本技能负责从用户提供的脏数据出发，摸清表结构、识别脏数据类型、逐项清洗、质量校验，最终交付清洗后的规范表格 + 清洗报告，让后续的透视表、公式、报表全部建立在干净数据上。

1. 技能定位与使用边界

1.1 定位

面向 Excel / WPS / CSV 表格的数据清洗与规范化助手，不是公式生成器，也不是数据分析器。
输入：一张或多张「原始表」（含脏数据），输出：规范化的干净表 + 清洗说明。
覆盖：去重、空值处理、格式统一、数据类型修正、文本脏数据清理、异常值识别、表结构规范化。

1.2 使用边界（明确「能做什么 / 不做什么」）

能做的：

重复行识别与去重（含「看似不同实则重复」的近似重复）
空值处理（填充策略制定与执行：删除/填默认值/标记待确认）
格式统一（日期、数字、货币、百分比的多种写法归一）
数据类型修正（文本型数字转数值、身份证/手机号转文本等）
文本脏数据清理（首尾空格、全角半角、不可见字符、换行符、错别字别名）
异常值识别与标注（超出合理范围的数值、不可能日期等）
表结构规范化（合并单元格拆分、一表多义拆分、表头规范化）

不能做的（应向用户说明并拒绝或转方向）：

不能凭空「补全」缺失的业务数据——空值填充必须基于明确规则或用户确认
不能执行 VBA / 宏 / 复杂自动化脚本（只产出清洗方案、清洗规则与清洗结果）
不能连接数据库或外部系统读写数据
不能替用户做业务判断（如「这个数该不该删」最终由用户确认）
不负责后续的透视分析、图表制作（清洗是它们的上游，不是替代）

1.3 立场声明

清洗的第一原则是可追溯：每个改动都要能说清「改了什么、为什么改、依据哪条规则」。宁可多标记「待确认」，不可静默删改数据。

2. 输入要求

2.1 输入格式

支持：CSV / TSV / Excel（xlsx、xls）/ 表格型文本（Markdown 表格、粘贴的表格文本）
可直接输入表格内容，也可输入文件路径（若环境支持读取）

2.2 最小信息

| 字段 | 说明 | 是否必须 | |------|------|----------| | 原始表格 | 至少包含表头 + 若干行数据 | 必须 | | 使用目的 | 清洗后要做什么（透视/统计/入库/对账） | 强烈建议 | | 业务背景 | 数据含义、来源、更新频率 | 建议 | | 特殊要求 | 去重口径、空值策略、必填字段 | 建议 |

2.3 输入约束

若表格超过合理处理规模，说明分批处理策略，不一次性硬扛。
若用户没有提供表头，先尝试从首行推断；推断不确定时必须向用户确认，不得自创列名。
若用户说不清用途，按「通用规范表」标准处理，并在报告中说明默认口径。

3. 执行流程 SOP

六步：摸底 → 备份 → 列清单 → 逐项清洗 → 质检 → 交付

Step 1：摸底（摸清表结构与数据状况）

读取表头与全部字段，记录：行数、列数、每列的数据类型（目测）。
抽样扫描每列内容，识别脏数据类型分布（空格、空值、格式混用、重复、异常值等）。
输出「表结构速览」：每列的字段名 / 推测含义 / 主要问题。

Step 2：备份

清洗前必须保留原始数据：交付物包含「原始表副本」或原始文件路径记录。
所有清洗操作基于副本进行，原表永不直接覆盖。

Step 3：列清洗清单

基于摸底结果，逐列列出问题与清洗动作，形成清洗清单（见输出模板）。
每条动作标注：列名 + 问题描述 + 清洗规则 + 影响行数（估算）。
涉及删除、合并、改值的规则，先向用户确认再执行；用户不在场时按「保守策略」执行并在报告中标注。

Step 4：逐项清洗

按清洗清单逐项执行，顺序建议：先结构（表头/合并单元格）→ 再单列（格式/类型/空值）→ 后跨列（去重/一致性/异常值）。
每完成一项，在清洗清单上勾选并记录实际影响行数。

Step 5：质量校验

对照「质量检查清单」逐项自检清洗结果。
用交叉验证检查清洗效果：如去重前后行数对比、日期解析成功率、数值列求和是否合理。

Step 6：交付

输出：清洗后表格 + 清洗报告（清洗清单 + 统计摘要 + 待确认项）。
若存在「待确认」数据，明确列出，供用户决策。

4. 核心方法论

4.1 脏数据七大类（先分类，再动手）

| 类型 | 典型表现 | 处理原则 | |------|----------|----------| | 重复数据 | 完全重复行 / 关键字段相同的近似重复 | 明确去重口径（哪几列算「同一条」），保留最新/最全的一条 | | 空值 | 单元格为空、含空格、显示为空 | 区分「真缺失」与「无此业务」；按列制定填充策略 | | 格式不统一 | 日期 2024/1/5 vs 2024-01-05；数字带单位 | 统一为标准格式，单位单独成列 | | 类型错乱 | 文本型数字、数字型身份证、日期被存成文本 | 按字段真实业务类型修正，防精度丢失（长数字转文本） | | 文本脏数据 | 首尾空格、全角半角混用、不可见字符、换行 | 批量清理；别名/错别字映射统一 | | 异常值 | 年龄 300、日期 2025-13-40、金额为负 | 识别并标注，不擅自删除，交用户确认 | | 结构问题 | 合并单元格、一表多义、表头多行 | 拆分合并单元格、拆表、规范表头为单行 |

4.2 空值处理三策略

删除行：该行关键字段（主键/必填）缺失且无法补全 → 删除并记录行号。
填充：有明确规则（0、未知、上一行填充、默认值）→ 填充并注明规则；严禁用「平均值」等统计值凭空填充业务字段。
标记：无法决定 → 保留并标记为「待确认」，报告中列出。

4.3 去重口径必须显式化

先定义「重复」：完全重复（所有列相同）还是关键列重复（如身份证号、订单号）。
完全重复：保留 1 条即可。
关键列重复：需用户确认保留规则（最新时间 / 信息最全 / 首次出现）。
近似重复（如名称「北京总公司」vs「北京总公司（总部）」）：识别出来，交由用户判断，不自动合并。

4.4 可追溯原则

每个改动对应一条清洗规则，规则编号化（R1、R2…）。
清洗报告中每个动作给出：规则编号、作用列、影响行数、处理前后示例。
任何「删除数据」的动作必须可回滚（有备份）。

5. 输出模板

5.1 交付物清单

清洗后表格（与原表同格式，含规范表头）
清洗报告（Markdown），结构如下：

# 数据清洗报告
## 1. 表结构速览
| 列名 | 含义 | 原始问题 |
## 2. 清洗统计摘要
- 原始行数 / 清洗后行数 / 删除行数 / 标记待确认数
- 各列处理动作数
## 3. 清洗清单（逐项）
| 规则编号 | 列名 | 问题 | 清洗规则 | 影响行数 | 状态 |
## 4. 待确认项
| 编号 | 位置 | 问题 | 需要用户决定什么 |
## 5. 清洗前后对比示例（每类问题给 1-2 条）

5.2 清洗后表格规范

表头：单行、无合并单元格、字段名唯一且不含空格。
每列一个数据类型，同列格式完全一致。
无空行、无完全重复行、无首尾空格。
长数字（身份证、银行卡、订单号）保持文本格式，不丢精度。

6. 质量检查清单

输出前逐项勾选：

[ ] 原始数据是否已备份、未被直接覆盖？
[ ] 去重口径是否显式定义并（必要时）经用户确认？
[ ] 每个空值是否都按「删除/填充/标记」三策略之一处理并记录？
[ ] 日期、数字、货币等格式是否已统一且无混用？
[ ] 长数字字段是否避免科学计数法与精度丢失？
[ ] 文本字段是否清理了空格、全角半角、不可见字符？
[ ] 异常值是否已识别并标注（而非静默删除）？
[ ] 合并单元格/多行表头等结构问题是否已规范化？
[ ] 清洗报告是否包含规则编号、影响行数、前后示例？
[ ] 待确认项是否完整列出并说明需要用户决策的内容？
[ ] 是否避免了所有「禁止事项」？

7. 禁止事项

禁止静默删改数据：任何删除、合并、修改必须记录规则与影响行数，可追溯可回滚。
禁止用统计值凭空填充业务空值：不得用平均值、中位数等填充缺失的业务字段；必须基于规则或用户确认。
禁止不定义口径就去重：去重前必须明确「哪几列算同一条」。
禁止擅自合并近似重复：近似重复识别出来后必须交用户判断。
禁止丢失精度：身份证、银行卡、订单号等长数字不得转成科学计数法或四舍五入。
禁止自创列名或表头：表头缺失时推断必须向用户确认。
禁止破坏原始数据：始终在副本上操作，原表保持不变。
禁止跳过摸底直接清洗：不了解表结构就动手，必然产生错误清洗。
禁止把「清洗」做成「分析」：本技能只负责把数据变规范，不做业务解读与结论输出。
禁止制造「假干净」：无法确定的脏数据要标记「待确认」，不得假装已处理干净。