【表格】用 AI 一键清洗脏数据

摘要：用 Kimi（或智谱清言 GLM）规划 + Excel Power Query，把一张乱糟糟的表洗成干净数据。

1. 痛点引入（30 秒共鸣）

你肯定遇过这种事：同事甩给你一张 Excel，说"帮忙整理下发我"。你点开一看——表头是合并单元格，"华东"两个字横跨三行；姓名列里" 张三 "前后带着空格；入职日期一栏里"2023/1/5""2023.01.05""2023-1-5"三种写法混着来；还有一列叫"部门-工号"，本该是两列偏被塞进一个格；最要命的是，底下还藏着两行一模一样的重复数据。

你心里咯噔一下：这要是手动改，几千行得改到什么时候？改错一个空格，对账就对不上。你也隐约听人说过 Excel 里有个叫 Power Query（中文界面叫"获取和转换"，它是 Excel 自带的一个"数据清洗台"——你点几下按钮，它就记住步骤，以后数据变了点"刷新"就能重洗一遍，不用重做）的东西能搞定，但那一排按钮你一个都不认识。

别慌。这篇文章就教你一个省事办法：让 Kimi 先把清洗顺序想清楚，你再照着它的清单在 Power Query 里点十几下鼠标。AI 替你动脑，你只动手，每一步还都能看懂在干嘛。

2. 目标产出

学完这篇，你能拿到两样东西：

一张干净表。干净的标准很具体：没有合并单元格造成的空缺；文本字段前后没空格；日期列是真正的"日期"类型（不是看起来像日期的文字）；该拆的列已拆开；重复行已删掉。
一条可复用的清洗流程（在 Power Query 里叫"查询"，查询（query）＝Power Query 里保存的一套清洗步骤，像一份菜谱）。下回同事又发来一张同结构的脏表，你不用重做，右键"刷新"就行。

所谓"一键"，不是真的一个按钮搞定，而是 AI 替你把步骤排好序，你照做即可——这比自己硬记 Power Query 按钮快得多，也稳得多。

3. 案例实战

下面用一张假设的销售花名册当例子，问题和你手上的差不多。先把它在 Excel 里建成一张"正式表格"，我给它起名"表1"。

⚠️ 建表前必须先取消合并（醒目提示）：Excel 的「表格（Ctrl+T）」不支持合并单元格。务必先选中"地区"那几行 → 开始 → 合并后居中 → 取消单元格合并（确认每格都有独立值后），再选中整个数据区域按 Ctrl+T（或插入 → 表格）建表。颠倒顺序（先 Ctrl+T 再取消合并）会让表建歪、值丢失。

脏表长这样：

地区	姓名	入职日期	部门-工号	销售额
华东（合并三行）	张三	2023/1/5	销售-1001	5000
（合并，空格）	李四	2023.01.05	销售-1002	6000
（合并，空格）	王五	2023-1-5	销售-1003	5000
华北	赵六	2023/2/1	市场-2001	7000
华东	张三	2023/1/5	销售-1001	5000

最后一行和第一行完全重复，就是我们要干掉的"重复行"。

⚠️ 录入脏表时的关键一步（避免后面替换崩）：把上面这张表敲进 Excel 时，先把"入职日期"整列设为文本格式（选中该列 → 右键 → 设置单元格格式 → 文本），或在每个日期前加英文单引号 '（如 '2023/1/5）。否则 Excel 会把 2023/1/5 当成真日期存成序列号，等到第 5 节步骤里"把./-替换成/"时就找不到可替换的字符、整步直接失效。

第一步：把脏表交给 AI，让它出方案（附提示词原文）

打开 Kimi 或智谱清言（GLM）（或任何能写代码的 AI），把下面这段提示词整段粘进去。这段提示词的关键是：告诉 AI 你的列名、具体问题，并且明确要求它"别编 Power Query 没有的功能"。

