夜雨聆风别再被杂乱数据折磨了，这些方法让你1分钟搞定！
数据分析中有个很重要的预处理步骤，叫做数据清洗。简单来说，就是把数据中“脏脏的”部分——缺失的、重复的、错误的等等——给它清除掉，剩下“干净的”数据。
工作中我们经常会遇见一些乱七八糟的表格：数据不规范、行列不整齐、数据挤在一个单元格里……这种表格往往会影响我们对数据进行运算和分析。今天就来聊聊Excel数据清洗的常用方法，帮你快速把杂乱数据变规范。
⚠️ 清洗前必做一件事：操作前务必先备份数据，复制到另一个工作表或工作簿中。这能让你在操作失误时有个“后悔药”。
一、文本清理：去掉多余空格和不可见字符
数据中最隐蔽也最常见的问题就是多余空格和不可见字符。“张三 ”和“张三”在Excel眼中是两个完全不同的字符串，会让VLOOKUP、COUNTIF等查找匹配函数失效。
✅ 解决方案：TRIM + CLEAN 组合拳
TRIM函数：删除文本开头、结尾和单词之间多余的空格，只保留一个空格。用法：
=TRIM(A2)CLEAN函数：清理文本中的非打印字符（从其他系统导入时常混入）
更彻底的做法是两者嵌套使用：
=TRIM(CLEAN(A2))先清除不可见字符，再去掉多余空格。
💡 小贴士：如果遇到TRIM清除不了的空格（如从网页复制的不间断空格），可以用这个公式：
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
二、统一文本格式：大小写规范化
英文名、产品编码、邮箱地址在不同记录中大小写不同——“JOHN”、“john”、“John”会被当作三个不同的值。
✅ 三个函数搞定大小写：
| 函数 | 作用 | 示例 |
|---|---|---|
=UPPER(A2) | 全部转大写 | john → JOHN |
=LOWER(A2) | 全部转小写 | JOHN → john |
=PROPER(A2) | 首字母大写 | john doe → John Doe |
处理完记得复制粘贴为数值，替换掉原数据。
三、拆分数据：把“一锅炖”的数据分开
有时候一个单元格里塞了多种信息：“张三经理”、“广东省深圳市南山区”，无法按字段筛选或分组统计。
✅ 方法一：分列功能（最常用）
选中数据列 →「数据」→「分列」→ 选择「分隔符号」→ 选择分隔符（逗号、空格等）→ 完成
✅ 方法二：TEXTSPLIT函数（Excel新版）
=TEXTSPLIT(A2,"、") 可以按指定分隔符把文本拆分成多列。
✅ 方法三：快速填充（Ctrl+E）
在相邻列输入一个你想要的结果示例，按Ctrl+E，Excel会自动识别规律并填充。
四、删除重复数据
重复的条目会严重扭曲分析结果。
✅ 一键去重：选中数据区域 →「数据」→「删除重复项」→ 选择要检查重复的列 → 确定
如果想标记而非删除重复值，可以用条件格式：「开始」→「条件格式」→「突出显示单元格规则」→「重复值」。
五、处理缺失数据
数据中缺了一两块怎么办？
✅ 方法一：删除缺失行（数据量小、缺失少时）
按F5键 →「定位条件」→「空值」→ 确定，右键删除整行。
✅ 方法二：填充固定值
同样用Ctrl+G定位空值，输入0或N/A等填充值，按Ctrl+Enter批量填充。
✅ 方法三：填充统计值
用平均值、中位数等统计值填充。比如用=AVERAGE(区域)算出平均值后填入空缺。
六、处理错误值
✅ IFERROR函数：在公式外套上=IFERROR(原公式, "替代值")，错误时显示你指定的内容。
对于“不该出现的数据”（比如等级只有A/B/C却出现了D），可以用查找替换或筛选直接定位并修正。
七、规范化日期格式
日期格式五花八门是最让人头疼的问题之一——2019/9/1、2020.11.10、20200119混在一起。
✅ 分列法统一日期：
选中日期列 →「数据」→「分列」→「下一步」→「下一步」→ 勾选「日期」→ 选择「YMD」→ 完成
八、绿色三角：文本型数字转数值
复制进来的数据左上角出现绿色三角，无法正常运算。
✅ 一键转换：选中带绿色三角的单元格 → 点击旁边的感叹号 → 选择「转换为数字」
九、进阶利器：Power Query
如果数据清洗工作量大且需要反复操作，Power Query是你的不二之选。
通过「数据」→「获取数据」导入数据，在Power Query编辑器中进行清洗（去重、拆分列、更改数据类型、填充空值等），最后点击「关闭并加载」。最关键的是：所有操作步骤都会被记录，下次更新数据时一键刷新即可。
十、预防胜于清洗
与其事后清洗，不如提前预防：
数据验证：「数据」→「数据验证」，设置输入规则，避免不规范数据产生
条件格式：高亮显示异常值，第一时间发现问题
规范录入：用数字格式添加单位，而非手动输入（如“100元”）
写在最后
数据清洗不是一次性动作，而是数据分析前必须完成的标准步骤。宁可花10分钟把数据洗到100%干净，也不愿后面返工1小时。
掌握以上这些方法，面对再乱的表格也能从容应对。你学会了吗？ 下次遇到杂乱数据，试试这些技巧，让Excel帮你1分钟搞定！