分享

Excel表格为什么那么慢以及怎么解决(三)查找公式的效率分析

 ExcelEasy 2021-01-21

我们对Excel的直观感受就是公式一多,Excel计算速度就会很慢。但是实际上并不是这样的。Excel中很多公式并不必然导致计算速度变慢,让计算速度变慢的原因是你对公式的选择以及公式的写法。

上一篇我们为大家分析了常见的求和(计数)公式的效率分析。我们了解到,尽管不同的函数对计算效率有影响,它们的计算速度有时相差很大,但是最终对计算速度提升最大的,还是我们需要根据一开始我们揭示的秘密原则来设计的公式。

今天,我们为大家分析常见的查找公式的计算效率。

我们这一系列文章都是基于这个工具进行的。这个工具是用VBA写的,你可以通过下面的方式获得这个工具:

  1. 关注本公众号:ExcelEasy
  2. 回复:计算速度分析工具

常见的查找公式及计算速度比较


我们先来看数据和场景:

这是我们的示例数据(大约有4万行),其中前面6列是我们将来用于查找的列,为了 简单和可操作,我们加了一个辅助列:helper,这个列就是前面6列中间用“-”连接起来,最后一列是查找中需要返回的结果。

而我们设定的查找场景也很简单:

可以看到,我们给定了前6列的值,需要查找对应的源数据中的数值。我们设定了100行这样的数据。

1. 普通的VLOOKUP公式




我们先来看常用的VLOOKUP函数:

=VLOOKUP(B6&"-"&C6&"-"&D6&"-"&E6&"-"&F6&"-"&G6,数据!$G$2:$H$42942,2,0)

这是一个非常简单的公式,把6列条件用“-”连接起来,然后在源数据表中查找后两列(辅助列和数值)。经过分析,这100个查找公式的计算时间是0.41秒:

你可能认为还可以,对吧。如果你看过这一系列前面的文章,你就会发现这个时间实际上是个不可接受的时间!。

为了比较,我们在查找场景中也加了一个辅助列,我们再用这个辅助列试试:

=VLOOKUP(H6,数据!$G$2:$H$42942,2,0)

这回计算时间变成了0.4秒:

跟上次差不多。可见这个字符串的处理的时间可以忽略不计。所以,在下面的公式中,如果没有必要,我就直接用辅助列了。

2. Index+Match




我们再来看很多人比较推崇的Index+Match公式:

=INDEX(数据!$H$2:$H$42942,MATCH(Index!H6,数据!$G$2:$G$42942,0))

这个公式是先用Match匹配条件,然后找到位置,再根据位置用Index函数返回相应结果。它的计算时间是接近0.40秒:

其实差不多。所有从计算效率上来看,Index+Match并没有什么太大优势。

有些朋友可能会记得我们上一篇里讲到的效率最高的公式用到了Match,确实,在设计高效率的公式时Match很常用,但是Match本身并不能保证你的公式效率高!

3. SUMIFS




我们仔细分析查找场景,发现我们需要返回的结果是数值。在这种情况下,如果我们能保证查找结果唯一的话,完全可以使用求和公式进行查找。这里,我们就使用上次介绍的效率比较好的SUMIFS函数来进行比较:

=SUMIFS(数据!$H$2:$H$42942,数据!$G$2:$G$42942,Index!H6)

这次的计算时间是1.14秒:

可见,就查找这个工作来说,用求和公式的速度还是慢了至少一个数量级。如果用数组求和,SUMPRODUCT之类的公式,速度会更慢。

优化查找公式的方法


对查找公式的分析优化非常简单,就是将精确匹配改为近似匹配。这是由这两种匹配的算法决定的。
精确匹配就是在查找区域中逐一与条件进行比较,直到找到一样的,然后返回。这样,如果你源数据有N行的话,平均一条查找时间就是N/2。

