INDEX & MATCH -toimintoyhdistelmä Excelissä (10 helppoa esimerkkiä)

Excelissä on paljon toimintoja - noin 450+ niitä.

Ja monet näistä ovat yksinkertaisesti mahtavia. Työmäärä, jonka voit tehdä muutamalla kaavalla, yllättää minut edelleen (vaikka olen käyttänyt Exceliä yli 10 vuotta).

Ja kaikkien näiden hämmästyttävien toimintojen joukossa erottuu INDEX MATCH -toimintoyhdistelmä.

Olen suuri INDEX MATCH -yhdistelmän fani ja olen tehnyt sen melko selväksi monta kertaa.

Kirjoitin jopa artikkelin Index Match VS VLOOKUPista, joka herätti hieman keskustelua (voit tarkistaa ilotulituksen kommentit -osiosta).

Tänään kirjoitan tämän artikkelin, joka keskittyy yksinomaan Index Matchiin näyttääkseni sinulle yksinkertaisia ​​ja edistyneitä skenaarioita, joissa voit käyttää tätä tehokasta kaavayhdistelmää ja saada työn aikaan.

merkintä: Excelissä on muitakin hakukaavoja - kuten VLOOKUP ja HLOOKUP, ja nämä ovat hienoja. Monien mielestä VLOOKUP on helpompi käyttää (ja se pitää paikkansa). Uskon, että INDEX MATCH on monissa tapauksissa parempi vaihtoehto. Mutta koska ihmiset kokevat sen vaikeaksi, sitä käytetään vähemmän. Joten yritän yksinkertaistaa sitä tämän opetusohjelman avulla.

Ennen kuin näytän teille, kuinka INDEX MATCH -yhdistelmä muuttaa analyytikkojen ja datatieteilijöiden maailmaa, haluan ensin esitellä teille yksittäiset osat - INDEX- ja MATCH -toiminnot.

INDEX-toiminto: löytää arvoihin perustuvat koordinaatit

Helpoin tapa ymmärtää indeksitoiminnon toiminta on ajatella sitä GPS -satelliittina.

Heti kun kerrot satelliitille leveys- ja pituuskoordinaatit, se tietää tarkalleen minne mennä ja löytää kyseisen sijainnin.

Joten huolimatta siitä, että sillä on hämmästyttävä määrä lat-pitkiä yhdistelmiä, satelliitti tietäisi tarkalleen, mistä etsiä.

Etsin nopeasti työpaikkaani ja sain tämän.

Joka tapauksessa maantiedettä riittää.

Aivan kuten satelliitti tarvitsee leveys- ja pituusastekoordinaatit, Excelin INDEX -toiminto tarvitsee rivin ja sarakkeen numeron tietääkseen, mihin soluun viittaat.

Ja se on Excel INDEX -toiminto pähkinänkuoressa.

Joten määrittele se yksinkertaisin sanoin sinulle.

INDEX -funktio löytää rivin ja sarakkeen numeron avulla solun annetulta alueelta ja palauttaa sen arvon.

INDEX on sinänsä hyvin yksinkertainen toiminto, jolla ei ole apuohjelmaa. Loppujen lopuksi et todennäköisesti tiedä rivien ja sarakkeiden numeroita.

Mutta…

Se, että voit käyttää sitä muiden toimintojen kanssa (vihje: MATCH), jotka voivat löytää rivin ja sarakkeen numeron, tekee INDEXistä erittäin tehokkaan Excel -toiminnon.

Alla on INDEX -funktion syntaksi:

= INDEKSI (taulukko, rivin_numero, [sarakkeen_numero]) = INDEKSI (taulukko, rivin_numero, [sarakkeen_numero], [alueen_numero])

• array - a solualue tai matriisivakio.
• rivinumero - rivin numero, josta arvo on haettava.
• [col_num] - sarakkeen numero, josta arvo on haettava. Vaikka tämä on valinnainen argumentti, mutta jos rivinumeroa ei anneta, se on annettava.
• [alueen_numero] - (Valinnainen) Jos taulukko -argumentti koostuu useista alueista, tätä numeroa käytettäisiin viittauksen valitsemiseksi kaikista alueista.

INDEX -funktiolla on kaksi syntaksia (vain FYI).

Ensimmäistä käytetään useimmissa tapauksissa. Toista käytetään vain kehittyneissä tapauksissa (kuten kolmisuuntaisen haun tekeminen), jota käsittelemme yhdessä esimerkissä myöhemmin tässä opetusohjelmassa.

Mutta jos olet uusi tässä toiminnossa, muista vain ensimmäinen syntaksi.

Alla on video, joka selittää INDEX -toiminnon käytön

MATCH -toiminto: Etsii sijainnin hakuarvon perusteella

Palatakseni edelliseen pituus- ja leveysasteeseen, MATCH on toiminto, joka voi löytää nämä sijainnit (Excel -laskentataulukkomaailmassa).

