VBA不调用工作表函数如何实现COUNTIF/SUMIF的计数求和功能

完全可以实现对应效果，全程不需要向单元格写入工作表公式，也不需要调用任何Excel工作表函数，仅用VBA原生语法就能完成同等逻辑的条件计数、条件求和，数据量大时运行效率远高于公式方案。

原代码逻辑梳理 #

先明确原有公式的计算规则，保证实现效果完全对齐：

• 匹配关键字：取Solution1工作表B6单元格的值
• 条件计数：统计Transaction工作表B列（R1C1引用规则下C2为第2列，即B列）等于关键字的条目数，结果写入C6
• 条件求和：分别对Transaction工作表F、G、H列（R1C1引用规则下C6/C7/C8为第6/7/8列，即F/G/H列）中匹配关键字对应行的数值求和，结果依次写入D6、E6、F6

实现思路 #

• 先把Transaction表涉及的待匹配列、求和列数据一次性读入内存数组，避免逐单元格读写的性能损耗
• 逐行遍历数组，判断匹配列值是否等于关键字，手动完成计数、数值累加
• 计算完成后直接把结果以纯值形式写入目标单元格，全程不生成公式、不调用WorksheetFunction类下的任何工作表函数

实现代码 #

Sub CalcWithoutFormula()
    Dim transData As Variant, matchKey As Variant
    Dim cntResult As Long, sumD As Double, sumE As Double, sumF As Double
    Dim lastRow As Long, i As Long
    
    Application.ScreenUpdating = False
    
    ' 读取匹配关键字
    matchKey = ThisWorkbook.Sheets("Solution1").Range("B6").Value
    
    ' 读取Transaction表目标范围数据到内存
    With ThisWorkbook.Sheets("Transaction")
        lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
        transData = .Range("B1:H" & lastRow).Value
    End With
    
    ' 初始化结果变量
    cntResult = 0
    sumD = 0
    sumE = 0
    sumF = 0
    
    ' 逐行遍历匹配计算
    For i = 1 To UBound(transData, 1)
        If transData(i, 1) = matchKey Then
            cntResult = cntResult + 1
            ' 用VBA原生Val函数处理值，对齐SUMIF忽略文本、空值的逻辑
            sumD = sumD + VBA.Val(transData(i, 5))
            sumE = sumE + VBA.Val(transData(i, 6))
            sumF = sumF + VBA.Val(transData(i, 7))
        End If
    Next i
    
    ' 写入结果
    With ThisWorkbook.Sheets("Solution1")
        .Range("C6").Value = cntResult
        .Range("D6").Value = sumD
        .Range("E6").Value = sumE
        .Range("F6").Value = sumF
    End With
    
    Application.ScreenUpdating = True
End Sub

扩展说明 #

如果需要批量计算多行关键字的统计结果，可以搭配VBA原生的Dictionary对象做分类汇总，仅需遍历一次Transaction表就能完成所有关键字的计数、求和计算，性能比逐行写公式、逐单元格循环的方案高数十倍。

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