头部广告

Excel新函数Xlookup公式就是厉害,必学的7大用法!你会几个?

我们平时在WPS时经常需要对数据进行查找匹配,最新版WPS中出现了一个Xlookup公式,使用这个公式可以轻松解决我们工作中的各种查找匹配问题。今天就跟大家分享一下Xlookup函数公式必学的7大用法,希望能提高大家的工作效率。

Xlookup函数公式介绍

函数功能:XLOOKUP函数是一个查找函数,在某个范围或数组中搜索匹配项,并通过第二个范围或数组返回

相应的项,默认情况下使用精准匹配。

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

语法解读:

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

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

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

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

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

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

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

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

参数为:1,从上到下进行数据查询, 这是默认选项。参数为:-1,从最后一项到第一项进行搜索。参数

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

以上就是XLOOKUP的所有参数,函数参数虽然比较多,但是第四、第五、第六参数都是可以省略的,我们在平时使用这个函数时一般只需设置前三个函数即可。

一、普通基本查询

如下图所示,我们想查询赵飞的基本工资,我们可以直接使用公式=XLOOKUP(G3,A2:A8,D2:D8),在这里我们可以把函数的第四、第五、第六参数都省略掉,我们在平时使用这个函数时一般只需设置前三个函数即可。

1.jpg


二、逆向查找匹配

如下图所示,我们想通过员工姓名查找到员工的编号,也就是从右往左逆向查询,我们只需要输入公式=XLOOKUP(H3,B2:B8,A2:A8)

2.jpg


三、从下向上纵向查询

有时我们需要对表格数据进行纵向查询,如下图所示,我们需要通过姓名赵飞查找出对应的奖金,我们只需要输入公式=XLOOKUP(H3,B1:E1,B5:E5)

3.jpg


四、屏蔽错误值查找

如果我们的原始表格数据中查找不到结果,并且希望将错误值#N/A返回为空白,这是要如果使用Xlookup函数的话就可以设置他的【第四参数】,这样就可以可以自动屏蔽错误值的了。

如下图所示,我们查找姓名为“刘大能”的基本工资,其实是找不到的,这时我们可以使用公式=XLOOKUP(H3,B2:B8,A2:A8,"找不到结果")或者=XLOOKUP(H3,B2:B8,A2:A8,""),这样当找不到值时就返回空或者第四参数的文字。

4.jpg


五、一次性的查找匹配多列数据

如下图所示,我们想根据姓名,一次性的查找匹配出多个值(性别、部门、基本工资)

5.jpg


1、同样首先在右侧的查询结果表格中,要先选中G4:I4这几列(因为在wps中没有溢出功能),也就是要返回的结果数据列,如下图所示

6.jpg


2、然后在上面的公式位置输入公式=XLOOKUP(F4,A:A,B:D),输入完公式之后按「Ctrl+Shift+Enter」三键组合,获取所有的查询结果。如下图所示

7.gif


公式解释:

1、第一个参数【查找值】:F4;第二个参数【查找数组】:A:A;第三个参数【返回数组】:B:D

2、需要注意,输入公式后必须使用「Ctrl+Shift+Enter」三键组合才能获取查询结果。

3、这个功能有一个缺点:结果列在数据表中必须是连续的。

六、多条件查询

我们就以下图的员工月薪表为例,查询所在团队为“一组”名称为“赵飞”的“月薪”,这时就涉及到多条件查询,就是通过员工名称和所属团队查询对应的月薪。

8.jpg


具体方法:

1、使用公式

在目标单元格中输入公式:=XLOOKUP(E3&F3,A3:A9&B3:B9,C3:C9)。

如下图所示:

9.jpg


解读:

第一个参数:想要查找值是E3和F3,所以中间用“&”符号链接即可,查找值就是E3&F3

第二个参数:要查询的数据区域,同样是左侧表格的“员工”和“团队”两列,所以中间也是用“&”符号链接,即A3:A9&B3:B9

第三参数:要返回的数据区域就是员工月薪这一列数据

七、关键字模糊查找匹配

当我们使用Xlookup进行关键字查询,我们就需要设置它的【第五参数】即可,第4个参数不填,第5个参数填写2,代表关键词通配符匹配。通配符我们一般使用*(代表任意多个字符)。

如下图所示我们将查找姓名设置为*明,然后只需要将公式设置为:=XLOOKUP(G3,A2:A8,D2:D8,"",2)就可以找到李明的基本工资。

10.jpg


底部广告