Excel

Indeksin ja MATCHin käyttäminen

How Use Index Match

INDEX ja MATCH ovat Excelin suosituin työkalu edistyneempien hakujen suorittamiseen. Tämä johtuu siitä, että INDEX ja MATCH ovat uskomattoman joustavia - voit tehdä vaaka- ja pystysuuntaisia ​​hakuja, kaksisuuntaisia ​​hakuja, vasemmanpuoleisia hakuja, kirjainkoon mukaisia ​​hakuja ja jopa hakuja useiden kriteerien perusteella. Jos haluat parantaa Excel-taitojasi, INDEXin ja MATCHin pitäisi olla luettelossasi.

Tässä artikkelissa kerrotaan yksinkertaisella tavalla, kuinka hakemistoja voidaan käyttää hakemistoissa. Se vie vaiheittaisen lähestymistavan, joka selittää ensin INDEXin, sitten MATCH, ja näyttää sitten, kuinka nämä kaksi toimintoa voidaan yhdistää dynaamisen kaksisuuntaisen haun luomiseksi. Sivun alapuolella on edistyneempiä esimerkkejä.



INDEX-toiminto | MATCH-toiminto | HAKEMISTO ja MATCH | Kaksisuuntainen haku | Vasen haku | Kirjainkoko on merkitsevä | Lähin ottelu | Useita kriteerejä | Lisää esimerkkejä



INDEX-toiminto

INDEX-toiminto Excelissä on uskomattoman joustava ja tehokas, ja löydät sen valtavasta joukosta Excel-kaavoja, erityisesti edistyneitä kaavoja. Mutta mitä INDEX todella tekee? Lyhyesti sanottuna INDEX hakee arvon tietylle alueelle. Oletetaan esimerkiksi, että sinulla on aurinkokuntamme taulukko planeetoista (katso alla) ja haluat saada neljännen planeetan, Marsin, kaavan. Voit käyttää INDEXia näin:

 
= INDEX (B3:B11,4)

INDEX: n avulla saat 4. planeetan nimen
INDEX palauttaa arvon alueen 4. rivillä.



Video: Kuinka etsiä asioita hakemistolla INDEX

Entä jos haluat saada Marsin halkaisijan INDEX-indeksillä? Tällöin voimme toimittaa sekä rivinumeron että sarakkeen numeron ja tarjota suuremman alueen. Alla oleva INDEX-kaava käyttää B3: D11: n kaikkia tietoja, rivinumero 4 ja sarake 2:

 
= INDEX (B3:D11,4,2)

Indeksin avulla saat 4. planeetan halkaisijan
INDEX hakee arvon riviltä 4, sarake 2.



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 tiedät todella jonkin sijainnin laskentataulukossa? '

Juuri oikein. Tarvitsemme tavan etsiä etsimiemme asioiden sijainti.

Anna MATCH-toiminto.

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: n avulla pystysuuntaisen sijainnin löytäminen
MATCH palauttaa arvon 3, koska Peach on kolmas kohde. MATCH ei eroa isoja ja pieniä kirjaimia.

MATCH ei välitä, onko alue vaaka- tai pystysuora, kuten näet alla:

 
= MATCH ('peach',C4:I4,0)

MATCH-toiminnon avulla löydät paikan vaakasuoralta alueelta
Sama tulos vaaka-alueella, MATCH palauttaa 3.

Video: Kuinka käyttää MATCHia tarkkoihin otteluihin

Tärkeää: MATCH-funktion viimeinen argumentti on hakutyyppi. Ottelutyyppi on tärkeä ja ohjaa, onko haku täsmällinen vai likimääräinen. Monissa tapauksissa haluat käyttää nollaa (0) pakottaaksesi tarkan haun käyttäytymistä. Hakutyyppi on oletusarvoisesti 1, mikä tarkoittaa likimääräistä vastaavuutta, joten on tärkeää antaa arvo. Katso MATCH sivu Lisätietoja.

HAKEMISTO ja MATCH yhdessä

Nyt kun olemme käsitelleet INDEXin ja MATCHin perusteet, miten yhdistämme nämä kaksi toimintoa yhteen kaavaan? Harkitse alla olevia tietoja, taulukko, jossa on luettelo myyjistä ja kuukausittaiset myyntiluvut kolmelta kuukaudelta: 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 INDEX: lle rivin ja sarakkeen numeron arvon noutamiseksi. Esimerkiksi Frantzin helmikuun myyntinumeron palauttamiseksi annamme alueen C3: E11 rivillä 5 ja sarakkeella 2:

 
= INDEX (C3:E11,5,2) // returns 94

Mutta emme tietenkään halua koodata numeroita. Sen sijaan haluamme a dynaaminen Katso ylös.

Kuinka me teemme sen? MATCH-toiminto tietysti. MATCH toimii täydellisesti löytääksemme tarvitsemamme asemat. Työskentelemällä yksi askel kerrallaan, jätetään sarake kovakoodatuksi 2: ksi ja tehdään rivinumerosta dynaaminen. Tässä on tarkistettu kaava, jossa MATCH-funktio on sisäkkäin INDEX: n sisällä viiden sijasta:

 
= INDEX (C3:E11, MATCH ('Frantz',B3:B11,0),2)

Ottaen asiat eteenpäin, käytämme H2: n arvoa MATCH: ssa:

 
= INDEX (C3:E11, MATCH (H2,B3:B11,0),2)

