使用 Sumifs() 的更快方法 [英] Faster way of using Sumifs()

问题描述

我有一个每周任务，我需要更新一份报告(目前只有 5 万多行)，该报告每周增加大约 500 行.手动添加新数据后，我运行下面的代码做一个Sumifs()来汇总数据.

I have a weekly task where I need to update a report (currently just over 50K rows) which is growing by around 500 rows every week. After the new data is added manually, I run the code below to do a Sumifs() to summarize the data.

数据结构是:A列到C列是条件列(数字-字母-数字)，D列是要求和的数量(整数).数据是连续的.我的宏将 Sumifs() 公式放入 E 列 - 覆盖那里的内容.

The data structure is: columns A to C are the criteria columns (numeric-alpha-numeric), column D has the quantity to sum (whole numbers). The data is contiguous. My macro puts the Sumifs() formula into column E – overwriting what’s there.

我的问题是:可以更快地完成这项任务吗?目前我运行宏只需要一分钟多一点，但随着数据的增长，这个时间会变长.

My question is: can this task be done quicker? It currently takes me just over a minute to run the macro, but this gets longer as the data grows.

这个网站上有很多关于使用数组更快地完成任务的内容，但没有一个例子对我来说意义重大，如果可能的话，我宁愿不使用它们.

There’s a lot on this site about using Arrays to do tasks more quickly, but none of the examples make much sense to me and I would prefer not to use them if possible.

Sub MySumIfs()
Dim LastRow As Long

LastRow = Sheet1.Range("A1").End(xlDown).Row

With Sheet1.Range("E2:E" & LastRow)
    .FormulaR1C1 = "=sumifs(R2C4:R" & LastRow & "C4, R2C1:R" & LastRow & "C1, RC1, R2C2:R" & LastRow & "C2, RC2, R2C3:R" & LastRow & "C3, RC3)"
    .Value = .Value
End With

End Sub

推荐答案

这是另一种方式:

EDIT - 更新添加averageifs"；和sumifs"我最初的(错误的)countifs"版本...

EDIT - updated to add "averageifs" and "sumifs" to my initial (mistaken) "countifs" version...

Sub SetupDummyData()
    Const NUM As Long = 100001
    Range("A1:E1").Value = Array("A_Header", "B_Header", "C_Header", "Value", "ResultHere")
    Range("A2:A" & NUM).Formula = "=""A#"" & round(RAND()*10,0)"
    Range("B2:B" & NUM).Formula = "=""B#"" & round(RAND()*10,0)"
    Range("C2:C" & NUM).Formula = "=""C#"" & round(RAND()*10,0)"
    Range("D2:D" & NUM).Formula = "=round(RAND()*100,1)"
    
    Range("A2:D" & NUM).Value = Range("A2:D" & NUM).Value
End Sub


Sub Tester()
    
    Dim arr, ws, rng As Range, keyCols, valueCol As Long, destCol As Long, i As Long, frm As String, sep As String
    Dim t, dict, arrOut(), arrValues(), v, tmp, n As Long
    
    keyCols = Array(1, 2, 3)  'these columns form the composite key
    valueCol = 4              'column with values (for sum)
    destCol = 5               'destination for calculated values
    
    t = Timer
    
    Set ws = ActiveSheet
    Set rng = ws.Range("A1").CurrentRegion
    n = rng.Rows.Count - 1
    Set rng = rng.Offset(1, 0).Resize(n) 'exclude headers
    
    'build the formula to create the row "key"
    For i = 0 To UBound(keyCols)
        frm = frm & sep & rng.Columns(keyCols(i)).Address
        sep = "&""|""&"
    Next i
    arr = ws.Evaluate(frm)  'get an array of composite keys by evaluating the formula
    arrValues = rng.Columns(valueCol).Value  'values to be summed
    ReDim arrOut(1 To n, 1 To 1)             'this is for the results
    
    Set dict = CreateObject("scripting.dictionary")
    'first loop over the array counts the keys
    For i = 1 To n
        v = arr(i, 1)
        If Not dict.exists(v) Then dict(v) = Array(0, 0) 'count, sum
        tmp = dict(v) 'can't modify an array stored in a dictionary - pull it out first
        tmp(0) = tmp(0) + 1                 'increment count
        tmp(1) = tmp(1) + arrValues(i, 1)   'increment sum
        dict(v) = tmp                       'return the modified array
    Next i
    
    'second loop populates the output array from the dictionary
    For i = 1 To n
        arrOut(i, 1) = dict(arr(i, 1))(1)                       'sumifs
        'arrOut(i, 1) = dict(arr(i, 1))(0)                      'countifs
        'arrOut(i, 1) = dict(arr(i, 1))(1) / dict(arr(i, 1))(0) 'averageifs
    Next i
    'populate the results
    rng.Columns(destCol).Value = arrOut
    
    Debug.Print "Checked " & n & " rows in " & Timer - t & " secs"

End Sub

这篇关于使用 Sumifs() 的更快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！