ARTICLE · 1353144

用 AI 写 Excel VBA ⑦:合并拆分后的脏数据,一条宏清干净

发布时间：2026-11-14 00:27:42 最近访问：2026-11-14 00:27:43

系列名称：用 AI 写 Excel VBA｜20 节零基础实战课
适用环境：Microsoft 365 / Excel 2016+ 桌面版，Windows（WPS 需另装 VBA 插件，Mac 部分 API 不支持）
阅读时长：约 12 分钟
难度：⭐⭐⭐（实战篇，需要前 6 讲基础）

🎯 一句话成果

今天你拿到一段数据清洗宏：一键完成去重复、删空行、去首尾空格、清除非打印字符。合并完的脏表、从系统导出的乱表，跑一次就干净。

📌 场景痛点

第 5 讲合并完 20 个文件，第 6 讲拆分完总表，你打开结果一看：

同一个订单号出现了 3 次（重复值）
中间夹着几行空行（合并时带进来的）
客户名前面有空格，"张三"和"　张三"被当成两个人
某些单元格里有乱码字符（从网页复制来的 CHAR(160) 非断行空格）
金额列混进了文本格式的数字

你开始手动：数据→删除重复值→筛选空行→右键删除→逐个 Trim……5000 行搞完，半小时没了，还漏了几个。

这些活，VBA 一个循环全干完。

但注意：数据清洗里有两个经典翻车点，新手必踩：

正序删行会跳行漏删
——删了第 5 行，原来的第 6 行变成新的第 5 行，循环就跳过了
RemoveDuplicates 只认连续区域
——如果中间有空行隔开，去重不完整

今天一次性全解决。

🧠 清洗规则定义（让 AI 明确边界）

清洗项	操作	注意事项
重复值	按指定列去重，保留首次出现	要告诉 AI 按哪列判断
空行	整行全空的行删除	用倒序循环
首尾空格	文本列 `Trim`	区分文本列和数字列
非打印字符	`Clean` + 替换 `CHAR(160)`	网页复制常见
文本型数字	转成真数字	金额列常见

🤖 这一讲用的提示词（直接抄）

【角色】你是资深 Excel VBA 专家，代码要稳、带中文注释、带错误处理。【场景】当前工作簿有一个"清洗源"工作表，第1行是标题，第2行起是数据。数据约 5000 行，A到G列。已知问题：- 有重复行（按A列"订单号"判断）- 有整行全空的行- 文本列（B列客户名、C列产品名）首尾有空格- 部分单元格含非打印字符（如 CHAR(160) 网页空格）- E列金额有时是文本格式【动作】写一个数据清洗宏，一键完成以下操作：1. 按 A 列（订单号）去重，保留首次出现2. 删除整行全空的行（倒序循环）3. B列和C列文本去首尾空格 + Clean非打印字符4. E列金额转为数字格式5. 清洗前弹窗确认，显示将执行哪些操作6. 清洗后弹窗报告：删了几行重复、删了几行空行、清洗了多少单元格【规则】- 倒序删行（For i = lastRow To 2 Step -1）- 不修改列结构、不删列- 清洗前不清空任何数据，只做"净化"- 关闭屏幕刷新【输出】完整可运行 Sub，中文注释，On Error 兜底。

📋 AI 给的代码（完整可运行）

