Excel 性能：优化性能障碍的提示

项目
04/07/2023

适用于：Excel | Excel 2013 | Office 2016 | VBA

按照这些提示优化 Excel 中经常出现的性能障碍。

优化引用和链接

了解如何提高与引用和链接类型相关的性能。

请勿使用向前引用和向后引用

若要提高清晰度并避免错误，请设计公式，使其不会将正向 (引用右侧或下方) 其他公式或单元格。向前引用通常不影响计算性能，但首次计算工作簿的极端情况除外（在这种情况下，如果有很多公式需要推迟计算，则可能需要较长的时间来建立合理的计算序列）。

尽量减少使用通过迭代计算循环引用

通过迭代计算循环引用的速度很慢，因为需要进行多次计算，而且这些计算是单线程的。通常可以使用代数“展开”循环引用，这样就不再需要进行迭代计算。例如，在现金流和利息计算中，尽量先计算不含利息的现金流，再计算利息，然后计算包括利息在内的现金流。

Excel 逐个工作表计算循环引用，而不考虑依赖关系。因此，如果循环引用跨多个工作表，通常计算速度会很慢。尝试将循环计算移动到单个工作表上，或优化工作表计算顺序，以避免不必要的计算。

在迭代计算启动之前，Excel 必须重新计算工作簿以标识所有循环引用及其依赖项。此过程相当于两到三次迭代计算。

在确定循环引用及其依赖项后，每次迭代不仅需要 Excel 计算循环引用中的所有单元格，还需要计算依赖于循环引用链中单元格的所有单元格，以及可变单元格及其依赖项。如果有依赖于循环引用中的单元格的复杂计算，将其隔离到一个单独的关闭工作簿中，并在循环计算聚合后打开它以进行重新计算，这样速度可以更快。

减少循环计算中的单元格数和这些单元格占用的计算时间非常重要。

避免工作簿之间的链接

尽可能避免工作簿间的链接；这种链接的速度可能很慢，容易断开，并且不一定易于查找和修复。

使用数量较少的大型工作簿通常（但不总是）优于使用数量较多的小型工作簿。以下是一些例外情况：你具有很多极少重新计算的前端计算，因此可以将它们放在单独的工作簿中；或者你的 RAM 不足。

尝试使用在关闭的工作簿上工作的简单直接单元格引用。通过执行此操作，在重新计算任何工作簿时可避免重新计算所有链接的工作簿。此外，还可以看到 Excel 从关闭的工作簿中读取的值，这通常对于调试和审核工作簿非常重要。

如果不能避免使用链接的工作簿，请尝试将它们全部打开而不是关闭，并且先打开链接指向的工作簿再打开包含链接的工作簿。

最大程度地减少工作表之间的链接

使用很多工作表可以简化工作簿的使用，但是，通常计算指向其他工作表的引用比计算工作表内的引用速度要慢。

最大程度地减小已用区域

为了节省内存和减小文件大小，Excel 会尝试只存储已用工作表上的区域的相关信息。这称为已用区域。有时，各种编辑和格式设置操作将已用区域显著扩展到你当前认为已使用的区域之外。这会导致性能障碍和文件大小障碍。

可以使用 Ctrl+End 检查工作表上可见的已用区域。如果这是多余的，则应考虑删除上次实际使用的单元格下方和右侧的所有行和列，然后保存工作簿。首先创建备份副本。如果在扩展到删除区域或引用删除区域的范围使用公式，这些范围的大小将缩小或更改为 #N/A。

允许额外数据

如果经常向工作表中添加多行或多列数据，则需要找出一种方法，使您的 Excel 公式自动引用新数据区域，而不是每次都尝试查找并更改您的公式。

为此，可以在公式中使用远超出当前数据边界的较大区域。然而，在某些情况下，这可能会导致无效计算而且很难维护，这是因为删除行和列可能会在你不注意的情况下减小区域。

使用结构化表引用（推荐）

从 Excel 2007 开始，可以使用结构化表引用，此类引用可随所引用表大小的增加或减小自动扩大和缩小。

此解决方案有诸多优点：

与其他方法（整列引用和动态区域）相比，这种方法对性能产生的不利条件较少。
在单个工作表上拥有多个数据表很容易。
嵌入到表中的公式还可以随数据的大小而扩大和缩小。

或者，使用整列引用和整行引用

另一种方法是使用整列引用，例如 $A:$A。此引用返回 A 列中的所有行。因此，可以根据需要添加任意数量的数据，引用始终都包含这些数据。

此解决方案包含以下优点和缺点：

许多 Excel 内置函数（SUM、SUMIF）高效计算整列引用，因为它们自动识别列中上次使用的行。但是，类似于 SUMPRODUCT 这样的数组计算函数既不能处理整列引用，也无法计算列中的所有单元格。
用户定义的函数不会自动识别列中最后使用的行，因此，计算整个列引用时经常效率低下。然而，可以轻松地对用户定义函数进行编程，以便它们识别上次使用的行。
如果单个工作表上有多个数据表，则很难使用整个列引用。
在 Excel 2007 和更高版本中，数组公式可以处理整列引用，但这会强制计算列中的所有单元格，包括空单元格。计算速度可能很慢，特别是对于 100 万行数据。

或者，可以使用动态区域

通过在已命名区域的定义中使用 OFFSET 或 INDEX 和 COUNTA 函数，可以使已命名区域引用的区域动态扩大和缩小。例如，使用下列公式之一创建已定义名称：

  =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
  =Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)+ROW(Sheet1!$A$1) - 1,1)

