Vlookup实现不了的功能我用Xlookup函数实现,即简单,又高效

Vlookup函数,最常用的查询引用函数之一,在Excel的数据查询引用中占有一席之地,非常的使用,如果你还掌握,请查阅历史中的相关记录,但Vlookup函数也有一些缺陷,如逆向查询,一对多查询等等,实现起来就有一定的难度,但这些对于Xlookup函数而言,都可以轻松实现。但此函数必须在Office 365版本中使用。


一、Xlookup函数功能及语法结构。

功能:在指定的范围或数组中搜索匹配项,并通过第二个范围或数组返回相应的项。

语法结构:=Xlookup(查询值,查询值范围,返回值范围,[未查询到值时的返回值],[匹配模式],[搜索模式])。

解读:

1、参数“查询值”、“查询值范围”、“返回值范围”是必须参数,不可省略。

2、参数“未查询到值时的返回值”可选,是指没有符合条件查询条件的值时,可以返回指定的值,此值是自定义的,但如果忽略则返回#N/A。

3、参数“匹配模式”可选,共有4种,分别为0、-1、1、2。其中0为精准匹配;-1为精准匹配或下一个较小的项;1为精准匹配或下一个较大的项;2位通配符匹配;省略时默认为精准匹配。

4、参数“搜索模式”可选,共有4种,分别为1、-1、2、-2。其中1为从第一项到最后一项全部搜索;-1为从最后一项到第一项搜索;2为二进制文件搜索(升序模式);-2为二进制文件搜索(降序模式)。省略时默认从第一项到最后一项全部搜索。

如果要是Xlookup发挥更大的作用,后3个参数的功能就要得到充分的应用。


二、Xlookup函数应用案例解读。

1、常规查询。

目的:根据“员工姓名”查询对应的“月薪”。

方法:

在目标单元格中输入公式:=XLOOKUP(L3,D3:D12,I3:I12)。

解读:

此公式中省略了后3个参数,全部按照默认值执行。


2、逆向查询。

目的:根据“员工姓名”查询员工所属“部门”。

方法:

在目标单元格中输入公式:=XLOOKUP(L3,D3:D12,B3:B12)。

解读:

用Xlookup实现逆向查询,还是常规用法,只需将对应的数据范围填写到对应的参数上即可,是不是很接单呢?


3、多列查询。

目的:根据员工姓名返回“年龄”、“性别”、“婚姻”、“学历”、“月薪”等列。

方法:

在第一个目标单元格,即M3中输入公式:=XLOOKUP(L3,D3:D12,E3:I12)。

解读:

和常规查询相比,只是返回值的范围变“大”了而已,但达到的目的却完全不同,而且不需要选择所有的目标单元格区域,只需在第一个目标单元格中输入公式即可。是不是很方便?


4、自动除错。

目的:如果查询不到对应的值,则返回“无此记录”。

方法:

在目标单元格中输入公式:=XLOOKUP(L3,D3:D12,E3:J12,"无此记录")。

解读:

参数“未查询到值时的返回值”可以根据需要自定义。


5、模糊查询。

目的:根据员工“月薪”和“扣缴比例”查询员工的扣缴比例。

方法:

在目标单元格中输入公式:=XLOOKUP(I3,L$3:L$8,M$3:M$8,,-1)。

解读:

当参数“匹配模式”为-1时,如果没有查询到指定的值,则自动匹配小于当前值的最大值。


6、多条件查询。

目的:根据“部门”和“职位”查询对应的员工。

方法:

在目标单元格中输入公式:=XLOOKUP(M3&N3,B3:B12&C3:C12,E3:E12)。

解读:

多条件查询就是将“值”和“范围”用“&”符号连接起来即可。


7、查询最新结果。

目的:查询“产品”的最新“批发价”。

方法:

在目标单元格中输入公式:=XLOOKUP(I3,B3:B12,C3:C12,,,-1)。

解读:

结合数据表,要查询“产品”的最新“批发价”,其实就是从数据表的最后一行逆向向第一行查询,结合Xlookup的特点,只需将第6个参数的值设置为-1即可。


最美尾巴:

文中从Xlookup的功能、语法结构,以及应用技巧等7个方面对Xlookup做了详细全面的解读,相对于Vlookup,具有明显的优势,所以还未与Xlookup成功牵手的,要速度哦!


展开阅读全文

页面更新:2024-03-08

标签:函数   功能   目的   高效   精准   公式   员工   参数   目标   模式   简单   方法

1 2 3 4 5

上滑加载更多 ↓
推荐阅读:
友情链接:
更多:

本站资料均由网友自行发布提供,仅用于学习交流。如有版权问题,请与我联系,QQ:4156828  

© CopyRight 2008-2024 All Rights Reserved. Powered By bs178.com 闽ICP备11008920号-3
闽公网安备35020302034844号

Top