Excel VBA中如何将数组传入SUMPRODUCT无需输出到工作表

这里提供两种无需输出临时P列的实现方式：

方案1：全内存VBA计算（无任何工作表临时写入、无公式） #

直接在VBA里读取所有需要的列数据，完成匹配计算后直接写入K列，完全绕开临时区域和工作表公式：

Sub SumWithoutTempColumn()
    Dim ws As Worksheet
    Dim arrA, arrB, arrD, arrF, arrI, arrK
    Dim i As Long, j As Long, total As Double
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ' 一次性读取所有需要的列数据到内存数组
    arrA = ws.Range("A2:A250").Value
    arrB = ws.Range("B2:B250").Value
    arrD = ws.Range("D2:D250").Value
    arrF = ws.Range("F2:F250").Value
    arrI = ws.Range("I2:I250").Value
    ReDim arrK(1 To 249, 1 To 1) ' 对应K2:K250共249行
    
    ' 遍历每一行I列的匹配值
    For i = 1 To UBound(arrI)
        total = 0
        ' 遍历所有行计算匹配和
        For j = 1 To UBound(arrA)
            If arrA(j, 1) & " " & arrB(j, 1) = arrI(i, 1) Then
                total = total + arrD(j, 1) * arrF(j, 1)
            End If
        Next j
        arrK(i, 1) = IIf(total = 0, "", total)
    Next i
    
    ' 一次性写入结果到K列
    ws.Range("K2:K250").Value = arrK
End Sub

这种方式效率最高，全程没有多余的工作表读写操作，也不会产生任何临时数据。

方案2：修改公式逻辑，直接拼接A/B列替代P列引用 #

如果你希望保留公式写法的灵活性，可以直接把P列的拼接逻辑内嵌到公式里，完全不需要用到P列：

Sub SumDataWithoutTemp()
    Const dFormula As String _
        = "=IFERROR(SUMPRODUCT(--(A$2:A$250&"" ""&B$2:B$250=I2),D$2:D$250,F$2:F$250),"""")"
    With ThisWorkbook.Worksheets("Sheet1").Range("K2:K250")
        .Formula = dFormula
        .Value = .Value
    End With
End Sub

公式直接将A列和B列拼接后参与匹配，不需要提前把拼接结果写入P列。

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