当前位置:首页 > 教育 > 正文

引X入室,当事人MATCH表示:很后悔,非常后悔!


此言不虚,MATCH函数有个最大的局限就是:排序

例如:查找小于等于80的值的位置,源数据需要升序排列;查找大于等于80的值的位置,源数据需要降序排列

关于MATCH的用法,点击这篇文章查看—— MATCH的用法及注意事项

0基础的同学也可以先查看这个小视频。


今天想和大家分享的是,引X入室,成功变身的XMATCH函数!

为什么说MATCH很后悔呢?

因为XMATCH实在是太好用了,全不用受排序的限制,当真如法外狂徒一般,直接掀了MATCH家的锅灶,并且抢了他的饭碗。


一起来看看吧!

XMATCH函数, 仅在office2021以上版本和WPS中才有。

即返回查找值在数组或单元格区域的相对位置,参数如下:


四个参数,查找值和查找数组是必填,匹配模式和搜索模式为可填。
匹配模式 主要有四种,见下表:


搜索模式 也是四种:


理论的东西太枯燥,下面,我们还是结合几个实际案例来看一下。

01
查找第一次和最后一次出库数量

如图所示,要查找复印机的首末出库数,输入两个公式:
第一次出库=XMATCH(H2,E2:E12,,1)
最后一次出库=XMATCH(H2,E2:E12,,-1)


函数原理 :第三参数省略,代表精确匹配,第四参数1为正向搜索,2为反向搜索。

现在,我们要求不返回位置,而是要返回具体的值,只需要把XMATCH获取到的位置作为第二参数,在INDEX提供的查找区域(数量列)中去取值就可以了。

函数公式如下:
=INDEX(F2:F12,XMATCH(H2,E2:E12,,1))
=INDEX(F2:F12,XMATCH(H2,E2:E12,,-1))

02
使用通配符查找

要查找“机”字结尾的产品的出库情况,可以把第3参数换成2,就是使用通配符的匹配模式,支持“*”,“?”,“~”,这三种通配符。

公式如下:
=INDEX(F2:F12,XMATCH("*机",E2:E12,2,1))
=INDEX(F2:F12,XMATCH("*机",E2:E12,2,-1))

03
根据范围下限确定库存是否紧急

如图所示,我们需要判断库存状态,辅助表中,给出了库存的下限值。

现在,我们就可以通过查找小于等于查找值的数字的位置,第三参数就可以写为-1。

比如出库数量为83,在下限范围内查找等于或者最近一个小于83的值,即为81,库存状态为正常。

输入公式:=INDEX($K$3:$K$6,XMATCH(F2,$J$3:$J$6,-1))


注意:写下限的时候,不用像match函数,需要升序排列,可以乱序。

04
根据范围上限确定库存是否紧急

同理,上面这个问题,我们也可以通过上限值来确定。

查找大于或者等于查找值的数字的位置,使用第三参数1。

=INDEX($K$3:$K$6,XMATCH(F2,$J$3:$J$6,1))

05
交叉查询

如图所示,我们需要在这张二维表中交叉查询某个值时,可以通过两个XMATCH分别来确定INDEX函数取值范围的行和列序号

输入公式
=INDEX(D2:G10,XMATCH(I3,C2:C10),XMATCH(J3,D1:G1))


注意 :如果默认是精确匹配,正向搜索,XMATCH中的第三和第四参数可以省略。
06
多条件查询

和MATCH函数一样,XMATCH可以通过重构查找值查找范围实现多条件查找。

公式如下:
=INDEX(E2:E9,XMATCH(G3&H3,C2:C9&D2:D9))

好的,以上就是今天要给大家分享的法外狂徒——XMATCH的常规用法

你可能想看:

有话要说...

取消
扫码支持 支付码