二维码 购物车
部落窝在线教育欢迎您!

等了64个夜晚,VLOOKUP坐字法合并单元格查找的秘密终于破了!

 

作者:老菜鸟来源:部落窝教育发布时间:2019-03-27 09:25:31点击:1178

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

如何在合并的单元格中使用VLOOKUP进行数据查找?在123日的教程大胆合并吧!VLOOKUP坐字法专做单元格合并查找中作者推荐使用VLOOKUP“坐”字法。很多伙伴对这个“坐”字法非常感兴趣,想了解其中的原理。苦等64个夜晚,今天“坐”字法背后的秘密终于浮出了水面。

123日发布的教程中VLOOKUP里出现了一个“坐”字,大家纷纷表示想了解这个“坐”字到底是何意,今天就为大家解释这个公式的原理。


要说这个“坐”的含义,得从VLOOKUP的第四参数说起。

熟悉VLOOKUP的朋友都知道,这个函数一共有四个参数,第四参数的作用就是控制匹配方式,填写1或者TRUE表示近似匹配,通常也叫做模糊匹配:


填写0或者FALSE表示精确匹配:


对于精确匹配,大家都明白,但对于近似匹配,大家就难理解了。我们通过一个例子来看看这两种匹配的区别:


从上图可以看到,在我们的数据区域A列中并没有“人事部”。查找“人事部”,精确查找得到了错误值,而近似查找找到了类似的部门——“人力资源中心”。

难道近似查找这么智能?其实这里找到“人力资源中心”只是一个巧合。在这个数据源中,A列是按照升序排列的,现在我们把排序打乱,再看看查找结果如何:


结果令人大跌眼镜,全乱了,这是什么鬼?

请注意,这并不是公式的错误,之所以得到这样的结果,根本原因是精确查找和近似查找的查找原理不同。精确查找使用的是遍历法,而近似查找使用的是二分法。

以下简单说明这两种查找的原理。

1.遍历法

这种方法是将需要查找的内容,在指定的查找区域中,逐一进行比较,当找到完全一致的内容后,即可得到对应的结果。

例如查找“采购部”,查找区域是A:A,遍历法会从A1单元格开始找,当找到A2时发现目标,返回结果,就完成了第一个数据的查找;接着找“人事部”,还是从A1开始找,找遍A列所有的单元格,也没有发现目标,只能得到#N/A,说明没有找到所需的结果。

由此也可以体会到遍历法的特点:查找准确性高,但需要一个一个对比数据,当数据多的时候,查找速度慢是个很大的问题。

下面再来看看二分法又是如何进行查找的。

2.二分法

与遍历法不同,二分法不逐一比较,并且对数据源做了一种假设,这个假设就是默认数据升序排列

我们知道,升序排列的数据中,小的在上,大的在下。二分法默认数据升序排列,本质就是不管数据实际是如何排序的,都认为上边小,下边大。

二分法对数据进行比较时,不像遍历法那样从上往下逐个做比较,而是从数据源最中间的位置开始进行比较。如果要找的值比中间位置的值大,则向下找,反之则向上找,以此类推。二分法查找的结果要么是小于等于查找内容的值,要么就是错误。

 

举个例子来说:


在这个例子中,数据源有五个数,中间位置的数据是9。当要找的值为2时,首先用2和最中间的9比较,29,所以继续向上找。以此类推,找不到小于或等于2的数据时就会得到#N/A

接着再查找第二个数字99和中间的数据比较,正好相同,所以直接得到正确的结果;

最后查找数字1099,结果都是6。由于二分法默认数据是升序排列,当要找的数据比数据源中所有的值都大时,二分法就会一直向下找,找不到的时候就会返回最后一个数据,所以这里只要查找值大于9,得到的结果都是数据源中的最后一个数据6

 

   关于二分法的查找原理,官方的资料并没有详细介绍,只是提到几个要点:

1)默认数据源是升序排列;

2当查找值比查找区域内的任何值都大时,则返回数据区域内最下面的数据;

3)如果查找值小于查找区域内的最小值时,则会得到错误值#N/A

通过这个例子我们大致可以理解二分法的特点,那就是查找速度快,一次就能排除一半数据;但如果数据源不是升序排列的,结果可能并不是我们所需要的。

汉字之间的大小关系通常是按照拼音的顺序排列的,所以用zuo这个拼音的字,基本都是比较靠后的。在实际使用中,最小的字是“”(ā,也读yā),最大字是“”,“不是汉字但比所有汉字都大。

 

最后再来解释一下VLOOKUP第四参数省略时的两个小细节:

1)只省略第四参数值,不省略逗号时,表示精确查找;

2)如果将逗号和第四参数值全部省略,表示模糊查找。


在这个公式中,外层的VLOOKUP省略第四参数而保留了逗号,表示精确查找;内层的VLOOKUP同时省略逗号和参数,表示模糊查找。

明白了以上原理,用VLOOKUP “坐”字法解决合并单元格的查询就不神秘了。譬如,A2:A5合并后只保留了A2单元格值“财务中心”。查上图刘丙奇的奖金基数,因为A1:A3只有两个值“部门”“财务中心”并都比“坐”字小,所以公式VLOOKUP("",$A$1:A3,1)的结果就是A1:A3 中最下方值“财务中心”,公式VLOOKUP(VLOOKUP("",$A$1:A3,1),$G$1:$H$7,2,)就等于VLOOKUP"财务中心",$G$1:$H$7,2,)

今天的教程就到这了,还有哪些知识是你想了解的可以留言,咱们下期再见。

本文配套的练习课件请加入QQ群:264539405下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

如何在合并单元格中使用VLOOKUP查找大胆合并吧!VLOOKUP坐字法专做单元格合并查找

VLOOKUP常见问题《破除日期迷惑,多条件查找就用Vlookup!》

VLOOKUP找不到数据的原因《你一定要了解:公式没错Vlookup仍找不到数据的3大原因》