快速找到所需要的Excel函数-Excel最快的查找方法-Excel学习网

我在“挥发性工作簿”中讨论了报告工作簿，以测试Excel查找方法的计算时间。该工作簿包含一个包含五列的报告，每列包含1,000行公式。每个公式从50,000行数据库返回数据。

为了对计算进行计时，我依靠CalcTimer.xls工作簿，该工作簿在使用Excel VBA测试报告计算时间中进行了讨论。

我在运行1.60 GHz的旧笔记本电脑上进行了这些测试。您的结果可能会更快，但大约与我的成正比。

计算间接费用时间

Excel需要花费一定的时间来显示A列中显示的随机选择的代码。尽管该时间对于所有测试都是一致的，但它可能会扭曲两个测试之间的比率。

因此，我首先测试了在不使用任何查找公式的情况下计算25次所需的时间。以下所有“净计算时间”值均减去以下计算时间。

计算时间：0.141秒。

使用未排序数据的Excel查找时间

VLOOKUP和INDEX-MATCH均可用于未排序的数据。在以下摘要中，我提供了“关键公式”。在所有情况下，请根据需要将它们复制到报告的其余区域。

因此，让我们看看这些查找方法是如何执行的。

VLOOKUP，未分类的数据

此试用版中的公式依赖于VLOOKUP，其格式如下：

= VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）

range_lookup参数是可选的。如果它的值为FALSE，则VLOOKUP返回一个完全匹配项，可用于未排序的数据。如果为TRUE或省略，则返回近似匹配项，并且必须仅对已排序的数据使用。

关键公式是：

B3：= VLOOKUP（$ A3，Data，B $ 1，FALSE）

下面的净计算时间似乎很长，大约260秒。但是请记住，该报告具有5000个查找公式，我们计算了25次。因此，这125,000次查询中的每一次仅花费大约.0011秒。

净计算时间：260.781

在一式中进行INDEX-MATCH，未分类的数据

此试验中的公式同时使用INDEX和MATCH：

= INDEX（参考，row_num，column_num，area_num）

= MATCH（lookup_value，lookup_array，match_type）

MATCH的match_type参数是可选的。它可以具有三个值：

0：完全匹配。可以用于未排序的数据。

1：近似匹配。（默认值。）必须用于按升序排序的数据。

-1：近似匹配。必须用于按降序排序的数据。

INDEX和MATCH函数可以在一个公式中或在单独的公式中使用。该测试使用了一个公式：

B3：= INDEX（Data，MATCH（$ A3，Code，0），B $ 1）

请注意，以下净计算时间与VLOOKUP大致相同。

净计算时间：253.453

两个公式中的INDEX-MATCH，未排序的数据

与VLOOKUP不同，INDEX-MATCH方法可以分为两个公式。如图中所示，当几个公式需要从数据库的同一行或同一列返回数据时，此功能非常有用。通过这种设计，一个MATCH公式可以完成较慢的工作，而任何INDEX公式都可以进行较快的工作。这大大加快了计算速度。

以下是主要公式：

B3：= INDEX（数据，$ G3，B $ 1）

G3：= MATCH（$ A3，代码，0）

在Lotus 1-2-3和Excel的早期，我们被告知，当公式引用公式上方和左侧的单元格时，公式将更快地进行计算。因此，如果您是像我这样的老朋友，您可能想知道，如果将上图中的“行”列移到“数据1”列的左侧，此报告是否可以更快地计算出。我尝试了该设计，并且计算时间完全没有受到影响。

从下面的计算时间可以看出，该设计代表了未排序数据的最佳实践。因为我们使用的是前两种方法使用的查找公式的数量的五分之一，所以我们的计算时间大约是原来的五分之一。

净计算时间：52.234

使用排序数据的Excel查找时间

当VLOOKUP和MATCH处理排序的数据时，可以将它们设置为使用二进制搜索方法，该方法比上面讨论的方法快得多。

不幸的是，此方法返回两个函数的近似匹配。这很不幸，因为根据我的经验，大多数查找都需要完全匹配。也就是说，如果我们寻找数据中不存在的值，则需要使用公式来返回错误值。我们不希望他们返回最佳猜测。

幸运的是，有一种简单的方法可以解决此问题，如下所示。尽管该解决方案的计算时间几乎增加了一倍，但提高的精度值得不菲的代价。

VLOOKUP，排序数据

该试验的关键公式简短易懂，可以用两种方式编写：

B3：= VLOOKUP（$ A3，数据，B $ 1）

B3：= VLOOKUP（$ A3，数据，B $ 1，TRUE）

注意，通过对数据进行排序并使用二进制搜索技术（由range_lookup参数确定），我们将计算时间从大约260秒减少到大约半秒。

实际上，搜索时间如此之短，以至于如果我们将查找次数加倍，则只会将总计算时间增加约半秒。这使我们可以自由修改搜索公式，以使我们与排序后的数据完全匹配：

B3：= IF（VLOOKUP（$ A3，Data，1）= $ A3，VLOOKUP（$ A3，Data，B $ 1），NA（））

在这里，我们首先查找代码，然后返回找到的代码。如果返回的代码等于原始代码，则我们完全匹配。因此，我们再次查找代码并返回我们实际想要的值。否则，如果没有完全匹配，则返回＃N / A。

近似匹配的

净计算时间：0.594 完全匹配版本的净计算时间：0.781

用一个公式进行INDEX-MATCH，排序数据

此版本的密钥公式可以用两种方式编写：

B3：= INDEX（数据，MATCH（$ A3，代码，1），B $ 1）

B3：= INDEX（数据，MATCH（$ A3，代码），B $ 1）

与VLOOKUP一样，我们可以修改此公式以提供完全匹配的内容：

B3：= IF（INDEX（Code，MATCH（$ A3，Code，1））= $ A3，INDEX（Data，MATCH（$ A3，Code，1），B $ 1），NA（））

近似匹配的

净计算时间：0.453 精确匹配版本的净计算时间：0.688

两个公式中的INDEX-MATCH，已排序的数据

最后，该试验对INDEX和MATCH使用单独的公式：

B3：= INDEX（数据，$ G3，B $ 1）

G3：= MATCH（$ A3，代码，1）

在这里，我们可以修改单元格G3以提供完全匹配：

G3：= IF（INDEX（Code，MATCH（$ A3，Code，1））= $ A3，MATCH（$ A3，Code，1），NA（））

换句话说，对排序后的数据使用两种形式的INDEX-MATCH方法可能比使用VLOOKUP或一种形式的INDEX-MATCH技术要快得多，这是最佳实践。

近似匹配的

净计算时间：0.391 精确匹配的净计算时间：0.438

了解查找测试结果

这是我总结这些结果的方法：

如果要从表的一行返回多个值，请在一个公式中使用MATCH查找该行，然后在其他公式中使用INDEX返回值。这始终是最快的方法。

如果您的数据已排序，请使用上面说明的排序数据版本。这样做可以将计算时间缩短几个数量级。

如果您需要排序数据中的精确匹配，请不要依赖近似匹配。相反，请始终使用双重查找方法来确保Excel实际上已经找到了您的lookup_value。

在最坏的情况下，INDEX-MATCH方法的速度与VLOOKUP差不多。在最好的情况下，速度要快得多。

有关VLOOKUP和INDEX-MATCH的更多信息，请参见：Excel的VLOOKUP与INDEX-MATCH函数。