需要帮助在 VBA 中优化 SUMIFS
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19162315/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Need help optimizing SUMIFS within VBA
提问by Clouse24
I've been using this site for months now to help get me started in my early coding career and this is my first post here. I apologize if I missed this question while searching the forums yesterday. I had a spreadsheet with roughly 6,000 active Sumifs referencing a table of 500,000+ rows. As you can imagine, this took a moment to calculate. I've written them into VBA so they only calculate when the user chooses to do so. However, with so many sumifs, the code takes about 3-5 minutes to run through. I'm looking for ways to speed this up for a better end-user experience. I'll post how I'm performing the sumifs below. Just for some context, this is performing the sumifs on the same group of users twice, but with one different criteria. Let me know if I left out any pertinent information.
我几个月来一直在使用这个网站来帮助我开始我的早期编码生涯，这是我在这里的第一篇文章。如果我昨天在搜索论坛时错过了这个问题，我深表歉意。我有一个包含大约 6,000 个活动 Sumif 的电子表格，引用了一个包含 500,000 多行的表格。可以想象，这需要一些时间来计算。我已将它们写入 VBA，因此它们仅在用户选择时进行计算。但是，有这么多 sumif，代码大约需要 3-5 分钟才能运行。我正在寻找加快速度以获得更好的最终用户体验的方法。我将在下面发布我如何执行 sumifs。仅就某些情况而言，这是对同一组用户执行两次 sumif，但使用不同的标准。如果我遗漏了任何相关信息，请告诉我。
For i = 1 To 12
Range("Emp_Utility").Offset(1, i).Activate
Do Until Cells(ActiveCell.Row, 2) = ""
ActiveCell.Value = Application.WorksheetFunction.SumIfs(Hrs, Emp, Cells(ActiveCell.Row, 2), Y, Cells(4, ActiveCell.Column), M, Cells(3, ActiveCell.Column))
ActiveCell.Offset(1, 0).Activate
Loop
Next i
For a = 1 To 12
Range("Emp_Billable").Offset(1, a).Activate
Do Until Cells(ActiveCell.Row, 30) = ""
ActiveCell.Value = Application.WorksheetFunction.SumIfs(Hrs, Emp, Cells(ActiveCell.Row, 2), Y, Cells(4, ActiveCell.Column), M, Cells(3, ActiveCell.Column), Bill, "No")
ActiveCell.Offset(1, 0).Activate
Loop
Next a
采纳答案by jlaverde
Load the ranges into variant arrays and then write the SUMIFS in the code, not using formulas. If you need examples let me know and I'll guide you through it.
将范围加载到变体数组中，然后在代码中写入 SUMIFS，而不是使用公式。如果您需要示例，请告诉我，我会指导您完成。
EDIT: No prob. Here's an example then.
编辑：没有问题。这是一个例子。
Sub example()
Dim EmpUtil, EmpBillable As Variant ' Creates variant array
EmpUtil = Range("Emp_Utility") 'Places Range into EmpUtil Array
EmpBillable = Range("Emp_Billable") 'Places Range into EmpBillable Array
For x = LBound(EmpUtil) To UBound(EmpUtil) 'Cycles through EmpUtil Rows
'Do comparisons and additions here
'References to arrays should be something like
' "EmpUtil(x,3) = example" - for the 3rd column
'for calculations on each column you cycle through each column for that row
For y = LBound(EmpUtil, 2) To UBound(EmpUtil, 2)
EmpUtil(x, y) = Calculation
Next y
Next x
For x = LBound(EmpBillable) To UBound(EmpBillable) 'Cycles through EmpBillable Rows
'Do comparisons and additions here
Next x
Range("Emp_Utility") = EmpUtil 'Places EmpUtil Array back into Range
Range("Emp_Billable") = EmpBillable 'Places EmpBillable Array back into Range
End Sub
This should get you started.
这应该让你开始。
回答by kevin9999
You might consider an alternative to SUMIFS (which can take forever to calculate) such as a combination of SUM and SORT. Please see the answer to How to perform SumIf using VBA on an array in Excelwhich looks at using a SUM and SORT combination resulting in very fast calculations whilst still returning the same values as if a SUMIFS method had been used.
您可能会考虑替代 SUMIFS（可能需要很长时间来计算），例如 SUM 和 SORT 的组合。请参阅如何在 Excel中的数组上使用 VBA 执行 SumIf的答案，该答案着眼于使用 SUM 和 SORT 组合导致非常快速的计算，同时仍然返回与使用 SUMIFS 方法相同的值。
