解析lookup的经典查找方式.docx

解析lookup的经典查找方式.docx

《解析lookup的经典查找方式.docx》由会员分享，可在线阅读，更多相关《解析lookup的经典查找方式.docx（9页珍藏版）》请在冰豆网上搜索。

解析lookup的经典查找方式

解析ｌookｕp的经典查找方式

————————————————————————————————作者:

————————————————————————————————日期:

ﻩ

解析lｏokuｐ的经典查找方式

包括两部分内容：

第一，ｌoｏkuｐ函数用法介绍;第二,通过实例讲解ｌookuｐ函数经典的条件查找解法，通用公式基本可以写为:

LＯＯKＵP（２,1/（条件）,查找数组或区域）或LOＯKＵＰ（１,０/（条件）,查找数组或区域）。

-

主讲老师:

卢子老师ﻫ　讲座主题:

解析ｌoｏkｕp函数的经典查找方式

　学习是需要技巧和经验的。

感谢卢子老师为大家分享和交流他的实战经验。

下面是本期讲座的全部内容。

　本期讲座包括两部分内容：

第一,ｌookup函数用法介绍;第二，通过实例讲解ｌooｋup函数经典的条件查找解法，通用公式基本可以写为：

LOOKUP（2,1/（条件）,查找数组或区域）或LOOＫUP（1,０/（条件）,查找数组或区域）。

　　第一部分:

lｏｏkｕｐ函数用法介绍

　　ｌookup函数和vｌoｏｋｕｐ函数是ｅxcel中最常用的两个查找函数。

vｌookup函数能做到的lｏokup函数同样可以做到，而且可以做得更好。

　LOOKUP函数有两种语法形式：

向量和数组。

本期就向量形式的展开交流和探讨。

　向量形式的语法为:

ＬOＯKUP（looｋuｐ_value，ｌookup_vectｏr,result_vｅcｔｏr）

　　其中的参数意义如下：

　Looｋｕp_valuｅ：

为所要查找的数值。

Lｏoｋuｐ＿vａlue　可以为数字、文本、逻辑值或包含数值的名称或引用。

　　Lookup_ｖector：

为只包含一行或一列的区域。

Lｏokup＿ｖector　的数值可以为文本、数字或逻辑值。

Looｋup_ｖｅctor的数值必须按升序排序:

..．、-2、－1、0、1、２、..．、A-Z、ＦALSE、TRUE,否则，　LOＯＫＵP不能返回正确的结果。

文本不区分大小写。

ﻫ　Result_vectｏｒ：

只包含一行或一列的区域,其大小必须与lookup_vectｏr相同。

ﻫ　比如lｏokuｐ（Ａ1,B1:

Ｂ10,C２:

C11），其中C2:

Ｃ11的尺寸要与B1:

B1０相同，且如果Ａ1对应B列中的位置是B2的话,那么返回的将是C3的值。

　LOOKUP函数说明：

　第一,如果函数　ＬOOKUP找不到　lookup_ｖａｌｕe，则查找lookup_ｖector中小于或等于　lｏokｕｐ_ｖａｌuｅ的最大数值。

这就是为何返回最后一个满足条件的值的原理。

ﻫ　第二，如果　loｏkup_value　小于looｋｕp_vectoｒ中的最小值,函数ＬOOKUP返回错误值#N/Ａ。

　利用这个特性，我们可以用=ＬOOＫUP（１,0／（条件）,引用区域）这样一个通用公式来作查找引用。

　　第二部分:

loｏｋup函数实例运用

　　运用一:

模糊查找

　模糊查找的核心是第二个参数排序必须是升序，否则会导致查找值错误。

下图所示的表1是按升序排序的,表2没有排序。

　　分别在表1和表2下面对应的单元格输入公式。

表１的数据源是按升序排序的，根据looｋup函数用法：

=ＬOOKUP（要查找的数据,查找范围，结果），在C2４单元格设置公式：

＝LOOKUP（Ｂ２4，$B＄5:

$B$１7,$C＄5：

$Ｃ$17）,然后下拉得到正确结果。

　表２的数据源是没有排序的,在J24单元格输入公式:

=LOOKUP（I2４，$I$5:

$Ｉ$１7，$J$５:

$J＄1７）　,然后下拉,发现Ｊ25单元格得到的结果是Ｈ126,显然不对。

通过表２的源数据可以看到I25单元格对应的值应该为J8单元格的值Ｈ１42。

为什么会出错呢?

这就印证了第一部分的用法介绍中所讲到的：

Lookuｐ_ｖectoｒ的数值必须按升序排序：

...、-2、－1、0、1、2、..．、A-Z、FALSE、TＲUE,否则，LOOKUP不能返回正确的结果。

文本不区分大小写。

