Excel函数学习之LOOKUP函数的5种用法

轻辰君_9649

轻辰君_9649

2022-08-01

29504人浏览

转载

在之前的文章《excel函数学习之聊聊多个求和函数》中,我们学习了几种求和函数。而今天我们来聊聊lookup函数,过去大家使用vlookup函数的时候,经常都会遇到一些麻烦事,那就是vlookup查找只找到满足条件的第一个值就不找了,并且还只能从左往右查找,简直是太多限制了。今天给大家介绍lookup函数,可以完美的解决这两个问题,太好用了!

LOOKUP函数非常强大,有引用函数之王的称号。要想完全掌握它,必须了解它的5种常见用法和它的二分法查找原理。由于相关知识点比较多,所以教程将分成上下两篇。今天我们首先通过五个例子来了解这个函数的5种常见用法。明天我们再来说二分法查找原理和之前文章中遗留的两个LOOKUP问题。

一、常规引用

格式:LOOKUP(查找值,查找区域)

例1:根据姓名查找语文成绩,公式为=LOOKUP(H2,C2:D19)

1.png

例2:根据姓名查找英语成绩,公式为:=LOOKUP(H2,C2:F19)

2.png

通过这两个例子我们可以发现,LOOKUP在进行查找时公式的结构非常简单,查找值和要找的结果分别位于查找区域的首列和末列。

但是仅仅了解这个用法是远远不够的,如果我们再试一个数据的话,有可能就会发现问题:

3.png

当查找姓名变成赵永福的时候,结果就不对了,这是因为LOOKUP函数使用的是二分法查找,也就是模糊匹配,关于这一点,我们将在明天的教程《LOOKUP函数用法全解(下)——LOOKUP的二分法查找原理》中详细解释。

因此,在使用LOOKUP进行常规查找的时候,有一个非常重要的步骤,就是按照查找内容(姓名所在的c列)升序排序。

4.png

当我们排序以后,公式的结果立刻变成了正确的,是不是很神奇!

5.png

这又引出了一个新的问题,如果数据不能排序的话,LOOKUP函数还能用吗?

肯定能用啊,下面来看看LOOKUP函数的第二种用法。

二、精确查找的套路

格式:=LOOKUP(1,0/(查找范围=查找值),结果范围)

在I2单元格输入公式:

=LOOKUP(1,0/(C2:C19=H2),D2:D19),回车,可看到正确结果。

6.png

关于这个套路的1和0/到底是什么意思,也是提问率最高的问题之一,在未讲解二分法原理之前,简单来说一下公式的意思。1就是要查找的值,但是条件变了,不是直接查找姓名,而是根据姓名得到的一组逻辑值:

7.png

注意这里只有一个TRUE,也就是我们要找的姓名。

接下来用0除以这些逻辑值,在进行计算的时候逻辑值TRUE代表1,FALSE代表0,当分母为0也就是FALSE的时候,计算结果是错误值:

8.jpg

因此,LOOKUP的工作就变成了在一组数据中找1。由于这组数据只有一个0,其他都是错误值,二分法使得LOOKUP只能找到不大于查找值的最后一个数字,因此只能找到0,最后根据0的行位置(第2行)得到第三参数对应位置的数据,即D2就是我们需要的结果。

这部分内容算是函数学习中比较有难度的知识点了,初学者可能理解困难,这不要紧,随着学习的深入,当对数组和逻辑值这两大要点掌握比较熟练的时候,这些内容就很好理解了。目前如果不能完全理解,记住这个套路就行:=LOOKUP(1,0/(查找范围=查找值),结果范围)。同时这个套路还能延伸出多条件精确查找用法:

=LOOKUP(1,0/((查找范围1=查找值1)* (查找范围2=查找值2)* (查找范围3=查找值3)),结果范围)

就是在每个查找范围内找到要找的值,得到的逻辑值相乘后同时符合多个条件的位置就是1,原理与单条件的一样。

9.png

三、反向查找的套路

Excel公式工具(专业版)
Excel公式工具(专业版)

从自然语言描述生成Excel公式,诊断表格错误,支持VLOOKUP、条件求和等常用函数。

下载

与我们熟知的VLOOKUP不同,使用LOOKUP函数进行反向查找时非常简单,公式结构为:

=LOOKUP(查找值,查找列,结果列),下面这个例子是按照姓名排序后再反向查找的效果:

10.png

如果数据不能排序的话,使用精确查找的套路:=LOOKUP(1,0/(C2:C19=H8),B2:B19)

11.png

四、按区间查找的套路

根据学生的总分给出相应的评语。50分以下的为“很差”,50-100分的为“差”,100-150分的为“一般”,150-200分的为“较好”,200-250分的为“优秀”,250分及以上的为“能手”。

12.png

这里用的公式为:

=LOOKUP(G2,{0,50,100,150,200,250;"很差","差","一般","较好","优秀","能手"})

