Excel · 第 1 篇
数据清洗与整理
“数据分析师 80% 的时间花在数据清洗上。”——这不是夸张，而是现实。
拿到一份原始数据，你会发现它充满了各种”脏东西”：重复行、空值、格式不统一、错别字、异常值…如果不先把数据洗干净，后面的分析全部白搭。
为什么数据清洗如此重要
| 脏数据问题 | 对分析的影响 |
|---|---|
| 重复记录 | 统计指标虚高（比如订单量多算了） |
| 缺失值 | 计算平均值等统计量时结果失真 |
| 格式不统一 | 相同的数据被当成不同类别（如”北京”和”北京市”） |
| 异常值 | 一条错误数据可能拉偏整体平均值 |
| 数据类型错误 | 数字被存成文本，无法计算 |
第一步：拿到数据先做什么
打开一份新的数据文件后，按以下顺序快速了解数据：
1. 看数据规模
- 按
Ctrl + End跳到数据最后一行，确认数据有多少行、多少列 - 或者在状态栏看”计数”
2. 看列名（字段）
确认每列代表什么含义。如果列名是英文或缩写，先建一张字段说明表：
| 列名 | 含义 | 数据类型 | 示例 |
|---|---|---|---|
| order_id | 订单编号 | 文本 | ORD20250301001 |
| user_id | 用户编号 | 文本 | U10023 |
| order_date | 下单日期 | 日期 | 2025-03-01 |
| amount | 订单金额 | 数值 | 258.50 |
| city | 城市 | 文本 | 上海 |
3. 看数据样本
随机浏览前 20 行和最后 20 行，快速感知数据质量。
4. 看基本统计量
选中数值列，状态栏会显示”求和、平均值、计数”。快速判断数据范围是否合理。
去除重复数据
方法一：使用”删除重复项”功能
操作路径：数据 → 删除重复项
步骤：
- 选中整个数据区域（
Ctrl + A） - 点击
数据选项卡 →删除重复项 - 勾选用于判断重复的列（通常选所有列，或仅选关键字段如”订单编号”）
- 点击确定，Excel 会告诉你删除了多少条重复记录
方法二：用 COUNTIF 标记重复
如果不想直接删除，而是先标记出来审查：
=COUNTIF($A$2:$A$1000, A2)
- 结果为 1 表示唯一
- 结果 > 1 表示有重复
然后用条件格式高亮大于 1 的行，人工审查后再决定是否删除。
方法三：用条件格式快速识别
操作路径：开始 → 条件格式 → 突出显示单元格规则 → 重复值
这个方法最快，但只能高亮显示，不能自动删除。
实际案例：电商订单去重
你拿到一份电商订单数据，发现有些订单编号出现了多次。可能的原因：
| 情况 | 处理方式 |
|---|---|
| 完全相同的行（系统 bug 导致重复写入） | 直接删除重复行 |
| 订单编号相同但金额不同（退款/修改订单） | 保留最新的一条 |
| 订单编号相同但日期不同（跨天支付） | 根据业务逻辑判断保留哪条 |
处理缺失值
如何发现缺失值
方法一：使用定位功能
- 选中数据区域
Ctrl + G（定位） →定位条件→空值- Excel 会选中所有空单元格
方法二：用 COUNTBLANK 函数统计
=COUNTBLANK(B2:B1000)
统计 B 列中空值的数量。
方法三：用筛选功能
点击筛选按钮，在下拉菜单中勾选”(空白)”，快速查看所有空值行。
缺失值的处理策略
| 策略 | 适用场景 | Excel 操作 |
|---|---|---|
| 删除整行 | 缺失比例低（<5%），且缺失随机 | 筛选空值行后删除 |
| 填充固定值 | 有合理的默认值（如”未知”） | Ctrl + G → 空值 → 输入值 → Ctrl + Enter |
| 填充前/后值 | 时间序列数据，空值应继承前一行 | 定位空值 → 输入 =A1（上一行）→ Ctrl + Enter |
| 填充平均值 | 数值型数据，缺失随机 | 先计算平均值，再填充 |
| 保留不处理 | 缺失本身有业务含义（如”未填写”） | 标记为”未填写”以区分 |
实际案例：用户信息表缺失处理
| 字段 | 缺失率 | 处理方案 |
|---|---|---|
| user_id | 0% | 无需处理 |
| age | 15% | 填充中位数（更稳健），或标记为”未知” |
| gender | 8% | 标记为”未知” |
| city | 3% | 尝试从 IP 地址反查，否则标记为”未知” |
| register_date | 0.1% | 从其他系统交叉查验 |
格式统一化
格式不统一是最隐蔽也最烦人的问题。同一个意思可能有 N 种写法。
文本格式统一
常见问题和解决方案：
| 问题 | 示例 | 解决方案 |
|---|---|---|
| 多余空格 | ” 北京 “ | =TRIM(A2) 去除首尾空格 |
| 全角半角混用 | “１２３” vs “123” | =ASC(A2) 转为半角 |
| 大小写不一致 | “Beijing” vs “BEIJING” | =UPPER(A2) 或 =LOWER(A2) 或 =PROPER(A2) |
| 换行符/制表符 | 单元格内有隐藏字符 | =CLEAN(A2) 去除不可打印字符 |
| 同义词不统一 | “北京”/”北京市”/”BJ” | 用查找替换或 VLOOKUP 映射表 |
综合清洗公式：
=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), "")))
这个公式同时处理了不可打印字符、不间断空格和首尾空格。
日期格式统一
日期格式混乱是最常见的问题之一：
| 原始格式 | 统一目标 |
|---|---|
| 2025-03-01 | 2025/3/1 |
| 03/01/2025 | 2025/3/1 |
| 2025年3月1日 | 2025/3/1 |
| 20250301 | 2025/3/1 |
处理方法：
- 选中日期列
Ctrl + 1打开”设置单元格格式”- 选择”日期”类别，选择统一的日期格式
- 如果日期被识别为文本，用
=DATEVALUE(A2)转换
处理”20250301”格式的文本日期：
=DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2))
数值格式统一
| 问题 | 解决方案 |
|---|---|
| 数字被存为文本 | 选中列 → 数据 → 分列 → 完成（强制转换） |
| 金额带货币符号 | =SUBSTITUTE(SUBSTITUTE(A2,"¥",""),"$","") * 1 |
| 百分比格式 | “25%” 和 “0.25” 混用 → 统一为数值 |
| 千分位逗号 | “1,234,567” → =SUBSTITUTE(A2,",","") * 1 |
文本处理技巧
分列（Text to Columns）
场景：一列”姓名-部门”需要拆成两列。
操作路径：数据 → 分列 → 选择分隔符（如”-“）
合并（CONCATENATE / &）
场景：把”年”、”月”、”日”三列合并成一个日期。
=A2 & "-" & B2 & "-" & C2
或使用 TEXTJOIN（Excel 2019+）：
=TEXTJOIN("-", TRUE, A2, B2, C2)
提取文本
| 需求 | 公式 |
|---|---|
| 提取前 4 个字符 | =LEFT(A2, 4) |
| 提取后 3 个字符 | =RIGHT(A2, 3) |
| 提取中间字符 | =MID(A2, 5, 3) — 从第 5 位开始取 3 个字符 |
| 提取数字 | 需要用嵌套公式或 VBA |
| 查找特定文本位置 | =FIND("@", A2) — 返回”@”的位置 |
实际案例：从邮箱提取域名
=MID(A2, FIND("@", A2) + 1, LEN(A2) - FIND("@", A2))
| 原始邮箱 | 提取结果 |
|---|---|
| [email protected] | gmail.com |
| [email protected] | company.cn |
数据验证
数据清洗完成后，需要验证清洗结果是否正确。
验证清单
| 检查项 | 方法 |
|---|---|
| 总行数是否正确 | 清洗前后行数对比 |
| 是否还有重复 | 再次运行”删除重复项”检查 |
| 是否还有空值 | COUNTBLANK 检查关键列 |
| 数值范围是否合理 | MIN、MAX 检查极值 |
| 分类值是否统一 | 用”数据验证”或透视表检查唯一值列表 |
| 日期范围是否正确 | 检查 MIN(日期) 和 MAX(日期) |
用数据透视表验证分类字段
对于”城市”、”渠道来源”这类分类字段，用透视表可以快速看到所有唯一值：
- 插入透视表
- 把分类字段拖到”行”区域
- 检查是否有异常的分类值（如错别字、空格导致的重复分类）
常用快捷键速查
| 操作 | Windows 快捷键 | Mac 快捷键 |
|---|---|---|
| 选中整个数据区域 | Ctrl + Shift + End |
Cmd + Shift + End |
| 跳到数据末尾 | Ctrl + End |
Cmd + End |
| 定位空值 | Ctrl + G → 空值 |
Ctrl + G → 空值 |
| 查找替换 | Ctrl + H |
Cmd + H |
| 批量填充 | 选中 → 输入 → Ctrl + Enter |
选中 → 输入 → Ctrl + Enter |
| 筛选 | Ctrl + Shift + L |
Cmd + Shift + L |
| 删除整行 | Ctrl + - |
Ctrl + - |
| 插入整行 | Ctrl + Shift + + |
Ctrl + Shift + + |
实战练习：清洗一份电商用户数据
假设你拿到一份包含以下问题的用户数据：
| 问题 | 涉及字段 | 数据示例 |
|---|---|---|
| 姓名有多余空格 | name | ” 张三 “ |
| 手机号格式不一 | phone | “138-1234-5678” / “13812345678” |
| 性别写法不统一 | gender | “男”/”M”/”male”/”1” |
| 注册日期格式混乱 | reg_date | “2025-01-15” / “20250115” / “2025/1/15” |
| 城市名不统一 | city | “上海”/”上海市”/”Shanghai” |
| 年龄有异常值 | age | 150、-5、0 |
| 邮箱有空值 | (空) |
清洗步骤：
- 姓名：
=TRIM(A2)去除空格 - 手机号：
=SUBSTITUTE(SUBSTITUTE(B2, "-", ""), " ", "")去除连字符和空格 - 性别：建立映射表，用 VLOOKUP 统一为”男”/”女”/”未知”
- 注册日期：用 IF + DATEVALUE + DATE 函数组合统一格式
- 城市：查找替换 “上海市”→”上海”，”Shanghai”→”上海”
- 年龄：用
=IF(OR(F2<0, F2>120), "异常", F2)标记异常值 - 邮箱：
COUNTBLANK统计空值率，决定是否填充”未填写”
练习题
-
你拿到一份销售数据，发现”产品名称”列有以下写法：”iPhone 15”、”iphone 15”、”IPHONE 15”、”iPhone15”。请设计一个清洗方案把它们统一。
-
一列日期数据混合了 “2025-03-01”、”03/01/2025”、”2025年3月1日” 三种格式。请写出将它们统一转换为日期类型的公式。
-
你拿到一份 10 万行的用户数据。请设计一个完整的数据清洗流程（包括检查项和清洗步骤），确保数据可以用于后续分析。
小结
| 要点 | 说明 |
|---|---|
| 第一步 | 拿到数据先看规模、字段、样本、统计量 |
| 去重 | 删除重复项功能，或 COUNTIF 标记后审查 |
| 缺失值 | 根据业务场景选择删除、填充或保留 |
| 格式统一 | TRIM、CLEAN、UPPER/LOWER、日期格式转换 |
| 数据验证 | 清洗后必须验证，不要假设数据已经干净 |
下一篇我们将学习 透视表与数据汇总——用 Excel 最强大的分析工具，快速做分组统计和交叉分析。