Office 365函数新世界:XLOOKUP

HI,大家好,我是星光。

前段时间微软在365版本中更新了一个新函数,叫做XLOOKUP。一时间闹得沸沸扬扬,很多人宣称这家伙将彻底淘汰VLOOKUP,甚至有人说微软即将把VLOOKUP抛弃了……胡扯的没有天际。

做商品数据分析的朋友都知道,一个产品能否完全代替甚至淘汰另外一个产品,不但要看它功能强弱,还要看版本迭代、用户习惯、操作简易性等因素。LOOKUP函数、INDEX+MATCH组合,每一个都号称功能比VLOOKUP强大百倍,但这么多年过去了,VLOOKUP不还是活的好好的?

——打个响指,不过我们今天倒无意讨论VLOOKUP生存还是死亡的问题,而是分享一下XLOOKUP到底有多强大,强大到一出生,很多江湖老司机就激扬失色,纷纷跟风宣扬VLOOKUP即将被淘汰的论调。

照例先说一下XLOOKUP的语法,它有六个参数,成功超越大哥大OFFSET,成为参数最多的函数之一。

=XLOOKUP(查找值,查找范围,结果范围,[容错值],[匹配方式],[查询模式])

参数看起来很多,不过只有前三个是必须的,后面均可省略。

下面我们举12个例子+两道练习题,由易入难、从简到繁、从入门到进阶,让大家对XLOOKUP的作用和运算方式有一个全面的了解。

……

1)单条件查询

如下图所示,B:D列是数据明细,需要根据F列姓名查询相关电话号码。

G2输入公式▼

=XLOOKUP(F2,B:B,D:D)

F2是查找值,B列是查找范围,D列是结果范围,公式的意思也就是在B列查找F2,找到后返回D列对应的结果。

2)容错查询

如下图所示,B:D列是数据明细,需要根据F列姓名查询相关电话号码,但和上一个案例所不同的是,如果查无结果,需要返回指定值:查无结果。

G2输入公式▼

=XLOOKUP(F2,B:B,D:D,'查无')

XLOOKUP的第4参数可以指定容错值,当查无结果时避免返回错误值#N/A,省去了外围再嵌套一个IFERROR函数。

3)模糊条件查询

如下图所示,A:B列是数据明细,需要根据F列姓名的简称查询相关特长。这是一个模糊查询的示例,比如查找星光,对应的结果为看见星光。

E2输入公式▼

=XLOOKUP('*'&D2&'*',A:A,B:B,'查无',2)

XLOOKUP的查找值是'*'&D2&'*',*是通配符,可以代替0到多个字符串,'*'&D2&'*'也就指包含D2的字符串。

但和VLOOKUP所不同的是,XLOOKUP默认不支持通配符匹配,只有将第5参数设置为常数2时,才支持通配符匹配。

XLOOKUP的第5参数可以指定匹配方式,包含了精确匹配、区间匹配以及通配符匹配等。

4)区间查询

如下图所示,F:G列是评分标准,60以下不及格,80以下及格等,需要根据该评分标准,对C列的成绩计算评级。

D2输入公式▼

=XLOOKUP(C2,$F$2:$F$5,$G$2:$G$5,'',-1)

XLOOKUP第5参数为-1,指定了匹配方式是'精确匹配或下一个较小的项',比如查找84,找不到精确匹配,则寻找比它小的项,也就是80,然后取其对应结果:'良好'。

这儿的XLOOKUP等同于LOOKUP函数▼

=LOOKUP(C2,F:G)

但和LOOKUP所不同的是,XLOOKUP函数不要求查找区域首列数据升序排列,即便把F:G列的数据打乱了,也不妨碍它寻找'精确匹配或下一个较小的项'的计算规则▼

除此之外,XLOOKUP还支持'精确匹配或下一个较大的项'的计算规则▼

=XLOOKUP(C2,$F$2:$F$5,$G$2:$G$5,'',1)

第5参数指定值为1,比如查找80,找不到精确匹配,则寻找比它大的项,也就是90。

5)查询符合条件的最后一个结果

如下图所示,A:C列是数据明细,其中日期字段升序排列。需要根据E列姓名查询相关销售额,但和前面案例所不同的是,它需要查找每个人最后一次销售额,也就是符合条件的最后一条记录。

F2输入公式▼

=XLOOKUP(E2,B:B,C:C,'查无',0,-1)

XLOOKUP的第6参数可以指定查询方式,默认是从前往后找~找到即止;此外也可以从后往前找~找到即止。

本例是寻找符合查询条件的最后一条记录,需要从后往前找~找到即止,也就是将第6参数设置为-1。

6)二分法查询

如下图所示,A:C列是数据源,其中姓名列有升序排序,现在需要根据E列姓名查询相关电话号码。

F2输入公式▼

=XLOOKUP(E2,A:A,C:C,'查无',0,2)

第6参数指定值为2,查找方式是升序排序情况下的二分法查找。

这里也可以使用公式▼

=XLOOKUP(E2,A:A,C:C,'查无')

两者相比有何不同呢?

主要是查询方式的区别。后者是从前往后找,虽然说找到即止,但效率也不是很高。后者是二分法查找,效率非常高。

比如查询看见星光,前者要从第1行开始遍历,找到第10行才找到结果,它需要找10次。而后者折半查找,只需要找3次就可以了。数据量越大后者的效率优势就越高——不过后者要求查询范围需排序处理。

7)横向查询

如下图所示,A:D列是数据明细,需要根据F1指定的科目查询对应的成绩。

F2输入公式▼

=XLOOKUP(F1,B1:D1,B2:D2)

当查询范围是一个横向区域时,XLOOKUP也就可以像HLOOKUP一样,实现横向数据查询。

8)多列数据查询

如下图所示,A:D列是数据明细,需要根据F列的姓名,查询对应的特长、电话和得分等多列数据。

G2输入公式▼

=XLOOKUP($F2,$A:$A,B:D)

当结果范围是一个多行多列的区域时,XLOOKUP可以根据查询范围的行列特性,返回一个多行或多列的结果区域。本例中查找范围是单列(A列),结果范围是B:D列,因此返回B:D列多列结果。

9)交叉表查询

如下图所示,A:D列是数据明细,需要根据F列的姓名,查询对应的电话、特长和得分等多列数据。和上面的案例所不同的是,结果表的字段排列顺序和数据源不一致,也就是通常所说的交叉表查询了。

G2输入公式▼

=XLOOKUP($F2,$A$2:$A$11,XLOOKUP(G$1,$B$1:$D$1,$B$2:$D$11))

公式使用了两个XLOOKUP函数。

先说XLOOKUP(G$1,$B$1:$D$1,$B$2:$D$11)。

上面解释过,当结果范围是一个多行多列的区域时,XLOOKUP可以根据查询范围的行列特性,返回一个多行或多列的结果区域。本例中查找范围是单行($B$1:$D$1),结果范围是$B$2:$D$11,因此返回一个多行单列数据。

比如查找G1的值为'电话',则返回C2:C11。以此作为第2个XLOOKUP的结果范围。

10)多条件查询

如下图所示,A:C列是数据明细,需要根据E列的年和F列的姓名,查询对应的得分。

G2输入公式▼

=XLOOKUP(E2&F2,$A$2:$A$11&$B$2:$B$11,$C$2:$C$11)

XLOOKUP支持数组运算,本例中查找值为E2&F2,查找范围是年字段&姓名字段,即$A$2:$A$11&$B$2:$B$11▼

(0)

相关推荐