在公式中使用动态区域名称时，它会自动扩大以包含新条目。

对于动态区域，使用 INDEX 公式通常比使用 OFFSET 公式更可取，因为 OFFSET 的缺点是它是一个可变函数，每次重新计算都要对其进行计算。

性能下降是因为动态区域公式中的 COUNTA 函数必须检查多个行。可以通过将公式的 COUNTA 部分存储在单独单元格或已定义的名称中，然后在动态区域内引用单元格或名称来最大程度减少性能下降：

 Counts!z1=COUNTA(Sheet1!$A:$A)
 OffsetDynamicRange=OFFSET(Sheet1!$A$1,0,0,Counts!$Z$1,1)
 IndexDynamicRange=Sheet1!$A$1:INDEX(Sheet1!$A:$A,Counts!$Z$1+ROW(Sheet1!$A$1) - 1,1)

还可以使用 INDIRECT 等函数来构造动态区域，但 INDIRECT 是可变函数，且始终计算单线程。

动态区域具有以下优点和缺点：

动态区域可以很好地限制数组公式执行的计算数。
在单列中使用多个动态区域需要专用的计数函数。
使用多个动态区域会降低性能。

缩短查找计算时间

在 Office 365 版本 1809 和更高版本中，在从相同表区域查找多个列（或使用 HLOOKUP 查找行）时，Excel 的 VLOOKUP、HLOOKUP 和 MATCH 对未排序数据进行完全匹配要比以往快得多。

也就是说，对于早期的 Excel 版本，查找仍然是常见的重要计算障碍。幸运的是，有许多方法可以改进缩短计算时间。如果使用完全匹配选项，则函数的计算时间与找到匹配之前扫描的单元格数量成正比。对于在较大区域进行查找，这一时间可能很长。

对排序数据使用 VLOOKUP、HLOOKUP 和 MATCH 的近似匹配选项的查找时间很短，并且不会根据所查找的区域长度显著增加。特征与二进制搜索相同。

了解查找选项

确保你理解 MATCH、VLOOKUP 和 HLOOKUP 中的 match-type 和 range-lookup 选项。

以下代码示例显示 MATCH 函数的语法。有关详细信息，请参阅 WorksheetFunction 对象的 Match 方法。

  MATCH(lookup value, lookup array, matchtype)

Matchtype=1 在查找数组按升序排序时返回小于或等于查找值的最大匹配项（近似匹配）。如果查找数组未按升序排序，MATCH 将返回错误结果。默认选项是按升序排序的近似匹配项。
Matchtype=0 请求完全匹配项并假定数据未排序。
Matchtype=-1 在查找数组按降序排序时返回大于或等于查找值的最小匹配项（近似匹配）。

以下代码示例显示 VLOOKUP 和 HLOOKUP 函数的语法。有关详细信息，请参阅 WorksheetFunction 对象的 VLOOKUP 和 HLOOKUP 方法。

  VLOOKUP(lookup value, table array, col index num, range-lookup)
  HLOOKUP(lookup value, table array, row index num, range-lookup)

Range-lookup=TRUE 返回小于或等于查找值的最大匹配项（近似匹配）。这是默认选项。表数组必须按升序排序。
Range-lookup=FALSE 请求完全匹配项并假定数据未排序。

尽可能避免对未排序数据执行查找，因为速度较慢。如果数据已排序，但想要进行完全匹配，请参阅对缺失值的排序数据使用两个查找。

使用 INDEX 和 MATCH 或 OFFSET 而不是 VLOOKUP

请尝试使用 INDEX 和 MATCH 函数而不是 VLOOKUP。虽然 VLOOKUP 比 MATCH 和 INDEX，或 OFFSET 组合的速度稍快（大约快 5%）、更简单，并使用更少的内存，但 MATCH 和 INDEX 所提供的额外灵活性通常可以显著节省时间。例如，可以将完全 MATCH 的结果存储在单元格中，并在几个 INDEX 语句中重用。

INDEX 函数是快速运行的不变函数，它可以加快重新计算的速度。 OFFSET 函数的运行速度也很快；但它是可变函数，因此有时会显著增加处理计算链所需的时间。

可以轻松将 VLOOKUP 转换为 INDEX 和 MATCH。以下两个语句返回相同结果：

 VLOOKUP(A1, Data!$A$2:$F$1000,3,False)

 INDEX(Data!$A$2:$F$1000,MATCH(A1,$A$1:$A$1000,0),3)

加快查找速度

由于完全匹配查找可能很慢，因此可以考虑使用以下选项来提高性能：

使用一个工作表。将查找和数据保存在同一工作表上会更快。
如果可以，先对数据执行 SORT（SORT 较快），然后使用近似匹配。
当必须使用完全匹配查找时，请将要扫描的单元格范围限制到最小区域。使用表和结构化引用或动态区域名称，而不是引用大量的行或列。有时可以预先计算查找的范围下限和范围上限。

对缺少值的排序数据使用两个查找

对于在数行内执行的查找，两个近似匹配显著快于一个完全匹配。 （分界点是大约 10-20 行。）

如果可以对数据排序，但由于不能确定要查找的值是否位于查找范围内而仍无法使用近似匹配，则可以使用以下公式：

  IF(VLOOKUP(lookup_val ,lookup_array,1,True)=lookup_val, _
      VLOOKUP(lookup_val, lookup_array, column, True), "notexist")

