教你效率提升百倍的Excel函数公式——XLOOKUP好用到哭(附案例)
01 XLOOKUP的升级变化
XLOOKUP是2019年才由微软推出的新函数,目前只能在微软的Office 365才能使用。好消息是在去年,WPS宣布可以在WPS Windows版免费使用。
XLOOKUP可以看作是 Lookup 家族的合体版,包括了Vlookup、Hlookup、Lookup、Index、Match等都融入到这一个函数中。
XLOOKUP的语法示意图如下所示。
XLOOKUP函数的最大变化就是可以直接指定要返回的区域,不像VLOOKUP要指定第几位,单这个一个变换就解决了VLOOKUP使用中一些复杂的程序构造。
我们来讲解一些常见的场景
1.常规查询
在这里,我们想要通过“抽取客户号”为条件查询存款余额,使用VLOOKUP和XLOOKUP都可以实现相同的效果。使用XLOOKUP的好处就是不用去数需要返回第几列的数据,而是直接指定返回的数据列。
为什么vlookup显示的是公式
2.反向查询
VLOOKUP查询有一个问题是:只能从左往右查。如果想反向查询,就需要构造复杂的程序。但是XLOOKUP完全没有这个问题,因为可以指定返回的数据,无论在什么位置。
3.多条件查询
假如我们想多个条件组合进行查询,使用VLOOKUP函数的话需要构造复杂的数组,而XLOOKUP只需要用&把需要查询的条件和区域分别进行连接即可。
4.一对多查询
如果我们查的内容比较多,查的列顺序与原来数据的排列也不一样,我们就需要用到VLOOKUP+MATCH函数的组合,并通过拖动来返回结果。而如果使用XLOOKUP,选择需要返回数值的区域后,就可以一次返回所有的结果。
5.最近值查
如果我们想根据姓名查最近一次的考试成绩,使用VLOOKUP函数比较困难。使用LOOKUP可以实现,但是要构造复杂的程序,而使用XLOOKUP构造起来比较简单。
6.未到值显示
XLOOKUP有一个新功能是当我们查结果不存在时,可以指定要显示的内容。而使用VLOOKUP只能直接返回一个错误代码。
7.区间查
如果想根据区间的条件来匹配查询,实现更加灵活的数据填充,VLOOKUP和XLOOKUP都可以用比较高效的方式来实现。
02 实战操作。
讲了这么多方法,具体怎么用,接下来数据州就用案例来实战操作。
1.实验环境。
操作系统:Windows。
Excel版本:WPS Office 2022。
2.实验内容。
(1)数据预览。
为了讲解Excel XLOOKUP方法的实战操作,我们准备了多个XLOOKUP与VLOOKUP共同应用场景,包括了客户存款、日期、姓名、城市等内容的精确查询、近似查询、多条件查询等场景。
(2)实战讲解
1.常规查询。
在表1中,我们要根据随机抽取的“客户号”,从前面“客户号”的名单中去匹配,然后把对应客户的“存款余额”精准地查询出来。
如果使用VLOOKUP函数的话,我们在F2单元格使用公式:
=VLOOKUP(E2,A:B,2,FALSE)。
如果使用XLOOKUP函数,我们在I2单元格使用公式:
=XLOOKUP(H2,A:A,B:B)。
这里公式的含义是,根据H2单元格的条件,在A列去匹配,返回对应B列的结果。是不是比VLOOKUP函数更加的简洁?
在其余单元格也应用公式,就实现了所有的查询。
2.反向查询。
在表2中,我们想通过抽取的存款余额查询客户的姓名,可是在VLOOKUP中一般是从左往右查询数据,所以我们不能使用常规的函数方式。
我们在单元格F2中输入公式:
=VLOOKUP(E2,IF({1,0},B2:B4,A2:A4),2,FALSE)。
这个复杂的函数具体什么意思,请查看上节课内容。
如果使用XLOOKUP函数,可以在I2单元格输入公式:
=XLOOKUP(H2,B:B,A:A)
具体含义是根据H2单元格的条件,在B列去匹配,返回对应A列的结果。XLOOKUP函数不存在查询顺序的问题,比VLOOKUP函数简洁了不要太多。
3.多条件查询。
在表3中,客户“张三”有多项存款余额记录,因此我们需要“客户名”和“存款余额”两个条件来查满意打分。
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论