Tämä opetusohjelma opastaa sinua käyttämään INDEX & MATCH -yhdistelmää hakujen tekemiseen Excelissä ja Google Sheetsissä.
INDEX & MATCH, täydellinen pari
Katsotaanpa tarkemmin joitain tapoja yhdistää INDEX- ja MATCH -toiminnot. MATCH -toiminto on suunniteltu palauttamaan kohteen suhteellinen sijainti taulukossa, kun taas INDEX -funktio voi noutaa kohteen taulukosta, jolla on tietty sijainti. Näiden kahden synergian avulla he voivat suorittaa melkein minkä tahansa etsinnän, jota saatat tarvita.
INDEX / MATCH -yhdistelmää on perinteisesti käytetty VLOOKUP -toiminnon korvaajana. Yksi tärkeimmistä syistä on kyky tehdä vasenkätinen haku (katso seuraava osa).
Huomautus: uusi XLOOKUP-toiminto voi nyt suorittaa vasemmanpuoleisia hakuja.
Katso vasemmalle
Käytämme tätä taulukkoa koripallotilastoista:
Haluamme löytää Bobin pelaajan #. Koska pelaaja # on nimisarakkeen vasemmalla puolella, emme voi käyttää VLOOKUPia.
Sen sijaan voimme tehdä perus -MATCH -pyynnön Bobin rivin laskemiseksi
= OSU (H2, B2: B5, 0)
Tämä etsii sanan "Bob" täsmällistä vastaavuutta, joten funktiomme palauttaa numeron 2, koska "Bob" on 2: ssatoinen asema.
Seuraavaksi voimme käyttää INDEX -funktiota palauttaaksesi rivin vastaavan soittimen #. Syötä nyt vain manuaalisesti "2" toimintoon:
= INDEKSI (A2: A5, 2)
Tässä INDEX viittaa A3: een, koska se on 2toinen solu A2: A5 -alueella ja palauttaa tuloksen 42. Yleistavoitetta varten voimme sitten yhdistää nämä kaksi:
= INDEKSI (A2: A5, MATCH (H2, B2: B5, 0))
Tässä on se etu, että pystyimme palauttamaan tuloksen sarakkeesta, joka oli etsimämme vasemmalla puolella.
Kaksiulotteinen haku
Katsotaanpa taulukkoamme aikaisemmin:
Tällä kertaa haluamme kuitenkin hakea tietyn tilaston. Olemme huomanneet, että haluamme etsiä palautuksia solusta H1. Sen sijaan, että sinun pitäisi kirjoittaa useita IF -lausekkeita sen määrittämiseksi, mistä sarakkeesta tulos saadaan, voit käyttää MATCH -toimintoa uudelleen. INDEX -toiminnolla voit määrittää riviarvon ja sarakkeen arvo. Lisäämme tänne toisen MATCH -toiminnon määrittääksemme, minkä sarakkeen haluamme. Se näyttää siltä
= MATCH (H1, A1: E1, 0)
H1 -solumme on pudotusvalikko, jonka avulla voimme valita, mitä luokkaa haluamme etsiä, ja sitten MATCH määrittää, mikä taulukon sarake kuuluu. Liitetään tämä uusi bitti edelliseen kaavaamme. Huomaa, että meidän on säädettävä ensimmäinen argumentti kahdeksi ulottuvuudeksi, koska emme enää halua vain tulosta sarakkeesta A.
= INDEKSI (A2: E5, MATCH (H2, B2: B5, 0), MATCH (H1, A1: E1, 0))
Esimerkissämme haluamme löytää paluuta Charlielle. Kaavamme arvioi tämän seuraavasti:
= INDEKSI (A2: E5, MATCH ("Charlie", B2: B5, 0), MATCH ("Rebounds", A1: E1, 0)) = INDEX (A2: E5, 3, 4) = D4 = 6
Olemme nyt luoneet joustavan kokoonpanon, jonka avulla käyttäjä voi hakea taulukosta haluamansa arvon ilman, että hänen tarvitsee kirjoittaa useita kaavoja tai haarautuvia IF -lausekkeita.
Useita osioita
Sitä ei käytetä usein, mutta INDEXillä on viides argumentti, jonka avulla voidaan määrittää kumpi alueella argumentin sisällä käytettävä. Tämä tarkoittaa, että tarvitsemme tavan siirtää useita alueita ensimmäiseen argumenttiin. Voit tehdä tämän käyttämällä ylimääräisiä sulkuja. Tämä esimerkki havainnollistaa, miten voit noutaa tuloksia laskentataulukon eri taulukoista INDEXin avulla.
Tässä on asettelu, jota käytämme. Meillä on tilastoja kolmesta eri neljänneksestä.
Soluihin H1: H3 olemme luoneet avattavia tietojen validointiluetteloita eri valintoja varten. Neljänneksen pudotusvalikko tulee J2: J4: stä. Käytämme tätä toisessa MATCH -lausekkeessa määrittääksemme, mitä aluetta käytetään. Kaavamme H4: ssä näyttää tältä:
= INDEKSI ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0))
Olemme jo keskustelleet kahden sisäisen MATCH -toiminnon toiminnasta, joten keskitymme ensimmäiseen ja viimeiseen argumenttiin:
= INDEKSI ((A3: E6, A10: E13, A17: E20),…, MATCH (H3, J2: J4, 0))
Olemme antaneet INDEX -funktiolle useita taulukkoja ensimmäisessä argumentissa liittämällä ne kaikki sulkeisiin. Toinen tapa, jolla voit tehdä tämän, on Kaavat - Määritä nimi. Voit määrittää nimen nimeltä "MyTables" määritelmällä
= INDEKSI (MyTable, MATCH (H2, Table1347 [Name], 0), MATCH (H1, Table1347 [#Headers], 0), MATCH (H3, J2: J4,0))
Palataanpa koko lausuntoon. Erilaiset MATCH -toiminnot kertovat INDEX -toiminnolle tarkalleen, mistä etsiä. Ensinnäkin määritämme, että "Charlie" on 3rd rivi. Seuraavaksi haluamme "Rebounds", joka on 4th sarake. Lopuksi olemme päättäneet, että haluamme tuloksen 2toinen pöytä. Kaava arvioi tämän kautta seuraavasti:
= INDEKSI ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0)) = INDEKSI ((A3: E6, A10: E13, A17: E20), 3, 4, 2) = INDEKSI (A10: E13, 3, 4) = D13 = 14
Kuten mainitsimme tämän esimerkin alussa, taulukoiden on oltava samassa laskentataulukossa. Jos voit kirjoittaa oikeat tavat kertoa INDEXille, miltä riviltä, sarakkeelta ja/tai alueelta haluat noutaa tietoja, INDEX palvelee sinua erittäin hyvin.
Google Sheets -INDEKSI & MATCH
Kaikki yllä olevat esimerkit toimivat täsmälleen samalla tavalla Google Sheetsissä kuin Excelissä.
