VBA 替代 Excel SUMPRODUCT 多条件查找 [英] VBA alternative to Excel SUMPRODUCT multiple criteria lookup

问题描述

我有一个包含数万行的大型电子表格.我想根据多个条件查找值并获取相关值.目前，我使用 SUMPRODUCT 函数，但是有这么多行，计算需要很多分钟.

功能:

=SUMPRODUCT((数组 1 条件) * (数组 2 条件) * 数组值)

示例:

=SUMPRODUCT((B15:B23="John")*(C15:C23="North")*(E15:E23=1)*D15:D23)

来自此处的示例.>

问题:是否有更有效的方法来使用多个条件进行此类查找 - 也许使用 VBA?我试过使用索引匹配，但它只给我第一个匹配的值，我不确定它在性能方面是否更好.

解决方案

请试试这个代码.它非常快，使用数组并在内存中工作.请确认它对您的实际情况足够快.它在A2:D"范围内工作最后一行，并返回E:E"列中的结果.

Sub testSumprodBis()Dim sh As Worksheet, arrI As Variant, arrF As Variant, lastR As LongDim i As Long, j As Long, pCount As Long, d As Object设置 sh = ActiveSheetlastR = sh.Range("A" & Cells.Rows.count).End(xlUp).rowarrI = sh.Range("A2:D" & lastR).ValueReDim arrF(1 To UBound(arrI, 1), 1 To 1)Set d = CreateObject("Scripting.Dictionary")对于 i = 1 到 lastR - 1如果不是 d.Exists(UCase(arrI(i, 1) & arrI(i, 2) & arrI(i, 4))) 然后对于 j = 1 到最后 R - 1如果 UCase(arrI(i, 1)) = UCase(arrI(j, 1)) 并且 arrI(i, 4) = arrI(j, 4) 然后pCount = pCount + arrI(j, 3)万一下一个d(UCase(arrI(i, 1) & arrI(i, 2) & arrI(i, 4))) = pCountarrF(i, 1) = pCount: pCount = 0别的arrF(i, 1) = d(UCase(arrI(i, 1) & arrI(i, 2) & arrI(i, 4)))万一下一个sh.Range("E2").Resize(UBound(arrF, 1), 1).Value = arrF结束子

现在，代码使用了一个字典，其中已经计算的案例被保留并且只用于相似的案例，而不是重新计算......

已对于我的情况，它几乎立即起作用，但发生这种情况是因为仅重复了从上面的示例中获取的值.请告诉我您的真实文件需要多少费用.

I have a large spreadsheet with tens of thousands of rows. I want to look up values based on multiple criteria and get the associated values. Currently, I use the SUMPRODUCT function, but with that many rows, it takes many minutes to calculate.

Function:

=SUMPRODUCT((array 1 criteria) * (array2 criteria) * array values) 

Example:

=SUMPRODUCT((B15:B23="John")*(C15:C23="North")*(E15:E23=1)*D15:D23)

Example from here.

Question: Is there a more efficient way to do this type of lookup with multiple criteria - maybe with VBA? I have tried using index match, but it only gives me the value of the first match and I am not sure it is better performance-wise.

解决方案

Please try this code. It is very fast, using array and working in memory. Please confirm that it works fast enough for your real case. It works in range "A2:D" & last row, and returns the result in column "E:E".

Sub testSumprodBis()
 Dim sh As Worksheet, arrI As Variant, arrF As Variant, lastR As Long
 Dim i As Long, j As Long, pCount As Long, d As Object

  Set sh = ActiveSheet
  lastR = sh.Range("A" & Cells.Rows.count).End(xlUp).row
  arrI = sh.Range("A2:D" & lastR).Value
  ReDim arrF(1 To UBound(arrI, 1), 1 To 1)
  Set d = CreateObject("Scripting.Dictionary")

  For i = 1 To lastR - 1
    If Not d.Exists(UCase(arrI(i, 1) & arrI(i, 2) & arrI(i, 4))) Then
        For j = 1 To lastR - 1
            If UCase(arrI(i, 1)) = UCase(arrI(j, 1)) And arrI(i, 4) = arrI(j, 4) Then
                pCount = pCount + arrI(j, 3)
            End If
        Next j
        d(UCase(arrI(i, 1) & arrI(i, 2) & arrI(i, 4))) = pCount
        arrF(i, 1) = pCount: pCount = 0
    Else
        arrF(i, 1) = d(UCase(arrI(i, 1) & arrI(i, 2) & arrI(i, 4)))
    End If
  Next
  sh.Range("E2").Resize(UBound(arrF, 1), 1).Value = arrF
End Sub

Now, the code uses a dictionary where the already calculated cases are kept and only used for similar ones, instead of recalculating...

Edited: It works almost instant for my case, but this is happening because a only repeated the values taken form your above example. Please let me know how much it takes for your real file.

这篇关于VBA 替代 Excel SUMPRODUCT 多条件查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！