- Finansijski Snowboard - http://www.mcb.rs/blog -

INDEX and MATCH u EXCELU – Miloš Cvetković (KONSULTANT), MENADŽMENT CENTAR BEOGRAD

Untitled

Kada vas pitaju da li znate da radite u Excel-u, obično misle da li znate da analizirate podatke kroz pivot i da li koristite VLOOKUP funkciju. Na treninzima sam primetio da polaznici obično znaju da koriste VLOOKUP ili da su je barem jednom koristili, a najčešće je koriste kada popunjavaju dodatne kolone koje su im potrebne za analizu, odnosno, filtriranje u pivotu.

Međutim, izazovi u radu sa VLOOKUP-tom su to što ona ne radi ako se ono što tražite (Lookup_value) ne nalazi u prvoj koloni u okviru tabele iz koje „čupate“ podatke (Table array). Sledeći izazov je to što je treći argument (col_index num) konstanta, tako da ako neko doda ili izbriše kolonu, onda morate ručno da menjate broj kolone iz koje vraćate podatke. Na sve to dodajte desetak kolona, koje popunjavate VLOOKUP funkcijom, tako da vam svaka izmena oduzima vreme.

Rešenje je krajnje jednostavno – koristite INDEX i MATCH funkciju. Controlleri retko koriste ove dve funkcije, ali nema razloga za to jer su vrlo jednostavne. Funkcija INDEX ima tri argumenta:

Rezultat funkcije INDEX je vrednost koja ispisana u ćeliji koja ima koordinate (broj) za red i kolonu u okviru opsega u kojem tražimo (Array). Ako nam je potreban broj zaposlenih u prodajnom objektu broj 109 u Nišu, potrebno je da vratimo podatke iz reda 10 i kolone 4.

Slika 1

Naravno da nećemo ručno da upisujemo koordinate za red i kolonu, već ćemo da koristimo funkciju MATCH za oba argumenta. Funkcija MATCH slična je funkciji VLOOKUP, samo što za razliku od VLOOKUP-a kao rezultat ne vraća vrednost iz ćelije, nego broj kolone ili reda u kojem se nalazi ono šta tražimo. Funkcija MATCH takođe ima tri argumenta:

Da bi smo dobili koordinate za kolonu (kol 4) koristićemo funkciju MATCH. Prvi argument je zaglavlje kolone koju želimo da popunimo. Drugi argument je kompletno zaglavlje tabele iz koje vraćamo vrednosti, a poslednji je nula, za Exact match.

Slika 2

Slika 3

Sličan je princip i kada su nam potrebne koordinate za red. Postoji jedan preduslov da biste mogli da koristite ovu funkciju, a to je zaglavlje. Zaglavlje kolone koju želite da popunite mora da bude isto kao i zaglavlje u tabeli iz koje „čupamo“ podatke. (Npr: Zaposlenih = Zaposlenih). Osim ovog preduslova, morate da obratite pažnju i na još dve stvari: opseg i fiksiranje.

Opseg ćelija u funkcijama MATCH mora da se podudara sa visinom i širinom opsega Array u okviru INDEX funkcije.

Slika 4

Potrebn je još samo da obratimo pažnju na fiksiranje i uspeli smo da primenimo INDEX i MATCH funkciju. Ako pretpostavimo da ćemo svaku funkciju da prevlačimo na dole i na desno, onda postoji pravilo po kojem ćemo da fiksiramo. Pravilo za fiksiranje ćelija:

Kod ovog trećeg pravila postoji nekoliko izuzetaka, ali u suštini ona uvek važe. Ovako to izgleda u praksi:

Slika 5

Prednosti funkcija INDEX i MATCH u odnosu na VLOOKUP su u tome što kolona u okviru koje tražimo neki kriterijum ne mora da bude prva kolona u tabeli. Još jedna prednost je što ne morate da brojite kolonu iz koje vraćate vrednosti, već MATCH funkcija to radi umesto vas. Takođe, ako neko izbriše neku kolonu ili doda novu u bazu iz koje čupamo podatke, funkcija INDEX će vratiti tačan razultat. Probajte slobodno.

Ako ste uspeli da razumete kako rade ove dve funkcije, ne sumnjam da ćete da nađete primenu istih u vašim tabelama. Ove i druge trikove možete da pronađete na blogu: mcb.rs/blog.

Tekst je preuzet iz Controlling magazina #10. Ceo magazin možete pročitati na ovom linku.