Excel 数据清洗：分析前必做的 8 项检查

做数据分析时，最常见的错误往往不是公式写错，而是数据本身有问题：一列金额里混着几个文本，SUM 悄悄把它们漏掉；数据透视表把「合计」行也当成一个区域加了进去；日期列一半是真日期、一半是文字，排序和按月汇总全乱了。

数据清洗听起来枯燥，但分析结果对不对，基本在这一步就决定了。本文按照“应该先查什么”的顺序，列出几乎每份表格都会遇到的 8 类问题，每一类都讲清楚症状、为什么会出错、最快的修复方法，并给出 Excel 中文版和 WPS 表格的菜单路径。无论你是自己写公式、做透视表，还是把文件交给 AI 工具分析，这份清单都适用。

1. 表头不在第一行

症状： 打开表格，第一行是大标题「2026 年三季度销售报表」，接着一行空白，可能还有“制表人：张伟”，真正的列名在第 5 行。

为什么会出错： 数据透视表、Power Query、AI 工具都要先判断哪一行是列名。判断错了，「2026 年三季度销售报表」就会变成第一列的列名，下面几行说明文字也会被当成数据。

修复方法： 删除表头上方的所有行，或者把数据区域复制到一个新工作表，从 A1 开始粘贴。如果这份报表每周都要重新导出，可以在 Power Query 里一次性设置：数据 → 从表格/区域，进入编辑器后 开始 → 删除行 → 删除最前面几行，以后刷新就会自动重复这一步。

一个好习惯：一个工作表只放一张表，第一行是列名，列名不重复、不留空。

2. 用占位符代替空白

症状： 单元格里填的是「无」「N/A」「-」「--」「待定」「/」「？」，或者只有一个空格。

为什么会出错： SUM 和 AVERAGE 会忽略文本，所以这些行被悄悄排除在合计和平均值之外，你算出的平均值其实是基于更少的行。更麻烦的是 COUNT 和 COUNTA 会给出不同的行数，不同的人用不同的函数，报表里的数字就对不上。

修复方法： 选中该列，按 Ctrl+H 打开「查找和替换」，把每种占位符替换为空（替换为一栏什么都不填）。注意勾选「单元格匹配」，否则「无锡」里的「无」也会被替换掉。占位符种类多时，可以用辅助列：

=IF(OR(A2="无",A2="-",A2="N/A",TRIM(A2)=""),"",A2)

然后把辅助列「选择性粘贴 → 数值」覆盖回原列。

3. 数字被存成了文本

症状： 数字靠左对齐；单元格左上角有绿色小三角；明明全是数字的一列，SUM 结果却是 0。从 ERP、财务系统或网页复制下来的数据尤其常见。

为什么会出错： 看起来像数字的文本不是数字。公式会跳过它；排序时“10”排在“9”前面；图表什么也画不出来。

最快的修复： 选中整列，数据 → 分列 → 直接点「完成」，Excel 会把每个单元格重新解析一遍。也可以选中区域后点左上角出现的黄色感叹号，选择「转换为数字」。

顽固情况： 从网页复制的数据常带有不间断空格（CHAR(160)），TRIM 去不掉。用辅助列：

=VALUE(TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160),""))))

带货币符号和单位的金额，比如「¥2,400.50」或「2400元」，本质是同一个问题：

=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"¥",""),",",""),"元",""))

如果表里混着「1.2万」这种写法，需要单独处理：=IF(RIGHT(A2)="万",VALUE(LEFT(A2,LEN(A2)-1))*10000,VALUE(A2))。

反过来的坑： 身份证号、银行卡号、超长订单号应该是文本。Excel 数字只保留 15 位有效数字，18 位身份证号一旦被当成数字，后 3 位会永久变成 0，还会显示成科学计数法。导入 CSV 时要在「分列」第 3 步把这些列设为「文本」。

4. 日期格式不统一

症状： 同一列里有 2024/3/5、2024-03-05、2024.3.5、20240305、2024年3月5日，有的右对齐（真日期），有的左对齐（文本）；按日期排序顺序不对，按月汇总少了很多行。

为什么会出错： 中文版 Excel 能识别 2024/3/5、2024-3-5 和 2024年3月5日，但不认识用点号分隔的 2024.3.5，而 20240305 只是一个八位数字。另外，从海外系统导出的数据可能是 03/04/2024，它到底是 3 月 4 日还是 4 月 3 日，取决于导出系统的地区设置，光改单元格格式解决不了。

修复方法：

• 点号日期：选中列，Ctrl+H 把「.」替换为「/」。
• 八位数字日期：数据 → 分列 → 下一步 → 下一步 → 列数据格式选「日期：YMD」→ 完成。
• 美式或英式日期：先找一个“日”大于 12 的值（如 25/12/2024）确认格式，再用 =DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))（日在前）重建。

如果文件还要再导出给别的系统，建议统一存成 yyyy-mm-dd，这是唯一不会产生歧义的格式。

5. 数据中夹着合计和小计行

症状： 数据中间出现「小计」「合计」「总计」行，或者每个区域下面有一行加粗的汇总。

为什么会出错： 这是合计翻倍最常见的原因。对整列求和或做数据透视表时，小计会和它所汇总的明细行一起被加进去。

