数据清洗助手：修好90%杂乱表格的三招
去重、标准化、校验。把公式粘进辅助列往下拖——不用宏，不用付费工具。
每张乱表都有同样三种病：重复行让你总数翻倍、文本不统一（尾巴空格、大小写混乱、公司名错别字）、以及数值根本就是错的。按这个顺序修——第一步永远别跳。
第一招 —— 去重
- 先找重复：加一列辅助列，用
——显示2以上的行就是重复，手动删掉多余的，让你决定保留哪一份。=COUNTIF(A:A, A2) - 或直接抽唯一值：在空白列写
，返回干净的去重清单。=UNIQUE(A2:A500)
第二招 —— 标准化文本
——去掉多余和尾部空格（大多数查找失败的隐形元凶）。=TRIM(A2)
——把全大写或全小写的名字修成首字母大写。编码类内容用 =UPPER()。=PROPER(A2)- 然后把清洗好的列复制，右键选择性粘贴为数值——否则它永远只是公式。
第三招 —— 校验
——邮箱地址长度应该差不多；一个3字符的行混在25字符的行里立刻现形。=LEN(A2)
——检查每个邮箱是否只出现一次。=COUNTIF(邮箱列, A2)
对付疑难杂症的AI提示词
我有一张表，列为[A、B、C]。A列的公司名不统一（空格、错别字、法律后缀混杂）。写出清洗它的确切Excel公式，再列出5行看起来还有问题的数据。样本：[粘贴最多50行]顺序很重要：先去重，再标准化，最后校验。先标准化再去重，同一行的两份副本还是两份；先校验再标准化，一半的报警最后查出来只是尾巴空格。按这个顺序跑三招，表格自己会告诉你脏乱在哪。
对反复出现的脏乱（每周导出、CRM数据转储），别每周手洗一次。把五列辅助列建好一次，以后每次新粘贴自动过一遍。花两小时搭一次管线，胜过永远每周一洗四十分钟。
最后一步：清洗完之后，从头到尾抽一行看看。改错了一条记录的干净表格，比你看得懂的脏表更不值钱。先信任，再验证。
怎么用
- 先复制一份表，再用COUNTIF找重复——没有备份之前绝不删。
- 在辅助列跑TRIM和PROPER，再把结果以纯数值粘回去。
- 用LEN和COUNTIF校验；公式搞不定的疑难杂症，用AI提示词。
不需要。三招只用五个简单函数：TRIM、PROPER、UNIQUE、COUNTIF、LEN。粘到辅助列里往下拖，全部技能就这些。
真正做清洗的话，一次粘几百行以内合适。几千行的规模，请老老实实在表格里用公式；AI更适合看规律和写公式，不适合替你跑整份文件。
先合并成一张表再清洗。两份文件进了同一个地方，跨表重复才好找出来，而且公式只跑一遍，不用跑两遍。
加辅助列转换：点分隔的日期用 =DATEVALUE(SUBSTITUTE(A2,".","-"))，再把列格式设为日期。如果全列写法五花八门，把样本列粘给AI，请它给一套统一规则。
有——所以先右键工作表标签，移动或复制，勾选建立副本。在副本上清洗，原件原封不动。删掉一个重复项，绝不能等于弄丢了唯一记录。
适用人群
这一页是给“接手表格的人”用的，不是给“建表格的人”用的：拿到培训签到导出表的人事、同时收到三套系统供应商名单的财务助理、要用活动报名拼出一份发送清单的行政。这张表在你手上只待一个下午，你得让它能用，而没有人会替你重做上游系统。
也要说清楚它不是什么。辅助列里的三个公式是对单个文件的临时修补，不是数据治理。如果同一批错误每周从同一个来源冒出来，长期解法在上游——加校验规则、把字段设为必填、改导出模板——这需要和系统负责人谈。它也不能替代隐私与档案合规审查：把员工或客户数据贴进对话工具，是政策问题，不是清洗问题。至于跨两个系统按模糊规则匹配人或公司，那需要专门的匹配工具和一个对结果负责的人；靠一列公式，只会得到一批看起来很确定的错误匹配。
如何解读结果
判断一张清洗过的表，看的是“每个报警都有解释”，不是看着整齐。前后各记四个数：总行数、去重后的唯一值数、被文本公式改动的行数、仍然校验不通过的行数。剩下的每一条报警，你都应该能张口说出原因。校验列一片空白，通常说明这项检查根本没跑过。
| 检查项 | 正常返回 | 返回异常意味着什么 |
|---|---|---|
=COUNTIF(A:A,A2) | 绝大多数行显示1 | 出现2或以上，就是还没处理的重复项 |
=LEN(A2) | 同一字段长度集中在很窄的区间 | 邮箱列里出现三四个字符，多半是记录被截断了 |
=UNIQUE(range) | 数量与你预期的名单一致 | 数量偏多，说明大小写或空格还留着 |
然后挑一行，从头读到尾：姓名、邮箱、部门、日期。这一行作为一个“人”或一张“订单”讲得通，表大体就是可靠的；如果读起来只是几个碰巧挨在一起的字段，说明你清掉的是字符，不是结构问题。
一个实际例子
一位培训协调员从学习平台导出了412行签到记录，公司名是学员自己手打的。她先复制了一份工作表，再按顺序跑三招。
| 步骤 | 她跑的是什么 | 之前 | 之后 |
|---|---|---|---|
| 找重复 | 对邮箱列用 =COUNTIF(B:B,B2) | 412行 | 18行被标记，去重后394条 |
| 标准化文本 | 先 =TRIM(C2)，再 =PROPER(...) | 57行带多余空格，同一家机构有6种写法 | 统一成1种写法，空格清掉 |
| 校验 | =LEN(B2) | 3行不足六个字符 | 3行单独隔离待核实 |
她把辅助列以数值形式粘回去，把412行的原件留在隐藏工作表里，最后交出去的是391条可用记录，并附了一段说明：三条待核实的记录、十八条已删除的重复。让这份文件可信的正是这段说明——接收的人能看见你做过哪些判断，也能不同意其中任何一条。
常见错误
- 没复制就删。一次排错、一次删多了，唯一的一份就没了；复制只要十秒。
- 先去空格再去重。只差一个尾部空格的两行对不上，你的重复检查就会安静地放过它们。
- 辅助列留着公式就交出去。别人一排序、一筛选，或者文件一传，值就重算或报错；记得选择性粘贴为数值。
- 直接对原始列跑检查。唯一值清单会把“Acme Ltd”和“acme ltd ”当成两条，技术上没错，实际使用里毫无意义。
- 两份导出分别洗完再合并。跨文件的重复，只有在两张表进了同一个工作表之后才看得见。
- 让AI“把几千行洗干净”。它返回的是文字，不是改好的文件；让它写公式、看规律，然后你自己去跑那个公式。