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

我们对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”模板文件