快捷找到所需要的Excel函数-Excel最快的查找方法--英雄云拓展知识分享

我在“挥发性工作簿”中讨论了报告工作簿，以测试Excel查找方法的计算时间。该工作簿包括一个包括5列的报告，每列包括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参数是可选的。它可以具有3个值：0：完全匹配。可以用于未排序的数据。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⑵⑶和Excel的初期，我们被告知，当公式援用公式上方和左边的单元格时，公式将更快地进行计算。因此，这种情况下，如果您是像我这样的老朋友，您可能想知道，如果将上图中的“行”列移到“数据1”列的左边，此报告是不是可以更快地计算出。我尝试了该设计，并且计算时间完全没有遭到作用。从下面的计算时间可以看出，该设计代表了未排序数据的最好实践。由于我们使用的是前两类方法使用的查找公式的数量的5分之一，所以我们的计算时间大概是原来的5分之一。净计算时间：52.234使用排序数据的Excel查找时间当VLOOKUP和MATCH处理排序的数据时，可以将它们设置为使用2进制搜索方法，该方法比上面讨论的方法快很多。令人遗憾的是，此方法返回两个函数的近似匹配。这很不幸，由于针对我的经验，大多数查找都需要完全匹配。也就是说，如果我们寻觅数据中不存在的值，则需要使用公式来返回毛病值。我们不希望他们返回最好猜想。荣幸的是，有一种简单的方法可以解决此问题，以下所示。虽然该解决方案的计算时间几近增加了一倍，但提高的精度值得不菲的代价。VLOOKUP，排序数据

该实验的关键公式简短易懂，可以用两类方式编写：B3：= VLOOKUP（$ A3，数据，B $ 1）B3：= VLOOKUP（$ A3，数据，B $ 1，TRUE）注意，经过对数据进行排序并使用2进制搜索技术（由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函数。