VBA数据清洗秘籍！4个实战案例+代码，效率狂飙10倍！-优快云博客

本文链接：https://blog.youkuaiyun.com/Start_mswin/article/details/148801764

"Excel数据像一团乱麻？人工处理耗时又易错？" 如果你还在为重复数据、格式混乱、无效字符等问题抓狂，这篇VBA数据清洗实战指南将彻底改变你的工作方式！本文直击数据清洗4大高频痛点场景——手机号精准提取、重复数据一键清理、日期格式标准化、多列数据智能合并，附赠可直接套用的VBA代码模板。掌握这些技巧，原本需要2小时的数据清洗工作，现在5分钟就能搞定！无论你是Excel小白还是VBA进阶者，都能从案例中解锁"自动化清洗"的核心密码，让数据整理效率实现质的飞跃！

VBA数据清洗示例代码与案例分享

在Excel中使用VBA进行数据清洗可大幅提升效率，以下是几个典型场景的案例和代码实现：

案例1：提取特定格式数据（手机号）

vba

        Sub ExtractPhoneNumbers()
    Dim regEx As Object, cell As Range
    Dim matches As Object, result As String
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
    Dim outputRow As Long: outputRow = 1
    
    ' 创建正则表达式对象
    Set regEx = CreateObject("VBScript.RegExp")
    regEx.Pattern = "1[3-9]\d{9}"  ' 11位手机号模式
    regEx.Global = True
    
    ' 遍历A列数据
    For Each cell In ws.Range("A1:A" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)
        If regEx.Test(cell.Value) Then
            Set matches = regEx.Execute(cell.Value)
            For Each match In matches
                ws.Cells(outputRow, "B").Value = match.Value
                outputRow = outputRow + 1
            Next match
        End If
    Next cell
End Sub

功能说明：从A列文本中提取所有11位手机号并输出到B列4。

案例2：数据去重与格式标准化

vba
Sub CleanDuplicateData() Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary") Dim ws As Worksheet: Set ws = ActiveSheet Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row Dim arrData, i As Long ' 读取数据到数组 arrData = ws.Range("A1:A" & lastRow).Value ' 清洗处理 For i = 1 To UBound(arrData) ' 格式标准化：去除空格/统一大写 arrData(i, 1) = UCase(Trim(arrData(i, 1))) ' 去重逻辑 If Not dict.Exists(arrData(i, 1)) And Len(arrData(i, 1)) > 0 Then dict.Add arrData(i, 1), i Else arrData(i, 1) = "" ' 标记重复项 End If Next i ' 写回处理结果 ws.Range("B1:B" & lastRow).Value = arrData ws.Range("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete End Sub

功能说明：对A列数据执行大小写转换、空格清理和重复项删除1。

案例3：日期格式统一

vba

        Sub StandardizeDates()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
    Dim rng As Range: Set rng = ws.Range("C2:C100")
    Dim cell As Range
    
    For Each cell In rng
        If IsDate(cell.Value) Then
            cell.NumberFormat = "yyyy-mm-dd"  ' 统一格式
            cell.Value = DateValue(cell.Value) ' 转换为标准日期
        Else
            cell.Interior.Color = RGB(255, 200, 200) ' 标记异常
        End If
    Next cell
End Sub

功能说明：将C列日期统一为ISO格式（yyyy-mm-dd），异常值高亮标记2。

案例4：多列数据组合清洗

vba

        Sub CombineCleanColumns()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Data")
    Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Dim i As Long
    
    For i = 2 To lastRow
        ' 合并三列数据并用"-"分隔
        ws.Cells(i, "D").Value = _
            CleanText(ws.Cells(i, "A")) & "-" & _
            CleanText(ws.Cells(i, "B")) & "-" & _
            CleanText(ws.Cells(i, "C"))
    Next i
End Sub

Function CleanText(inputText As String) As String
    ' 移除特殊字符/多余空格
    CleanText = Replace(Trim(inputText), Chr(160), "") ' 移除不间断空格
    CleanText = Replace(CleanText, vbCrLf, "")         ' 移除换行符
End Function

功能说明：合并三列数据并移除特殊字符，输出到D列3。