INDEX ja MATCH löytääksesi helmikuun myynnin mille tahansa nimelle
MATCH löytää 'Frantz' ja palauttaa 5 riville INDEX.

Yhteenvetona:

  1. INDEX tarvitsee numeerisia sijainteja.
  2. MATCH löytää nämä paikat.
  3. MATCH on sisäkkäin sisällä INDEX.

Otetaan nyt sarakkeen numero.

Kaksisuuntainen haku INDEX: llä ja MATCH: lla

Yllä käytimme MATCH-funktiota etsimään rivinumero dynaamisesti, mutta koodaamme sarakkeen numeron kovalla koodilla. Kuinka voimme tehdä kaavasta täysin dynaamisen, jotta voimme palauttaa myynnin mille tahansa myyjälle kuluvan kuukauden aikana? Temppu on käyttää MATCHia kahdesti - kerran rivin sijainnin saamiseksi ja kerran sarakkeen sijainnin saamiseksi.

Yllä olevista esimerkeistä tiedämme, että MATCH toimii hyvin sekä vaaka- että pystysuunnassa. Tämä tarkoittaa, että voimme helposti löytää tietyn kuukauden sijainnin MATCHilla. Esimerkiksi tämä kaava palauttaa maaliskuun sijainnin, joka on 3:

 
= MATCH ('Mar',C2:E2,0) // returns 3

Mutta tietysti emme halua kovakoodaamista minkä tahansa arvoja, joten päivitämme laskentataulukon sallimaan kuukauden nimen syöttö, ja etsi MATCH: lla tarvitsemamme sarakkeen numero. Alla olevassa näytössä näkyy tulos:

Dynaaminen haku hakemistolla INDEX ja MATCH
Täysin dynaaminen, kaksisuuntainen haku, jossa on INDEX ja MATCH.

 
= INDEX (C3:E11, MATCH (H2,B3:B11,0), MATCH (H3,C2:E2,0))

Ensimmäinen MATCH-kaava palauttaa 5 kohtaan INDEX rivinumerona, toinen MATCH-kaava palauttaa arvon 3 INDEX-sarakkeen numeroksi. Kun MATCH on suoritettu, kaava yksinkertaistuu seuraavasti:

 
= INDEX (C3:E11,5,3)

ja INDEX palauttaa oikein 10 525 dollaria, joka on Frantzin myyntiluku maaliskuussa.

Huomaa: voit käyttää Tietojen vahvistus luoda avattavat valikot myyjän ja kuukauden valitsemiseksi.

Video: Kuinka tehdä kaksisuuntainen haku hakemistolla ja MATCH

Video: Kaavan virheenkorjaus F9: llä (nähdäksesi MATCH-paluuarvot)

Vasen haku

Yksi INDEXin ja MATCHin tärkeimmistä eduista VLOOKUP-funktioon nähden 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 hakemistolla INDEX ja MATCH

Lue yksityiskohtainen selitys täältä .

Kirjainkoon mukainen haku

Itse asiassa MATCH-toiminto ei eroa isoja ja pieniä kirjaimia. Käytät kuitenkin EXACT-toiminto INDEX- ja MATCH-näppäimillä suorittaaksesi etsinnän, joka kunnioittaa isoja ja pieniä kirjaimia, kuten alla on esitetty:

Kirjainkoolla erottava haku INDEX- ja MATCH-sovelluksilla

Lue yksityiskohtainen selitys täältä .

Huomaa: tämä on taulukon kaava ja se on syötettävä painamalla + + shift + enter, paitsi sisään Excel 365 .

Lähin ottelu

Toinen esimerkki, joka osoittaa INDEXin ja MATCHin joustavuuden, on ongelma löytää lähin ottelu . Alla olevassa esimerkissä käytämme MIN-toiminto yhdessä ABS-toiminto että luoda hakuarvo ja hakutaulukko sisällä MATCH-toiminto. Pohjimmiltaan käytämme MATCH: ta pienimmän eron löytämiseen. Sitten haemme vastaavan matkan sarakkeesta B INDEX: n avulla.

Löydä lähin ottelu INDEX: n ja MATCH: n kanssa

Lue yksityiskohtainen selitys täältä .

Huomaa: tämä on taulukon kaava ja se on syötettävä painamalla + + shift + enter, paitsi sisään Excel 365 .

Useiden ehtojen haku

Yksi Excelin hankalimmista ongelmista on useisiin kriteereihin perustuva haku. Toisin sanoen haku, joka vastaa useampaa kuin yhtä saraketta samanaikaisesti. Alla olevassa esimerkissä käytämme INDEX ja MATCH ja looginen logiikka vastaamaan 3 saraketta: Kohde, Väri ja Koko:

INDEKSI ja MATCH useilla ehdoilla

Lue yksityiskohtainen selitys täältä .

Huomaa: tämä on taulukon kaava ja se on syötettävä painamalla + + shift + enter, paitsi sisään Excel 365 .

kuinka ohittaa rivi Excelissä

Lisää esimerkkejä INDEX + MATCH

Tässä on muutamia perusesimerkkejä toiminnasta INDEX ja MATCH, joista jokaisella on yksityiskohtainen selitys:

Kirjoittaja Dave Bruns Liitteet Tiedosto exceljet-hakemisto ja match.xlsx


^