修复方法： 在标签列用筛选，数据 → 筛选，搜索「计」，把合计、小计、总计行全部删掉；需要汇总时用数据透视表或在数据区域外用 SUBTOTAL 重新计算。如果这张报表是别人维护的，不要直接改，把明细行复制到新表再分析。

6. 重复行

症状： 同一个订单号出现两次。通常是导出执行了两次再拼接，或者源系统关联查询时把一条记录“扇出”成了多条。

为什么会出错： 所有计数和合计都会虚高，而且往往集中在某一段日期，很难一眼看出来。

修复方法： 完全相同的行，用 数据 → 删除重复值，勾选全部列。按关键字段去重时只勾选订单号那一列。想先看看有哪些重复，用 开始 → 条件格式 → 突出显示单元格规则 → 重复值，或者辅助列 =COUNTIF(A:A,A2)>1 再筛选 TRUE。更完整的方法和“该保留哪一行”的判断，见 Excel 查找和删除重复项。

7. 合并单元格

症状： 区域名「华东」合并了五行，下面四行在区域列里其实是空的。

为什么会出错： 合并单元格是排版功能。从分析角度看，那四行没有区域，按区域分组时它们就会丢失；很多操作（排序、筛选、透视表）遇到合并单元格也会报错。

修复方法： 选中该列，开始 → 合并后居中 → 取消单元格合并；然后 开始 → 查找和选择 → 定位条件 → 空值，输入 = 再按一下 ↑ 键引用上方单元格，按 Ctrl+Enter 批量填充，最后复制该列「选择性粘贴 → 数值」。

8. 多余空格和不可见字符

症状： 「北京」和「北京 」看起来一样，筛选和透视表却分成两项；VLOOKUP 或 XLOOKUP 明明有这个值却返回 #N/A。

为什么会出错： 首尾空格、全角空格、换行符、不间断空格都会让两个“看起来一样”的值不相等。中文数据里还有全角和半角混用的问题，比如「（」和「(」、全角数字「１２」。

修复方法： =TRIM(CLEAN(A2)) 去掉普通空格和换行；全角空格用 =SUBSTITUTE(A2,"　","")；全半角混用可以用 =ASC(A2) 把全角字母数字转成半角。统一之后再做查找、分组和去重。

WPS 表格里怎么操作

上面的方法在 WPS 表格中基本都能用，函数名和写法完全一致，主要差别在菜单位置：

• 删除重复项： WPS 在 数据 → 重复项 → 删除重复项；同一个菜单里还有「设置高亮重复项」和「拒绝录入重复项」，后者可以从源头防止重复录入。
• 文本转数字： 选中区域后点旁边的提示图标，选择「转换为数字」；较新版本在「开始」选项卡也有转换相关入口，具体位置随版本略有不同。
• 分列： 数据 → 分列，步骤与 Excel 一致。
• 定位空值： 开始 → 查找 → 定位，或直接按 Ctrl+G。
• Power Query： WPS 没有与 Excel 完全相同的 Power Query 编辑器，需要每周重复的清洗流程，可以考虑录制步骤或用 AI 工具一次性处理。

常见错误

1. 在原文件上直接清洗。 删除重复值、覆盖粘贴都是不可逆的，先另存一份副本。
2. 把缺失值填成 0。 空白会被平均值排除，0 会把平均值拉低，还把“没有数据”伪装成“数值为 0”。除非 0 确实是真实值，否则留空。
3. 只改格式不改值。 把文本日期的单元格格式改成“日期”，值仍然是文本；必须用分列或公式重新生成。
4. 替换时没勾「单元格匹配」。 把「无」替换为空，结果「无锡」变成了「锡」。
5. 长编号被转成数字。 身份证号、订单号一旦丢失精度就无法恢复，导入时就要设为文本。
6. 清洗完没有复核。 至少用一个手工 SUM 或 COUNTA 对比清洗前后的行数和金额，确认只删掉了该删的。

5 分钟检查清单

分析任何文件之前，先过一遍：

1. 列名在第一行吗？（问题 1）
2. 数值列上 =COUNT() 和 =COUNTA() 结果一致吗？（问题 2、3）
3. 日期都右对齐、能按时间正确排序吗？（问题 4）
4. 标签列筛选「计」，有结果吗？（问题 5）
5. 删除重复值的预览显示 0 个重复吗？（问题 6）
6. 数据区域里有合并单元格吗？（问题 7）
7. 关键列用 TRIM 处理后，分组数量有变化吗？（问题 8）

全部通过，公式、透视表和 AI 的答案就会彼此一致；任何一项不通过，先修好再分析，既更快，也更可靠。想了解清洗之后常用的汇总函数，可以看 数据分析常用 Excel 公式。

让 AI 先帮你找问题

如果不想逐项手动排查，可以把文件上传到 ChatExcel，直接问：“这份表有哪些数据质量问题需要注意？”它会读取工作簿里的每一个工作表，基于全部数据检查数值列里的非数字值、重复编号、无法识别的日期和疑似合计行，并告诉你哪些问题会影响你接下来要问的数字。

找到问题之后，你可以继续让它生成一份清洗后的 Excel 文件：去掉重复行、拆分工作表，直接下载。需要在自己的表里修复时，问“在 Excel 里怎么用公式做到？”，它会给出对应的 Excel 公式。ChatExcel 只提供付费方案（Plus $29/月起），没有免费版，详情见 价格页面 或 常见问题。