公式第一部分的运作方式是对查找列本身执行近似查找。

  VLOOKUP(lookup_val ,lookup_array,1,True)

可以使用以下公式检查从查找列得到的结果是否与查找值相同（在这种情况下，你有一个完全匹配项）：

  IF(VLOOKUP(lookup_val ,lookup_array,1,True)=lookup_val,

如果此公式返回“True”，则找到了完全匹配项，所以可以再次执行近似查找，但这次从列中返回所需的结果。

  VLOOKUP(lookup_val, lookup_array, column, True)

如果从查找列得到的结果与查找值不匹配，则表示它是缺失值，公式将返回“notexist”。

注意，如果查找的值小于列表中的最小值，则会收到错误。可以使用 IFERROR 来处理此错误，或者向列表添加一个小的测试值。

对于缺少值的未排序数据，使用 IFERROR 函数

如果必须对未排序数据使用完全匹配查找，但是不能确定查找值是否存在，通常必须处理找不到匹配项时返回的 #N/A。从 Excel 2007 开始，可以使用 IFERROR 函数，该函数既快又简单。

  IF IFERROR(VLOOKUP(lookupval, table, 2 FALSE),0)

在早期版本中，一个简单但较慢的方法是使用包含两个查找的 IF 函数。

  IF(ISNA(VLOOKUP(lookupval,table,2,FALSE)),0,_
      VLOOKUP(lookupval,table,2,FALSE))

如果使用完全 MATCH 一次，将结果存储在单元格中，然后在执行 INDEX 之前测试结果，则可以避免双重完全查找。

  In A1 =MATCH(lookupvalue,lookuparray,0)
  In B1 =IF(ISNA(A1),0,INDEX(tablearray,A1,column))

如果无法使用两个单元格，则使用 COUNTIF。它通常比完全匹配查找更快。

  IF (COUNTIF(lookuparray,lookupvalue)=0, 0, _
      VLOOKUP(lookupval, table, 2 FALSE))

使用 MATCH 和 INDEX 对多个列进行完全匹配查找

通常可以多次重复使用存储的完全 MATCH。例如，如果要对多个结果列执行完全查找，则可以使用一个 MATCH 和多个 INDEX 语句（而不是多个 VLOOKUP 语句）来节省时间。

为 MATCH 添加一个额外的列来存储结果 (stored_row)，并对每个结果列使用以下语句：

  INDEX(Lookup_Range,stored_row,column_number)

或者，可以在数组公式中使用 VLOOKUP。 (必须使用 Ctrl+-Shift+Enter 输入数组公式。Excel 将添加 { 和 } 以显示这是) 数组公式。

  {VLOOKUP(lookupvalue,{4,2},FALSE)}

对一组连续的行或列使用 INDEX

还可以从一个查找操作返回多个单元格。要查找多个连续的列，可以使用数组公式中的 INDEX 函数一次返回多个列（使用 0 作为列号）。还可以使用 INDEX 函数一次返回多个行。

  {INDEX($A$1:$J$1000,stored_row,0)}

这将从之前的 MATCH 语句创建的存储行返回列 A 到列 J。

使用 MATCH 返回单元格的矩形块

使用 MATCH 和 OFFSET 函数可返回单元格的矩形块。

使用 MATCH 和 INDEX 进行二维查找

通过将 INDEX 函数与两个内嵌的 MATCH 函数（一个对应行，一个对应列）结合使用，可以对表的行和列使用单独的查找，从而有效地执行二维表查找。

使用子集区域进行多索引查找

在大型工作表中，可能经常需要使用多个索引进行查找，例如查找国家/地区的产品量。为此，可以连接索引并使用连接的查找值执行查找。然而，有两个原因会导致其效率低下：

连接字符串是计算密集型操作。
查找将涵盖较大区域。

例如，查找 (查找的第一行和最后一行，然后查找该子集范围内的产品) ，通常更有效。

考虑三维查找选项

除行和列外，若还要查找要使用的表，则可以使用以下方法，这些方法重点处理如何使 Excel 查找或选择表。

如果要查找的每个表（第三维）都存储为一组已命名结构化表、区域名称或存储为表示区域的文本字符串表，则可以使用 CHOOSE 或 INDIRECT 函数。

使用 CHOOSE 和区域名称是一种有效方法。 CHOOSE 是不可变函数，但最适用于相对较少的表。此示例动态使用 TableLookup_Value 来选择要用于查找表的区域名称 (TableName1, TableName2, ...)。
```
  INDEX(CHOOSE(TableLookup_Value,TableName1,TableName2,TableName3), _
  MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
```
下面的示例使用 INDIRECT 函数和 TableLookup_Value 动态创建要用于查找表的工作表名称。这种方法的优点是简单，能够处理大量表。因为 INDIRECT 是可变的单线程函数，所以即使没有数据发生变化，查找也是每次计算时计算的单线程。使用此方法速度较慢。
```
  INDEX(INDIRECT("Sheet" &amp; TableLookup_Value &amp; "!$B$2:$Z$1000"), _
  MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
```
还可以使用 VLOOKUP 函数查找要用于表的工作表名称或文本字符串，然后使用 INDIRECT 函数将结果文本转换为区域。
```
  INDEX(INDIRECT(VLOOKUP(TableLookup_Value,TableOfTAbles,1)),MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
```

另一方法是将所有表聚合到一个大型表中，该表包含另一标识各个表的列。然后可以使用前面的示例中所示的多索引查找方法。

使用通配符查找

MATCH、VLOOKUP 和 HLOOKUP 函数允许使用通配符？ (任何单个字符) ，并且 * (字母完全匹配项上) 任何字符或任意数量的字符。有时，可使用此方法避免多个匹配项。

优化数组公式和 SUMPRODUCT

数组公式和 SUMPRODUCT 函数功能强大，但必须谨慎处理。单个数组公式可能需要多次计算。

优化数组公式计算速度的关键在于，确保尽可能减少在数组公式中计算的单元格和表达式的数量。请记住，数组公式与可变公式有点相似：如果它引用的任何一个单元格发生更改、可变或者经过重新计算，则数组公式会计算该公式中的所有单元格并计算执行计算所需的所有虚拟单元格。

要优化数组公式的计算速度，请执行以下操作：

将数组公式中的表达式和区域引用提取到单独的辅助列和行中。这样可以更好地利用 Excel 中的智能重新计算过程。
不要引用完整行，或引用超出需要的行和列。数组公式被迫计算公式中的所有单元格引用，即使单元格为空或未使用。从 Excel 2007 开始，有 100 万行可用，引用整个列的数组公式的计算速度极其缓慢。
从 Excel 2007 开始，如果可以，请使用结构化引用来最大程度地减少数组公式计算的单元格数。
在 Excel 2007 之前的版本中，尽可能使用动态区域名称。尽管它们可变，但这是值得的，因为它们最大程度缩小了区域大小。
注意同时引用行和列的数组公式：这将强制计算矩形区域。
尽可能使用 SUMPRODUCT；它比等效的数组公式速度稍微快一点。

考虑对多条件数组公式使用 SUM 的选项

如果可以，应始终使用 SUMIFS、COUNTIFS 和 AVERAGEIFS 函数来取代数组公式，因为它们的计算速度快得多。 Excel 2016 引入了快速 MAXIFS 和 MINIFS 函数。

在 Excel 2007 之前的版本中，数组公式通常用于计算具有多个条件的总和。这相对容易实现，尤其是在 Excel 中使用条件求和向导时，但通常比较慢。通常有更快的方法可以得到相同的结果。如果只有几个多条件 SUM，则可以使用 DSUM 函数，它比等效的数组公式速度快得多。

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

使用动态区域名称或结构化表引用来最大程度地减少单元格数量。
将多个条件拆分为一列辅助公式，每一行返回 True 或 False，然后引用 SUMIF 或数组公式中的辅助列。这似乎不会减少单个数组公式的计算数量；然而，在大多数情况下，它只允许智能重新计算过程重新计算需要重新计算的辅助列中的公式。
考虑将所有条件都连接到单个条件中，然后使用 SUMIF。
如果数据可以排序，则计算行组的数目并限制数组公式考虑子集组。

优先处理多条件 SUMIFS、COUNTIFS 和其他 IFS 系列函数

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

考虑对多条件数组公式使用 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)

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

