Excel INDEX funkcija (primjeri + video)
Kada koristiti Excel INDEX funkciju
Excel INDEX funkcija može se koristiti kada želite dohvatiti vrijednost iz tabličnih podataka, a imate broj retka i broj stupca podatkovne točke. Na primjer, u donjem primjeru možete upotrijebiti funkciju INDEX za dobivanje oznaka "Tom" u fizici kada znate broj retka i broj stupca u skupu podataka.
Što vraća
Vraća vrijednost iz tablice za navedeni broj retka i broj stupca.
Sintaksa
= INDEX (niz, red_broj, [broj_kolona])
= INDEX (niz, broj redaka, [broj_kolona], [broj_područja])
INDEX funkcija ima 2 sintakse. Prvi se koristi u većini slučajeva, međutim, u slučaju trosmjernog pretraživanja, koristi se drugi (obrađen u primjeru 5).
Ulazni argumenti
- niz - a raspon ćelija ili konstanta niza.
- red_broj - broj retka iz kojeg se dohvaća vrijednost.
- [broj_kolona] - broj stupca iz kojeg se dohvaća vrijednost. Iako je ovo izborni argument, ali ako broj_reda nije naveden, potrebno ga je navesti.
- [broj_područja] - (Izborno) Ako se argument niza sastoji od više raspona, ovaj će se broj koristiti za odabir reference iz svih raspona.
Dodatne napomene (dosadne stvari … ali važno je znati)
- Ako je broj retka ili broj stupca 0, vraća vrijednosti cijelog retka ili stupca.
- Ako se funkcija INDEX koristi ispred reference ćelije (poput A1 :), umjesto vrijednosti vraća referencu ćelije (vidi primjere u nastavku).
- Najčešće se koristi zajedno s funkcijom MATCH.
- Za razliku od VLOOKUP -a, funkcija INDEX može vratiti vrijednost s lijeve strane vrijednosti traženja.
- INDEX funkcija ima dva oblika - oblik polja i obrazac reference
- "Obrazac polja" je mjesto gdje dohvaćate vrijednost na temelju broja retka i stupca iz zadane tablice.
- "Referentni obrazac" je mjesto gdje postoji više tablica, a vi koristite argument area_num za odabir tablice, a zatim dohvatite vrijednost unutar nje koristeći broj retka i stupca (pogledajte primjer ispod ispod).
Excel INDEX funkcija - primjeri
Evo šest primjera korištenja Excel INDEX funkcije.
Primjer 1 - Pronalaženje Tomovih oznaka u fizici (dvosmjerno pretraživanje)
Pretpostavimo da imate skup podataka kao što je prikazano u nastavku:
Da biste pronašli Tomove ocjene u fizici, upotrijebite donju formulu:
= INDEKS ($ B $ 3: $ E $ 10,3,2)
Ova formula INDEX navodi niz kao $ B $ 3: $ E $ 10 koji ima oznake za sve predmete. Zatim koristi broj retka (3) i broj stupca (2) za dohvaćanje Tomovih ocjena u fizici.
Primjer 2 - Dinamiziranje vrijednosti LOOKUP pomoću funkcije MATCH
Možda nije uvijek moguće ručno odrediti broj retka i broj stupca. Možda imate ogroman skup podataka ili ga želite učiniti dinamičnim kako bi automatski identificirao ime i/ili predmet naveden u ćelijama i dao točan rezultat.
Nešto kao što je prikazano ispod:
To se može učiniti kombinacijom INDEKSA i MATCH funkcije.
Evo formule koja će učiniti traženje vrijednosti dinamičkim:
= INDEKS ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))
U gornjoj formuli, umjesto tvrdo kodiranja broja retka i broja stupca, funkcija MATCH se koristi da bi bila dinamična.
- Broj dinamičkog retka dat je sljedećim dijelom formule - MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0). Skenira imena učenika i identificira vrijednost pretraživanja (u ovom slučaju $ 5 USD). Zatim vraća broj retka vrijednosti traženja u skupu podataka. Na primjer, ako je vrijednost pretraživanja Matt, vratit će 1, ako je Bob, vratit će 2 i tako dalje.
- Broj dinamičkog stupca dat je sljedećim dijelom formule - MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0). Skenira nazive predmeta i identificira vrijednost pretraživanja (u ovom slučaju $ H $ 4). Zatim vraća broj stupca vrijednosti traženja u skupu podataka. Na primjer, ako je vrijednost pretraživanja Math, vratit će 1, ako je Physics, vratit će 2 itd.
Primjer 3 - Korištenje padajućih popisa kao vrijednosti traženja
U gornjem primjeru moramo ručno unijeti podatke. To bi moglo oduzeti vrijeme i sklono pogreškama, pogotovo ako imate ogroman popis vrijednosti za pretraživanje.
U takvim je slučajevima dobra ideja stvoriti padajući popis vrijednosti pretraživanja (u ovom slučaju to mogu biti imena učenika i predmeti), a zatim jednostavno odabrati s popisa. Na temelju odabira, formula bi automatski ažurirala rezultat.
Nešto kao što je prikazano ispod:
Ovo čini dobru komponentu nadzorne ploče jer možete imati ogroman skup podataka sa stotinama učenika na stražnjoj strani, ali krajnji korisnik (recimo učitelj) može brzo dobiti ocjene učenika u predmetu jednostavnim odabirom iz padajući izbornik.
Kako to napraviti:
Formula korištena u ovom slučaju ista je kao u primjeru 2.
= INDEKS ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))
Vrijednosti pretraživanja pretvorene su u padajuće popise.
Evo koraka za stvaranje padajućeg popisa Excel:
- Odaberite ćeliju u kojoj želite padajući popis. U ovom primjeru, u G4, želimo imena učenika.
- Idite na Podaci -> Alati za podatke -> Provjera podataka.
- U dijaloškom okviru Validacija podataka na kartici postavki odaberite Popis s padajućeg izbornika Dopusti.
- U izvoru odaberite $ A $ 3: $ A $ 10
- Pritisnite U redu.
Sada ćete imati padajući popis u ćeliji G5. Slično, možete stvoriti jedan u H4 za subjekte.
Primjer 4 - Povratne vrijednosti iz cijelog retka/stupca
U gornjim primjerima koristili smo Excel INDEX funkciju za dvosmjerno pretraživanje i dobivanje jedne vrijednosti.
Što ako želite dobiti sve ocjene učenika. To vam može omogućiti da pronađete maksimalni/minimalni rezultat tog učenika ili ukupne ocjene postignute u svim predmetima.
Na jednostavnom engleskom jeziku prvo želite dobiti cijeli niz bodova za učenika (recimo Boba), a zatim unutar tih vrijednosti identificirati najveći rezultat ili ukupan broj bodova.
Evo trika.
U Excel INDEX funkciji, kada unesete broj stupca kao 0, vratit će vrijednosti cijelog tog retka.
Dakle, formula za ovo bi bila:
= INDEKS ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0)
Sada ova formula. ako se koristi kako jest, vratit će #VALUE! pogreška. Iako prikazuje pogrešku, u pozadini vraća niz koji ima sve ocjene za Toma - {57,77,91,91}.
Ako odaberete formulu u načinu uređivanja i pritisnete F9, moći ćete vidjeti niz koji vraća (kao što je prikazano u nastavku): 
Slično, na temelju vrijednosti pregledavanja, kada je broj stupca naveden kao 0 (ili je ostavljen prazan), vraća sve vrijednosti u retku za traženu vrijednost
Sada za izračun ukupnog rezultata koji je Tom postigao možemo jednostavno upotrijebiti gornju formulu unutar funkcije SUM.
= SUM (INDEKS ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0))
Na sličnim linijama, za izračun najvećeg rezultata, možemo koristiti MAX/LARGE, a za izračun minimalnog, možemo koristiti MIN/SMALL.
Primjer 5 - Trosmjerno pretraživanje pomoću INDEX/MATCH
Excel INDEX funkcija izgrađena je za rukovanje trosmjernim traženjima.
Što je trosmjerno pretraživanje?
U gornjim primjerima koristili smo jednu tablicu s ocjenama za učenike iz različitih predmeta. Ovo je primjer dvosmjernog pretraživanja jer koristimo dvije varijable za dohvaćanje rezultata (ime učenika i predmet).
Pretpostavimo da za godinu dana student ima tri različite razine ispita, jedinični test, srednji i završni ispit (to sam imao dok sam bio student).
Trosmjerno pretraživanje bilo bi mogućnost da se studentu daju ocjene za određeni predmet s određene razine ispita. To bi učinilo trosmjerno pretraživanje jer postoje tri varijable (ime studenta, naziv ispitanika i razina ispita).
Evo primjera trosmjernog pretraživanja:
U gornjem primjeru, osim odabira studentskog imena i naziva predmeta, možete odabrati i razinu ispita. Na temelju razine ispita vraća odgovarajuću vrijednost iz jedne od tri tablice.
Evo formule koja se koristi u ćeliji H4:
= INDEKS (($ 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 = "Unit Test", 1, IF ($ H $ 2 = "Midterm", 2,3)))
Razložimo ovu formulu da bismo razumjeli kako djeluje.
Ova formula uzima četiri argumenta. INDEX je jedna od onih funkcija u Excelu koja ima više od jedne sintakse.
= INDEX (niz, broj redaka, [broj_kolona])
= INDEX (niz, broj redaka, [broj_kolona], [broj_područja])
Do sada smo u svim gornjim primjerima koristili prvu sintaksu, ali za trosmjerno pretraživanje moramo koristiti drugu sintaksu.
Pogledajmo sada svaki dio formule na temelju druge sintakse.
- niz - ($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23): Umjesto korištenja jednog niza, u ovom slučaju koristili smo tri niza unutar zagrada.
- row_num - MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0): MATCH funkcija koristi se za pronalaženje položaja imena učenika u ćeliji $ G $ 4 na popisu imena učenika.
- col_num - MATCH ($ H $ 3, $ B $ 2: $ E $ 2,0): MATCH funkcija se koristi za pronalaženje položaja imena subjekta u ćeliji $ H $ 3 na popisu naziva subjekta.
- [area_num] - IF ($ H $ 2 = ”Unit Test”, 1, IF ($ H $ 2 = ”Midterm”, 2,3)): Vrijednost broja područja govori funkciji INDEX koji niz odabrati. U ovom primjeru imamo tri niza u prvom argumentu. Ako odaberete Unit Test s padajućeg izbornika, funkcija IF vraća 1, a funkcije INDEX odabiru prvi niz iz tri niza (što je $ B $ 3: $ E $ 7).
Primjer 6 - Stvaranje reference pomoću funkcije INDEX (dinamički imenovani rasponi)
Ovo je jedno divlje korištenje funkcije Excel INDEX.
Uzmimo jednostavan primjer.
Imam popis imena kako je prikazano u nastavku:
Sada mogu koristiti jednostavnu INDEX funkciju da dobijem prezime na popisu.
Evo formule:
= INDEKS ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Ova funkcija jednostavno broji broj ćelija koje nisu prazne i vraća zadnju stavku s ovog popisa (radi samo ako na popisu nema praznih mjesta).
E sad, ovdje dolazi čarolija.
Ako formulu stavite ispred reference ćelije, formula će vratiti referencu ćelije odgovarajuće vrijednosti (umjesto same vrijednosti).
= A2: INDEKS ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Očekivali biste da gornja formula vraća = A2: "Josh" (gdje je Josh zadnja vrijednost na popisu). Međutim, vraća = A2: A9 i stoga dobivate niz imena kao što je prikazano u nastavku:
Jedan praktičan primjer gdje ova tehnika može biti korisna je stvaranje dinamičkih imenovanih raspona.
To je to u ovom vodiču. Pokušao sam obuhvatiti glavne primjere korištenja Excel INDEX funkcije. Ako želite vidjeti još primjera dodanih na ovaj popis, javite mi u odjeljku komentara.
Napomena: Dao sam sve od sebe da pročitam ovaj vodič, ali u slučaju da pronađete greške ili pravopisne pogreške, obavijestite me 🙂
Excel INDEX funkcija - video vodič
- Excel VLOOKUP funkcija.
- Excel HLOOKUP funkcija.
- Excel INDIREKTNA funkcija.
- Excel MATCH funkcija.
- Excel OFFSET funkcija.
Možda će vam se svidjeti i sljedeći vodiči za Excel:
- VLOOKUP vs. INDEKS/UTAKMICA
- Podudaranje indeksa programa Excel
- Traženje i vraćanje vrijednosti u cijelom retku/stupcu.
