Excel

INDEXin ja MATCHin käyttö

How Use Index Match

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)

Indeksin avulla neljännen planeetan nimi
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)

Indeksin avulla neljännen planeetan halkaisija
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)

Paikan etsiminen pystysuoralta alueelta MATCH -toiminnon avulla
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)

Paikan etsiminen vaakasuoralta alueelta MATCH -toiminnon avulla
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.

Myynti myyjän mukaan kuukausittain

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)

INDEX ja MATCH löytää helmikuun myynnit mille tahansa nimelle
MATCH löytää 'Frantz' ja palauttaa rivin INDEX 5: ksi.

Yhteenvetona:

  1. INDEX tarvitsee numeerisia sijainteja.
  2. MATCH löytää ne paikat.
  3. 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:

Dynaaminen haku INDEX- ja MATCH -tekniikoilla
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:

Vasen haku INDEX ja MATCH

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:

Kirjainkoko erottaa haun INDEX ja MATCH

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.

Etsi lähin ottelu INDEX ja MATCH

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:

INDEX ja MATCH useilla kriteereillä

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:

Kirjailija Dave Bruns Attachments Tiedosto exceljet -indeksi ja match.xlsx


^