数据分析师训练营/ 第二篇 Excel/ 第六章
第六章 · 数据清洗

脏数据急救

本章我们完成数据分析的第一道工序:数据清洗。很多人以为它就是“把空值填一填、把重复删一删”, 但真正决定清洗质量的,是你对 Excel 底层规则的理解——为什么同样是 1280,有的能求和,有的不能? 为什么日期会有五种长相?为什么透视表一碰到合并单元格就报错?

所以本章分两半:6.1–6.3 先讲清楚“为什么”,6.4–6.6 再带你完整清洗一份真实的订单表。 理论没看懂就动手,你只会记住“点哪个按钮”,换一份数据照样抓瞎。

理论 : 实操 = 5 : 5 约 8 学时 前置 第 5 章

6.1数据清洗到底在洗什么

先看一个真实场景。运营发来一份 6 月订单表,说月报的数不对,财务那边差了 3 万多。 你打开一看,表格长这样——

6月订单明细原始表
图 6.1.1 实验用的原始订单表(点击可放大)

这张表看起来挺正常,但里面藏了四类问题:金额有的靠左、有的靠右;日期有五种写法; 第一行是一个横跨五列的标题;还有 20 个订单被记了两遍。它们有一个共同点—— Excel 不报错,但算出来的数是错的。这就是脏数据最危险的地方。

6.1.1脏数据的六种类型

行业里对“脏数据”没有统一标准,但实际工作中反复出现的就是下面六类。你以后拿到任何一份表, 都可以拿这张表对照着查一遍。

类型长什么样会造成什么后果
缺失单元格为空、填了 -、NULL、N/A求平均被拉低;分群时整条记录被丢掉
重复同一订单出现两行,字段略有差异金额被重复累加,GMV 虚高
类型错误数字存成文本(左上角绿三角)、日期存成文本求和漏算、无法按月汇总
格式不一致同一列有 2024/6/1、20240601、6/1/2024排序错乱、去重失败
异常值金额 99,999、年龄 200、结束时间早于开始时间平均值被一笔数据带偏
结构错误合并单元格、标题占了一行、多级表头透视表报错、排序错位

6.1.2为什么必须先清洗:GIGO

计算机领域有一句老话:Garbage In, Garbage Out——垃圾进,垃圾出。 你后面所有的分析、图表、结论,都建立在原始数据之上。如果源头错了,后面做得越漂亮,错得越离谱。

一个真实的教训:某公司月报的 GMV 连续三个月偏高 3%,没人发现。原因是订单表里有一批 “补录”记录重复上报。直到财务对账才暴露——三个月的经营决策,全建立在虚高的数字上。

6.1.3清洗的四条铁律

这四条不是技巧,是纪律。违背任何一条,你都会在某个深夜加班重做。

铁律含义
原始数据只读永远复制一份再动手。原文件是唯一的“案发现场”,改了就再也回不去了。
先体检,后治疗先统计行数、空值率、类型分布,搞清楚问题全貌,再决定怎么处理。上来就删是最危险的。
每个动作留痕改了什么、改了多少行、为什么改,全部写进清洗记录。别人问你“数为什么不一样”,你要能一条条指出来。
可复现换一个人、换一台电脑,按你的记录能做出同样的结果。做不到,就等于没洗。

6.2Excel 的四条底层规则

这一节是本章的理论核心。你遇到的 90% 的“Excel 灵异事件”,都能用这四条规则解释。 看不懂没关系,先硬着头皮读一遍,后面每一步操作我都会回头引用它们。

1

单元格只有五种类型,而 Excel 只对“数值”求和

一个单元格里装的到底是数字还是文字,Excel 分得很清楚。常见的五种类型是:

类型例子对齐方式能否求和
数值1280.00靠右可以
文本"1280.00"、QN2406001靠左不行,被跳过
日期2024-06-01靠右本质是数值,可以
逻辑TRUE / FALSE居中参与运算时为 1/0
错误#N/A、#VALUE!—传染,整列都错

