如何在Excel中的数组上使用VBA执行SumIf [英] How to perform SumIf using VBA on an array in Excel

问题描述

我试图想出最快的方法来执行一个SumIf函数在Excel中的数据集有大约。 110000行。我已经提出了三种方式，但没有一个是令人满意的。

I'm trying to come up with the fastest way to perform a SumIf function in Excel on a dataset that has approx. 110'000 lines. I've come up with three ways, but none of them are satisfying.

这里我第一个尝试：在我的电脑上执行100秒！

Here the first one I tried: Execution time on my PC 100 seconds!

    Sub Test1_WorksheetFunction()

Dim MaxRow As Long, MaxCol As Long
Dim i As Long
Dim StartTimer, EndTimer, UsedTime

StartTimer = Now()

With wsTest
    MaxRow = .UsedRange.Rows.Count
    MaxCol = .UsedRange.Columns.Count

    For i = 2 To MaxRow
        .Cells(i, 4) = WorksheetFunction.SumIf(wsData.Range("G2:G108840"), .Cells(i, 1), wsData.Range("R2:R108840"))
    Next i

End With

EndTimer = Now()
MsgBox (DateDiff("s", StartTimer, EndTimer))

End Sub

这是第二种方法：执行时间在55秒以上一点点

Here is the second Method: Execution Time a bit better at 55 seconds

Sub Test2_Formula_and_Copy()

Dim MaxRow As Long, MaxCol As Long
Dim i As Long
Dim StartTimer, EndTimer, UsedTime

StartTimer = Now()

With wsTest
    MaxRow = .UsedRange.Rows.Count
    MaxCol = .UsedRange.Columns.Count

    Range("D2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(Tabelle1[KUNDENBESTELLNR],Test!RC[-3],Tabelle1[ANZAHL NACHFRAGE])"
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D6285")
    Range("D2:D6285").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End With

EndTimer = Now()
MsgBox (DateDiff("s", StartTimer, EndTimer))

End Sub

第三次尝试：执行速度太慢，从未完成。

Third attempt: Execution so slow it never finished.

Sub Test3_Read_in_Array()

Dim MaxRow As Long, MaxCol As Long
Dim SearchRange() As String, SumRange() As Long
Dim i As Long, j As Long, k
Dim StartTimer, EndTimer, UsedTime
Dim TempValue

StartTimer = Now()

With wsData
    MaxRow = .UsedRange.Rows.Count
    ReDim SearchRange(1 To MaxRow)
    ReDim SumRange(1 To MaxRow)
    For i = 1 To MaxRow
        SearchRange(i) = .Range("G" & (1 + i)).Value
        SumRange(i) = .Range("R" & (1 + i)).Value
    Next i
End With

With wsTest
    MaxRow = .UsedRange.Rows.Count
    For i = 2 To MaxRow
        For j = LBound(SearchRange) To UBound(SearchRange)
            k = .Cells(i, 1).Value
            If k = SearchRange(j) Then
            TempValue = TempValue + SumRange(j)
            End If
        Next j
        .Cells(i, 4) = TempValue
    Next i
End With


EndTimer = Now()
MsgBox (DateDiff("s", StartTimer, EndTimer))

End Sub

显然，我还没有掌握VBA任何其他编程语言）。有人可以帮助我，使其效率高吗？一定有办法！对不对？

Clearly I have not yet mastered VBA (or any other programming language for that matter). Can someone help me in getting this to be efficient? There must be a way! Right?

谢谢！

推荐答案

一个更快的方法来计算Sumifs一段时间，当我想出了以下解决方案。而不是使用Sumifs，您将标准范围中使用的值连接为单个值，然后使用简单的If公式 - 结合一个范围Sort - 可以获得与使用Sumifs相同的结果。

I’d been searching for a faster method for calculating Sumifs for some time when I came up with the following solution. Instead of using Sumifs, you concatenate the values used in the criteria ranges as a single value, then using simple If formulas – combined with one range Sort – you achieve the same results as if you’d used Sumifs.

在我自己的情况下，使用25K行和2个标准范围的Sumifs进行评估平均需要18.4秒 - 使用If和Sort方法，平均需要0.67秒。

In my own case, using Sumifs with 25K rows and 2 criteria ranges to evaluate was taking 18.4 seconds on average – using the If and Sort method, it was taking 0.67 seconds on average.

 Sub FasterThanSumifs()
    'FasterThanSumifs Concatenates the criteria values from columns A and B -
    'then uses simple IF formulas (plus 1 sort) to get the same result as a sumifs formula

    'Columns A & B contain the criteria ranges, column C is the range to sum
    'NOTE: The data is already sorted on columns A AND B

    'Concatenate the 2 values as 1 - can be used to concatenate any number of values
    With Range("D2:D25001")
        .FormulaR1C1 = "=RC[-3]&RC[-2]"
        .Value = .Value
    End With

    'If formula sums the range-to-sum where the values are the same
    With Range("E2:E25001")
        .FormulaR1C1 = "=IF(RC[-1]=R[-1]C[-1],RC[-2]+R[-1]C,RC[-2])"
        .Value = .Value
    End With

    'Sort the range of returned values to place the largest values above the lower ones
    Range("A1:E25001").Sort Key1:=Range("D1"), Order1:=xlAscending, _
    Key2:=Range("E1"), Order2:=xlDescending, Header:=xlYes
    Sheet1.Sort.SortFields.Clear

    'If formula returns the maximum value for each concatenated value match &
    'is therefore the equivalent of using a Sumifs formula
    With Range("F2:F25001")
        .FormulaR1C1 = "=IF(RC[-2]=R[-1]C[-2],R[-1]C,RC[-1])"
        .Value = .Value
    End With

    End Sub

这篇关于如何在Excel中的数组上使用VBA执行SumIf的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！