Sub 一键数据清洗()    '========================================    '功能：去重 + 删空行 + 去空格 + 清乱码 + 转数字    '核心：倒序删行 + Trim/Clean + RemoveDuplicates    '========================================    Dim ws As Worksheet    Dim lastRow As Long, i As Long, j As Long    Dim rng As Range    Dim cellVal As String    Dim dupCount As Long, emptyCount As Long, cleanCount As Long    Dim msg As String    On Error GoTo ErrorHandler    Application.ScreenUpdating = False    Application.Calculation = xlCalculationManual    Set ws = ThisWorkbook.Sheets("清洗源")    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row    If lastRow < 2 Then        MsgBox "数据不足，无需清洗。", vbInformation        GoTo CleanUp    End If    '---- 确认弹窗 ----    msg = "将对""清洗源""表执行以下清洗操作：" & vbCrLf & vbCrLf    msg = msg & "1. 按 A列（订单号）去重，保留首次出现" & vbCrLf    msg = msg & "2. 删除整行全空的行（倒序安全删除）" & vbCrLf    msg = msg & "3. B/C列去首尾空格 + 清除非打印字符" & vbCrLf    msg = msg & "4. E列金额转为数字格式" & vbCrLf & vbCrLf    msg = msg & "是否继续？"    If MsgBox(msg, vbYesNo + vbQuestion, "确认清洗") <> vbYes Then        MsgBox "已取消。", vbInformation        GoTo CleanUp    End If    dupCount = 0    emptyCount = 0    cleanCount = 0    '===== 步骤1：按A列去重 =====    '先标记重复（用 CountIf 辅助列），后面统一删    '注意：RemoveDuplicates 方法简单但不够灵活，用辅助列法更可控    ws.Columns("H").Clear  ' 清辅助列    ws.Cells(1, "H").Value = "重复标记"    Dim dict As Object    Set dict = CreateObject("Scripting.Dictionary")    For i = 2 To lastRow        Dim orderId As String        orderId = Trim(CStr(ws.Cells(i, 1).Value))        If dict.Exists(orderId) And orderId <> "" Then            ws.Cells(i, "H").Value = "重复"            dupCount = dupCount + 1        ElseIf orderId <> "" Then            dict(orderId) = i        End If    Next i    '===== 步骤2+3：倒序删空行 + 删重复行 =====    '重新计算末行（去重后可能变少）    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row    For i = lastRow To 2 Step -1        '检查是否重复行        If ws.Cells(i, "H").Value = "重复" Then            ws.Rows(i).Delete        Else            '检查是否空行            Set rng = ws.Range("A" & i & ":G" & i)            If Application.WorksheetFunction.CountA(rng) = 0 Then                ws.Rows(i).Delete                emptyCount = emptyCount + 1            End If        End If    Next i    '清辅助列    ws.Columns("H").Clear    '===== 步骤3：B/C列去空格 + Clean =====    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row    For i = 2 To lastRow        'B列：客户名        If Trim(ws.Cells(i, 2).Value) <> "" Then            cellVal = ws.Cells(i, 2).Value            'Trim + Clean + 替换 CHAR(160)            cellVal = Trim(Application.WorksheetFunction.Clean(cellVal))            cellVal = Replace(cellVal, Chr(160), " ")            cellVal = Replace(cellVal, Chr(9), " ")  ' Tab            ws.Cells(i, 2).Value = cellVal            cleanCount = cleanCount + 1        End If        'C列：产品名        If Trim(ws.Cells(i, 3).Value) <> "" Then            cellVal = ws.Cells(i, 3).Value            cellVal = Trim(Application.WorksheetFunction.Clean(cellVal))            cellVal = Replace(cellVal, Chr(160), " ")            ws.Cells(i, 3).Value = cellVal            cleanCount = cleanCount + 1        End If    Next i    '===== 步骤4：E列金额转数字 =====    For i = 2 To lastRow        If ws.Cells(i, 5).Value <> "" Then            '如果看起来是数字但存储为文本            If Not IsNumeric(ws.Cells(i, 5).Value) Then                '尝试清理后转换                Dim numVal As Double                On Error Resume Next                numVal = CDbl(Replace(Replace(ws.Cells(i, 5).Value, ",", ""), " ", ""))                On Error GoTo 0                If Err.Number = 0 Then                    ws.Cells(i, 5).Value = numVal                    ws.Cells(i, 5).NumberFormat = "#,##0.00"                End If                Err.Clear            Else                '已经是数字，确保格式正确                ws.Cells(i, 5).NumberFormat = "#,##0.00"            End If        End If    Next iCleanUp:    Application.Calculation = xlCalculationAutomatic    Application.ScreenUpdating = True    MsgBox "清洗完成！" & vbCrLf & vbCrLf & _           "删除重复行：" & dupCount & vbCrLf & _           "删除空行：" & emptyCount & vbCrLf & _           "文本清洗单元格数：" & cleanCount & vbCrLf & vbCrLf & _           "数据已净化，请检查结果。", vbInformation    Exit SubErrorHandler:    Application.Calculation = xlCalculationAutomatic    Application.ScreenUpdating = True    MsgBox "清洗出错：" & Err.Description & vbCrLf & "出错行：" & i, vbCriticalEnd Sub

🔍 逐段拆解（核心 4 处）

1）倒序删行——永远不跳行的秘密

For i = lastRow To 2 Step -1    ws.Rows(i).DeleteNext i

为什么不能正序？

