每天在清理 Excel?让 AI 写段 VBA,一键洗完 5 类脏数据
- 2026-11-06 16:21:45
每天在清理 Excel?让 AI 写段 VBA,一键洗完 5 类脏数据一张 12 行的销售明细，5 类问题，手工要清 6 分钟。这一课给你一条完整链路：说清规则 → AI 写代码 → 粘进 Excel → 点一下。 V Excel 自动化实验室· 10月21日 · 阅读约 8 分钟 这一课不讲语法，也不从变量、循环开始。我们只做一件事：把一张脏表洗干净。下面你会看到真实的表格截图、能直接复制的提示词、能直接跑的代码，以及跑完之后弹出的那份报告。 01 先看这张表：12 行，5 类问题 月初，五家门店把销售明细报上来。12 行数据，看上去挺整齐，直接拿去透视也没报错。 
图 1：原始销售明细，浅红为 5 类问题所在（示例数据为虚构） 但把它丢进数据透视表，问题就一个个冒出来了： 重复记录 第 4 行和第 3 行一模一样，SO-1002 被报了两遍。销售额凭空多出 840。 空白行 第 6 行、第 13 行整行是空的。排序后沉到底部，筛选时插在中间。 多余空格 第 9、10 行的内容前后带着看不见的空格。"SO-1006" 和 "SO-1006 " 在 Excel 眼里是两个不同的值，VLOOKUP 直接匹配不上。 文本型数字 第 11 行的数量左上角有个绿三角，看着是 1，实际存的是文本 "1"。SUM 会直接跳过它，合计少算 1 件。 缺失与格式异常 第 7、8 行销售员是空的；第 8 行日期写成了 2026.01.09，跟其他行的 2026/1/8 不是一路。 一句话概括：这张表"看起来能用"，实际上每一处都在悄悄污染结果。最要命的是它不报错——你只有在对账发现差了几百块的时候，才会回头怀疑它。 ✦ 02 Before：手工处理，我要走 6 步 1 Ctrl+H 查找替换，把空格全删掉。但这么干会误伤"上海 一店"这种名字中间本来就有空格的。 2 「数据」→「删除重复项」，勾选全部 7 列。勾少了删不干净，勾多了会把不同订单误删。 3 筛选空白行，选中可见行，右键删除。删完还得确认一下有没有删多。 4 选中数量列 →「数据」→「分列」→ 直接点完成，把文本硬转成数字。这一步最容易漏格子。 5 销售员空的两行：翻通讯录、翻群聊、或者先标黄放着。 6 把 2026.01.09 手动改成 2026/1/9，顺便检查还有没有同类的。 ⚠️真正麻烦的不是慢，是"漏" 第 4 步最容易出问题：你以为转好了，其实还有几格是文本。合计结果悄悄少了，等到季度对账才发现。而且换一份新表，这 6 步要原样重来一遍，规则全靠你的脑子记。 ✦ 03 第一步不是写代码，是把规则说清楚 AI 写不出它不知道的规则。 所以在打开对话框之前，先做一件事：把"什么样的数据算脏、脏了怎么处理"一条条写下来。这张表，我定了 5 条： R1 整行全空 跳过，不写进结果。 R2 7 个字段完全相同 只保留第一条。 R3 去掉首尾空格 包括网页复制带来的不间断空格；名字中间的空格要保留。 R4 文本型数字转真数字 只处理数量和金额两列，其他列不动。 R5 该由人决定的，标黄不代劳 销售员为空、日期认不出来 → 整行标浅黄，右侧写上原因，不删也不改。 最后再补一句：结果写到新工作表「清洗结果」，原表一个字都不许动。 💡为什么第 5 条要"标黄不删"？ 因为"销售员是谁"和"2026.01.09 到底是几月几号"，这两个问题只有你知道答案。让代码去猜，等于给三个月后的自己埋一颗雷。 ✦ 04 把上面这些话，原样丢给 AI 下面这段提示词可以直接复制。方括号里的内容换成你自己的表和规则，其余照抄就行。 提示词 · 完整版 你是一位熟悉 Excel VBA 的办公自动化开发人员。请帮我写一个可以直接运行的 VBA 宏，用来清洗一张销售明细表。请严格按下面的规则实现，不要自行增加或删减。 【一、表格结构】 · 工作簿里有一张名为「销售明细」的工作表，表头在第 1 行，数据从第 2 行开始。 · 共 7 列，顺序为： A 订单编号 / B 日期 / C 门店 / D 销售员 / E 产品编码 / F 数量 / G 金额 · 订单编号每行必有，可以用它来定位最后一行。 · 数据量约 2000 行，以后可能增长到 2 万行。 【二、处理规则】 R1 整行所有列都为空 → 跳过，不写入结果。 R2 7 个字段的值完全相同 → 只保留第一次出现的那条，后面的跳过。 R3 每个单元格去掉首尾空格；要先把不间断空格 Chr(160) 替换成普通空格， 再执行 Trim。单元格内部（比如门店名中间）的空格必须保留。 R4 只处理 F 列数量和 G 列金额：如果单元格是文本型数字，转成真正的数字； F 列格式设为 0，G 列格式设为 #,##0.00。其他列不要做类型转换。 R5 满足下列任一条件的行，整行填充浅黄底色 RGB(255,242,204)， 并在 H 列写上原因，不删除、不修改原有值： · D 列销售员为空 · B 列日期用 IsDate 判断为假（认不出来） 原因文本用中文分号连接，例如「销售员为空；日期无法识别；」 【三、输出要求】 · 结果写入名为「清洗结果」的新工作表；如果它已存在，先清空再写入。 · 第 1 行复制原表表头，并在 H1 写入「待确认原因」。 · 「销售明细」原表的任何单元格都不允许修改或删除。 · 处理完成后弹出 MsgBox，报告：读取行数、删除空白行数、删除重复行数、 清除空格处数、文本转数值个数、标黄行数、最终保留行数、耗时（秒）。 · 最后自动调整列宽，表头加粗。 【四、代码要求】 1. 给完整代码，不要片段，从 Sub 写到 End Sub。 2. 顶部写清楚：宏名称、作用、如何安装、如何运行。 3. 关键步骤加中文注释，注释写在对应代码上方。 4. 变量声明齐全，模块顶部加 Option Explicit。 5. 如果「销售明细」工作表不存在，或者没有数据行，给出友好提示并退出， 不要抛出原始错误。 6. 不要用 Select / Activate 操作单元格，避免屏幕闪烁。 7. 先复述你理解的 5 条规则，我确认无误后你再输出代码。 这段提示词里，有四样东西是必须写清的，缺一样结果就不稳： 表结构 几列、表头在第几行、哪一列是唯一编号。不写这个，AI 会自己猜，猜错就是全盘皆错。 规则编号 R1 到 R5 分开写。这样后面你说"第 3 条改成只去半角空格"，它能精确定位到那一行。 输出位置 写新表还是覆盖原表，必须明说。"不要动原表"这句话，值得每次都写。 边界要求 遇到认不出的怎么办、数据量多大、要不要弹报告。这三句决定了代码是"能跑"还是"敢用"。 ✦ 05 这是它给我的代码，可以直接跑 AI 返回的版本我改了三处：补了一列"待确认原因"，加了输出表已存在的判断，把报错处理去掉换成前置检查。下面是可以直接用的完整版。 VBA · CleanSalesData Option Explicit '=============================================================== ' 宏名称：CleanSalesData ' 作用 ：把「销售明细」清洗成一张能直接做透视的干净表 ' 安装 ：Alt+F11 → 插入 → 模块 → 粘贴本段 → 按 F5 ' 前提 ：表头在第 1 行，数据从第 2 行开始，共 A 到 G 七列 ' 注意 ：文件需另存为 .xlsm；首次运行请先在副本上测试 '=============================================================== Sub CleanSalesData() Dim wsSrc AsWorksheet, wsOut AsWorksheet Dim lastRow AsLong, lastCol AsLong Dim r AsLong, c AsLong, outRow AsLong Dim v AsVariant, key AsString Dim blankRow AsBoolean, dupRow AsBoolean Dim why AsString Dim cntBlank AsLong, cntDup AsLong Dim cntTrim AsLong, cntNum AsLong, cntFlag AsLong Dim seen AsObject Dim t AsDouble t = Timer lastCol = 7 '---- 1. 找到数据源 ---- OnErrorResumeNext Set wsSrc = ThisWorkbook.Worksheets("销售明细") OnError GoTo 0 If wsSrc IsNothingThen MsgBox"找不到名为「销售明细」的工作表，请改成你的表名。", _ vbExclamation, "提示" ExitSub EndIf '---- 2. 定位最后一行 ---- ' 先按第 1 列向上找；再用 UsedRange 兜住表格末尾的空白行 lastRow = wsSrc.Cells(wsSrc.Rows.Count, 1).End(xlUp).Row If wsSrc.UsedRange.Row + wsSrc.UsedRange.Rows.Count - 1 > lastRow Then lastRow = wsSrc.UsedRange.Row + wsSrc.UsedRange.Rows.Count - 1 EndIf If lastRow < 2 Then MsgBox"「销售明细」里没有数据行。", vbExclamation, "提示" ExitSub EndIf '---- 3. 准备输出表：已存在就清空，原表一律不动 ---- OnErrorResumeNext Set wsOut = ThisWorkbook.Worksheets("清洗结果") OnError GoTo 0 If wsOut IsNothingThen Set wsOut = ThisWorkbook.Worksheets.Add(After:=wsSrc) wsOut.Name = "清洗结果" Else wsOut.Cells.Clear EndIf wsSrc.Rows(1).Copy wsOut.Rows(1) ' 复制表头 wsOut.Cells(1, 8).Value = "待确认原因" outRow = 2 Set seen = CreateObject("Scripting.Dictionary") '---- 4. 逐行处理 ---- For r = 2 To lastRow ' R1 整行全空 → 跳过 blankRow = (Application.CountA(wsSrc.Range(wsSrc.Cells(r, 1), _ wsSrc.Cells(r, lastCol))) = 0) If blankRow Then cntBlank = cntBlank + 1 Else ' R3 去首尾空格，同时拼一个内容指纹用于判重 key = "" For c = 1 To lastCol v = wsSrc.Cells(r, c).Value IfVarType(v) = vbString Then v = Trim(Replace(CStr(v), Chr(160), " ")) If v <> CStr(wsSrc.Cells(r, c).Value) Then _ cntTrim = cntTrim + 1 EndIf wsOut.Cells(outRow, c).Value = v key = key & "|" & CStr(v) Next c ' R2 指纹出现过 → 这条是重复的 dupRow = seen.Exists(key) If dupRow Then cntDup = cntDup + 1 wsOut.Range(wsOut.Cells(outRow, 1), _ wsOut.Cells(outRow, 7)).ClearContents Else seen.Add key, True ' R4 数量、金额：文本型数字转真数字 For c = 6 To 7 IfVarType(wsOut.Cells(outRow, c).Value) = vbString Then IfIsNumeric(wsOut.Cells(outRow, c).Value) Then wsOut.Cells(outRow, c).Value = _ CDbl(wsOut.Cells(outRow, c).Value) If c = 6 Then wsOut.Cells(outRow, c).NumberFormat = "0" Else wsOut.Cells(outRow, c).NumberFormat = "#,##0.00" EndIf cntNum = cntNum + 1 EndIf EndIf Next c ' R5 该由人决定的，标黄不代劳 why = "" IfLen(wsOut.Cells(outRow, 4).Value) = 0 Then why = why & "销售员为空；" EndIf IfNotIsDate(wsOut.Cells(outRow, 2).Value) Then why = why & "日期无法识别；" EndIf IfLen(why) > 0 Then wsOut.Range(wsOut.Cells(outRow, 1), _ wsOut.Cells(outRow, 8)).Interior.Color = _ RGB(255, 242, 204) wsOut.Cells(outRow, 8).Value = why cntFlag = cntFlag + 1 EndIf outRow = outRow + 1 EndIf EndIf Next r '---- 5. 收尾 ---- wsOut.Columns("A:H").AutoFit wsOut.Rows(1).Font.Bold = True MsgBox"清洗完成！" & vbCrLf & vbCrLf & _ "读取记录：" & (lastRow - 1) & " 行" & vbCrLf & _ "删除空白行：" & cntBlank & " 行" & vbCrLf & _ "删除重复记录：" & cntDup & " 行" & vbCrLf & _ "清除多余空格：" & cntTrim & " 处" & vbCrLf & _ "文本转数值：" & cntNum & " 个" & vbCrLf & _ "标黄待确认：" & cntFlag & " 行" & vbCrLf & _ "最终保留：" & (outRow - 2) & " 行" & vbCrLf & _ "耗时：" & Format(Timer - t, "0.00") & " 秒" & vbCrLf & vbCrLf & _ "结果写入「清洗结果」表，原始数据未修改。", _ vbInformation, "处理报告" EndSub 💡这段代码只做五件事 跳空行、去重、清空格、转数值、标黄可疑行。没有一句是为了炫技，变量名也都是英文加中文注释。你不需要看懂全部，只要知道每一块对应上面哪条规则。 ✦ 06 粘进去，跑起来：4 步 1 在 Excel 里按 Alt + F11，打开 VBA 编辑器。 2 菜单栏「插入」→「模块」。左侧工程窗口里会多出一个 Module1。 3 把上面整段代码粘进右侧的空白代码窗口。 4 光标点进代码里，按 F5，或点工具栏那个绿色三角。 
图 2：粘贴完代码的 VBA 编辑器，左侧「模块 → Module1」就是代码存放的地方 ⚠️两个必须做的动作 一、另存为 .xlsm。 .xlsx 存不了宏，关掉文件代码就没了。二、第一次运行，先 复制一份文件当试验品。哪怕代码写了"不动原表"，也请给它一次犯错的机会，别给真实数据。 ✦ 07 After：点一下，弹出这份报告 
图 3：运行结束弹出的处理报告，每个数字都能和原始表对上 这 9 个数字不是随便写的。原始 12 行，减去 2 个空行、1 条重复，剩 9 行；2 处空格、1 个文本数字被就地修正；2 行因为缺销售员、日期认不出来被标了黄。 
图 4：「清洗结果」表。多出的 H 列是"待确认原因"，浅黄两行是要你来决定的 原表呢？打开「销售明细」，12 行一字未动。这意味着你可以随时重跑、随时对比、随时回退。 对比项 手工 6 步 这段宏 处理 12 行 约 6 分钟 0.03 秒 处理 2000 行 更久，且更容易看漏 通常 1 秒内 下次换新表 6 步重来一遍 打开、按 F5 规则存在哪 你的脑子里 代码里，随时能改 ✅关于"快了多少"，说点实在的 0.03 秒是 12 行样例在我这台电脑上的实测值。2000 行是 0.5 秒还是 3 秒，取决于你的配置、表里有没有公式、有没有条件格式。我不打算给你一个"效率提升 98%"的数字。真正稳定可衡量的收益是：6 个需要记住顺序的动作，变成了 1 次点击。 ✦ 08 这一篇只讲 3 个知识点 代码里还有循环、字典、单元格对象，这些后面章节会慢慢出现。这一篇只挑三个：它们如果没搞懂，后面每个宏都会踩同一个坑。 K1 怎么准确找到"最后一行" 这一行是整个宏的地基。它等价于：光标停在 A 列最底下（第 1048576 行），然后按 Ctrl + ↑。 K1 · 定位最后一行 ' 先按订单编号列向上找，相当于光标停在 A 列底部按 Ctrl + ↑ lastRow = wsSrc.Cells(wsSrc.Rows.Count, 1).End(xlUp).Row ' 再用 UsedRange 兜一次底，覆盖表格末尾的空白行 If wsSrc.UsedRange.Row + wsSrc.UsedRange.Rows.Count - 1 > lastRow Then lastRow = wsSrc.UsedRange.Row + wsSrc.UsedRange.Rows.Count - 1 EndIf 它有三个坑，每一个我都真实踩过： 它只看内容，不看格式。 单元格铺了底色、加了边框、设了格式，但没有值，它照样跳过。 关键列中间不能断。 如果 A 列第 500 行是空的，它就停在第 499 行，后面的数据全部漏掉。所以代码里认的是"订单编号"这种每行必有的列。 表格末尾的空白行，它压根看不见。 图 1 里第 13 行是空的，它实际会停在 12。所以我们还得用 UsedRange 兜一次底。 K2 .Value 和 .Text 不是一回事 图 1 第 11 行那个绿三角，就是这件事最直观的样子：单元格里显示 1，实际存的是文本 "1"。 .Text拿到的是"你看到的"——受列宽和显示格式影响，列宽不够时会变成一串 ####。.Value拿到的是"真正存的"——文本就是文本，数字就是数字。 所以判断文本型数字要这么写： K2 · 判断文本型数字 ' 先确认它是文本，再确认长得像数字，最后才转 IfVarType(cell.Value) = vbString Then IfIsNumeric(cell.Value) Then cell.Value = CDbl(cell.Value) EndIf EndIf K3 Trim 去不掉什么 Trim 只去首尾的普通空格。下面这三样它一概不管： 全角空格 中文输入法下按空格打出来的那个，宽度不一样的。 不间断空格 Chr(160) 从网页、PDF、其他系统复制过来的数据里最常见，肉眼完全看不出来。 换行符与制表符 Alt+Enter 打出来的那种，会让一个单元格看着正常、实际占两行。 所以代码里写的是 先替换、再 Trim： K3 · 先替换再 Trim ' 先把不间断空格换成普通空格，再 Trim ' 顺序如果反了，Chr(160) 会原封不动留在单元格里 v = Trim(Replace(CStr(v), Chr(160), " ")) ✦ 09 宏故意留下的那两行 回到图 4，看第 5 行和第 6 行。它们是浅黄色的，右边写着原因。 SO-1004：销售员空。SO-1005：销售员空，日期写成 2026.01.09。 为什么不一并处理掉？ 因为销售员空，代码不知道补谁；2026.01.09 既可能是 1 月 9 日，也可能是个编号。这些问题只有你知道答案，让代码猜，就是给你自己埋雷。 AI 生成的代码不是标准答案，它只是把你写下的规则，翻译成了一串动作。规则没写到的地方，它一定会露馅。你要做的，是把露馅的地方找出来，而不是假装没看见。 ✦ 10 拿去就能用：3 个边界测试 代码跑通只是及格线。真正的验证，是拿它去撞三次墙。在副本上做，每个测试不超过两分钟： A 在 A 列中间挖空一格 测试它会不会提前停住。如果结果少了后面一大截，说明你的表必须保证订单编号列没有空格——这正是 K1 讲的第二个坑。 B 把某个金额改成 0 或负数 看它会不会被当成异常。本宏不判断金额正负，如果你需要，就在提示词里加一条 R6，让它标黄。 C 在表头上方插三行标题 让表头挪到第 4 行。代码会按第 1 行取表头，结果全乱。改法很简单：把代码里所有 1和 2的起始行号换成 4 和 5。 📌这一课的四个要点 ▸先写规则，再要代码。规则编号（R1–R5）是你后面改需求的抓手。 ▸代码只写结果表，原表一个字不动。这是你敢反复试的前提。 ▸该由人决定的（缺人、日期歧义），标黄不代劳。 ▸跑通不算完，拿 A/B/C 三个边界测试撞一遍，才算敢用。 你手上的表，最脏的是哪一种？ 重复记录 / 空白行 / 多余空格 / 文本数字 / 日期混乱 评论区说一个最让你崩溃的场景，我挑典型的写进后续章节 👇
本文来自网友投稿或网络内容，如有侵犯您的权益请联系我们删除，联系邮箱：wyl860211@qq.com 。