Yksinkertaisella kielellä Excel MATCH -toiminto voi löytää solun sijainnin alueella.

Ja millä perusteella se löytäisi solun sijainnin?

Perustuu hakuarvoon.

Jos sinulla on esimerkiksi alla oleva luettelo ja haluat löytää nimen "Mark" sijainnin, voit käyttää MATCH -toimintoa.

Funktio palauttaa 3, koska se on solun sijainti, jossa on nimi Mark.

MATCH -toiminto alkaa etsiä ylhäältä alas etsintäarvoa (joka on "Merkki") määritetyllä alueella (joka on tässä esimerkissä A1: A9). Heti kun se löytää nimen, se palauttaa sijainnin kyseisellä alueella.

Alla on Excelin MATCH -funktion syntaksi.

= MATCH (haun_arvo, hakutoiminto, [hakutyyppi])

• lookup_value - Arvo, jolle etsit vastaavuutta lookup_array.
• lookup_array - Solualue, josta etsit haun_arvoa.
• [ottelutyyppi] - (Valinnainen) Tämä määrittää, miten Excelin pitäisi etsiä vastaava arvo. Se voi kestää kolme arvoa -1, 0 tai 1.

Ottelutyyppiargumentin ymmärtäminen MATCH -toiminnossa

On vielä yksi asia, joka sinun on tiedettävä MATCH -toiminnosta, ja se koskee sitä, miten se kulkee tietojen läpi ja löytää solun sijainnin.

MATCH -funktion kolmas argumentti voi olla 0, 1 tai -1.

Alla on selitys näiden argumenttien toiminnasta:

• 0 - tämä etsii arvon täsmällisen vastaavuuden. Jos tarkka vastaavuus löytyy, MATCH -toiminto palauttaa solun sijainnin. Muuten se palauttaa virheen.
• 1 - tämä löytää suurimman arvon, joka on pienempi tai yhtä suuri kuin hakuarvo. Jotta tämä toimisi, tietoalue on lajiteltava nousevaan järjestykseen.
• -1 - tämä löytää pienimmän arvon, joka on suurempi tai yhtä suuri kuin hakuarvo. Jotta tämä toimisi, tietoalue on lajiteltava laskevaan järjestykseen.

Alla on video, joka selittää MATCH -toiminnon käytön (yhdessä hakutyyppiargumentin kanssa)

Yhteenvetona ja lyhyesti sanottuna:

• INDEX tarvitsee solun sijainnin (rivin ja sarakkeen numero) ja antaa solun arvon.
• MATCH löytää paikan käyttämällä hakuarvoa.

Yhdistämme heidät luomaan voimalaitoksen (INDEX + MATCH)

Nyt kun sinulla on perustiedot INDEX- ja MATCH -toimintojen toiminnasta erikseen, yhdistämme nämä kaksi ja opimme kaikista niistä upeista asioista, joita se voi tehdä.

Ymmärtääkseni tämän paremmin, minulla on muutamia esimerkkejä, joissa käytetään INDEX MATCH -yhdistelmää.

Aloitan yksinkertaisella esimerkillä ja näytän sitten myös joitain edistyneitä käyttötapauksia.

Lataa esimerkkitiedosto napsauttamalla tätä

Esimerkki 1: Yksinkertainen haku käyttämällä INDEX MATCH -yhdistelmää

Tehdään yksinkertainen haku INDEX/MATCH -toiminnolla.

Alla on taulukko, jossa minulla on kymmenen oppilaan arvosanat.

Tästä taulukosta haluan löytää Jimin merkit.

Alla on kaava, jolla voit tehdä tämän helposti:

= INDEKSI ($ A $ 2: $ B $ 11, MATCH ("Jim", $ A $ 2: $ A $ 11,0), 2)

Jos luulet, että tämä voidaan tehdä helposti VLOOKUP -toiminnolla, olet oikeassa! Tämä ei ole paras käyttö INDEX MATCH -mahtavuuteen. Huolimatta siitä, että olen INDEX MATCHin fani, se on hieman vaikeampaa kuin VLOOKUP. Jos haluat hakea tietoja oikealla olevasta sarakkeesta, sinun kannattaa käyttää VLOOKUP -toimintoa.

Syy, miksi olen näyttänyt tämän esimerkin, joka voidaan helposti tehdä myös VLOOKUPilla, on näyttää sinulle, kuinka INDEX MATCH toimii yksinkertaisessa ympäristössä.

Nyt haluan näyttää INDEX MATCHin edun.

Oletetaan, että sinulla on samat tiedot, mutta sen sijaan, että ne olisivat sarakkeissa, ne ovat riveillä (kuten alla on esitetty).

Tiedätkö mitä, voit silti käyttää INDEX MATCH -yhdistelmää saadaksesi Jimin merkit.

