跳到主要内容

Excel数据清洗与整理

本章概览​

数据清洗是数据分析的基础工作,占据数据分析工作量的60-80%。脏数据会导致错误的分析结果,因此掌握数据清洗技能至关重要。本章将系统讲解Excel中的数据清洗方法和技巧。

学习目标

  • 识别常见的数据质量问题
  • 掌握文本处理和格式统一方法
  • 学会处理重复值和缺失值
  • 熟练运用数据清洗函数
  • 掌握Power Query数据清洗技巧

21.1 数据质量问题识别​

21.1.1 常见数据质量问题​

格式不统一

# 日期格式混乱
2024-01-01
2024/1/1
01/01/2024
2024年1月1日

# 数值格式不一致
1000
1,000
1000.00
1000元

多余空格

" 张三"        # 前导空格
"张三 "        # 尾随空格
"张  三"       # 中间多余空格

大小写混乱

BEIJING
Beijing
beijing
BeiJing

重复数据

编号   姓名   部门
001   张三   销售
001   张三   销售    # 完全重复
002   李四   技术
002   李四   市场    # 部分重复

缺失值

姓名   部门   工资
张三   销售   8000
李四          9000    # 部门缺失
王五   技术            # 工资缺失

异常值

年龄: 5, 28, 35, 150, 42  # 150为异常值
工资: 8000, 9000, -500, 12000  # 负数为异常

21.1.2 数据检查方法​

排序检查法

  • 按列排序查看极值
  • 发现空值、异常值
  • 检查数据范围

筛选检查法

  • 使用自动筛选
  • 查看唯一值列表
  • 发现不规范数据

条件格式法

# 高亮重复值
选中区域 → 条件格式 → 突出显示单元格规则 → 重复值

# 高亮空白单元格
条件格式 → 新建规则 → 使用公式 → =ISBLANK(A1)

# 高亮异常值
条件格式 → 新建规则 → 只为包含以下内容的单元格设置格式

公式检查法

# 检查重复
=COUNTIF($A$1:$A$100,A1)>1

# 检查空值
=ISBLANK(A1)

# 检查数据类型
=ISNUMBER(A1)
=ISTEXT(A1)

21.2 文本数据清洗​

21.2.1 去除空格​

TRIM函数

=TRIM(A1)    # 删除前导/尾随空格,中间多余空格转为单个空格

示例

原始数据: "  张  三  "
结果: "张 三"

SUBSTITUTE替换法

=SUBSTITUTE(A1," ","")    # 删除所有空格

批量处理

  1. 辅助列输入公式
  2. 复制结果
  3. 选择性粘贴为值
  4. 删除原列和辅助列

21.2.2 统一大小写​

转换函数

=UPPER(A1)    # 全部大写: "BEIJING"
=LOWER(A1)    # 全部小写: "beijing"
=PROPER(A1)   # 首字母大写: "Beijing"

应用场景

# 身份证号统一大写
=UPPER(A1)

# 英文姓名规范化
=PROPER("john smith")  → "John Smith"

# 邮箱地址统一小写
=LOWER(A1)

21.2.3 提取和拆分文本​

LEFT/RIGHT/MID函数

=LEFT(A1,3)           # 从左提取3个字符
=RIGHT(A1,4)          # 从右提取4个字符
=MID(A1,2,5)          # 从第2位开始提取5个字符

示例:身份证号提取信息

身份证: 110101199001011234

出生年份: =MID(A1,7,4)   → 1990
出生月份: =MID(A1,11,2)  → 01
出生日期: =MID(A1,13,2)  → 01
性别: =IF(MOD(MID(A1,17,1),2)=0,"女","男")

FIND/SEARCH函数定位

=FIND("@",A1)         # 查找@位置(区分大小写)
=SEARCH("@",A1)       # 查找@位置(不区分大小写)

# 提取邮箱用户名
=LEFT(A1,FIND("@",A1)-1)

# 提取邮箱域名
=RIGHT(A1,LEN(A1)-FIND("@",A1))

分列功能

  1. 选中数据列
  2. 数据 → 分列
  3. 选择分隔符或固定宽度
  4. 完成

21.2.4 合并文本​

&运算符

=A1&B1               # 直接连接
=A1&" "&B1           # 中间加空格
=A1&"-"&B1           # 中间加连字符

CONCATENATE/CONCAT函数

=CONCATENATE(A1,B1,C1)    # 老版本
=CONCAT(A1:C1)            # 新版本,支持区域

TEXTJOIN函数(推荐)

=TEXTJOIN(分隔符, 忽略空值, 文本区域)

