数据清洗的常用方法
目录
数据清洗常用函数
一、TRIM：删除多余空格
二、CLEANT：清洗不可见文字
三、SUBSTITUTE：替换特定文本
四、MAX/MIN：找最大/最小值
五、VALUE：文本转数值
六、IFERROR：错误值自动替换
方法一：Excel 函数公式法
一、删除重复订单
二、筛选空白无效数据
三、拆分商品名称文本
四、统一数字格式
方法二：Power Query 自动化
一、删除重复项/空行
二、拆分列
三、替换值
四、更改数据类型
方法三：Python 代码法
清洗思路总结
给一份杂乱的订单表：删除重复订单、筛选空白无效数据、拆分商品名称文本、统一数字格式。
数据清洗常用函数
一、TRIM：删除多余空格
=TRIM(A1:A10)
二、CLEANT：清洗不可见文字
=CLEAN(A1)
三、SUBSTITUTE：替换特定文本
=SUBSTITUTE(A1,"旧文本","新文本")
四、MAX/MIN：找最大/最小值
=MAX(A1:A10)或=MIN(A1:A10)
五、VALUE：文本转数值
=VALUE(A1)
六、IFERROR：错误值自动替换
=IFERROR(A1/B1,"除数不能为0")
方法一：Excel 函数公式法
如果习惯在 Excel 里操作，用函数公式是最直接的。
一、删除重复订单
最快捷的方法是使用“数据”选项卡下的“删除重复值”功能。如果想用公式标记，可以在辅助列输入
=IF(COUNTIF($A$2:A2,A2)>1,"重复","") ，
然后向下填充，就能把所有重复的订单号标记出来。
二、筛选空白无效数据
选中整个表格，按Ctrl+Shift+L开启筛选功能。点击每一列的筛选箭头，取消勾选“(空白)”，就能快速隐藏所有空白行。
三、拆分商品名称文本
1.懒人神器： 选中需要拆分的列，直接按Ctrl+E，Excel 会自动识别你的意图进行填充，非常智能。
2.公式法： 如果商品名和规格是用特定符号（如“-”或“/”）隔开的，可以用“数据”→“分列”功能。或者用LEFT 、 MID 、 RIGHT函数根据字符位置进行提取。
3.清除多余空格： 平台导出的数据常带有很多看不见的空格，导致匹配出错。可以用=TRIM(A2)函数清除单元格前后和中间多余的空格。
四、统一数字格式
选中需要统一的列，点击“数据”→“分列”，最后一步直接点击“完成”即可快速将文本型数字转换为数值型。 也可以用=VALUE(A2)函数转换。
方法二：Power Query 自动化
如果数据需要定期清洗，强烈推荐使用 Excel 自带的Power Query工具。它能把你的清洗步骤记录下来，下次有新数据时，一键刷新就能自动完成所有操作。
一、删除重复项/空行
在 Power Query 编辑器中，选中列后，在“开始”选项卡下找到“删除行”，可以选择“删除重复项”或“删除空行”。
二、拆分列
选中要拆分的列，在“转换”选项卡下点击“拆分列”，可以根据分隔符（如逗号、短横线）或固定宽度来拆分。
三、替换值
可以轻松替换错误值或空值，比如把所有错误值替换为“未知”。
四、更改数据类型
点击列标题旁边的数据类型图标，可以统一将日期、数字、文本格式标准化。
方法三：Python 代码法
对于数据量特别大或者清洗逻辑特别复杂的情况，用 Python 的 pandas 库会非常高效。
import pandas as pd # 读取Excel文件 df = pd.read_excel('淘宝闪购订单表.xlsx') # 1. 删除重复订单 (假设'订单号'是唯一标识) df.drop_duplicates(subset=['订单号'], inplace=True) # 2. 筛选空白无效数据 (删除所有列都为空的行) df.dropna(how='all', inplace=True) # 删除'商品名称'列为空的行 # df.dropna(subset=['商品名称'], inplace=True) # 3. 拆分商品名称文本 (假设用'-'分隔) # 将'商品名称'列按'-'分割，并扩展为多列 split_cols = df['商品名称'].str.split('-', expand=True) # 将分割后的新列合并回原DataFrame df = pd.concat([df, split_cols], axis=1) # 重命名新列 df.rename(columns={0: '商品主名', 1: '商品规格'}, inplace=True) # 4. 统一数字格式 (将'数量'列转换为数值类型) df['数量'] = pd.to_numeric(df['数量'], errors='coerce') # 保存清洗后的数据 df.to_excel('清洗后的订单表.xlsx', index=False)清洗思路总结
1.偶尔一次： 用 Excel 的“删除重复值”、“筛选”和 Ctrl+E 最快。
2.重复工作： 一定要学 Power Query，一劳永逸，下次刷新就行。
3.数据量巨大： 考虑用 Python，处理速度和灵活性都更高。