Excelin MATCH-funktiolla voit etsiä tietyn arvon suhteellisen sijainnin solualueella tai taulukossa.
MATCH-funktio on samanlainen kuin VLOOKUP-funktio, koska ne molemmat on luokiteltu Excelin haku-/viitefunktioihin. VLOOKUP etsii tiettyä arvoa sarakkeesta ja palauttaa arvon samalla rivillä, kun taas MATCH-funktio etsii tietyn arvon alueella ja palauttaa kyseisen arvon sijainnin.
Excelin MATCH-funktio etsii määritetyn arvon solualueelta tai taulukosta ja palauttaa kyseisen arvon ensimmäisen esiintymisen suhteellisen sijainnin alueella. MATCH-funktiolla voidaan myös etsiä tietty arvo ja palauttaa sitä vastaava arvo INDEX-funktion avulla (kuten Vlookup). Katsotaanpa, kuinka voit käyttää Excelin MATCH-funktiota hakuarvon sijainnin löytämiseen solualueelta.
Excelin MATCH-funktio
MATCH-funktio on Excelin sisäänrakennettu funktio, ja sitä käytetään ensisijaisesti paikantamaan hakuarvon suhteellinen sijainti sarakkeessa tai rivissä.
MATCH-funktion syntaksi:
=MATCH(hakuarvo,hakutaulukko,[hakutyyppi})Missä:
lookup_value – Arvo, jonka haluat etsiä tietyltä solualueelta tai taulukosta. Se voi olla numeerinen arvo, tekstiarvo, looginen arvo tai soluviittaus, jolla on arvo.
lookup_array – Solutaulukot, joista haet arvoa. Sen on oltava yksi sarake tai yksi rivi.
match_type – Se on valinnainen parametri, joka voidaan asettaa arvoon 0,1 tai -1 ja oletusarvo on 1.
- 0 etsii tarkkaa vastaavuutta, kun sitä ei löydy, palauttaa virheen.
- -1 etsii pienintä arvoa, joka on suurempi tai yhtä suuri kuin lookup_value, kun hakutaulukko nousevassa järjestyksessä.
- 1 etsii suurinta arvoa, joka on pienempi tai yhtä suuri kuin look_up-arvo, kun hakutaulukko laskevassa järjestyksessä.
Etsi tarkan osuman sijainti
Oletetaan, että meillä on seuraava tietojoukko, josta haluamme löytää tietyn arvon sijainnin.
Tässä taulukossa haluamme löytää kaupungin nimen sijainnin (Memphis) sarakkeesta (A2:A23), joten käytämme tätä kaavaa:
=MATCH("memphis",A2:A23;0)Kolmanneksi argumentiksi asetetaan "0", koska haluamme löytää tarkan vastaavuuden kaupungin nimelle. Kuten näet, kaupungin nimi "memphis" on kaavassa pienillä kirjaimilla, kun taas taulukossa kaupungin nimen ensimmäinen kirjain on isolla kirjaimella (Memphis). Silti kaava pystyy löytämään määritetyn arvon sijainnin annetulla alueella. Tämä johtuu siitä, että MATCH-funktio ei välitä kirjainkoolla.
merkintä: Jos hakuarvoa ei löydy hakualueelta tai jos määrität väärän hakualueen, funktio palauttaa #N/A-virheen.
Voit käyttää soluviittausta funktion ensimmäisessä argumentissa suoran arvon sijaan. Alla oleva kaava etsii arvon sijainnin solussa F2 ja palauttaa tuloksen soluun F3.
Etsi likimääräisen osuman sijainti
Voit etsiä hakuarvon likimääräistä tai tarkkaa vastaavuutta kahdella tavalla ja palauttaa sen sijainnin.
- Yksi tapa on löytää pienin arvo, joka on suurempi tai yhtä suuri (seuraava suurin vastaavuus) kuin määritetty arvo. Se voidaan saavuttaa asettamalla funktion viimeinen argumentti (match_type) arvoon "-1"
- Toinen tapa on suurin arvo, joka on pienempi tai yhtä suuri (seuraava pienin vastaavuus) kuin annettu arvo. Se voidaan saavuttaa asettamalla funktion match_typeksi "1"
Seuraava pienin ottelu
Jos funktio ei löydä tarkkaa vastaavuutta määritettyyn arvoon, kun hakutyyppi on '1', se etsii suurimman arvon, joka on hieman pienempi kuin määritetty arvo (mikä tarkoittaa seuraavaksi pienintä arvoa) ja palauttaa sijaintinsa . Jotta tämä toimisi, sinun on lajiteltava taulukko nousevaan järjestykseen, jos ei, seurauksena on virhe.
Esimerkissä käytämme alla olevaa kaavaa löytääksemme seuraavaksi pienimmän vastaavuuden:
=MATCH(F2;D2:D23;1)Kun tämä kaava ei löytänyt tarkkaa vastaavuutta solun F2 arvolle, se osoittaa seuraavaksi pienimmän arvon, eli 98, kohtaan (16).
Seuraava Suurin ottelu
Kun hakutyypiksi on asetettu -1 ja MATCH-funktio ei löydä tarkkaa vastaavuutta, se löytää pienimmän arvon, joka on suurempi kuin määritetty arvo (mikä tarkoittaa seuraavaksi suurinta arvoa) ja palauttaa sijaintinsa. Hakutaulukko on lajiteltava laskevaan järjestykseen tätä menetelmää varten, muuten se palauttaa virheen.
Syötä esimerkiksi seuraava kaava löytääksesi seuraavaksi suurimman hakuarvon vastaavuuden:
=MATCH(F2;D2:D23;-1)Tämä MATCH-funktio etsii arvoa F2:sta (55) hakualueelta D2:D23, ja kun se ei löydä tarkkaa vastaavuutta, se palauttaa seuraavan suurimman arvon eli 58:n sijainnin (16).
Jokerimerkkiottelu
Jokerimerkkejä voidaan käyttää MATCH-funktiossa vain, kun match_type-asetuksena on "0" ja hakuarvo on tekstimerkkijono. MATCH-funktiossa voit käyttää yleismerkkejä: tähti (*) ja kysymysmerkki (?).
- Kysymysmerkki (?) käytetään yhdistämään mikä tahansa yksittäinen merkki tai kirjain tekstimerkkijonoon.
- Tähti (*) käytetään yhdistämään mikä tahansa määrä merkkejä merkkijonon kanssa.
Käytimme esimerkiksi kahta '?' jokerimerkkiä MATCH-funktion lookup_value (Lo??n) -funktiossa löytääksemme arvon, joka vastaa tekstimerkkijonoa minkä tahansa kahden merkin kanssa (jokerimerkkien paikoissa). Ja funktio palauttaa vastaavan arvon suhteellisen sijainnin solussa E5.
=MATCH("Lo??n",A2:A22,0)
Voit käyttää (*) jokerimerkkiä samalla tavalla kuin (?), mutta tähti vastaa mitä tahansa merkkiä, kun taas kysymysmerkkiä käytetään mitä tahansa yksittäistä merkkiä.
Jos esimerkiksi käytät 'sp*', funktio voi täsmätä kaiuttimen, nopeuden tai spielbergin jne. kanssa. Mutta jos funktio löytää useita tai päällekkäisiä arvoja, jotka vastaavat hakuarvoa, se palauttaa vain ensimmäisen arvon sijainnin.
Esimerkissä kirjoitimme "Kil*o" lookup_value-argumenttiin. Joten MATCH()-funktio etsii tekstiä, joka sisältää "Kil" alussa, "o" lopussa ja minkä tahansa määrän merkkejä niiden välissä. 'Kil*o' vastaa Kilimanjaroa taulukossa ja siksi funktio palauttaa Kilimanjaron suhteellisen sijainnin, joka on 16.
INDEX ja MATCH
MATCH-funktioita käytetään harvoin yksinään. Ne yhdistettiin usein muiden toimintojen kanssa tehokkaiden kaavojen luomiseksi. Kun MATCH-toiminto yhdistetään INDEX-toimintoon, se voi suorittaa edistyneitä hakuja. Monet ihmiset haluavat edelleen käyttää VLOOKUP-toimintoa arvon etsimiseen, koska se on yksinkertaisempaa, mutta INDEX MATCH on joustavampi ja nopeampi kuin VLOOKUP.
VLOOKUP voi etsiä arvoa vain pystysuunnassa eli sarakkeissa, kun taas INDEX MATCH -yhdistelmä voi tehdä sekä pysty- että vaakasuuntaisia hakuja.
INDEKSI-funktio, jota käytetään arvon hakemiseen tietystä taulukon tai alueen kohdasta. MATCH-funktio palauttaa arvon suhteellisen sijainnin sarakkeessa tai rivissä. Yhdistettynä MATCH löytää tietyn arvon rivin tai sarakkeen numeron (sijainnin), ja INDEKSI-funktio hakee arvon kyseisen rivin ja sarakkeen numeron perusteella.
INDEX-funktion syntaksi:
=INDEKSI(taulukko,rivin_numero,[sarakkeen_numero],)Katsotaan joka tapauksessa, kuinka INDEX MATCH toimii esimerkin avulla.
Alla olevassa esimerkissä haluamme hakea "Quiz2"-pisteet opiskelijalle "Anne". Tätä varten käytämme alla olevaa kaavaa:
=INDEKSI(B2:F20,VASTAA(H2,A2:A20,0),3)INDEX tarvitsee rivin ja sarakkeen numeron arvon hakemiseksi. Yllä olevassa kaavassa sisäkkäinen MATCH-funktio löytää arvon 'Anne' (H2) rivinumeron (paikan). Sitten syötämme kyseisen rivin numeron INDEX-funktiolle alueella B2:F20 ja sarakenumerolla (3), jonka määritämme. Ja INDEKSI-funktio palauttaa pistemäärän '91'.
Kaksisuuntainen haku INDEXin ja MATCHin avulla
Voit myös käyttää INDEX- ja MATCH-funktioita arvon etsimiseen kaksiulotteiselta alueelta (kaksisuuntainen haku). Yllä olevassa esimerkissä käytimme MATCH-funktiota arvon rivinumeron paikallistamiseen, mutta syötimme sarakkeen numeron manuaalisesti. Mutta voimme löytää sekä rivin että sarakkeen yhdistämällä kaksi MATCH-funktiota, toinen INDEX-funktion argumenttiin rivin_numero ja toinen argumenttiin sarakkeen_numero.
Käytä tätä kaavaa kaksisuuntaiseen hakuun INDEXin ja MATCHin kanssa:
=INDEKSI(A1:F20,OTSAUS(H2,A2:A20,0),VASTAUS(H3,A1:F1,0))Kuten tiedämme, MATCH-funktio voi etsiä arvoa sekä vaaka- että pystysuunnassa. Tässä kaavassa sarakkeen_num-argumentin toinen MATCH-funktio etsii Quiz2:n (4) sijainnin ja toimittaa sen INDEX-funktiolle. Ja INDEKSI hakee tuloksen.
Nyt tiedät kuinka käyttää Match-toimintoa Excelissä.