我们对Excel的直观感受就是公式一多,Excel计算速度就会很慢。但是实际上并不是这样的。Excel中很多公式并不必然导致计算速度变慢,让计算速度变慢的原因是你对公式的选择以及公式的写法。 上一篇我们为大家分析了常见的求和(计数)公式的效率分析。我们了解到,尽管不同的函数对计算效率有影响,它们的计算速度有时相差很大,但是最终对计算速度提升最大的,还是我们需要根据一开始我们揭示的秘密原则来设计的公式。 今天,我们为大家分析常见的查找公式的计算效率。 我们这一系列文章都是基于这个工具进行的。这个工具是用VBA写的,你可以通过下面的方式获得这个工具:
常见的查找公式及计算速度比较 这是我们的示例数据(大约有4万行),其中前面6列是我们将来用于查找的列,为了 简单和可操作,我们加了一个辅助列:helper,这个列就是前面6列中间用“-”连接起来,最后一列是查找中需要返回的结果。 而我们设定的查找场景也很简单: 1. 普通的VLOOKUP公式 =VLOOKUP(B6&"-"&C6&"-"&D6&"-"&E6&"-"&F6&"-"&G6,数据!$G$2:$H$42942,2,0) 你可能认为还可以,对吧。如果你看过这一系列前面的文章,你就会发现这个时间实际上是个不可接受的时间!。 为了比较,我们在查找场景中也加了一个辅助列,我们再用这个辅助列试试: =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)) 其实差不多。所有从计算效率上来看,Index+Match并没有什么太大优势。 3. SUMIFS =SUMIFS(数据!$H$2:$H$42942,数据!$G$2:$G$42942,Index!H6) 这次的计算时间是1.14秒: 优化查找公式的方法 从这个精确匹配的工作方式中我们可以得到两条提示:
我们按照这一列进行降序排列: 这样,我们需要查找的那些值就排在了数据区域的前面。此时的计算时间是0.005秒: 真是一个令人激动的数字啊! 如果我们按照这个辅助列进行升序排列,将这些值排在数据区域的后面,那么计算时间就变成0.04秒: 从上图可以看出,即使数据量到了一千万,近似查找也只需要最多比较16次就可以得到结果了。 在Excel中,Vlookup,Match都提供了近似匹配的用法,Lookup更是只提供了近似匹配的用法。 1. Vlookup近似匹配 然后使用下面的公式: =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,否则就返回空字符串。 这个公式的计算时间是: 2. Index+Match近似匹配 =INDEX(数据!$H$2:$H$42942,MATCH(Index!H6,数据!$G$2:$G$42942,1)) 其中,Match的最后一个参数从0变成了1。这个公式的计算时间是: 3. Lookup =LOOKUP(H6,数据!$G$2:$G$42942,数据!$H$2:$H$42942) 这个公式的计算时间为: 总结 对于查找场景来说,选择那个函数并不重要。重要的是需要将匹配方式从精确匹配改为近似匹配,数据量越大,这么做对计算速度的改进越显著。如果不能使用近似匹配,那么可以将要查找的结果行部分转移到数据源区域的前面一部分,也可以显著提高速度。如果能够将他们尽可能放在一起,也会有很好的效果。 取得本文模板文件的方式:
|
|