如何优化VBA嵌套循环，提升SumIfs类数据汇总效率？

核心优化方向（按效果优先级排序） #

• 禁用工作表交互开销
  计算前关闭屏幕更新、事件触发和自动计算，完成后恢复，避免UI层面的性能损耗：

  ' 计算前关闭
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual
  
  ' 你的核心计算逻辑放在这里
  
  ' 计算后恢复
  Application.Calculation = xlCalculationAutomatic
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  

• 用内存数组替代单元格直接读写
  单元格IO是VBA性能瓶颈的核心，将原始数据和目标区域一次性读入数组，在内存中完成计算后再批量写回：

  ' 读取原始数据到数组
  Dim rawData As Variant
  rawData = Sheets("CBK200 Raw Data").UsedRange.Value
  
  ' 遍历19个目标工作表
  Dim targetWs As Worksheet
  Dim targetArr As Variant
  For Each targetWs In ThisWorkbook.Worksheets
      If targetWs.Name <> "CBK200 Raw Data" Then ' 过滤原始数据表
          ' 读取目标表区域到数组（按需调整范围）
          targetArr = targetWs.Range("A1:BD254").Value
          
          ' 内存中完成汇总计算（结合后续字典预汇总逻辑）
          ' ...
          
          ' 批量写回目标表
          targetWs.Range("A1:BD254").Value = targetArr
      End If
  Next targetWs
  

• 字典预汇总替代多次循环遍历原始数据
  仅遍历原始数据1次，用字典按汇总条件分组存储求和结果，之后直接从字典取值填充目标表，彻底避免27万次重复遍历原始数据：

  Dim sumDict As Object
  Set sumDict = CreateObject("Scripting.Dictionary")
  Dim rawRow As Long
  Dim conditionKey As String
  
  ' 预汇总原始数据：按你的SumIfs条件拼接唯一键，存储对应求和值
  For rawRow = 2 To UBound(rawData) ' 假设第1行是表头
      ' 示例：按原始数据第2列（行条件）和第3列（列条件）拼接键，求和第4列数据
      conditionKey = rawData(rawRow, 2) & "|" & rawData(rawRow, 3)
      If sumDict.Exists(conditionKey) Then
          sumDict(conditionKey) = sumDict(conditionKey) + rawData(rawRow, 4)
      Else
          sumDict(conditionKey) = rawData(rawRow, 4)
      End If
  Next rawRow
  
  ' 填充目标表数组
  Dim targetRow As Long, targetCol As Long
  Dim rowCond As String, colCond As String
  For Each targetWs In ThisWorkbook.Worksheets
      If targetWs.Name <> "CBK200 Raw Data" Then
          targetArr = targetWs.Range("A1:BD254").Value
          For targetRow = 1 To 254
              rowCond = targetArr(targetRow, 1) ' 假设目标表A列是行条件
              For targetCol = 1 To 56
                  colCond = targetArr(1, targetCol) ' 假设目标表第1行是列条件
                  conditionKey = rowCond & "|" & colCond
                  ' 从字典取预汇总值，无匹配则设为0
                  targetArr(targetRow, targetCol) = IIf(sumDict.Exists(conditionKey), sumDict(conditionKey), 0)
              Next targetCol
          Next targetRow
          targetWs.Range("A1:BD254").Value = targetArr
      End If
  Next targetWs
  

• 预存目标表条件到数组
  如果目标表的行/列条件固定，可提前将所有19个工作表的条件提取到单独数组，避免在循环中重复读取单元格，进一步减少IO开销。

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