VBA中能否直接批量应用WorksheetFunction至多目标单元格且媲美公式法效率？

你的核心需求是：批量对大量单元格应用多参数SUMIFS，最终仅保留数值，同时避免可见的中间公式步骤，且效率不低于「写公式→复制→粘贴数值」的方法。以下是两种满足要求的高效方案：

一、优化版公式法（隐藏中间步骤） #

原公式法的优势在于Excel引擎批量处理公式的效率极高，我们可以通过VBA在后台完成公式写入、计算、转数值的全流程，全程隐藏中间步骤，同时通过关闭Excel的交互特性进一步提升速度。

代码示例 #

Sub EfficientSumifsValues()
    Dim targetRange As Range
    Dim sumRange As Range
    Dim criteriaRanges As Variant
    Dim criteriaValues As Variant
    
    ' 定义你的数据范围（根据实际情况修改）
    Set targetRange = Range("B1:B3000") ' 目标输出范围
    Set sumRange = Range("D1:D35000") ' SUMIFS的求和范围
    ' 定义条件范围和对应的条件值（示例为6个参数，可按需增减）
    criteriaRanges = Array(Range("A1:A35000"), Range("B1:B35000"), Range("C1:C35000"), _
                          Range("E1:E35000"), Range("F1:F35000"), Range("G1:G35000"))
    criteriaValues = Array("=RC1", "=RC2", "=RC3", "=RC5", "=RC6", "=RC7") ' R1C1格式的条件引用
    
    ' 关闭Excel交互特性以提速
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    
    ' 构建SUMIFS的R1C1公式
    Dim formulaStr As String
    formulaStr = "=SUMIFS(" & sumRange.Address(ReferenceStyle:=xlR1C1) & ","
    For i = LBound(criteriaRanges) To UBound(criteriaRanges)
        formulaStr = formulaStr & criteriaRanges(i).Address(ReferenceStyle:=xlR1C1) & "," & criteriaValues(i) & ","
    Next i
    formulaStr = Left(formulaStr, Len(formulaStr) - 1) & ")" ' 移除最后一个多余的逗号
    
    ' 批量写入公式并转数值
    With targetRange
        .FormulaR1C1 = formulaStr
        .Calculate ' 强制计算（因为手动计算模式）
        .Value = .Value ' 直接将公式转为数值
    End With
    
    ' 恢复Excel默认设置
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub

二、批量数组计算法（无循环，直接生成结果数组） #

原Variant数组循环法效率低的核心原因是每次循环单独调用WorksheetFunction.SUMIFS，产生了大量Excel与VBA的交互开销。我们可以利用Application.Evaluate一次性计算整个目标范围的结果，直接生成结果数组后赋值给单元格，效率与公式法相当。

代码示例 #

Sub ArraySumifsValues()
    Dim targetRange As Range
    Dim sumRange As String
    Dim criteriaParts As String
    
    ' 定义范围（使用R1C1格式字符串，避免对象引用开销）
    Set targetRange = Range("B1:B3000")
    sumRange = "R1C4:R35000C4" ' 求和范围D1:D35000的R1C1格式
    ' 构建6个条件的字符串（示例条件为对应行的A、B、C、E、F、G列值）
    criteriaParts = "R1C1:R35000C1,RC1,R1C2:R35000C2,RC2,R1C3:R35000C3,RC3," & _
                    "R1C5:R35000C5,RC5,R1C6:R35000C6,RC6,R1C7:R35000C7,RC7"
    
    ' 关闭交互特性
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    ' 一次性计算所有结果并赋值
    targetRange.Value = .Evaluate("SUMIFS(" & sumRange & "," & criteriaParts & ")")
    
    ' 恢复设置
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

方案说明 #

1. 优化版公式法：完全复用Excel原生公式引擎的批量计算能力，通过关闭屏幕更新、手动计算等设置消除不必要的开销，全程用户看不到公式，最终直接保留数值。
2. 批量数组计算法：利用Evaluate将整个SUMIFS计算转为批量数组操作，避免循环调用的交互开销，代码更简洁，效率与公式法持平。

两种方案的效率都能达到甚至超过你测试的「公式→复制→粘贴数值」方法，同时满足无可见中间步骤的需求。

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