Excel INDEX -toiminto - Esimerkkejä kaavasta + ILMAINEN video

Excel INDEX -toiminto (esimerkit + video)

Milloin käyttää Excel INDEX -toimintoa

Excel INDEX -toimintoa voidaan käyttää, kun haluat hakea arvon taulukkotiedoista ja sinulla on datapisteen rivinumero ja sarakkeen numero. Esimerkiksi alla olevassa esimerkissä voit käyttää INDEX -toimintoa saadaksesi Tomin merkit fysiikassa, kun tiedät tietojoukon rivinumeron ja sarakkeen numeron.

Mitä se palauttaa

Se palauttaa taulukon arvon määritetylle rivin- ja sarakenumerolle.

Syntaksi

= INDEKSI (taulukko, rivin_numero, [sarakkeen_numero])
= INDEKSI (taulukko, rivin_numero, [sarakkeen_numero], [alueen_numero])

INDEX -funktiolla on kaksi syntaksia. Ensimmäistä käytetään useimmissa tapauksissa, mutta kolmitiehauissa käytetään toista (esimerkissä 5 kuvattu).

Syöttöargumentit

• array - a solualue tai matriisivakio.
• rivinumero - rivin numero, josta arvo on haettava.
• [col_num] - sarakkeen numero, josta arvo on haettava. Vaikka tämä on valinnainen argumentti, mutta jos rivinumeroa ei anneta, se on annettava.
• [alueen_numero] - (Valinnainen) Jos taulukko -argumentti koostuu useista alueista, tätä numeroa käytettäisiin viittauksen valitsemiseksi kaikista alueista.

Lisätietoja (Boring Stuff… but Important to Know)

• Jos rivin tai sarakkeen numero on 0, se palauttaa vastaavasti koko rivin tai sarakkeen arvot.
• Jos INDEX -toimintoa käytetään soluviittauksen (kuten A1 :) edessä, se palauttaa soluviittauksen arvon sijasta (katso esimerkkejä alla).
• Yleisimmin käytetty MATCH -toiminnon kanssa.
• Toisin kuin VLOOKUP, INDEX -toiminto voi palauttaa arvon hakuarvon vasemmalta puolelta.
• INDEX -funktiolla on kaksi muotoa - Array -lomake ja Viite -lomake
  • "Matriisilomake" on paikka, jossa haet arvon tietyn taulukon rivin ja sarakkeen numeron perusteella.
  • 'Viitemuoto' on paikka, jossa on useita taulukoita, ja valitset taulukon area_num -argumentin avulla ja haet sen jälkeen arvon rivin ja sarakkeen numeron avulla (katso live -esimerkki alla).

Excel INDEX -toiminto - Esimerkkejä

Tässä on kuusi esimerkkiä Excel INDEX -toiminnon käytöstä.

Esimerkki 1 - Tomin merkkien löytäminen fysiikasta (kaksisuuntainen haku)

Oletetaan, että sinulla on alla näkyvä tietojoukko:

Löydä Tomin merkit fysiikasta käyttämällä seuraavaa kaavaa:

= INDEKSI ($ B $ 3: $ E $ 10,3,2)

Tämä INDEX -kaava määrittää taulukon muodossa $ B $ 3: $ E $ 10, jossa on kaikkien kohteiden merkit. Sitten se käyttää rivinumeroa (3) ja sarakkeen numeroa (2) Tomin merkkien noutamiseksi fysiikasta.

Esimerkki 2 - LOOKUP -arvon muuttaminen dynaamiseksi MATCH -toiminnolla

Rivinumeron ja sarakkeen numeron määrittäminen manuaalisesti ei välttämättä ole aina mahdollista. Sinulla voi olla valtava tietojoukko tai haluat tehdä siitä dynaamisen, jotta se tunnistaa automaattisesti soluissa määritetyn nimen ja/tai aiheen ja antaa oikean tuloksen.

Jotain alla olevan kuvan mukaisesti:

Tämä voidaan tehdä käyttämällä INDEX- ja MATCH -toimintoa.

Tässä on kaava, joka tekee hakuarvoista dynaamisia:

= INDEKSI ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))

Yllä olevassa kaavassa rivinumeron ja sarakkeen numeron kovakoodauksen sijaan MATCH-toimintoa käytetään tekemään siitä dynaaminen.

• Dynaaminen rivinumero annetaan seuraavalla kaavan osalla - MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0). Se skannaa opiskelijoiden nimet ja tunnistaa hakuarvon (tässä tapauksessa $ 5 $). Sitten se palauttaa tietojoukon hakuarvon rivinumeron. Jos esimerkiksi hakuarvo on Matt, se palauttaa 1, jos se on Bob, se palauttaa 2 ja niin edelleen.
• Dynaamisen sarakkeen numero saadaan seuraavasta kaavan osasta - MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0). Se skannaa aiheiden nimet ja tunnistaa hakuarvon ($ H $ 4 tässä tapauksessa). Sitten se palauttaa tietojoukon hakuarvon sarakkeen numeron. Jos esimerkiksi hakuarvo on matematiikka, se palauttaa 1, jos se on fysiikkaa, se palauttaa 2 ja niin edelleen.

