用xlookup函数做日报,比vlookup强大100倍

2019年微软正式推出了Xlookup函数,我们可以把它看作是vlookup的升级版。Xlookup弥补了vlookup的诸多不足,可以说是现阶段最强大的查找函数。不少人都表示说:用了30几年的vlookup终于可以功成身退了!

但是比较遗憾的是当时仅仅只有office365才可以使用这个函数,以至于人们对其知之甚少,但是最近WPS也更新了xlookup函数,我觉得现在是时候来学习下这个强大的查找函数了

Xlookup作用与参数

Xlookup:搜索数据数据区域中的值,然后返回找到的第一个匹配结果。

语法:=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

第一参数(必须参数):lookup_value,想要查找值

第二参数(必须参数):lookup_array,想要在哪个数据区域中查找

第三参数(必须参数):return_array,要返回的数据区域,就是结果所在的区域

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

第五参数(可选参数):match_mode,指定匹配类型

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

参数为:-1,近似匹配,未找到结果,返回下一个较小的项。

参数为:1,近似匹配,未找到结果,返回下一个较大的项。

参数为:2 ,通配符匹配

第六参数(可选参数):search_mode, 指定要使用的搜索模式

参数为:1,从第一项开始执行搜索。 这是默认选项。

参数为:-1,从最后一项开始执行反向搜索。

参数为:2,根据 lookup_array 按升序排序的二进制搜索。 如果未排序,将返回无效结果。

参数为:-2,根据lookup_array 按降序排序的二进制搜索。 如果未排序,将返回无效结果。

以上就是xlookup的所有参数,虽然比较多,但是使用起来却非常方便,下面我们就通过实际的例子 来学习下这个函数

一、普通查找

如下图所示,在这里我们想要查找鲁班的语文成绩,只需要将公式设置为:=XLOOKUP(H2,A1:A9,C1:C9)就能找到正确的结果,在这里我们需要注意的是前三个参数是必须参数,后三个参数是可选参数,也就是说前三个参数必须填写,后三个参数可以选择性填写,在普通查找中,一般将其省略即可

二、屏蔽错误值

Xlookup函数的第四参数可以屏蔽错误值,这样的话就不必再嵌套IFERROR函数来屏蔽错误值了。

比如在这里我们想要查找孙悟空的语文成绩,在数据表中是没有孙悟空的,函数就会返回#N/A这个错误值,但是我们只需要将公式设置为:=XLOOKUP(H7,A1:A9,C1:C9,””)函数就会将错误值屏蔽掉,在这里两个双引号就表示空值

如果你将第四参数设置为:”找不到结果”函数的结果就会返回找不到结果

三、横向查找

在之前想要进行横向查找,就需要使用hlookup这个函数,现在xlookup也具备了横向查找的功能,只需要将公式设置为:=XLOOKUP(A10,A1:I1,A3:I3)即可

通过这个例子需要强调一点的是:第一参数与第二参数的方向与个数都需要一一 对应,否则的话函数也将返回错误值。

四、通配符查找

通过函数介绍可以知道,我们只需要将xlookup的第五参数设置为2就能进行通配符查找,所谓的通配符就是可以代表任意字符的符号,常用的有2个

?:代表任意单个字符

*:代表任意多个字符

使用这个特性,我们可以利用xlookup实现通过关键字查找数据的效果,比如在这里我们将查找值设置为*白,然后只需要将公式设置为:=XLOOKUP(H2,A1:A9,C1:C9,,2)就可以找到李白的语文成绩。

五、反向查找

所谓的反向查找就是查找左侧的数据,但是Vlookup是不能查找左侧数据的,所以在进行反向查找的时候我们需要使用利用IF{1,0}来构建二维数组,但是xlookup不存在这样的情况,它是可以查找左侧数据的。

比如在这里,我们想要通过工号查找姓名,只需要将函数设置为:=XLOOKUP(H2,B1:B9,A1:A9),就可以找到姓名,非常的简单

六、多条件查找

Xlookup函数进行多条件查询也非常的简单,只需要利用连接符号将查找值与查找区域连接起来即可

如下图,张飞是存在重名的,在这里我们想要查找2班张飞的语文成绩,只需要将公式设置为:=XLOOKUP(G2&H2,A1:A9&B1:B9,C1:C9)即可找到正确的结果,在这里就是使用连接符号将姓名与班级连接在一起来查找的

七、查找多列数据

Xlookup函数可以实现设置一次函数,自动查找多列数据的效果,但是有一个缺点:结果列在数据表中必须是连续的。

如下图所示,我们想要查找他们的成绩,只需要在G2单元格中输入公式:=XLOOKUP(F2,A1:A8,B1:D8)后面的数据就会自动显示,需要注意的是我们需要在G2单元格中向下填充公式.

在这里我们需要明白1点,xlookup返回的结果个数,是由第三参数的列数决定的,在这里我们选择了3列数据,所以函数就会返回3个结果。

八、查找最后一次时间

这个用处不大,主要是为了说明xlookup可以自定义查找方向。Vlookup函数只能从上往下查找数据,而xlookup可以通过设置第六参数实现从下往上查找数据。

比如在这里我们想要求数据最后一次出现的时间,首先我们需要将数据进行升序排序,随后只需要将公式设置为:=XLOOKUP(D2,A1:A25,B1:B25,,,-1)即可,在这里是将第四第五参数直接忽略掉了,直接将第六参数设置为-1

我们需要注意的是:当使用xlookup查找数据遇到重复,函数也仅仅只能返回第一个找到的结果,这点与vlookup一致

以上就是今天分享的全部内容了,xlookup函数的作用还有很多,总之就是vlookup能做的它都能做,vlookup不能做的它还能做,今天这个xlookup函数希望对各位表格在做投放数据日报的时候会有所帮助。

最后祝大家都能准时下班!

1、版权声明:除非注明,版权均属 老杨SEM博客 原创文章,转载请注明出处。
2、版权协议:本站采用创作共用版权 CC BY-NC-ND/2.5/CN 许可协议,凡是投稿的内容表明该作者以授权给老杨SEM博客及其各类自媒体帐号、网站专栏中以及各种版权信息处理
3、侵权处理:用户认定侵权争议的内容,请第一时间提供版权争议链接为相关原创证明材料并加盖公章,发送到邮箱。第一时间断开版权争议的内容链接,并永不上架该作者的其他内容。
原文地址: 用xlookup函数做日报,比vlookup强大100倍

发表评论

提供最优质的资源集合

立即查看 了解详情