VBA 中的 SUMPRODUCT 公式

SUMPRODUCT Formula in VBA

提问by Adnan

I want to use following SUMPRODUCTformula in VBA:

我想SUMPRODUCT在 VBA 中使用以下公式：

=SUMPRODUCT((Sale!$J:$J48576=C12)*Sale!$D:$D48576,Sale!$M:$M48576)

I used this code but it gives an error

我使用了这段代码，但它给出了一个错误

Run-time error '13': Type mismatch

运行时错误“13”：类型不匹配

Dim slWks As Worksheet

Set slWks = Sheets("Sale")
ActiveSheet.Range("F12").Value = _
Application.WorksheetFunction.SumProduct((slWks.Range("J5:J1048576") = _
    ActiveSheet.Range("C12")) * slWks.Range("D5:D1048576"), slWks.Range("M5:M1048576"))

How can I write that formula with its values using vba?

如何使用 vba 编写带有其值的公式？

回答by Rory

Two possible simple solutions, given that worksheetfunction methods won't work with arrays the size that you are using:

两种可能的简单解决方案，因为 worksheetfunction 方法不适用于您使用的大小的数组：

First, add the formula and then replace it with its value

首先，添加公式，然后将其替换为其值

With activesheet.Range("F12")
   .Formula =" =SUMPRODUCT((Sale!$J:$J48576=C12)*Sale!$D:$D48576,Sale!$M:$M48576)"
   .Value2 = .Value2
End With

Second, use Evaluate:

其次，使用Evaluate：

With Activesheet
   .range("F12").Value2 = .Evaluate("SUMPRODUCT((Sale!$J:$J48576=C12)*Sale!$D:$D48576,Sale!$M:$M48576)")
End With

回答by Robin Mackenzie

Taking a guess at your use case:

猜测您的用例：

• C12is some product you are interested in
• Sale!$J$5:$J$1048576is a range of products
• (Sale!$J$5:$J$1048576=C12)gives an array like {1,1,1,0,0,0...}
• Sale!$D$5:$D$1048576is a range of unit prices
• Sale!$M$5:$M$1048576is a range of number of units sold
• Your SUMPRODUCTgives a the revenue of for the product in C12

• C12是一些你感兴趣的产品
• Sale!$J$5:$J$1048576是一系列产品
• (Sale!$J$5:$J$1048576=C12)给出一个数组 {1,1,1,0,0,0...}
• Sale!$D$5:$D$1048576是一系列单价
• Sale!$M$5:$M$1048576是销售单位数量的范围
• 你SUMPRODUCT给出了产品的收入C12

So for this sample data:

所以对于这个样本数据：

You could use this code to do leverage SUMPRODUCT:

您可以使用此代码进行杠杆作用SUMPRODUCT：

Option Explicit

Sub SumProductWithVBA()

    Dim ws As Worksheet
    Dim strProduct As String
    Dim dblRevenue As Double
    Dim wsf As WorksheetFunction
    Dim v1 As Variant, v2 As Variant, v3 As Variant, v4 As Variant
    Dim i As Long

    Set wsf = Application.WorksheetFunction
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ' condition for SUMPRODUCT
    strProduct = ws.Range("A1").Value

    ' get the values of the ranges
    v1 = wsf.Transpose(ws.Range("A4:A15").Value)
    v2 = wsf.Transpose(ws.Range("B4:B15").Value)
    v3 = wsf.Transpose(ws.Range("C4:C15").Value)

    ' make the array like {1,1,1,0,0,0...etc}
    ' this is the equivalent of the SUMPRODUCT((range=value)... bit
    ReDim v4(1 To UBound(v1))
    For i = 1 To UBound(v1)
        If v1(i) = strProduct Then
            v4(i) = 1
        Else
            v4(i) = 0
        End If
    Next i

    ' now do the SUMPRODUCT with all the arrays set-up    
    dblRevenue = wsf.SumProduct(v4, v2, v3)

    ' test the output    
    MsgBox dblRevenue

End Sub

回答by Mohammad Aslam

I had the same problem with sumproduct function and after many experiments I solved my problem with this code:

我对 sumproduct 函数有同样的问题，经过多次实验，我用这段代码解决了我的问题：

Sub Test2()
  Dim WS As Worksheet
  Dim a, b, c, Criteria As Range
  Dim data1, data2, data3, crite As String
  Dim LasTRow As Long

  Set WS = ThisWorkbook.Sheets("Sale")
  LasTRow = WS.Cells(Rows.Count, 1).End(xlUp).Row

  Set a = WS.Range("A5:A" & LasTRow)
  Set b = WS.Range("b5:b" & LasTRow)
  Set c = WS.Range("c5:c" & LasTRow)
  Set Criteria = WS.Range("A1")

  data1 = a.Address
  data2 = b.Address
  data3 = c.Address
  crite = Criteria.Address
  WS.Range("b1").Formula = "=sumproduct((" & data1 & " = " & crite & ") *( " & data2 & " ) * ( " & data3 & " ))"

End Sub