🤖 AI 跟我学 新手入门

Excel 数据清洗 AI 实战：5 大脏数据场景搞定

Excel 数据清洗 AI 保姆教程：把脏数据丢给 AI 自动整理，含 3 套通用 prompt、5 类常见脏数据救援方案和清洗前后对比示例

发布 2026/05/19 📎 参考官方文档

你是不是也遇到过这种情况：HR 系统导出来的员工信息表，5000 行数据，名字里夹着空格、电话号码格式 5 种乱七八糟、入职日期一半「2026-05-12」一半「2026/5/12」还有写「五月十二号」的。要做透视表前必须先清洗，手动改一上午都改不完。这时候用 Excel 数据清洗 AI，是最省事的解法。

这篇就教你怎么用 Excel 数据清洗 AI。从最简单的「去空格」，到带正则的进阶玩法，再到怎么处理一份 5 万行的脏表，3 个 prompt 模板直接抄，30 分钟把脏数据变成干净表。

为什么是 AI 而不是 Excel 自带的「数据清理」

Excel 2019 之后有「数据 → 清理」菜单，但它有 3 个硬伤：

  • 只能做最基础的「去空格 / 大小写转换」：稍复杂的格式统一就不行
  • 不会识别业务规则：它不知道你的「电话号码格式」标准是哪种
  • 批量替换很弱：5 种不同的脏数据格式要替换 5 次

AI 大模型能解决这 3 个问题。实测下来：

  • Claude（Sonnet 4.5 / Opus 4.5）：理解业务规则最强，能识别脏数据模式后给统一方案。本文以它为主，看 Claude 是什么。
  • ChatGPT（GPT-5.1 及以上）：能直接读 Excel 文件后给清洗后的版本下载。
  • Kimi / 豆包 / DeepSeek：国内免登录，长表能塞进去，适合中文姓名地址清洗。看 国产 AI 对比 选一个。

选哪个的标准很简单：超长表（10 万行以上）用 Kimi，复杂业务规则用 Claude。如果你想要更系统的 Excel AI 思路，可以先看 AI 分析 Excel 配合阅读。

3 个 prompt 模板，复制就能用

数据清洗一般是三种场景：格式统一、错值修正、缺失值填充。每种给你一个直接能用的 prompt。

模板 1：格式统一（最常用）

适用：同一列的数据有多种格式（日期、电话、地址），需要统一成一种。

📋 Prompt 模板 
你是一位资深 Excel 数据清洗工程师，帮我把一列脏数据格式统一。
数据信息：
    

  • 表名：[填表名]
    • 

  • 这一列的字段名：[如「电话号码」]
    • 

  • 这一列的当前情况：

      

    • 总行数：[如 5000 行]
      • 

    • 已发现的格式种类：

        

      • 格式 1：[如「13812345678」（11 位连续数字），约 60%]
        • 

      • 格式 2：[如「138-1234-5678」（带连字符），约 20%]
        • 

      • 格式 3：[如「+86 138 1234 5678」（带国家码和空格），约 15%]
        • 

      • 格式 4：[如「(021) 12345678」（座机带区号），约 5%]
        • 

      

      • 

    • 其他可能的脏数据：[如「空值、含中文字符、长度异常」]
      • 

    

    • 

目标格式：
[如「统一成 11 位连续数字，座机加 0 区号-号码格式」]
业务规则：
    

  • 规则 1：[如「手机号必须 11 位，否则标’格式错误’」]
    • 

  • 规则 2：[如「座机号识别 010 / 021 / 0755 等区号开头的数字」]
    • 

  • 规则 3：[如「+86 国家码统一去掉」]
    • 

请按以下格式输出：
    

  1. 

    清洗方案
按数据格式分组，每组给清洗方法（公式 / 替换 / 手动）。
    

    2. 

  2. 

    完整 Excel 公式
    

      

    • 给一个单元格公式，能识别 4 种格式并统一输出
      • 

    • 用 IFS 或 SWITCH 函数，不要嵌套 5 层 IF
      • 

    • 配套的「格式错误」标记公式
      • 

    

    3. 

  3. 

    实际操作步骤
    

      

    • 第 1 步：在 B 列输入公式
      • 

    • 第 2 步：选中 B 列复制 → 选择性粘贴 → 数值，覆盖 A 列
      • 

    • 第 3 步：删除 B 列
      • 

    

    4. 

  4. 

    容易踩坑的地方
