教程 ｜ Excel性能优化方法（五）

01

如果必须使用数组公式，以下是一些加快速度的好方法：

• 使用动态区域名称或结构化表引用来最大程度地减少单元格数量。

• 将多个条件拆分为一列辅助公式，每一行返回 True 或 False，然后引用 SUMIF 或数组公式中的辅助列。这似乎不会减少单个数组公式的计算数量；然而，在大多数情况下，它只允许智能重新计算过程重新计算需要重新计算的辅助列中的公式。

• 考虑将所有条件都连接到单个条件中，然后使用 SUMIF。

• 如果数据可以排序，则计算行组的数目并限制数组公式考虑子集组。

02

多条件 SUMIFS、COUNTIFS 和其他 IFS 系列函数

这些函数从左到右依次计算每个条件。因此，将最严格的条件放在最前面会更加高效，这样后续条件只需查看最小行数。

03

对多条件数组公式使用 SUMPRODUCT 的选项

从 Excel 2007 开始，应尽可能始终使用 SUMIFS、COUNTIFS 和 AVERAGEIFS 函数，并在 Excel 2016 中使用 MAXIFS 和 MINIFS 函数来取代 SUMPRODUCT 公式。

在早期版本，使用 SUMPRODUCT 而不是 SUM 数组公式有几个优点：

• SUMPRODUCT 不必使用 Ctrl+Shift+Enter 完成数组输入。

• SUMPRODUCT 通常速度要快一些（5% 到 10%）。

可按如下方式将 SUMPRODUCT 用于多条件数组公式：

SUMPRODUCT(--(Condition1),--(Condition2),RangetoSum)

在此示例中，Condition1 和 Condition2 都是条件表达式，例如 $A$1:$A$10000<=$Z4。因为条件表达式返回 True 或 False 而不是数字，必须将它们强制转换为 SUMPRODUCT 函数内的数字。可以用两个减号 (--)，或者通过加上 0 (+0) 或乘以 1 (x1) 来执行此操作。使用 -- 的速度略快于 +0 或 x1。

请注意，在条件表达式中使用的区域或数组的大小和形状必须与要求和的区域相同，并且它们不能包含整列。

还可以直接乘以 SUMPRODUCT 中的项，而不是使用逗号分隔它们：

  SUMPRODUCT((Condition1)*(Condition2)*RangetoSum)

这通常比使用逗号语法稍微慢一些，如果要求和的区域包含文本值，它会给出一个错误。但是，它更加灵活一些，因为求和的区域可能有多个列，而条件只有一个列。

04

使用 SUMPRODUCT 进行相乘并添加区域和数组

在执行加权平均值计算等情况下（在这些情况下需要将一个数字区域与另一数字区域相乘并对结果求和），对 SUMPRODUCT 使用逗号语法比输入数组的 SUM 的速度快 20% 至 25%。

{=SUM($D$2:$D$10301*$E$2:$E$10301)}=SUMPRODUCT($D$2:$D$10301*$E$2:$E$10301)=SUMPRODUCT($D$2:$D$10301,$E$2:$E$10301)

这三个公式全部产生相同的结果，但是对 SUMPRODUCT 使用逗号语法的第三个公式所花费的时间仅占其他两个公式所需计算时间的大约 77%。

05

注意潜在的数组和函数计算障碍

对 Excel 中的计算引擎进行优化，以利用数组公式和引用区域的函数。然而，这些公式和函数的一些异常安排有时会（但不总是）导致计算时间的显著增加。

如果发现计算障碍涉及数组公式和区域函数，则应查找以下内容：

• 部分重叠引用。

• 引用在另一数组公式或区域函数中计算的部分单元格块的数组公式和区域函数。在时序分析中经常会出现此情况。

• 一组按行引用的公式，第二组公式按列引用第一组公式。

• 一大组包含一组列的单行数组公式，每一列底部都带有 SUM 函数。