VBA条件求和代码无报错但Excel未响应问题求助

性能瓶颈原因 #

• 重复调用SumIf导致计算量爆炸：你的代码对每行L列单元格都执行一次WorksheetFunction.SumIf，94000行就会触发94000次SumIf计算。每次SumIf都要遍历整个A列（94000行）匹配I列值，时间复杂度为O(n²)，数据量越大，耗时呈指数级增长。
• 逐单元格读写开销大：循环中直接操作ws.Cells(i,12).Value，VBA与Excel界面的交互IO成本极高，逐行操作会累积大量不必要的耗时。
• 变量类型不合理：i和lastrow使用Double类型，而行号本质是整数，用Long类型更高效且能避免类型转换的额外消耗。

优化后的代码 #

改用字典先一次性统计A列对应G列的求和结果，再批量写入L列，同时关闭Excel后台冗余操作：

Sub OptimizedSumIf()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim i As Long
    Dim lastrow As Long
    Dim lastrowG As Long
    Dim dataArr As Variant
    Dim sumDict As Object
    Dim key As Variant
    
    Set wb = ThisWorkbook
    Set sumDict = CreateObject("Scripting.Dictionary")
    
    ' 关闭Excel界面相关冗余操作
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    For Each ws In wb.Worksheets
        lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
        lastrowG = ws.Cells(Rows.Count, "G").End(xlUp).Row
        ' 取最大行号避免数组越界
        lastrow = IIf(lastrow > lastrowG, lastrow, lastrowG)
        
        ' 将数据读入内存数组，减少单元格交互
        dataArr = ws.Range("A1:L" & lastrow).Value
        
        ' 第一步：用字典统计A列对应G列的总和
        For i = 2 To lastrow
            key = dataArr(i, 1) ' A列的值
            If Not sumDict.Exists(key) Then
                sumDict(key) = dataArr(i, 7) ' G列的值
            Else
                sumDict(key) = sumDict(key) + dataArr(i, 7)
            End If
        Next i
        
        ' 第二步：匹配I列的值，写入L列
        For i = 2 To lastrow
            key = dataArr(i, 9) ' I列的值
            If sumDict.Exists(key) Then
                dataArr(i, 12) = sumDict(key)
            Else
                dataArr(i, 12) = 0 ' 无匹配项时填0
            End If
        Next i
        
        ' 将数组批量写回工作表
        ws.Range("A1:L" & lastrow).Value = dataArr
        ' 清空字典，准备处理下一个工作表
        sumDict.RemoveAll
    Next ws
    
    ' 恢复Excel默认设置
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub

优化点说明 #

• 字典统计求和：仅遍历A列和G列一次，将每个A列值对应的G列总和存入字典，时间复杂度降为O(n)，计算量大幅降低。
• 数组批量处理：把数据区域一次性读入内存数组，所有操作在内存完成，最后批量写回工作表，彻底消除VBA与Excel界面的频繁交互开销。
• 关闭后台冗余操作：暂停屏幕更新、事件触发和自动计算，避免Excel在代码运行时执行额外的界面刷新和计算。
• 修正变量类型：将i和lastrow改为Long类型，符合行号的整数属性，提升运行效率。

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