Esimerkki 3 - Pudotusvalikoiden käyttäminen hakuarvoina

Yllä olevassa esimerkissä meidän on syötettävä tiedot manuaalisesti. Se voi olla aikaa vievää ja virhealtista, varsinkin jos sinulla on valtava hakuarvojen luettelo.

Hyvä idea tällaisissa tapauksissa on luoda avattava luettelo hakuarvoista (tässä tapauksessa se voi olla opiskelijoiden nimet ja oppiaineet) ja valita sitten yksinkertaisesti luettelosta. Valinnan perusteella kaava päivittää tuloksen automaattisesti.

Jotain alla olevan kuvan mukaisesti:

Tämä on hyvä koontinäyttökomponentti, koska sinulla voi olla valtava tietojoukko, jossa on satoja oppilaita takapäässä, mutta loppukäyttäjä (esimerkiksi opettaja) voi nopeasti saada oppilaan arvosanat yksinkertaisesti tekemällä valinnat pudotusvalikko.

Kuinka tehdä tämä:

Tässä tapauksessa käytetty kaava on sama kuin esimerkissä 2.

= INDEKSI ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))

Hakuarvot on muutettu avattaviksi luetteloiksi.

Voit luoda avattavan Excel -luettelon seuraavasti:

• Valitse solu, johon haluat avattavan luettelon. Tässä esimerkissä G4: ssä haluamme oppilaiden nimet.
• Siirry kohtaan Data -> Datatyökalut -> Tietojen vahvistus.
• Valitse Tietojen validointi -valintaikkunan asetusvälilehden avattavasta Salli-luettelosta Lista.
• Valitse lähteestä $ A $ 3: $ A $ 10
• Napsauta OK.

Nyt sinulla on avattava luettelo solussa G5. Voit myös luoda sellaisen H4: ssä aiheille.

Esimerkki 4 - Palauta arvot koko riviltä/sarakkeelta

Yllä olevissa esimerkeissä olemme käyttäneet Excel INDEX -toimintoa kaksisuuntaisen haun ja yhden arvon saamiseksi.

Entä jos haluat saada kaikki opiskelijan arvosanat? Tämän avulla voit löytää kyseisen oppilaan enimmäis-/vähimmäispistemäärän tai kaikkien oppiaineiden pisteet.

Yksinkertaisesti englanniksi haluat ensin saada oppilaan (esim. Bobin) koko pisteiden rivin ja tunnistaa sitten näiden arvojen sisällä korkeimmat pisteet tai kaikkien pisteiden kokonaismäärän.

Tässä on temppu.

Excel INDEX -toiminnossa, kun kirjoitat sarakkeen numero 0, se palauttaa koko rivin arvot.

Joten kaava tähän olisi:

= INDEKSI ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0)

Nyt tämä kaava. jos käytetään sellaisenaan, palauttaisi #ARVO! virhe. Vaikka se näyttää virheen, se palauttaa taustaohjelmassa taulukon, jossa on kaikki Tomin pisteet - {57,77,91,91}.

Jos valitset kaavan muokkaustilassa ja painat F9, näet sen palauttaman taulukon (kuten alla):

Vastaavasti sen perusteella, mikä on hakuarvo, kun sarakkeen numero on 0 (tai jätetään tyhjäksi), se palauttaa kaikki hakuarvon rivin arvot

Nyt Tomin kokonaispistemäärän laskemiseksi voimme yksinkertaisesti käyttää yllä olevaa kaavaa SUMMA -funktiossa.

= SUMMA (INDEKSI ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0))

Samankaltaisilla riveillä korkeimman pistemäärän laskemiseksi voimme käyttää MAX/LARGE ja pienimmän laskemiseen MIN/SMALL.

Esimerkki 5 - Kolmitiehaku käyttämällä INDEX/MATCH -toimintoa

Excel INDEX -toiminto on suunniteltu käsittelemään kolmitiehakuja.

Mikä on kolmisuuntainen haku?

Yllä olevissa esimerkeissä olemme käyttäneet yhtä taulukkoa, jossa on pisteitä eri aiheiden opiskelijoille. Tämä on esimerkki kaksisuuntaisesta hausta, kun käytämme pisteiden noutamiseen kahta muuttujaa (opiskelijan nimi ja aihe).

Oletetaan nyt, että vuoden kuluttua opiskelijalla on kolme eri tasoa kokeita, yksikkötesti, välitesti ja loppukokeet (näin minulla oli opiskellessani).