Alla on kaava, joka antaa sinulle tuloksen:

= INDEKSI ($ B $ 1: $ K $ 2,2, MATCH ("Jim", $ B $ 1: $ K $ 1,0))

Huomaa, että sinun on muutettava aluetta ja vaihdettava rivi-/sarakeosia, jotta tämä kaava toimisi myös vaakatiedoissa.

Tätä ei voi tehdä VLOOKUPilla, mutta voit silti tehdä sen helposti HLOOKUPilla.

INDEX MATCH -yhdistelmä pystyy käsittelemään vaakasuuntaista ja pystysuuntaista dataa helposti.

Lataa esimerkkitiedosto napsauttamalla tätä

Esimerkki 2: Etsi vasemmalle

Se on yleisempi kuin luulet.

Usein saatat joutua noutamaan tiedot sarakkeesta, joka on hakuarvon sarakkeen vasemmalla puolella.

Jotain alla olevan kuvan mukaisesti:

Jos haluat selvittää Michaelin myynnin, sinun on tehtävä haku vasemmalta.

Jos ajattelet VLOOKUPia, anna minun pysäyttää sinut.

VLOOKUP ei ole tarkoitettu vasemmanpuoleisten arvojen etsimiseen ja noutamiseen.

Voitko silti tehdä sen käyttämällä VLOOKUPia?

Kyllä sinä voit!

Mutta siitä voi tulla pitkä ja ruma kaava.

Joten jos haluat etsiä ja noutaa tietoja vasemmalla olevista sarakkeista, sinun on parempi käyttää INDEX MATCH -yhdistelmää.

Alla on kaava, jolla saadaan Michaelin myyntinumero:

= INDEKSI ($ A $ 2: $ C $ 11, MATCH ("Michael", C2: C11,0), 2)

Toinen kohta tässä INDEX MATCH: ssa. VLOOKUP voi noutaa tiedot vain sarakkeista, jotka ovat sen sarakkeen oikealla puolella, jolla on hakuarvo.

Esimerkki 3: Kaksisuuntainen haku

Toistaiseksi olemme nähneet esimerkkejä, joissa halusimme hakea tiedot sen sarakkeen viereisestä sarakkeesta, jolla on hakuarvo.

Mutta tosielämässä tiedot ulottuvat usein useiden sarakkeiden läpi.

INDEX MATCH pystyy helposti käsittelemään kaksisuuntaisen haun.

Alla on aineisto opiskelijan arvosanoista kolmessa eri aineessa.

Jos haluat hakea oppilaan arvosanat nopeasti kaikista kolmesta aiheesta, voit tehdä sen INDEX MATCH -toiminnolla.

Alla oleva kaava antaa Jimille kaikki kolme kohdetta (kopioi ja liitä yhteen soluun ja vedä täyttääksesi muut solut tai kopioi ja liitä muihin soluihin).

= INDEKSI ($ B $ 2: $ D $ 11, MATCH ($ F $ 3, $ A $ 2: $ A $ 11,0), MATCH (G $ 2, $ B $ 1: $ D $ 1,0))

Selitän nopeasti myös tämän kaavan.

INDEX -kaava käyttää alueena B2: D11.

Ensimmäinen MATCH käyttää nimeä (Jim solussa F3) ja hakee sen sijainnin nimisarakkeesta (A2: A11). Tästä tulee rivinumero, josta tiedot on haettava.

Toinen MATCH -kaava käyttää aiheen nimeä (solussa G2) saadakseen kyseisen aiheen nimen sijainnin B1: D1: ssä. Esimerkiksi matematiikka on 1, fysiikka on 2 ja kemia on 3.

Koska nämä MATCH -paikat syötetään INDEX -funktioon, se palauttaa pisteet opiskelijan nimen ja aiheen nimen perusteella.

Tämä kaava on dynaaminen, mikä tarkoittaa, että jos muutat oppilaan nimen tai aiheen nimen, se toimii edelleen ja hakee oikeat tiedot.

Yksi hieno asia INDEX/MATCHin käytössä on se, että vaikka vaihdat kohteiden nimet, se antaa sinulle edelleen oikean tuloksen.

Esimerkki 4: Haun arvo useista sarakkeista/ehdoista

Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat hakea merkinnät merkille "Pitkä".

Koska tiedot ovat kahdessa sarakkeessa, en voi etsiä Markia ja hakea tietoja.

Jos teen sen tällä tavalla, saan markkijärjestelmätiedot Mark Frostista, en Mark Longista (koska MATCH -toiminto antaa minulle tuloksen sille kohdatusta MARK -merkistä).

Yksi tapa tehdä tämä on luoda auttajasarake ja yhdistää nimet. Kun sinulla on auttaja -sarake, voit käyttää VLOOKUP -toimintoa ja saada merkkitiedot.

