XMATCH函数比MATCH强百倍,倒序与近似查询更灵活!

职场   职场   2024-09-20 08:34   河北  


Excel情报局

职场联盟Excel

生产挖掘分享Excel基础技能
Super Excel Man
用1%的Excel基础搞定99%的职场问题
做一个超级实用的Excel公众号
Excel是门手艺玩转需要勇气
数万Excel爱好者聚集地


    



大家好,今天我们来讲讲XMATCH函数基本概念以及常规应用。XMATCH函数比MATCH函数开头就多了一个字母“X”,可以说是对MATCH函数功能的提升与增强,弥补了其一些缺憾,实用性方面对我们使用者来说更加的人性化了。

首先我们先来复习并回忆一下老函数MATCH函数的基本概念吧。

MATCH函数是查找类的函数。作用是可以查找并返回某值在指定区域内的相对位置。参数也相对比较容易理解的:
=MATCH(查找值, 查找区域, [查找方式])


第一参数代表需要查找的值。

第二参数代表包含要查找值的连续单元格区域。

第三参数是可选参数,代表查找方式:

0或省略代表精确匹配。-1代表查找大于或等于查找值的最小值(此时查找区域需要降序排列)。1代表查找小于或等于查找值的最大值(此时查找区域需要升序排列)。


而新函数XMATCH函数比老函数MATCH函数多个一个第四参数,作用却增强了不少。参数也相对比较容易理解:

=XMATCH(查找值, 查找区域, [查找方式], [搜索类型]) 


其中第三参数和第四参数是可选项


第三参数查找方式
0或省略表示精确匹配。-1表示小于等于查找值的最大值(近似匹配)。1表示大于等于查找值的最小值(近似匹配)。2表示通配符匹配,其中 *, ? 和 ~ 有特殊含义。


第四参数搜索类型
1或省略表示从第一个元素到最后一个元素搜索。-1表示从最后一个元素到第一个元素搜索。2表示依赖于查找值按升序排序的二进制搜索。2表示依赖于查找值按降序排序的二进制搜索。


对于普通的查询功能,两个函数的参数输入方式一样,并没有什么区别,比如我们想要查找并返回E2和E5单元格的姓名“李四”在A2:A7区域内的相对位置


MATCH函数输入法

=MATCH(E2,A2:A7,0)


XMATCH函数输入法

=XMATCH(E5,A2:A7,0)


两者都能利用第三参数“0”精确查询的查找方式,查询到“李四”在A2:A7区域内的相对位置均为“2”。



接下来我们再来讲讲XMATCH函数如何解决近似查询的


比如说我们想要查询分数高于80分且最接近80分的分数是多少,因为“80分”在数据源B列是不存在的,所以我们称这种查询为近似查询。


我们可以首先输入XMATCH函数

=XMATCH(E2,B2:B7,1)


利用XMATCH函数,查询E2单元格“80”在B2:B7区域内的相对位置,借助第三参数“1”,确定查找方式大于等于查找值的最小值(近似匹配)。所以确定这个高于80分且最接近80分的分数位于B2:B7区域内的第6行。



因为最终要获取的是“分数”,所以继续嵌套INDEX函数

=INDEX(B2:B7,XMATCH(E2,B2:B7,1))


用上一步的XMATCH函数返回的行数“6”,作为INDEX函数的第二参数。即查询出B2:B7区域内,第6行与第1列交叉值为分数“83”。INDEX函数的第一参数与第三参数重合,故第三参数“列号”省略了。



如果将XMATCH改成老函数MATCH,将MATCH函数的第三参数设置为“-1”,代表查找大于或等于查找值“80”的最小值

=INDEX(B2:B7,MATCH(E2,B2:B7,-1))


返回值结果会出错



出错原因就是:文章开头对MATCH函数基本介绍中的第三参数“-1”代表查找大于或等于查找值的最小值,此时查找区域须要降序排列。如果没有降序排序的话,会出现上面返回的错误结果。


我们必须对数据源中的“成绩”列进行“降序”排序,会发现返回结果正确了



最后我们再来讲讲XMATCH函数的倒序查询案例


我们看到数据源中“张三”是有重名的,但其对应的“学号”是不同的。我们想要查询出最后一个“张三”所对应的学号是多少,即倒序从最后往前的顺序查询。


先使用XMATCH函数

=XMATCH(A10,A2:A7,0,-1)


使用XMATCH函数首先确定A10单元格的“张三”在A2:A7区域内,最后一个“张三”所在的相对位置为“5”。因为第四参数“-1”表示从最后一个元素到第一个元素搜索。



最后仍然是用INDEX函数交叉查询获取“学号”

=INDEX(C2:C7,XMATCH(A10,A2:A7,0,-1))


用上一步中XMATCH函数返回的行数“5”,作为INDEX函数的第二参数。即查询出C2:C7区域内,第5行与第1列交叉值为学号“A002”。INDEX函数的第一参数与第三参数重合,故第三参数省略了。



其实重复学习Excel,刚开始是挺难的,但重复的次数多了总会成为习惯,先从坚持一星期开始,你会爱上那种脑子又累,又无限充实的感觉。回顾关键内容,善用图片表达,学会建立联系,拓展深度广度,浓缩关键概念,应用到行动中,善于归纳总结,尝试进行分享。


 

 

知识点精彩超链接点击阅读

VLOOKUP的新搭档HSTACK函数,实现逆向查询
一个小小的VSTACK函数,就能实现多工作表合并
VLOOKUP最新用法,提取混合内容中的手机号
带超链接的VLOOKUP函数,让查询体验起飞
最新方法来了:按指定的次数重复数据
合并工作表,用两个函数就搞定啦
比VLOOKUP还好用的多条件查找
点击谁就筛选谁,实现筛选自动化
以“数字”作为分隔符,进行分列
点击谁,就对谁自动条件求和
更多实用内容请在号内历史文章搜索 

Excel情报局
Excel表格爱好者,分享一些日常的积累。做一个职场人都能看懂的表格技巧公众号。多学一个Excel小技巧,会让你在职场中多一分底气与自信。(同名视频号)
 最新文章