01

写在前面

占用计算时间的不是公式数量或工作簿大小。而是单元格引用和计算操作的数量以及正在使用的函数的效率。

因为大多数工作表都是通过复制同时包含绝对引用和相对引用的公式来构造的，所以它们通常包含大量公式，这些公式包含重复的或相同的计算和引用。

避免复杂的宏大公式和数组公式。通常，最好增加行和列的数量，并减少复杂的计算。这样，Excel 中的智能重算和多线程计算都能有更好的机会来优化计算。

那么，加快工作簿的计算都有哪些规则可循呢？

02

删除相同、重复和不必要的计算

查找相同、重复和不必要的计算，并确定 Excel 计算此障碍的结果大概需要多少个单元格引用和计算。然后，考虑如何使用更少的引用和计算得出相同的结果。

这通常涉及到下述一个或多个步骤：

• 减少每个公式中的引用数。

• 将重复的计算移动到一个或更多帮助器单元格中，然后从原始公式引用帮助器单元格。

• 再使用一些行和列来计算和存储一次中间结果，以便可在其他公式中重复使用它们。

03

尽可能使用最高效的函数

在找到涉及函数或数组公式的障碍时，请确定能否使用更高效的方式获得相同的结果。例如：

• 与在未排序的数据上进行查找相比，在已排序的数据上查找可能快数十倍或数百倍。

• VBA 用户定义的函数通常比 Excel 中的内置函数慢（但精细编写的 VBA 函数可能很快）。

• 尽量减少 SUM 和 SUMIF 等函数中所用单元格的数量。计算时间与所用单元格的数量成正比（忽略未使用的单元格）。

• 请考虑将运行较慢的数组公式替换为用户定义的函数。

04

充分利用智能重算和多线程计算

Excel 中的智能重算和多线程计算使用得越充分，每次 Excel 重算时要执行的处理越少，因此：

• 如果可以，请避免使用 INDIRECT 和 OFFSET 等可变函数，除非它们的效率明显优于替代项。（经过精心设计后使用 OFFSET 通常速度很快。）

• 尽量减少数组公式和函数中正在使用的区域的大小。

• 将数组公式和宏大公式拆分到单独的帮助程序列和行。

• 避免单线程函数：

• PHONETIC

• CELL（使用“格式”或“地址”参数时）

• INDIRECT

• GETPIVOTDATA

• CUBEMEMBER

• CUBEVALUE

• CUBEMEMBERPROPERTY

• CUBESET

• CUBERANKEDMEMBER

• CUBEKPIMEMBER

• CUBESETCOUNT

• ADDRESS，其中给定第 5 个参数 (sheet_name)

• 引用数据透视表的任何数据库函数（DSUM、DAVERAGE 等）

• ERROR.TYPE

• HYPERLINK

• VBA 和 COM 加载项用户定义的函数

• 避免迭代使用数据表和循环引用：这两者均执行单线程计算。

05

对每次更改进行计时和测试

你所做的一些更改可能令你感到惊讶，这可能是由于未得到你认为应得到的结果，或者计算速度比预期的慢。因此，应按如下方式对每次更改进行计时和测试：

• 使用 RangeTimer 宏对要更改的公式进行计时。

• 进行更改。

• 通过使用 RangeTimer 宏对已更改的公式进行计时。

• 检查已更改的公式是否仍得出正确答案。

06

示例

例一：即时更新总和

你需要计算一列包含 2,000 个数字的即时更新总和。假设 A 列包含数字，B 和 C 列包含即时更新总和。

可使用 SUM（高效函数）编写公式（参考B列）

将公式向下复制到 B2000。

SUM 总共使用了多少单元格引用？B1 引用一个单元格，B2000 引用 2,000 个单元格。平均每个单元格是 1,000 个引用，因此总共是 200 万个引用。如果选择 2,000 个公式并使用 RangeTimer 宏，则显示 B 列 2,000 个公式的计算用时为 80 毫秒。其中大部分计算都重复多次：B2:B2000 的每个公式都使用 SUM A1:A2。

也可以使用（参考C列）

C1=A1
C2=C1+A2

将此公式向下复制到 C2000。

现在总共使用了多少单元格引用？每个公式均使用两个单元格引用（第一个公式除外）。因此，总数为 1999*2+1=3999。这可减少 500 个单元格引用。

RangeTimer 显示 C 列 2,000 个公式的计算用时为 3.7 毫秒，而 B 列的计算用时为 80 毫秒。这一改变的性能改进系数仅为 80/3.7=22（而非 500），因为每个公式都有少量开销。

麦斯贝思敬酒服新娘2020新款女酒红色订婚结婚回门小晚礼服连衣裙平时可穿大合唱团演出服 酒红色 XL

京东198.00购买

例二：错误处理

执行大量计算的公式时如果在出现错误时结果显示为零（在完全匹配查找中经常遇到这种情况），则可以通过多种方式编写它。

• 可以将它编写为单个公式，这样运行速度很慢：

  B1=IF(ISERROR(time expensive formula),0,time expensive formula)

• 可编写成两个公式（此方法较快）：

  A1=time expensive formula

  B1=IF(ISERROR(A1),0,A1)

• 或者，可使用 IFERROR 函数，它运行速度快且简单，还是单个公式：

  B1=IFERROR(time expensive formula,0)

07

写在最后

Excel 让你能够高效管理更大型的工作表，与早期版本相比，它还能大幅提升计算速度。创建大型工作表时，很容易就采用会导致计算缓慢的方式进行构建。计算缓慢的工作表会增加错误，因为用户发现在执行计算时很难进行维护。

通过使用一组简单的技术，可将大部分计算缓慢的工作表提速 10 倍或 100 倍。此外，你还可在设计和创建工作表时应用这些技术来确保其快速计算