首页 > Excel专区 > Excel教程 >

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

Excel教程 2022-03-08 21:49:14

我在“挥发性工作簿”中讨论了报告工作簿,以测试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函数。


标签: 公式数据时间计算excel教程

office教程网 Copyright © 2016-2020 http://www.office9.cn. Some Rights Reserved. 苏ICP备20040415号-9