头部广告

Excel新函数XLOOKUP的第5参数有妙用,你真的会用吗?

我是【桃大喵学习记】,欢迎大家关注哟~,每天为你分享职场办公软件使用技巧干货!

日常工作中,我们经常使用Excel新函数XLOOKUP对数据进行查找匹配。今天跟大家分享的是XLOOKUP函数第5参数的用法,巧妙使用第5参数有时可以轻松解决我们工作中的数据查找匹配问题。

一、XLOOKUP函数介绍

功能:XLOOKUP函数是一个查找函数,在某个范围或数组中搜索匹配项,并通过第二个范围或数组返回相应的项,默认情况下使用精准匹配。

语法:=XLOOKUP(查找值,查找数组,返回数组,未找到值,匹配模式,搜索模式)。

解读:

第一参数:想要查找值,可以是单个值或者数组值

第二参数:想要在那个数据区域中查找

第三参数:要返回的数据区域

第四参数(可选):未找到值,就返回第四参数,省略它函数默认返回#N/A这个错误值

第五参数(可选):匹配模式,可填0、1、-1、2

参数为:0 ,精确匹配,找不到结果,返回 #N/A这个错误值,这是默认选项。

参数为:-1,精确匹配找不到结果,返回下一个较小的项。

参数为:1,精确匹配,找不到结果,返回下一个较大的项。

参数为:2 ,通配符匹配

第六参数(可选):指定匹配模式,可填1、-1、2、-2

参数为:1,从上到下进行数据查询, 这是默认选项。

参数为:-1,从最后一项到第一项进行搜索。

参数为:2,二分搜索(升序排序) 。

参数为:-2,二分搜索(降序排序)

以上就是XLOOKUP的所有参数,函数参数虽然比较多,但是我们在平时使用这个函数时一般只需设置前三个函数即可。但是,今天要跟大家分享的是使用第5参数:匹配模式,来进行“通配符匹配”查询和判断区间数据等级。

二、XLOOKUP函数通配符模糊查找

如下图所示,我们需要根据公司简称,查找出“公司名称”包含公司简称的信息,然后返回对应的“合同金额”,只需在目标单元格中输入公式:

=XLOOKUP("*"&D2&"*",A2:A7,B2:B7,"",2)

就可以找到想要的信息了

1.jpg

解读:

XLOOKUP函数默认是不支持模糊查找匹配的,如果要用通配符,第五参数必须填2,这也是XLOOKUP函数的一个特殊之处。通配符我们一般使用*(代表任意多个字符),只要数据中包含查找关键词,就会返回对应结果。

三、XLOOKUP函数根据区间查找数据

如下图所示,左侧是员工考核成绩表格,我们需要根据右考核成绩区间来评定不同的等级。这时我们完全可以使用XLOOKUP函数来实现。

2.jpg

第一步:先创建一个辅助列,把每个成绩考核区间的最低标准列出来,手动输入即可

0<成绩<60,这个范围的最小值是0;

60<=成绩<70,这个范围的最小值是60;

70<=成绩<90,这个范围的最小值是70;

90<=成绩<100,这个范围的最小值是90;

所以,辅助列的数组从上到下分别是90、70、60、0,如下图所示

3.jpg

第二步:在目标单元格中输入公式:

=XLOOKUP(C2,G:G,H:H,,-1)

然后点击回车,下拉填充即可

4.gif

解读:

公式中第1参数:C2 就是查找值,是具体的考核成绩;

第2参数:G:G 就是要查找的数据区域;

第3参数:,H:H 就是返回的数组;

第4参数:为空,查找不到信息返回空;

第5参数:-1,匹配模式为-1,表示精确匹配,若未找到所查找的内容返回较小值。

比如说在对考核成绩78,进行查找匹配时它会从上向下查找匹配,首先找到78在哪个数值之间,它是在70-90这个范围之间,然后会匹配到较小的那个数值,也就是70,这样就查询结果就是对应的“良好”。

总之、当XLOOKUP函数第5参数即匹配模式为-1,表示精确匹配,若未找到所查内容返回较较小值。

5.jpg

以上就是【桃大喵学习记】今天的干货分享~觉得内容对你有帮助,记得顺手点个赞哟~。我会经常分享职场办公软件使用技巧干货!大家有什么问题欢迎留言关注!


底部广告