你是一位 Excel Power Query 专家。我有一张脏数据表，结构和问题如下：
- 地区：表头有合并单元格，同一个值跨多行（如"华东"占三行）
- 姓名：部分单元格前后有多余空格，例如" 张三 "
- 入职日期：格式混乱，混用了 2023/1/5、2023.01.05、2023-1-5
- 部门-工号：应拆成两列，例如"销售-1001"
- 销售额：存在整行重复的数据

请输出两份内容：
1) 一份给新手照做的 Power Query 清洗步骤清单，按先后顺序排，
   每步写清在 Power Query 编辑器里的菜单路径
   （例如：转换 → 格式 → 修整）。
2) 对应的 Power Query M 代码（就是"高级编辑器"里那段公式），
   用中文注释每一行在做什么。

要求：不要编造 Power Query 没有的功能；拿不准的地方直接说明，
不要用"AI 一键自动"这类夸张说法。

AI 大概率会回你一份步骤清单（删空行、修整、统一日期、拆列、删重复……）外加一段 M 代码。下面第二步我把这份清单落地成真实可点的操作——你完全可以对着 AI 给的清单做，也可以直接照我下面写的来。

第二步：在 Excel 的 Power Query 里照做

前置处理（进 Power Query 前最后确认）：前面建"表1"时，"地区"列应该已经取消过合并（若漏了，现在补：选中"地区"那几行 → 开始 → 合并后居中 → 取消单元格合并）。取消后，"华东"会留在第一个单元格，下面两格变空——这个空待会儿用"填充"补上。

正式进入 Power Query：点数据 → 从表格/区域（有的版本在数据 → 获取数据 → 从表格/区域）。Excel 会弹出 Power Query 编辑器（Power Query Editor＝专门整理数据的窗口，右侧有"应用的步骤"面板，你每点一个按钮，这里就多一行记录）。

下面按清洗顺序操作，每一步都标了菜单路径：

把第一行当表头：Power Query 通常会自动识别。如果没识别，点主页 → 将第一行用作标题。
补回合并单元格断掉的值：选中"地区"列 → 转换 → 填充 → 向下。"向下填充"＝把上方有值的格子，复制填到下面连续的空格里，于是"华东"补齐下面两行。
去掉姓名前后空格：选中"姓名"列 → 转换 → 格式 → 修整。"修整（Trim）"＝只去掉最前和最后的多余空格，中间的正常空格保留。如果怀疑有肉眼看不见的乱码字符（比如从网页复制来的换行符），再点一次转换 → 格式 → 清除（"清除（Clean）"＝去掉那些非打印字符）。
统一日期格式（最容易踩坑，详见第 5 节）：先统一分隔符。选中"入职日期"列 → 转换 → 替换值，把"."替换成"/"；再替换一次，把"-"替换成"/"。现在都成了"2023/1/5"这种。然后选中该列 → 转换 → 数据类型 → 日期。如果仍报错，改用转换 → 数据类型 → 使用区域设置，地区选"中文(中国)"，类型选"日期"。
拆分"部门-工号"：选中该列 → 转换 → 拆分列 → 按分隔符 → 分隔符选"自定义"，输入"-" → 确定。会生成两列，默认叫"部门-工号.1""部门-工号.2"，右键列名改成"部门"和"工号"。
删除重复行：点主页 → 删除行 → 删除重复项。不选中任何列时，只有"所有字段都一模一样的行"才会被判定重复——正好干掉那条重复的张三。
删掉可能残留的空行：主页 → 删除行 → 删除空行。

最后点主页 → 关闭并上载（Close & Load），干净表就回到 Excel 新工作表里了。以后同事又发来同结构脏表，只要替换数据源、右键刷新，整套清洗自动重跑。

附：对应的 Power Query M 代码（想进阶看"高级编辑器"里这段，和上面 7 步一一对应；表格名"表1"要和你 Ctrl+T 建的表名一致）：

