二维码 购物车
部落窝在线教育欢迎您!

VLOOKUP&LOOKUP双雄战(三):LOOKUP守得云开见月明

 

作者:小花来源:部落窝教育发布时间:2019-04-29 10:03:34点击:1179

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

在前三个回合的较量中,LOOKUP处于下风。第四回合的比赛题目是“区间查询”,简单来说,就是判断某个数值属于哪个区间哪个等级。虽说VLOOKUP和LOOKUP都将利用二分法原理完成区间查询,但二分法LOOKUP唯一的、根本的内功心法,LOOKUP能否凭此守得云开见月明呢?

 

“年少万兜鍪,坐断东南战未休。”VLOOKUPLOOKUP的故事还在继续,刀锋未休!连续吃瘪的LOOKUP试图挽住颓势,二分法千呼万唤始出来,精彩马上开始!

ROUND 04  区间查询

在数值查询中,我们经常需要查找数值所对应的区间。一个经典的问题就是学生成绩等级评定,0-60(不含60)为不及格,60-75(不含75)为及格,75-85(不含85)为良好,85以上为优秀。面对这种问题,你是否还在用IF函数反复嵌套?

3.1:区间查询——IF函数嵌套

OUT了!!!快来看看VLOOKUPLOOKUP是怎么做的吧?

在使用这两个函数之前,我们必须按下图所示,对各区间及对应值进行升序排列:

1.将数值区间的分界值按升序依次填入连续的单元格,即从最小值到最大值,自上而下填入同一列单元格中。

2.各区间分界值采用区间下界值,例如,“及格”区间数值60,“优秀”区间数值85

3.如果最小的数值区间无下界,也必须赋予一个足够小的数值,例如-88,否则公式可能会报错。

图片

3.2:区间查询——查找范围

接下来就是VLOOKUPLOOKUP展现战斗力的时候了!!!

VLOOKUP""字之差,难得模糊

VLOOKUP函数的解决之道和其基础用法非常类似,只需将最后一个参数更改为1TRUE即可。

=VLOOKUP(B2,$E$2:$F$5,2,1)

3.3:区间查询——VLOOKUP""字之差

公式说明

最末参数是0的时候,VLOOKUP精确查找,采用遍历法原理将查找值与查找区域首列值从上到下逐一比较。但最末参数是1的时候,VLOOKUP模糊查找,采用二分法原理将查找值与查找区域首列的二分位值(中间值)进行比较。模糊查找要得到正确结果,查找区域首列必须升序排列。记住这一点,你就能轻松驾驭区间查找。否则,VLOOKUP区间查找公式将会带来灾难!!!

3.4:区间查询——查找范围须升序排列

LOOKUP:基础用法,简单粗暴

同样的问题,LOOKUP是怎么做的呢?基础用法,直接上!!!就是这么简单粗暴!

3.5:区间查询——粗暴的LOOKUP

公式说明:

LOOKUP函数只采用二分法查找,所以只要做好了升序排列和区间设置,按平常基本用法输入公式即可。

区间查找,不论是VLOOKUP还是LOOKUP的查找都采用的是二分法查找。这里以LOOKUP函数为例,说说二分法。

LOOKUP的二分法

LOOKUP总是将查询范围视为一组从小到大排列的有序数组,通过将目标值与查询区域的二分位值进行比较,从而确定目标值出现在查询区域的上半区细分范围(目标值小于二分位值)还是下半区细分范围(目标值大于二分位值)。然后继续将目标值与细分范围的二分位值进行比较,循环往复,直到细分范围不可再分,返回最后一个小于或等于目标值的二分位对应的结果。

特别说明:

1.二分位值的确定。如果查询范围的数据个数为奇数,二分位值取中间项;若为偶数,则取中间两项中项序较小的一项;若仅一项,则视该唯一项为二分位值。例如,5个数据,二分值取第3项,6个数据,二分值也取第3项。

2.连续相等取末原则。如果二分位值与目标值完全相等,则停止二分法查询,而采用连续相等取末原则返回数据。即,如果从二分位值开始向下存在一个连续等于目标值的区域,则返回这个连续区域最后一个数据对应的值;如果从二分位值开始向下不存在连续等于目标值的区域,则返回二分值对应的值。

3.如果目标值大于所有二分位值,则返回查询范围的最后一个值;如果目标值小于所有二分位值,则返回#N/A错误值。

文字生涩,结合实例图解会更加容易!

3.6:区间查询——LOOKUP的二分法

C2=LOOKUP(B2,$E$2:$E$7,$F$2:$F$7)

目标单元格B2先与第一个二分位E4比较,2<3;转而与E4的上半区E2:E3的二分位E2比较,2>1;继续与E2的下半区二分位E3进行比较,2<3。至此,细分范围不可再分,最后一个小于或等于目标值的二分位为E2,所以返回E2对应的结果F2,即"A"

C3=LOOKUP(B3,$E$2:$E$7,$F$2:$F$7)

目标单元格B3先与第一个二分位E4比较,3=3;此时停止二分法查询,转为连续相等取末查找。自当前二分位E4开始,E4E5连续等于3,因此返回E5对应的结果F5,即"D"。图中的E3E4相邻且等于3,但其在E4上方,故不参与第二阶段的匹配;E7也等于3,但E2:E7区域不是连续等于3的区域,所以E7不参与匹配。只有E5满足条件,位于连续相等区域的最末!!!划重点,LOOKUP这一特性经常与其忽略错误值的特性联用,形成经典的LOOKUP(1,0/(条件)...)结构!这一用法我们后续会详细介绍。

C4=LOOKUP(B4,$E$2:$E$7,$F$2:$F$7)

目标单元格B4先与第一个二分位E4比较,4>3;转而与E4的下半区E5:E7的二分位E6比较,4>2;继续与E6的下半区二分位E7进行比较,4>3。至此,细分范围不可再分,最后一个小于或等于目标值的二分位为E7,所以返回E7对应的结果F7,即"F"

第四回合,VLOOKUPLOOKUP都能很好地解决区间查询问题,但LOOKUP保持了它一贯的用法,所以这一局LOOKUP小胜!

结束语

二分法是查询函数中最难的知识点之一,尽管为了讲好这一知识点,小花已经费劲心思,但可能很多小伙伴还是无法完全理解。纸上得来终觉浅,绝知此事要躬行,希望小伙伴们多多练习,在实操中寻找答案!天下英雄谁敌手,战不止争不休,敬请期待!

 

本文配套的练习课件请加入QQ群:264539405下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

VLOOKUPLOOKUP的比较①VLOOKUP&LOOKUP双雄战(一):VLOOKU的漂亮开局

VLOOKUPLOOKUP的比较VLOOKUP&LOOKUP双雄战()LOOKUP剑走偏锋》

LOOKUP二分法讲解 LOOKUP函数用法全解(下)——LOOKUP函数的二分法原理》