2-3 个常见错误的提醒。
    

    5. 

要求：
    

  • 公式必须能在 Excel 2019 / Office 365 / WPS 通用
    • 

  • 不要建议用 VBA（普通用户不会）
    • 

  • 给的公式要能直接复制粘贴
    •

用法：把方括号内容换成你的实际数据，整段发给 AI。30 秒出方案。

模板 2：错值修正（业务规则驱动）

适用：数据本身不脏（格式正确），但值不对（如年龄填了 200、价格填了负数）。

📋 Prompt 模板 
你是一位资深业务数据分析师，帮我找出并修正一份数据里的错值。
数据信息：
    

  • 表名：[填表名]
    • 

  • 字段列表（字段名 + 类型 + 业务范围）：

      

    1. age（数字，正常范围 18-65）
      2. 

    2. salary（数字，正常范围 3000-50000）
      3. 

    3. join_date（日期，正常范围 2000-01-01 至今）
      4. 

    4. department（文本，必须是「销售/产品/技术/运营」其中之一）
…
      5. 

    

    • 

业务规则（用于判断错值）：
    

  • 规则 1：年龄 < 18 或 > 65 → 错值，可能是 typo
    • 

  • 规则 2：薪资 < 3000 或 > 50000 → 异常，需复核
    • 

  • 规则 3：入职日期 > 今天 → 录错
    • 

  • 规则 4：部门不在白名单 → 拼写错误
    • 

请按以下格式输出：
    

  1. 

    错值检测公式
每个字段一个公式，输出「正常 / 错值 / 需复核」三种状态。
    

    2. 

  2. 

    自动修正建议
    

      

    • 能自动修的：给修正公式（如年龄 220 大概率是 22 多写了一个 0）
      • 

    • 不能自动修的：给标记方案（如「人工复核」+ 原因提示）
      • 

    

    3. 

  3. 

    异常报告
清洗后给一份报告：
    

      

    • 总行数
      • 

    • 检测出的错值数（按字段分组）
      • 

    • 已自动修正的数量
      • 

    • 需人工复核的数量
      • 

    

    4. 

  4. 

    防止再次脏数据
建议在录入端加哪些校验规则（如数据验证、下拉列表）。
    

    5. 

要求：
    

  • 自动修正必须保守，不确定的不要修（标错值即可）
    • 

  • 检测公式要能批量应用到全表
    • 

  • 异常报告用透视表能直接生成
    •

这个 prompt 的关键是业务规则比技术规则重要。AI 不知道你公司「薪资 100 万是错值还是正常」，必须你告诉它。

模板 3：缺失值填充（高难度场景）

适用：某些列有大量空值，需要按一定规则填充。

📋 Prompt 模板 
你是一位资深数据预处理专家，帮我处理一份有缺失值的数据。
数据信息：
    

  • 表名：[填表名]
    • 

  • 总行数：[填行数]
    • 

  • 字段列表 + 缺失情况：

      

    1. 字段 A：缺失 0%
      2. 

    2. 字段 B：缺失 15%
      3. 

    3. 字段 C：缺失 45%（接近一半空）
      4. 

    4. 字段 D：缺失 5%
      5. 

    

    • 

每个字段的填充策略（必须明确，不能含糊）：
    

  • 字段 A：[如「不需要处理」]
    • 

  • 字段 B：[如「空值填 0」/「空值填平均值」/「空值填’未知’」/「按其他字段推断」]
    • 

  • 字段 C：[如「缺失太多，直接删除这一列」]
    • 

  • 字段 D：[如「按同一客户的其他行推断（向下填充）」]
    • 