Jos olet kiinnostunut oppimaan, miten tämä tehdään, lue tämä opetusohjelma VLOOKUPin käytöstä useilla ehdoilla.

Mutta INDEX/MATCH -yhdistelmällä et tarvitse auttajasaraketta. Voit luoda kaavan, joka käsittelee useita ehtoja itse kaavassa.

Alla oleva kaava antaa tuloksen.

= INDEKSI ($ C $ 2: $ C $ 11, MATCH ($ E $ 3 & "|" & $ F $ 3, $ A $ 2: A11 & "|" & $ B $ 2: $ B $ 11,0))

Selitän nopeasti, mitä tämä kaava tekee.

Kaavan MATCH -osa yhdistää hakuarvon (Mark ja Long) sekä koko hakutoiminnon. Kun $ A $ 2: A11 & ”|” & $ B $ 2: $ B $ 11 käytetään hakutaulukkona, se todella tarkistaa hakuarvon etu- ja sukunimen yhdistetyn merkkijonon perusteella (erotettuna putkisymbolilla).

Näin varmistat, että saat oikean tuloksen käyttämättä apupylväitä.

Voit tehdä tämänkaltaisen haun (jossa on useita sarakkeita/ehtoja) myös VLOOKUP -toiminnolla, mutta sinun on käytettävä apusaraketta. INDEX MATCH -yhdistelmä tekee tämän tekemisen hieman helpoksi ilman apupylväitä.

Esimerkki 5: Hae arvot koko riviltä/sarakkeelta

Yllä olevissa esimerkeissä olemme käyttäneet INDEX -funktiota arvon saamiseksi tietystä solusta. Annat rivin ja sarakkeen numeron, ja se palauttaa arvon kyseisessä solussa.

Mutta voit tehdä enemmän.

Voit myös käyttää INDEX -funktiota saadaksesi arvot koko riviltä tai sarakkeelta.

Ja kuinka tämä voi olla hyödyllistä, kysyt!

Oletetaan, että haluat tietää Jimin kokonaispistemäärän kaikista kolmesta aiheesta.

INDEX -funktion avulla voit ensin hakea kaikki Jimin merkit ja käyttää sitten SUM -funktiota saadaksesi kokonaismäärän.

Katsotaanpa, miten tämä tehdään.

Alla on kaikkien oppilaiden pisteet kolmessa aineessa.

Alla oleva kaava antaa minulle Jimin kokonaispistemäärän kaikissa kolmessa aiheessa.

= SUMMA (INDEKSI ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0))

Selitän kuinka tämä kaava toimii.

Temppu tässä on käyttää sarakkeen numerona 0.

Kun käytät INDEX -funktion sarakkeen numerona 0, se palauttaa kaikki riviarvot. Samoin, jos käytät rivinumerona 0, se palauttaa kaikki sarakkeen arvot.

Joten kaavan alla oleva osa palauttaa arvoryhmän - {97, 70, 73}

INDEKSI ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0)

Jos kirjoitat tämän yllä olevan kaavan vain Excelin soluun ja painat Enter, näet #ARVO! virhe. Tämä johtuu siitä, että se ei palauta yhtä arvoa, vaan arvojoukon.

Mutta ei hätää, arvomaailma on edelleen olemassa. Voit tarkistaa tämän valitsemalla kaavan ja painamalla F9 -näppäintä. Se näyttää kaavan tuloksen, joka tässä tapauksessa on kolmen arvon matriisi - {97, 70, 73}

Jos nyt käärität tämän INDEX -kaavan SUMMA -funktioon, se antaa sinulle kaikkien Jimin pisteiden summan.

Voit myös käyttää samaa saadaksesi Jimin korkeimmat, pienimmät ja keskimääräiset arvot.

Aivan kuten olemme tehneet tämän opiskelijalle, voit tehdä tämän myös oppiaineelle. Jos esimerkiksi haluat aiheen keskimääräisen pistemäärän, voit pitää rivinumeron 0 INDEX -kaavassa ja se antaa sinulle kaikki kyseisen sarakkeen arvot.

Lataa esimerkkitiedosto napsauttamalla tätä

Esimerkki 6: Etsi opiskelijan arvosana (likimääräinen ottelutekniikka)

Tähän mennessä olemme käyttäneet MATCH -kaavaa haun arvon tarkan vastaavuuden saamiseksi.

Mutta voit käyttää sitä myös likimääräisen ottelun tekemiseen.

Mikä helvetti nyt on likimääräinen ottelu?

Anna minun selittää.

Kun etsit asioita, kuten nimiä tai tunnuksia, etsit tarkkaa vastaavuutta. Mutta joskus sinun on tiedettävä hakualueesi alue. Yleensä näin on numeroiden kanssa.

Esimerkiksi luokanopettajana saatat haluta tietää, mikä on kunkin oppilaan arvosana oppiaineessa, ja arvosana päätetään pisteiden perusteella.