关键结论:看起来像数字,不等于它是数字。判断方法有两个,都很简单:

  1. 看对齐:默认情况下,数值靠右,文本靠左。
  2. 看绿三角:单元格左上角有个绿色小三角,就是 Excel 在提醒你“这是文本型数字”。
ABCD
3QN240600632024/6/19王××4,124.52
4QN240600262024-06-27陈××1,109.24
5QN2406032920240630周××1,819.11
6QN240600126/20/2024钱××2,024.53
12QN240600652024.6.3赵××1,068.40
16QN240601956/17/2024陈××3,367.99
图 6.2.1 同一列里,靠右的是数值,靠左带绿三角的是文本
最坑的地方:Excel 不会报错。你选中 D 列看求和,它只把数值加起来,文本那一部分 “安静地消失”了。你不主动核对,永远发现不了。
2

日期本质是一个数字,不是“长得像日期”的字符串

Excel 内部用序列号记录日期:把 1900 年 1 月 1 日当作第 1 天,往后每过一天加 1。 所以你看到的 2024-06-01,在 Excel 眼里其实是 45444。

1900-01-01
=
1
…
2024-06-01
=
45444
图 6.2.2 日期的存储方式:序列号

这个规则带来两个直接后果,你必须记住:

  • 真正的日期可以排序、可以按月分组、可以做减法。因为它是数字。
  • 文本日期什么都做不了。"2024/6/1" 和 "2024-06-01" 在 Excel 看来是两个完全不同的字符串,排序会按字母排,不是按时间。

怎么判断是不是真日期

点一下单元格,看编辑栏:

文本日期

编辑栏显示:2024/6/1 (和格子里一模一样)

靠左对齐,不能按年/月分组

真日期

编辑栏显示:2024/6/1,但单元格格式是日期

靠右对齐,右键“设置单元格格式”能看到日期类型

更可靠的判断:在空白格输入 =ISNUMBER(B3)。返回 TRUE 是真日期(或数值), 返回 FALSE 就是文本。这个方法百试百灵。
3

合并单元格不是“一个格子”,是“一片格子只留一个值”

你在 A1 输入“6 月订单明细”,然后把 A1:E1 合并。看起来是一个横跨五列的大格子, 但在 Excel 内部,值是存在 A1 里的,B1 到 E1 是空的,只是被视觉上隐藏了。

ABCDE
1 6 月订单明细
实际存储 "6 月订单明细" 空 空 空 空
2订单号下单时间客户金额备注
图 6.2.3 合并单元格的真实存储:只有左上角有值

这就是为什么:排序时整块会跟着乱跑、筛选时只能按第一列筛、透视表直接报错或算错。 因为 Excel 在处理数据时,认为 B1:E1 是空的,它没法理解“这一行其实是一个标题”。

结论:只要你要做数据分析(排序、筛选、透视表、去重),表里就不能有合并单元格。 一个都不能有。
4

透视表只认一维表:一行必须是一条记录

这是初学者最容易忽视、也最难改的一个习惯。所谓一维表,就是: 每一行 = 一条完整的记录,每一列 = 一个字段,只有一个表头行。

二维表(交叉表)—— 不能做分析
地区1 月2 月3 月
华东120135150
华南9088102

月份变成了“列名”,你没法按月份筛选,也没法算 3 个月的总趋势。

一维表 —— 可以做任何分析
地区月份销售额
华东1 月120
华东2 月135
华南1 月90

每个维度都是一列,透视表可以任意组合。

回头看实验数据:它其实已经是一维表了(每行一个订单),只是被合并单元格和标题行破坏了结构。 所以第 2、3 步要做的,就是把它“还原”成标准的一维表。

6.3拿到一份表,先做体检

理论讲完了,但别急着动手。真实工作里,你拿到的表千奇百怪,先花 3 分钟做一次“体检”, 比上来就删要快得多。体检就看三件事:

看什么怎么问怎么查
粒度 这一行代表什么?一个订单?一个商品?一次点击? 找主键,看有没有重复。粒度为“订单”的表,订单号必须唯一。
类型 哪些列该是数字?哪些该是日期? 看对齐方式、绿三角;用 =ISNUMBER() 抽查。
完整性 有多少空值?有没有重复?有没有离谱的极值? COUNTBLANK 算空值率;排序看最大值最小值;条件格式标重复。

