Excel脏数据:AI统一格式去重,生成异常核验表

同一份系统导出的表格，日期列里可能同时出现“2026/7/1”“7月1日”和文本日期；客户名称后面藏着空格，分类名称也可能有多种写法。

如果直接做数据透视表，结果未必报错，但同一天、同一客户或同一分类可能被拆开统计。

这类任务不适合交给AI“一键洗干净”。更稳妥的办法，是让AI负责发现问题、提出规则，Excel负责执行和复核，无法判断的记录则进入异常核验表。

清洗的目标不是让表格看起来整齐，而是让每一次修改都能解释、复现和撤回。

先把三种工作分开

• AI负责识别：
  扫描字段结构，找出日期格式不一致、空值、异常值和疑似重复记录，输出建议，不直接删除数据。
• Excel负责复现：
  用辅助列、条件格式、COUNTIF和统计公式执行明确规则，保留修改痕迹。
• 人负责判断：
  确认缺少年份的日期、公司简称、姓名近似、关键字段缺失等模糊情况。

下面用一份销售、运营或客户数据表作为通用示例。最终要交付的不是一张“处理过的表”，而是清洗表、异常核验表和规则清单三件套。

第一步：保留原表，建立四个工作表

不要直接在系统导出的原表上修改。复制文件或工作表后，建立以下四个工作表：

1. 原始数据_只读：
   保存未经修改的数据，并增加“原始行号”。
2. 清洗工作区：
   所有转换都通过辅助列完成。
3. 异常核验：
   集中保存不能自动决定的记录。
4. 清洗规则：
   记录字段、问题、转换规则和规则编号。

开始前记录总行数、总列数和关键字段空值数。清洗结束后再做一次同样的统计，用来发现误删或覆盖。

第二步：让AI只做扫描，不改数据

可以把工作副本交给WorkBuddy，或在WPS表格中选中数据区域调用WPS AI。第一轮只要求它输出结构化诊断，不允许填空、删行或合并近似记录。

可直接复制这段提示词：

请扫描这份表格的字段结构，但不要修改任何单元格。找出日期格式不一致、多余空格、不可见字符、大小写不一致、分类名称不统一、关键字段空值、完全重复和疑似重复记录。请按“字段名、问题类型、原始示例、建议规则、是否需要人工确认”输出。禁止推测缺失值，禁止删除记录，禁止自动合并近似项。

如果AI只给出“已清洗完成”之类的结论，不要采用结果。重新要求它列出原始位置、判断理由和建议规则。

第三步：把建议改写成明确规则

AI给出的自然语言建议，需要写进“清洗规则”工作表。每条规则至少包含规则编号、适用字段、转换动作、例外条件和核验方式。

可以先建立这些基础规则：

• 普通空格：
  用TRIM清除首尾多余空格，并压缩文本中连续的普通空格。
• 不可见字符：
  用CLEAN处理部分非打印字符。
• 不间断空格：
  使用 =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))，先替换特殊空格，再清理文本。
• 英文大小写：
  根据字段用途选择UPPER、LOWER或PROPER，整列采用同一规则。
• 日期：
  真实日期值统一显示格式；无法确定年份、日期顺序或原始含义的文本日期，不自动猜测，转入异常核验表。
• 分类名称：
  只转换规则表中已经确认的对应关系，未收录的名称保留原值并标记。
• 缺失值：
  只标记，不根据上下文自行补全。

处理日期时要区分“修改显示格式”和“把文本转换为日期值”。仅把单元格显示成统一样式，不能证明其中的文本已经成为可计算的日期。

第四步：用Excel标出重复项和空值

先根据业务含义确定去重键。订单数据可以使用订单号，客户数据可能需要客户编号；如果必须组合多个字段，应先把已经标准化的字段拼成辅助键。

假设辅助键位于H列，可在“重复次数”列输入：

=COUNTIF($H$2:$H$1000,H2)

结果大于1表示同一个键出现多次。也可以把 =COUNTIF($H$2:$H$1000,H2)>1 用作条件格式公式，先高亮重复记录，再决定是否删除。

Excel的“删除重复项”会依据选中的列判断重复，保留第一条并删除其余记录。它删除的是整条重复记录，因此不要在没有核对去重键和保留顺序时直接执行。

空值可以用COUNTBLANK统计，异常文本可以用LEN辅助检查长度。条件格式负责把重复、空值和越界候选项标出来，但不替代业务判断。

第五步：生成异常核验表，再导出结果

将所有不能由明确规则处理的记录复制到“异常核验”工作表。建议至少保留以下字段：

• 原始行号
• 业务主键
• 问题字段
• 原始值
• 标准化建议
• 异常类型
• 规则编号
• 处理状态
• 人工结论
• 备注

完全相同的重复记录可以按已确认的键处理；公司简称、相近姓名、缺少主键和年份不完整的日期，则应逐条确认。人工确认后，再把结论写回清洗工作区，并保留原值列。

最终检查三组数字：原始行数与清洗后行数、各关键字段的空值数、每组重复键的处理数量。任何无法解释的变化，都应回到原始数据和对应规则重新检查。

失败时怎么处理

AI擅自补值或删除记录

停止使用该结果，回到原始副本。把提示词改成“只列问题和位置，不修改、不填充、不删除”，再分字段扫描。

TRIM之后仍然匹配不上

检查是否存在不间断空格或其他不可见字符。可以用LEN比较清洗前后的文本长度，再使用SUBSTITUTE、CLEAN和TRIM组合处理。

去重后行数下降过多

撤回删除操作，改用条件格式或COUNTIF只做标记。重新核对去重键、字段标准化规则和记录保留顺序。

日期无法可靠转换

保留原始文本，不推测年份或日期顺序。将记录放入异常核验表，等待业务人员确认。

这套流程不能替你决定什么

AI可以发现模式，却不知道两个相似名称在业务上是否属于同一对象；Excel可以准确执行公式，却不知道空值应该保留、补录还是作废。

WPS AI的具体入口和可用能力也可能随版本或会员状态变化。无论使用哪种AI工具，真正可靠的部分始终是规则清单、原始数据和可追踪的人工结论。

一份清洗完成的表格，不应该只剩下整齐的数据。它还应该回答三个问题：改了什么，为什么改，以及哪一些记录仍然需要人来决定。