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

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

分享到:

收藏收藏人气：1人

版权说明：原创作品，禁止转载。

编按：

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

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

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

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

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

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

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

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

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

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

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

1.遍历法

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

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

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

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

2.二分法

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

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

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

举个例子来说：

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

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

最后查找数字10和99，结果都是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,)。