INDEX ja MATCH on Excelin suosituin työkalu kehittyneempien hakujen suorittamiseen. Tämä johtuu siitä, että INDEX ja MATCH ovat uskomattoman joustavia-voit tehdä vaaka- ja pystysuuntaisia, 2-suuntaisia, vasemmanpuoleisia, kirjainkokoherkkiä ja jopa useiden kriteerien mukaisia hakuja. Jos haluat parantaa Excel -taitojasi, luettelossa on oltava INDEX ja MATCH.
Tässä artikkelissa selitetään yksinkertaisesti, kuinka INDEX- ja MATCH -järjestelmiä käytetään yhdessä hakujen suorittamiseen. Se ottaa askel askeleelta lähestymistavan, jossa ensin selitetään INDEX, sitten MATCH ja näytetään sitten, miten nämä kaksi toimintoa yhdistetään dynaamisen kaksisuuntaisen haun luomiseksi. Sivun alapuolella on edistyneempiä esimerkkejä.
INDEX -toiminto | MATCH -toiminto | INDEKSI ja MATCH | 2-suuntainen haku | Vasen haku | Kirjainkoolla on merkitystä | Lähin ottelu | Useita kriteerejä | Lisää esimerkkejä
INDEX -toiminto
Excelin INDEX -toiminto on fantastisen joustava ja tehokas, ja löydät sen valtavasta määrästä Excel -kaavoja, etenkin kehittyneitä kaavoja. Mutta mitä INDEX todella tekee? Lyhyesti sanottuna INDEX hakee arvon tietystä alueen alueesta. Oletetaan esimerkiksi, että sinulla on aurinkokuntamme planeettojen taulukko (katso alla) ja haluat saada neljännen planeetan Marsin nimen kaavalla. Voit käyttää INDEXiä seuraavasti:
= INDEX (B3:B11,4)
INDEX palauttaa alueen neljännen rivin arvon.
Video: Kuinka etsiä asioita INDEXin avulla
Entä jos haluat saada Marsin halkaisijan INDEXin avulla? Siinä tapauksessa voimme antaa sekä rivinumeron että sarakkeen numeron ja tarjota suuremman alueen. Alla oleva INDEX -kaava käyttää B3: D11: n kaikkia tietoja, rivinumerolla 4 ja sarakkeen numerolla 2:
= INDEX (B3:D11,4,2)
INDEX hakee rivin 4 sarakkeen 2 arvon.
Yhteenvetona voidaan todeta, että INDEX saa arvon tietyssä paikassa solualueella numeerisen sijainnin perusteella. Kun alue on yksiulotteinen, sinun on annettava vain rivinumero. Kun alue on kaksiulotteinen, sinun on annettava sekä rivin että sarakkeen numero.
Tässä vaiheessa saatat ajatella: 'Mitä sitten? Kuinka usein todella tiedät jonkun sijainnin laskentataulukossa? '
Juuri oikein. Tarvitsemme tavan löytää etsimiemme asioiden sijainti.
Siirry MATCH -toimintoon.
MATCH -toiminto
MATCH -toiminto on suunniteltu yhteen tarkoitukseen: löytää kohteen sijainti alueelta. Voimme esimerkiksi käyttää MATCHia saadaksemme sanan 'persikka' sijainnin tässä hedelmäluettelossa:
= MATCH ('peach',B3:B9,0)
MATCH palauttaa 3, koska 'Peach' on kolmas kohde. MATCH ei erota kirjainkokoa.
MATCH ei välitä siitä, onko alue vaakasuora vai pystysuora, kuten alla näet:
= MATCH ('peach',C4:I4,0)
Sama tulos vaaka -alueella, MATCH palauttaa 3.
Video: Kuinka käyttää MATCHia täsmällisiin osumiin
Tärkeää: MATCH -funktion viimeinen argumentti on hakutyyppi. Hakutyyppi on tärkeä ja määrittää, onko täsmäytys tarkka tai likimääräinen. Monissa tapauksissa haluat käyttää nollaa (0) pakottamaan tarkan haun käyttäytymisen. Hakutyypin oletusarvo on 1, mikä tarkoittaa likimääräistä osumaa, joten on tärkeää antaa arvo. Katso MATCH -sivu Lisätietoja.
INDEX ja MATCH yhdessä
Nyt kun olemme käsitelleet INDEXin ja MATCHin perusteet, miten yhdistämme nämä kaksi toimintoa yhdeksi kaavaksi? Harkitse alla olevia tietoja, taulukkoa, jossa on luettelo myyjistä ja kuukausittaiset myyntiluvut kolmen kuukauden ajalta: tammikuu, helmikuu ja maaliskuu.
Oletetaan, että haluamme kirjoittaa kaavan, joka palauttaa tietyn myyjän helmikuun myyntiluvun. Yllä olevasta keskustelusta tiedämme, että voimme antaa INDEXille rivin ja sarakkeen numeron arvon noutamiseksi. Esimerkiksi palauttaaksemme Frantzin helmikuun myyntinumeron, tarjoamme alueen C3: E11, rivi 5 ja sarake 2:
= INDEX (C3:E11,5,2) // returns 94
Mutta emme tietenkään halua koodata numeroita. Sen sijaan haluamme a dynaaminen Katso ylös.
Kuinka teemme sen? Tietenkin MATCH -toiminto. MATCH toimii täydellisesti tarvittavien paikkojen löytämiseksi. Työskentelemme askel kerrallaan, jätetään sarake kovakoodattuna 2 ja tehdään rivinumerosta dynaaminen. Tässä on tarkistettu kaava, jossa MATCH -funktio on sijoitettu INDEXin sisään 5: n sijasta:
= INDEX (C3:E11, MATCH ('Frantz',B3:B11,0),2)
Otamme asiat askeleen pidemmälle, käytämme MATCH: n H2 -arvoa:
= INDEX (C3:E11, MATCH (H2,B3:B11,0),2)
MATCH löytää 'Frantz' ja palauttaa rivin INDEX 5: ksi.
Yhteenvetona:
- INDEX tarvitsee numeerisia sijainteja.
- MATCH löytää ne paikat.
- MATCH on sisäkkäin INDEXin sisällä.
Tarkastellaan nyt sarakkeen numeroa.
Kaksisuuntainen haku INDEX- ja MATCH-tekniikoilla
Yllä käytimme MATCH -funktiota löytääksesi rivinumeron dynaamisesti, mutta sarakkeen numero kovakoodattiin. Miten voimme tehdä kaavasta täysin dynaamisen, jotta voimme palauttaa myynnin keneltä tahansa myyjältä tietyn kuukauden aikana? Temppu on käyttää MATCHia kahdesti - kerran rivirivin saamiseksi ja kerran sarakkeen sijainnin saamiseksi.
Yllä olevista esimerkeistä tiedämme, että MATCH toimii hyvin sekä vaaka- että pystysuorien matriisien kanssa. Tämä tarkoittaa, että voimme helposti löytää tietyn kuukauden aseman MATCH: n avulla. Esimerkiksi tämä kaava palauttaa maaliskuun aseman, joka on 3:
= MATCH ('Mar',C2:E2,0) // returns 3
Mutta emme tietenkään halua kovakoodata minkä tahansa päivitä laskentataulukko sallimaan kuukauden nimen syöttäminen ja käytä MATCH -näppäintä tarvittavan sarakkeen numeron löytämiseen. Alla oleva näyttö näyttää tuloksen:
Täysin dynaaminen, kaksisuuntainen haku INDEX- ja MATCH-tekniikoilla.
= INDEX (C3:E11, MATCH (H2,B3:B11,0), MATCH (H3,C2:E2,0))
Ensimmäinen MATCH -kaava palauttaa rivin numeroksi INDEX, toinen MATCH -kaava palauttaa sarakkeen numeroksi INDEX. Kun MATCH on suoritettu, kaava yksinkertaistuu seuraavasti:
= INDEX (C3:E11,5,3)
ja INDEX palauttaa oikein 10 525 dollaria, Frantzin myyntiluvun maaliskuussa.
Huomaa: voit käyttää Tietojen validointi luoda avattavat valikot myyjän ja kuukauden valitsemiseksi.
Video: Kuinka tehdä kaksisuuntainen haku INDEXin ja MATCHin avulla
Video: Kaavan virheenkorjaus F9: llä (nähdäksesi MATCH -palautusarvot)
Vasen haku
Yksi INDEXin ja MATCHin tärkeimmistä eduista VLOOKUP -toimintoon verrattuna on kyky suorittaa 'vasen haku'. Yksinkertaisesti sanottuna tämä tarkoittaa vain hakua, jossa ID -sarake on oikein arvoista, jotka haluat hakea, kuten alla olevassa esimerkissä näkyy:
Lue yksityiskohtainen selitys täältä .
Kirjainkoolla herkkä haku
MATCH-toiminto ei sinänsä erota kirjainkokoa. Käytät kuitenkin EXACT -toiminto INDEX- ja MATCH -näppäimillä voit etsiä isoja ja pieniä kirjaimia alla kuvatulla tavalla:
Lue yksityiskohtainen selitys täältä .
Huomaa: tämä on taulukon kaava ja on annettava näppäimellä control + shift + enter, paitsi sisään Excel 365 .
Lähin ottelu
Toinen esimerkki, joka osoittaa INDEXin ja MATCHin joustavuuden, on ongelman löytäminen lähin ottelu . Alla olevassa esimerkissä käytämme MIN -toiminto yhdessä ABS -toiminto kohteeseen luoda hakuarvo ja hautaulukko sisällä MATCH -toiminto. Käytännössä käytämme MATCH: ta pienimmän eron löytämiseen. Sitten käytämme INDEXiä hakeaksemme siihen liittyvän matkan sarakkeesta B.
Lue yksityiskohtainen selitys täältä .
Huomaa: tämä on taulukon kaava ja on annettava näppäimellä control + shift + enter, paitsi sisään Excel 365 .
Useiden kriteerien haku
Yksi Excelin vaikeimmista ongelmista on haku, joka perustuu useisiin kriteereihin. Toisin sanoen haku, joka vastaa useampaa kuin yhtä saraketta samanaikaisesti. Alla olevassa esimerkissä käytämme INDEX- ja MATCH- ja boolen logiikka vastaa kolmea saraketta: kohde, väri ja koko:
Lue yksityiskohtainen selitys täältä .
Huomaa: tämä on taulukon kaava ja on annettava näppäimellä control + shift + enter, paitsi sisään Excel 365 .
kuinka ohittaa rivi Excelissä
Lisää esimerkkejä INDEX + MATCH -ominaisuudesta
Tässä on muutamia perusesimerkkejä INDEX- ja MATCH -toiminnoista toiminnassa, joista jokaisella on yksityiskohtainen selitys:
- Perusindeksi ja MATCH tarkka (sisältää Toy Storyn)
- Basic INDEX ja MATCH likimääräiset (arvosanat)
- Kaksisuuntainen haku INDEX- ja MATCH-tekniikoilla (likimääräinen ottelu)
exceljet -indeksi ja match.xlsx
