Kuinka INDEX- ja MATCH-funktiota käytetään Excelissä

2024 Kirjoittaja: Abigail Brown | [email protected]. Viimeksi muokattu: 2023-12-17 06:46

Mitä tietää

INDEKSI-funktiota voidaan käyttää yksinään, mutta MATCH-funktion lisääminen sen sisään luo edistyneen haun.
Tämä sisäkkäinen toiminto on joustavampi kuin VLOOKUP ja voi tuottaa tuloksia nopeammin.

Tässä artikkelissa kerrotaan, kuinka INDEX- ja MATCH-funktioita käytetään yhdessä kaikissa Excelin versioissa, mukaan lukien Excel 2019 ja Microsoft 365.

Mitä ovat INDEX- ja MATCH-funktiot?

INDEX ja MATCH ovat Excelin hakufunktioita. Vaikka ne ovat kaksi täysin erillistä funktiota, joita voidaan käyttää yksinään, niitä voidaan myös yhdistää kehittyneitä kaavoja varten.

INDEKSI-funktio palauttaa arvon tai viittauksen arvoon tietystä valinnasta. Sitä voidaan käyttää esimerkiksi arvon etsimiseen tietojoukon toiselta riviltä tai viidenneltä riviltä ja kolmannelta sarakkeelta.

Vaikka INDEXiä voidaan hyvin käyttää yksinään, MATCHin lisääminen kaavaan tekee siitä hieman hyödyllisemmän. MATCH-funktio etsii tietyn kohteen solualueelta ja palauttaa sitten kohteen suhteellisen sijainnin alueella. Sitä voitaisiin esimerkiksi käyttää määrittämään, että tietty nimi on nimiluettelon kolmas kohta.

INDEX and MATCH Syntaksi & Arguments

Näin on kirjoitettava molemmat funktiot, jotta Excel ymmärtää ne:

=INDEKSI(taulukko, rivin_numero, [sarakkeen_numero])

array on solualue, jota kaava käyttää. Se voi olla yksi tai useampi rivi ja sarake, kuten A1:D5. Se on pakollinen.
row_num on taulukon rivi, josta palautetaan arvo, kuten 2 tai 18. Se on pakollinen, ellei sarakkeen_numero ole läsnä.
column_num on taulukon sarake, josta palautetaan arvo, kuten 1 tai 9. Se on valinnainen.

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value on arvo, jonka haluat yhdistää lookup_arrayssa. Se voi olla numero, teksti tai looginen arvo, joka kirjoitetaan manuaalisesti tai johon viitataan soluviittauksen kautta. Tämä on pakollista.
lookup_array on solualue, jonka läpi selaa. Se voi olla yksi rivi tai yksi sarake, kuten A2:D2 tai G1:G45. Tämä on pakollista.
match_type voi olla -1, 0 tai 1. Se määrittää, kuinka lookup_value yhdistetään lookup_array:n arvojen kanssa (katso alla). 1 on oletusarvo, jos tämä argumentti jätetään pois.

