☰

比较公式，哪个最有效

tag-icon • tag-icon microsoft-excel
比较公式，哪个最有效

有没有办法测试 Excel 中公式的速度/效率？

这可以在 SQL Server 中通过显示实际的执行计划来完成，Excel 可以做类似的事情吗？

例如，以下查询都会给出相同的结果，

{=SUM(SUMIF(A:A,D1:D3,B:B))}

=SUMPRODUCT(ISNUMBER(MATCH(A1:A25,D1:D3,0))*B1:B25)

=SUMIF(A1:A25,D2,B1:B25)+SUMIF(A1:A25,D3,B1:B25)+SUMIF(A1:A25,D1,B1:B25)

如果我在数千行中使用这些公式，我的工作表将需要越来越长的时间来计算。

我如何确定哪一个是最有效的？

问题不仅仅与这些例子有关，是否有一种普遍的方法可以找出执行/性能？

答案1

在一些手册中我发现求和函数或者统计分析系统比乘积和，因此我决定测试哪一个更快，并且我使用了下面显示的 VBA 代码，计算两个公式所花费的时间。

Sub CalculateRunTime()

 Dim StartTime As Double
 Dim SecondsElapsed As Double


 StartTime = Timer

'Range("E28").FormulaArray = "=SUMPRODUCT(MATCH(B25:B27,A25:A50,0))*(C25:C50)"

Range("F28").Formula = "=SUMIF(A25:A50,B25,C25:C50)+SUMIF(A25:A50,B26,C25:C50)+SUMIF(A25:A50,B27,C25:C50)"

 SecondsElapsed = Round(Timer - StartTime, 2)


  MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation

End Sub

结果：

SUMPRODUCT 所花费的时间：

在此处输入图片描述

SUMIF 所用的时间：

在此处输入图片描述

注意：

  • 我发现求和函数花费的时间更少。**

  • 在某些测试中，经过的时间可能会有所不同，因为取决于速度中央处理器还。

  • 如果想要获取以分钟为单位的经过时间，如果使用巨大的数据范围，则可以使用此代码。

    MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")


MsgBox "This code ran successfully in " & 
_ MinutesElapsed & " minutes", VbInformation

相关内容