使用 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%。

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

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

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

部分重叠引用。
引用在另一数组公式或区域函数中计算的部分单元格块的数组公式和区域函数。在时序分析中经常会出现此情况。
一组按行引用的公式，第二组公式按列引用第一组公式。
一大组包含一组列的单行数组公式，每一列底部都带有 SUM 函数。

高效使用函数

函数大大扩展了 Excel 的功能，但是使用函数的方式通常会影响计算时间。

避免使用单线程函数

大多数本机 Excel 函数都能很好地处理多线程计算。但是，尽可能避免使用以下单线程函数：

VBA 和自动化用户定义函数 (UDF)，但基于 XLL 的 UDF 可以是多线程函数
PHONETIC
CELL（使用“格式”或“地址”参数时）
INDIRECT
GETPIVOTDATA
CUBEMEMBER
CUBEVALUE
CUBEMEMBERPROPERTY
CUBESET
CUBERANKEDMEMBER
CUBEKPIMEMBER
CUBESETCOUNT
ADDRESS，其中给出了第五个参数 (sheet_name)
引用数据透视表的任何数据库函数（DSUM、DAVERAGE 等）
ERROR.TYPE
HYPERLINK

将表用于处理区域的函数

对于处理区域的 SUM、SUMIF 和 SUMIFS 等函数，计算时间与正在求和或计数的已用单元格数成正比。不会检查未使用的单元格，因此整个列引用相对有效，但最好确保不包含比所需更多的已用单元格。使用表，或计算子集区域或动态区域。

减少可变函数

可变函数会降低重新计算的速度，因为它们会增加每次计算时必须重新计算的公式数量。

通过使用 INDEX（而不是 OFFSET）和 CHOOSE（而不是 INDIRECT）来减少可变函数的数量。不过，OFFSET 是一个快速计算函数，通常可以用创造性方式进行快速计算。

使用 C 或 C++ 用户定义的函数

以 C 或 C++ 编程的用户定义函数和使用 C API 的用户定义函数（XLL 加载项函数）执行速度通常比使用 VBA 或 Automation 开发的用户定义函数（XLA 或自动化加载项）快。有关详细信息，请参阅开发 Excel 2010 XL。

VBA 用户定义函数的性能对你编程和调用它们的方式非常敏感。

使用速度更快的 VBA 用户定义函数

