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

来源:优页文档 作者:优页文档

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

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

ROUND 04  区间查询


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

图片图3.1:区间查询——IF函数嵌



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

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

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

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

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

图片

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

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

图片

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

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

=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开始,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小胜!

图片

结束语


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


资讯来源说明:本文章来自网络收集,如侵犯了你的权益,请联系:puerppt#163.com进行删除。

PPT模板

  • 蓝色卡通蓝色防溺水安全教育PPT
  • 蓝色儿童安全教育学生防溺水主题PPT
  • 蓝色中小学防溺水安全教育PPT
  • 重温入党誓词党史学习教育党课PPT
  • 微党课重温入党誓词永远忠诚于党PPT
  • 入党誓词宣誓入党仪式背景PPT
  • 纪律处分条例pptPPT
  • 党课党建主题班会纪律处分条例pptPPT
  • 党课党员教育管理工作条例PPT
  • 预防溺水pptPPT

Excel模板

  • 单据粘贴单11
  • 出差申报单11
  • 出差申报单(2)1
  • 付款申请单11
  • 付款申请单(3)1
  • 入库单1
  • 领料单
  • 销售明细单1
  • 销售明细单(3)
  • 付款申请单(2)1

Word模板

  • 面试招聘
  • 社会招聘面试审批流程
  • 电话营销销售人员的招聘面试流程
  • 求职招聘登记表及面试记录表
  • 校园招聘面试结果评价表
  • 校园招聘求职者面试报告示范模板
  • 招聘面试评分记录表
  • 招聘面试评估表
  • 招聘面试评价表
  • 招聘面试记录表
优页文档

优页文档(www.youyedoc.com)是一家专注于分享高质量的PPT模板、Excel表格、Word模板的下载网站,1000+各行业优质设计师每日更新200+优质办公文档模板,满足各行业办公需求。海量office文档制作教程,致力于打造国内最大最权威的办公文档下载一站式服务平台

Copyright © 2021-2024 www.youyedoc.com. All Rights Reserved.   粤ICP备2021116258号

本站所有文档资源来源于互联网或作者上传,仅供学习研究使用,版权归作者所有,请勿用于商业用途,如果用于商业用途请联系作者,如果因为您将本站资源用于其他用途而引起的纠纷,本站不负任何责任。

如果本站内容无意中侵犯了您的版权,请联系youyedoc,我们会及时处理。