业务背景（帮你理解填充逻辑）：
[如「数据来自销售 CRM，部分字段是销售人员手动填的，遗漏正常」]
请按以下格式输出：
    

  1. 

    每个字段的填充方法
    

      

    • 字段 + 方法 + Excel 公式或操作步骤
      • 

    • 给完整公式，不要描述
      • 

    

    2. 

  2. 

    填充前的预检查
    

      

    • 哪些字段缺失太严重（>40%），建议放弃
      • 

    • 哪些字段填充有风险（可能造成数据偏差）
      • 

    

    3. 

  3. 

    填充后的数据质量检查
    

      

    • 用什么公式验证填充结果合理
      • 

    • 如何对比填充前后的数据分布
      • 

    

    4. 

  4. 

    注意事项
    

      

    • 缺失值填充会影响后续统计（如平均值），怎么提醒下游使用者
      • 

    • 是否需要单独加一列「是否填充」标记原始缺失行
      • 

    

    5. 

要求：
    

  • 不要鼓励「估算填充」，能空着就空着
    • 

  • 删除列前必须确认对业务的影响
    • 

  • 给的公式要能直接复制粘贴使用
    •

跑完你就有一份明确的缺失值处理方案。多数情况下，能空着就别填，填错比空着危害更大。

手把手 5 步走

光有 prompt 不够，下面是从打开脏表到出干净版的完整流程。

第 1 步：先扫一遍表，列出所有「脏」的地方

不要急着清洗，先用 5 分钟看一下：

  • 哪些列有空值，空多少
  • 哪些列格式不统一（日期、电话、地址常见）
  • 哪些列有明显错值（年龄填 200、价格填 -100）
  • 哪些列有重复或近似重复（如「华为公司」「华为科技」可能是同一家）

这份「脏数据清单」就是你给 AI 的素材。

第 2 步：按重要性排序

不是所有脏数据都要清洗。按业务影响排：

  • 必清：影响关键统计（如金额、日期）
  • 建议清：影响展示（如名字带空格）
  • 可不清：偶尔出现且业务不依赖的字段

通常 80% 的清洗工作量在 20% 的字段上。先搞定重要的。

第 3 步：分场景选模板

  • 同字段多种格式 → 模板 1
  • 值有错 → 模板 2
  • 大量空值 → 模板 3

针对每个要清洗的字段，单独跑一次对应模板。

第 4 步：先在小数据上验证

AI 给的公式直接套全表风险大。建议：

  • 复制前 50 行到一个新 sheet
  • 跑 AI 给的公式
  • 人工对比清洗前后，确认效果
  • 没问题再去全表跑

这一步能避免「公式跑错 5000 行全错了」的灾难。

第 5 步：清洗完做留档

清洗完成后做 3 件事：

  • 保留原始表：另存一份「原始数据-日期.xlsx」，万一发现清洗错了能回滚
  • 写清洗日志：哪些字段做了什么处理，给下游使用者看
  • 加数据校验：如果数据会持续入库，在入口加数据验证规则，防止再脏

[此处放截图：Excel 数据清洗前后对比，左边是含多种格式的脏数据，右边是统一格式的干净数据]

AI 输出示例 + 5 个让它清洗更好的技巧

下面是用模板 1 清洗「电话号码格式统一」的真实输出，给你参考。

1. 清洗方案
   把所有手机号统一成 11 位连续数字，座机统一成「区号-号码」格式。
   公式优先识别手机号特征（13/14/15/17/18/19 开头 + 11 位）。

2. 完整 Excel 公式
   B2 单元格输入：
   =IFS(
     LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-",""),"+","")," ",""))=11,
       SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-",""),"+","")," ",""),
     LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-",""),"+","")," ",""))=13,
       MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-",""),"+","")," ",""),3,11),
     TRUE, "格式错误"
   )

3. 实际操作步骤
   - 第 1 步：在 B2 输入上面公式
   - 第 2 步：双击 B2 右下角填充柄到最后一行
   - 第 3 步：复制 B 列 → 在 A 列右键 → 选择性粘贴 → 数值
   - 第 4 步：删除 B 列

