ARTICLE · 1147025
Excel数据清洗怎么做?7个常用方法一次讲清
从系统导出一份Excel表格，看着数据挺完整，真正用起来却发现各种问题：
姓名前后多了空格，查找时匹配不上 明明是一列数字，SUM求和却算不出来 表格里夹着大量空白行 同一个客户出现好几次，不知道该删哪条 日期格式乱七八糟，排序结果也不对
这些问题单独看都不难，但如果有几百、几千行数据，一个个手动修改就很麻烦。
其实，这些都属于Excel里的数据清洗。
这篇就用7个常见场景，讲清楚Excel数据清洗怎么做，以及处理时需要注意什么。
一、清理多余空格：TRIM函数
假设A列是员工姓名。
表面上看都是“张三”，但有些单元格实际上是：
" 张三 ""张三 ""张 三"
这些多余空格可能导致查找、匹配和去重出现问题。
最常用的处理方法是TRIM函数。
在B2输入：
=TRIM(A2)
然后向下填充。
TRIM会删除普通文本中多余的空格，保留单词之间的单个普通空格。
例如：
" 张三 " → "张三"
但如果原始内容是：
"张 三" → "张 三"
中间仍然会保留一个空格。
如果确认姓名里不应该有任何普通空格，也可以用：
=SUBSTITUTE(A2," ","")
这会把所有普通半角空格删除。
需要注意：
TRIM主要处理普通空格。如果数据从网页、系统或其他软件复制过来，里面可能含有不间断空格，单纯使用TRIM未必有效。
可以进一步尝试：
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
先把常见的不间断空格替换成普通空格，再进行清理。
如果是英文姓名、英文地址等数据，不要直接删除所有空格，否则可能把原本正确的内容连在一起。
二、清理不可见字符：CLEAN函数
有时候单元格看起来没有问题，但复制到其他地方后，出现莫名其妙的换行或异常字符。
比如从某些系统导出的备注：
“订单已完成”
实际内容里却夹着制表符、换行符等控制字符。
这种情况可以试试CLEAN函数：
=CLEAN(A2)
它可以删除文本中一部分不可打印字符，尤其是ASCII编码0～31范围内的控制字符。
如果希望同时清理普通多余空格，可以组合使用：
=TRIM(CLEAN(A2))
但要注意，CLEAN并不能清理所有Unicode不可见字符。
另外，如果单元格中的换行本来就是为了区分地址、备注内容，直接清理可能让文字连在一起。
所以清理之前，先判断这些字符到底是异常数据，还是原本就有用的排版。
三、数字变成文本：解决求和为0的问题
这是我觉得最值得新手掌握的一类问题。
比如从财务系统导出销售金额：
120035004800
看起来全是数字，输入：
=SUM(B2:B100)
结果却是0，或者比预期少很多。
原因可能是：这些数据看起来是数字，Excel实际上却把它们当成了文本。
可以先用一个公式判断：
=ISNUMBER(B2)
如果返回TRUE，说明B2是数值。
如果返回FALSE，说明它不是数值，可能是文本，也可能是其他类型。
对于普通的文本数字，有三种常见处理方法。
方法1：使用错误检查提示
如果单元格左上角有绿色小三角，可以选中数据，点击错误检查按钮，选择“转换为数字”。
方法2：使用分列功能
选中需要处理的数据列。
进入：
数据 → 分列 → 完成
对于普通的文本数字，通常可以直接转换为数值。
方法3：使用VALUE函数
在旁边输入：
=VALUE(B2)
如果B2是Excel能够识别的文本数字，就可以转换为真正的数值。
但如果里面混有货币符号、特殊空格、非标准分隔符等内容，可能还需要先清理。
这里尤其要提醒：
员工编号、订单号、物料编码等不应该随便转成数值。
例如编号00125，如果转换成数字，就可能变成125。
超过15位的长编号还可能面临精度丢失的问题。
所以数据清洗不是把所有内容都变成数字，而是让每一列的数据类型符合它的实际用途。
四、批量删除空白行：先判断什么才算空行
从系统导出的Excel，经常会夹着一些空白行。
如果数据量很大，一行行删除显然不现实。
但这里有个特别容易踩的坑：
某个单元格为空，不代表整行都是空的。
例如：
李四这一行只是部门没有填写，并不是整行空白。
如果直接定位空值，然后删除所有对应的整行，就可能误删李四的数据。
如果要删除真正的整行空白，可以借助辅助列。
假设数据位于A到C列，在D2输入：
=COUNTA(A2:C2)
向下填充。
结果为0，说明这几列没有非空单元格。
接下来筛选辅助列中等于0的行，确认无误后，再删除对应整行。
不过，COUNTA会把返回空字符串""的公式单元格也视为非空。
如果数据里存在这类公式，需要进一步判断，不能完全依赖COUNTA。
建议：删除前先保存原文件副本，尤其不要对整张表直接执行“定位空值→删除整行”。
五、清理重复数据：删除前先确定保留规则
整理客户名单、订单记录、员工信息时，经常会遇到重复数据。
Excel自带“删除重复项”功能。
操作方法：
选中数据区域 → 数据 → 删除重复项
然后选择用于判断重复的列。
例如：
如果按照员工编号去重，就可以保留一条001记录。
但这里有两个关键问题。
第一，整行重复和指定字段重复不是一回事。
如果选择所有列，Excel会按照所有选中列的组合判断重复。
如果只选择员工编号，则员工编号相同就会被视为重复，即使其他字段不同。
第二，删除重复项默认保留首次出现的记录。
假设同一个客户有两条记录：
9月1日：联系电话A
9月20日：联系电话B
如果想保留最新资料，就不能直接点击“删除重复项”。
更合适的方法是先按照更新时间从新到旧排序，再按客户编号去重。
这样才能优先保留最新记录。
需要注意，排序时必须保证整行数据一起移动，避免姓名、电话、日期错位。
去重之前，先想清楚：按照什么判断重复？重复以后保留哪一条？
这往往比点击按钮更重要。
六、统一日期格式：先区分真实日期和文本日期
Excel里的日期问题也很常见。
同一张表可能同时出现：
2026/9/23
2026-09-23
2026.09.23
2026年9月23日
看起来都在表示日期，但它们不一定属于相同的数据类型。
情况1：本来就是真实日期，只是显示方式不同。
选中日期列，按Ctrl+1。
进入“设置单元格格式”，选择日期，或者在自定义格式中输入：
yyyy-mm-dd
就能统一显示成类似：
2026-09-23
这种操作只改变显示方式，不改变底层日期值。
情况2：日期实际上是文本。
比如从其他系统导入的2026.09.23。
如果修改单元格格式后没有变化，就可能需要先把文本转换成真实日期。
可以尝试：
数据 → 分列 → 选择适合原始数据的日期类型 → 完成
如果原始日期格式不标准，还可能需要先统一分隔符。
转换完成后，可以用：
=ISNUMBER(A2)
辅助检查日期是否已经变成Excel数值日期。
因为Excel通常以序列数存储真实日期。
需要特别注意：
如果一列里混合了真实日期和文本日期，排序可能出现异常。
日期清洗的重点不是让它们看起来一样，而是确保Excel真正把它们识别为日期。
七、批量统一文本内容：查找替换和SUBSTITUTE
最后一个场景也很常见。
例如部门名称：
销售部
销售一部
销售部门
或者订单状态：
已完成
完成
已办结
如果这些内容本来代表同一个分类，统计时却可能被当成不同项目。
对于简单、明确的替换，可以直接使用Excel的查找替换。
按：
Ctrl+H
输入查找内容和替换内容。
例如：
查找：已办结
替换为：已完成
确认后执行替换。
如果不希望直接修改原始列，可以使用SUBSTITUTE函数：
=SUBSTITUTE(A2,"已办结","已完成")
这种方法适合先生成清洗后的结果，再检查是否符合预期。
不过，批量替换最怕误伤原本正确的数据。
比如把所有“销售”替换成“市场”，可能连“销售支持部”这样的名称也一起改掉。
所以替换之前，最好先检查具体匹配范围。
对于部门、地区、订单状态等字段，建议建立一张统一的标准名称对照表，明确哪些旧名称应该对应哪些新名称。
这样比每次凭印象手动替换更可靠。
八、数据清洗时，最容易忽略的4个问题
学完上面7种方法，还有几个习惯值得养成。
1. 不要直接覆盖原始数据
尤其是删除空行、去重、批量替换等操作，建议保留原始文件，清洗结果另存。
2. 先判断数据类型，再处理格式
数字、编号、日期、文本，看起来可能很像，但Excel的处理方式不同。
3. 批量处理之前先用几行测试
特别是公式转换、日期转换、批量替换，先确认结果正确，再扩展到整列。
4. 清洗后一定要检查结果
例如清洗前有1000行，清洗后只剩850行，就应该知道少掉的150行是什么原因。
不能只看表格变整齐了，就认为清洗成功。
九、数据量很大时，还有更省事的办法
前面这些方法，使用Excel本身就可以完成。
如果只是偶尔处理几十行数据，其实没必要专门找其他工具。
但如果经常要清理几百、几千行数据，每次都要重复删除空行、去除空格、清理异常字符，或者按不同字段去重，操作起来还是比较繁琐。
这也是我做「表格工具箱 Lite」的原因之一。
里面有表格清理、智能去重、批量替换等功能，可以把一些常见的重复操作集中处理。
免费、无需注册，文件在本地处理，不上传服务器，也不会直接覆盖原文件。
当然，工具能省去重复点击，但像“哪些记录该删除”“哪些名称应该统一”，仍然需要根据实际业务判断。
👉 点击这里使用表格工具箱Lite
写在最后
Excel数据清洗，并不是简单地把表格整理得好看一点。
真正的目的，是让数据能够被正确地查找、匹配、计算、排序和统计。
这7种方法可以对应到7类常见问题：
多余空格 → TRIM 不可见字符 → CLEAN 文本数字 → ISNUMBER、VALUE、分列 空白行 → 辅助列判断 重复记录 → 删除重复项 日期混乱 → 格式设置、日期转换 内容不统一 → 查找替换、SUBSTITUTE
如果平时经常从业务系统导出Excel，建议把这些方法熟悉一下。
很多看似复杂的表格问题，其实只是数据在进入统计和分析之前，没有清理干净。