Alla on esimerkki, jossa haluan arvosanan kaikille oppilaille ja arvosana päätetään oikealla olevan taulukon perusteella.

Joten jos opiskelija saa alle 33, arvosana on F ja jos hän saa alle 50 mutta yli 33, se on E ja niin edelleen.

Alla on kaava, joka tekee tämän.

= INDEKSI ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8,1), 1)

Selitän kuinka tämä kaava toimii.

MATCH -funktiossa olemme käyttäneet yhtä [match_type] -argumenttina. Tämä argumentti palauttaa suurimman arvon, joka on pienempi tai yhtä suuri kuin hakuarvo.

Tämä tarkoittaa, että MATCH -kaava kulkee merkkialueen läpi, ja heti kun se löytää merkkialueen, joka on yhtä suuri tai pienempi kuin hakumerkit, se pysähtyy siihen ja palauttaa sijaintinsa.

Joten jos hakumerkin arvo on 20, MATCH -funktio palauttaa 1 ja jos se on 85, se palauttaa 5.

INDEX -funktio käyttää tätä sijainti -arvoa arvosanan saamiseen.

TÄRKEÄÄ: Jotta tämä toimisi, tietosi on lajiteltava nousevaan järjestykseen. Jos ei, voit saada vääriä tuloksia.

Huomaa, että yllä oleva voidaan tehdä myös käyttämällä alla olevaa VLOOKUP -kaavaa:

= HAKU (B2, $ E $ 3: $ F $ 8,2, TRUE)

Mutta MATCH -toiminto voi mennä askeleen pidemmälle likimääräisen ottelun suhteen.

Voit myös saada laskevan datan ja käyttää INDEX MATCH -yhdistelmää löytääksesi tuloksen. Jos esimerkiksi muutan arvosanataulukon järjestystä (kuten alla), löydän silti oppilaiden arvosanat.

Tätä varten minun tarvitsee vain muuttaa [match_type] -argumentiksi -1.

Alla on käyttämäni kaava:

= INDEKSI ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8, -1), 1)

VLOOKUP voi myös tehdä likimääräisen vastaavuuden, mutta vain silloin, kun tiedot on lajiteltu nousevaan järjestykseen (mutta se ei toimi, jos tiedot on lajiteltu laskevaan järjestykseen).

Esimerkki 7: Kirjainkokoherkät haut

Tähän mennessä kaikki tekemämme haut ovat olleet kirjainkoolla merkitystä.

Tämä tarkoittaa, että onko hakuarvo Jim, JIM vai jim, sillä ei ole väliä. Saat saman tuloksen.

Mutta entä jos haluat, että haku on kirjainkoko.

Näin on yleensä silloin, kun sinulla on suuria tietojoukkoja ja mahdollisuus toistaa tai eri nimet/tunnukset (ainoa ero on näin)

Oletetaan esimerkiksi, että minulla on seuraava oppilaiden tietojoukko, jossa on kaksi oppilasta nimeltä Jim (ainoa ero on se, että yksi on kirjoitettu nimellä Jim ja toinen jim).

Huomaa, että on kaksi oppilasta, joilla on sama nimi - Jim (solu A2 ja A5).

Koska tavallinen haku ei toimi, sinun on tehtävä kirjainkokoherkkä haku.

Alla on kaava, joka antaa sinulle oikean tuloksen. Koska tämä on taulukkokaava, sinun on käytettävä Control + Vaihto + Enter.

= INDEKSI ($ B $ 2: $ B $ 11, MATCH (TRUE, EXACT (D3, A2: A11), 0), 1)

Selitän kuinka tämä kaava toimii.

EXACT -toiminto tarkistaa, onko hakuarvo (joka tässä tapauksessa on "jim") täsmällisesti. Se käy läpi kaikki nimet ja palauttaa FALSE, jos se ei ole ottelu, ja TRUE, jos se on ottelu.

Tarkan funktion tulos tässä esimerkissä on siis - {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}

Huomaa, että on vain yksi TOSI, jolloin EXACT -toiminto löysi täydellisen vastaavuuden.

MATCH -funktio löytää sitten TOSI -paikan EXACT -funktion palauttamasta taulukosta, joka on tässä esimerkissä 4.

Kun olemme saaneet paikan, INDEX -toiminto käyttää sitä löytääkseen merkit.

Esimerkki 8: Etsi lähin ottelu

Mennään nyt vähän eteenpäin.

Oletetaan, että sinulla on tietojoukko, josta haluat löytää henkilön, jolla on työkokemusta lähinnä vaadittua kokemusta (mainittu solussa D2).

Vaikka hakukaavoja ei ole tehty tähän, voit yhdistää sen muihin toimintoihin (kuten MIN ja ABS).

Alla on kaava, joka löytää henkilön, jolla on kokemusta lähinnä vaadittua, ja palauttaa henkilön nimen. Huomaa, että kokemuksen on oltava lähinnä (mikä voi olla vähemmän tai enemmän).

