同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍 Lookup函数最全详解 图文

2020年6月29日14:14:36 评论 122 views
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页

VLOOKUP函数即使很强大,但是也有局限性,有些查找无法实现。今天,跟大家分享一个万能查找函数:LOOKUP。

VLOOKUP函数可以实现的查找LOOKUP也可以,VLOOKUP函数不能实现的查找LOOKUP依然可以,这就是它的与众不同之处。

下面跟大家讲讲LOOKUP函数的几种用法:

一、LOOKUP函数语法。

作用:从单行或单列或数组中查找一个值,条件是向后兼容性。分为向量形式和数组形式。

同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍

1、向量公式。

= LOOKUP(lookup_value,lookup_vector,[result_vector])。

= LOOKUP(查找值,查找区域,返回结果区域)。

2、数组公式。

= LOOKUP(lookup_value,array)。

= LOOKUP(查找值,数组)。

参数说明:

lookup_value:查找值。必需。可以是对单元格的引用、数字、文本、名称或逻辑值。

lookup_vector:查找区域。必需。只能是一行或一列。可以是对单元格引用、数字、文本、名称或逻辑值,文本不区分大小写。

result_vector:返回的结果区域。可选。只能是一行或一列,且与查找区域大小要相同。

array:行和列中值的集合。必需。可以是对单元格的引用、数字、文本、名称或逻辑值,文本不区分大小写。数组的值必须按升序排列。

注意:

(1)lookup函数查询方式,采用二分法查询。

(2)lookup_vector和array必须按升序排序,否则不能返回正确的结果。

(3)如果找不到查找值,lookup函数会返回小于或等于查找值的最大值。

(4)如果查找值小于查找区域的最小值,lookup函数会返回 #N/A 错误。

(5)如果数组的列数大于行数,则lookup函数会在第一行中查找要找的值。如果数组的行数大于列数,则lookup函数会在第一列中查找要找的值。

二、单条件查找:根据姓名查找测评总分。

要求:根据下图中C11单元格的姓名查找与之对应的测评总分。

方法一:使用lookup向量形式。

具体操作步骤如下:

1、首先,我们要查找的姓名所在的查找区域为B2:B10,所以先选中B2:B10单元格区域 -- 点击菜单栏的“数据”选项卡 -- 在“排序和筛选”工具组中点击“升序”按钮。

同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍

2、在C12单元格中输入公式“=LOOKUP(C11,B2:B10,E2:E10)”-- 按回车键即可。

同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍

3、动图演示如下。

同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍

【公式解析】上述公式中的C11为查找值,B2:B10为查找区域,E2:E10为返回结果区域,并且查找区域按升序排列。

方法二:使用lookup数组形式。

具体操作步骤如下:

1、选中B2:E10单元格区域 -- 点击菜单栏的“数据”选项卡 -- 在“排序和筛选”工具组中点击“升序”按钮。

同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍

2、在C12单元格中输入公式“=LOOKUP(C11,B2:E10)”-- 按回车键即可。

同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍

3、动图演示如下。

同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍

【公式解析】上述公式中C11为查找值,B2:E10为数组集合。查找之前必须先对数组进行升序排序。

三、单条件逆向查找:根据姓名查询学号。

要求:根据下图中C11单元格的姓名查找与之对应的学号。

具体操作步骤如下:

1、在C12单元格中输入公式“=LOOKUP(1,0/($B$2:$B$10=C11),$A$2:$A$10)”-- 按回车键即可。

同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍

2、动图演示如下。

同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍

【公式解析】($B$2:$B$10=C11)判断这个区域中是否等于“蔡晓丽”,如果是,返回TRUE,否则,返回FALSE。TRUE=1,FALSE=0。利用LOOKUP的特性(忽略错误值),所以用0除TRUE和FALSE,0/($B$2:$B$10=C11)满足条件返回0,不满足返回错误值。公式变成“=LOOKUP(1,0/{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;},$A$2:$A$10)”,可以看到以上公式的数组中只有一个0,其他都是错误值。二分法使得LOOKUP只能找到不大于查找值的最后一个数字,因此只能找到0,最后根据0的行位置(第8行)得到第3个参数对应位置的数据,即A8就是我们想要的结果。

四、多条件查找:根据姓名和系院查找专业。

要求:根据下图中C11单元格的姓名与C12单元格的系院查找与之对应的专业。

具体操作步骤如下:

1、选中C13单元格 -- 在编辑栏中输入公式“=LOOKUP(1,0/(($B$2:$B$10=C11)*($C$2:$C$10=C12)),$D$2:$D$10)”-- 按回车键即可。

同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍

2、动图演示如下。

同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍

五、提取单元格内的数字。

要求:在B2:B4单元格中提取A2:A4单元格里面的数字。

具体操作步骤如下:

1、选中B2单元格 -- 在编辑栏中输入公式“=-LOOKUP(1,-LEFT(A2,ROW($1:$4)))”-- 按回车键回车。

同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍

2、鼠标移到B2单元格右下角出现“”字符号时往下拖动单元格至B5,即可提取A2:A5单元格区域里面的数字。

同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍

3、动图演示如下。

同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍

【公式解析】LEFT函数是从左往右开始提取,第2个参数使用的是数组形式,ROW($1:$4)相当于{1,2,3,4},所以公式“LEFT(A2,ROW($1:$4))”相当于{"4";"4个";"4个馒";"4个馒头"},在LEFT前面加上负号,就可以把其中的数字转为数值,文字变成错误值。-LEFT(A2,ROW($1:$4))就相当于{-4;#VALUE!;#VALUE!;#VALUE!}。错误值被LOOKUP忽略,所以公式就变成=-LOOKUP(1,{-4})。根据LOOKUP函数的二分法查找原理,当查找值大于查找区域的所有数据时,返回最后一个值,这里只有一个“-4”,所以返回“-4”,在LOOKUP前面加个负号,就变成4,也就是我们要提取的数字。

六、查询某一列中的最后一个数值。

要求:查找下图中A列的最后一个数值。

具体操作步骤如下:

1、选中B2单元格 -- 在编辑栏中输入公式“=LOOKUP(9E+307,A:A)”-- 按回车键即可。

同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍

2、动图演示如下。

同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍

【公式解析】9E+307是Excel中允许键入的最大数值,用它来做LOOKUP函数的查找值,可以返回某一行或某一列中的最后一个数值。

七、查询某一列中最后一个单元格的内容。

要求:查找下图中A列的最后一个数值。

具体操作步骤如下:

1、选中B2单元格 -- 在编辑栏中输入公式“=LOOKUP(1,0/(A:A<>""),A:A)”-- 按回车键即可。

同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍

2、动图演示如下。

同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍

【公式解析】(A:A<>"")判断A列的值不等于空,如果等于空就返回。

八、查找某一列中最后一次出现的数据。

要求:查找下图中D列的姓名在A列区域中最后一次出现的打卡时间。

具体操作步骤如下:

1、选中E2单元格 -- 在编辑栏中输入公式“=LOOKUP(1,0/($A$2:$A$10=D2),$B$2:$B$10)”-- 按快捷键回车。

同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍

2、看到上图中返回的结果是一串小数,我们需要转换成日期格式。选中E2单元格 -- 点击鼠标右键 -- 选择“设置单元格格式”。

同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍

3、弹出“设置单元格格式”对话框 -- 在“数字”选项卡下点击“自定义”-- 在“类型”输入框中选择“yyyy/m/d h:mm:ss”-- 点击“确定”按钮。

同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍

4、可以看到那一串浮点数变成日期格式的文本。这就是我们用公式求出的最后一次打卡的时间。

同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍

5、动图演示如下。

同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍

九、区间判断。

要求:判断下图中B列销售业绩对应的级别。级别判断标准为单元格区域E2:F6。

具体操作步骤如下:

1、选中C2单元格 -- 在编辑栏中输入公式“=LOOKUP(B2,$E$2:$F$6)”-- 按回车键回车。

同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍

2、鼠标移到C2单元格右下角并双击,即可求出C3:C6单元格区域的结果。

同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍

3、动图演示如下。

同样是Excel中的查找函数,这个函数却比VLOOKUP功能强100倍

以上就是我们工作中经常会用到的几种LOOKUP函数的用法,很多人都说一看就会,一做就不会。其实你只要记住下面这个套路就可以:

=LOOKUP(1,0/((条件1)*( 条件2)* ( 条件N)),目标区域或数组)。

希望你看完这篇文章的时候,可以帮忙转发点个赞,毕竟这么详细的教程,网上真的找不到第2篇!

 

本文已取的原作者授权 禁止转载 原文链接为https://www.toutiao.com/item/6714474147495281156/

  • 郑广学老师微信号
  • EXCEL880B
  • weinxin
  • 我的微信公众号
  • EXCEL880
  • weinxin

发表评论

您必须才能发表评论!