# 示例
=TEXTJOIN("-",TRUE,A1:C1)
# 结果: "北京-朝阳-望京"

# 忽略空值
A1="北京", B1="", C1="望京"
=TEXTJOIN("-",TRUE,A1:C1)  → "北京-望京"
=TEXTJOIN("-",FALSE,A1:C1) → "北京--望京"

21.2.5 替换和清除​

SUBSTITUTE函数

=SUBSTITUTE(原文本, 旧文本, 新文本, [第几个])

# 示例
=SUBSTITUTE("010-12345678","-","")  → "01012345678"
=SUBSTITUTE("aaa","a","b",2)        → "aba" (只替换第2个)

REPLACE函数

=REPLACE(原文本, 起始位置, 字符数, 新文本)

# 示例
=REPLACE("13812345678",4,4,"****")  → "138****5678"

CLEAN函数

=CLEAN(A1)    # 删除不可打印字符(如换行符)

TRIM+CLEAN组合

=TRIM(CLEAN(A1))    # 同时处理空格和不可打印字符

21.3 数值数据清洗​

21.3.1 文本转数值​

常见问题

  • 数值前有单引号 '1000
  • 数值后有单位 "1000元"
  • 数值中有千分位符号 "1,000"

VALUE函数

=VALUE(A1)    # 将文本转为数值

乘以1法

=A1*1    # 简单快速

处理千分位符

=VALUE(SUBSTITUTE(A1,",",""))
# "1,000" → 1000

提取数字

# 从"1000元"提取数字
=LEFT(A1,LEN(A1)-1)*1