= INDEKSI ($ A $ 2: $ A $ 15, MATCH (MIN (ABS (D2-B2: B15))), ABS (D2- $ B $ 2: $ B $ 15), 0))

Koska tämä on taulukkokaava, sinun on käytettävä Control + Vaihto + Enter.

Tämän kaavan temppu on muuttaa hakuarvoa ja hakutoimintoa löytääksesi vaadittujen ja todellisten arvojen vähimmäiskokemusero.

Ennen kuin selitän kaavan, ymmärrämme, miten teet sen manuaalisesti.

Käydään läpi jokainen sarake B -sarakkeessa ja huomaat, että kokemuksessa on ero vaaditun ja henkilön saaman kokemuksen välillä. Kun sinulla on kaikki erot, löydät vähimmäismäärän ja haet kyseisen henkilön nimen.

Juuri tätä teemme tällä kaavalla.

Anna minun selittää.

MATCH-kaavan hakuarvo on MIN (ABS (D2-B2: B15)).

Tämä osa antaa sinulle pienimmän erot annetun kokemuksen (joka on 2,5 vuotta) ja kaikkien muiden kokemusten välillä. Tässä esimerkissä se palauttaa 0,3

Huomaa, että olen käyttänyt ABS: ää varmistaakseni, että etsin lähintä (mikä voi olla enemmän tai vähemmän kuin annettu kokemus).

Tästä vähimmäisarvosta tulee nyt hakuarvo.

MATCH-funktion hakutoiminto on ABS (D2- $ B $ 2: $ B $ 15).

Tämä antaa meille joukon numeroita, joista 2,5 (vaadittu kokemus) on vähennetty.

Nyt meillä on hakuarvo (0,3) ja hakutoiminto ({6,8; 0,8; 19,5; 21,8; 14,5; 11,2; 0,3; 9,2; 2; 9,8; 14,8; 0,4; 23,8; 2,9})

MATCH -funktio löytää tämän taulukon sijainnin 0.3, joka on myös sen henkilön nimi, jolla on lähin kokemus.

INDEX -funktio käyttää tätä paikkanumeroa palauttaakseen henkilön nimen.

Aiheeseen liittyvää luettavaa: Löydä lähin ottelu Excelistä (esimerkkejä hakukaavojen avulla)

Lataa esimerkkitiedosto napsauttamalla tätä

Esimerkki 9: Käytä INDEX MATCH -merkkiä jokerimerkkien kanssa

Jos haluat etsiä arvon osittaisen haun yhteydessä, sinun on käytettävä yleismerkkejä.

Alla on esimerkiksi yrityksen nimi ja sen markkina -arvo, ja haluat saada markkina -arvon. tiedot kolmelta oikealta puolelta.

Koska nämä eivät ole tarkkoja osumia, et voi tehdä säännöllistä hakua tässä tapauksessa.

Voit kuitenkin saada oikeat tiedot käyttämällä tähtiä (*), joka on jokerimerkki.

Alla on kaava, joka antaa sinulle tiedot yhdistämällä yritysten nimet pääsarakkeesta ja hakemalla sen markkina -arvoluvun.

= INDEKSI ($ B $ 2: $ B $ 10, MATCH (D2 & ”*”, $ A $ 2: $ A $ 10,0), 1)

Selitän kuinka tämä kaava toimii.

Koska hakuarvojen täsmällistä vastaavuutta ei ole, olen käyttänyt D2 ja ”*” hakuarvona MATCH -toiminnossa.

Tähti on jokerimerkki, joka edustaa mitä tahansa määrää merkkejä. Tämä tarkoittaa, että Apple* kaavassa tarkoittaisi mitä tahansa tekstimerkkijonoa, joka alkaa sanalla Apple ja jonka jälkeen voi olla mikä tahansa määrä merkkejä.

Joten kun Omena* käytetään hakuarvona ja MATCH -kaava etsii sitä sarakkeesta A, se palauttaa sijainnin "Apple Inc.", koska se alkaa sanalla Apple.

Voit myös käyttää jokerimerkkejä löytääksesi merkkijonoja, joiden hakuarvo on niiden välissä. Jos käytät esimerkiksi * Omenaa * hakuarvona, se löytää minkä tahansa merkkijonon, jossa on sana omena missä tahansa.

Huomautus: Tämä tekniikka toimii hyvin, kun sinulla on vain yksi vastaavuus. Mutta jos sinulla on useita vastaavia esiintymiä (esimerkiksi Apple Inc ja Apple Corporation), MATCH -toiminto palauttaa vain ensimmäisen vastaavan esiintymän sijainnin.

Esimerkki 10: Kolmitiehaku

Tämä on INDEX MATCHin edistynyt käyttö, mutta katan sen silti näyttääkseni tämän yhdistelmän voiman.