powerquery
let
    // 1. 从 Excel 表格"表1"读取数据
    //    （数据已是 Ctrl+T 正式表，[Content] 会把首行直接当表头、列名就是"地区/姓名/…"，
    //     所以无需再 Table.PromoteHeaders，这一步在从正式表导入时反而多余）
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],

    // 2. 把"地区"列的空值向下填充，补回合并单元格断掉的值
    向下填充 = Table.FillDown(源, {"地区"}),

    // 3. 去掉"姓名"列前后空格，并清除不可见字符
    修整姓名 = Table.TransformColumns(向下填充,
        {"姓名", each Text.Clean(Text.Trim(_)), type text}),

    // 4. 先把日期分隔符统一成 "/"
    替换点 = Table.ReplaceValue(修整姓名, ".", "/", Replacer.ReplaceText, {"入职日期"}),
    替换横线 = Table.ReplaceValue(替换点, "-", "/", Replacer.ReplaceText, {"入职日期"}),

    // 5. 把"入职日期"改成真正的日期类型（指定中文本地，避免月日颠倒）
    改日期类型 = Table.TransformColumnTypes(替换横线,
        {{"入职日期", type date}}, "zh-CN"),

    // 6. 按 "-" 把"部门-工号"拆成"部门"和"工号"两列
    拆分部门工号 = Table.SplitColumn(改日期类型, "部门-工号",
        Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"部门", "工号"}),

    // 7. 删除整行重复
    删除重复项 = Table.RemoveDuplicates(拆分部门工号),

    // 8. 删除完全为空的行
    删除空行 = Table.RemoveEmptyRows(删除重复项)
in
    删除空行

4. 原理小结

一句话讲透：Power Query 不是"改原表"，而是给你记了一份清洗菜谱——你在编辑器里每点一个按钮，右侧"应用的步骤"就多一行 M 公式（M 是 Power Query 的专属公式语言，就像 Excel 里的函数公式）。AI 的作用，是帮你把这份菜谱的先后顺序想对、想全；你照着点，等于把菜谱一行行写进去了。因为整份流程都被记下来了，所以数据源一换、点"刷新"，菜谱就自动重做一遍，脏数据永远是脏数据，干净结果随时能再来一份。

5. 避坑指南

合并单元格必须先取消，再填充。 Power Query 直接读合并单元格会丢值——它只认左上角那个格，其余当成空。一定先在 Excel 取消合并，再用"填充→向下"把值补回去。
日期别拿"更改类型"硬怼。 三种写法混着来时，直接转"日期"类型必报错。先统一分隔符（替换值），或用"使用区域设置"指定中文本地；真遇到"2023/1/5"和"05/01/2023"这种月日顺序冲突的，Power Query 也救不了，得先和出表的人确认到底哪边是月。
空格分两种，工具也不同。 普通的" 张三 "用"修整"就能去；但从网页/PDF 拷来的换行符、不间断空格这类看不见的字符，"修整"去不掉，得上"清除"。两个一起用最稳。
拆分列会改列名。 拆完默认叫"xxx.1""xxx.2"，下游要是引用原列名会报错。记得手动重命名，保持和报表、公式里的叫法一致。
删重复看的是"整行"。 没选中列时，只有所有字段都一样的行才算重复。想按某一列（比如按"工号"）去重，要先选中那几列再点删除重复项，或在 M 里写 Table.RemoveDuplicates(表, {"工号"})。

6. 进阶延展

回看 L1 的 B1《AI 帮你 10 分钟做完一张报销表》：规范的表头、整洁的列结构是清洗的地基，先看它少走弯路。
下一篇 B3《AI 写复杂函数与数组公式》：表洗干净了，下一步就是让 XLOOKUP、FILTER、LET 这些函数替你算账；DAX 度量值在 B22、透视表入门在 B1/B21。
再深一层：把这套清洗存成"模板查询"，配合 宏（macro＝Excel 里能录下来自动执行的一串操作） 一键刷新，VBA 实战见 B7（邮件合并批量导出 PDF）与 C1（周报自动化）。

🛠️ 用 AI 一键清洗脏数据