excel中LOOKUP万能函数18大用法,与VLOOKUP,IF十几种函数组合无敌,必收藏
发布时间:2022-05-22 22:12:15
本文介绍Excel的万能函数LOOKUP的18种用法大全,配合VLOOKUP、INDIRECT、MATCH、IF、LEFT、RIGHT、MID等各种函数的嵌套使用,再加上函数公式逐条超细致解析,详细程度堪比教科书。
▍先了解LOOKUP的二分法查找原理,可以理解为一分为二,一直分到不能再分为止。三个案例图讲述二分法查找原理:图1、图2、图3
图1
图2
图3
▍“二分法”的2个小规律,可以让我们快速口算出LOOKUP的结果。
① 规律1:当查找值足够大,比查找区域的数都大时,匹配的都是最后一个数。比如查找值是20,查找区域是{10,8,16,17,19},LOOKUP匹配的是最后一个数19;当查找值是100,查找区域是{20,30,50,88,66,32},匹配的是最后一个数32。
② 规律2:当查找区域是升序排列时,LOOKUP会从下往上找,第一个等于或小于查找值的数就是最终匹配的数。比如当查找值是100,查找区域是{20,30,50,80,100,100},最终匹配的是最后一个数100;当查找值是100,查找区域是{20,30,50,98,99,101},最终匹配的数是99。
▶所以下面的案例会用到查找值“座”和“9E+307”来匹配最后一个文本和数字。“座”这个字代表超级大的文本,找最后一个文本就用“座”;“9E+307”这个数字代表超级大的数字,找最后一个数字就用“9E+307”。
▍LOOKUP函数和VLOOKUP函数不一样,它不用区分逆向还是正向查询。
说了这么多,LOOKUP的二分法查找可以完成哪些功能?
▼一、利用模糊查找对多级区间快速判定结果,教 IF函数和VLOOKUP函数怎么低调做函数。
公式模板:=LOOKUP(查找值,1行或1列的查找区域,1行或1列的结果区域)。查找区域要升序排列。
◆如图4:求销售额的提成区间,对员工评级,LOOKUP信手捏来。
图4:销售区间员工提成判定,等级评级
如果没有右边的比例表格,公式可以直接写成数组形式,比如求提成比例:D2单元格=LOOKUP(C3,{0;2000;5000;8000;10000},{0.01;0.03;0.05;0.1;0.12}),单元格格式设置成百分比格式就可以了。
◆如图5:对这些员工的身高进行判定,得出他们适合穿什么尺码的衣服,170到174归到170这一档,175到179归到175这一档,依次类推。在D3单元格里输入公式=LOOKUP(C3,$G$3:$M$3,$G$2:$M$2) 。
图5:LOOKUP多区间判定
PK环节:
如果用VLOOKUP函数来完成,只能用VLOOKUP函数的模糊查找功能,而且右边的尺码表还得转置,而且查找区域也要升序排列,这里VLOOKUP完败。
如果是用IF函数来完成,就得输入这么长的公式,7个IF函数嵌套,=IF(C3<165,"XS",IF(C3<170,"S",IF(C3<175,"M",IF(C3<180,"L",IF(C3<185,"XL",IF(C3
▼二 、单条件精确查找
公式模板:=LOOKUP(1,0 / (查找值=1行或1列查找区域),1行或1列结果区域) 。
如图7:通过人名求部门和工号,G2单元格输入公式=LOOKUP(1,0/($B$2:$B$5=$F2),$A$2:$A$5) 。其中 0/($B$2:$B$5=$F2)为查找区域,$A$2:$A$5为结果区域。公式详解看下图
图7:单条件精确查找
▼三 、多条件精确查找
公式模板:=LOOKUP(1,0 / ((查找值1=查找区域1)*(查找值2=查找区域2)*……),1行或1列结果区域) 。
如图8:求薪资,但是有同名的人,所以“部门*人名”就变成唯一性了。H2单元格输入公式 =LOOKUP(1,0/(($F2=$B$2:$B$5)*($G2=$A$2:$A$5)),$D$2:$D$5) 。
其中 ($F2=$B$2:$B$5)*($G2=$A$2:$A$5)乘号*相当于AND函数,要两条件同时满足。$D$2:$D$5为结果区域。公式详解看下图
图8:多条件精确查找
▼四 、制作查询表,可以分类别查找最后一条记录
公式模板:=LOOKUP(1,0 / (查找值=1行或1列查找区域),1行或1列结果区域) 。
如图9:可以在E3单元格输入不同的“产品名称”,就会自动出现对应的“最后出库时间和出库人”。
图9:按要求查最后一条记录
在日期列F2单元格输入公式=LOOKUP(1,0/($E3=$B:$B),$A:$A),公式解析:E3人名在B列出现一次就会显示一个TRUE,不出现就是FALSE;最后查找区域就只剩{0;0;0……},查找值1在一堆的0里找最终结果,因为一堆0可以理解为升序状态,从下往上找第一个小于或等于自身查找值的数就是要的值,所以1模糊匹配最后一个0,最后一个0对应的结果日期就是要的值。
在出库人列G3单元格输入公式=LOOKUP(1,0/($E3=$B:$B),$C:$C),也是同理。公式可以下拉,同时查多个产品的最后出库时间。
▼五 、合并单元格不用取消,正向查找引用,VLOOKUP配合LOOKUP就可做到
公式模板:=VLOOKUP(LOOKUP("座",1列或1行查找区域),多行多列查找区域,结果在第几列,精确查找0) 。
如图10:A列的合并单元格不取消,照样可以引用A10:B13的单价,C3单元格输入公式=VLOOKUP(LOOKUP("座",$A$3:A3),$A$11:$B$13,2,0),再鼠标下拉单元格。公式详解看下图
图10:合并单元格不用取消,照样用公式引用成功
▼六 、合并单元格不用取消,逆向查找引用
公式模板:=LOOKUP("座",INDIRECT("$列或行$数:$列或行"&MATCH(查找值,$列或行$1:$列或行数,0))) 。
如图11:A列合并单元格不用取消,在B3单元格输入公式=LOOKUP("座",INDIRECT("$A$8:$A"&MATCH($A3,$B$1:$B$13,0))),引用成功。公式详解看下图
图11:用LOOKUP和INDIRECT、MATCH函数组合
▼七 、拆分合并单元格并自动填充内容
① 拆分行方向合并单元格并自动填充内容
公式模板:=LOOKUP("座",查找区域) 。
如图12:第3行有合并单元格,是“一月”和“二月”。现在想插入一行,快速将第三行的内容复制下来,同时拆分合并单元格并自动填充原来合并单元格的内容。在A4单元格输入公式=LOOKUP("座",$A$3:A$3)。
图12:LOOKUP行方向自动拆分单元格并复制内容
② 拆分列方向合并单元格并自动填充内容
公式模板:=LOOKUP("座",查找区域) 。
如图13:A列有合并单元格,是“地方名”和“合计”。现在想插入一列,快速将A列的内容复制下来,同时拆分合并单元格并自动填充原来合并单元格的内容。在A4单元格输入公式=LOOKUP("座",$B$4:$B4)。
图13:LOOKUP列方向自动拆分单元格并复制内容
▼八 、通过全称查简称
公式模板:=LOOKUP(9E+307,FIND(简称的查找值,全称的查找区域),结果区域)。
FIND函数的第一参数必须是简称内容,第二参数必须是全称内容。不然会错误。
如图14:在B10单元格输入公式:=LOOKUP(9E+307,FIND(A10,$A$2:$A$6),$B$2:$B$6)。公式详解看下图
图14:通过全称查简称
▼九 、通过简称查全称
公式模板:=LOOKUP(9E+307,FIND(简称的查找区域,全称的查找值),结果区域)。
FIND函数的第一参数必须是简称内容,第二参数必须是全称内容。不然会错误。
如图15:在B3单元格输入公式=LOOKUP(9E+307,FIND($A$10:$A$14,A3),$B$10:$B$14)。公式详解看下图
图15:通过简称查全称
▼十 、查找引用一行或一列的最后一个数字、最后一个文本、最后一个非空内容
公式模板:公式中的$A$1:$F$1可以换成任何需要的区域 。如图16
图16:查最后一个数字、文本、非空内容
▼十一 、提取左边数字
公式模板:= -LOOKUP(1,-LEFT(查找值,ROW($1:$足够大的数字))),最后一定要按CTRL+Shift+回车,三键,不然会出错。公式详解看下图17:
图17:提取左边数字
▼十二 、提取右边数字
公式模板:= -LOOKUP(1,-RIGHT(查找值,ROW($1:$足够大的数字))),最后一定要按CTRL+Shift+回车,三键,不然会出错。公式详解看下图18:
图18:提取右边数字
▼十三 、提取中间数字
公式模板:= -LOOKUP(1,-MIDB(查找值,SEARCHB("?",查找值),ROW($1:$足够大的数字))),最后一定要按CTRL+Shift+回车,三键,不然会出错。公式详解看下图19:
图20:提取中间数字
▼十四 、提取任意位置的数字
公式模板:= -LOOKUP(1,-MID(查找值,MIN(FIND(ROW($1:$10)-1,查找值&1/17)),ROW($1:$足够大的数字))),最后一定要按CTRL+Shift+回车,三键,不然会出错。公式详解看下图20:
图20:提取任意位置的数字
▼十五 、提取排名前几的人员信息
假如要提取排名前3的人员信息,公式模板:=IF(ROW($A1)>3,"",LOOKUP(1,0/(LARGE($B$3:$B$8,ROW($A1))=$B$3:$B$8),A$3:A$8))。$B$3:$B$8是查找区域,A$3:A$8是结果区域,这两个区域以实际要求的内容来定。如图21:详见公式解析图
图21:提取排名前3的人员信息
函数LARGE(数据区域,第几大值),比如第1大值,返回“数据区域中最大的数值”;比如第3大值,返回“数据区域中第3大的数值”。ROW($A1)下拉单元格会变成ROW($A2)、ROW($A3)。
excel中LOOKUP万能函数18大用法,与VLOOKUP,IF十几种函数组合无敌,必收藏的下载地址:
猜你喜欢
- excel顺序的重复数据或有规律的数据可以使用填充来提高速度,间隔行的数据输入,一个个单元格输入效率太低,所以大家都在找寻批量输入的方法。要
- 此前苹果已经为 AirPods 产品线推出了 AppleCare+ 服务计划,用户可付费购买该计划以延长耳机的保修期。今日苹果推出了全新的第
- 你知道吗?在iOS 15中,苹果新增了一个异地恋必备的SharePlay功能。什么是SharePlay?简单说,就是用户可以在FaceTim
- Excel2016中怎么绘制函数图像?Excel2016中想绘制一个函数图像,该怎么绘制呢?下面我们就来看看excel绘制函数图像的实例教程
- Excel表格是我们经常使用的文档,但是这样的表格如果发生损坏的话要怎么处理呢,其实是很简单的,有四种方法。不管是什么软件,都免不了被损坏的
- 本文介绍Microsoft Excel中SUMXMY2函数的语法和用法。函数说明SUMXMY2函数的主要作用是返回两数组中对应数值之差的平方
- 通过使用多级列表模板和 Word 中的内置标题样式(“标题 1”、“标题 2”等),可以向文档内的标题应用编号方案。例如,可以对标题编号,使
- excel中怎么计算贷款的月还款金额?计算是excel的强项,今天我们就以贷款后每个月还款为实例,教大家怎么用excel中pmt函数来计算还
- 以下代码为通过VBA代码来设置Word字体的各种格式。WApp.Selection.Font.NameFarEast = "华文中
- win10系统的局域网让用户更方便共享以及传输文件,通常打开win10网络就能够发现其他电脑,但是有的用户打开后却看不到其他电脑,这问题要怎
- 当我们的计算机升级并安装win10操作系统时,一些小伙伴在上网时会提示当前的网卡不支持win10系统。对于这个问题,边肖认为大多数问题可能是
- Win10系统有很多的小功能,比如任务栏的个性化设置等,可以设置任务栏的颜色变化,让自己的桌面显示更加美观个性化。那么win10怎么设置任务
- iOS 16最引人注目的变化就是重新设计的锁屏屏幕,新的锁屏允许用户定制各种视觉元素,添加锁屏小组件,创造属于个人的完美的锁屏。iOS16锁
- 对PDF转换后的Word文件进行编辑:将Word文档转换之后,默认使用的是受保护的视图,可以使用 启用编辑 来使用编辑功能。1、点击启用编辑
- 许多用户在公司上班的时候,每每到快下班的时候经常会需要发送一些文件,但是又不想等待,这个时候就可以设置一个定时关机,把文件发送给对方,然后电
- 这是设置前的效果Excel使用页眉设置方法打印每一页首先,在打印标题下输入页面布局-页面设置-工作表,单击顶部标题行旁边的按钮,然后选择需要
- 很多朋友会遇到这样的问题,就是很有很多页的数据,少的有几十页,多的可能有几百页,然后需要合并到一个页面做数据分析,如果一页页的复制粘贴的话,
- 每年春节前夕,苹果的智能语音助手 Siri 都会根据春节传统增加一些新的功能。今年也不例外,Siri 学会了牛年春节吉祥话,还有“春节故事”
- word中怎么缩小行距,word2007中的微软雅黑字体行距很明显,行与行之间间隔太大,直接调整行距是没有任何效果的,这个字体有关系,在不改
- 今天一位做系统开发的同事,给我以下样表:由于导入系统需要,需要把每个“档号”的最后一个“-”符号及以后内容的内容去掉(上图中的红色部分)。需