六项体检指标

指标怎么算合格标准
总行数Ctrl+End 看最后一行与来源系统一致
主键唯一性=COUNT(A:A)-COUNTIF(A:A,A2) 或条件格式标重复0 重复
空值率=COUNTBLANK(C2:C481)/COUNTA(C2:C481)关键字段 < 5%
数值范围对金额列排序,看最大最小无负数、无离谱极值
日期范围对日期列排序落在业务周期内
枚举值分布透视表统计每个取值出现次数无错别字、无多余空格
本关数据体检的结果:482 行(含标题和表头);主键有 20 个重复;金额列 40 个文本; 日期 5 种格式;金额最大值 99,999;客户字段 52 个空值。这些数字后面都会用到。

6.4实验准备

理论部分到此结束。从这一节开始,你要在自己的电脑上动手了。先把环境准备好。

6.4.1实验环境

项目要求
操作系统Windows 10 / 11 或 macOS
软件Microsoft Excel 2016 及以上,或 WPS 表格(菜单名略有差异)
设备必须是电脑。手机版 Excel 做不了分列和删除重复值。
数据本章提供的 6月订单明细_原始.xlsx(480 行节选)

6.4.2下载实验文件

实验数据

6月订单明细_原始.xlsx

480 行 · 5 列 · 含四类脏数据

下载
交付模板

清洗记录模板.md

实验完成后要填写的记录表

下载
注意:下载后先复制一份,命名为 6月订单明细_清洗中.xlsx。 原始文件从这一刻起只读——这是 6.1.3 的第一条铁律。

6.5实验步骤

下面 8 个步骤,顺序不能乱。每一步我都按正点原子的写法分成四段: 做什么 → 为什么 → 怎么做 → 做完看到什么。 “为什么”那一栏,对应的就是 6.2 节的某条规则。

已完成 0 / 8 预计 35 分钟
01

复制一份,别动原文件

1 分钟 · 对应铁律 1
做什么
建立一份可修改的副本,保留原始文件作为对照。
为什么
清洗过程中随时可能改错。没有原始文件,你连“改之前是什么样”都说不清,更没法向财务解释差异。
怎么做
  1. 右键 6月订单明细_原始.xlsx → 复制 → 空白处 粘贴
  2. 把副本重命名为 6月订单明细_清洗中.xlsx
看到什么
文件夹里有两个文件。原文件的修改时间必须保持不变。
卡住了 / 出错了

如果已经不小心改了原文件,重新下载一份即可。但从今天起记住:原始数据永远只读。

02

取消合并单元格

2 分钟 · 对应规则 3
做什么
把 A1:E1 拆开,让每一格独立。
为什么
规则 3 说过:合并单元格只有左上角有值,其余四格是空的。不拆开,后面的排序、筛选、透视表全会错位。
怎么做
  1. 选中 A1:E1(从 A1 拖到 E1)
  2. 点 开始 → 合并后居中(此按钮此时是按下状态,点一下取消)
看到什么
只有 A1 有「6 月订单明细」,B1 到 E1 变成空白,格子之间出现竖线。
卡住了 / 出错了

弹出「只保留左上角的值」→ 点确定,文字还在 A1。

找不到按钮:在「开始」选项卡的「对齐方式」组里,图标是一个带箭头的方框。

03

删掉多余的第 1 行

1 分钟 · 对应规则 4
做什么
让表头回到第一行。
为什么
规则 4:一维表只能有一个表头行,而且必须在第一行。标题行留着,Excel 会把「6 月订单明细」当成字段名。
怎么做
  1. 右键最左边的行号 1
  2. 选 删除
看到什么
第一行变成「订单号 / 下单时间 / 客户 / 金额 / 备注」,左下角行数变成 481。
04

把金额转成数值 核心

