arrays - VBA - 带条件的求和数组列 - 像 excel sumif

Question

我想根据几个条件对数组中的列求和。如果数据在 Excel 中，我会使用 =SUMIFS 公式。

我拥有的二维数组中的示例数据集是:

ID1     ID2     ID3     Value
0       1       1       4
0       2       2       5
1       3       2       6
0       1       1       3
0       1       0       2

我想根据以下条件对值列求和:

ID1=0
ID2=1
ID3=1

因此第 1 行和第 4 行符合此条件，因此答案为 7 (4+3)

我如何在 VBA 中构造它。

请注意，ID 可能是无限的，也可能是字符串，所以我不能在循环中设置 ID=0。

Answer 1

只是一个关于速度的小警告!

我认为问题是针对2D 数组 而不是针对excel.range 因为excel 范围上的循环非常缓慢(仅当您有很多数据，但我敢打赌，如果您打算使用 VBA 宏，那是通常的情况 ;-) )

在我发现几个报告此问题的链接之前，我一直遭受范围缓慢的困扰(例如，有 10000 个单元格，一个用户报告 9,7seg 与 0,16 seg 使用 2D 阵列!!)。链接如下。我的建议是始终使用二维数组，简单、干净、快速!

查看更多性能测试:

• http://dailydoseofexcel.com/archives/2006/12/04/writing-to-a-range-using-vba/
• http://blogs.office.com/b/microsoft-excel/archive/2008/10/03/what-is-the-fastest-way-to-scan-a-large-range-in-excel.aspx

因此，如果你想处理大量数据，Jakub 回复的代码应该稍微改变一下，以获得二维数组的能力 :

Public Function sumIfMultipleConditionsMet2(rng As Range, ParamArray conditions() As Variant) As Double
    Dim conditionCount As Long: conditionCount = UBound(conditions) + 1
    Dim summedColumnIndex As Long: summedColumnIndex = conditionCount + 1
    Dim currentRow As Range
    Dim result As Double: result = 0 'Changed from Long to Double
    Dim i As Long

    If rng.Columns.Count <> conditionCount + 1 Then
        Err.Raise 17, , "Invalid range passed"
    End If        

    Dim conditionsMet As Boolean

    'USING AN ARRAY INSTEAD OF A RANGE
    Dim arr As Variant
    arr = rng.Value 'Copy the range to an array
    Dim r As Long

    For r = LBound(arr, 1) To UBound(arr, 1)  'OLD: For Each currentRow In rng.Rows
        conditionsMet = True
        For i = LBound(conditions) To UBound(conditions)
            ' cells collection is indexed from 1, the array from 0
            ' OLD: conditionsMet = conditionsMet And (currentRow.Cells(1, i + 1).Value = conditions(i))
            conditionsMet = conditionsMet And (arr(r, i + 1) = conditions(i))
        Next i

        If conditionsMet Then
            'OLD: result = result + currentRow.Cells(1, summedColumnIndex).Value
            result = result + arr(r, summedColumnIndex)
        End If
    Next r

    sumIfMultipleConditionsMet2 = result
End Function

按照 Jakub 在他的回复中显示的方式使用它:

debug.Print sumIfMultipleConditionsMet2(Range("A1:D50000"), 0, 1, 1)

希望你喜欢!

问候，安德烈斯

---

PS:如果你想更进一步，这里有excel的更多提速技巧。希望你喜欢!

• http://www.avdf.com/apr98/art_ot003.html//参见技巧#1 - 为 Excel 编写快速 VBA 代码的好信息。
• http://blogs.msdn.com/excel/archive/2009/03/12/excel-vba-performance-coding-best-practices.aspx - 快速编码
• http://support.microsoft.com/kb/153090/en-us - 将 Excel 数组传递给 VB
• http://support.microsoft.com/kb/177991 - 将数组传递给工作表时的一些限制(excel 2000 有点旧!)