當前位置:首頁 > 教育 > 正文

引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的常規用法

你可能想看:

有話要說...

取消
掃碼支持 支付碼