新函數(shù)XMATCH用法
?
作者:小窩來(lái)源:部落窩教育發(fā)布時(shí)間:2023-12-11 18:31:05點(diǎn)擊:815
新函數(shù)XMATCH的用法。通過(guò)它與老函數(shù)MATCH的對(duì)比,可以發(fā)現(xiàn)它不需要排序、默認(rèn)完全匹配,公式書(shū)寫(xiě)簡(jiǎn)單,因此,如果你用的是高版本Excel或者新的WPS版本,則就用XMATCH取代MATCH吧。
新函數(shù)XMATCH是老函數(shù)MATCH的全面升級(jí)版,Excel2021及以上,或者WPS表格的新版本都支持。
1.作用與語(yǔ)法
在數(shù)組或者區(qū)域中查找指定數(shù)據(jù)并返回其相對(duì)位置。
=XMATCH(查找值,查找區(qū)域,[匹配方式],[查找方式])
第一、二參數(shù)必須有,作用與MATCH函數(shù)相同。
第三參數(shù),匹配方式,相比MATCH變化大,有4個(gè)選項(xiàng),具體如下??梢钥闯?,XMATCH最大的優(yōu)勢(shì)就是不需要排序。
第四參數(shù),查找方式,有4個(gè)選項(xiàng),若非必要,一般省略第4參數(shù)。MATCH函數(shù)沒(méi)有第四參數(shù)。
2.基本用法
1)完全匹配——查找等于查找值的值的位置
譬如查找等于100的數(shù)據(jù)的位置。用XMATCH很簡(jiǎn)潔,因?yàn)槟J(rèn)就是完全匹配,所以用前方兩個(gè)參數(shù)即可。而用MATCH函數(shù),必須加上第3參數(shù)0。
2)查找小于等于查找值的最大值的位置
譬如查找小于等于101的最大值的位置。由于當(dāng)前是降序排列,所以MATCH查找結(jié)果是錯(cuò)誤的,而XMATCH采用遍歷法沒(méi)有排序要求,結(jié)果是正確的。
3)查找大于等于查找值的最小值的位置
譬如查找大于等于98的最小值的位置。由于當(dāng)前A2:A10是降序排列,所以MATCH的查找結(jié)果與XMATCH的查找結(jié)果一致,都是正確的。
4)使用通配符模糊匹配
譬如查找包含101的數(shù)字的位置。XMATCH和MATCH都支持使用通配符進(jìn)行模糊查找,使用的區(qū)別在于,XMATCH有專(zhuān)門(mén)的匹配選項(xiàng)數(shù)字“2”,而MATCH需要沿用完全匹配選項(xiàng)“0”。
說(shuō)明:
由于當(dāng)前A2:A10是數(shù)字,所以要查找包含字符101的數(shù)據(jù)需要將其轉(zhuǎn)化為文本。公式中的TEXT函數(shù)作用就是將A2:A10轉(zhuǎn)化為文本。
5)逆序查找:查找最后一個(gè)符合要求的數(shù)據(jù)的位置
XMATCH默認(rèn)是從開(kāi)始到結(jié)尾查找第一個(gè)符合要求的數(shù)據(jù)的位置。如果要查找最后一個(gè),則需要逆序查找,第四參數(shù)寫(xiě)為-1。
6)二分法查找:排序后查找
XMATCH默認(rèn)都是采用遍歷法查找的,不需要排序。但如果強(qiáng)迫XMATCH采用二分法查找,就必須排序,然后設(shè)置第四參數(shù)為2或者-2。
譬如查找大于等于101的最小值的位置,公式=XMATCH(D46,A46:A54,1,-2)
說(shuō)明:
查找大于等于查找值的最小值,意味第三參數(shù)是1;當(dāng)前數(shù)據(jù)A46:A54是降序排列,用二分法查找,第四參數(shù)就必須是-2。
如果此處A46:A54是升序排列,第4參數(shù)就必須是2。
3.典型應(yīng)用
XMATCH與MATCH類(lèi)似,通常與其他函數(shù)搭配使用,最常見(jiàn)的就是與INDEX函數(shù)搭配。
1)常規(guī)查找
譬如查找“花無(wú)缺”的數(shù)學(xué)成績(jī)。
=INDEX(D2:D7,XMATCH(A11,A2:A7))
2)交叉查找
譬如查找花無(wú)缺的數(shù)學(xué)、鐵心蘭的英語(yǔ)成績(jī)。
=INDEX($C$14:$E$19,XMATCH(A23,$A$14:$A$19),XMATCH(B23,$C$13:$E$13))
說(shuō)明:
第一個(gè)XMATCH函數(shù)確定返回的行數(shù),第二個(gè)XMATCH函數(shù)確定返回的列數(shù)。
3)查找等級(jí)
如果用下限值作為查找區(qū)域,就是在各等級(jí)的下限值中查找小于等于查找值的最大下限所對(duì)應(yīng)的等級(jí)。譬如查各學(xué)員的成績(jī)等級(jí)。
=INDEX($G$27:$G$31,XMATCH(C27,{135,120,105,90,0},-1))
如果用上限制作為查找區(qū)域,就是在各等級(jí)的上限值中查找大于等于查找值的最小上限所對(duì)應(yīng)的等級(jí)。譬如查各學(xué)員的成績(jī)等級(jí)。
=INDEX($G$27:$G$31,XMATCH(C27,{150,134,119,104,89},1))
4)查找最接近的數(shù)
譬如下方,查找最接近70的數(shù)。
=INDEX(A35:A41,XMATCH(0,(70-A35:A41)^2,1))
說(shuō)明:
最接近的數(shù),意味著原數(shù)據(jù)與它之間的差值的絕對(duì)值最小或者差值的平方最小。
公式中XMATCH(0,(70-A35:A41)^2,1)表示求大于等于零的最小差值平方數(shù)的位置。
如果用絕對(duì)值,公式=INDEX(A35:A41,XMATCH(0,ABS(70-A35:A41),1))
本文配套的練習(xí)課件請(qǐng)?zhí)砑涌头⑿?span>buluowojiaoyu索取。
做Excel高手,快速提升工作效率,部落窩教育Excel精品好課任你選擇!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
版權(quán)申明:
本文作者小窩;部落窩教育享有稿件專(zhuān)有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車(chē)一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒(méi)有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問(wèn)題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說(shuō)是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!
最新教程
- SHEETSNAME,一鍵獲得所有工作表名稱完成目錄制作
- 延遲退休落地,快速查詢你上幾年才退休!
- 快速將PDF電子發(fā)票數(shù)據(jù)提取到發(fā)票明細(xì)登記表的方法
- 提取、查找、替換數(shù)據(jù)的王者——REGEXP正則函數(shù)
- Excel表10萬(wàn)行數(shù)據(jù),篩選卡頓怎么辦?
- 兩個(gè)不用嵌套的萬(wàn)能提取公式,含用REGEXP函數(shù)提取
- 用Excel制作刷題器,備考無(wú)憂 !
- 如何用LET+LAMBDA直接寫(xiě)遞歸公式?
- 用函數(shù)合并多個(gè)工作表數(shù)據(jù)
- 用LAMBDA自定義顏色求和函數(shù)