# 通用公式(提取所有数字)
=SUMPRODUCT(MID(0&A1,LARGE(ISNUMBER(--MID(A1,ROW($1:$99),1))*ROW($1:$99),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)

21.3.2 数值格式统一​

ROUND函数族

=ROUND(A1,2)     # 四舍五入到2位小数
=ROUNDUP(A1,2)   # 向上舍入
=ROUNDDOWN(A1,2) # 向下舍入

=INT(A1)         # 向下取整
=TRUNC(A1,2)     # 截断到2位小数

TEXT函数格式化

=TEXT(A1,"0.00")        # 保留2位小数
=TEXT(A1,"#,##0")       # 千分位格式
=TEXT(A1,"0.00%")       # 百分比格式
=TEXT(A1,"¥#,##0.00")   # 货币格式

21.3.3 处理负数和零值​

ABS函数

=ABS(A1)    # 取绝对值

替换负数

=IF(A1<0,0,A1)    # 负数替换为0
=MAX(A1,0)        # 同上,更简洁

处理除零错误

=IFERROR(A1/B1,0)        # 错误时返回0
=IF(B1=0,"",A1/B1)       # B1为0时返回空

21.4 日期时间数据清洗​

21.4.1 日期格式统一​

TEXT函数转换

=TEXT(A1,"yyyy-mm-dd")        # 2024-01-01
=TEXT(A1,"yyyy年m月d日")       # 2024年1月1日
=TEXT(A1,"m/d/yyyy")          # 1/1/2024

DATE函数重组

=DATE(年,月,日)

# 从文本"20240101"提取日期
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

DATEVALUE函数

=DATEVALUE("2024-01-01")    # 文本转日期

21.4.2 提取日期组件​

YEAR/MONTH/DAY函数

=YEAR(A1)     # 提取年份: 2024
=MONTH(A1)    # 提取月份: 1
=DAY(A1)      # 提取日: 1

WEEKDAY函数

=WEEKDAY(A1,2)    # 返回星期几(1-7,周一为1)
=TEXT(A1,"aaaa")  # 返回中文星期几

应用:计算年龄

=DATEDIF(出生日期,TODAY(),"Y")
=YEAR(TODAY())-YEAR(A1)    # 简化版

21.4.3 时间格式处理​

提取小时/分钟/秒

=HOUR(A1)      # 小时
=MINUTE(A1)    # 分钟
=SECOND(A1)    # 秒

时间差计算

=B1-A1         # 时间差(单位:天)
=(B1-A1)*24    # 转换为小时
=(B1-A1)*1440  # 转换为分钟

格式化时间

=TEXT(A1,"h:mm AM/PM")    # 12小时制
=TEXT(A1,"hh:mm:ss")      # 24小时制

21.5 处理重复值​

21.5.1 识别重复值​

条件格式高亮

  1. 选中区域
  2. 条件格式 → 突出显示单元格规则 → 重复值
  3. 选择格式

COUNTIF函数

=COUNTIF($A$1:$A$100,A1)    # >1表示重复

高级筛选

  1. 数据 → 高级筛选
  2. 勾选"选择不重复的记录"
  3. 复制到其他位置

21.5.2 删除重复值​

数据工具

  1. 选中数据区域
  2. 数据 → 删除重复项
  3. 选择用于判断重复的列
  4. 确定

注意事项

  • 操作不可撤销,建议先备份
  • 保留第一次出现的记录
  • 可基于单列或多列组合

21.5.3 唯一值提取​

UNIQUE函数(Microsoft 365)

=UNIQUE(A1:A100)    # 提取唯一值
=UNIQUE(A1:A100,,TRUE)    # 只返回出现一次的值

传统方法:高级筛选

  1. 数据 → 高级筛选
  2. 勾选"选择不重复的记录"
  3. 复制到其他位置

21.6 处理缺失值​

21.6.1 识别缺失值​

ISBLANK函数

=ISBLANK(A1)    # TRUE表示空白

条件格式高亮

新建规则 → 使用公式 → =ISBLANK(A1)

筛选空白单元格

  • 自动筛选 → (空白)

21.6.2 填充缺失值​

方法1:定位空值批量填充

  1. 选中数据区域
  2. Ctrl+G → 定位条件 → 空值
  3. 输入值或公式
  4. Ctrl+Enter批量填充

方法2:向下填充

选中区域 → Ctrl+D    # 向下填充
选中区域 → Ctrl+R    # 向右填充

方法3:使用公式填充

=IF(ISBLANK(A2),A1,A2)    # 空值用上一行填充
=IFERROR(A2,A1)           # 错误或空值用A1

方法4:填充平均值

=IF(ISBLANK(A1),AVERAGE($A$1:$A$100),A1)

21.6.3 删除包含缺失值的行​

筛选后删除

  1. 自动筛选
  2. 取消勾选(空白)
  3. 选中可见行
  4. 右键 → 删除行

定位后删除

  1. 选中列
  2. Ctrl+G → 定位条件 → 空值
  3. 右键 → 删除 → 整行

21.7 数据清洗函数组合​

21.7.1 综合清洗公式​

清洗姓名

# 去空格+规范大小写
=PROPER(TRIM(A1))

# 示例
"  john  SMITH  " → "John Smith"

清洗电话号码

# 删除所有非数字字符
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-","")," ",""),"(",""),")")

# 更通用:只保留数字
=SUMPRODUCT(MID(0&A1,LARGE(ISNUMBER(--MID(A1,ROW($1:$50),1))*ROW($1:$50),ROW($1:$50))+1,1)*10^ROW($1:$50)/10)

清洗邮箱

# 转小写+去空格
=LOWER(TRIM(A1))

21.7.2 数据验证公式​

验证邮箱格式

=AND(ISNUMBER(FIND("@",A1)),ISNUMBER(FIND(".",A1)),LEN(A1)>5)

验证手机号

=AND(LEN(A1)=11,ISNUMBER(A1*1),LEFT(A1,1)="1")

验证身份证号

=AND(LEN(A1)=18,ISNUMBER(VALUE(LEFT(A1,17))))

21.8 Power Query数据清洗​

21.8.1 Power Query简介​

优势

  • 可视化操作
  • 步骤可追溯
  • 批量处理
  • 自动化刷新

启动Power Query

  • 数据 → 获取数据 → 从工作表/从表/范围

21.8.2 常用清洗操作​

删除列/行

  • 右键列标题 → 删除
  • 右键行 → 删除

删除重复项

  • 主页 → 删除行 → 删除重复项

填充空值

  • 选中列 → 转换 → 填充 → 向下/向上

替换值

  • 右键列 → 替换值
  • 输入旧值和新值

拆分列

  • 选中列 → 拆分列 → 按分隔符/字符数

合并列

  • 选中多列 → 转换 → 合并列
  • 选择分隔符

21.8.3 数据类型转换​

更改类型

  • 点击列标题左侧图标
  • 选择数据类型:文本/数字/日期/时间

提取日期组件

  • 日期列 → 添加列 → 日期 → 年/月/日/星期

21.8.4 应用步骤​

查看步骤

  • 右侧窗格显示所有操作步骤
  • 可删除、重命名、调整顺序

应用更改

  • 主页 → 关闭并上载
  • 数据加载到新工作表或现有表

刷新数据

  • 右键表 → 刷新
  • 重新执行所有清洗步骤

21.9 实战案例​

案例1:清洗客户联系表​

原始数据问题

姓名        电话             邮箱
" 张三 "   138-1234-5678   ZHANG@EMAIL.COM
李四        13987654321      li@email.com
王五        139 8765 4321   WANG@email.com

清洗步骤

# 1. 清洗姓名(去空格)
=TRIM(A2)

# 2. 清洗电话(删除分隔符)
=SUBSTITUTE(SUBSTITUTE(B2,"-","")," ","")

# 3. 清洗邮箱(转小写+去空格)
=LOWER(TRIM(C2))

结果

姓名    电话          邮箱
张三    13812345678  zhang@email.com
李四    13987654321  li@email.com
王五    13987654321  wang@email.com

案例2:销售数据标准化​

原始数据

日期          金额
2024/1/1     ¥1,000
01-02-2024   2000元
2024.1.3     3000

清洗步骤

# 1. 统一日期格式
=TEXT(DATEVALUE(A2),"yyyy-mm-dd")

# 2. 提取纯数字金额
=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"¥",""),",",""),"元",""))

