吐槽并不能心情好,只会被别人所谓的同理心带入阴沟,没有实力的发泄,最后终被看了笑话。
——我曾放过的狠话,终究会狠。
身边人是我们的福气,他们身边聚人聚财,把事情做好,把人处好,时间会证明你是有价值的。
——小丁是很不错的,人好事好。
要么向上,要么向下,我甘愿做别人觉得难的事,吃大苦,耐大劳,最后的原点还是会在这里。
——我才不要做圣人,偶做蛮人。 https://t.cn/R2L8zxW
——我曾放过的狠话,终究会狠。
身边人是我们的福气,他们身边聚人聚财,把事情做好,把人处好,时间会证明你是有价值的。
——小丁是很不错的,人好事好。
要么向上,要么向下,我甘愿做别人觉得难的事,吃大苦,耐大劳,最后的原点还是会在这里。
——我才不要做圣人,偶做蛮人。 https://t.cn/R2L8zxW
用好Xlookup函数,查询引用不再烦恼,条件及逆向查询统统搞定
Lookup以及Vlookup函数,是Excel中应用最广泛的函数,但在2019年,微软公布了Xlookup函数,但只能在Office 365版本中使用;如果你使用的是版本较低的Office,也可以在新版的WPS中使用,和VLOOKUP函数相比,只是将字母V变成了X,但其功能确发生了很大的变化。
功能:在某个范围或数组中搜索匹配项,并通过第二个范围或数组返回相应的项,默认情况下使用精准匹配。
语法结构:=Xlookup(查询值,查询值范围,返回值范围,[未查询到值时的返回值],[匹配模式],[搜索模式])。
解读:
1、 参数“查询值”、“查询值范围”、“返回值范围”很好理解,就是字面意思,其中“值”就是指定的字段或单元格地址,“范围”就是单元格的区域。
2、参数“未查询到值时的返回值”是指:在查询值范围中未找到要查询的值时,返回的指定值,在Vlookup函数中,如果查询不到指定的值,则返回#N/A,但在Xlookup中,可以指定具体的值,如果省略则返回#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函数的用法。
一、普通查询。
目的:根据员工姓名查询对应的月薪。
方法:
在目标单元格中输入公式:=XLOOKUP(J3,C3:C12,H3:H12)。
解读:
1、此方法中应用了Xlookup必须的3个参数,其省略的参数全部按照默认的值执行。
二、多条件查询。
如果只有如下图的基础数据,该如何查询“李丽”的“月薪”呢?
通过对数据源的分析,我们不难发现,“李丽”有2个人,除了性别不同外,其它基础信息均相同,此时,我们要精准的查询李丽的月薪,就需要增加条件。即通过姓名和性别两个条件来查询。
目的:通过员工姓名和性别查询对应的月薪。
方法:
在目标单元格中输入公式:=XLOOKUP(I3&J3,B3:B12&D3:D12,G3:G12)。
解读:
如果此功能用VLOOKUP函数去实现,公式该如何去编辑了?但如果用Xlookup函数,条件及数据区域之间只需用“&”符号链接即可,是不是非常简单?
三、逆向查询。
目的1:根据员工的姓名查询对应的工号。
方法:
在目标单元格中输入公式:=XLOOKUP(J3,C3:C12,B3:B12)。
解读:
用Xlookup逆向查询时,只需将对应的数据范围填写到对应的参数区域即可,不需要重构数组等操作。
四、未找到查询内容时返回指定的内容。
目的:如果查询不到相关人员的信息,则返回“查无此人”。
方法:
在目标单元格中输入公式:=XLOOKUP(J3,C3:C12,H3:H12,"查无此人")。
解读:
如果不指定第4个参数,则默认返回值为错误代码#N/A。指定第4个参数后,公式和数据的可读性更强。
五、精准查询或向下匹配。
目的:查询等于或低于指定月薪的最高月薪员工姓名。
方法:
在目标单元格中输入公式:=XLOOKUP(J3,H3:H12,C3:C12,,-1)。
解读:
此用法的关键在于第5个参数,在前文中我们已经介绍过,“匹配模式”共有4种,当为-1时为精准匹配或下一个较小的项,也就是返回等于或小于当前值的最大值。
六、精准查询或向上匹配。
目的:查询等于或高于指定月薪的最低月薪员工姓名。
方法:
在目标单元格中输入公式:=XLOOKUP(J3,H3:H12,C3:C12,,1)。
解读:
如果明白了第5个示例,第六个示例也就很容易理解了,其关键还是在第5个参数,当参数值为1时为精准匹配或下一个较大的项,也就是返回等于或大于当前值的最小值。
七、从数据库的末尾逆向查询。
目的:查询产品最后一次的销售单价。
方法:
在目标单元格中输入公式:=XLOOKUP(J3,C3:C12,D3:D12,,0,-1)。
解读:
此用法的关键在于第6个参数,在前文中我们已经介绍过,“搜索模式”共有4种,当为-1时为从最后一项到第一项搜索,即从数据库的末尾逆向搜索。如果要从数据库的第一条记录开始查询,则将第6个参数修改为1或省略即可。
八、一对多查询。
目的:根据产品编号查询对应的所有信息。
方法:
在目标单元格中输入公式:=XLOOKUP(J3,B3:B12,C3:G12),并用Ctrl+Shift+Enter填充。
解读:
由于返回的时数组区域,所以必须用Ctrl+Shift+Enter填充。
最美尾巴:
本节课内容中我们重点介绍了Xlookup的用法,其实上述功能也可以用VLOOKUP或Lookup等函数完成,但具体该如何实现了,欢迎大家在留言区讨论哦!
Lookup以及Vlookup函数,是Excel中应用最广泛的函数,但在2019年,微软公布了Xlookup函数,但只能在Office 365版本中使用;如果你使用的是版本较低的Office,也可以在新版的WPS中使用,和VLOOKUP函数相比,只是将字母V变成了X,但其功能确发生了很大的变化。
功能:在某个范围或数组中搜索匹配项,并通过第二个范围或数组返回相应的项,默认情况下使用精准匹配。
语法结构:=Xlookup(查询值,查询值范围,返回值范围,[未查询到值时的返回值],[匹配模式],[搜索模式])。
解读:
1、 参数“查询值”、“查询值范围”、“返回值范围”很好理解,就是字面意思,其中“值”就是指定的字段或单元格地址,“范围”就是单元格的区域。
2、参数“未查询到值时的返回值”是指:在查询值范围中未找到要查询的值时,返回的指定值,在Vlookup函数中,如果查询不到指定的值,则返回#N/A,但在Xlookup中,可以指定具体的值,如果省略则返回#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函数的用法。
一、普通查询。
目的:根据员工姓名查询对应的月薪。
方法:
在目标单元格中输入公式:=XLOOKUP(J3,C3:C12,H3:H12)。
解读:
1、此方法中应用了Xlookup必须的3个参数,其省略的参数全部按照默认的值执行。
二、多条件查询。
如果只有如下图的基础数据,该如何查询“李丽”的“月薪”呢?
通过对数据源的分析,我们不难发现,“李丽”有2个人,除了性别不同外,其它基础信息均相同,此时,我们要精准的查询李丽的月薪,就需要增加条件。即通过姓名和性别两个条件来查询。
目的:通过员工姓名和性别查询对应的月薪。
方法:
在目标单元格中输入公式:=XLOOKUP(I3&J3,B3:B12&D3:D12,G3:G12)。
解读:
如果此功能用VLOOKUP函数去实现,公式该如何去编辑了?但如果用Xlookup函数,条件及数据区域之间只需用“&”符号链接即可,是不是非常简单?
三、逆向查询。
目的1:根据员工的姓名查询对应的工号。
方法:
在目标单元格中输入公式:=XLOOKUP(J3,C3:C12,B3:B12)。
解读:
用Xlookup逆向查询时,只需将对应的数据范围填写到对应的参数区域即可,不需要重构数组等操作。
四、未找到查询内容时返回指定的内容。
目的:如果查询不到相关人员的信息,则返回“查无此人”。
方法:
在目标单元格中输入公式:=XLOOKUP(J3,C3:C12,H3:H12,"查无此人")。
解读:
如果不指定第4个参数,则默认返回值为错误代码#N/A。指定第4个参数后,公式和数据的可读性更强。
五、精准查询或向下匹配。
目的:查询等于或低于指定月薪的最高月薪员工姓名。
方法:
在目标单元格中输入公式:=XLOOKUP(J3,H3:H12,C3:C12,,-1)。
解读:
此用法的关键在于第5个参数,在前文中我们已经介绍过,“匹配模式”共有4种,当为-1时为精准匹配或下一个较小的项,也就是返回等于或小于当前值的最大值。
六、精准查询或向上匹配。
目的:查询等于或高于指定月薪的最低月薪员工姓名。
方法:
在目标单元格中输入公式:=XLOOKUP(J3,H3:H12,C3:C12,,1)。
解读:
如果明白了第5个示例,第六个示例也就很容易理解了,其关键还是在第5个参数,当参数值为1时为精准匹配或下一个较大的项,也就是返回等于或大于当前值的最小值。
七、从数据库的末尾逆向查询。
目的:查询产品最后一次的销售单价。
方法:
在目标单元格中输入公式:=XLOOKUP(J3,C3:C12,D3:D12,,0,-1)。
解读:
此用法的关键在于第6个参数,在前文中我们已经介绍过,“搜索模式”共有4种,当为-1时为从最后一项到第一项搜索,即从数据库的末尾逆向搜索。如果要从数据库的第一条记录开始查询,则将第6个参数修改为1或省略即可。
八、一对多查询。
目的:根据产品编号查询对应的所有信息。
方法:
在目标单元格中输入公式:=XLOOKUP(J3,B3:B12,C3:G12),并用Ctrl+Shift+Enter填充。
解读:
由于返回的时数组区域,所以必须用Ctrl+Shift+Enter填充。
最美尾巴:
本节课内容中我们重点介绍了Xlookup的用法,其实上述功能也可以用VLOOKUP或Lookup等函数完成,但具体该如何实现了,欢迎大家在留言区讨论哦!
#同铸中原国防 共建网络文明##永远的兵#【河南省辉县市“全国向上向善好青年”郭嘉毅应征入伍】郭嘉毅是河南省辉县市2022年秋季应征入伍的一名新兵,曾获“全国向上向善好青年”“全国优秀共青团员”等荣誉称号。他先后到10余所学校进行党史宣讲活动,听众累计超过3000人;积极参加近百项志愿服务活动,志愿时长1400小时;参与辉县义工爱心助学项目,累计为600多名贫困学子筹集学费近百万元;2020年主动奔赴疫情防控一线,挨家挨户排查居民5000余人……如今穿上军装,郭嘉毅表示,“我一定刻苦训练,用青春热血守护好祖国的每一寸土地。”(郭永超、段作一)#点赞中原##天南地北河南兵#
✋热门推荐