Excel 数据清洗从半天到 20 分钟,我让 AI 先别写公式

上篇说 AI 写的 SUMIFS 让 72% 的销售额安静蒸发——120 行明细，总额 774,640，它算出 216,751，还是周五对账财务才对出来。后台好几个人来问：那表到底怎么洗？这篇把整个流程补上。

先说结论：别让 AI 直接写公式。先让它把问题列出来，你确认了，再动手。顺序反了，就是上篇那个结果。

第一步：让 AI 当医生，别当装修队

把表头丢给它，加一句"这表要做透视"，用下面这段提示词，整段复制：

我有一张销售明细，表头是：日期、区域、销售员、金额、备注。数据很脏，我要做数据透视。你先别写公式。逐列告诉我：这一列可能有哪些脏法（写法混、类型混、空格、单位、空值、合并单元格）；每种脏法怎么在表里快速验证；确认哪列真有问题，我再找你要公式。一次只说一列，等我确认。兼容 Excel 2016 中文环境。

"一次只说一列"是这段的灵魂。它一口气给你八个公式，你贴不贴？贴了之后哪个对哪个错，你根本查不过来。

第二步：四类脏数据，各有一句现成的

诊断完，通常就是这四类。公式是我最后用的版本，列号按你的表改：

日期混写法（7月5日 / 2026/7/5 / 2026-7-5），统一成标准日期：

=IF(ISNUMBER(A2),TEXT(A2,"yyyy/m/d"),IFERROR(TEXT(DATEVALUE(A2),"yyyy/m/d"),A2))

数字型直接转格式，文本型 DATEVALUE 接住，转不了的保留原样——别让它瞎猜。留下的那几行，往往就是录错的行。

文本脏（前后空格、全角空格、"北京/北京市"混着写）：

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(12288),"")))

注意 TRIM 只吃半角空格，全角得靠 CHAR(12288) 那句。这个坑我第一次也踩了：清完看着干净，透视里"北京"还是分两组。地名统一别用公式硬换，让 AI 先给一张"写法→标准名"对照表，查找替换一次过。

数字存成文本（左上角绿三角、¥3,000、5万）：

=IFERROR(VALUE(SUBSTITUTE(SUBSTITUTE(D2,"¥",""),",","")),IF(RIGHT(D2,1)="万",VALUE(SUBSTITUTE(D2,"万",""))*10000,"转不了"))

"转不了"三个字是故意留的，比 #VALUE! 好找。

结构问题：合并单元格先全部取消、定位空值批量填上；重复行从第二行往下用 =COUNTIF(A$2:A2,A2)>1 标记，筛出来人工过一眼再删。直接点"删除重复项"我不敢——万一是同名不同人。

第三步：交付前对三个数

洗完别急着透视，先对账。

行数：SUBTOTAL(103,A:A)，洗前洗后必须一样，少一行就是清洗公式吃掉了一行。总数：SUBTOTAL(109,D:D)，跟财务口径对，774,640 差一分都不行。边界：抽跨月跨年的两三行，看日期转没转对，12月31日和1月1日最容易翻车。

三个数过了，再插透视表。还有个不显眼但救命的习惯：动手前把原始表复制一份存档。我上次直接在原表上洗，第 47 行备注写着"此单已退"，洗完没了，查无可查。

20 分钟的账：诊断 5 分钟，四类公式 10 分钟，对三个数 5 分钟。以前那半天，其实耗在"洗完不敢用、回头再检查一遍"。

回头看，这套里 AI 最值钱的不是公式——公式网上一搜也有。是"你先别写公式"那六个字。它逼你把问题先摊开，摊开了，脏数据就只剩四类，四类就各有现成的解。

表没洗干净的时候，透视不是不能做，是做完不敢签字。这篇讲的不是洗表，是那个签字的底气。