5 分钟 · 对应规则 1
做什么
让 40 个文本金额变回数值,使 SUM 能正确求和。
为什么
规则 1:文本不参与求和。这 40 个单元格带千分位逗号,Excel 把它们当字符串,SUM 时直接跳过。不修,你永远少算 9 万多。
怎么做
  1. 点列标 D,选中整列
  2. 按 Ctrl + H:查找 ,,替换为空 → 全部替换(先去掉千分位逗号)
  3. 保持 D 列选中 → 数据 → 分列
  4. 弹窗第一步直接点 完成(不要改任何选项)
看到什么
金额全部靠右对齐,绿三角消失。选中 D 列,状态栏出现「求和:1,208,928.65」。
分列按钮位置与错误求和
图 6.5.4 「数据 → 分列」的位置,以及转换前状态栏的错误求和
卡住了 / 出错了

替换了逗号还是不能求和:替换只去掉字符,单元格仍然是文本。必须再做一次「分列」。

分列后金额被劈成两列:说明你在分列时勾了逗号作分隔符。按 Ctrl+Z 撤销,重做,保持默认只勾 Tab。

状态栏没有求和:右键状态栏,把「求和」勾上。

05

把 5 种日期统一 核心

5 分钟 · 对应规则 2
做什么
把文本日期转成真正的日期类型,格式统一为 2024-06-01。
为什么
规则 2:日期是序列号,文本日期只是长得像。不转成真日期,你没法按月汇总、没法算账龄、没法做同期群。
怎么做
  1. 点列标 B,选中整列
  2. 数据 → 分列 → 下一步 → 下一步
  3. 第三步:列数据格式选 日期,右侧选 YMD → 完成
看到什么
B 列全部变成 2024-06-01 并靠右对齐。点任意一格,编辑栏显示 2024/6/1。
卡住了 / 出错了

显示 ####:列太窄,拉宽 B 列。

有几个变成 1900 年:那几格没被识别,单独选中再分列一次。

关于 6/4/2024:这批数据月份都是 6 月,所以它一定是 6 月 4 日。换一批数据,必须先问清楚是「月/日/年」还是「日/月/年」。

06

删除重复订单 核心

3 分钟 · 对应规则 4
做什么
同一个订单号只保留一条记录。
为什么
一张订单表的粒度是“一个订单”,订单号必须唯一。重复会让 GMV 被算两遍,这是月报虚高最直接的原因。
怎么做
  1. 点数据区任意单元格,按 Ctrl + A 选中整张表
  2. 数据 → 删除重复值
  3. 只勾选「订单号」,其余列的勾全部取消
  4. 点 确定
看到什么
提示「发现并删除了 20 个重复值,保留了 460 个唯一值」。
为什么只能勾订单号

两条重复记录的备注不一样:一条空着,一条写着「H5 补录」。

如果全勾,Excel 会认为这是两条不同的记录,一个都删不掉。你必须告诉它:判断重复的标准是订单号。

卡住了 / 出错了

删完还是 480 行:说明勾了全部列。撤销重来。

提示「未找到重复值」:可能只选中了部分区域,Ctrl+A 重选。

07

那笔 99,999,别删

3 分钟 · 判断
做什么
确认它是真实订单,保留,并写进清洗记录。
为什么
异常 ≠ 错误。它金额是整数、备注写着「企业采购」、客户名正常——三个信号都指向真实业务。删掉,总额少 10 万,你无法解释。
怎么做
  1. 选中 D 列 → 数据 → 降序
  2. 看第一行:99,999.00,备注「企业采购」
  3. 不修改,记进清洗记录
  4. 选中 A 列 → 按订单号升序,恢复顺序
看到什么
总额里包含这 99,999。删掉它,合计会变成 1,108,929.65,和财务永远对不上。
拿不准怎么办

问业务方一句:「6 月 19 日这笔 99999 是企业单吗?」问一句,比猜一小时强。

08

核对、另存、写记录

10 分钟 · 对应铁律 3、4
做什么
证明结果正确,并让别人能复现。
为什么
清洗完不算完,能解释清楚才算完。财务问“为什么你的数和我差 3 万”,你要能一条条指出来。
怎么做
  1. 选中 D 列,看状态栏求和
  2. 按 Ctrl + End,看最后一行行号
  3. 文件 → 另存为 → 6月订单明细_清洗后.xlsx
  4. 打开清洗记录模板,补完 6 条处理记录