按照评语的要求分成了六个等级,如果用if函数去做就很啰嗦,使用LOOKUP处理这类问题非常方便,公式结构也很简单:

=LOOKUP(分数值,{下限1, 下限2……;评语1,评语2……})

在写这个公式的时候注意两点:

  • 1.LOOKUP的第二参数使用了常量数组,这里的大括号是手动输入的,括号内用一个分号分开,左边是每个等级的下限,例如50分以下这个表述里下限就是0,50-100的下限就是50,以此类推,每个数字之间用逗号分开;分号右边是对应的评语,评语应当使用引号,同时用逗号分开(公式里的所有符号都是英文状态下的);

  • 2.数字区间应当遵循升序的排列顺序,否则结果就会错误。

五、关于数据排序的重要性

13.png

当我们按照学号查找姓名的时候,发现会出现错误,学号也是按升序排列的啊,怎么会错?

这是一种最常见的错误,这里的学号升序排列只是我们感觉如此而已,实际上升序的效果是这样的:

14.png

在使用LOOKUP的时候,如果不使用精确查找的套路,切记一定要排序才能保证公式结果的正确性。

小结

1、今天一共分享了五种LOOKUP的使用套路,分别是常规查找、精确查找、多条件查找,反向查找还有按区间查找,初学者掌握这些套路学会去套用解决问题就可以了;

2、LOOKUP函数的查找原理与我们之前学过的VLOOKUP不同,VLOOKUP函数的查找方式叫做遍历法,找到满足条件的第一个值就会停止查找,而LOOKUP函数使用的是二分法原理进行查找,要找到满足条件的最后一个值才会停止查找,这一点在函数的说明文档里也提到了;

15.png

我们可以通过一个简单的测试来验证这一点:

16.png

3、对数据源按升序排列这一点很重要:

17.jpg

如果不能排序,那么一定要使用这个套路:=LOOKUP(1,0/(查找范围=查找值),结果范围),这也是二分法的特性决定的;

4、LOOKUP这个函数很强大,同时也很难以理解,要想彻底弄清楚这个函数,必须了解二分法原理。明天的教程我们就来聊聊什么是二分法原理,顺便再把前一段时间遗留的两个问题(LOOKUP解决四舍五入的问题和进行数据提取的问题)做个解释。

相关学习推荐:excel教程

相关专题

更多
java基础知识汇总
java基础知识汇总

java基础知识有Java的历史和特点、Java的开发环境、Java的基本数据类型、变量和常量、运算符和表达式、控制语句、数组和字符串等等知识点。想要知道更多关于java基础知识的朋友,请阅读本专题下面的的有关文章,欢迎大家来php中文网学习。

2023.10.24

6444

49

excel对比两列数据异同
excel对比两列数据异同

Excel作为数据的小型载体,在日常工作中经常会遇到需要核对两列数据的情况,本专题为大家提供excel对比两列数据异同相关的文章,大家可以免费体验。

2023.07.25

6281

7

excel重复项筛选标色
excel重复项筛选标色

excel的重复项筛选标色功能使我们能够快速找到和处理数据中的重复值。本专题为大家提供excel重复项筛选标色的相关的文章、下载、课程内容,供大家免费下载体验。

2023.07.31

3996

5

excel复制表格怎么复制出来和原来一样大
excel复制表格怎么复制出来和原来一样大

本专题为大家带来excel复制表格怎么复制出来和原来一样大相关文章,帮助大家解决问题。

2023.08.02

3780

3

excel表格斜线一分为二
excel表格斜线一分为二

在Excel表格中,我们可以使用斜线将单元格一分为二。本专题为大家带来excel表格斜线一分为二怎么弄的相关文章,希望可以帮到大家。

2023.08.02

1444

3

excel斜线表头一分为二
excel斜线表头一分为二

excel斜线表头一分为二的方法有使用合并单元格功能方法、使用文本框功能方法、使用自定义格式方法。本专题为大家提供excel斜线表头一分为二相关的各种文章、以及下载和课程。

2023.08.02

737

5

绝对引用的输入方法
绝对引用的输入方法

绝对引用允许在公式中引用一个固定的单元格,而不会随着公式的复制和粘贴而改变引用的单元格。本专题为大家提供绝对引用相关内容的文章,大家可以免费体验。

2023.08.09

5168

7

java导出excel
java导出excel

在Java中,我们可以使用Apache POI库来导出Excel文件。本专题提供java导出excel的相关文章,大家可以免费体验。

2023.08.18

8323

6

excel输入值非法
excel输入值非法

在Excel中,当输入的数值非法时,有以下多种处理方法。本专题为大家提供excel输入值非法的相关文章,大家可以免费体验。

2023.08.18

1921

3

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程
Excel 教程
Excel 教程

共162课时 | 45.9万人学习

成为PHP架构师-自制PHP框架
成为PHP架构师-自制PHP框架

共28课时 | 3.7万人学习