一文讲透LOOKUP二分法原理
作者：逍遥来源：部落窝教育发布时间：2023-04-21 09:43:21点击：3325
编按：
LOOKUP查找为何需要升序排列？VLOOKUP模糊查找为何也需要升序排列？如果不升序排列会有什么后果？今天就给大家详细介绍。这个问题的解答涉及到Excel二分法查找本质。了解本文后，大家就会掌握VLOOKUP或LOOKUP区间查找的做法，就会掌握用坐字法查最后一条文本的做法，就会掌握LOOKUP不排序的精确查找做法。
LOOKUP,作为一个老牌函数，越是难的查找问题，用起来越是得心应手，（参考部落窝官网《VLOOKUP&LOOKUP双雄战》系列）。LOOKUP这么牛，但长久以来很少有人能真正注意到它，甚至在官方的“个人介绍”里，也常被当作其他兄弟VLOOKUP、XLOOKUP的衬托。
究其原因，还是在于LOOKUP复杂的查找逻辑——二分法，吓退了不少新手。
今天就从LOOKUP查找为何需要升序排列讲起，用简单、生动的例子让大家捋明白、学透彻，从此多一个查找利器。
一、LOOKUP查找为何需要升序排列？
LOOKUP查找需要数据升序排列，这是为什么呢？要知道答案，首先就得了解Excel的查找逻辑。Excel中包括两种查找逻辑：遍历法查找和二分法查找。
1. 遍历法查找
遍历法查找就是在指定的数据区域内从上到下逐个把数据拿出来与查找值进行比较的一种查找方法。
VLOOKUP精确查找用的就是遍历法查找。
如下图所示：查找薪资10000的级别。
公式：=VLOOKUP(D2,A2:B14,2,0)
VLOOKUP在A2:A14中进行查找，取第一个数字，2000，不对；3000，也不对；4000，不对……直到第9次取到10000，终于与查找值对上了，然后返回级别I。
遍历法查找，不考虑排序，只机械地从上到下逐一比较即可，很好理解也方便使用。
但是慢！
如果有10000个数据，要查找的值恰好位于第10000位，那就要查找一万次！
有没有快一点的方法？
有，二分法！
2.二分法查找
二分法查找就是逐次把数据分成上下两半（二分），用每个中间值与查找值进行比较查找的方法。中间值就是二分时位于中间位置的数值。每次二分，就把数据区域缩小一半，自然提高了速度。
怎么确定中间位置？
如果查找范围数据个数是单数，中间位置就是（个数+1）÷2；如果数据个数是偶数，中间位置就是（个数）÷2。
LOOKUP查找就是典型的二分法查找。
公式=LOOKUP(D3,A2:A14,B2:B14)
第一次二分：
中间值是8000，小于查找值10000，说明我们要查找的数据在中间值下方的区域。
第二次二分：
中间值是11000，大于查找值10000，说明我们要查找的值是在上方区域。
第三次二分：
中间值是9000，小于查找值10000，说明要向下查。
触底取值：
只有一个值了10000，此时“触底反弹”向上查， 向上区域中第一个数10000与查找值相等作为查找结果值，最后我们返回结果值对应的级别I。
只经历4次查找，我们就找到了正确值。
相比VLOOKUP用遍历法经历9次查找，孰优孰劣一目了然对不对？
用法总结：
当中间值小于查找值时，则向下查（不含中间值在内的下方的数据中继续二分查找）；
当中间值大于查找值时，则向上查（不含中间值在内的上方的数据中继续二分查找）；
到最后一个值，触底向上查。
3.LOOKUP查找升序排列的原因
弄清楚了遍历法查找和二分法查找的工作原理后，我们就可以回答LOOKUP查找为何需要数据升序排列了。
第一，这是二分法分区查找方法的必然。
二分法把数据分成上下两个区域。当中间值小于查找值，就往下查，当中间值大于查找值往上查。反复这个过程，快速缩小范围，快速逼近查找值。这个过程很像猜价格游戏。如果数据不是升序排列的，那么用中间值的大小来判断上下查找区域就失去了意义，每次二分很可能不是在逼近查找值而是远离查找值！
第二，不升序排列就很大可能找不到正确值。
譬如下图。
查找区域的数据没有采用升序排列。经过4次二分法查找，来到最后一个值13000，已知13000大于10000，此时就触底向上，取首位小于等于查找值的值，即7000，然后返回级别F。
这就是日常使用LOOKUP二分法进行查找，找不到正确值的原因——数据区域没有升序排列。
VLOOKUP作为LOOKUP的改进版，在模糊查找中保留了LOOKUP的特性——用二分法查找。因此当用VLOOKUP进行区间查找时，必须要保持数据升序排列。
二、二分法查找规律
通过上面案例，大家对二分法已经有了初步的了解。下面我们细说二分法的4条查找规律。
①升序排列是获得正确值前提。
②大往上小往下查。
③等值向下查，取连续等值末位数。
④触底向上查，取首位小于等于查找值的数。
注：①上下针对的是同列查找，即竖向查找；如果同行查找，即横向查找，则是左右。LOOKUP横向竖向均可查找。
②不管是否升序排列，二分法都严格遵守②③④的运作规则去取值。
1.第一条和第二条二分规律
数据升序排列在前面已经说明，不再赘述。
大往上小往下查：中间值小于查找值，向下（或向右）继续二分；反之，向上或向左继续二分。
2.第三条二分规律：等值向下查，取连续等值末位数
指的是中间值等于查找值时，将含中间值在内的向下（或向右）计数的连续等值区域中的最后一位作为查找结果。
有两种情况。
（1）向下（或向右）计数的连续等值区域只包含中间值一个，那中间值就是查找结果
譬如下方：第二个中间值等于查找值11000，由于向下等于查找值的连续区域只包括中间值一个，所以中间值就是查找结果。
（2）向下（或向右）计数的连续等值区域只包含多个值，那最后一个就是查找结果
譬如下方：第二个中间值等于查找值9，由于向下等于查找值的连续区域有3个值，所以第3个9是查找结果。
从上图还可以看出一个区别：当有多个值符合查找条件，VLOOKUP总是查找到第一个符合条件的值；而LOOKUP总是查找到最后一个符合条件的值（连续等值区域内）。
3.第四条二分规律：触底向上查，取首位小于等于查找值的数
指的是二分到最后一个数据，将含最后一个数据在内的向上（或向左）计数的区域中第一个小于等于查找值的数据作为查找结果。
有三种情况。
（1）最后一个数据小于或者等于查找值，则最后一个数据就是查找结果
譬如：经过3次二分后，得到最后一个值99，其向上的区域（截至前方的中间值）包括两个数字，第一个小于查找值100的就是最后一个值99。
拓展：
知道了这点，不难理解如果查找值大于查找区域中所有数据，则LOOKUP肯定返回最后一个值。
这就是高手用LOOKUP“座”字查找最后一个文本，用极大值查找最后一个数字的秘密。
对包括英文和中文的文本进行升序排列，默认按照文本首字母从A到Z进行编码排序，先排英文后排中文。中文中做、坐、座等字是编码很大很靠后的一批字，所以用它们通常能查到最后一条文本。
9E+307是一个极大数，近似Excel能处理的最大数值，所以当需要查最后一条数字的时候，通常用9E+307来查找。
（2）最后一个数据大于查找值，则从最后一个数据开始向上计数的第一个小于等于查找值的数据就是查找结果
譬如：经过3次二分后，得到最后一个值105，其向上的区域（截至前方的中间值）包括两个数字，第一个小于等于查找值100的就是96。
（3）最后一个数据大于查找值，且从最后一个数据开始向上计数找不到小于等于查找值的数据则返回错误值
譬如：三次二分后最后一个值是70，含它在内的上方区域中没有一个值小于等于查找值69，所以返回#N/A 错误值。
拓展：
不难理解如果查找值小于查找区域中所有数据，则LOOKUP肯定返回#N/A 错误值。
三、奇怪问题：为何只能查找小于或等于查找值的值？
LOOKUP只能查找小于或等于查找值的值，为什么不能查比查找值大的最小值呢？
用一个生活中大家都玩过的套圈圈游戏来解释。
你的面前从小到大地摆了许许多多的熊猫（查找区域），你花了20块钱，买了一个圈（查找值），准备拿着这个圈，去套熊猫公仔。套之前，老板娘告诉你游戏规则：圈圈必须完全将玩具容纳进去，才算成功。
20块一个的圈，拿去套熊猫，你最希望能套到哪个熊猫呢？
根据游戏规则，必须将玩具完全容纳才算成功。那么你能套中的，只能是比圆圈直径小的熊猫，极限发挥，能套中与圆圈直径相等的熊猫，但根本不可能套中比圆圈直径大的熊猫！
这就是LOOKUP只能查找小于或等于查找值的值的原因。
四、怎么理解不排序也能精确查找的LOOKUP套路公式？
LOOKUP精确查找的前提是升序排列，但是经过前辈们的探索与努力，也写出了不排序也能实现精确查找的套路公式：
=LOOKUP（1,0/(查找范围=查找值),结果范围）
譬如下面：
左侧，品种没有排序，编码最小的A排在了最末，用LOOKUP常规查找得不到正确数据；
右侧，同样没有排序，用套路公式得到了正确数据。
这是什么魔法？
其实这里巧妙利用了LOOKUP函数的特性——忽略查找区域中的错误值。
在下方的表中，我们设法把A品种外的所有品种变成了错误值#DIV/O、#NAME、#N/A等。用LOOKUP常规查找，得到了正确答案。原因就在于所有错误被忽略后，就只剩下A，闭着眼睛都能找到它。
套路公式中“0/(查找范围=查找值)”的作用就是把不符合条件的都变成错误值#DIV/O，符合条件的变成0。最后在0中查找1，根据二分法规律查找到唯一的0，再返回0对应的数量。这就是魔法！
如果品种列中A品种有多个，每个A都变成0并将在忽略错误后成为一个连续区域，根据二分法规律如果查找区域中 “所有数据都小于查找值，则LOOKUP肯定返回最后一个值”，则获得最后一个A的数量。如下。
好的，以上就是我花了两天的时间，给大家整理的关于LOOKUP二分法的原理。
懂得了这个原理，就能完全理解和掌握这个函数了，可以在多条件查找、逆向查找、横向查找、区间查找、简全称查找中大胆使用它而不必总依赖VLOOKUP了。
本文配套的练习课件请加入QQ群：902294808下载。
做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！
扫下方二维码关注公众号，可随时随地学习Excel：
相关推荐：
版权申明：
本文作者逍遥；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。