Jedna od najkorisnijih osobina programa Excel je operator INDEX. Pretražuje podatke u rasponu na sjecištu specificiranog retka i stupca, vraćajući rezultat u unaprijed određenu ćeliju. Ali puni potencijal ove funkcije otkriva se kada se koristi u složenim formulama u kombinaciji sa drugim operatorima. Pogledajmo razne opcije za njegovu primjenu.
Korišćenje funkcije INDEX
Operator INDEX pripada grupi funkcija iz kategorije "Linkovi i nizovi". Ima dve varijante: za nizove i reference.
Varijanta za nizove ima sljedeću sintaksu:
= INDEX (niz; red_broj; stupac_broj)
U ovom slučaju, posljednja dva argumenta u formuli mogu se koristiti i zajedno i bilo koji od njih, ako je niz jednodimenzionalan. U višedimenzionalnom opsegu treba koristiti obje vrijednosti. Također treba napomenuti da broj retka i stupca nije broj na koordinatama lista, već red u navedenom nizu.
Sintaksa za referentnu varijantu izgleda ovako:
= INDEX (link; line_number; column_number; [area_number])
Ovdje možete koristiti samo jedan od dva argumenta na isti način: "Broj linije" ili "Broj stupca". Argument "Broj područja" općenito je opcionalno i primjenjuje se samo kada je više operacija uključeno u više raspona.
Operator tako traži podatke u navedenom rasponu kada specificira red ili stupac. Ova funkcija je veoma slična u svojim mogućnostima vpr operator, ali za razliku od njega može pretraživati gotovo svuda, a ne samo u krajnjem lijevom stupcu tablice.
Metoda 1: Koristite INDEX operator za nizove
Pre svega, analiziramo, koristeći najjednostavniji primjer, algoritam za korištenje operatora INDEX za nizove.
Imamo tablicu plata. U prvoj koloni su prikazana imena zaposlenih, u drugom - datum uplate, au trećem - iznos zarade. Moramo da prikažemo ime zaposlenog u trećem redu.
- Izaberite ćeliju u kojoj će se prikazati rezultat obrade. Kliknite na ikonu "Umetni funkciju"koji se nalazi odmah lijevo od formule bar.
- Pojavljuje se postupak aktivacije. Funkcioneri. U kategoriji "Linkovi i nizovi" ovaj instrument ili "Puna abecedna lista" potražite ime INDEX. Nakon što smo pronašli ovog operatera, odaberite ga i kliknite na gumb. "OK"koji se nalazi na dnu prozora.
- Otvara se mali prozor u kojem morate odabrati jedan od tipova funkcija: "Array" ili "Link". Opcija koja nam je potrebna "Array". Nalazi se prvo i podrazumevano je izabrano. Stoga, samo trebamo pritisnuti tipku "OK".
- Otvara se prozor argumenta funkcije. INDEX. Kao što je gore navedeno, ima tri argumenta, i, shodno tome, tri polja za popunjavanje.
Na terenu "Array" Morate navesti adresu opsega podataka koji se obrađuje. Može se upravljati ručno. Ali da bismo olakšali zadatak, nastavićemo drugačije. Postavite kursor u odgovarajuće polje, a zatim zaokružite cijeli raspon tabličnih podataka na listu. Nakon toga, adresa polja se odmah prikazuje u polju.
Na terenu "Broj linije" stavi broj "3", jer uslovom moramo odrediti treće ime na listi. Na terenu "Broj stupca" postavite broj "1"budući da je stupac s imenima prvi u odabranom rasponu.
Nakon izvršenja svih navedenih postavki, kliknemo na gumb "OK".
- Rezultat obrade se prikazuje u ćeliji koja je navedena u prvom stavu ove instrukcije. To je izvedeno prezime koje je treće u popisu u odabranom rasponu podataka.
Analizirali smo primjenu funkcije. INDEX u višedimenzionalnom nizu (nekoliko stupaca i redova). Ako je opseg bio jednodimenzionalan, tada bi popunjavanje podataka u prozoru argumenta bilo još lakše. Na terenu "Array" na isti način kao i gore, navodimo njegovu adresu. U ovom slučaju, raspon podataka se sastoji samo od vrijednosti u jednom stupcu. "Ime". Na terenu "Broj linije" odredite vrijednost "3", jer morate znati podatke iz treće linije. Field "Broj stupca" općenito, možete ga ostaviti praznim, jer imamo jednodimenzionalni raspon u kojem se koristi samo jedna kolona. Pritisnemo dugme "OK".
Rezultat će biti isti kao gore.
To je bio najjednostavniji primjer za vas da vidite kako ova funkcija funkcionira, ali u praksi se ova opcija njezine upotrebe još uvijek rijetko koristi.
Lekcija: Čarobnjak funkcije Excel
Metoda 2: koristiti zajedno sa MATCH operatorom
U praksi, funkcija INDEX najčešće se koristi sa argumentom MATCH. Bunch INDEX - MATCH je moćan alat za rad u Excelu, koji je fleksibilniji u svojoj funkcionalnosti od najbližeg analognog operatera Vpr.
Glavni zadatak funkcije MATCH je oznaka broja u redoslijedu određene vrijednosti u odabranom rasponu.
Sintaksa operatora MATCH takve:
= MATCH (tražena vrijednost, lookup array, [match_type])
- Tražena vrijednost - to je vrijednost čija pozicija u rasponu tražimo;
- Looked array - ovo je raspon u kojem se ta vrijednost nalazi;
- Mapping type - Ovo je opcioni parametar koji određuje da li treba tačno ili približno tražiti vrijednosti. Tražićemo tačne vrednosti, tako da se ovaj argument ne koristi.
Pomoću ovog alata možete automatizirati uvođenje argumenata. "Broj linije" i "Broj stupca" u funkciji INDEX.
Da vidimo kako se to može uraditi sa specifičnim primerom. Radimo sve sa istom tabelom, o kojoj smo već govorili. Odvojeno, imamo dva dodatna polja - "Ime" i "Iznos". Potrebno je to uraditi tako da kada unesete ime zaposlenog, iznos novca koji on zarađuje automatski se prikazuje. Da vidimo kako se to može primeniti u praksi primenom funkcija INDEX i MATCH.
- Pre svega, saznaćemo kakvu platu prima radnik Parfenov, a mi unosimo njegovo ime u odgovarajuće polje.
- Izaberite ćeliju u polju "Iznos"u kojem će se prikazati konačni rezultat. Pokrenite prozor argumenta funkcije INDEX za nizove.
Na terenu "Array" unosimo koordinate kolone u kojoj se nalaze sume zarada zaposlenih.
Field "Broj stupca" ostavljamo prazno, jer, na primjer, koristimo jednodimenzionalni raspon.
Ali na terenu "Broj linije" samo trebamo napisati funkciju MATCH. Da bismo je napisali, slijedimo gore opisanu sintaksu. Odmah u polje unesite ime operatera "MATCH" bez navodnika. Zatim odmah otvorite nosač i odredite koordinate željene vrijednosti. To su koordinate ćelije u kojoj smo posebno zabilježili ime radnika Parfenova. Stavimo tačku-zarez i odredimo koordinate pregledanog opsega. U našem slučaju, ovo je adresa kolone sa imenima zaposlenih. Nakon toga zatvorite držač.
Nakon unosa svih vrijednosti, kliknite na gumb "OK".
- Rezultat iznosa zarade Parfenova DF nakon obrade je prikazan u polju "Iznos".
- Sada ako je polje "Ime" mijenjamo sadržaj sa "Parfenov D.F."na, na primjer, "Popova M.D."tada će se vrijednost plaće u polju automatski mijenjati. "Iznos".
Metoda 3: obrada više tabela
Sada ćemo vidjeti kako se koristi operater INDEX Možete rukovati s više tablica. U tu svrhu će se koristiti dodatni argument. "Broj područja".
Imamo tri stola. Svaka tabela prikazuje plate zaposlenih za određeni mjesec. Naš zadatak je da saznamo plate (treća kolona) drugog zaposlenog (drugi red) za treći mjesec (treći region).
- Izaberite ćeliju u kojoj će rezultat biti prikazan i na uobičajen način otvoren Čarobnjak za funkcije, ali pri odabiru tipa operatora odaberite referentni prikaz. Ovo nam je potrebno jer ovaj tip podržava rad sa argumentom "Broj područja".
- Otvara se prozor argumenta. Na terenu "Link" treba da navedemo adrese sva tri opsega. Da biste to uradili, postavite kursor u polje i izaberite prvi opseg levim tasterom miša. Onda stavimo tačku i zarez. Ovo je veoma važno, jer ako odmah pređete na izbor sledećeg niza, njegova adresa će jednostavno zameniti koordinate prethodnog polja. Dakle, nakon uvođenja točka-zareza, odaberite sljedeći raspon. Zatim ponovo stavljamo tačku i zarez i odabiremo poslednji niz. Svi izrazi koji se nalaze u polju "Link" uzmi u zagradama.
Na terenu "Broj linije" navedite broj "2", jer tražimo drugo ime na listi.
Na terenu "Broj stupca" navedite broj "3", pošto je kolona plata treća u svakoj tabeli.
Na terenu "Broj područja" stavi broj "3", jer moramo naći podatke u trećoj tabeli, koja sadrži informacije o platama za treći mjesec.
Nakon unosa svih podataka, kliknite na dugme "OK".
- Nakon toga, rezultati izračuna se prikazuju u unaprijed odabranoj ćeliji. Prikazuje iznos plate drugog zaposlenog (V. Safronov) za treći mjesec.
Metod 4: Izračunavanje suma
Referentni obrazac nije tako često korišćen kao obrazac niza, ali se može koristiti ne samo pri radu sa više opsega, već i za druge potrebe. Na primjer, može se koristiti za izračunavanje iznosa u kombinaciji s operatorom SUM.
Kada se zbraja iznos SUM ima sljedeću sintaksu:
= SUM (adresa niza)
U našem slučaju, iznos zarade svih radnika za mjesec može se izračunati pomoću sljedeće formule:
= SUM (C4: C9)
Ali možete ga malo modifikovati pomoću funkcije INDEX. Onda će izgledati ovako:
= SUM (C4: INDEX (C4: C9; 6))
U tom slučaju, koordinate početka niza označavaju ćeliju s kojom počinje. Ali u koordinatama specificiranja kraja niza, koristi se operator. INDEX. U ovom slučaju, prvi argument operatora INDEX označava domet, a drugi do poslednje ćelije je šesti.
Lekcija: Korisne Excel funkcije
Kao što vidite, funkcija INDEX može se koristiti u Excelu za rješavanje raznovrsnih zadataka. Iako smo razmotrili daleko od svih mogućih opcija za njegovu upotrebu, ali samo one koje su tražene. Postoje dvije vrste ove funkcije: referentna i za nizove. Najefikasnije se može koristiti u kombinaciji sa drugim operaterima. Ovako kreirane formule moći će riješiti najsloženije zadatke.
