快速找到所需要的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函数。
猜你喜欢
- 点击文件--页面设置然后点击页边距选项卡,选择装订线位置为“左”,装订线宽为40毫米。然后选择页眉和页脚:勾选奇偶页不同。点击确定,我把装订
- 说起Word的文档,就不得不说文本框,在Word较早的版本中文本框的功能比较单调,而Word2007就加入不少的美化功能,并且在样式库里可以
- 电脑连接打印机后需要下载安装打印机驱动才能正常运行,很多小伙伴还不知道Win10打印机驱动怎么下载?小编给大家带来Win10安装打印机驱动教
- 单元格:直接输入文字,再插入——直线——划直线——移动至单元格上。选直线——右键——设置自选图形格式——属性——大小、位置随单元格而变(点选
- Win10系统怎么设置时间显示秒?Win10右下角任务栏虽然能够显示时间,但是不能精确到秒。这个需要修改注册表,大家在操作前,记得做好备份。
- 如何处理win10驱动空间不足的问题?通常,win10的驱动空间不足是由于win10系统的垃圾太多或没有删除无用的文件安装包造成的。Win1
- 办公中经常会制作流程图/组织结构图,可以使用插入下的SmartArt,直接制作流程图。今天给大家分享下word组织结构图怎样制作,一起来看看
- 本文教你如何将数据图表添加到Microsoft Word文档中。步骤部分 1在Word中插入图表打开Microsoft Word文
- 如下图1所示,通过Excel图表展现了AI发展的历史。图1这个图表使用的是Excel散点图技术,绘制起来很简单。图表所使用的数据如下图2所示
- 当我们在Word文档中输入了含有日期、时间、人名和长度单位等特殊文本时,将在文本的下方出现紫色虚线,这就是word的智能标记功能,如图1所示
- Word页面布局在哪里?排版的Word文档,打印出来后,边距太大(或太小),怎么办?当前Word文档需要打印到的纸张不是A4纸,怎么办?排版
- 首先要知道.docm文件是启用宏的word文档,我们首先打开.docm文件,如图所示。点击文件位置,如图所示。选择另存为,选择保存位置,如图
- 最近,小伙伴对计算机的win10系统崩溃,无法接通电源进入系统进行修复。我们该怎么办事实上,我们可以下载新的系统并使用u盘进行安装。今天,边
- 新版 Office 2019 的启动画面 PPT 篇:打造 3D 电影级的演示 我们先来谈谈 PPT 2019 中
- Win10游戏模式,这令游戏玩家们很是欣喜。那么如何开启游戏模式呢?打开游戏模式以后真的能提升游戏性能吗?是否真的有用?这都需要实际的测试才
- iOS 14已经发布到iOS 14.7.1了,在所有发布的iOS 14版本中,哪个版本最好用呢?最新发布的iOS 14.7.1会是最好用的那
- 大 家好,我们可能常常会碰到将图片文字转换成word的难题,通常要将图片文字转换成word的图片,有些是扫描的,有些是用相机拍下来的,但不能
- 我讲到的大家都平时好好用一用练一练。这样的话,后期用起来,会非常的快。我在这里只说一下功能好了。具体的使用,大家要好好练习。这不是讲课的重点
- 一般情况下,我们都会推荐新款机型升级最新系统,旧款机型都会保守推荐停留在一个可以养老的稳定的系统版本,但对于新系统的各种新功能,也有不少老款
- 对于迅捷pdf转换成Excel转换器来说,你只需把pdf文件拖拽到软件界面中,然后单击“转换”即可完成转换。转换步骤如下: &