打开APP
userphoto
未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
excel函数技巧:两个查询函数的用法比较 续

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

—————————————————

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

***ROUND 04 区间查询

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

太OUT了!!!快来看看VLOOKUP和LOOKUP是怎么做的吧?

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

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

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

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

接下来就是VLOOKUP和LOOKUP展现战斗力的时候了!!!

VLOOKUP:"一"字之差,难得模糊

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

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

公式说明:

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

LOOKUP:基础用法,简单粗暴

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

公式说明:

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

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

LOOKUP的二分法

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

特别说明:

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

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

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

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

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

目标单元格B2先与第一个二分位E4比较,21;继续与E2的下半区二分位E3进行比较,2

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

目标单元格B3先与第一个二分位E4比较,3=3;此时停止二分法查询,转为连续相等取末查找。自当前二分位E4开始,E4、E5连续等于3,因此返回E5对应的结果F5,即"D"。图中的E3与E4相邻且等于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"。

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

***结束语:

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

****部落窝教育-excel查询函数技巧****

原创:小花/部落窝教育(未经同意,请勿转载)

本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP,阅读全文并永久保存 查看更多类似文章
猜你喜欢
类似文章
【热】打开小程序,算一算2024你的财运
【原创】085 EXCEL区间匹配LOOKUP与VLOOKUP的用法
Vlookup函数实例(全)
LOOKUP万能函数18大用法,与VLOOKUP,IF十几种函数组合无敌,必收藏
数据查找新时代来临!VLOOKUP完美谢幕,介绍一下强大的继任者XLOOKUP!
Vlookup又被虐打,Lookup最经典的8种用法,你全会吗?
Excel中VLOOKUP函数运用基础教程及技巧详解
更多类似文章 >>
生活服务
热点新闻
分享 收藏 导长图 关注 下载文章
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!

联系客服