看到什么
求和 1,208,928.65;最后一行 461(1 行表头 + 460 行数据)。

6.6结果验证

正点原子在「8.4 编译下载验证」里,是通过串口打印和 LED 亮灭来确认程序跑通的。 数据清洗的“现象”,就是两个数字:行数和金额。填进来,看看你对不对。

6.6.1两个数字

看 Excel 左下角的行号,减 1。
选中 D 列,看状态栏求和。

6.6.2五道判断题

不看前面的正文,凭第一反应选。选完立刻出解析。

Q1两条记录的订单号相同、备注不同。执行「删除重复值」时应该怎么做?
解析:重复的判定标准必须由你定义。两条记录只是备注不同,全勾时 Excel 会认为它们不重复,一个都删不掉。业务上的「同一条」是订单号。
Q2金额列靠左对齐、左上角有绿三角,选中整列求和偏小。最可能的原因是什么?
解析:靠左 + 绿三角是文本型数字的两个典型特征。SUM 只加数值,遇到文本不报错、直接略过——这是清洗里最隐蔽的错误。
Q3日期列用「分列 → 日期 → YMD」转换成功后,应该看到什么?
解析:真日期靠右,因为它的本质是序列号 45444,只是显示成了日期。变成纯数字说明单元格格式是「常规」,不是失败——把格式改成日期即可。
Q4金额列里有一笔 99,999.00,明显高于其他订单。正确处理是?
解析:异常不等于错误。备注写着「企业采购」、金额是整数、客户名正常,三个信号都指向真实业务。删掉会让总额少 10 万,而且没法向财务解释。
Q5一次清洗做完,应该交付什么?
解析:没有记录,别人无法复现,你也没法解释「为什么和财务差 3 万」。交付物永远是两份:结果 + 过程。
得分:0 / 5

6.7常见问题排查

按“现象 → 原因 → 解决”整理,遇到问题先在这里找。

现象原因解决
求和比预期少 有文本型数字被跳过(规则 1) 选中列 → 数据 → 分列 → 完成
日期没法按月分组 日期是文本,不是序列号(规则 2) 选中列 → 数据 → 分列 → 日期 YMD
透视表报错 / 数据错位 有合并单元格(规则 3) 选中整表 → 开始 → 合并后居中(取消)
删除重复值提示“未找到重复” 勾了全部列,而两行备注不同 只勾主键列,通常是订单号 / 用户 ID
删完重复,行数没变 没有选中整张表,只选了一部分 点数据区 → Ctrl+A → 再操作
金额列出现 #### 列太窄,不是数据错误 双击列边界自动调宽

6.8本章小结与练习

  1. 清洗的本质是修复“类型和结构”,不是删数据。删是最容易的,也是最容易删错的。
  2. 四条底层规则:五种单元格类型、日期是序列号、合并单元格只存一个值、透视表只认一维表。
  3. 四条铁律:原始只读、先体检后治疗、每个动作留痕、结果可复现。
  4. 四个核心操作:取消合并、分列转数值、分列转日期、按主键去重。
  5. 交付物不是“洗干净的表”,是“表 + 清洗记录”。没有记录,等于没洗。

6.8.1练习

  1. 如果不做第 4 步,D 列的求和会是多少?和正确答案差多少?
  2. 如果第 6 步把五列全勾上,会删除几行?为什么?
  3. 那笔 99,999 如果删掉,会对“平均客单价”产生多大影响?自己算一下。
  4. 把这批数据按“渠道”拆开(提示:备注里的 H5 补录),哪个渠道的重复率最高?
参考答案

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 补录,需要查上报链路。

下一章预告:第四章《月报自动化》会把本章的 8 个动作做成一个点一下就刷新的模板, 把每周 4 小时的手工活压到 10 分钟。前提是——本章的四条规则你已经真的理解了。
数据分析师训练营 · 第六章
Excel / WPS 均可,菜单名可能略有差异