Saate kasutada Exceli funktsiooni MATCH, et leida konkreetse väärtuse suhteline asukoht lahtrivahemikus või massiivis.
Funktsioon MATCH on sarnane funktsiooniga VLOOKUP, kuna mõlemad on liigitatud Exceli otsingu-/viitefunktsioonide alla. VLOOKUP otsib veerus kindlat väärtust ja tagastab väärtuse samas reas, samas kui funktsioon MATCH otsib teatud väärtust vahemikus ja tagastab selle väärtuse asukoha.
Exceli funktsioon MATCH otsib lahtrivahemikus või massiivist määratud väärtust ja tagastab selle väärtuse esmakordse suhte suhtelise positsiooni vahemikus. Funktsiooni MATCH saab kasutada ka teatud väärtuse otsimiseks ja sellele vastava väärtuse tagastamiseks funktsiooni INDEX abil (nagu ka Vlookup). Vaatame, kuidas kasutada Exceli funktsiooni MATCH otsinguväärtuse asukoha leidmiseks lahtrivahemikus.
Exceli MATCH funktsioon
Funktsioon MATCH on Exceli sisseehitatud funktsioon ja seda kasutatakse peamiselt otsinguväärtuse suhtelise asukoha leidmiseks veerus või reas.
Funktsiooni MATCH süntaks:
=VASTANE(otsingu_väärtus,otsingu_massiiv,[vaste_tüüp})Kus:
lookup_value – Väärtus, mida soovite otsida määratud lahtrivahemikust või massiivist. See võib olla numbriline väärtus, tekstiväärtus, loogiline väärtus või lahtriviide, millel on väärtus.
otsingu_massiiv – lahtrite massiivid, millest väärtust otsite. See peab olema üks veerg või üks rida.
vaste_tüüp – See on valikuline parameeter, mille saab määrata väärtusele 0,1 või -1 ja vaikeväärtus on 1.
- 0 otsib täpset vastet, kui seda ei leita, tagastab veateate.
- -1 otsib väikseimat väärtust, mis on suurem või võrdne otsingu_väärtusega, kui otsingumassiivi on kasvavas järjekorras.
- 1 otsib suurimat väärtust, mis on väiksem või võrdne look_up väärtusest, kui otsingumassiivi on kahanevas järjekorras.
Leidke täpse vaste positsioon
Oletame, et meil on järgmine andmestik, kust tahame leida teatud väärtuse asukoha.
Selles tabelis tahame leida veerust (A2:A23) linna nime (Memphis), seega kasutame järgmist valemit:
=MATCH("memphis",A2:A23,0)Kolmas argument on seatud väärtusele 0, kuna tahame leida linna nimele täpse vaste. Nagu näete, on linna nimi "memphis" valemis väiketähtedega, samas kui tabelis on linna nime esimene täht suure tähega (Memphis). Siiski suudab valem leida määratud väärtuse asukoha antud vahemikus. Põhjus on selles, et funktsioon MATCH on tõstutundlik.
Märge: Kui otsinguvahemikust otsingu_väärtust ei leitud või kui määrate vale otsinguvahemiku, tagastab funktsioon veateate #N/A.
Funktsiooni esimeses argumendis saate otsese väärtuse asemel kasutada lahtriviidet. Allolev valem leiab väärtuse asukoha lahtris F2 ja tagastab tulemuse lahtris F3.
Leidke ligikaudne vaste
Otsinguväärtuse ligikaudse või täpse vaste otsimiseks ja selle positsiooni tagastamiseks on kaks võimalust.
- Üks võimalus on leida väikseim väärtus, mis on määratud väärtusest suurem või sellega võrdne (suuruselt järgmine vaste). Seda saab saavutada, määrates funktsiooni viimase argumendi (match_type) väärtuseks "-1"
- Teine võimalus on suurim väärtus, mis on väiksem või võrdne (väikseim vaste) antud väärtusega. Seda saab saavutada, määrates funktsiooni match_type väärtuseks "1"
Järgmine väikseim vaste
Kui funktsioon ei leia määratud väärtusele täpset vastet, kui vaste tüübiks on seatud '1', otsib see suurima väärtuse, mis on veidi väiksem määratud väärtusest (mis tähendab väikseimat järgmist väärtust) ja tagastab oma asukoha . Selle toimimiseks peate sorteerima massiivi kasvavas järjekorras, vastasel korral tekib tõrge.
Näites kasutame järgmise väikseima vaste leidmiseks järgmist valemit:
=VASTA(F2,D2:D23,1)Kui see valem ei leidnud täpset vastet lahtris F2 olevale väärtusele, osutab see järgmise väikseima väärtuse positsioonile (16), st 98.
Järgmine suurim matš
Kui vaste tüübiks on seatud „-1” ja funktsioon MATCH ei leia täpset vastet, leiab see väikseima väärtuse, mis on määratud väärtusest suurem (mis tähendab suurimat väärtust) ja tagastab oma positsiooni. Selle meetodi jaoks tuleb otsingumassiivi sortida kahanevas järjekorras, vastasel juhul tagastab see veateate.
Näiteks sisestage järgmine valem, et leida otsinguväärtusele järgmine suurim vaste:
=VASTA(F2,D2:D23,-1)See funktsioon MATCH otsib väärtust F2 (55) otsinguvahemikus D2:D23 ja kui see ei leia täpset vastet, tagastab see suuruselt järgmise väärtuse positsiooni (16), st 58.
Metamärgi vaste
Metamärke saab funktsioonis MATCH kasutada ainult siis, kui vaste_tüüp on seatud väärtusele 0 ja otsinguväärtus on tekstistring. Funktsioonis MATCH saate kasutada metamärke: tärn (*) ja küsimärk (?).
- Küsimärk (?) kasutatakse mis tahes üksiku märgi või tähe sobitamiseks tekstistringiga.
- Tärn (*) kasutatakse suvalise arvu märkide sobitamiseks stringiga.
Näiteks kasutasime funktsiooni MATCH otsingu_väärtuses (Lo??n) kahte metamärki "?", et leida väärtus, mis sobib tekstistringiga mis tahes kahe märgiga (metamärkide kohtades). Ja funktsioon tagastab sobiva väärtuse suhtelise asukoha lahtris E5.
=MATCH("Lo??n",A2:A22,0)
Saate kasutada metamärki (*) samamoodi nagu (?), kuid tärni kasutatakse suvalise arvu märkide vastendamiseks, küsimärki aga mis tahes üksiku märgi vastendamiseks.
Näiteks kui kasutate 'sp*', võib funktsioon sobida kõlari, kiiruse või spielbergiga jne. Kui aga funktsioon leiab otsinguväärtusele vastavaid mitu/duplikaatväärtust, tagastab see ainult esimese väärtuse asukoha.
Näites sisestasime argumendisse lookup_value "Kil*o". Seega otsib funktsioon MATCH() teksti, mille alguses on 'Kil', lõpus 'o' ja suvaline arv märke nende vahel. "Kil*o" vastab massiivi Kilimanjarole ja seetõttu tagastab funktsioon Kilimanjaro suhtelise asukoha, mis on 16.
INDEX ja MATCH
Funktsioone MATCH kasutatakse harva eraldi. Sageli ühendati need võimsate valemite loomiseks teiste funktsioonidega. Kui funktsioon MATCH on kombineeritud funktsiooniga INDEX, saab see teha täpsemaid otsinguid. Paljud inimesed eelistavad endiselt kasutada väärtuse otsimiseks funktsiooni VLOOKUP, kuna see on lihtsam, kuid INDEX MATCH on paindlikum ja kiirem kui VLOOKUP.
VLOOKUP saab väärtusi otsida ainult vertikaalselt, st veergudest, samas kui kombinatsioon INDEX MATCH saab teha nii vertikaalseid kui ka horisontaalseid otsinguid.
Funktsioon INDEX, mida kasutatakse väärtuse hankimiseks tabeli või vahemiku kindlast asukohast. Funktsioon MATCH tagastab väärtuse suhtelise asukoha veerus või reas. Kombineerimisel leiab MATCH konkreetse väärtuse rea või veeru numbri (asukoha) ja funktsioon INDEX hangib väärtuse selle rea ja veeru numbri alusel.
Funktsiooni INDEX süntaks:
=INDEKS(massiiv,rea_arv, [veeru_arv],)Igatahes vaatame näitega, kuidas INDEX MATCH töötab.
Allolevas näites tahame hankida õpilase "Anne" skoori "Quiz2". Selleks kasutame järgmist valemit:
=INDEKS(B2:F20,VASTA(H2,A2:A20,0),3)INDEX vajab väärtuse toomiseks rea ja veeru numbrit. Ülaltoodud valemis leiab pesastatud funktsioon MATCH väärtuse 'Anne' (H2) reanumbri (positsiooni). Seejärel anname selle rea numbri funktsioonile INDEX vahemikuga B2:F20 ja veeru numbriga (3), mille me määrame. Ja funktsioon INDEX tagastab tulemuse '91'.
Kahesuunaline otsing funktsioonidega INDEX ja MATCH
Saate kasutada ka funktsioone INDEX ja MATCH, et otsida väärtust kahemõõtmelisest vahemikust (kahesuunaline otsing). Ülaltoodud näites kasutasime väärtuse reanumbri leidmiseks funktsiooni MATCH, kuid sisestasime veeru numbri käsitsi. Kuid me leiame nii rea kui ka veeru, kui pesastame kaks funktsiooni MATCH, millest üks on funktsiooni INDEX argumendis rida_num ja teine veeru_num.
Kasutage seda valemit kahesuunaliseks otsinguks koos INDEXi ja MATCHiga:
=INDEKS(A1:F20,VASTA(H2,A2:A20,0),VASTA(H3,A1:F1,0))Nagu me teame, saab funktsioon MATCH otsida väärtust nii horisontaalselt kui ka vertikaalselt. Selles valemis leiab veeru_num argumendi teine funktsioon MATCH ülesande Quiz2 (4) asukoha ja edastab selle funktsiooni INDEX. Ja INDEKS otsib skoori.
Nüüd teate, kuidas kasutada Exceli funktsiooni Match.