原创

又学一招：Excel 数据对比及 VBA 实现方案

在日常办公和数据处理中，经常会遇到 数据比对 的需求。例如：

财务部门需要核对两个报表是否一致；
测试人员需要比对导出数据与系统计算结果；
运营人员需要对比不同时间的数据差异。

如果只是少量数据，可以人工肉眼对比，但当数据量较大、结构复杂时，人工方式既低效又容易出错。此时，借助 Excel VBA 自动化对比 就成为一种高效解决方案。

为什么选择 VBA 进行 Excel 对比？

无需第三方工具：只依赖 Excel 自带的宏功能，兼容性好；
自动化处理：大幅减少人工核对时间；
结果清晰：可以选择输出差异清单，或直接在原表高亮；
扩展性强：既能处理单一工作表，也能扩展到多个文件、多个 Sheet。

一、单个 Excel Sheet 对比

📌 应用场景

对比同一文件中两个 Sheet（如“原始数据”和“导出数据”）；
对比同一张表中不同版本的数据。

📜 VBA 示例：单 Sheet 对比并输出差异

Sub CompareTwoSheets()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim r1 As Range, r2 As Range
    Dim maxRow As Long, maxCol As Long
    Dim i As Long, j As Long

    '👉 指定要对比的工作表名称
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")

    '计算最大行列（这里取两张表的最大范围，避免遗漏）
    maxRow = Application.Max(ws1.UsedRange.Rows.Count, ws2.UsedRange.Rows.Count)
    maxCol = Application.Max(ws1.UsedRange.Columns.Count, ws2.UsedRange.Columns.Count)

    '先清除旧的高亮
    ws1.UsedRange.Interior.ColorIndex = xlNone
    ws2.UsedRange.Interior.ColorIndex = xlNone

    '逐格对比
    For i = 1 To maxRow
        For j = 1 To maxCol
            Set r1 = ws1.Cells(i, j)
            Set r2 = ws2.Cells(i, j)
            '值不一致时高亮
            If r1.Text <> r2.Text Then
                r1.Interior.Color = vbYellow   'Sheet1 标黄
                r2.Interior.Color = vbGreen    'Sheet2 标绿
            End If
        Next j
    Next i

    MsgBox "对比完成！黄色 = Sheet1 差异，绿色 = Sheet2 差异", vbInformation
End Sub

📊 结果示例

执行后以不同颜色标识差异数据，清晰直观。

二、多个 Excel 文件、多 Sheet 对比

📌 应用场景

核对两个不同文件的数据（如两个版本的财务报表）；
对比多个 Sheet（如一个月内多个子表的内容）。

📜 VBA 示例：跨文件多 Sheet 对比

Sub CompareMultipleSheets()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim wsDiff As Worksheet
    Dim r As Long, c As Long, diffRow As Long
    Dim maxRow As Long, maxCol As Long
    Dim i As Long
    
    ' 打开两个需要对比的文件
    Set wb1 = Workbooks.Open("C:\Users\YourName\Desktop\File1.xlsx")
    Set wb2 = Workbooks.Open("C:\Users\YourName\Desktop\File2.xlsx")
    
    ' 新建差异表
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Sheets("Diff").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    Set wsDiff = ThisWorkbook.Sheets.Add
    wsDiff.Name = "Diff"
    wsDiff.Range("A1:E1").Value = Array("Sheet 名称", "行号", "列号", "File1 值", "File2 值")
    diffRow = 2
    
    ' 遍历两个文件的 Sheet
    For i = 1 To wb1.Sheets.Count
        Set ws1 = wb1.Sheets(i)
        Set ws2 = wb2.Sheets(i)
        
        ' 获取最大行列
        maxRow = ws1.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
        maxCol = ws1.Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
        
        ' 遍历对比
        For r = 1 To maxRow
            For c = 1 To maxCol
                If ws1.Cells(r, c).Value <> ws2.Cells(r, c).Value Then
                    wsDiff.Cells(diffRow, 1).Value = ws1.Name
                    wsDiff.Cells(diffRow, 2).Value = r
                    wsDiff.Cells(diffRow, 3).Value = c
                    wsDiff.Cells(diffRow, 4).Value = ws1.Cells(r, c).Value
                    wsDiff.Cells(diffRow, 5).Value = ws2.Cells(r, c).Value
                    diffRow = diffRow + 1
                End If
            Next c
        Next r
    Next i
    
    MsgBox "多文件多 Sheet 对比完成！共发现 " & diffRow - 2 & " 处差异。"
End Sub

📊 优势

可批量比对多个 Sheet；
自动输出差异清单，避免逐个手工对比；
特别适合跨部门对账、历史版本数据校验等。

三、方案对比

对比方式	适用场景	优势	劣势
条件格式公式	小规模数据	简单直观	只能单 Sheet
手工 VLOOKUP	单表查找差异	操作简单	不能批量比对
VBA 单 Sheet	核对同一文件两张表	自动化输出差异	需启用宏
VBA 多文件多 Sheet	批量报表、历史数据对比	批量高效、结果清晰	编写稍复杂