使用 Excel 公式计算和工作表函数通常比使用 VBA 用户定义函数更快。这是因为每个用户定义函数调用的开销都很小，而将信息从 Excel 传输到用户定义函数的开销很大。但精心设计和调用的用户定义函数要比复杂的数组公式计算速度快得多。

确保已将对工作表单元格的所有引用置于用户定义函数的输入参数中而不是用户定义函数的主体中，以便您可以避免添加不必要的 Application.Volatile。

如果必须有许多使用用户定义函数的公式，请确保处于手动计算模式，并且计算是从 VBA 开始的。如果计算不是从 VBA 调用的（例如，在自动模式下或在手动模式下按 F9 时），则 VBA 用户定义函数的计算速度要慢得多。当 Visual Basic 编辑器 (Alt+F11) 处于打开状态或已在当前 Excel 会话中打开时，尤其如此。

可以按如下方式捕获 F9 并将它重定向到 VBA 计算子例程。将此子例程添加到 Thisworkbook 模块。

  Private Sub Workbook_Open()
      Application.OnKey "{F9}", "Recalc"
  End Sub

将此子例程添加到标准模块。

  Sub Recalc()
      Application.Calculate
      MsgBox "hello"
  End Sub

自动化加载项中的用户定义的函数 (Excel 2002 及更高版本中) 不会产生 Visual Basic 编辑器开销，因为它们不使用集成编辑器。自动化加载项中 Visual Basic 6 用户定义函数的其他性能特征类似于 VBA 函数。

如果用户定义的函数处理一个区域内的每个单元格，则会将输入声明为一个区域、将其分配给包含数组的变量，并对其进行循环。如果要高效处理整列引用，必须创建输入区域的一个子集，在它与已用区域的交集处对其进行划分，如本例所示。

  Public Function DemoUDF(theInputRange as Range)
      Dim vArr as Variant
      Dim vCell as Variant
      Dim oRange as Range
      Set oRange=Union(theInputRange, theRange.Parent.UsedRange)
      vArr=oRange
      For Each vCell in vArr
          If IsNumeric(vCell) then DemoUDF=DemoUDF+vCell
      Next vCell
  End Function

如果用户定义函数使用工作表函数或 Excel 对象模型方法处理区域，则将区域声明为对象变量比将所有数据从 Excel 传输到用户定义函数通常更有效。

  Function uLOOKUP(lookup_value As Variant, lookup_array As Range, _
                   col_num As Variant, sorted As Variant, _
                   NotFound As Variant)
      Dim vAnsa As Variant
      vAnsa = Application.VLookup(lookup_value, lookup_array, _
                                  col_num, sorted)
      If Not IsError(vAnsa) Then
          uLOOKUP = vAnsa
      Else
          uLOOKUP = NotFound
      End If
  End Function

如果在计算链的早期调用用户定义的函数，则可以将其作为未计算的参数传递。在用户定义的函数中，可以通过对包含公式的空单元格使用以下测试来检测未计算的单元格：

  If ISEMPTY(Cell.Value) AND Len(Cell.formula)>0 then

每个对用户定义函数的调用以及每次将数据从 Excel 传输到 VBA 都会产生时间开销。有时，一个多单元格数组公式用户定义函数可通过将多个函数调用合并为一个具有多单元格输入区域且返回结果区域的函数，来帮助用户最大程度地减少这些开销。

最大程度地减少 SUM 和 SUMIF 引用的单元格区域

Excel SUM 和 SUMIF 函数通常用于大量单元格。这些函数的计算时间与涉及的单元格数成比例，因此，请尝试最大程度地减小函数引用的单元格区域。

使用通配符 SUMIF、COUNTIF、SUMIFS、COUNTIFS 和其他 IFS 函数

使用通配符？ (任何单字符) ， (* 没有字符或任意数量的字符) 字母范围的条件作为 SUMIF、COUNTIF、SUMIFS、COUNTIFS 和其他 IFS 函数的一部分。

选择期间至本日 SUM 和累积 SUM 方法

可以通过两种方法执行期间至本日 SUM 或累积 SUM。假设要以累积方式 SUM 的数字位于 A 列中，并且希望 B 列包含累积和；可以执行以下操作之一：

可以在 B 列中创建一个公式（如 =SUM($A$1:$A2)）并根据需要向下拖动任意距离。 SUM 的起始单元格定位在 A1 中，但是因为结束单元格包含相对行引用，所以它在每行中自动增加。
可以分别在单元格 B1 和 B2 中创建一个公式（如分别为 =$A1 和 =$B1+$A2）并根据需要向下拖动任意距离。它通过将此行的行数添加到前面的累积 SUM 来计算累积单元格。

对于 1,000 行，使用第一种方法，Excel 会进行大约 500,000 次计算，但使用第二种方法，Excel 仅进行大约 2,000 次计算。

计算子集总和

如果你的表（例如，区域中的网站）有多个已排序索引，则通过动态计算要在 SUM 或 SUMIF 函数中使用的行（或列）的子集区域的地址，通常可以节省大量计算时间。

若要计算行或列的子集区域的地址：

计算每个子集块的行数。
为每个块累计添加计数以确定其起始行。
将 OFFSET 与起始行和计数结合使用以将子集区域返回仅涉及行子集块的 SUM 或 SUMIF。

为筛选列表使用 SUBTOTAL

