Sisällys
Olemme aiemmin selittäneet aloittelijoille, kuinka VLOOKUPin perustoimintoja käytetään (englanniksi VLOOKUP, lyhenne sanoista "vertic lookup function"). Ja kokeneille käyttäjille näytettiin useita monimutkaisempia kaavoja.
Ja tässä artikkelissa yritämme antaa tietoja toisesta menetelmästä työskennellä vertikaalisen haun kanssa.
Saatat ihmetellä: "Miksi tämä on välttämätöntä?". Ja tämä on välttämätöntä kaikkien mahdollisten hakumenetelmien näyttämiseksi. Lisäksi lukuisat VLOOKUP-rajoitukset estävät usein halutun tuloksen saavuttamisen. Tässä suhteessa INDEX( ) MATCH( ) on paljon toimivampi ja monipuolisempi, ja niillä on myös vähemmän rajoituksia.
Perusteet INDEX MATCH
Koska tämän oppaan tarkoituksena on näyttää, kuinka hyvä tämä ominaisuus on, me Katsotaanpa perustietoja sen toiminnan periaatteista. Ja näytämme esimerkkejä ja pohdimme myös miksi, se on parempi kuin VLOOKUP ().
HAKEMISTO Funktioiden syntaksi ja käyttö
Tämä toiminto auttaa löytämään halutun arvon määritetyistä hakualueista sarakkeen tai rivin numeron perusteella. Syntaksi:
=INDEKSI(taulukko, rivin numero, sarakkeen numero):
- array – alue, jolla haku suoritetaan;
- rivin numero – määritetyssä taulukossa etsittävän rivin numero. Jos rivinumero on tuntematon, sarakkeen numero on määritettävä;
- sarakkeen numero – määritetystä taulukosta löytyvän sarakkeen numero. Jos arvoa ei tunneta, rivinumero vaaditaan.
Esimerkki yksinkertaisesta kaavasta:
=INDEKSI(A1:S10,2,3;XNUMX;XNUMX)
Toiminto hakee alueella A1 - C10. Numerot osoittavat, mistä rivistä (2) ja sarakkeesta (3) haluttu arvo näytetään. Tuloksena on solu C2.
Aika yksinkertaista, eikö? Mutta kun työskentelet oikeiden asiakirjojen kanssa, sinulla ei todennäköisesti ole tietoa sarakkeiden numeroista tai soluista. Tätä varten MATCH()-funktio on tarkoitettu.
MATCH-funktion syntaksi ja käyttö
MATCH()-funktio etsii haluttua arvoa ja näyttää sen likimääräisen numeron määritetyllä hakualueella.
Searchpos()-syntaksi näyttää tältä:
=MATCH(arvo hakuun, matriisi hakuun, hakutyyppi)
- hakuarvo – löydettävä numero tai teksti;
- searched array – alue, jossa haku suoritetaan;
- hakutyyppi – määrittää, etsitäänkö tarkka arvo vai sitä lähinnä olevat arvot:
- 1 (tai arvoa ei ole määritetty) – palauttaa suurimman arvon, joka on yhtä suuri tai pienempi kuin määritetty arvo;
- 0 – näyttää tarkan vastaavuuden haetun arvon kanssa. Yhdistelmässä INDEX() MATCH() tarvitset melkein aina tarkan vastaavuuden, joten kirjoitamme 0;
- -1 – Näyttää pienimmän arvon, joka on suurempi tai yhtä suuri kuin kaavassa määritetty arvo. Lajittelu suoritetaan laskevassa järjestyksessä.
Esimerkiksi alueella B1:B3 on rekisteröity New York, Pariisi, Lontoo. Alla oleva kaava näyttää numeron 3, koska Lontoo on listan kolmas:
=VALITSE (Lontoo,B1:B3,0)
INDEX MATCH -toiminnon käyttäminen
Olet varmaan jo alkanut ymmärtää, millä periaatteella näiden toimintojen yhteinen työ rakennetaan. Lyhyesti siis INDEX() etsii haluttua arvoa määritetyistä riveistä ja sarakkeista. Ja MATCH() näyttää näiden arvojen numerot:
=INDEKSI(sarake, josta arvo palautetaan, MATCH(hakuarvo, sarake, josta haetaan, 0))
Onko sinulla edelleen vaikeuksia ymmärtää, miten se toimii? Ehkä esimerkki selittää paremmin. Oletetaan, että sinulla on luettelo maailman pääkaupungeista ja niiden väestöstä:
Tietyn pääkaupungin, esimerkiksi Japanin pääkaupungin, väestön koon selvittämiseksi käytämme seuraavaa kaavaa:
=INDEKSI(C2:C10, OSTO(Japani, A2:A10,0))
Selitys:
- MATCH()-funktio etsii taulukosta A2:A10 arvon – "Japan" ja palauttaa luvun 3, koska Japani on luettelon kolmas arvo.
- Tämä luku meneerivinumero” INDEX()-kaavassa ja käskee funktiota tulostamaan arvon tältä riviltä.
Joten yllä olevasta kaavasta tulee vakiokaava INDEKSI(C2:C10,3). Kaava etsii soluista C2 - C10 ja palauttaa tiedot tämän alueen kolmannesta solusta, eli C4, koska lähtölaskenta alkaa toiselta riviltä.
Etkö halua määrätä kaupungin nimeä kaavaan? Kirjoita se sitten mihin tahansa soluun, sano F1, ja käytä sitä viitteenä MATCH()-kaavassa. Ja päädyt dynaamiseen hakukaavaan:
=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))
Tärkeää! Rivien määrä sisään ryhmä INDEX():n on oltava sama kuin rivien lukumäärä harkittu joukko MATCH(), muuten saat väärän tuloksen.
Odota hetki, miksi et vain käytä VLOOKUP()-kaavaa?
=VHAKU(F1, A2:C10, 3, epätosi)
Mitä järkeä on tuhlata aikaa näiden kaikkien näiden INDEX MATCHin monimutkaisten asioiden selvittämiseen?
Tässä tapauksessa ei ole väliä mitä toimintoa käytetään. Tämä on vain esimerkki ymmärtääksesi, kuinka INDEX()- ja MATCH()-funktiot toimivat yhdessä. Muut esimerkit osoittavat, mihin nämä toiminnot pystyvät tilanteissa, joissa VLOOKUP on voimaton.
INDEX MATCH tai VLOOKUP
Päättäessään käytettävästä hakukaavasta monet ovat yhtä mieltä siitä, että INDEX() ja MATCH() ovat huomattavasti parempia kuin VLOOKUP. Monet ihmiset käyttävät kuitenkin edelleen VLOOKUP(). Ensinnäkin VLOOKUP() on yksinkertaisempi, ja toiseksi käyttäjät eivät täysin ymmärrä kaikkia INDEX()- ja MATCH()-toimintojen käytön etuja. Ilman tätä tietoa kukaan ei suostu käyttämään aikaansa monimutkaisen järjestelmän tutkimiseen.
Tässä ovat INDEX()- ja MATCH():n tärkeimmät edut verrattuna VLOOKUP()
- Hae oikealta vasemmalle. VLOOKUP() ei voi hakea oikealta vasemmalle, joten etsimiesi arvojen on aina oltava taulukon vasemmanpuoleisissa sarakkeissa. Mutta INDEX() ja MATCH() voivat käsitellä tämän ilman ongelmia. Tämä artikkeli kertoo, miltä se näyttää käytännössä: kuinka löytää haluttu arvo vasemmalla puolella.
- Turvallinen pylväiden lisääminen tai poistaminen. VLOOKUP()-kaava näyttää vääriä tuloksia, kun sarakkeita poistetaan tai lisätään, koska VLOOKUP() tarvitsee tarkan sarakenumeron onnistuakseen. Luonnollisesti kun sarakkeita lisätään tai poistetaan, myös niiden numerot muuttuvat.
Ja INDEX()- ja MATCH()-kaavoissa määritetään sarakkeiden alue, ei yksittäisiä sarakkeita. Tämän seurauksena voit turvallisesti lisätä ja poistaa sarakkeita ilman, että sinun tarvitsee päivittää kaavaa joka kerta.
- Hakumäärillä ei ole rajoituksia. Käytettäessä VLOOKUP() hakuehtojen kokonaismäärä ei saa ylittää 255 merkkiä tai saat #ARVO! Joten jos tietosi sisältävät suuren määrän merkkejä, INDEX() ja MATCH() ovat paras vaihtoehto.
- Suuri käsittelynopeus. Jos pöytäsi ovat suhteellisen pieniä, et todennäköisesti huomaa mitään eroa. Mutta jos taulukko sisältää satoja tai tuhansia rivejä ja vastaavasti kaavoja on satoja ja tuhansia, INDEX () ja MATCH () selviytyvät paljon nopeammin kuin VLOOKUP (). Tosiasia on, että Excel käsittelee vain kaavassa määritetyt sarakkeet koko taulukon sijaan.
VLOOKUP()-funktion vaikutus suorituskykyyn on erityisen havaittavissa, jos laskentataulukko sisältää suuren määrän kaavoja, kuten VLOOKUP() ja SUM(). VLOOKUP()-funktioiden erilliset tarkistukset vaaditaan kunkin taulukon arvon jäsentämiseksi. Excelin on siis käsiteltävä valtava määrä tietoa, mikä hidastaa työtä huomattavasti.
Esimerkkejä kaavoista
Olemme jo selvittäneet näiden toimintojen hyödyllisyyden, joten voimme siirtyä mielenkiintoisimpaan osaan: tiedon soveltamiseen käytännössä.
Kaava etsiä oikealta vasemmalle
Kuten jo mainittiin, VLOOKUP ei voi suorittaa tällaista hakua. Joten jos halutut arvot eivät ole vasemmanpuoleisessa sarakkeessa, VLOOKUP() ei tuota tulosta. INDEX()- ja MATCH()-funktiot ovat monipuolisempia, eikä arvojen sijainnilla ole suurta merkitystä niiden toiminnassa.
Lisäämme esimerkiksi taulukkomme vasempaan reunaan sijoitussarakkeen ja yritämme selvittää, millä sijalla Maamme pääkaupunki on asukasluvulla mitattuna.
Kirjoitamme soluun G1 etsittävän arvon ja käytämme seuraavaa kaavaa etsiäksemme alueelta C1:C10 ja palauttamme vastaavan arvon arvosta A2:A10:
=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))
Kuiskata. Jos aiot käyttää tätä kaavaa useille soluille, varmista, että korjaat alueet absoluuttisella osoitteella (esim. 2 dollaria: 10 dollaria ja 2 dollaria: 4 dollaria 10 dollaria).
HAKEMISTO LISÄÄ ALTISTUMISTA LISÄÄ ALTISTUMISTA hakeaksesi sarakkeista ja riveistä
Yllä olevissa esimerkeissä olemme käyttäneet näitä toimintoja korvaamaan VLOOKUP()-funktiota arvojen palauttamiseksi ennalta määritetyltä rivialueelta. Mutta entä jos sinun on tehtävä matriisi- tai kaksipuolinen haku?
Se kuulostaa monimutkaiselta, mutta tällaisten laskelmien kaava on samanlainen kuin tavallinen INDEX() MATCH() -kaava, vain yhdellä erolla: MATCH()-kaavaa on käytettävä kahdesti. Ensimmäisen kerran saadaksesi rivinumeron ja toisen kerran saadaksesi sarakenumeron:
=INDEKSI(taulukko, MATCH(pystysuuntainen hakuarvo, hakusarake, 0), MATCH(vaakasuuntainen hakuarvo, hakurivi, 0))
Katsotaanpa alla olevaa taulukkoa ja yritetään tehdä kaava INDEKSI() EXPRESS() EXPRESS() näyttääkseen tietyn maan väestötiedot valitulta vuodelta.
Kohdemaa on solussa G1 (pystyhaku) ja kohdevuosi on solussa G2 (horisontaalinen haku). Kaava näyttää tältä:
=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))
Kuinka tämä kaava toimii
Kuten kaikki muutkin monimutkaiset kaavat, ne on helpompi ymmärtää jakamalla ne yksittäisiksi yhtälöiksi. Ja sitten voit ymmärtää, mitä kukin yksittäinen toiminto tekee:
- OSTO(G1,A2:A11,0) – etsii arvoa (G1) alueelta A2:A11 ja näyttää tämän arvon numeron, meidän tapauksessamme se on 2;
- HAKU(G2,B1:D1,0) – etsii arvoa (G2) alueelta B1:D1. Tässä tapauksessa tulos oli 3.
Löydetyt rivi- ja sarakenumerot lähetetään vastaavaan arvoon INDEX()-kaavassa:
=INDEKSI(B2:D11,2,3;XNUMX;XNUMX)
Tämän seurauksena meillä on arvo, joka on solussa 2 rivin ja 3 sarakkeen leikkauskohdassa alueella B2:D11. Ja kaava näyttää halutun arvon, joka on solussa D3.
Hae useilla ehdoilla INDEXin ja MATCHin avulla
Jos olet lukenut VLOOKUP()-oppaamme, olet todennäköisesti kokeillut useita hakukaavoja. Mutta tällä hakumenetelmällä on yksi merkittävä rajoitus - tarve lisätä apusarake.
Mutta hyvä uutinen on se INDEX()- ja MATCH():illa voit etsiä useita ehtoja ilman, että sinun tarvitsee muokata tai muuttaa laskentataulukkoasi.
Tässä on yleinen usean ehdon hakukaava INDEX() MATCH():
{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}
Muistilappu: tätä kaavaa on käytettävä yhdessä pikanäppäimen kanssa CTRL+SHIFT+ENTER.
Oletetaan, että sinun on löydettävä etsimäsi arvo kahden ehdon perusteella: Ostaja и Tuote.
Tämä vaatii seuraavan kaavan:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))
Tässä kaavassa C2:C10 on alue, jolla haku suoritetaan, F1 - tämä ehto, A2:A10 — on alue, jolla tilaa voidaan verrata, F2 - ehto 2, V2: V10 – vaihteluväli tilan 2 vertailua varten.
Älä unohda painaa yhdistelmää työn lopussa kaavan kanssa CTRL+SHIFT+ENTER – Excel sulkee kaavan automaattisesti aaltosulkeilla, kuten esimerkissä näkyy:
Jos et halua käyttää työssäsi taulukkokaavaa, lisää kaavaan toinen INDEX() ja paina ENTER, se näyttää esimerkissä:
Kuinka nämä kaavat toimivat
Tämä kaava toimii samalla tavalla kuin tavallinen INDEX() MATCH() -kaava. Voit etsiä useita ehtoja luomalla useita vääriä ja tosia ehtoja, jotka edustavat oikeita ja vääriä yksittäisiä ehtoja. Ja sitten nämä ehdot koskevat kaikkia vastaavia taulukon elementtejä. Kaava muuntaa False- ja True-argumentit arvoiksi 0 ja 1, ja tulostaa taulukon, jossa 1 on merkkijonosta löydetyt vastaavat arvot. MATCH() etsii ensimmäisen arvon, joka vastaa 1:tä, ja välittää sen INDEX()-kaavaan. Ja se puolestaan palauttaa jo halutun arvon määritetyllä rivillä halutusta sarakkeesta.
Kaava ilman taulukkoa riippuu INDEX():n kyvystä käsitellä niitä yksinään. Kaavan toinen INDEX() vastaa arvoa falsy (0), joten se välittää koko taulukon näillä arvoilla MATCH()-kaavaan.
Tämä on melko pitkä selitys tämän kaavan takana olevasta logiikasta. Lisätietoja saat artikkelista "INDEX MATCH useilla ehdoilla'.
AVERAGE, MAX ja MIN indeksissä INDEX ja MATCH
Excelissä on omat erikoistoiminnot keskiarvojen, maksimi- ja minimiarvojen etsimiseen. Mutta entä jos haluat saada tietoja solusta, joka liittyy näihin arvoihin? Tässä tapauksessa AVERAGE, MAX ja MIN on käytettävä yhdessä INDEXin ja MATCHin kanssa.
INDEX MATCH ja MAX
Voit etsiä suurimman arvon sarakkeesta D ja näyttää sen sarakkeessa C käyttämällä kaavaa:
=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))
INDEX MATCH ja MIN
Voit etsiä pienimmän arvon sarakkeesta D ja näyttää sen sarakkeessa C käyttämällä seuraavaa kaavaa:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))
SEARCH INDEX ja SERPENT
Voit etsiä keskiarvon sarakkeesta D ja näyttää tämän arvon C:ssä:
=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))
Riippuen siitä, miten tietosi on kirjoitettu, kolmas argumentti MATCH():lle on joko 1, 0 tai -1:
- jos sarakkeet on lajiteltu nousevaan järjestykseen, aseta 1 (silloin kaava laskee maksimiarvon, joka on pienempi tai yhtä suuri kuin keskiarvo);
- jos lajittelu on laskeva, niin -1 (kaava tulostaa minimiarvon, joka on suurempi tai yhtä suuri kuin keskiarvo);
- jos hakutaulukko sisältää arvon, joka on täsmälleen yhtä suuri kuin keskiarvo, aseta se arvoon 0.
Esimerkissämme populaatio on lajiteltu laskevaan järjestykseen, joten laitamme -1. Ja tuloksena on Tokio, koska väestön arvo (13,189 000) on lähimpänä keskiarvoa (XNUMX).
VLOOKUP() voi myös suorittaa tällaisia laskutoimituksia, mutta vain taulukkokaavana: VLOOKUP, jossa on AVERAGE, MIN ja MAX.
INDEX MATCH ja ESND/IFERROR
Olet todennäköisesti jo huomannut, että jos kaava ei löydä haluttua arvoa, se antaa virheen # N / A. Voit korvata tavallisen virheilmoituksen jollakin informatiivisemmalla. Aseta esimerkiksi argumentti kaavaan Vuonna XNUMX:
=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)
Jos syötät tällä kaavalla tietoja, joita ei ole taulukossa, lomake antaa sinulle määritetyn viestin.
Jos haluat kiinnittää kaikki virheet, paitsi Vuonna XNUMX voidaan käyttää IFERROR:
=IFERROR(INDEKSI(C2:C10,MATCH(F1,A2:A10,0)), "Jotain meni pieleen!")
Muista kuitenkin, että virheiden peittäminen tällä tavalla ei ole hyvä idea, koska vakiovirheet ilmoittavat kaavan rikkomuksista.
Toivomme, että INDEX MATCH()-funktion käyttöoppaastamme oli apua.