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函数，可以使命名范围所引用的区域动态扩展和收缩。例如，使用以下公式之一创建定义的名称：

VB

复制
  =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部分存储在单独的单元格或定义的名称中，然后引用动态范围中的单元格或名称来最大程度地减少性能下降：

VB

复制
 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函数的语法。有关详细信息，请参阅WorksheetFunction对象的Match方法。

VB

复制
  MATCH(lookup value, lookup array, matchtype)
当查找数组按升序排序（近似匹配）时，Matchtype=1返回小于或等于查找值的最大匹配项。如果查找数组未按升序排序，MATCH 将返回错误答案。默认选项是近似匹配升序排序。

Matchtype=0请求完全匹配并假设数据未排序。

如果查找数组按降序排序（近似匹配）， Matchtype=-1将返回大于或等于查找值的最小匹配项。

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

VB

复制
  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很容易。以下两个语句返回相同的答案：

VB

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

 INDEX(Data!$A$2:$F$1000,MATCH(A1,$A$1:$A$1000,0),3)
加快查找速度
由于精确匹配查找可能会很慢，因此请考虑以下选项来提高性能：

使用一张工作表。将查找和数据保存在同一张工作表上会更快。

如果可以的话，首先对数据进行排序（排序速度很快），然后使用近似匹配。

当必须使用精确匹配查找时，请将要扫描的单元格范围限制到最小。使用表和结构化引用或动态范围名称，而不是引用大量行或列。有时您可以预先计算查找的范围下限和上限。

使用两次查找来查找缺失值的排序数据
对于多行查找，两个近似匹配比一个精确匹配要快得多。（盈亏平衡点约为 10-20 行。）

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

VB

复制
  IF(VLOOKUP(lookup_val ,lookup_array,1,True)=lookup_val, _
      VLOOKUP(lookup_val, lookup_array, column, True), "notexist")
公式的第一部分通过对查找列本身进行近似查找来工作。

VB

复制
  VLOOKUP(lookup_val ,lookup_array,1,True)
您可以使用以下公式检查查找列中的答案是否与查找值相同（在这种情况下，您有完全匹配）：

VB

