VBA 数据清洗

VBA 数据清洗指用 VBA 宏对 Excel 原始数据进行标准化、去重、格式统一和异常值处理的过程，适合月度报表合并、ERP 导出数据整理、客户信息清洗等重复性场景。一句话：把“肉眼纠正”变成“一键运行”。

入口位置

编写 VBA 数据清洗宏的前置准备：

相关问题：VBA 数据清洗实战案例、VBA 数据清洗完整指南

1. 启用开发工具选项卡：文件 → 选项 → 自定义功能区 → 勾选“开发工具”
2. 打开 VBA 编辑器：Alt + F11 或开发工具 → Visual Basic
3. 插入模块：在工程资源管理器（Ctrl+R）中右键 VBAProject → 插入 → 模块
4. 安全设置：文件 → 选项 → 信任中心 → 启用所有宏（仅用于自己的清洗工具，生产环境建议数字签名）

操作示例：销售表清洗（可复制）

清洗目标：将导出的销售记录从“日期是文本、金额含单位、区域有空格”整理为规范的 Excel 表。

模拟数据：A1:D5 区域

A	B	C	D
日期	区域	产品	金额
202401	华东	A	¥1,200
202402	华北	B	2,300元
202403	华东	C	1,800
	华南	A	¥1,050

VBA 清洗代码（复制到模块中运行）：

Sub CleanSalesData()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long
    Dim cell As Range
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' 步骤1：将文本日期转为真日期（假设数据在A列，格式为yyyymm）
    For i = 2 To lastRow
        If IsNumeric(ws.Cells(i, 1).Value) And Len(ws.Cells(i, 1).Value) = 6 Then
            ws.Cells(i, 1).Value = DateSerial(Left(ws.Cells(i, 1).Value, 4), _
                Mid(ws.Cells(i, 1).Value, 5, 2), 1)
            ws.Cells(i, 1).NumberFormat = "yyyy-mm"
        End If
    Next i
    
    ' 步骤2：去空格并去除尾部的“元”等单位
    For i = 2 To lastRow
        ' 去区域前后空格（B列）
        ws.Cells(i, 2).Value = Trim(ws.Cells(i, 2).Value)
        
        ' 去金额（D列）的字符，保留数字
        If TypeName(ws.Cells(i, 4).Value) = "String" Then
            ws.Cells(i, 4).Value = Val(Replace(Replace(ws.Cells(i, 4).Value, _
                "¥", ""), "元", ""))
        End If
    Next i
    
    ' 步骤3：补充空白单元格的上一个非空值（日期列常用）
    For i = 2 To lastRow
        If IsEmpty(ws.Cells(i, 1)) And Not IsEmpty(ws.Cells(i - 1, 1)) Then
            ws.Cells(i, 1).Value = ws.Cells(i - 1, 1).Value
        End If
    Next i
    
    ' 步骤4：删除全空行
    For i = lastRow To 2 Step -1
        If WorksheetFunction.CountA(ws.Rows(i)) = 0 Then
            ws.Rows(i).Delete
        End If
    Next i
    
    MsgBox "清洗完成，共处理 " & lastRow - 1 & " 行数据"
End Sub

运行后预期结果（D2：1200，D3：2300，D4：1800，D5：1050；A列全部为真日期格式；区域无多余空格；第5行A列被补充为“2024-01”）。

公式或快捷键示例

VBA 之外，以下三个功能路径也是 VBA 数据清洗经常调用的底层能力：

功能	功能区路径	对应的 VBA 代码
去重	数据 → 删除重复值	Range("A1:D5").RemoveDuplicates Columns:=Array(1,2,3,4)
分列（文本转数字）	数据 → 分列 → 固定宽度/分隔符号	Range("A:A").TextToColumns DataType:=xlDelimited
查找替换	开始 → 查找 → 替换	Cells.Replace What:="¥", Replacement:=""

快捷键组合（手动检查时有效）：

• 定位空值：Ctrl+G → 定位条件 → 空值
• 选择性粘贴数值：Ctrl+Alt+V → V → Enter
• 显示公式：Ctrl+`（反引号）

常见错误

1. 绝对引用与相对引用混淆

• 错误写法：Cells(i, 2).Value = Trim(Cells(i, 2).Value) 在某些情况下引用了不对的行
• 正确做法：统一用 ws.Cells(i, 2) 明确指定工作表，避免 ActiveSheet 在不同工作簿切换时引错对象

2. 数字被存为文本却未转换

• 表现：用 IsNumeric() 检查返回 True，但公式计算时报错
• 检查方法：先对一列执行 Range("D:D").NumberFormat = "0" 再赋值，或用 CDbl() 强制转双精度

3. 去重时未考虑标题行

• 错误：Range("A:D").RemoveDuplicates 可能把表头当作数据行处理
• 正确：Range("A2:D5").RemoveDuplicates，或在代码前加 If i = 1 Then Skip

4. 删除行时索引越界

• 错误：For i = 1 To lastRow 顺序删除，行号上移导致跳过
• 正确：For i = lastRow To 2 Step -1 从底部向上删除

5. Trim 无法完全清除 Unicode 空格

• 表现：Trim(cell) 后 Len 仍大于预期
• 处理：改用 Replace(cell.Value, ChrW(160), "") 去除不间断空格（NBSP）

常见问题

VBA 数据清洗 是什么？

VBA 数据清洗是指用 Visual Basic for Applications 编写宏，对 Excel 工作表中的数据执行去除重复、统一格式、填充空值、纠正错误等标准化操作的过程。适合每月固定格式的报表、系统导出的不规整数据、或多人录入后的汇总表。

VBA 数据清洗 怎么操作？

基本流程：识别数据源区域 → 逐列或逐行清洗（去空格、转格式、去重）→ 处理异常值 → 输出清理后结果。可直接在 VBA 中写循环，也可录制宏后修改。关键在操作前备份原始数据、先用小样本测试。

VBA 数据清洗 常见错误有哪些？

主要是：未锁定工作表引用导致跨表误操作；删除行时未从底部倒序；空值填充逻辑不严谨导致表头被覆盖；去重时数组下标写错；以及忘记对“数字文本”做 *1 或 CDbl 转换。每次清洗完做一次“选择性粘贴数值 + 检查公式是否报错”即可定位问题。