Mitä hakutyyppiä käytetään
Hakutyyppi	Mitä se tekee	Sääntö	Esimerkki
1	Etsii suurimman arvon, joka on pienempi tai yhtä suuri kuin lookup_value.	Hakutaulukon arvot on asetettava nousevaan järjestykseen (esim. -2, -1, 0, 1, 2; tai A-Z; tai EPÄTOSI, TOSI.	lookup_value on 25, mutta se puuttuu lookup_arraysta, joten sen sijaan palautetaan seuraavaksi pienimmän luvun sijainti, kuten 22.
0	Etsii ensimmäisen arvon, joka on täsmälleen yhtä suuri kuin lookup_value.	Hakutaulukon arvot voivat olla missä tahansa järjestyksessä.	lookup_value on 25, joten se palauttaa paikan 25.
-1	Etsii pienimmän arvon, joka on suurempi tai yhtä suuri kuin lookup_value.	Lookup_array-arvot on sijoitettava laskevaan järjestykseen (esim. 2, 1, 0, -1, -2).	lookup_value on 25, mutta se puuttuu lookup_arraysta, joten sen sijaan palautetaan seuraavan suurimman luvun sijainti, kuten 34.

Käytä 1 tai -1 tilanteissa, joissa sinun on suoritettava likimääräinen haku asteikolla, kuten käsiteltäessä numeroita ja kun likiarvot ovat kunnossa. Muista kuitenkin, että jos et määritä match_type:tä, 1 on oletusarvo, mikä voi vääristää tuloksia, jos todella haluat täsmällisen vastaavuuden.

Esimerkki INDEX- ja MATCH-kaavat

Ennen kuin tarkastelemme kuinka yhdistää INDEX ja MATCH yhdeksi kaavaksi, meidän on ymmärrettävä, kuinka nämä funktiot toimivat itsenäisesti.

INDEKSI Esimerkit

=INDEKSI(A1:B2, 2, 2)

=INDEKSI(A1:B1, 1)

=INDEKSI(2:2, 1)=INDEKSI(B1:B2, 1)

Tässä ensimmäisessä esimerkissä on neljä INDEX-kaavaa, joita voimme käyttää eri arvojen saamiseksi:

=INDEKSI(A1:B2, 2, 2) etsii arvon A1:B2 läpi löytääkseen arvon toisesta sarakkeesta ja toisesta rivistä, joka on Stacy.
=INDEKSI(A1:B1, 1) etsii arvon A1:B1 läpi löytääkseen arvon ensimmäisestä sarakkeesta, joka on Jon.
=INDEKSI(2:2, 1) tutkii kaiken toisella rivillä löytääkseen arvon ensimmäisestä sarakkeesta, joka on Tim.
=INDEKSI(B1:B2, 1) etsii arvon B1:B2:sta ensimmäiseltä riviltä, joka on Amy.

MATCH-esimerkit

=MATCH("Stacy", A2:D2, 0)

=MATCH(14, D1:D2)

=MATCH(14, D1:D2, -1)=MATCH(13, A1:D1, 0)

Tässä on neljä helppoa esimerkkiä MATCH-toiminnosta:

=MATCH("Stacy", A2:D2, 0) etsii Stacya alueelta A2:D2 ja palauttaa tuloksena 3:n.
=MATCH(14, D1:D2) etsii arvoa 14 välillä D1:D2, mutta koska sitä ei löydy taulukosta, MATCH löytää seuraavaksi suurimman arvon se on pienempi tai yhtä suuri kuin 14, joka tässä tapauksessa on 13, joka on lookup_array:n kohdassa 1.
=MATCH(14, D1:D2, -1) on identtinen sen yllä olevan kaavan kanssa, mutta koska taulukko ei ole laskevassa järjestyksessä, kuten -1 vaatii, saamme virheilmoituksen.
=MATCH(13, A1:D1, 0) etsii lukua 13 taulukon ensimmäiseltä riviltä, joka palauttaa arvon 4, koska se on tämän taulukon neljäs kohde.

INDEX-MATCH Esimerkit

Tässä on kaksi esimerkkiä, joissa voimme yhdistää INDEXin ja MATCHin yhteen kaavaan:

Etsi soluviittaus taulukosta

=INDEKSI(B2:B5, OSTO(F1, A2:A5))

Tämä esimerkki sisältää MATCH-kaavan INDEX-kaavan sisällä. Tavoitteena on tunnistaa tuotteen väri käyttämällä tuotenumeroa.

Jos katsot kuvaa, näet "erotetuilla" riveillä, kuinka kaavat kirjoitettaisiin yksinään, mutta koska upotamme ne sisäkkäin, tapahtuu seuraavaa:

MATCH(F1, A2:A5) etsii F1-arvoa (8795) tietojoukosta A2:A5. Jos laskemme saraketta alaspäin, voimme nähdä sen olevan 2, joten MATCH-funktio juuri keksi sen.
INDEX-taulukko on B2:B5, koska viime kädessä etsimme arvoa kyseisestä sarakkeesta.
INDEKSI-funktio voidaan nyt kirjoittaa uudelleen näin, koska MATCH löysi 2: INDEX(B2:B5, 2, [sarakkeen_numero]).
Koska column_num on valinnainen, voimme poistaa sen ja jättää tämän: INDEX(B2:B5, 2).
Nyt tämä on kuin normaali INDEKSI-kaava, jossa löydämme B2:B5:n toisen kohteen arvon, joka on punainen.

Haku rivien ja sarakkeiden otsikoiden mukaan

=INDEKSI(B2:E13, OSTO(G1, A2:A13, 0), OSTO(G2, B1:E1, 0))

Tässä MATCH- ja INDEX-esimerkissä teemme kaksisuuntaisen haun. Ajatuksena on nähdä kuinka paljon rahaa saimme vihreistä tuotteista toukokuussa. Tämä on todella samanlainen kuin yllä oleva esimerkki, mutta ylimääräinen MATCH-kaava on sisällytetty hakemistoon INDEX.

MATCH(G1, A2:A13, 0) on ensimmäinen tässä kaavassa ratkaistu kohta. Se etsii G1:tä (sana "toukokuu") kohdassa A2:A13 saadakseen tietyn arvon. Emme näe sitä täällä, mutta se on 5.
MATCH(G2, B1:E1, 0) on toinen MATCH-kaava, ja se on todella samanlainen kuin ensimmäinen, mutta sen sijaan etsii G2:ta (sana "vihreä")) sarakkeiden otsikoissa kohdassa B1:E1. Tämä ratkaisee 3.
Voimme nyt kirjoittaa INDEKSI-kaavan uudelleen seuraavasti visualisoidaksemme, mitä tapahtuu: =INDEKSI(B2:E13, 5, 3). Tämä etsii koko taulukosta B2:E13 viidettä riviä ja kolmatta saraketta, joka palauttaa $180.

MATCH- ja INDEKSIsäännöt

Muista useita asioita, kun kirjoitat kaavoja näillä funktioilla:

MATCH ei erota kirjainkoosta, joten isoja ja pieniä kirjaimia käsitellään samalla tavalla tekstiarvoja sovitettaessa.
MATCH palauttaa N/A useista syistä: jos match_type on 0 ja lookup_value ei löydy, jos match_type on -1 ja lookup_array ei ole laskevassa järjestyksessä, jos match_type on 1 ja lookup_array ei ole nousevassa järjestyksessä järjestys, ja jos lookup_array ei ole yksi rivi tai sarake.
Voit käyttää jokerimerkkiä lookup_value-argumentissa, jos match_type on 0 ja lookup_value on tekstimerkkijono. Kysymysmerkki vastaa mitä tahansa yksittäistä merkkiä ja tähti mitä tahansa merkkijonoa (esim.esim. =MATCH("Jo", 1:1, 0)). Jos haluat käyttää MATCHia oikean kysymysmerkin tai tähden etsimiseen, kirjoita ensin ~.
INDEX palauttaa REF! jos rivin_numero ja sarakkeen_numero eivät osoita taulukon soluun.

Aiheeseen liittyvät Excel-funktiot

MATCH-toiminto on samanlainen kuin HAKU, mutta MATCH palauttaa kohteen sijainnin itse kohteen sijaan.

VLOOKUP on toinen hakutoiminto, jota voit käyttää Excelissä, mutta toisin kuin MATCH, joka vaatii INDEXin edistyneille hauille, VLOOKUP-kaavat tarvitsevat vain tämän yhden funktion.