XLOOKUP函数与VLOOKUP函数使用方法以及实例分析
发布时间:2023-06-05 18:41:07
从9个方面解读XLOOKUP函数与VLOOKUP/LOOKUP函数之间的用法差异,包括:基础语法、横向查找、纵向查找、一对多、返回多值、反向查找、多条件查找、模糊查找、近似查找、匹配最后一个等
XLOOKUP函数与VLOOKUP函数使用方法以及实例分析。从9个方面解读XLOOKUP函数与VLOOKUP/LOOKUP函数之间的用法差异,包括:基础语法、横向查找、纵向查找、一对多、返回多值、反向查找、多条件查找、模糊查找、近似查找、匹配最后一个等等。还有不明白的朋友一起详细了解下吧!
1、XLOOKUP基础语法
在学习任何一个函数之前,需要了解这个函数的基础语法,从微软官方的帮助文档里找到了这个函数的参数语法,共计有5个参数,跟LOOKUP的参数非常接近,但是使用起来会更加简单了一些。
其中第1~3个参数跟LOOKUP的参数非常接近,都是将「查找区域」和「结果区域」全部独立出来了,跟VLOOKUP的「选择区域」就有所差异,拆分出来会让函数更加灵活。
第4个参数match_mode表示匹配类型,可以使用「精确匹配」「通配符匹配」「2种近似匹配」,默认为0表示精确匹配。
第5个参数search_mode是新增的一个参数,表示搜索模式,在传统的VLOOKUP/LOOKUP系列函数中,搜索只能从上往下,并且寻找第2个、最后1个数值的时候会非常麻烦。而XLOOKUP就直接引进了这个参数,默认为1表示从上往下开始搜索,-1表示从下往上搜索,这2个会用的比较多一些。
至此,XLOOKUP的语法就初步了解了,接下来来实操下这个XLOOKUP函数有多强大!
2、基础纵向查询
例如下图查找“工号的电脑销售额”,在基础操作上VLOOKUP和XLOOKUP没有什么太大的差异,写法都非常简单,只是XLOOKUP将选择区域和返回区域拆分出来单独写了而已。
来总结下XLOOKUP基础纵向查询的套路:
结论:双方平均(VLOOKUP公式会简洁一点点,不过差异不大)
3、基础横向查询
既然能纵向查询,那么横向查询其实也是可以的。然而在VLOOKUP中,如果要实现横向查询会非常麻烦,又是需要构建虚拟数组。在这里我们使用INDEX+MATCH来代替VLOOKUP实现「基础横向查询」。
而使用XLOOKUP就非常简单了,所有操作都跟纵向查询没有任何差异,XLOOKUP会自动识别是什么方向,例如将上面的数据横放了,变成这个样子:
在横向查询中XLOOKUP完爆VLOOKUP,XLOOKUP的两个方向查询用法一模一样,没有任何差异,太智能了。
来总结下XLOOKUP横向查询的公式套路:
结论:XLOOKUP完爆VLOOKUP,因为VLOOKUP实现非常麻烦。
4、反向匹配查询
在前面的语法中,我们说到XLOOKUP将「查找区域」和「结果区域」全部独立出来了,所以在这里的话,反向匹配对于XLOOKUP来说没有什么障碍,相反VLOOKUP就需要去构建一个IF虚拟数组来实现了。
来感受下2个函数的用法:
VLOOKUP是通过IF({1,0},XXX,XXX)的方式构建一个虚拟数组来实现这个功能的,因为检索关键字必须在选择区域的第一列,对于新手来说理解非常不友好,而且复杂的数组公式还会消耗大量的计算机资源,XLOOKUP依然是这么牛逼完爆,格式没有什么变化,轻松完成反向查询:
结论:XLOOKUP完爆VLOOKUP,基础语法即可实现。
5、多条件查找
多条件查找是很多VLOOKUP的初学者的噩梦,需要写非常复杂的IF数组公式,而且又要注意定位引用的方式,而XLOOKUP使用起来就非常舒服了,只需要将多个条件利用&符号拼接起来就ok。
彻底告别复杂公式:
相信很多同学看到了VLOOKUP多条件查询都是一脸懵逼,IF到底是什么鬼,怎么老是出现?其实这里还是构建了一个虚拟数组,数组公式对于新手来说理解起来的确太困难。
将绝对定位去掉,我们来看下XLOOKUP公式究竟有多简洁,丝毫不拖泥带水:
=XLOOKUP(F4&G4, B4:B17&C4:C17, D4:D17, 0, 1)
总结XLOOKUP多条件查询公式套路:
结论:XLOOKUP再次完爆VLOOKUP,简洁速度快!
6、模糊查询匹配
VLOOKUP和XLOOKUP均支持模糊匹配,在Excel中使用模糊匹配需要用到通配符(*、?、~),这次两个函数不相上下,XLOOKUP只需要将第4个参数修改成2表示通配符匹配即可。
两个函数不相上下,因为这个功能比较简单,总结下XLOOKUP的模糊查询公式套路:
结论:XLOOKUP和VLOOKUP不相上下,因为都比较基础
7、匹配最后一个值
在某些情况下,我们需要找到记录里的最后一条数据,而恰好XLOOKUP的最后一个参数是搜索模式,只要我们将第5个参数search_mode修改成-1,就会倒序查找,这样就能找到最后一个数值了,非常简单。
而VLOOKUP本身实现匹配最后一个值非常麻烦,这里我们使用LOOKUP来代替:
LOOKUP的写法就非常难理解了,又是用0除,又是做逻辑符号判断等于的。对于新手实在太不友好,相比之下XLOOKUP的写法就非常简单了。直接将搜索模式一改就ok了,这个功能在人事应用中非常广,找到最后一次打卡时间和第一次打卡时间,非常简单。
套路总结:
结论:XLOOKUP完爆VLOOKUP/LOOKUP,写法简洁,计算快
8、查找多个值
查找多个值无论是利用VLOOKUP还是LOOKUP实现起来都非常麻烦,因为需要构建一个IF虚拟数组,而XLOOKUP就很便捷了。还记得我们的第一个参数叫“检索关键字”么?我们只需要将这个参数选中想要查找的多个值就ok了。
当然的话,一般查找多个值会对这些值做一个聚合运算,例如找到最大值、平均值、最小值等等。例如下方找到3个员工的销售额最大值,就非常简单:
而VLOOKUP又是要构建一个非常复杂的数组公式,甚至还用到了T函数,理解起来真是太吃力了。总结套路:
结论:XLOOKUP再次完爆VLOOKUP,写法非常简单。
9、查找返回多列
上面一个是查找多个值,对这个命题扩展下,就可以得到返回多列。在XLOOKUP函数中返回多列套路也非常简单,只需要将「结果区域」选择多列就ok。而VLOOKUP需要使用ROW或者COLUMN函数才能实现。
例如想找到某个工号的平均销售额,这里需要同时返回电脑和手机的销售额:
整体来说XLOOKUP函数比VLOOKUP函数理解起来更加简洁,因为VLOOKUP函数使用了ROW作为辅助函数,对于新手来说,理解起来需要一定的门槛,总结:
当然的 XLOOKUP函数的用法还有非常多种,例如还可以通过修改第4个参数实现「近似匹配-包含/不包含最小值」,还可以利用XLOOKUP函数代替MATCH+INDEX实现筛选功能,甚至连Offset这个动态构数函数都可以代替。
猜你喜欢
- 相信很多用户在使用电脑的时候,经常会发现出现在系统左下角总会展示一个搜索框,虽然能够帮助用户快速的查询各种数据,但也有用户觉得不实用,想要删
- 有不少用户还经常会将重要的文件和数据拷贝在U盘中,但最近有部分用户在插入U盘的过程中发现电脑没有任何的反应,导致无法正常读取,那么当我们遇到
- 相信有很多用户在使用Win10系统的电脑时候都经常会用到个性化和显示设置这两个功能,但最近有部分用户在使用的过程中发现自己无法正常的打开这两
- 简单的7个步骤设置“XX试卷 第x页 共y页”这种格式页码的方法。1、打开文档。2、单击“视图|页眉和页脚”菜单项。 &nbs
- PDF补丁是一款不错的办公软件,这款软件支持PDF文档编辑,支持处理PDF文档、提取文档内容等,本文提供的是这款软件制作书签的方法PDF补丁
- 360驱动大师是一款专门用来解决驱动安装问题的工具,那么有用户知道360驱动大师怎么测试宽带网速吗?针对这一问题,接下来小编就为大家带来具体
- 有些用户可能会发现,在 iPhone 上设置和添加 Apple Pay 之后,iPhone 无法使用此前的密码进行解锁了。此时,可以尝试通过
- 闪电PDF编辑器专业的PDF办公软件,该软件功能齐全,本文中介绍的是使用这款软件删除PDF空白页面的方法,有需要的朋友可以参考本文了解一下怎
- 1、打开WPS,新建空白演示文稿。2、选择演示文稿,在“插入”中选择“图形”中的元素。我们这里用到椭圆、圆角矩形、菱形和箭头线条。 &nbs
- wps拼写检查功能的使用方法1、首先打开wps,选择wps文字--选项,进入设置页面或者点击下拉“工具”选择拼写检查 &nb
- 除了系统漏洞的修复,iOS16.2正式版还为大家带来全新的无边记app和Apple Music唱歌功能。截止目前,还有不少用户都还不清楚无边
- wps怎么制作ppt幻灯片动画1.首先,在我们制作演示文稿时打开需要添加动画效果的页面,点击一下(左击)需要添加动画效果的图或文字,然后在其
- office里面的常用军powerpoint跟word是大家很依赖的两款软件,它们生成的文档当然也是不相同的格式,如何将Office制作的P
- wps演示设置文字行间距的方法:①启动WPS演示,单击插入--文本框--横向文本框。 ②在文本框内复制一段文字,可
- Apple Watch的“自动解锁”功能支持在手表靠近处于锁定模式的iPhone时可以自动解锁设备。如果你戴着口罩无法解锁iPhone,我们
- Microsoft Office 2013最新激活秘钥推荐!Microsoft Office 2013,又称为 Office 2013 或O
- 本文中为大家介绍的是一款PDF文档编辑软件设置文字透明度的方法,该软件功能齐全,界面简洁易于上手使用,有需要的朋友不妨参考本文了解一下闪电P
- 有时候我们在操作电脑时会因为一些特殊原因而选择去进行系统的重置,但在系统重置的过程中难免会出现一些问题,例如进度卡在28% 不动,这个时候其
- CAJViewer论文不显示目录怎么办?CAJViewer中打开论文,不小心将目录删除了,该怎么重新显示出来呢?下面我们就来看看CAJVie
- 如何使用ps给人物绘制逼真的眼泪?给大家介绍如何使用ps给人物绘制逼真的眼泪,一起来看看吧。1.打开ps,打开一张人物素材图片。2.使用钢笔