【公式解析系列】之LOOKUP(2,1/(条件),查找数组或区域)

gouweicao78

原帖发表于： 【公式解析系列】之LOOKUP(2,1/(条件),查找数组或区域)

写在前面的话：
不要迷恋二分法，二分法只是个传说。
因对LOOKUP二分法的研究，可能让人看起来貌似gouweicao78水平很高深。但本人一方面尽力让大家能看懂它，另一方面并没有提倡过大家一定要看懂二分法，因为升序查找，不需动用“二分法”，仅函数帮助也能说明白。对于这个原理的探究，仅仅是函数发烧友们的乐趣。

怎样看待LOOKUP的“高效函数”之称
第一，LOOKUP使用二分法原理，因此具有极高效率的运算方式；但是只推荐升序查找用它，升序的时候。
第二，LOOKUP(2,1/(条件),……，尽管是因为“二分法”让LOOKUP能找到最后一个满足条件的记录，但是，“条件”，比如(A1:A10="张三")——首先是一个数组运算，然后1/条件又来一次数组运算，最终才用LOOKUP二分法。这么一个“普通公式”中暗藏数组运算的东西，让“高效函数”背上了黑锅。



【正文】
LOOKUP函数有一个经典的条件查找解法，通用公式基本可以写为：

1. 
   
2. LOOKUP(2,1/(条件),查找数组或区域)     
   
3. 或
   
4. LOOKUP(1,0/(条件),查找数组或区域)
   

复制代码

很多初学者对此感觉非常诧异就，主要疑惑有：
1、公式中的2、1、0等数字有什么含义，明明在查找条件与这3个数字根本毫无联系，怎么能得到正确结果？
2、明明LOOKUP函数说明需要“升序”查找，否则可能无法返回正确的值，上面这种解法又是如何得改变这一说法呢？
3、据说LOOKUP函数的查找顺序是“二分法”，并且有流程图可循，是否可以结合此例进行讲解？


【函数帮助信息摘录】
语法：LOOKUP(lookup_value, lookup_vector, result_vector)
1、[要点]  lookup_vector 中的值必须以升序排列：...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE。否则，LOOKUP 可能无法返回正确的值。大写文本和小写文本是等同的。
2、如果 LOOKUP 函数找不到 lookup_value，则它与 lookup_vector 中小于或等于 lookup_value 的最大值匹配。
3、如果 lookup_value 小于 lookup_vector 中的最小值，则 LOOKUP 会返回 #N/A 错误值。


【释疑】简要地说，从逻辑推理来看：
1、首先，条件是一组逻辑判断的值或逻辑运算得到的由TRUE和FALSE组成或者0与非0组成的数组，因而：1/(条件)的作用是用于构建一个由1或者#DIV!0错误组成的值。
2、根据LOOKUP函数说明中的这一条：

如果 LOOKUP 函数找不到 lookup_value （即：2），则它与 lookup_vector 中小于或等于 lookup_value 的最大值（即：1）匹配。

也就是说，要在一个由1和#DIV!0组成的数组中查找2，肯定找不到2，因而将返回小于或等于2的最大值（也就是1）匹配。

为什么要用2来查找1或用1来查找0呢？因为如果有多个与第1参数相等的值，则Lookup就不一定返回“最后一个”所对应的记录，所以必须养成一个良好习惯，
而不要用：LOOKUP(1,1/(条件),……，或LOOKUP(,0/(条件),……

3、如果有多个满足条件的纪录，为何只返回最后一个，而不是第一个或其他呢？这个解释就需要二分法流程图的模拟了。而对于一般使用者来说，只需要记住“查找满足条件的最后一个记录”可以使用通用公式

1. LOOKUP(2,1/(条件),查找数组或区域)     
   
2. 或
   
3. LOOKUP(1,0/(条件),查找数组或区域)

复制代码

【参考链接】在此帖：[函数用法讨论系列10] LOOKUP的查找策略！
gouweicao78《Lookup函数二分法模拟器》
willin2000修正后的《LOOKUP查找策略完整流程图》

补充内容 (2017-10-29 08:50):
http://www.exceltip.cn/thread-5109-1-1-2.html

gouweicao78

如图：

公式：

1. 
   
2. =LOOKUP(2,1/((E2=$A$2:$A$9)*(F2=$B$2:$B$9)),C2:C9)
   

复制代码

根据Willin2000兄的完整流程图，一步一步判断走向和计算。

一、需要明白的几个含义：
1、posi——表示节点号n，A(posi)表示第n个值，比如posi=2，则表示LOOKUP第2参数数组中的第2个值；
2、X——表示Lookup的第一个参数，即查找的值，也就是上述公式中的“2”；
3、TYPE——表示数据类型，比如：数值、文本、逻辑值、错误值等；
4、left、right——表示二分法取值范围，即左边从数组的第几个值开始取，右边截止到数组的第几个值。初始状态下left=1，right=N表示从第1到第N整个数组所有的值都算。
上例中：1/(条件)得到的数组为：

1. 
   
2. {#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!}——共8个值
   

复制代码

所以，第一次posi=int((1+8)/2)=4——即从该数组的一半处分开（这也是“二分法”的由来）

二、需要注意的问题
上面的posi、left、right都是变量，在流程图中，根据规则不断修正从而得到下一步的posi点，而LOOKUP函数最终也是通过定位最后的posi点来返回查找结果的。


模拟动画如下（请单击动画或下载后观看，以便获得更佳效果）：

Viewsonicquanta

又看到狗尾草版版经典的“庖丁解牛”式的解释啦，简单易懂，由衷地谢谢你

huangyun

谢谢版主，进来学习。

xiongkehua2008

占個沙發學習一下先...gouweicao78兄功力高深不可測,是我等學習的好榜樣呀!

----------------------------------------------------------------------------------------------------------------
剛才趕上直播了,趕緊的讓路...

foundtree

{=MAX(IF((A$2:A9=E2)*(B$2:B9=F2),C$2:C9))}

foundtree

用min还可以找最新值

dfzaq58

好教材，值得一学习，希望老师能多发这样的贴子。

52UEXCEL

学习了，受教了，谢谢

lzzgzby

说实在的，这个lookup的二分法查找原理图我一直不是很明白，看来要花点时间认真看看了。
狗版的解释让我理解又更进了一步。