为什么Excel数组公式变慢? [英] Why are Excel Array formulas slow?

问题描述

我在网络上的许多地方都读到，使用数组公式的缺点之一是它们很慢(并且使用中间辅助列可以加快速度).

I have read in many places on the web that one of the downsides of using array formulas is that they are slow (and that using intermediate helper columns can speed things up).

给出的一般解释是，他们正在做很多工作.确实如此，但是辅助公式肯定也必须重现分布在不同单元中的相同数量的工作.

The general explanation given is that they are performing a lot of work. They are, but the helper formulas must surely also be reproducing the same amount of work spread across different cells.

我想更详细地了解:

1. 为什么数组公式比较慢?
2. 在什么情况下可能不是这样?

1. Why array formulas are slow?
2. What situations might it not be so?

推荐答案

让我们举一个具体的例子:

Let's have a concrete example:

公式:

在D2:E11中:

=IF($A2=D$1,$B2)

和E12中的

:

=SUM(D2:D11)

和E13中的

:

{=SUM(IF($A$2:$A$11=D$1,$B$2:$B$11))}

作为通过[Ctrl] + [Shift] + [Enter]确认的数组公式

as an array formula confirmed with [Ctrl]+[Shift]+[Enter]

:

=SUMIF($A$2:$A$11,D$1,$B$2:$B$11)

那么，如果A2:B11中发生某些变化，会发生什么?

So what happens if something changes in A2:B11?

在帮助器列中，仅需要重新计算那些实际上已更改的单元格的公式.当然，必须重新计算D12和E12中的和.但这正是开发Excel的原因.因此，该程序可能已经过优化，正好可以做到这一点.

In the helper columns only those formulas needs to recalculate which refers to the cells which have actually changed. And of course the sums in D12 and E12 must be recalculated. But this is exactly wherefore Excel was developed. So the program is probably optimized to do exactly this very performant.

必须完全重新计算数组公式，而与实际更改了哪些单元格无关.在A2:B11中的每个更改之后，有10个IF函数和SUM.而且没有优化的东西.数组上下文仅导致IF函数(默认情况下期望一个条件和一个value_if_the_criteria_is_true)对条件数组中的每个条件执行多次，并提供结果数组，然后将其与SUM相加.

The array formula must be fully recalculated, independent of which cells have actually changed. That are 10 IF functions and the SUM after each change in A2:B11. And there is nothing optimized. The array context causes only the IFfunction, which per default expect one criteria and one value_if_the_criteria_is_true, to perform multiple times for each criteria in the criteria array and provide a results array, which is then summed by SUM.

SUMIF也必须完全重新计算.但这将通过优化的预编译函数SUMIF完成.该函数不像数组公式那样简单地调用IF函数10次.

The SUMIF also must be fully recalculated. But this will be done with the precompiled function SUMIF which is optimized to do so. That function not simply calls the IF function 10 times, as the array formula does.

因此，就性能而言，带有助手列的解决方案将是最好的，然后是SUMIF，数组公式肯定是性能最低的解决方案.

So if it comes to performance, the solution with helper columns will be the best, then comes SUMIF and the array formula is surely the solution with lowest performance.

但是，当然，只有在影响公式的数据单元太多或工作表中的数组公式很多时，性能差异才会明显.因此，如果需要使用数组公式，则不应将整列或整行作为数组，并且不应在表中放置数千个数组公式.

But of course the performance difference will only noticeable if either there are much data cells which influence the formulas or if there are much array formulas in the sheet. So if one needs using array formulas, then those should not take whole columns or rows as arrays and one should not put thousands of array formulas in the sheet.

这篇关于为什么Excel数组公式变慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！