Muista, että sanoin, että INDEX -funktiolla on kaksi syntaksia:

= INDEKSI (taulukko, rivin_numero, [sarakkeen_numero]) = INDEKSI (taulukko, rivin_numero, [sarakkeen_numero], [alueen_numero])

Toistaiseksi kaikissa esimerkeissämme olemme käyttäneet vain ensimmäistä.

Mutta kolmitiehaussa sinun on käytettävä toista syntaksia.

Selitän ensin, mitä kolmiosainen ilme tarkoittaa.

Kaksisuuntaisessa haussa käytämme INDEX MATCH -kaavaa saadaksesi arvosanat, kun meillä on opiskelijan nimi ja aiheen nimi. Esimerkiksi Jimin merkkien hakeminen matematiikassa on kaksisuuntainen haku.

Kolmiosainen ilme tuo siihen uuden ulottuvuuden. Oletetaan esimerkiksi, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat tietää Jimin pistemäärän matematiikasta välitestissä, jolloin tämä olisi kolmitiehaku.

Alla on kaava, joka antaa tuloksen.

= INDEKSI (($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23), MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0), MATCH (G $ 4 , $ B $ 2: $ D $ 2,0), (JOS (G $ 3 = "Yksikkötesti", 1, JOS (G $ 3 = "Väliaika", 2,3))))

Yllä oleva kaava tarkisti kolme asiaa - opiskelijan nimi, aihe ja tentti. Kun se on löytänyt oikean arvon, se palauttaa sen soluun.

Selitän kuinka tämä kaava toimii jakamalla kaava osiin.

• array - ($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23): Tässä tapauksessa olen käyttänyt yhden taulukon sijaan kolmea taulukkoa suluissa.
• row_num - MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0): MATCH -toimintoa käytetään oppilaan nimen sijainnin löytämiseen solusta $ F $ 5 opiskelijan nimen luettelosta.
• col_num - MATCH (G $ 4, $ B $ 2: $ D $ 2,0): MATCH -toimintoa käytetään etsimään aiheen nimen sijainti solussa $ B $ 2 aiheen nimiluettelosta.
• [alueen_numero] - JOS (G $ 3 = ”Yksikkötesti”, 1, JOS (G $ 3 = ”Väliaika”, 2,3)): Alueen numeroarvo kertoo INDEX -funktiolle, mitä kolmesta taulukosta käyttää arvon noutamiseen. Jos tentti on yksikköjakso, IF -funktio palauttaa 1 ja INDEX -funktio hakee arvon ensimmäisen taulukon avulla. Jos koe on puolivälissä, IF-kaava palauttaa 2, muuten se palauttaa 3.

Tämä on kehittynyt esimerkki INDEX MATCH: n käytöstä, ja et todennäköisesti löydä tilannetta, kun sinun on käytettävä tätä. Mutta on silti hyvä tietää, mitä Excel -kaavat voivat tehdä.

Lataa esimerkkitiedosto napsauttamalla tätä

Miksi INDEX/MATCH on parempi kuin VLOOKUP?

Vai onko se?

Kyllä on - useimmissa tapauksissa.

Esittelen tapaukseni hetken kuluttua.

Mutta ennen kuin teen sen, haluan sanoa tämän - VLOOKUP on erittäin hyödyllinen toiminto ja rakastan sitä. Se voi tehdä monia asioita Excelissä ja käytän sitä silloin tällöin itse. Se ei kuitenkaan tarkoita sitä, etteikö parempaa voisi olla, ja INDEX/MATCH (jossa on enemmän joustavuutta ja toimintoja) on parempi.

Joten jos haluat tehdä perushaun, sinun on parempi käyttää VLOOKUPia.

INDEX/MATCH on VLOOKUP steroideista. Ja kun olet oppinut INDEX/MATCH, saatat aina haluta käyttää sitä (etenkin sen joustavuuden vuoksi).

Venyttämättä sitä liian pitkälle, kerron nopeasti syyt, miksi INDEX/MATCH on parempi kuin VLOOKUP.

INDEX/MATCH voi katsoa hakuarvon vasemmalle (sekä oikealle)

Kerroin sen yhdestä yllä olevasta esimerkistä.

Jos sinulla on arvo, joka on hakuarvon vasemmalla puolella, et voi tehdä sitä VLOOKUPilla

Ei ainakaan pelkällä VLOOKUPilla.

Kyllä, voit yhdistää VLOOKUPin muihin kaavoihin ja saada sen valmiiksi, mutta se muuttuu monimutkaiseksi ja sotkuiseksi.

INDEX/MATCH sen sijaan etsitään kaikkialta (olipa se sitten vasen, oikea, ylös tai alas)

INDEX/MATCH voi toimia pysty- ja vaaka -alueilla

Jälleen, täysin kunnioittaen VLOOKUPia, sitä ei ole tehty tekemään tätä.