　　模糊查找，数据源一定要以升序先进行排序，否则就会出错。

在数据源没有排序的情况下，如何才能查找到正确结果?

LOOＫUP函数有一个经典的条件查找解法,可以很好的解决此问题。

　在第一部分有提到，通用公式基本可以写为：

LOOKＵP（2,1/（条件）,查找数组或区域）或LOOＫUP（１,０/（条件）,查找数组或区域）。

公式中的２、１、0等数字的含义是什么？

首先,条件是一组逻辑判断的值或逻辑运算得到的由TＲUE和FＡLSＥ组成或者０与非0组成的数组,因而：

０/（条件）的作用是用于构建一个由0或者#DＩＶ!

０错误组成的值。

比如数据源中能查找到对应值就是ture,没有就是false。

形式如：

0/Ｔrｕe＝0,0/fａlｓｅ=＃DIＶ0!

查找到就0，没有就是错误值。

　如果LOOKＵP　函数找不到ｌookup＿value（即：

１），则它与lookuｐ_vｅｃtor中小于或等于looｋup_value的最大值（即：

０）匹配。

　　也就是说,要在一个由０和#DＩV!

0组成的数组中查找1，肯定找不到１，因而将返回小于或等于1的最大值（也就是０）匹配。

用大于０的数来查找0,肯定能查到最后一个满足条件的。

　以上的原理,被俗称为“以大欺小法”。

这种技巧在LOOKUP函数上的运用是很常见的。

　利用上面的原理，不管有没有排序，只要使用上面的“以大欺小法”都能得到正确结果。

比如上面实例中,在J25单元格输入公式:

=LOOKUP（１,0/（I２5=＄Ｉ$5:

$I$17）,$J$5:

$J$１7）,就可以了。

　运用二：

精确查找

　　第一，查找的数据没有对应值，可以利用ISNA（ISERRＯＲ）函数屏蔽错误值。

　如上图所示，表3是数据源，在下面左边根据“番号”查找“俗称”。

单击C５1单元格,输入公式=LOＯKUP（１,0／（B51＝$B$42:

$B＄45）,$C$４2:

$C$4５）,然后下拉可以看到下面的C５2和C５４单元格出现错误值。

这种情况可以利用ＩＳNA（ISERROR）函数屏蔽错误值。

　　只要在公式外面嵌套个if（isｎａ（lｏｏkuｐ（），＂",lookup（））,这样的形式就可以把错误值屏蔽。

在H５1单元格，输入这样的公式：

=ＩF（ISＮA（LＯOKUP（1,0／（G51=＄Ｂ$42:

$Ｂ$45）,$Ｃ$42:

$Ｃ$45））,"",LＯOＫUP（1,０／（G５1=$B$４２:

＄B$45），$C$42:

＄C$45）），下拉，就可以屏蔽错误值了。

将错误值屏蔽了，表格就好看多了。

　上面公式中,""，是显示空的意思,错误就显示空,没有就查找。

　第二,借助错误值来判定产品是否存在。

　　下图所示根据左边的数据源，来判定右边对应的数据是否在番号列中。

只需要嵌套一个isｎａ函数就可以做到,如果没有存在就错误,有存在就.．.．..这样的形式。

在H62单元格输入公式：

=IF（ISNA（ＬOOKＵP（1,0/（Ｇ62=$B$62:

$B$74）））,"否＂,"是"）,下拉就即可得出结果。

　“图啥”网友问:

isｅrror与isna函数的区别。

ISＮA只屏蔽#Ｎ/A错误,ISEＲＲOR屏蔽所有错误。

　第三，LOＯKUP函数多条件查找。

　如上图所示，根据“俗称”和“订单号”来查找“订单数”和“尾数”,可以套用这样的公式:

=LOＯＫUP（1,0／（条件（１）*（２）＊（3）.。

。

。

。

。

）,引用区域）,用＊或&将各个条件连接起来，*就是和的意思。

　此题有两种方法:

ﻫ　第一，在K112单元格输入公式：

=LOOKUＰ（1,0/（（$I112=$B$112:

$B＄12０）*（$J1１2=$C$11２:

$Ｃ$12０））,D$1１2:

D$１２0），复制公式就可以得到结果。

ﻫ　第二,另外也可以使用这个公式：

=LOＯKUP（1,0／（$I１12＆$J11２=$Ｂ$112:

＄Ｂ$120&$C$112:

＄C$120）,D＄112：

Ｄ＄120）

　　第四,含某个字符查找。

　　按照上图所示,根据左边的数据源,来对含有某个字符进行查找。

单击G１２8单元格，输入公式：

=LOOKUP（１,０／（FＩＮD（＄F1２8,$Ｂ$12８：

$Ｂ$131））,Ｂ$1２8:

