设置主页 | 收藏本站 Excel学习网-公益型的EXCEL在线学习网站,助您轻松办公!

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

2019年10月10日 20:47 发布来源:Excel学习网
我在“挥发性工作簿”中讨论了报告工作簿,以测试Excel查找方法的计算时间。该工作簿包含一个包含五列的报告,每列包含1,000行公式。每个公式从50,000行数据库返回数据。
 
为了对计算进行计时,我依靠CalcTimer.xls工作簿,该工作簿在使用Excel VBA测试报告计算时间中进行了讨论。
 
 
我在运行1.60 GHz的旧笔记本电脑上进行了这些测试。您的结果可能会更快,但大约与我的成正比。
 
计算间接费用时间
 
Excel需要花费一定的时间来显示A列中显示的随机选择的代码。尽管该时间对于所有测试都是一致的,但它可能会扭曲两个测试之间的比率。
 

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

因此,我首先测试了在不使用任何查找公式的情况下计算25次所需的时间。以下所有“净计算时间”值均减去以下计算时间。
 
计算时间:0.141秒。
 
使用未排序数据的Excel查找时间
 
VLOOKUP和INDEX-MATCH均可用于未排序的数据。在以下摘要中,我提供了“关键公式”。在所有情况下,请根据需要将它们复制到报告的其余区域。
 
因此,让我们看看这些查找方法是如何执行的。
 
VLOOKUP,未分类的数据
 

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

此试用版中的公式依赖于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,未分类的数据
 

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

此试验中的公式同时使用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,未排序的数据
 

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

 
与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,排序数据
 

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

 
该试验的关键公式简短易懂,可以用两种方式编写:
 
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,排序数据
 

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

 
此版本的密钥公式可以用两种方式编写:
 
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,已排序的数据
 

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

 
最后,该试验对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学习网 [ http://www.excelcn.com/]
需要保留本文链接地址:http://www.excelcn.com/jiqiao/10658.html
分享到:
网站地图 | XML地图 | 免责声明 | 关于我们 | Excel学习网:优秀的EXCEL在线学习和资源分享网站。
版权所有: CopyRight © 2013-2018 www.excelcn.com All Rights Reserved.
豫ICP备12002644号