目录

数据清洗常用函数

一、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，处理速度和灵活性都更高。