本章我们完成数据分析的第一道工序:数据清洗。很多人以为它就是“把空值填一填、把重复删一删”,
但真正决定清洗质量的,是你对 Excel 底层规则的理解——为什么同样是 1280,有的能求和,有的不能?
为什么日期会有五种长相?为什么透视表一碰到合并单元格就报错?
所以本章分两半:6.1–6.3 先讲清楚“为什么”,6.4–6.6 再带你完整清洗一份真实的订单表。
理论没看懂就动手,你只会记住“点哪个按钮”,换一份数据照样抓瞎。
先看一个真实场景。运营发来一份 6 月订单表,说月报的数不对,财务那边差了 3 万多。 你打开一看,表格长这样——
这张表看起来挺正常,但里面藏了四类问题:金额有的靠左、有的靠右;日期有五种写法; 第一行是一个横跨五列的标题;还有 20 个订单被记了两遍。它们有一个共同点—— Excel 不报错,但算出来的数是错的。这就是脏数据最危险的地方。
行业里对“脏数据”没有统一标准,但实际工作中反复出现的就是下面六类。你以后拿到任何一份表, 都可以拿这张表对照着查一遍。
| 类型 | 长什么样 | 会造成什么后果 |
|---|---|---|
| 缺失 | 单元格为空、填了 -、NULL、N/A | 求平均被拉低;分群时整条记录被丢掉 |
| 重复 | 同一订单出现两行,字段略有差异 | 金额被重复累加,GMV 虚高 |
| 类型错误 | 数字存成文本(左上角绿三角)、日期存成文本 | 求和漏算、无法按月汇总 |
| 格式不一致 | 同一列有 2024/6/1、20240601、6/1/2024 | 排序错乱、去重失败 |
| 异常值 | 金额 99,999、年龄 200、结束时间早于开始时间 | 平均值被一笔数据带偏 |
| 结构错误 | 合并单元格、标题占了一行、多级表头 | 透视表报错、排序错位 |
计算机领域有一句老话:Garbage In, Garbage Out——垃圾进,垃圾出。 你后面所有的分析、图表、结论,都建立在原始数据之上。如果源头错了,后面做得越漂亮,错得越离谱。
这四条不是技巧,是纪律。违背任何一条,你都会在某个深夜加班重做。
| 铁律 | 含义 |
|---|---|
| 原始数据只读 | 永远复制一份再动手。原文件是唯一的“案发现场”,改了就再也回不去了。 |
| 先体检,后治疗 | 先统计行数、空值率、类型分布,搞清楚问题全貌,再决定怎么处理。上来就删是最危险的。 |
| 每个动作留痕 | 改了什么、改了多少行、为什么改,全部写进清洗记录。别人问你“数为什么不一样”,你要能一条条指出来。 |
| 可复现 | 换一个人、换一台电脑,按你的记录能做出同样的结果。做不到,就等于没洗。 |
这一节是本章的理论核心。你遇到的 90% 的“Excel 灵异事件”,都能用这四条规则解释。 看不懂没关系,先硬着头皮读一遍,后面每一步操作我都会回头引用它们。
一个单元格里装的到底是数字还是文字,Excel 分得很清楚。常见的五种类型是:
| 类型 | 例子 | 对齐方式 | 能否求和 |
|---|---|---|---|
| 数值 | 1280.00 | 靠右 | 可以 |
| 文本 | "1280.00"、QN2406001 | 靠左 | 不行,被跳过 |
| 日期 | 2024-06-01 | 靠右 | 本质是数值,可以 |
| 逻辑 | TRUE / FALSE | 居中 | 参与运算时为 1/0 |
| 错误 | #N/A、#VALUE! | — | 传染,整列都错 |
关键结论:看起来像数字,不等于它是数字。判断方法有两个,都很简单:
| A | B | C | D | |
|---|---|---|---|---|
| 3 | QN24060063 | 2024/6/19 | 王×× | 4,124.52 |
| 4 | QN24060026 | 2024-06-27 | 陈×× | 1,109.24 |
| 5 | QN24060329 | 20240630 | 周×× | 1,819.11 |
| 6 | QN24060012 | 6/20/2024 | 钱×× | 2,024.53 |
| 12 | QN24060065 | 2024.6.3 | 赵×× | 1,068.40 |
| 16 | QN24060195 | 6/17/2024 | 陈×× | 3,367.99 |
Excel 内部用序列号记录日期:把 1900 年 1 月 1 日当作第 1 天,往后每过一天加 1。
所以你看到的 2024-06-01,在 Excel 眼里其实是 45444。
这个规则带来两个直接后果,你必须记住:
"2024/6/1" 和 "2024-06-01" 在 Excel 看来是两个完全不同的字符串,排序会按字母排,不是按时间。点一下单元格,看编辑栏:
编辑栏显示:2024/6/1 (和格子里一模一样)
靠左对齐,不能按年/月分组
编辑栏显示:2024/6/1,但单元格格式是日期
靠右对齐,右键“设置单元格格式”能看到日期类型
=ISNUMBER(B3)。返回 TRUE 是真日期(或数值),
返回 FALSE 就是文本。这个方法百试百灵。
你在 A1 输入“6 月订单明细”,然后把 A1:E1 合并。看起来是一个横跨五列的大格子, 但在 Excel 内部,值是存在 A1 里的,B1 到 E1 是空的,只是被视觉上隐藏了。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 6 月订单明细 | ||||
| 实际存储 | "6 月订单明细" | 空 | 空 | 空 | 空 |
| 2 | 订单号 | 下单时间 | 客户 | 金额 | 备注 |
这就是为什么:排序时整块会跟着乱跑、筛选时只能按第一列筛、透视表直接报错或算错。 因为 Excel 在处理数据时,认为 B1:E1 是空的,它没法理解“这一行其实是一个标题”。
这是初学者最容易忽视、也最难改的一个习惯。所谓一维表,就是: 每一行 = 一条完整的记录,每一列 = 一个字段,只有一个表头行。
| 地区 | 1 月 | 2 月 | 3 月 |
|---|---|---|---|
| 华东 | 120 | 135 | 150 |
| 华南 | 90 | 88 | 102 |
月份变成了“列名”,你没法按月份筛选,也没法算 3 个月的总趋势。
| 地区 | 月份 | 销售额 |
|---|---|---|
| 华东 | 1 月 | 120 |
| 华东 | 2 月 | 135 |
| 华南 | 1 月 | 90 |
每个维度都是一列,透视表可以任意组合。
回头看实验数据:它其实已经是一维表了(每行一个订单),只是被合并单元格和标题行破坏了结构。 所以第 2、3 步要做的,就是把它“还原”成标准的一维表。
理论讲完了,但别急着动手。真实工作里,你拿到的表千奇百怪,先花 3 分钟做一次“体检”, 比上来就删要快得多。体检就看三件事:
| 看什么 | 怎么问 | 怎么查 |
|---|---|---|
| 粒度 | 这一行代表什么?一个订单?一个商品?一次点击? | 找主键,看有没有重复。粒度为“订单”的表,订单号必须唯一。 |
| 类型 | 哪些列该是数字?哪些该是日期? | 看对齐方式、绿三角;用 =ISNUMBER() 抽查。 |
| 完整性 | 有多少空值?有没有重复?有没有离谱的极值? | COUNTBLANK 算空值率;排序看最大值最小值;条件格式标重复。 |
| 指标 | 怎么算 | 合格标准 |
|---|---|---|
| 总行数 | Ctrl+End 看最后一行 | 与来源系统一致 |
| 主键唯一性 | =COUNT(A:A)-COUNTIF(A:A,A2) 或条件格式标重复 | 0 重复 |
| 空值率 | =COUNTBLANK(C2:C481)/COUNTA(C2:C481) | 关键字段 < 5% |
| 数值范围 | 对金额列排序,看最大最小 | 无负数、无离谱极值 |
| 日期范围 | 对日期列排序 | 落在业务周期内 |
| 枚举值分布 | 透视表统计每个取值出现次数 | 无错别字、无多余空格 |
理论部分到此结束。从这一节开始,你要在自己的电脑上动手了。先把环境准备好。
| 项目 | 要求 |
|---|---|
| 操作系统 | Windows 10 / 11 或 macOS |
| 软件 | Microsoft Excel 2016 及以上,或 WPS 表格(菜单名略有差异) |
| 设备 | 必须是电脑。手机版 Excel 做不了分列和删除重复值。 |
| 数据 | 本章提供的 6月订单明细_原始.xlsx(480 行节选) |
6月订单明细_清洗中.xlsx。
原始文件从这一刻起只读——这是 6.1.3 的第一条铁律。
下面 8 个步骤,顺序不能乱。每一步我都按正点原子的写法分成四段: 做什么 → 为什么 → 怎么做 → 做完看到什么。 “为什么”那一栏,对应的就是 6.2 节的某条规则。
6月订单明细_原始.xlsx → 复制 → 空白处 粘贴6月订单明细_清洗中.xlsx如果已经不小心改了原文件,重新下载一份即可。但从今天起记住:原始数据永远只读。
弹出「只保留左上角的值」→ 点确定,文字还在 A1。
找不到按钮:在「开始」选项卡的「对齐方式」组里,图标是一个带箭头的方框。
替换了逗号还是不能求和:替换只去掉字符,单元格仍然是文本。必须再做一次「分列」。
分列后金额被劈成两列:说明你在分列时勾了逗号作分隔符。按 Ctrl+Z 撤销,重做,保持默认只勾 Tab。
状态栏没有求和:右键状态栏,把「求和」勾上。
2024-06-01。显示 ####:列太窄,拉宽 B 列。
有几个变成 1900 年:那几格没被识别,单独选中再分列一次。
关于 6/4/2024:这批数据月份都是 6 月,所以它一定是 6 月 4 日。换一批数据,必须先问清楚是「月/日/年」还是「日/月/年」。
两条重复记录的备注不一样:一条空着,一条写着「H5 补录」。
如果全勾,Excel 会认为这是两条不同的记录,一个都删不掉。你必须告诉它:判断重复的标准是订单号。
删完还是 480 行:说明勾了全部列。撤销重来。
提示「未找到重复值」:可能只选中了部分区域,Ctrl+A 重选。
问业务方一句:「6 月 19 日这笔 99999 是企业单吗?」问一句,比猜一小时强。
6月订单明细_清洗后.xlsx正点原子在「8.4 编译下载验证」里,是通过串口打印和 LED 亮灭来确认程序跑通的。 数据清洗的“现象”,就是两个数字:行数和金额。填进来,看看你对不对。
不看前面的正文,凭第一反应选。选完立刻出解析。
按“现象 → 原因 → 解决”整理,遇到问题先在这里找。
| 现象 | 原因 | 解决 |
|---|---|---|
| 求和比预期少 | 有文本型数字被跳过(规则 1) | 选中列 → 数据 → 分列 → 完成 |
| 日期没法按月分组 | 日期是文本,不是序列号(规则 2) | 选中列 → 数据 → 分列 → 日期 YMD |
| 透视表报错 / 数据错位 | 有合并单元格(规则 3) | 选中整表 → 开始 → 合并后居中(取消) |
| 删除重复值提示“未找到重复” | 勾了全部列,而两行备注不同 | 只勾主键列,通常是订单号 / 用户 ID |
| 删完重复,行数没变 | 没有选中整张表,只选了一部分 | 点数据区 → Ctrl+A → 再操作 |
| 金额列出现 #### | 列太窄,不是数据错误 | 双击列边界自动调宽 |
1. 1,152,141.40(未去重时)或去重后 1,115,121.36;正确答案 1,208,928.65,差 93,807.29。
2. 0 行。因为两条重复记录的备注不同(一条空、一条 H5 补录),全勾时 Excel 认为它们不重复。
3. 平均值会被这一笔拉高约 200 元(99,999 ÷ 460 ≈ 217)。
4. 重复全部来自 H5 补录,需要查上报链路。