复制
  IF(VLOOKUP(lookup_val ,lookup_array,1,True)=lookup_val,
如果此公式返回 True，则您已找到完全匹配，因此您可以再次进行近似查找，但这次从您想要的列返回答案。

VB

复制
  VLOOKUP(lookup_val, lookup_array, column, True)
如果查找列中的答案与查找值不匹配，则表示存在缺失值，并且公式将返回“notexist”。

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

对具有缺失值的未排序数据使用 IFERROR 函数
如果必须对未排序的数据使用精确匹配查找，并且无法确定查找值是否存在，则通常必须处理未找到匹配项时返回的#N/A。从 Excel 2007 开始，您可以使用IFERROR函数，该函数既简单又快速。

VB

复制
  IF IFERROR(VLOOKUP(lookupval, table, 2 FALSE),0)
在早期版本中，一种简单但缓慢的方法是使用包含两次查找的IF函数。

VB

复制
  IF(ISNA(VLOOKUP(lookupval,table,2,FALSE)),0,_
      VLOOKUP(lookupval,table,2,FALSE))
如果您使用精确MATCH一次，将结果存储在单元格中，然后在执行INDEX之前测试结果，则可以避免双重精确查找。

VB

复制
  In A1 =MATCH(lookupvalue,lookuparray,0)
  In B1 =IF(ISNA(A1),0,INDEX(tablearray,A1,column))
如果无法使用两个单元格，请使用COUNTIF。它通常比精确匹配查找更快。

VB

复制
  IF (COUNTIF(lookuparray,lookupvalue)=0, 0, _
      VLOOKUP(lookupval, table, 2 FALSE))
使用 MATCH 和 INDEX 在多列上进行精确匹配查找
您通常可以多次重复使用存储的精确匹配。例如，如果您要对多个结果列进行精确查找，则可以通过使用一个MATCH和多个INDEX语句而不是多个VLOOKUP语句来节省时间。

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

VB

复制
  INDEX(Lookup_Range,stored_row,column_number)
或者，您可以在数组公式中使用VLOOKUP 。（数组公式必须使用 Ctrl+-Shift+Enter 输入。Excel 将添加 { 和 } 以表明这是一个数组公式）。

VB

复制
  {VLOOKUP(lookupvalue,{4,2},FALSE)}
对一组连续的行或列使用 INDEX
您还可以从一次查找操作返回多个单元格。要查找多个连续列，可以在数组公式中使用INDEX函数一次返回多列（使用 0 作为列号）。您还可以使用INDEX函数一次返回多行。

VB

复制
  {INDEX($A$1:$J$1000,stored_row,0)}
这将从先前MATCH语句创建的存储行中将 A 列返回到 J 列。

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

使用 MATCH 和 INDEX 进行二维查找
通过使用带有两个嵌入式 MATCH 函数（一个用于行，一个用于列）的 INDEX 函数，对表的行和列进行单独查找，可以有效地执行二维表查找。

使用子集范围进行多索引查找
在大型工作表中，您可能经常需要使用多个索引进行查找，例如查找某个国家/地区的产品数量。为此，您可以连接索引并使用连接的查找值执行查找。然而，由于以下两个原因，这是低效的：

连接字符串是一项计算密集型操作。

查找将覆盖很大的范围。

计算查找的子集范围通常更有效（例如，通过查找国家/地区的第一行和最后一行，然后查找该子集范围内的产品）。

考虑三维查找的选项
除了行和列之外，要查找要使用的表格，还可以使用以下技巧，重点介绍如何让 Excel 查找或选择表格。

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

使用CHOOSE和范围名称可能是一种有效的方法。CHOOSE不是易失性的，但它最适合数量相对较少的表。此示例动态地用于TableLookup_Value选择TableName1, TableName2, ...用于查找表的范围名称 ( )。

VB

复制
  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是一个易失性单线程函数，因此即使没有数据发生更改，每次计算时也会单线程计算查找。使用这种方法很慢。

VB

复制
  INDEX(INDIRECT("Sheet" & TableLookup_Value & "!$B$2:$Z$1000"), _
  MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
您还可以使用VLOOKUP函数查找工作表的名称或用于表的文本字符串，然后使用INDIRECT函数将结果文本转换为范围。

VB

复制
  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用于多条件数组公式，如下所示：

VB

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

请注意，条件表达式中使用的范围或数组的大小和形状以及求和范围必须相同，并且它们不能包含整个列。

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

VB

复制
  SUMPRODUCT((Condition1)*(Condition2)*RangetoSum)
这通常比使用逗号语法稍慢，并且如果求和的范围包含文本值，则会出现错误。然而，它稍微更灵活，因为求和的范围可能有多个列，例如，当条件只有一列时。

使用 SUMPRODUCT 对范围和数组进行乘法和加法
在加权平均计算等情况下，您需要将一个数字范围乘以另一个数字范围并对结果求和，使用SUMPRODUCT的逗号语法可以比数组输入的SUM快 20% 到 25% 。

VB

复制
  {=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 可以是多线程的
语音
使用“格式”或“地址”参数时的 CELL
间接
获取枢轴数据
立方体会员
立方体值
立方体成员属性
立方体集
CUBER排名会员
立方体成员
立方集计数
sheet_name给出第五个参数 (the ) 的地址
引用数据透视表的任何数据库函数（DSUM、DAVERAGE 等）
错误类型
超级链接
使用表格来处理范围的函数
对于SUM、SUMIF和SUMIFS等处理范围的函数，计算时间与要求和或计数的已用单元格数量成正比。不检查未使用的单元格，因此整列引用相对有效，但最好确保不包含超出所需数量的已用单元格。使用表格，或计算子集范围或动态范围。

减少易失性函数
易失性函数会减慢重新计算的速度，因为它们会增加每次计算时必须重新计算的公式数量。

您通常可以通过使用INDEX而不是OFFSET以及CHOOSE而不是INDIRECT来减少易失性函数的数量。然而，OFFSET是一个快速函数，通常可以以创造性的方式使用，从而实现快速计算。

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

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

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

确保将对工作表单元格的所有引用都放在用户定义函数的输入参数中，而不是放在用户定义函数的主体中，这样就可以避免不必要地添加Application.Volatile。

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

您可以捕获 F9 并将其重定向到 VBA 计算子例程，如下所示。将此子例程添加到Thisworkbook模块中。

VB

复制
  Private Sub Workbook_Open()
      Application.OnKey "{F9}", "Recalc"
  End Sub
将此子例程添加到标准模块中。

VB

复制
  Sub Recalc()
      Application.Calculate
      MsgBox "hello"
  End Sub
自动化加载项（Excel 2002 及更高版本）中的用户定义函数不会产生 Visual Basic 编辑器开销，因为它们不使用集成编辑器。自动化插件中 Visual Basic 6 用户定义函数的其他性能特征与 VBA 函数类似。

如果您的用户定义函数处理范围中的每个单元格，请将输入声明为范围，将其分配给包含数组的变量，然后对其进行循环。如果要有效地处理整个列引用，则必须创建输入范围的子集，在其与使用范围的交集处将其划分，如本例所示。

VB

复制
  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 传输到用户定义的函数更有效。

VB

复制
  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
如果您的用户定义函数在计算链的早期被调用，则它可以作为未计算的参数传递。在用户定义的函数中，您可以通过对包含公式的空单元格使用以下测试来检测未计算的单元格：

VB

复制
  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 的数字位于 A 列中，并且您希望 B 列包含累积和；您可以执行以下任一操作：

您可以在 B 列中创建一个公式，例如，=SUM($A$1:$A2)并将其向下拖动到您需要的位置。SUM 的起始单元格固定在 A1 中，但由于结束单元格具有相对行引用，因此它会针对每行自动增加。

=$A1您可以在单元格 B1 和单元格 B2 中创建一个公式=$B1+$A2，然后将其向下拖动到您需要的位置。这通过将此行的编号添加到先前的累积SUM来计算累积单元格。

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

计算子集总和
当表有多个排序索引（例如，区域内的站点）时，您通常可以通过动态计算要在SUM或SUMIF函数中使用的行（或列）子集范围的地址来节省大量计算时间。

要计算行或列的子集范围的地址：

计算每个子集块的行数。

将每个块的计数累加起来以确定其起始行。

将OFFSET与起始行和计数一起使用可将子集范围返回到仅覆盖行子集块的SUM或SUMIF 。

对过滤列表使用 SUBTOTAL
使用SUBTOTAL函数对筛选列表进行求和。SUBTOTAL函数很有用，因为与 SUM 不同，它忽略以下内容：

过滤列表产生的隐藏行。从 Excel 2003 开始，您还可以使SUBTOTAL忽略所有隐藏行，而不仅仅是筛选的行。

其他小计功能。

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

避免使用 DFunction
DFunctions DSUM、DCOUNT、DAVERAGE等比等效的数组公式要快得多。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设置为xlCalculationManual，则 Excel 仅在用户显式启动计算时才计算工作簿。在自动计算模式下，Excel 确定何时进行计算。例如，每当与公式相关的单元格值发生更改时，Excel 都会重新计算该公式。如果将计算模式切换为手动，则可以等到与公式关联的所有单元格都更新后再重新计算工作簿。通过在代码运行时仅在必要时重新计算工作簿，您可以提高性能。

Application.EnableEvents关闭事件。如果Application.EnableEvents设置为False，Excel 不会引发事件。如果有正在侦听 Excel 事件的加载项，这些加载项在记录事件时会消耗计算机上的资源。如果加载项不需要记录代码运行时发生的事件，则关闭事件可以提高性能。

ActiveSheet.DisplayPageBreaks关闭分页符。如果ActiveSheet.DisplayPageBreaks设置为False，Excel 不显示分页符。代码运行时无需重新计算分页符，并且在代码执行后计算分页符可提高性能。

 重要的

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

以下示例显示了在执行 VBA 宏时可以关闭的功能。

VB

复制
  ' 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 的值。这些单元格不包含公式。

VB

复制
  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 的值。这些单元格不包含公式。

VB

复制
  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”。

VB

复制
  For i = 0 To ActiveSheet.Shapes.Count
      ActiveSheet.Shapes(i).Select
      Selection.Text = "Hello"
  Next i
以下代码示例显示了直接引用每个 Shape 并将文本更改为“Hello”的优化代码。

VB

复制
  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 格式紧凑得多，但压缩量取决于工作簿的内容。例如，十个工作簿显示的大小缩减系数范围为 2 到 8，平均缩减系数为 4。从 Excel 2007 开始，打开和保存性能仅比 XLS 格式稍慢。

XLSX 格式

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

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

如果一本或多本工作簿的打开和关闭速度比合理的慢，则可能是由以下问题之一引起的。

临时文件

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

太多的临时文件可能会导致问题，因此您应该偶尔清理它们。但是，如果您安装了需要重新启动计算机的软件，而您尚未这样做，则应在删除临时文件之前重新启动。

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

跟踪共享工作簿中的更改

跟踪共享工作簿中的更改会导致工作簿文件大小快速增加。

交换文件碎片

确保您的 Windows 交换文件位于具有大量空间的磁盘上，并且定期对磁盘进行碎片整理。

具有密码保护结构的工作簿

其结构受密码保护的工作簿（“工具”菜单 > “保护” > “保护工作簿”> 输入可选密码）的打开和关闭速度比不受可选密码保护的工作簿慢得多。

使用范围问题

过大的使用范围可能会导致打开速度缓慢和文件大小增加，特别是当它们是由具有非标准高度或宽度的隐藏行或列引起时。有关已用范围问题的更多信息，请参阅最小化已用范围。

工作表上有大量控件

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

大量其他工作簿的链接

如果可能，请先打开要链接到的工作簿，然后再打开包含链接的工作簿。通常，打开工作簿比读取关闭的工作簿中的链接更快。

病毒扫描程序设置

某些病毒扫描程序设置可能会导致打开、关闭或保存出现问题或速度缓慢，尤其是在服务器上。如果您认为这可能是问题所在，请尝试暂时关闭病毒扫描程序。

计算速度慢导致打开和保存速度慢

在某些情况下，Excel 在打开或保存工作簿时会重新计算它。如果工作簿的计算时间较长并导致出现问题，请确保将计算设置为“手动”，并考虑关闭“保存前计算”选项（“工具” > “选项” > “计算”）。

工具栏文件 (.xlb)

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

进行额外的性能优化
您可以在以下方面进行性能改进。

数据透视表

数据透视表提供了一种汇总大量数据的有效方法。

总计为最终结果。如果您需要生成总计和小计作为工作簿最终结果的一部分，请尝试使用数据透视表。

总计作为中间结果。数据透视表是生成汇总报告的好方法，但请尽量避免创建使用数据透视表结果作为计算链中的中间总计和小计的公式，除非您可以确保满足以下条件：

计算过程中数据透视表已正确刷新。

数据透视表尚未更改，因此信息仍然可见。

如果您仍想使用数据透视表作为中间结果，请使用GETPIVOTDATA函数。

条件格式和数据验证

条件格式和数据验证很棒，但使用大量条件格式和数据验证会显着减慢计算速度。如果显示该单元格，则在每次计算时以及刷新包含条件格式的单元格的显示时，都会对每个条件格式公式进行求值。Excel 对象模型具有Worksheet.EnableFormatConditionsCalculation属性，以便您可以启用或禁用条件格式的计算。

定义的名称

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

由于每次计算引用名称的公式时都会计算名称，因此应避免将计算密集型公式或函数放在定义的名称中。在这些情况下，将计算密集型公式或函数放在某处的备用单元格中并直接或通过使用名称引用该单元格可以明显更快。

仅偶尔使用的公式

许多工作簿包含大量公式和查找，这些公式和查找涉及将输入数据转换为适合计算的形状，或者用作针对数据大小或形状变化的防御措施。当您有偶尔使用的公式块时，您可以复制并粘贴特殊值以暂时消除公式，或者可以将它们放在一个单独的、很少打开的工作簿中。由于工作表错误通常是由于没有注意到公式已转换为值而引起的，因此单独的工作簿方法可能更可取。

使用足够的内存

32 位版本的 Excel 最多可以使用 2 GB RAM，对于大地址感知 32 位版本的 Excel 2013 和 2016，最多可以使用 4 GB RAM。但是，运行 Excel 的计算机也需要内存资源。因此，如果计算机上只有 2 GB RAM，Excel 无法充分利用 2 GB，因为一部分内存分配给操作系统和正在运行的其他程序。为了优化 Excel 在 32 位计算机上的性能，我们建议计算机至少具有 3 GB RAM。

Excel performance - Tips for optimizing performance obstructions | Microsoft Learn