设为首页收藏本站|繁體中文

Excel 技巧网

 找回密码
 注册

QQ登录

只需一步,快速开始

手机号码,快捷登录

查看: 32019|回复: 57

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

  [复制链接]
发表于 2009-12-3 23:08:32 | 显示全部楼层 |阅读模式
  • 署名作者: gouweicao78
  • 版权声明: 版权归本站与作者共有 除本站官方外非作者本人转载须经许可并注明出处
  • 本文来自:
  • 引用作品:
  • 适用版本: 2010 2007 2003以前版本 
  • 语言环境: 简体中文
  • 学习方法: 掌握Excel技巧的关键是动手操作 | 下载 ≠ 知识


  • 免费注册成为本站会员,享用更多功能,结识更多Office办公高手!

    您需要 登录 才可以下载或查看,没有帐号?注册

    x
    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/(条件),查找数组或区域)
    复制代码
    【参考链接】
    gouweicao78《Lookup函数二分法模拟器http://www.exceltip.net/thread-285-1-1.html
    willin2000修正后的《LOOKUP查找策略完整流程图http://www.exceltip.net/thread-308-1-1.html
     楼主| 发表于 2009-12-3 23:28:08 | 显示全部楼层
    本帖最后由 gouweicao78 于 2009-12-4 00:13 编辑

    示例: 用LOOKUP查找2个条件的数据.rar (1.98 KB, 下载次数: 465)

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

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

    评分

    参与人数 1魅力值 +5 收起 理由
    snowangle007 + 5

    查看全部评分

    回复 支持 反对

    使用道具 举报

    发表于 2010-1-5 10:05:16 | 显示全部楼层
    貌似用斑竹提到的两种公式,lookup_vector不需要按升序排列,不知道是不是这样,请斑竹及各位高手指导一下。
    回复 支持 反对

    使用道具 举报

     楼主| 发表于 2010-1-5 10:47:33 | 显示全部楼层
    1、LOOKUP为什么需要lookup_vector升序排列?
    从二分法的角度来说,数据以升序排列的方式是最合理的,就像我们翻字典可以根据首字拼音字母直接翻到附近,大大缩小需要查找的范围,从而相比从第1个到最后1个查找的“遍历法”来说,是一个非常高效的算法。
    举个例子来说:要查字母C,首先第一次二分法是它属于A~M,然后再属于A~G,接着属于A~D,然后再属于C~D,最后敲定为C。也就是大约为5次的查找。假如,我们把字典反过来了(降序排列了),那么LOOKUP就傻眼了,有兴趣可以按照流程图对照走一下,算算大约几次。

    2、LOOKUP为什么不需要lookup_vector升序排列?
    因为LOOKUP用的是二分法,它根据每一个posi(流程图中的节点)的数据情况决定下一步的流程,即便不是升序排列,它也是如此查找。

    3、所谓“高效”,是指LOOKUP本身这种运算是高效的,但是:
    1/(条件)——这个首先是条件判断,比如1/(A1:A100="张三"),首先运算100次比较,得到逻辑值,接着运算100次除法,得到1或#DIV!0,最后才是LOOKUP的二分法。

    因而,实际上本帖解析的这个解法,并不是“高效”的解法。

    综上所述:升序,是为了更高效地运作二分法;此处的“二分法”并非严格意义的二分法,如果给了它并非升序的数据,它也会按照流程来运算。

    评分

    参与人数 1魅力值 +5 收起 理由
    好大一棵树王玉 + 5 感谢帮助:)

    查看全部评分

    回复 支持 反对

    使用道具 举报

    发表于 2010-1-5 10:55:14 | 显示全部楼层
    我试了试,如果lookup_vector中没有lookup_value ,但是有小于lookup_value的最大值,用斑竹提供的公式得到的是#N/A 错误。不知道斑竹提供的公式是不是有一定的局限,也就是说lookup_vector中必须有lookup_value的值。
    回复 支持 反对

    使用道具 举报

     楼主| 发表于 2010-1-5 11:09:51 | 显示全部楼层
    得到#N/A错误,在流程图中就给出这样的可能啊,你对照流程的走法去算:
    比如共10个数据,那么N=10,left=1,right=10,第一个posi=int((left+right)/2)=5,………………
    然后判断第5个数据的数据类型是否与lookup_value相同,数据的大小情况如何,再继续判断下一步流程。
    回复 支持 反对

    使用道具 举报

    发表于 2010-1-5 11:48:44 | 显示全部楼层
    那如果lookup_vector不是按照升序排列,并且lookup_vecto中没有lookup_value的值,我要查找lookup_value的话该怎么办呢。
    回复 支持 反对

    使用道具 举报

     楼主| 发表于 2010-1-5 12:11:50 | 显示全部楼层
    呵呵,一句话,按照流程走。
    =LOOKUP("Z",{"A","E","B"}),就符合你说的那种情况,按照流程走最终找到最后一个记录,因为Z比A、E、B都“大”。
    =LOOKUP(1,0/(条件),……)也是如此,因为1比0/(条件)的结果(0和#DIV!0,同类型的只有0)都大。
    把1换为2,把0换为1也是一个道理。
    回复 支持 反对

    使用道具 举报

    发表于 2010-1-5 13:46:27 | 显示全部楼层
    可是这样也不能得到正确的答案啦,就像斑竹举的例子里一样,1比0/(条件)的结果始终为false,导致最终的结果还是#N/A ,而正确的答案不是应该为“E”么。
    回复 支持 反对

    使用道具 举报

     楼主| 发表于 2010-1-5 15:42:38 | 显示全部楼层
    如果找不到,也就是(条件)判断都是FALSE,例如:
    =LOOKUP(1,0/("Z"={"A","E","B"}),{"A","E","B"})——找到最后一个Z的记录,当然是找不到,导致最终结果为#N/A!错误。


    但=LOOKUP("Z",{"A","E","B"})不一样
    left=1、right=3,第一个节点为int((1+3)/2)=2,即字母E与Z相比,比Z小。
    下一步判断posi(就是2)是否小于right(3),即是否最后一个记录,不是最后一个记录则left=posi+1=2+1=3,接下来:新的posi=int((3+3)/2)=3,也就是字母B,还是比Z小。
    下一步判断posi(就是3)是否小于right(3),因为是最后一个记录,则返回A(posi)就是第3个记录B
    走一下流程图就知道了,如图:

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

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

    使用道具 举报

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

    本版积分规则

    关闭

    站长推荐上一条 /1 下一条

    Excel技巧网的会员探讨问题仅代表其个人意见,与网站的立场无关。任何违反国家和地方相关法律法规的言论,本站有义务协助政府相关部门追究发言者的责任!
    本站中非注明转载文章与案例的版权为作者与Excel技巧网共有。若非原文作者,本站之外任何单位或个人未经允许,不得将其用于商业用途。
    若非原文作者,任何形式的非商业性转载必须获得Excel技巧网或作者允许,并注明作者和出处。
    会员发表的帖子如涉及版权纠纷,须自行负责。详情请参考注册时的网站服务条款。
    本站特聘法律顾问:沈学律师

    Archiver|手机版|Excel技巧网 ( 闽ICP备08107682号-2 ) | 闽公网安备 35020302032608号  

    GMT+8, 2018-7-23 11:52

    Powered by Discuz! X3.3

    © 2001-2017 Comsenz Inc.

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