Excel VBA：如何通过公式数组更快地计算排名？ [英] Excel VBA: How to make faster calculation on Ranking by Formula Array?
问题描述
我有一个Excel VBA应用程序。等级计算需要4分钟才能获得999行数。我在D列中有学校，总GPA在AF列中，结果排名在AH列（第34列）。排名范围是AF4：AF1003（起始行是4，结束行是1003
，例如但取决于数据大小）排名标准在D列中。等级值是AF列。 j表示尊重行号。 Sheet11.cells（j，34）是结果细胞。如何加快计算速度？它根据每个学校学生提供正确的排名（不是
跳过数字），但时间需要更多。 j和RI是整数变量。
做的不是SheetX（j，1）.Value = vbNullString
SheetX.Cells（j， 34）.FormulaArray =" = SUM（IF（$ D $ 4：$ D $& RI&" = $ D& j&"，IF（$ AF"& j& "< $ AF $ 4：$ AF $ &&;& RI&"，1 / COUNTIFS（$ D $ 4：$ D $"& RI&"，$ D"& j
&"，$ AF $ 4：$ AF $"& RI&"，$ AF $ 4：$ AF $"& RI &"））））+ 1"
j = j + 1
循环
还有另一列将整体GPA（所有学校）排名为：
SheetX.Cells（j，33）.FormulaArray =" = SUM（IF（AF& j&"< $ AF $ 4：$ AF $"& RI&"，1 / COUNTIF（$ AF $ 4：$ AF $"& RI&"，$ AF $ 4：$ AF $& RI&"）））+ 1"
在同一个循环中
谢谢
需要立即帮助。我本周要去试用测试版。
- SK
SK Bhattarai
< blockquote>
我需要您的工作簿副本进行测试并确定，但我相信您应该能够将该公式放在该范围的第一个单元格中，然后复制该单元格并粘贴到剩余范围。 / p>
您需要确定插入公式的第一个单元格，然后确定从第二个单元格到最后一个单元格的范围，然后复制第一个单元格并粘贴公式。 / p>
以上将摆脱循环和加速计算但没有数据的例子我无法确定。
你也可以设置在循环开始之前计算到手动，然后在循环结束后设置为自动。
Application.Calculation = xlManual '在循环前插入
Application.Calculation = xlAutomatic '循环后插入
我认为速度变慢，因为每次从循环中插入公式时所有公式都在计算，而上述方法将允许它只计算一次完成后。
I have an Excel VBA app. It takes 4 minutes to Rank Calculation for 999 row numbers. I have Schools in D column and total GPA is in AF column and resulting rank is in AH column (34 th column). Rank Range is AF4:AF1003 (starting row is 4 and ending row is 1003 for eg but depends on data size) Rank Criteria is in D column. to be rank value is AF column. j means respecting row number. Sheet11.cells(j, 34) is resulting cells. How to make faster calculation? It gives correct rank based on each school students (in not skipping number) but time takes more. j and RI are integer variables.
Do while not SheetX(j, 1).Value = vbNullString
SheetX.Cells(j, 34).FormulaArray = "=SUM(IF($D$4:$D$" & RI & "=$D" & j & ",IF($AF" & j & "<$AF$4:$AF$" & RI & ",1/COUNTIFS($D$4:$D$" & RI & ",$D" & j & ",$AF$4:$AF$" & RI & ",$AF$4:$AF$" & RI & "))))+1"
j = j + 1
Loop
There is another column for ranking overall GPA (All School) as:
SheetX.Cells(j, 33).FormulaArray = "=SUM(IF(AF" & j & "<$AF$4:$AF$" & RI & ",1/COUNTIF($AF$4:$AF$" & RI & ",$AF$4:$AF$" & RI & ")))+1"Within the Same Do While Loop
Thanks
Needs immediate help. I am going to demo beta this week.
- SK
SK Bhattarai
I would need a copy of your workbook to test and be sure but I believe that you should be able to put the formula in the first cell of the range and then copy that cell and paste to the remaining range.
You will need to identify the first cell in which to insert the formula and then identify the range from the 2nd cell to the last cell and then copy the first cell and paste the formula.
The above will get rid of the loop and speed up calculation but without an example of data I can't be sure.
You could also set calculation to Manual before the loop starts and then set to Automatic after the loop is finished.
Application.Calculation = xlManual 'Insert before the loop
Application.Calculation = xlAutomatic 'Insert after the loop
I believe that the speed is slowed because all the formulas are calculating each time a formula is inserted from within the loop and the above method will allow it to only calculate once when finished.
这篇关于Excel VBA：如何通过公式数组更快地计算排名？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