想象一下：你站在第 5 行，删了它。原来的第 6 行现在变成了第 5 行。循环走到 i = 6，但新的第 5 行（原来的第 6 行）被跳过了。

倒序就不存在这个问题：从最后一行往上删，删第 5 行时，第 6 行不受影响（它还在第 6 行）。

这是 VBA 数据清洗里最重要的一个知识点，没有之一。

2）辅助列标记重复 + 统一删除

ws.Cells(i, "H").Value = "重复"  ' 标记' ...If ws.Cells(i, "H").Value = "重复" Then ws.Rows(i).Delete

用 Dictionary 判断重复，标记在辅助列 H，然后统一删除。比 RemoveDuplicates 方法更灵活：

可以精确控制"按哪列判断"
可以统计删了多少行
可以和空行删除合并在同一轮倒序循环里

3）`Clean` + `Trim` + `Replace(Chr(160))` 三连击

cellVal = Trim(Application.WorksheetFunction.Clean(cellVal))
cellVal = Replace(cellVal, Chr(160), " ")

Trim
：去掉首尾空格
Clean
：去掉 ASCII 0-31 的非打印字符
Replace(Chr(160), " ")
：网页复制常见的"非断行空格"，Trim 去不掉它，必须单独处理

三连击覆盖 99% 的"看不见的脏字符"。

4）文本型数字转换

numVal = CDbl(Replace(Replace(ws.Cells(i, 5).Value, ",", ""), " ", ""))

金额列常见"1,234.56"被存为文本。先去掉逗号和空格，再 CDbl 转数字。转成功后设格式为 #,##0.00。

⚠️ 数据清洗的 5 个真·坑

现象	根因	解决
去重后数据变少了，但没删干净	重复行中间有空行隔开，`RemoveDuplicates` 只认连续区域	用辅助列 + Dictionary 法（本讲代码）
删空行时跳行漏删	正序循环删行	改为倒序 `For i = lastRow To 2 Step -1`
`Trim` 后空格还在	是 `CHAR(160)` 网页空格	加 `Replace(Chr(160), " ")`
文本转数字后变成 `####`	列宽不够	加 `Columns.AutoFit`
清洗完发现数据不对	清洗前没备份	代码里加备份提示，或手动 `Ctrl+Z` 回退

🛠️ 让 AI 改版的常用追问

基础宏跑通后，可以直接追：

"加一个撤销功能：清洗前自动备份到新 sheet"
"增加清洗日志：记录每行做了什么操作，输出到新 sheet"
"增加按多列去重：A列+B列相同才算重复"
"增加异常值检测：金额为负或超过 100 万的标黄"
"清洗完后自动生成数据质量报告（重复率、空值率、异常值统计）"

🐍 Python 对比教学（顺手多学一手）

同样需求，Python 用 pandas：

import pandas as pddf = pd.read_excel("清洗源.xlsx")# 去重df = df.drop_duplicates(subset=["订单号"], keep="first")# 删空行df = df.dropna(how="all")# 去空格df["客户名"] = df["客户名"].str.strip().str.replace(r"\s+", " ", regex=True)df["产品名"] = df["产品名"].str.strip().str.replace(r"\s+", " ", regex=True)# 转数字df["金额"] = pd.to_numeric(df["金额"].astype(str).str.replace(",", ""), errors="coerce")df.to_excel("清洗后.xlsx", index=False)

对比：pandas 链式调用更简洁，但 VBA 宏在同事电脑上零部署直接跑。

⚠️ 排错速查

报错	原因	修法
运行时错误 13：类型不匹配	`CDbl` 转换失败	检查是否有非数字字符（如"暂无""N/A"），加 `IsNumeric` 判断
清洗后数据全没了	`lastRow` 算错，从 0 开始倒序	检查末行计算逻辑
辅助列 H 没清掉	代码异常中断	手动删 H 列，或在错误处理里加 `ws.Columns("H").Clear`
宏运行完 Excel 卡死	没恢复 `ScreenUpdating`	错误处理里也要恢复

📌 本讲小结

你今天拿到了	能力
完整清洗宏	去重+删空行+去空格+清乱码+转数字
倒序删行	VBA 数据清洗第一铁律
辅助列标记法	比 `RemoveDuplicates` 更灵活
Trim+Clean+Replace 三连	覆盖 99% 脏字符
排错清单	跳行/漏删/类型不匹配一眼定位