快速找到所需要的Excel函数-Excel最快的查找方法-
发布时间:2023-09-14 21:24:47
我在“挥发性工作簿”中讨论了报告工作簿,以测试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高端大气上档次的图表—帕累托图制作方法
- 很多时候,为了不让垃圾文件占满磁盘空间,我们总是会不断的删除掉一些文件,然而却每次都会不小心把一些重要文件也给清理掉了,这时候我们就追悔莫及
- 在wps中方框里打钩这个符号是我们平时经常用到的项目标示符,在我们的文档中经常使用这个符号,下面小编就告诉大家怎么在方框中打勾。1、首先我们
- 一键重装系统是现在很多用户在电脑出现问题后使用的首选方法,但是出现失败后却不知道该怎么办了,今天就给你们带来了一键重装系统失败恢复方法,快来
- 在wps表格中,经常会使用筛选功能来处理数据,对于新手来说,可能不知道具体的操作,下面就让小编告诉大家怎样在wps表格中使用筛选功能。在wp
- 合理的字体间距使到我们的文章更整洁美观,那么调整文字间距有什么快速方法吗。以下是辛勤小编为您带来的关于wps调整文字间的间距,希望对您有所帮
- 使用win10系统的用户有很多都不知道怎么去控制应用的大小缩放设置,所以下面就给你们带来了win10控制应用缩放方法,如果你也有需要就快来看
- 有很多计算机用户在玩游戏时会打开垂直同步,但后来发现在玩游戏时总是会出现跳帧的情况。如果要关闭但不知道如何操作,如何关闭win7计算机的垂直
- 在利用Excel制作表格时我们就需要将数据转换成图表,如扇形图或者柱形图,那么要怎么做呢?对于刚从其它版本转型过来的应该就不会太懂吧,没关系
- 在外媒记者曝光微软官方Win10系统U盘谍照后,有用户又曝光了Windows10家庭版升级专业版的系统U盘照片,从照片上看,Win10系统升
- 1、首先在桌面右击鼠标选择新建→选择Microsoft Excel 工作表,新建完成后双击打开工作表,如图所示。2、打开工作表后在A1任意输
- 新手小白如何重装Win10系统?自己要是小白的话建议使用软件自带的一键处理功能,找到自己对应想要的版本然后进行下载重装,整个过程由石大师自动
- 三线表格在今天已经使用的很普及了,掌握wps文字制作三线表的方法,对于工作和学习是很有帮助的,下面就让小编告诉你怎样使用wps文字制作三线表
- 电脑网页游戏打不开怎么办?好多用户反映自己想在电脑上玩网页游戏,但是却出现了打不开的问题,这要怎么办呢,因为很多网页游戏都是用flash制作
- win10计划重启怎么关闭?当我们在win10系统中设置了计划重启之后,不想继续重启该怎么关闭呢,计划重启怎么关闭,将已计划重启任务关闭,下
- 恋爱自古一来 便是人们所 追赶的 热门, 信赖 已经 堕入 恋爱的小 同伴们也深有 领会, 面临 本人的爱人,想把 本人的 内心话说给他听,
- 在word文档中常常会看到某段文字后面有一个头向下的小箭头“↓”,其实这是手动换行符。在打印的时候其实不会被打印出来,但电子排版的时候会影响
- 通常电脑系统出现故障的时候,可以在安全模式下进行一些简单的修复,但是有朋友在进入Win8.1系统安全模式后,因多次输入微软账户密码错误导致无
- 很多朋友不清楚Win7共享文件夹无法访问怎么办?下面小编给大家带来Win7共享文件夹无法访问解决方法,需要的朋友可以参考下在 Windows
- 有这样的需求,想限制表格中数据的编辑,让一些没有权限的朋友不可对单元格数据进行编辑、修改。比如只允许部分单元格的内容可以修改而其他单元格数据