从这个精确匹配的工作方式中我们可以得到两条提示:

  1. 第一个提示很直接,就是源数据量越多,我们的平均查找时间越长(有点废话的意思😀),而且这个时间与数据量的关系是线性的。

  2. 第二个提示更有意思,它提示我们如果你将要查找的记录放在源数据的前面,那么查找速度会更快。我们下面看看不同位置下查找速度有多大区别:

首先我们在源数据中添加一个辅助列,用来表示该条记录是否在前面我们的查找场景中出现(也就是要把查找场景中那100行记录挑出来),在这个筛选中为1的那些就是在查找场景中被当作条件的记录。

我们按照这一列进行降序排列:

这样,我们需要查找的那些值就排在了数据区域的前面。此时的计算时间是0.005秒:

真是一个令人激动的数字啊!

如果我们按照这个辅助列进行升序排列,将这些值排在数据区域的后面,那么计算时间就变成0.04秒:

速度大约相差10倍。
但是,速度仍然比我们前面没有排序的时间快多了。是排序造成的吗?
答案不是的。并不是排序造成了这个查找速度变得飞快,而是因为我们选择的排序方式将查找值放在了一起,这使得Excel的查找算法可以利用这一个优势从而得到速度的极大提升。
而近似匹配用的算法是折半查找(折半查找的具体原理就不讲了),在N行源数据里找到一条记录的时间平均是ln(N)。这个时间比N/2强多了,而且源数据量越大,这个算法的时间优势越大:

从上图可以看出,即使数据量到了一千万,近似查找也只需要最多比较16次就可以得到结果了。

在Excel中,Vlookup,Match都提供了近似匹配的用法,Lookup更是只提供了近似匹配的用法。

下面是这些不同的近似匹配的公式的计算时间:

1. Vlookup近似匹配




使用近似匹配,首先必须将源数据按照G列(Helper列)升序排列:

然后使用下面的公式:

=VLOOKUP(H6,数据!$G$2:$H$42942,2,1)

注意最后一个参数从0改成了1。

这个公式的计算时间是:

不到0.01秒。速度的提高了40倍!!!

需要注意的是,使用了近似匹配后,再也不会返回#N/A了!即使找不到匹配结果也不会返回#N/A!这个公式总会给你返回一个结果(有可能不对😲)。此时需要修改这个公式使它在找不到结果的时候能够返回空字符串。这时可以使用两遍Vlookup:

=IF(VLOOKUP(H6,数据!$G$2:$H$42942,1,1)=H6,VLOOKUP(H6,

数据!$G$2:$H$42942,2,1),"")

第一个Vlookup是判断是否找到了正确的结果,如果是,就返回第二个Vlookup,否则就返回空字符串。

这个公式的计算时间是:

仍然比精确匹配提高了接近40倍。(如果现在再分析精确匹配的公式的计算时间,会有不小的变化)

2. Index+Match近似匹配




Index+Match的近似匹配的公式如下:

=INDEX(数据!$H$2:$H$42942,MATCH(Index!H6,数据!$G$2:$G$42942,1))

其中,Match的最后一个参数从0变成了1。这个公式的计算时间是:

跟Vlookup近似匹配的时间差不多。

3. Lookup




Lookup只有近似匹配的用法:

=LOOKUP(H6,数据!$G$2:$G$42942,数据!$H$2:$H$42942)

这个公式的计算时间为:

与前两个相比,也不占优势。

总结


对于查找场景来说,选择那个函数并不重要。重要的是需要将匹配方式从精确匹配改为近似匹配,数据量越大,这么做对计算速度的改进越显著。如果不能使用近似匹配,那么可以将要查找的结果行部分转移到数据源区域的前面一部分,也可以显著提高速度。如果能够将他们尽可能放在一起,也会有很好的效果。

取得本文模板文件的方式:

  1. 关注本公众号

  2. 点击底部菜单“联系客服”,与客服取得联系,索取“查找公式的计算效率分析3”模板文件

    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多