到底这LOOKUP函数有什么玄机?我看完才懂
发布时间:2023-06-30 05:21:35
LOOKUP函数非常强大,有引用函数之王的称号。要想完全掌握它,必须了解它的5种常见用法和它的二分法查找原理。由于相关知识点比较多,所以教程将分成上下两篇。今天我们首先通过五个例子来了解这个函数的5种常见用法,后续我们再来说说二分法查找原理。
1、常规引用
格式:LOOKUP(查找值,查找区域)
例1:根据姓名查找语文成绩,公式为:
=LOOKUP(H2,C2:D19)
例2:根据姓名查找英语成绩,公式为:
=LOOKUP(H2,C2:F19)
通过这两个例子我们可以发现,LOOKUP在进行查找时公式的结构非常简单,查找值和要找的结果分别位于查找区域的首列和末列。
但是仅仅了解这个用法是远远不够的,如果我们再试一个数据的话,有可能就会发现问题:
当查找姓名变成赵永福的时候,结果就不对了,这是因为LOOKUP函数使用的是二分法查找,也就是模糊匹配,关于这一点,我们将在后续的教程中详细解释。
因此,在使用LOOKUP进行常规查找的时候,有一个非常重要的步骤,就是按照查找内容(姓名所在的C列)升序排序。
当我们排序以后,公式的结果立刻变成了正确的,是不是很神奇!
这又引出了一个新的问题,如果数据不能排序的话,LOOKUP函数还能用吗?
肯定能用啊,下面来看看LOOKUP函数的第二种用法。
2、精确查找的套路
格式:=LOOKUP(1,0/(查找范围=查找值),结果范围)
在I2单元格输入公式:
=LOOKUP(1,0/(C2:C19=H2),D2:D19)回车,可看到正确结果。
关于这个套路的1和0/到底是什么意思,也是提问率最高的问题之一,在未讲解二分法原理之前,简单来说一下公式的意思。1就是要查找的值,但是条件变了,不是直接查找姓名,而是根据姓名得到的一组逻辑值:
注意这里只有一个TRUE,也就是我们要找的姓名。
接下来用0除以这些逻辑值,在进行计算的时候逻辑值TRUE代表1,FALSE代表0,当分母为0也就是FALSE的时候,计算结果是错误值:
因此,LOOKUP的工作就变成了在一组数据中找1。由于这组数据只有一个0,其他都是错误值,二分法使得LOOKUP只能找到不大于查找值的最后一个数字,因此只能找到0,最后根据0的行位置(第2行)得到第三参数对应位置的数据,即D2就是我们需要的结果。
这部分内容算是函数学习中比较有难度的知识点了,初学者可能理解困难,这不要紧,随着学习的深入,当对数组和逻辑值这两大要点掌握比较熟练的时候,这些内容就很好理解了。目前如果不能完全理解,记住这个套路就行:
=LOOKUP(1,0/(查找范围=查找值),结果范围)。同时这个套路还能延伸出多条件精确查找用法:
=LOOKUP(1,0/((查找范围1=查找值1)* (查找范围2=查找值2)* (查找范围3=查找值3)),结果范围)
就是在每个查找范围内找到要找的值,将得到的逻辑值相乘后同时符合多个条件的位置就是1,原理与单条件的一样。
3、反向查找的套路
与我们熟知的VLOOKUP不同,使用LOOKUP函数进行反向查找时非常简单,公式结构为:
=LOOKUP(查找值,查找列,结果列),下面这个例子是按照姓名排序后再反向查找的效果:
如果数据不能排序的话,使用精确查找的套路:
=LOOKUP(1,0/(C2:C19=H8),B2:B19)
4、按区间查找的套路
根据学生的总分给出相应的评语。50分以下的为“很差”,50-100分的为“差”,100-150分的为“一般”,150-200分的为“较好”,200-250分的为“优秀”,250分及以上的为“能手”。
这里用的公式为:
=LOOKUP(G2,{0,50,100,150,200,250;"很差","差","一般","较好","优秀","能手"})
按照评语的要求分成了六个等级,如果用if函数去做就很啰嗦,使用LOOKUP处理这类问题非常方便,公式结构也很简单:
=LOOKUP(分数值,{下限1,下限2……;评语1,评语2……})
在写这个公式的时候注意两点:
1.LOOKUP的第二参数使用了常量数组,这里的大括号是手动输入的,括号内用一个分号分开,左边是每个等级的下限,例如50分以下这个表述里下限就是0,50-100的下限就是50,以此类推,每个数字之间用逗号分开;分号右边是对应的评语,评语应当使用引号,同时用逗号分开(公式里的所有符号都是英文状态下的);
2.数字区间应当遵循升序的排列顺序,否则结果就会错误。
5、关于数据排序的重要性
当我们按照学号查找姓名的时候,发现会出现错误,学号也是按升序排列的啊,怎么会错?
这是一种最常见的错误,这里学号的升序排列只是我们感觉如此而已,实际上升序的效果是这样的:
在使用LOOKUP的时候,如果不使用精确查找的套路,切记一定要排序才能保证公式结果的正确性。
小结
1、今天一共给大家分享了五种LOOKUP的使用套路,分别是常规查找、精确查找、多条件查找,反向查找还有按区间查找,初学者掌握这些套路学会去套用解决问题就可以了;
2、LOOKUP函数的查找原理与我们之前学过的VLOOKUP不同,VLOOKUP函数的查找方式叫做遍历法,找到满足条件的第一个值就会停止查找,而LOOKUP函数使用的是二分法原理进行查找,要找到满足条件的最后一个值才会停止查找,这一点在函数的说明文档里也提到了;
我们可以通过一个简单的测试来验证这一点:
3、对数据源按升序排列这一点很重要:
如果不能排序,那么一定要使用这个套路:=LOOKUP(1,0/(查找范围=查找值),结果范围),这也是二分法的特性决定的;
4、LOOKUP这个函数很强大,同时也很难以理解,要想彻底弄清楚这个函数,必须了解二分法原理。在后续的教程中,我们将根据二分法原理做一个详细的解释。
到底这LOOKUP函数有什么玄机?我看完才懂的下载地址:
猜你喜欢
- Excel单元格数据显示成百分比形式,最最常规的做法就是设置单元格格式,然后在选择百分比。其实还有一个更好用的实现Excel单元格内容转为百
- 今天我们介绍的excel中怎么让小数点居中就是一个很不错的技巧。它是如何实现的呢?下面我们就来学习一下。首先打开一个Excel表格,我们看到
- 最近两天,许多Windows电脑用户都收到了Win10的最新更新推送,很多用户也选择了将系统进行更新。但是,目前有用户反应,自己的电脑在更新
- excel表格中的数据怎么使用多条件排序?excel表格中的数据想要排序,不只地按照一个关键词,想在年龄从大到小的基础上按工资的多到少排序,
- 有时候在使用Win10系统的电脑时,会突然提示音频服务未响应,然后电脑就没有声音,检查了电脑也不知道是哪里出现了问题,那么碰到这种情况应该怎
- Word文档常用工具栏中的“显示比例”选择框是用于缩放显示页面的。您可以在Word应用中点击上方菜单栏中的“视图”选项,然后点击“显示比例”
- ①启动Excel2010,单击视图--全部重排。 ②这时弹出一个小窗口,我们选择重排的方式,例如,我选择的就是垂直
- 本章教大家如何在Excel 2010中进行自定义筛选以及依次编号,这些对我们以后使用Excel表格会有很大的帮助前面一篇教程我们介绍了如何进
- 分类汇总是通过使用SUBTOTAL函数与汇总函数(例如“求和”或“平均值”等)一起计算得到的。可以为每列显示多个汇总函数类型。插入分类汇总的
- 本篇文章向大家介绍word在奇偶页怎么设置不同的页眉方法,教程真的很不错,推荐到脚本之家,一起来看看吧我们在编辑论文时,有时候想奇数页页眉显
- 我们在使用Excel表格进行办公的时候,经常会在第一列输入很多序号。一般只需要拖动填充即可,但如果序号太多就显得很困难,一起来了解一下如何快
- 对于office的无法安装的解决错误表示:office ran into a problem becauce its system serv
- 职场办公,我们经常要和什么办公软件打交道呢?当然是Word了。相信大家都会在Word里面打字,但是光会这个还不够,想要进一步提高我们的工作效
- 在工作中,经常使用Excel来进行数据的记录及分析,在进行数据分析时,如何做得数据一目了然?如在本文的教程中,怎么快速快速显示销售额的波动趋
- PDF提示文件名与真实的文件名怎么办?PDF文件打开发现窗口显示的名字和真实的文件名不一致,该怎么办呢?下面我们就来看看详细的教程,需要的朋
- 我们知道在Excel单元格中输入当前时间的快捷键是“Ctrl+Shift+;”,但是这种输入方法只能显示小时和分钟,即HH:MM格式的时间。
- 我们在excel中输入一些公式的时候,经常需要用到角标,角标是如何输入到单元格中的呢?其实输入方法不难,下面就跟小编一起看看excel表格输
- 现在很多用户经常外出的都喜欢轻便一点的电脑,所以这类人群为自己选择的电脑就会是笔记本电脑。使用笔记本电脑有一个特点就是如您合上电脑屏幕,它就
- 现在有很多用户都在使用Word来进行一些文档的编辑,而我们在编辑文档的时候难免会需要对某一段重点内容进行标记,这时候就可以使用红色框突出,那
- 使用电脑的过程中难免会遇到一些问题,最常见的就是电脑开关机问题,有用户反映win10电脑关机又自动开机,这是怎么回事?重试了几次还是一样。主