使用 SUBTOTAL 函数可以 SUM 筛选的列表。 SUBTOTAL 函数很有用，因为与 SUM 不同，它会忽略以下内容：

因筛选列表导致的隐藏行。从 Excel 2003 开始，还可以使 SUBTOTAL 忽略所有隐藏行，而不仅仅是筛选掉的行。
其他 SUBTOTAL 函数。

使用 AGGREGATE 函数

AGGREGATE 函数是一种计算 19 种不同聚合数据方法的强大而高效的方法（如 SUM、MEDIAN、PERCENTILE 和 LARGE）。 AGGREGATE 具有选项来忽略隐藏行或筛选掉的行、错误值和嵌套的 SUBTOTAL 和 AGGREGATE 函数。

避免使用 DFunctions

DSUM、DCOUNT、DAVERAGE 等 DFunctions 的速度比等效的数组公式快得多。 DFunctions 的缺点是条件必须在一个单独的区域内，这使得在许多情况下使用和维护它们都不切实际。从 Excel 2007 开始，应使用 SUMIFS、COUNTIFS 和 AVERAGEIFS 函数而不是 DFunctions。

创建速度更快的 VBA 宏

使用以下提示创建更快的 VBA 宏。

代码运行时禁用除必要功能之外的所有其他功能

为了提高 VBA 宏的性能，在执行代码时显式关闭不需要的功能。通常，在代码运行后进行一次重新计算或一次重绘就足够了，并且可以提高性能。在代码执行之后，将功能恢复到其原始状态。

在执行 VBA 宏时，通常可以禁用以下功能：

Application.ScreenUpdating 关闭屏幕更新。如果将 Application.ScreenUpdating 设置为 False，Excel 不会重绘屏幕。在代码运行时，屏幕会快速更新，通常用户不需要查看每个更新。在代码执行之后更新一次屏幕，可以提高性能。
Application.DisplayStatusBar 关闭状态栏。如果将 Application.DisplayStatusBar 设置为 False，Excel 将不显示状态栏。状态栏设置与屏幕更新设置是分开的，这样即使屏幕没有更新，也可以显示当前操作的状态。但是，如果不需要显示每个操作的状态，则在代码运行时关闭状态栏也会提高性能。
Application.Calculation 切换到手动计算。如果将 Application.Calculation 设置为 xlCalculationManual，则 Excel 仅在用户显式启动计算时才计算工作簿。在自动计算模式下，Excel 决定何时进行计算。例如，每当与公式相关的单元格值发生变化时，Excel 都会重新计算该公式。如果将计算模式切换到手动，则可以等到与公式相关的所有单元格更新后再重新计算工作簿。通过在代码运行时仅在必要的情况下重新计算工作簿，可以提高性能。
Application.EnableEvents 禁用事件。如果将 Application.EnableEvents 设置为 False，Excel 将不引发事件。如果有加载项在侦听 Excel 事件，这些加载项在记录事件时将消耗计算机上的资源。如果加载项没有必要记录代码运行时发生的事件，则关闭事件可以提高性能。
ActiveSheet.DisplayPageBreaks 禁用分页符。如果将 ActiveSheet.DisplayPageBreaks 设置为 False，Excel 将不显示分页符。无需在代码运行时重新计算分页符，执行代码后计算分页符可以提高性能。

重要

请记住，在执行代码后将此功能恢复到其原始状态。

下面的示例展示了在执行 VBA 宏时可以禁用的功能。

  ' Save the current state of Excel settings.
  screenUpdateState = Application.ScreenUpdating
  statusBarState = Application.DisplayStatusBar
  calcState = Application.Calculation
  eventsState = Application.EnableEvents
  ' Note: this is a sheet-level setting.
  displayPageBreakState = ActiveSheet.DisplayPageBreaks 

  ' Turn off Excel functionality to improve performance.
  Application.ScreenUpdating = False
  Application.DisplayStatusBar = False
  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False
  ' Note: this is a sheet-level setting.
  ActiveSheet.DisplayPageBreaks = False

  ' Insert your code here.

  ' Restore Excel settings to original state.
  Application.ScreenUpdating = screenUpdateState
  Application.DisplayStatusBar = statusBarState
  Application.Calculation = calcState
  Application.EnableEvents = eventsState
  ' Note: this is a sheet-level setting
  ActiveSheet.DisplayPageBreaks = displayPageBreaksState

在单个操作中读写大型数据块

通过显式减少数据在 Excel 和代码之间传输的次数来优化代码。与其一次循环单元格来获取或设置一个值，不如在一行中获取或设置整个单元格区域内的值，根据需要使用包含二维数组的变体来存储值。下面的代码示例比较这两个方法。

以下代码示例显示了未优化的代码，它以一次一个的方式循环遍历单元格来获取和设置单元格 A1:C10000 的值。这些单元格不包含公式。

  Dim DataRange as Range
  Dim Irow as Long
  Dim Icol as Integer 
  Dim MyVar as Double 
  Set DataRange=Range("A1:C10000") 

  For Irow=1 to 10000 
      For icol=1 to 3
          ' Read the values from the Excel grid 30,000 times.
          MyVar=DataRange(Irow,Icol) 
          If MyVar > 0 then 
              ' Change the value.
              MyVar=MyVar*Myvar 
              ' Write the values back into the Excel grid 30,000 times.
              DataRange(Irow,Icol)=MyVar
          End If 
      Next Icol 
  Next Irow

