Funkcija Excel INDEX (primeri + video)
Kdaj uporabiti funkcijo Excel INDEX
Excel INDEX funkcijo lahko uporabite, če želite pridobiti vrednost iz tabelarnih podatkov in imate številko vrstice in številko stolpca podatkovne točke. V spodnjem primeru lahko na primer v funkciji INDEX dobite oznake »Tom« v Fiziki, če poznate številko vrstice in številko stolpca v naboru podatkov.
Kaj vrača
Vrne vrednost iz tabele za podano številko vrstice in številko stolpca.
Sintaksa
= INDEX (matrika, številka vrstice, [stolpec_številka])
= INDEX (matrika, številka vrstice, [stolpec_številka], [številka območja])
Funkcija INDEX ima 2 sintaksi. Prva se v večini primerov uporablja, v primeru trosmernih iskanj pa druga (opisana v primeru 5).
Argumenti vnosa
- matrika - a obseg celic ali konstanta matrike.
- vrstica_število - številko vrstice, iz katere je treba pridobiti vrednost.
- [col_num] - številka stolpca, iz katerega je treba pridobiti vrednost. Čeprav je to neobvezen argument, če pa številka vrstice ni navedena, jo je treba podati.
- [število_območja] - (Izbirno) Če je argument niza sestavljen iz več obsegov, bi to število uporabili za izbiro sklica iz vseh obsegov.
Dodatne opombe (dolgočasne stvari … a pomembno vedeti)
- Če je številka vrstice ali stolpca 0, vrne vrednosti celotne vrstice oziroma stolpca.
- Če se funkcija INDEX uporablja pred sklicem na celico (na primer A1 :), namesto vrednosti vrne sklic na celico (glej primere spodaj).
- Najpogosteje se uporablja skupaj s funkcijo MATCH.
- Za razliko od VLOOKUP -a lahko funkcija INDEX vrne vrednost z leve strani iskalne vrednosti.
- Funkcija INDEX ima dve obliki - obliko matrike in referenčni obrazec
- "Obrazec matrike" je mesto, kjer iz določene tabele pridobite vrednost na podlagi številke vrstice in stolpca.
- »Referenčni obrazec« je tam, kjer je več tabel, z argumentom area_num pa izberete tabelo in nato v njej pridobite vrednost s številko vrstice in stolpca (glejte primer v živo spodaj).
Funkcija Excel INDEX - Primeri
Tukaj je šest primerov uporabe funkcije Excel INDEX.
Primer 1 - Iskanje Tomovih oznak v fiziki (dvosmerno iskanje)
Recimo, da imate nabor podatkov, kot je prikazano spodaj:
Če želite poiskati Tomove ocene v fiziki, uporabite naslednjo formulo:
= INDEKS ($ B $ 3: $ E $ 10,3,2)
Ta formula INDEX podaja matriko kot $ B $ 3: $ E $ 10, ki ima oznake za vse predmete. Nato uporabi številko vrstice (3) in številko stolpca (2) za pridobivanje Tomovih oznak v fiziki.
Primer 2 - Ustvarjanje dinamične vrednosti LOOKUP z uporabo funkcije MATCH
Morda ni vedno mogoče ročno določiti številke vrstice in številke stolpca. Morda imate ogromen nabor podatkov ali pa ga želite narediti dinamičnega, da samodejno identificira ime in/ali predmet, naveden v celicah, in da pravilen rezultat.
Nekaj, kot je prikazano spodaj:
To lahko storite s kombinacijo funkcije INDEX in MATCH.
Tu je formula, s katero bodo iskalne vrednosti dinamične:
= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))
V zgornji formuli se namesto trdega kodiranja številke vrstice in številke stolpca funkcija MATCH uporabi za njeno dinamičnost.
- Številka dinamične vrstice je podana z naslednjim delom formule - MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0). Skenira ime študentov in identificira iskalno vrednost (v tem primeru $ 5 USD). Nato vrne številko vrstice iskalne vrednosti v naboru podatkov. Če je na primer iskalna vrednost Matt, bo vrnila 1, če je Bob, bo vrnila 2 itd.
- Številka dinamičnega stolpca je podana z naslednjim delom formule - MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0). Skenira imena zadev in identificira iskalno vrednost (v tem primeru $ 4 USD). Nato vrne številko stolpca iskalne vrednosti v naboru podatkov. Če je na primer iskalna vrednost Math, bo vrnila 1, če je fizika, bo vrnila 2 itd.
Primer 3 - Uporaba spustnih seznamov kot iskalnih vrednosti
V zgornjem primeru moramo podatke vnesti ročno. To je lahko dolgotrajno in nagnjeno k napakam, še posebej, če imate ogromen seznam iskalnih vrednosti.
V takih primerih je dobra ideja ustvariti spustni seznam iskalnih vrednosti (v tem primeru so to lahko imena učencev in predmeti) in nato preprosto izbrati s seznama. Na podlagi izbire bi formula samodejno posodobila rezultat.
Nekaj, kot je prikazano spodaj:
To je dobra komponenta nadzorne plošče, saj imate lahko na zadnji strani ogromen nabor podatkov s stotinami študentov, vendar lahko končni uporabnik (recimo učitelj) hitro pridobi ocene študenta pri predmetu, tako da preprosto izbere spustni meni.
Kako to narediti:
Formula, uporabljena v tem primeru, je enaka kot v primeru 2.
= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))
Iskalne vrednosti so bile pretvorjene v spustne sezname.
Za ustvarjanje spustnega seznama Excel:
- Na spustnem seznamu izberite celico, v kateri želite. V tem primeru v G4 želimo imena učencev.
- Pojdite na Podatki -> Podatkovna orodja -> Preverjanje podatkov.
- V pogovornem oknu Potrjevanje podatkov na zavihku z nastavitvami izberite Seznam s spustnega menija Dovoli.
- V viru izberite $ A $ 3: $ A $ 10
- Kliknite V redu.
Sedaj boste imeli spustni seznam v celici G5. Podobno lahko za subjekte ustvarite enega v H4.
Primer 4 - Vrnjene vrednosti iz celotne vrstice/stolpca
V zgornjih primerih smo uporabili funkcijo Excel INDEX za dvosmerno iskanje in pridobitev ene vrednosti.
Kaj pa, če želite dobiti vse ocene študenta. Tako lahko ugotovite najvišji/minimalni rezultat tega študenta ali skupne ocene pri vseh predmetih.
V preprosti angleščini želite najprej pridobiti celotno vrstico ocen za študenta (recimo Boba), nato pa znotraj teh vrednosti določiti najvišjo oceno ali skupno število vseh točk.
Tu je zvijača.
V funkciji Excel INDEX, ko vnesete številka stolpca kot 0, vrne vrednosti celotne vrstice.
Formula za to bi bila torej:
= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0)
Zdaj ta formula. če se uporablja kot je, bi vrnil #VALUE! napaka. Medtem ko prikaže napako, v ozadju vrne polje, ki vsebuje vse ocene za Toma - {57,77,91,91}.
Če izberete formulo v načinu za urejanje in pritisnete F9, boste lahko videli matriko, ki jo vrne (kot je prikazano spodaj): 
Podobno glede na to, kakšna je vrednost iskanja, ko je številka stolpca podana kot 0 (ali ostane prazna), vrne vse vrednosti v vrstici za iskalno vrednost
Zdaj za izračun skupnega rezultata, ki ga je dosegel Tom, lahko preprosto uporabimo zgornjo formulo v funkciji SUM.
= SUM (INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0))
V podobnih vrsticah lahko za izračun najvišje ocene uporabimo MAX/LARGE, za izračun minimalne pa MIN/SMALL.
Primer 5 - Trosmerno iskanje z uporabo INDEX/MATCH
Excel INDEX funkcija je zasnovana za tristransko iskanje.
Kaj je tristransko iskanje?
V zgornjih primerih smo za študente iz različnih predmetov uporabili eno tabelo z ocenami. To je primer dvosmernega iskanja, saj za pridobitev ocene uporabljamo dve spremenljivki (ime študenta in predmet).
Recimo, da ima študent v enem letu tri različne stopnje izpitov, preizkus enote, vmesni in zaključni izpit (to sem imel, ko sem bil študent).
Trosmerno iskanje bi pomenilo, da študent dobi ocene za določen predmet z določene stopnje izpita. To bi pomenilo tristransko iskanje, saj obstajajo tri spremenljivke (ime študenta, ime predmeta in stopnja izpita).
Tu je primer tristranskega iskanja:
V zgornjem primeru lahko poleg izbire študentovega imena in imena predmeta izberete tudi stopnjo izpita. Na podlagi ravni izpita vrne ujemajočo se vrednost iz ene od treh tabel.
Tu je formula, uporabljena v celici H4:
= INDEX (($ 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), IF ($ H $ 2 = "Test enote", 1, IF ($ H $ 2 = "Vmesni", 2,3)))
Razčlenimo to formulo, da bi razumeli, kako deluje.
Ta formula upošteva štiri argumente. INDEX je ena od tistih funkcij v Excelu, ki ima več kot eno sintakso.
= INDEX (matrika, številka vrstice, [stolpec_številka])
= INDEX (matrika, številka vrstice, [številka_ stolpca], [številka območja])
Doslej smo v vseh zgornjih primerih uporabili prvo skladnjo, toda za tristransko iskanje moramo uporabiti drugo sintakso.
Zdaj pa poglejmo vsak del formule na podlagi druge skladnje.
- matrika - ($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23): V tem primeru smo v oklepaju uporabili tri matrike.
- row_num - MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0): funkcija MATCH se uporablja za iskanje položaja študentovega imena v celici $ G $ 4 na seznamu študentskega imena.
- col_num - MATCH ($ H $ 3, $ B $ 2: $ E $ 2,0): funkcija MATCH se uporablja za iskanje položaja imena predmeta v celici $ H $ 3 na seznamu imen subjekta.
- [area_num] - IF ($ H $ 2 = "Unit Test", 1, IF ($ H $ 2 = "Midterm", 2,3)): Vrednost številke območja pove funkciji INDEX, katero matriko naj izbere. V tem primeru imamo v prvem argumentu tri matrike. Če iz spustnega menija izberete Test enote, funkcija IF vrne 1, funkcije INDEX pa izberejo prvo matriko iz treh nizov (kar je $ B $ 3: $ E $ 7).
Primer 6 - Ustvarjanje reference z uporabo funkcije INDEX (dinamična imenovana območja)
To je ena divja uporaba funkcije Excel INDEX.
Vzemimo preprost primer.
Imam seznam imen, kot je prikazano spodaj:
Zdaj lahko s preprosto funkcijo INDEX dobim priimek na seznamu.
Tukaj je formula:
= INDEX ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Ta funkcija preprosto šteje število celic, ki niso prazne, in vrne zadnji element s tega seznama (deluje le, če na seznamu ni praznih mest).
Sedaj pa tukaj pride čarovnija.
Če formulo postavite pred sklic na celico, bi formula vrnila sklic na celico ustrezne vrednosti (namesto same vrednosti).
= A2: INDEX ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Pričakujete, da bo zgornja formula vrnila = A2: "Josh" (kjer je Josh zadnja vrednost na seznamu). Vendar vrne = A2: A9 in zato dobite niz imen, kot je prikazano spodaj:
En praktičen primer, kjer je ta tehnika lahko v pomoč, je ustvarjanje dinamičnih imenovanih obsegov.
To je to v tej vadnici. Poskušal sem zajeti glavne primere uporabe funkcije Excel INDEX. Če želite na seznam dodati več primerov, mi to sporočite v razdelku za komentarje.
Opomba: trudil sem se po najboljših močeh, da sem prebral to vadnico, če pa odkrijete napake ali pravopisne napake, mi to sporočite 🙂
Funkcija Excel INDEX - video vadnica
- Excel VLOOKUP funkcija.
- Excel HLOOKUP funkcija.
- Excel INDIRECT funkcija.
- Excel MATCH funkcija.
- Excel OFFSET funkcija.
Morda vam bodo všeč tudi naslednji vaje za Excel:
- VLOOKUP vs. INDEX/MATCH
- Ujemanje indeksa Excel
- Iskanje in vračanje vrednosti v celotni vrstici/stolpcu.
