S funkcijo Excel MATCH lahko poiščete relativni položaj določene vrednosti v obsegu celic ali matriki.
Funkcija MATCH je podobna funkciji VLOOKUP, saj sta obe razvrščeni v Excelove funkcije iskanja/referenčnih funkcij. VLOOKUP išče določeno vrednost v stolpcu in vrne vrednost v isti vrstici, medtem ko funkcija MATCH išče določeno vrednost v obsegu in vrne položaj te vrednosti.
Excelova funkcija MATCH išče določeno vrednost v obsegu celic ali matriki in vrne relativni položaj prvega pojava te vrednosti v obsegu. Funkcijo MATCH lahko uporabite tudi za iskanje določene vrednosti in vrnitev ustrezne vrednosti s pomočjo funkcije INDEX (tako kot Vlookup). Poglejmo, kako uporabiti Excelovo funkcijo MATCH za iskanje položaja iskane vrednosti v obsegu celic.
Excelova funkcija MATCH
Funkcija MATCH je vgrajena funkcija v Excelu in se uporablja predvsem za lociranje relativnega položaja iskane vrednosti v stolpcu ali vrstici.
Sintaksa funkcije MATCH:
=MATCH(iskana_vrednost,niz_iskanja,[vrsta_ujemanja})Kje:
iskalna_vrednost – Vrednost, ki jo želite poiskati v določenem obsegu celic ali v matriki. Lahko je številčna vrednost, besedilna vrednost, logična vrednost ali sklic na celico, ki ima vrednost.
iskalni_array – Nizi celic, v katerih iščete vrednost. Biti mora en stolpec ali ena vrstica.
match_type – To je izbirni parameter, ki ga lahko nastavite na 0,1 ali -1, privzeta vrednost pa je 1.
- 0 išče natančno ujemanje, ko ga ne najde, vrne napako.
- -1 išče najmanjšo vrednost, ki je večja ali enaka lookup_value, ko je iskana matrika v naraščajočem vrstnem redu.
- 1 išče največjo vrednost, ki je manjša ali enaka vrednosti look_up, ko je niz iskanja v padajočem vrstnem redu.
Poiščite položaj natančnega ujemanja
Predpostavimo, da imamo naslednji nabor podatkov, kjer želimo najti položaj določene vrednosti.
V tej tabeli želimo najti položaj imena mesta (Memphis) v stolpcu (A2:A23), zato uporabimo to formulo:
=MACH("memphis",A2:A23,0)Tretji argument je nastavljen na '0', ker želimo poiskati natančno ujemanje imena mesta. Kot lahko vidite, je ime mesta "memphis" v formuli napisano z malimi črkami, medtem ko je v tabeli prva črka imena mesta velika (Memphis). Kljub temu lahko formula najde položaj določene vrednosti v danem območju. To je zato, ker je funkcija MATCH neobčutljiva na velike in male črke.
Opomba: Če lookup_value ni najdena v obsegu iskanja ali če podate napačen obseg iskanja, bo funkcija vrnila napako #N/A.
Namesto neposredne vrednosti lahko uporabite sklic na celico v prvem argumentu funkcije. Spodnja formula poišče položaj vrednosti v celici F2 in vrne rezultat v celici F3.
Poiščite položaj približnega ujemanja
Obstajata dva načina, kako lahko iščete približno ali natančno ujemanje iskane vrednosti in vrnete njen položaj.
- Eden od načinov je, da poiščete najmanjšo vrednost, ki je večja ali enaka (naslednje največje ujemanje) navedeni vrednosti. To je mogoče doseči z nastavitvijo zadnjega argumenta (match_type) funkcije kot '-1'
- Drug način je največja vrednost, ki je manjša ali enaka (naslednje najmanjše ujemanje) dani vrednosti. To je mogoče doseči z nastavitvijo match_type funkcije kot '1'
Naslednja najmanjša tekma
Če funkcija ne najde natančnega ujemanja z določeno vrednostjo, ko je vrsta ujemanja nastavljena na '1', poišče največjo vrednost, ki je nekoliko manjša od podane vrednosti (kar pomeni naslednjo najmanjšo vrednost) in vrne njen položaj . Da bo to delovalo, morate matriko razvrstiti v naraščajočem vrstnem redu, če ne, bo prišlo do napake.
V primeru uporabljamo spodnjo formulo za iskanje naslednjega najmanjšega ujemanja:
=MACH(F2,D2:D23,1)Ko ta formula ni mogla najti natančnega ujemanja za vrednost v celici F2, kaže na položaj (16) naslednje najmanjše vrednosti, to je 98.
Naslednja največja tekma
Ko je vrsta ujemanja nastavljena na '-1' in funkcija MATCH ne najde natančnega ujemanja, poišče najmanjšo vrednost, ki je večja od podane vrednosti (kar pomeni naslednjo največjo vrednost), in vrne njen položaj. Iskalni niz mora biti za to metodo razvrščen v padajočem vrstnem redu, sicer bo vrnil napako.
Vnesite na primer naslednjo formulo, da poiščete naslednje največje ujemanje z vrednostjo iskanja:
=MACH(F2,D2:D23,-1)Ta funkcija MATCH išče vrednost v F2 (55) v iskalnem območju D2:D23 in ko ne najde natančnega ujemanja, vrne položaj (16) naslednje največje vrednosti, to je 58.
Ujemanje z nadomestnimi znaki
Nadomestne znake je mogoče uporabiti v funkciji MATCH samo, če je match_type nastavljen na '0' in je iskalna vrednost besedilni niz. Obstajajo nadomestni znaki, ki jih lahko uporabite v funkciji MATCH: zvezdica (*) in vprašaj (?).
- Vprašaj (?) se uporablja za ujemanje katerega koli posameznega znaka ali črke z besedilnim nizom.
- zvezdica (*) se uporablja za ujemanje poljubnega števila znakov z nizom.
Na primer, uporabili smo dva nadomestna znaka '?' v lookup_value (Lo??n) funkcije MATCH, da bi našli vrednost, ki se ujema z besedilnim nizom s katerim koli dvema znakoma (na mestih nadomestnih znakov). Funkcija vrne relativni položaj ujemajoče se vrednosti v celici E5.
=MATCH("Lo??n",A2:A22,0)
Nadomestni znak (*) lahko uporabite na enak način kot (?), vendar se zvezdica uporablja za ujemanje poljubnega števila znakov, medtem ko se vprašaj uporablja za ujemanje s katerim koli posameznim znakom.
Če na primer uporabite 'sp*', se funkcija lahko ujema z zvočnikom, hitrostjo ali spielbergom itd. Če pa funkcija najde več/podvojenih vrednosti, ki se ujemajo z vrednostjo iskanja, bo vrnila samo položaj prve vrednosti.
V primeru smo v argument lookup_value vnesli »Kil*o«. Funkcija MATCH() torej išče besedilo, ki vsebuje 'Kil' na začetku, 'o' na koncu in poljubno število znakov vmes. 'Kil*o' se ujema s Kilimandžarom v matriki in zato funkcija vrne relativni položaj Kilimandžara, ki je 16.
INDEKS in UJEMANJE
Funkcije MATCH se le redko uporabljajo same. Pogosto so združene z drugimi funkcijami za ustvarjanje močnih formul. Ko je funkcija MATCH kombinirana s funkcijo INDEX, lahko izvaja napredno iskanje. Mnogi ljudje še vedno raje uporabljajo VLOOKUP za iskanje vrednosti, ker je enostavnejši, vendar je INDEX MATCH bolj prilagodljiv in hitrejši kot VLOOKUP.
VLOOKUP lahko išče vrednost samo navpično, t.j. stolpce, medtem ko kombinacija INDEX MATCH lahko izvaja tako navpično kot vodoravno iskanje.
Funkcija INDEX, ki se uporablja za pridobivanje vrednosti na določenem mestu v tabeli ali obsegu. Funkcija MATCH vrne relativni položaj vrednosti v stolpcu ali vrstici. Ko je združen, MATCH poišče številko vrstice ali stolpca (lokacijo) določene vrednosti, funkcija INDEX pa pridobi vrednost na podlagi te številke vrstice in stolpca.
Sintaksa funkcije INDEX:
=INDEX(matrika,številka_vrstice,[številka_stolpca],)Kakorkoli že, poglejmo, kako deluje INDEX MATCH s primerom.
V spodnjem primeru želimo pridobiti oceno 'Quiz2' za študentko 'Anne'. Za to bomo uporabili spodnjo formulo:
=INDEX(B2:F20,UJEMANJE(H2,A2:A20,0),3)INDEX potrebuje številko vrstice in stolpca, da pridobi vrednost. V zgornji formuli ugnezdena funkcija MATCH najde številko vrstice (položaj) vrednosti 'Anne' (H2). Nato dodamo to številko vrstice funkciji INDEX z obsegom B2:F20 in številko stolpca (3), ki jo določimo. Funkcija INDEX pa vrne rezultat '91'.
Dvosmerno iskanje z INDEX in MATCH
Za iskanje vrednosti v dvodimenzionalnem obsegu (dvosmerno iskanje) lahko uporabite tudi funkciji INDEX in MATCH. V zgornjem primeru smo uporabili funkcijo MATCH za iskanje številke vrstice vrednosti, vendar smo številko stolpca vnesli ročno. Lahko pa najdemo tako vrstico kot stolpec tako, da ugnezdimo dve funkciji MATCH, eno v argument row_num in drugo v argument column_num funkcije INDEX.
Uporabite to formulo za dvosmerno iskanje z INDEX in MATCH:
=INDEX(A1:F20,UJEMANJE(H2,A2:A20,0),MACH(H3,A1:F1,0))Kot vemo, lahko funkcija MATCH išče vrednost tako vodoravno kot navpično. V tej formuli druga funkcija MATCH v argumentu colum_num najde položaj Quiz2 (4) in ga posreduje funkciji INDEX. In INDEX pridobi rezultat.
Zdaj veste, kako uporabljati funkcijo Match v Excelu.