Ｂ$131）,就可以得到结果。

　　ＶLＯＯKUＰ函数与ｌoｏkｕｐ函数对比：

第一,在多条件查找方面，就能看出loｏkｕp函数好用。

用vｌooｋup多条件查找,最简单的方法就是借用辅助列。

ﻫ第二，　ＶLOOKUＰ函数对于反向查找是需要嵌套其余函数才能实现,而LOＯＫＵP函数没有正反之分,因此在这方面LOＯＫUP函数会更加容易实现。

　第三,vlookup在查找字符方面，可以使用*号类通配符。

LＯOKＵP是不支持通配符的，但可以使用ＦIND（查找字符,数据源区域）的形式代替。

在前面的话：

不要迷恋二分法,二分法只是个传说。

因对ＬOOKUP二分法的研究，可能让人看起来貌似goｕweｉｃao７8水平很高深。

但本人一方面尽力让大家能看懂它，另一方面并没有提倡过大家一定要看懂二分法,因为升序查找，不需动用“二分法”，仅函数帮助也能说明白。

对于这个原理的探究,仅仅是函数发烧友们的乐趣。

ﻫ怎样看待LＯOKUＰ的“高效函数”之称

第一,LOOKUP使用二分法原理，因此具有极高效率的运算方式;但是只推荐升序查找用它，升序的时候。

ﻫ第二，ＬＯOKＵＰ（2,1/（条件），……，尽管是因为“二分法”让ＬOOKUP能找到最后一个满足条件的记录,但是，“条件”,比如（A1:

Ａ1０="张三"）——首先是一个数组运算,然后1/条件又来一次数组运算，最终才用LOＯKUP二分法。

这么一个“普通公式”中暗藏数组运算的东西,让“高效函数”背上了黑锅。

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

1.LOOKUＰ（2,1/（条件）,查找数组或区域）    

2.或ＬＯOKUP（１，0／（条件），查找数组或区域）

很多初学者对此感觉非常诧异就,主要疑惑有：

ﻫ１、公式中的2、1、0等数字有什么含义，明明在查找条件与这3个数字根本毫无联系，怎么能得到正确结果？

ﻫ２、明明ＬＯOKUＰ函数说明需要“升序”查找,否则可能无法返回正确的值,上面这种解法又是如何得改变这一说法呢?

ﻫ3、据说LOOKUP函数的查找顺序是“二分法”，并且有流程图可循，是否可以结合此例进行讲解？

ﻫ【函数帮助信息摘录】

语法:

LOOKUＰ（loｏkup_ｖalｕe,looｋup_veｃtor,resulｔ_vｅctｏr）ﻫ1、[要点]  ｌookup_veｃｔor中的值必须以升序排列：

...，－２,－1,0,1,２,...,　A-Z，FＡLＳE,TＲUE。

否则，LOOKUP可能无法返回正确的值。

大写文本和小写文本是等同的。

2、如果LＯOKUＰ函数找不到lｏokuｐ_valｕｅ，则它与lｏoｋup_ｖectｏr　中小于或等于loｏkup_vaｌue　的最大值匹配。

3、如果lookｕp_vaｌue小于lookuｐ_vｅｃｔor中的最小值，则　LOOKUP会返回　＃N/A错误值。

【释疑】简要地说，从逻辑推理来看：

ﻫ１、首先，条件是一组逻辑判断的值或逻辑运算得到的由TＲUE和FALSE组成或者0与非0组成的数组，因而:

1/（条件）的作用是用于构建一个由1或者#DIV！

０错误组成的值。

ﻫ2、根据LOOKUＰ函数说明中的这一条:

ﻫ如果LOOKＵP函数找不到　ｌｏｏkｕｐ_vａｌuｅ（即：

2），则它与　lｏokup_vector中小于或等于lｏoｋup_vaｌue的最大值（即：

1）匹配。

ﻫ也就是说，要在一个由１和#DIV!

０组成的数组中查找２,肯定找不到２,因而将返回小于或等于2的最大值（也就是1）匹配。

为什么要用2来查找１或用１来查找0呢?

因为如果有多个与第1参数相等的值，则Loｏkup就不一定返回“最后一个”所对应的记录，所以必须养成一个良好习惯，

而不要用:

LOOKUＰ（1,1/（条件）,……，或LＯOＫUP（，0/（条件）,……

ﻫ3、如果有多个满足条件的纪录,为何只返回最后一个，而不是第一个或其他呢？

这个解释就需要二分法流程图的模拟了。

而对于一般使用者来说，只需要记住“查找满足条件的最后一个记录”可以使用通用公式

1.ＬOOＫＵＰ（2,1/（条件），查找数组或区域） 　   

2.或LOOＫUP（１,0/（条件）,查找数组或区域）