案例3:去重并统计​

需求 从订单表中提取唯一客户,并统计订单数

方法

# 1. 删除重复项(数据工具)
选中客户列 → 删除重复项

# 2. 统计订单数
=COUNTIF(原始数据!$A:$A,A2)

# 3. 或使用数据透视表
行:客户
值:订单号(计数)

21.10 数据清洗最佳实践​

21.10.1 清洗流程​

标准流程

  1. 数据检查:了解数据质量问题
  2. 备份数据:保留原始数据
  3. 制定计划:列出清洗步骤
  4. 执行清洗:使用函数或工具
  5. 验证结果:检查清洗效果
  6. 文档记录:记录清洗过程

21.10.2 注意事项​

使用辅助列

  • 不要直接修改原始数据
  • 公式写在辅助列
  • 验证无误后粘贴为值

分步骤处理

  • 一次处理一个问题
  • 便于检查和调试
  • 最后合并公式

保留原始数据

  • 复制工作表进行清洗
  • 或使用Power Query(不修改源数据)

记录清洗规则

  • 文档化清洗步骤
  • 便于重复使用
  • 团队协作时共享

21.10.3 效率提升技巧​

快捷键

  • Ctrl+H:查找替换
  • Ctrl+G:定位
  • Ctrl+D:向下填充
  • Ctrl+E:快速填充

批量操作

  • 定位空值批量填充
  • 公式复制后粘贴为值
  • 使用Power Query批处理

创建清洗模板

  • 常用清洗步骤保存为模板
  • Power Query保存查询步骤
  • VBA录制清洗宏

21.11 数据质量保障​

21.11.1 数据验证​

设置数据验证规则

数据 → 数据验证
- 整数:年龄 0-120
- 日期:入职日期 <= 今天
- 自定义:手机号长度=11

验证公式示例

# 邮箱验证
=AND(ISNUMBER(FIND("@",A1)),LEN(A1)>5)

# 身份证验证
=LEN(A1)=18

# 不允许重复
=COUNTIF($A$1:$A$1000,A1)<=1

21.11.2 数据录入规范​

统一标准

  • 制定数据字典
  • 规定字段格式
  • 使用下拉列表

模板设计

  • 预设格式和验证
  • 添加说明和示例
  • 保护公式单元格

本章小结​

核心要点

  1. 问题识别:了解常见数据质量问题
  2. 文本清洗:TRIM、SUBSTITUTE、TEXT等函数
  3. 数值清洗:VALUE、ROUND、格式统一
  4. 日期清洗:TEXT、DATE、DATEVALUE
  5. 重复值:删除重复项、UNIQUE函数
  6. 缺失值:识别、填充或删除
  7. Power Query:可视化数据清洗工具

清洗函数速查

问题函数
空格TRIM
大小写UPPER/LOWER/PROPER
提取文本LEFT/RIGHT/MID
查找位置FIND/SEARCH
替换SUBSTITUTE/REPLACE
文本转数值VALUE
文本转日期DATEVALUE
合并文本TEXTJOIN/CONCAT
去除重复UNIQUE(365)/删除重复项
处理空值ISBLANK/IFERROR

学习建议

  • 养成数据检查习惯
  • 熟练掌握清洗函数
  • 学会使用Power Query
  • 建立清洗流程规范

下一步学习

  • 第20章:Excel常用函数详解
  • 第22章:Excel数据透视表精通
  • 第23章:Excel高级函数应用

思考练习​

  1. 清洗一份包含多余空格、格式不统一的客户信息表
  2. 从身份证号中批量提取出生日期和性别
  3. 统一混乱的日期格式(如"2024/1/1"、"01-01-2024"等)
  4. 清洗包含单位的数值数据(如"1000元"、"¥2,000")
  5. 使用Power Query清洗多个工作表的数据并合并

练习数据集 可以从网上下载真实数据集(如政府开放数据),这些数据通常需要清洗。