Kolmitiehaku olisi kyky saada oppilaan arvosanat tietystä aiheesta tietystä tenttitasosta. Tämä tekisi siitä kolmivaiheisen haun, koska siinä on kolme muuttujaa (opiskelijan nimi, aiheen nimi ja kokeen taso).

Tässä on esimerkki kolmitiehausta:

Yllä olevassa esimerkissä voit valita opiskelijan nimen ja aiheen nimen lisäksi myös kokeen tason. Kokeen tason perusteella se palauttaa yhden kolmesta taulukosta vastaavan arvon.

Tässä on solussa H4 käytetty kaava:

= INDEKSI (($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23), MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0), MATCH ($ H $ 3, $ B $ 2: $ E $ 2,0), JOS ($ H $ 2 = "Yksikkötesti", 1, JOS ($ H $ 2 = "Väliaika", 2,3)))

Hajotetaan tämä kaava ymmärtääksemme, miten se toimii.

Tämä kaava sisältää neljä argumenttia. INDEX on yksi niistä Excelin toiminnoista, jolla on useampi kuin yksi syntaksi.

= INDEKSI (taulukko, rivin_numero, [sarakkeen_numero])
= INDEKSI (taulukko, rivin_numero, [sarakkeen_numero], [alueen_numero])

Tähän mennessä kaikissa yllä olevissa esimerkeissä olemme käyttäneet ensimmäistä syntaksia, mutta kolmisuuntaisen haun tekemiseksi meidän on käytettävä toista syntaksia.

Katsotaan nyt kaavan jokainen osa toisen syntaksin perusteella.

• array - ($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23): Tässä tapauksessa yhden taulukon käyttämisen sijaan olemme käyttäneet kolmea taulukkoa suluissa.
• row_num - MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0): MATCH -funktiota käytetään oppilaan nimen sijainnin löytämiseen solusta $ G $ 4 oppilaan nimen luettelosta.
• col_num - MATCH ($ H $ 3, $ B $ 2: $ E $ 2,0): MATCH -funktiota käytetään aiheen nimen sijainnin löytämiseen solun $ H $ 3 kohdenimen luettelosta.
• [alueen_numero] - JOS ($ H $ 2 = ”Yksikkötesti”, 1, JOS ($ H $ 2 = ”Väliaika”, 2,3)): Pinta -alan numero kertoo INDEX -funktiolle, minkä taulukon valitsee. Tässä esimerkissä meillä on kolme taulukkoa ensimmäisessä argumentissa. Jos valitset avattavasta valikosta Yksikkötesti, IF-funktio palauttaa 1 ja INDEX-funktiot valitsevat ensimmäisen taulukon kolmesta taulukosta (joka on $ B $ 3: $ E $ 7).

Esimerkki 6 - Referenssin luominen INDEX -toiminnolla (dynaamiset nimetyt alueet)

Tämä on yksi Excel INDEX -toiminnon villi käyttö.

Otetaan yksinkertainen esimerkki.

Minulla on luettelo nimistä alla olevan kuvan mukaisesti:

Nyt voin käyttää yksinkertaista INDEX -funktiota saadakseni sukunimen luettelosta.

Tässä on kaava:

= INDEKSI ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))

Tämä toiminto laskee yksinkertaisesti tyhjien solujen määrän ja palauttaa luettelon viimeisen kohteen (se toimii vain, jos luettelossa ei ole tyhjiä).

Mitä taikuutta tässä nyt tulee.

Jos asetat kaavan soluviittauksen eteen, kaava palauttaa vastaavan arvon soluviittauksen (itse arvon sijaan).

= A2: INDEKSI ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))

Odotat yllä olevan kaavan palauttavan = A2: ”Josh” (jossa Josh on luettelon viimeinen arvo). Se kuitenkin palauttaa = A2: A9 ja näin saat alla olevien nimien joukon:

Yksi käytännön esimerkki, jossa tämä tekniikka voi olla hyödyllinen, on dynaamisten nimettyjen alueiden luominen.

Se on tässä opetusohjelmassa. Olen yrittänyt kattaa tärkeitä esimerkkejä Excel INDEX -toiminnon käytöstä. Jos haluat nähdä lisää esimerkkejä tähän luetteloon, kerro siitä kommenttiosassa.

Huomautus: Olen yrittänyt parhaani mukaan todistaa tämän opetusohjelman, mutta jos löydät virheitä tai kirjoitusvirheitä, ilmoita siitä minulle 🙂

Excel INDEX -toiminto - Video -opetusohjelma

• Excel VLOOKUP -toiminto.
• Excel HLOOKUP -toiminto.
• Excel Epäsuora toiminto.
• Excel MATCH -toiminto.
• Excel OFFSET -toiminto.

Saatat pitää myös seuraavista Excel -opetusohjelmista:

• VLOOKUP vs. INDEKSI/MATCH
• Excel -hakemisto
• Haku- ja palautusarvot koko rivillä/sarakkeessa.