4. 容易踩坑的地方
   - 复制粘贴后 A 列变成纯数字，可能丢失前导 0（如座机号 010），需要把 A 列设为「文本」格式
   - 「格式错误」的行需要人工核对
   - 公式不识别带括号的座机（如「(021)」），建议先把括号替换成空

照这个建议跑一次，5000 行 30 秒清洗完。

5 个让 AI 清洗更好的技巧

技巧 1：给真实示例比给规则强

抽 5-10 行实际脏数据贴给 AI，比描述「我的电话号码格式有 4 种」效率高 10 倍。AI 看到真实样本能识别更多隐藏模式。

技巧 2：明确业务规则，不要让 AI 猜

「手机号必须 11 位」「薪资必须正数」这种规则要写清楚。AI 不知道你的业务标准，猜的话容易把正常数据当脏数据删掉。

技巧 3：先标记再修改

让 AI 给「标记错值」的公式，不要直接「自动修正」。标出来后人工过一遍，确认无误再批量改。

技巧 4：保留可追溯性

清洗前加一列「原始值」，把原始数据复制过去。这样清洗后还能对比原始数据，发现问题能回滚。

技巧 5：让 AI 估算清洗的副作用

清洗完丢一句「如果按这个方案清洗，可能会误删多少正确数据」。AI 会评估风险，让你做最后决策。

一个常见失败案例 + 怎么补救

失败场景：你直接对全表跑 AI 给的公式，结果发现 200 行变成「格式错误」。

仔细一看：

  • 100 行是真的格式错（确实是脏数据）
  • 80 行是 AI 公式没考虑到的边界情况（如「+86-138-1234-5678」混合格式）
  • 20 行是 AI 误判（如「021-12345678」是合法座机，被标错）

补救方法：

  1. 回去补样本：把误判的 20 行贴给 AI，让它改进公式
  2. 加白名单：「021-」「010-」开头的视为合法座机
  3. 分批跑：5000 行先跑 1000 行检查，再跑下 1000 行

记住：AI 数据清洗不是替你做数据治理，是替你做批量处理。规则要你定，AI 只负责执行。

进阶玩法 + 类似场景

学会用 AI 清洗数据，同一套思路可以做很多事。

进阶玩法 1：让 AI 帮你写数据校验规则

清洗完后，让 AI 给一套数据验证规则（Excel 「数据 → 数据验证」），加在录入端。能从源头避免再脏。

进阶玩法 2：用 Power Query 替代公式

如果数据每周更新一次，让 AI 给 Power Query 步骤（M 语言）而不是 Excel 公式。Power Query 能记录清洗步骤，下次新数据来一键刷新。

进阶玩法 3：让 AI 做模糊匹配去重

「华为公司」和「华为科技有限公司」可能是同一家。让 AI 用模糊匹配算法（如 Levenshtein 距离）识别近似重复，给出合并建议。

类似场景：这套方法还能做什么

同样的「描述脏数据 + 给业务规则 + 让 AI 出公式」流程，可以套用到：

常见问题

Q：AI 能直接清洗 Excel 文件吗？ A：ChatGPT 和 Claude 都支持上传 .xlsx 文件后输出清洗后的版本。但实测稳定的做法：让 AI 给公式，自己在 Excel 里跑。这样能保留你对清洗结果的控制权，发现问题随时改公式。

Q：清洗后原始数据会丢吗？ A：不会。AI 给的是「在新列写公式」的方案，原始列不变。只有最后「选择性粘贴覆盖」时才会动原始列，建议先另存一份原始表。

Q：免费版 AI 能清洗吗？ A：能。清洗任务是公式生成，免费版 ChatGPT、Claude、Kimi 都够用。10 万行以上数据用 Kimi（长文本能力强）。

Q：脏数据有敏感信息（身份证、电话）能放 AI 吗？ A：不能放真实数据。把样本里的敏感字段打码（如「138****5678」），让 AI 看到「模式」而不是「真实值」就够给方案。

Q：清洗完发现误删了一些正确数据怎么办？ A：3 个补救步骤：① 立刻打开原始表备份；② 找出误删行，分析为什么被误判；③ 把这些行的特征反馈给 AI，让它改进公式重跑。