以下代码示例显示了优化后的代码，它以一次全部的方式使用数组来获取和设置单元格 A1:C10000 的值。这些单元格不包含公式。

  Dim DataRange As Variant
  Dim Irow As Long 
  Dim Icol As Integer 
  Dim MyVar As Double 
  ' Read all the values at once from the Excel grid and put them into an array.
  DataRange = Range("A1:C10000").Value2 

  For Irow = 1 To 10000 
      For Icol = 1 To 3 
          MyVar = DataRange(Irow, Icol) 
          If MyVar > 0 Then 
              ' Change the values in the array.
              MyVar=MyVar*Myvar 
              DataRange(Irow, Icol) = MyVar 
          End If 
      Next Icol 
  Next Irow 
  ' Write all the values back into the range at once.
  Range("A1:C10000").Value2 = DataRange

从 Excel 区域读取数据时使用 .Value2 而不是 .Value 或 .Text

.Text 返回单元格的格式化值。速度较慢，如果用户缩放，可能返回 ###，并可能丢失精度。
.Value 在区域被格式化为日期或货币的情况下，返回 VBA 货币变量或 VBA 日期变量。速度较慢，可能会丢失精度，并且在调用工作表函数时可能导致错误。
.Value2 速度快，不会改变正在从 Excel 检索的数据。

避免选择并激活对象

选择和激活对象的处理过程比直接引用对象更为密集。通过直接引用 Range 或 Shape 等对象，可以提高性能。下面的代码示例比较了这两种方法。

以下代码示例显示未优化的代码，它选择活动工作表中的每个形状并将文本更改为“Hello”。

  For i = 0 To ActiveSheet.Shapes.Count
      ActiveSheet.Shapes(i).Select
      Selection.Text = "Hello"
  Next i

以下代码示例显示优化后的代码，它直接引用每个形状并将文本更改为“Hello”。

  For i = 0 To ActiveSheet.Shapes.Count
      ActiveSheet.Shapes(i).TextEffect.Text = "Hello"
  Next i

使用这些额外的 VBA 性能优化

下面是可以在 VBA 代码中使用的额外性能优化列表：

通过将数组直接分配给 Range 来返回结果。
使用显式类型声明变量，以避免在代码执行期间确定数据类型的开销（可能在一个循环中进行多次）。
对于在代码中频繁使用的简单函数，请自己在 VBA 中实现这些函数而不是使用 WorksheetFunction 对象。有关详细信息，请参阅使用速度更快的 VBA 用户定义函数。
使用 Range.SpecialCells 方法缩小与代码交互的单元格数量。
使用 XLL SDK 中的 C API 实现功能时，请考虑性能提升。有关详细信息，请参阅 Excel 2010 XLL SDK 文档。

请考虑 Excel 文件格式的性能和大小

与早期版本相比，从 Excel 2007 开始，Excel 包含各种各样的文件格式。忽略宏、模板、加载项、PDF 和 XPS 文件格式变体，三种主要格式是 XLS、XLSB 和 XLSX。

XLS 格式

XLS 格式与早期版本的格式相同。使用这种格式时，限制为 256 列和 65,536 行。当你以 XLS 格式保存 Excel 2007 或 Excel 2010 工作簿时，Excel 将运行兼容性检查。文件大小几乎与早期版本相同（可能会存储一些其他信息），性能略慢于早期版本。 Excel 按照单元格计算顺序执行的任何多线程优化都不会以 XLS 格式保存。因此，在以 XLS 格式保存工作簿、关闭和重新打开工作簿之后，工作簿的计算速度可能会变慢。
XLSB 格式

XLSB 是在 Excel 2007 中引入的二进制格式。它结构为包含许多二进制文件的压缩文件夹。它比 XLS 格式紧凑得多，但压缩量取决于工作簿的内容。例如，10 个工作簿显示从 2 到 8 的大小缩减系数，平均缩减系数为 4。从 Excel 2007 开始，打开和保存性能只比 XLS 格式稍慢。
XLSX 格式

XLSX 是 Excel 2007 中引入的 XML 格式，也是 Excel 2007 中开始采用的默认格式。 XLSX 格式是一个包含许多 XML 文件的压缩文件夹（如果将文件扩展名更改为 .zip，则可以打开压缩文件夹并检查其内容）。通常，XLSX 格式创建的文件比 XLSB 格式的文件大（平均为 XLSB 格式的 1.5 倍），但它们仍明显小于 XLS 文件。打开和保存的时间应比 XLSB 文件稍微长一些。

打开、关闭和保存工作簿

你可能会发现打开、关闭和保存工作簿要比计算它们慢得多。有时这只是因为工作簿较大，但也可能有其他原因。

如果您的一个或多个工作簿的打开和关闭速度比正常速度慢，则可能是由以下问题之一导致的。

临时文件