Loppujen lopuksi V in VLOOKUP tarkoittaa pystysuoraa.

VLOOKUP voi käydä läpi vain pystysuuntaisia ​​tietoja, kun taas INDEX/MATCH voi käydä läpi tietoja sekä pystysuunnassa että vaakasuunnassa.

Tietenkin HLOOKUP -toiminto huolehtii vaakasuorasta hausta, mutta se ei ole VLOOKUP sitten … eikö?

Pidän siitä, että INDEX MATCH -yhdistelmä on riittävän joustava toimimaan sekä pysty- että vaakasuuntaisten tietojen kanssa.

VLOOKUP ei toimi laskevien tietojen kanssa

Mitä tulee likimääräiseen otteluun, VLOOKUP ja INDEX/MATCH ovat samalla tasolla.

Mutta INDEX MATCH ottaa asian huomioon, koska se voi käsitellä myös laskevassa järjestyksessä olevia tietoja.

Näytän tämän yhdessä tämän opetusohjelman esimerkeistä, joissa meidän on löydettävä oppilaiden arvosanat arviointitaulukon perusteella. Jos taulukko on lajiteltu laskevaan järjestykseen, VLOOKUP ei toimi (mutta INDEX MATCH toimisi).

INDEX/MATCH voi olla hieman nopeampi

Olen totuudenmukainen. Itse en suorittanut tätä testiä.

Luotan Excel -mestarin - Charley Kydin - viisauteen.

Nopeusero VLOOKUP- ja INDEX/MATCH -toiminnoissa on tuskin havaittavissa, kun käytössä on pieniä tietojoukkoja. Mutta jos sinulla on tuhansia rivejä ja monia sarakkeita, tämä voi olla ratkaiseva tekijä.

Charley Kyd sanoo artikkelissaan:

”Pahimmassa tapauksessa INDEX-MATCH-menetelmä on suunnilleen yhtä nopea kuin VLOOKUP; parhaimmillaan se on paljon nopeampaa. ”

INDEX/MATCH on riippumaton todellisesta sarakkeen sijainnista

Jos sinulla on alla esitetyn kaltainen tietojoukko hakiessasi Jimin fysiikan pistettä, voit tehdä sen käyttämällä VLOOKUP -toimintoa.

Tätä varten voit määrittää sarakkeen numeroksi 3 VLOOKUPissa.

Kaikki on hyvin.

Mutta entä jos poistan Matematiikka -sarakkeen?

Siinä tapauksessa VLOOKUP -kaava rikkoutuu.

Miksi? - Koska kolmannen sarakkeen käyttö oli kovakoodattu ja kun poistan sarakkeen siltä väliltä, ​​kolmannesta sarakkeesta tulee toinen sarake.

Tässä tapauksessa on parempi käyttää INDEX/MATCH, koska voit tehdä sarakkeen numerosta dynaamisen käyttämällä MATCH -toimintoa. Sarakenumeron sijaan se tarkistaa aiheen nimen ja palauttaa sen sarakkeen numeron avulla.

Varmasti voit tehdä sen yhdistämällä VLOOKUPin MATCHiin, mutta jos yhdistät joka tapauksessa, miksi et tee sitä INDEXillä, joka on paljon joustavampi.

Kun käytät INDEX/MATCH, voit lisätä/poistaa sarakkeita turvallisesti tietojoukkoosi.

Kaikista näistä tekijöistä huolimatta VLOOKUP on suosittu.

Ja se on suuri syy.

VLOOKUP on helpompi käyttää

VLOOKUP ottaa enintään neljä argumenttia. Jos voit kietoa pään näiden neljän ympärille, voit mennä.

Ja koska VLOOKUP käsittelee myös suurimman osan perushakutapauksista, siitä on nopeasti tullut suosituin Excel -toiminto.

Kutsun sitä Excel -toimintojen kuninkaaksi.

INDEX/MATCH on sen sijaan hieman vaikeampi käyttää. Saatat jäädä jumiin, kun alat käyttää sitä, mutta aloittelijalle VLOOKUP on paljon helpompi selittää ja oppia.

Eikä tämä ole nollasummapeli.

Joten jos olet uusi hakumaailmassa etkä tiedä miten käyttää VLOOKUPia, opi se paremmin.

Minulla on yksityiskohtainen opas VLOOKUPin käyttämisestä Excelissä (paljon esimerkkejä)

Tarkoitukseni tässä artikkelissa ei ole asettaa kaksi mahtavaa toimintoa toisiaan vastaan. Halusin näyttää sinulle INDEX MATCH -yhdistelmän voiman ja kaikki mahtavat asiat, joita se voi tehdä.

Toivottavasti pidit tätä artikkelia hyödyllisenä.

Kerro mielipiteesi kommenttien osassa, ja jos huomaat virheen tässä opetusohjelmassa, kerro siitä minulle.