教程 | Excel性能优化方法（三）

01

INDEX+match/offset

使用 INDEX 和 MATCH 或 OFFSET 而不是 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)

02

SORT的应用

加快查找速度

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

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

03

两个查找

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

对于在数行内执行的查找，两个近似匹配显著快于一个完全匹配。 （分界点是大约 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 来处理此错误，或者向列表添加一个小的测试值。

04

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))

05

Match+Index

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

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

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

INDEX(Lookup_Range,stored_row,column_number)

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

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