Jedna od najkorisnijih karakteristika programa Excel je operator INDEX. Traži podatke u rasponu na sjecištu određenog retka i stupca, vraćajući rezultat u prethodno određenu ćeliju. No, pune mogućnosti ove funkcije otkrivaju se kada se koriste u složenim formulama u kombinaciji s drugim operatorima. Pogledajmo različite mogućnosti njegove primjene.
Korištenje INDEX funkcije
Operator INDEX pripada grupi funkcija iz kategorije Upućivanja i nizovi. Dvije su sorte: za nizove i za reference.
Opcija za nizove ima sljedeću sintaksu:
= INDEX (niz; red_number; stupac_broj)
U isto vrijeme, posljednja dva argumenta u formuli mogu se koristiti i zajedno i bilo koji od njih, ako je niz jednodimenzionalan. Za višedimenzionalni raspon treba koristiti obje vrijednosti. Treba također uzeti u obzir da se broj retka i stupca ne podrazumijeva kao broj na koordinatama lista, već red unutar samog određenog niza.
Sintaksa za referentnu opciju je sljedeća:
= INDEX (link; red_number; stupac_broj; [oblast_broj])
Ovdje na isti način možete koristiti samo jedan od dva argumenta: Broj linije ili Broj stupca. Argument "Područje broja" općenito je opcionalan i primjenjuje se samo ako je u operaciju uključeno nekoliko raspona.
Stoga, operator traži podatke u navedenom rasponu prilikom određivanja retka ili stupca. Ova značajka je vrlo slična VLR operator, ali za razliku od toga, može pretraživati gotovo svugdje, a ne samo u krajnjem lijevom stupcu tablice.
1. metoda: koristite indeks INDEX za polja
Prije svega, hajde da analiziramo operatera pomoću najjednostavnijeg primjera INDEX za nizove.
Imamo tabelu sa platama. U prvom stupcu su prikazana imena zaposlenih, u drugom - datum isplate, a u trećem - iznos iznosa zarade. Moramo u trećem retku prikazati ime zaposlenog.
- Odaberite ćeliju u kojoj će se prikazati rezultat obrade. Kliknite na ikonu "Umetanje funkcije", koji se nalazi odmah s lijeve strane trake formule.
- Postupak aktivacije u tijeku Čarobnjaci za funkcije. U kategoriji Upućivanja i nizovi ovaj alat ili "Kompletna abecedna lista" tražite ime INDEX. Nakon što pronađete ovog operatera, odaberite ga i kliknite na gumb "OK", koji se nalazi na dnu prozora.
- Otvara se mali prozor u kojem trebate odabrati jednu od vrsta funkcija: Niz ili Link. Treba nam opcija Niz. Nalazi se prvo i označeno prema zadanim postavkama. Stoga samo moramo kliknuti na gumb "OK".
- Otvara se prozor argumenata funkcije INDEX. Kao što je već spomenuto, ona ima tri argumenta, i prema tome, tri polja koja treba popuniti.
U polju Niz Morate navesti adresu raspona podataka koji se obrađuje. Može se voziti ručno. Ali da bismo olakšali zadatak, postupit ćemo drugačije. Stavite kursor u odgovarajuće polje, a zatim kružite čitavim rasponom tabličnih podataka na listu. Nakon toga, u polju će se odmah prikazati adresa raspona.
U polju Broj linije stavi broj "3", jer uslovom trebamo odrediti treće ime na popisu. U polju Broj stupca podesite broj "1", jer je stupac s imenima prvi u odabranom rasponu.
Nakon što su sve navedene postavke dovršene, kliknite na gumb "OK".
- Rezultat obrade prikazuje se u ćeliji koja je navedena u prvom odlomku ove upute. Naime, zaključeno prezime je treće na listi u odabranom rasponu podataka.
Ispitali smo primjenu funkcije INDEX u višedimenzionalnom nizu (više stupaca i redaka). Da je raspon jednodimenzionalan, popunjavanje podataka u prozoru s argumentima bilo bi još lakše. U polju Niz istim postupkom kao i gore, naznačujemo njegovu adresu. U ovom se slučaju raspon podataka sastoji od samo vrijednosti u jednom stupcu. "Ime". U polju Broj linije navesti vrijednost "3", jer morate saznati podatke iz trećeg reda. Polje Broj stupca u principu, možete ga ostaviti prazno, jer imamo jednodimenzionalni raspon u kojem se koristi samo jedan stupac. Kliknite na dugme "OK".
Rezultat će biti potpuno isti kao gore.
Ovo vam je bio najjednostavniji primjer da vidite kako ova funkcija funkcionira, ali se u praksi slična verzija njene upotrebe i dalje rijetko koristi.
Lekcija: Čarobnjak za funkcije u Excelu
Metoda 2: koristiti se zajedno s operatorom PRETRAŽI
U praksi funkcija INDEX najčešće korištena argumentom PRETRAŽI. Hrpa INDEX - PRETRAŽI je moćan alat pri radu u Excelu koji je po svojoj funkcionalnosti fleksibilniji od najbližeg analoga - operatera VPR.
Glavni cilj funkcije PRETRAŽI je pokazatelj broja u redoslijedu određene vrijednosti u odabranom rasponu.
Sintaksa operatora PRETRAŽI takve:
= SEARCH (search_value, lookup_array, [match_type])
- Tražena vrijednost - to je vrijednost čiju poziciju u rasponu tražimo;
- Gledano polje je raspon u kojem se nalazi ta vrijednost;
- Vrsta podudaranja - Ovo je izborni parametar koji određuje da li treba tražiti vrijednosti točno ili približno. Tražit ćemo tačne vrijednosti, stoga se ovaj argument ne koristi.
Pomoću ovog alata možete automatizirati unos argumenata Broj linije i Broj stupca u funkciji INDEX.
Pogledajmo kako se to može učiniti konkretnim primjerom. Radimo s istim stolom o kojem je gore govora. Zasebno imamo dva dodatna polja - "Ime" i "Iznos". Potrebno je osigurati da se prilikom unosa imena zaposlenika automatski prikazuje iznos zarađenog novca. Pogledajmo kako se to može primijeniti u praksi primjenom funkcija INDEX i PRETRAŽI.
- Prije svega, saznajemo koliku plaću prima zaposleni Parfenov D. F. Unesite njegovo ime u odgovarajuće polje.
- Odaberite ćeliju u polju "Iznos"u kojem će se prikazati konačni rezultat. Pokrenite prozor argumenata funkcije INDEX za nizove.
U polju Niz upisujemo koordinate stupca u kojem su plaće zaposlenih.
Polje Broj stupca ostavite prazno, jer kao primjer koristimo jednodimenzionalni raspon.
Ali na terenu Broj linije samo moramo napisati funkciju PRETRAŽI. Da bismo ga napisali pridržavamo se gore spomenute sintakse. Odmah u polje unesite ime operatera "PRETRAŽI" bez citata Zatim odmah otvorite nosač i navedite koordinate željene vrijednosti. Ovo su koordinate ćelije u koje smo zasebno zapisali ime zaposlenog Parfenova. Stavljamo tačku s zarezom i označavamo koordinate raspona koji se pregledava. U našem slučaju to je adresa stupca s imenima zaposlenih. Nakon toga, zatvorite držač.
Nakon unosa svih vrijednosti kliknite na gumb "OK".
- Na terenu se prikazuje iznos zarade D. Parfenov nakon obrade "Iznos".
- Sad ako na terenu "Ime" mi ćemo promeniti sadržaj sa "Parfenov D.F."npr. "Popova M. D.", tada će se vrijednost plaća u polju automatski promijeniti "Iznos".
3. način: obrada više tabela
Sada da vidimo kako se koristi operator INDEX Možete obraditi više tabela. U tu svrhu primijenit će se dodatni argument. "Područje broja".
Imamo tri stola. Svaka tabela prikazuje plaće zaposlenih za mjesec dana. Naš zadatak je da treći mjesec (treća regija) otkrijemo platu (treći stupac) drugog zaposlenika (drugi red).
- Odaberite ćeliju u kojoj će se rezultat prikazati i otvorite na uobičajeni način Čarobnjak za funkcije, ali prilikom odabira vrste operatera odaberite referentni prikaz. Potrebno nam je jer ovaj tip podržava rukovanje argumentima. "Područje broja".
- Otvara se prozor argumenta. U polju Link moramo navesti adrese sva tri raspona. Da biste to učinili, postavite kursor u polje i odaberite prvi raspon pritisnutim lijevim gumbom miša. Zatim stavite točku sa zarezom. Ovo je vrlo važno jer ako odmah krenete u odabir sljedećeg niza, tada će njegova adresa jednostavno zamijeniti koordinate prethodnog. Nakon upisivanja zarezom odaberite sljedeći raspon. Zatim ponovo stavljamo zarez i odabiremo zadnji niz. Čitav izraz koji je na terenu Link uzmi u zagrade.
U polju Broj linije navedite broj "2", s obzirom da tražimo drugo prezime na popisu.
U polju Broj stupca navedite broj "3"jer je stupac sa platama treći u nizu u svakoj tabeli.
U polju "Područje broja" stavi broj "3", jer moramo pronaći podatke u trećoj tabeli koja sadrži podatke o platama za treći mjesec.
Nakon unosa svih podataka kliknite na gumb "OK".
- Nakon toga rezultati izračuna prikazani su u prethodno odabranoj ćeliji. Prikazuje iznos plaće drugog zaposlenika (V. M. Safronov) za treći mjesec.
Metoda 4: izračunati iznos
Referentni obrazac se ne koristi tako često kao oblik matrice, ali može se koristiti ne samo za rad s više raspona, već i za druge potrebe. Na primjer, može se koristiti za izračunavanje iznosa u kombinaciji s operatorom SUM.
Prilikom sabiranja iznosa SUM ima sljedeću sintaksu:
= SUM (array_address)
U našem konkretnom slučaju, iznos zarade svih zaposlenih mesečno može se izračunati sledećom formulom:
= SUM (C4: C9)
Ali možete je malo modificirati pomoću funkcije INDEX. Tada će imati sljedeći oblik:
= SUM (C4: INDEX (C4: C9; 6))
U ovom slučaju, koordinate početka niza označavaju ćeliju s kojom počinje. Ali u koordinatama koje označavaju kraj polja koristi se operator INDEX. U ovom slučaju prvi argument operatera INDEX označava raspon, a druga - na zadnjoj ćeliji - šesti.
Lekcija: Korisne Excel funkcije
Kao što vidite, funkcija INDEX može se koristiti u Excelu za rješavanje prilično raznolikih zadataka. Iako smo razmotrili daleko od svih mogućih opcija njegove primjene, ali samo najpopularnije. Postoje dvije vrste ove funkcije: referentna i za nizove. Najefikasnije se može koristiti u kombinaciji s drugim operatorima. Formule stvorene na ovaj način moći će riješiti najsloženije probleme.
