ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

EH搜索     
EH云课堂-专业的职场技能充电站 Excel转在线管理系统,怎么做看这里 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 EH云课堂直播课程免费学 打造核心竞争力的职场宝典
300集Office 2010微视频教程 Tableau-数据可视化工具 精品推荐-800套精选PPT模板,点击获取 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 Excel VBA经典代码实践指南
查看: 124043|回复: 346

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

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2009-12-4 12:07 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:数据查询和匹配
原帖发表于: 【公式解析系列】之LOOKUP(2,1/(条件),查找数组或区域)

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

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



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

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

  2. 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. LOOKUP(1,0/(条件),查找数组或区域)
复制代码
【参考链接】在此帖[函数用法讨论系列10] LOOKUP的查找策略!
gouweicao78《Lookup函数二分法模拟器
willin2000修正后的《LOOKUP查找策略完整流程图

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

评分

参与人数 11财富 +50 鲜花 +16 技术 +1 收起 理由
notkonw + 2
yuhuajia + 1 优秀作品
paladin417 + 2 太强大了
晟枫 + 2
点滴梧桐雨 + 2 值得肯定

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2009-12-4 12:09 | 显示全部楼层
如图:
LOOKUP流程图查找示例.jpg
公式:

  1. =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. {#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点来返回查找结果的。


模拟动画如下(请单击动画或下载后观看,以便获得更佳效果):
Lookup二分法1.gif

用LOOKUP查找2个条件的数据.rar

1.98 KB, 下载次数: 4167

评分

参与人数 2鲜花 +4 收起 理由
yingzi1212 + 2 太强大了
陈文华 + 2 值得肯定

查看全部评分

TA的精华主题

TA的得分主题

发表于 2009-12-4 12:13 | 显示全部楼层
又看到狗尾草版版经典的“庖丁解牛”式的解释啦,简单易懂,由衷地谢谢你

TA的精华主题

TA的得分主题

发表于 2009-12-4 12:25 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2009-12-4 12:34 | 显示全部楼层
占個沙發學習一下先...gouweicao78兄功力高深不可測,是我等學習的好榜樣呀!

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

TA的精华主题

TA的得分主题

发表于 2009-12-4 12:57 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2009-12-4 13:00 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2009-12-4 13:12 | 显示全部楼层

回复 1楼 gouweicao78 的帖子

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

TA的精华主题

TA的得分主题

发表于 2009-12-4 13:14 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2009-12-4 13:17 | 显示全部楼层

回复 2楼 gouweicao78 的帖子

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

评分

参与人数 1鲜花 +2 收起 理由
依扬 + 2 优秀作品

查看全部评分

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关注官方微信,高效办公专列,每天发车

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2019-10-20 06:22 , Processed in 0.085059 second(s), 16 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2020 Wooffice Inc.

   

沪公网安备 31011702000001号 沪ICP备11019229号

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师

快速回复 返回顶部 返回列表