Excel VBA两工作表匹配求和及单元格高亮技术问询

需求说明 #

• 提取Sheet1的A列值，在Sheet2的A列中查找匹配项
• 将Sheet2中所有匹配项的B列数值求和，写入Sheet1对应行的B列
• Sheet1中无匹配值的A列单元格高亮红色
• Sheet2中被匹配到的A列单元格高亮绿色

示例表格 #

Sheet1 示例 #

Sheet2 示例 #

原测试代码问题分析 #

你编写的测试代码存在以下问题：

• 错误地将ws2指向Worksheets(1)，应改为Worksheets(2)
• 嵌套循环中使用exit for导致内层循环仅执行一次，外层循环无法正常遍历所有单元格
• 变量rng未声明就直接使用，不符合VBA规范
• 直接引用整列Range("A:A")会遍历大量空单元格，降低运行效率

优化后的完整代码 #

Sub MatchAndSum()
    ' 关闭屏幕更新，提升运行速度
    Application.ScreenUpdating = False
    
    ' 定义工作表对象
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    
    ' 获取Sheet1和Sheet2的有效数据行数（避免遍历整列）
    Dim lastRow1 As Long, lastRow2 As Long
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
    
    ' 定义变量存储查找值和求和结果
    Dim searchVal As Variant, sumResult As Double
    Dim i As Long, j As Long
    
    ' 先清除Sheet1和Sheet2的原有高亮格式
    ws1.Range("A1:A" & lastRow1).Interior.ColorIndex = xlNone
    ws2.Range("A1:A" & lastRow2).Interior.ColorIndex = xlNone
    
    ' 遍历Sheet1的每一行
    For i = 1 To lastRow1
        searchVal = ws1.Cells(i, "A").Value
        ' 跳过空单元格
        If searchVal <> "" Then
            ' 使用SumIf函数快速求和，替代嵌套循环
            sumResult = Application.WorksheetFunction.SumIf(ws2.Range("A1:A" & lastRow2), searchVal, ws2.Range("B1:B" & lastRow2))
            
            ' 将求和结果写入Sheet1的B列
            ws1.Cells(i, "B").Value = sumResult
            
            ' 判断是否有匹配项，无匹配则高亮红色
            If sumResult = 0 Then
                ws1.Cells(i, "A").Interior.Color = RGB(255, 0, 0) ' 红色
            End If
        End If
    Next i
    
    ' 遍历Sheet2，标记匹配到的单元格为绿色
    For j = 1 To lastRow2
        searchVal = ws2.Cells(j, "A").Value
        If searchVal <> "" Then
            ' 检查当前值在Sheet1中是否存在匹配
            If Not IsError(Application.Match(searchVal, ws1.Range("A1:A" & lastRow1), 0)) Then
                ws2.Cells(j, "A").Interior.Color = RGB(0, 255, 0) ' 绿色
            End If
        End If
    Next j
    
    ' 恢复屏幕更新
    Application.ScreenUpdating = True
End Sub

代码关键说明 #

1. 屏幕更新控制：Application.ScreenUpdating = False避免代码运行时屏幕频繁刷新，大幅提升效率
2. 有效范围获取：通过End(xlUp)获取最后一行数据，避免遍历整列的空单元格
3. SumIf函数：利用Excel内置函数快速完成求和，比嵌套循环更高效简洁
4. 格式清除：先清除原有高亮，避免多次运行后格式混乱
5. 匹配判断：使用Application.Match检查Sheet2的值是否在Sheet1中存在，存在则高亮绿色

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