如何编写Excel VBA脚本对比两工作表并生成差异汇总报告

需求说明 #

对比Sheet1与Sheet2的员工数据（字段：eid、name、sal），生成的汇总报告需包含：

• 两个工作表的数据行数统计
• 标注仅存在于Sheet1的记录（如name为"de"的行）
• 标注仅存在于Sheet2的记录（如eid为3的行）
• 明确差异记录所属的工作表

完整VBA脚本 #

Sub CompareSheetsAndGenerateReport()
    Dim ws1 As Worksheet, ws2 As Worksheet, reportWs As Worksheet
    Dim lastRow1 As Long, lastRow2 As Long
    Dim eidDict As Object, ws2Eids As Object
    Dim i As Long, reportRow As Long
    Dim eidKey As String, dataParts As Variant
    
    ' 绑定目标工作表
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    
    ' 创建/激活对比报告工作表
    On Error Resume Next
    Set reportWs = ThisWorkbook.Worksheets("对比报告")
    If Err.Number <> 0 Then
        Set reportWs = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        reportWs.Name = "对比报告"
    End If
    On Error GoTo 0
    
    ' 清空报告表原有内容
    reportWs.Cells.Clear
    
    ' 写入行数统计
    reportWs.Range("A1").Value = "工作表数据对比汇总报告"
    reportWs.Range("A1").Font.Bold = True
    reportWs.Range("A3").Value = "Sheet1数据行数：" & ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row - 1
    reportWs.Range("A4").Value = "Sheet2数据行数：" & ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row - 1
    reportWs.Range("A3:A4").Font.Bold = True
    
    ' 初始化字典存储Sheet1的eid与对应数据
    Set eidDict = CreateObject("Scripting.Dictionary")
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastRow1
        eidKey = CStr(ws1.Cells(i, "A").Value)
        If Not eidDict.Exists(eidKey) Then
            eidDict(eidKey) = ws1.Cells(i, "B").Value & "|" & ws1.Cells(i, "C").Value
        End If
    Next i
    
    ' 初始化字典存储Sheet2的eid与对应数据
    Set ws2Eids = CreateObject("Scripting.Dictionary")
    lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastRow2
        eidKey = CStr(ws2.Cells(i, "A").Value)
        If Not ws2Eids.Exists(eidKey) Then
            ws2Eids(eidKey) = ws2.Cells(i, "B").Value & "|" & ws2.Cells(i, "C").Value
        End If
    Next i
    
    ' 写入仅存在于Sheet1的记录
    reportRow = 6
    reportWs.Range("A6").Value = "仅存在于Sheet1的记录"
    reportWs.Range("A6").Font.Bold = True
    reportWs.Range("A7:C7").Value = Array("eid", "name", "sal")
    reportWs.Range("A7:C7").Font.Bold = True
    
    For Each eidKey In eidDict.Keys
        If Not ws2Eids.Exists(eidKey) Then
            reportRow = reportRow + 1
            dataParts = Split(eidDict(eidKey), "|")
            reportWs.Cells(reportRow, "A").Value = eidKey
            reportWs.Cells(reportRow, "B").Value = dataParts(0)
            reportWs.Cells(reportRow, "C").Value = dataParts(1)
            reportWs.Cells(reportRow, "D").Value = "所属工作表：Sheet1"
        End If
    Next eidKey
    
    ' 写入仅存在于Sheet2的记录
    reportRow = reportRow + 2
    reportWs.Range("A" & reportRow).Value = "仅存在于Sheet2的记录"
    reportWs.Range("A" & reportRow).Font.Bold = True
    reportRow = reportRow + 1
    reportWs.Range("A" & reportRow & ":C" & reportRow).Value = Array("eid", "name", "sal")
    reportWs.Range("A" & reportRow & ":C" & reportRow).Font.Bold = True
    
    For Each eidKey In ws2Eids.Keys
        If Not eidDict.Exists(eidKey) Then
            reportRow = reportRow + 1
            dataParts = Split(ws2Eids(eidKey), "|")
            reportWs.Cells(reportRow, "A").Value = eidKey
            reportWs.Cells(reportRow, "B").Value = dataParts(0)
            reportWs.Cells(reportRow, "C").Value = dataParts(1)
            reportWs.Cells(reportRow, "D").Value = "所属工作表：Sheet2"
        End If
    Next eidKey
    
    ' 自动调整列宽
    reportWs.Columns("A:D").AutoFit
    
    MsgBox "对比报告已生成，请到「对比报告」工作表查看", vbInformation
End Sub

使用步骤 #

1. 打开包含目标Sheet1和Sheet2的Excel文件
2. 按下Alt + F11打开VBA编辑器
3. 在左侧工程窗口右键点击当前工作簿，选择「插入」→「模块」
4. 将上述代码粘贴到模块中
5. 按下F5运行脚本，或回到Excel界面后点击「开发工具」→「宏」，选择CompareSheetsAndGenerateReport执行

关键逻辑说明 #

• 用Scripting.Dictionary存储两个工作表的eid与对应数据，实现高效的存在性检查
• 自动创建「对比报告」工作表，清空原有内容后写入统计结果与差异记录
• 分别遍历两个字典，筛选出仅存在于单个工作表的记录，并标注所属工作表
• 自动调整报告列表宽，提升可读性

内容的提问来源于stack exchange，提问作者gbalu