临时文件可以累积到 Windows 95、Windows 98 和 Windows ME) 中的 \Windows\Temp 目录 (，或者 Windows 2000 和 Windows XP) 中的 \Documents and Settings\User Name\Local Settings\Temp 目录 (。 Excel 为工作簿和打开的工作簿所使用的控件创建这些文件。软件安装程序也会创建临时文件。如果 Excel 因任何原因而停止响应，则可能需要删除这些文件。

临时文件太多可能会导致问题，因此应不时地清理它们。然而，如果安装的软件需要重启计算机，但尚未重启，则应该先重启再删除临时文件。

打开临时目录的一种简单的方法是从 Windows“开始”菜单打开：单击“开始”，然后单击“运行”。在文本框中，键入 %temp%，然后单击“确定”。

跟踪共享工作簿中的更改

跟踪共享工作簿中的更改会导致工作簿文件大小迅速增加。
零碎的交换文件

确保您的 Windows 交换文件所在的磁盘具有大量空间并且定期整理磁盘碎片。
具有密码保护结构的工作簿

结构受密码保护的工作簿 (“工具 ”菜单 >“保护>工作簿> ”输入可选密码) 打开和关闭比没有可选密码保护的工作簿慢得多。
已用区域问题

过大的已用区域可能会导致打开速度变慢以及文件大小增加，在由具有非标准高度或宽度的隐藏行或列导致已用区域过大时尤其如此。有关已用区域问题的详细信息，请参阅最大程度地减小已用区域。
工作表上的大量控件

工作表上的大量控件（复选框、超链接等）可能会减慢打开工作簿的速度，因为使用的临时文件数量太多。这还可能导致在 WAN（甚至 LAN）上打开或保存工作簿出现问题。如果遇到此问题，应考虑重新设计工作簿。
大量指向其他工作簿的链接

如果可能，在打开包含链接的工作簿之前，先打开要链接的工作簿。通常，打开工作簿比从关闭的工作簿中读取链接要快。
病毒扫描程序设置

某些病毒扫描仪设置可能会导致打开、关闭或保存问题或速度变慢，特别是在服务器上。如果你认为这可能是问题所在，请尝试暂时关闭病毒扫描程序。
计算缓慢导致打开和保存缓慢

某些情况下，Excel 会在打开或保存工作簿时对其进行重新计算。如果工作簿的计算时间很长并导致问题，请确保将计算设置为“手动”，并在保存 选项之前考虑关闭 计算（“工具”>“选项”>“计算”）。
工具栏文件 (.xlb)

检查工具栏文件的大小。一个的典型工具栏文件在 10 KB 和 20 KB 之间。可以通过使用 Windows Search 搜索 *.xlb 来找到 XLB 文件。每个用户都有唯一的 XLB 文件。添加、更改或自定义工具栏会增加 toolbar.xlb 文件的大小。删除该文件将删除所有工具栏自定义项（将其重命名为“toolbar.OLD”更安全）。下次打开 Excel 时将创建一个新的 XLB 文件。

进行额外的性能优化

可以在以下区域进行性能改进。

数据透视表

数据透视表提供汇总大量数据的有效方式。
- 总计作为最终结果。如果需要在工作簿的最终结果中生成总计和小计，请尝试使用数据透视表。
- 总计作为中间结果。数据透视表是生成摘要报告的重要方法，但要尽量避免在计算链中创建使用数据透视表结果作为中间总计和小计的公式，除非您可以确保以下条件：
- 在计算过程中已正确刷新数据透视表。
- 数据透视表没有更改，因此信息仍然可见。
如果仍想使用数据透视表作为中间结果，请使用 GETPIVOTDATA 函数。
条件格式和数据验证

条件格式和数据验证非常好，但是大量使用会显著降低计算速度。如果显示单元格，则在每次计算时以及刷新包含该条件格式的单元格的显示时计算每个条件格式公式。 Excel 对象模型具有 Worksheet.EnableFormatConditionsCalculation 属性，因此可以启用或禁用条件格式计算。
已定义名称

已定义名称是 Excel 中最强大的功能之一，但它们确实需要额外的计算时间。使用引用其他工作表的名称会增加计算过程的复杂程度。此外，应尽量避免嵌套名称（引用其他名称的名称）。

因为每次计算引用名称的公式时都会计算名称，所以应避免将计算密集型公式或函数放在定义名称中。在这些情况下，将计算密集型公式或函数放在空闲单元格中，并直接或通过名称引用该单元格，速度会快得多。
仅偶尔使用的公式

许多工作簿包含大量公式和查找，它们涉及将输入数据转换成适当的计算形状，或者被用作防止数据大小或形状变化的措施。如果拥有仅偶尔使用的一组公式，可以复制和粘贴特殊值来临时消除公式，也可以将它们放在一个单独的、很少打开的工作簿中。由于工作表错误通常是由于没有注意到公式已转换为值而引起的，因此最好使用单独的工作簿方法。
使用足够的内存

32 位版本的 Excel 可以使用最多 2 GB 的 RAM，也可以使用最多 4 GB 的 RAM 来支持 Large Address Aware 32 位版本的 Excel 2013 和 2016。然而，运行 Excel 的计算机同样需要内存资源。因此，如果计算机上只有 2 GB 的 RAM，Excel 就不能充分利用这 2 GB 的内存，因为一部分内存分配给了操作系统和其他正在运行的程序。要在 32 位计算机上优化 Excel 的性能，建议计算机至少具有 3 GB RAM。

64 位版本的 Excel 没有 2 GB 或最多 4 GB 的限制。有关详细信息，请参阅 Excel 性能：性能和限制提升中的“大数据集和 64 位版本的 Excel”部分。

结论

本文介绍优化 Excel 功能（如链接、查找、公式、函数和 VBA 代码）以避免常见障碍和提高性能的方法。

另请参阅

支持和反馈

有关于 Office VBA 或本文档的疑问或反馈？请参阅 Office VBA 支持和反馈，获取有关如何接收支持和提供反馈的指南。