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

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

14440674_1137047216379716_7522169480346175562_n

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:

  • Array – opseg ćelija iz kojih želite da vratite vrednost. Kao kod VLOOKUP-a (Table array). Ovde možete da uzmete u opseg celu tabelu u kojoj se nalaze vrednosti koje želite da vratite i ostali podaci iz te tabele. U nekim slučajevima možete da selektujete i sve ćelije u Sheet-u.
  • Row_num – slično kao i treći argument (col_index num) u VLOOKUP funkciji. Potrebno je da upišete kordinate (broj) za red u kojem se nalazi podatak koji želite da vratite.
  • Column_num – isto kao i Row_num. Upisujete kordinate (broj) kolone u kojoj se nalaze podaci koje želite da vratite.

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:

  • Lookup_value – šta tražimo, kao kod VLOOKUP-a. Može da bude šifra proizvoda, naziv proizvoda…
  • Lookup_array – gde tražimo. Ovde je važno da selektujete ćelije u okviru jednog reda ili jedne kolone u kojoj tražimo šifru, naziv proizvoda… U nekim slučajevima možete da selektujete celu kolonu ili ceo red. Važno je da znate da u okviru Lookup_array-a ne možete da imate više od jednog reda ili jedne kolone u selekciji.
  • Match_type – kao i kod VLOOKUP-a, najčešće (u 99,9% slučajeva) koristimo nulu za Exact match.

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:

  • sve što se nalazi levo od INDEX funkcije – fiksiraj kolonu
  • sve što se nalazi iznad INDEX funkcije – fiksiraj red
  • sve što se nalazi u drugom Sheet-u – fiksiraj sve.

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.

Autor

Miloš  Cvetković


3 Komentara

  1. Ema :
    11.04.2017 u 13:08|Permalink

    Jos jedna opcija je da se koristi Lookup kao varijacija a sto se tice treceg argumenta moue se uspjesno kombinovati sa funkcijom Columns

  2. Ema :
    11.04.2017 u 13:08|Permalink

    Jos jedna opcija je da se koristi Lookup kao varijacija a sto se tice treceg argumenta moze se uspjesno kombinovati sa funkcijom Columns

  3. Vesna :
    06.09.2017 u 16:20|Permalink

    Meni samo nije jasno zasto je u Match za red upisano B5? predstavite lepo za red kao sto ste za kolonu slikama

Napiši odgovor Ema Cancel reply

Newsletter pretplata

Autori Tagovi
  • Aleksandar Duković (4)
  • Aleksandar Nedeljković (1)
  • Aleksandar Nešić (1)
  • Aleksandar Pejčić (1)
  • Aleksandar Predić (2)
  • Aleksandar Teofilović (3)
  • Aleksandra Dutina (2)
  • Aleksandra Janković (1)
  • Aleksandra Paunić (1)
  • Aleksandra Đorđević (2)
  • Almir A. Budalica (20)
  • Ana Petonjić (1)
  • Ana Petrović (1)
  • Andreas Graf (1)
  • Andrej Beslać (1)
  • Andrej Kuku (1)
  • Andrija Pavlović (2)
  • Anja Atanasijević (1)
  • Anja Dadasović (1)
  • Biljana Dimitrijević (1)
  • Biljana Dukovic (1)
  • Biljana Masić (6)
  • Bjarte Bogsnes (1)
  • Bogdan Kravić (1)
  • Bojan Grahovac (1)
  • Bojan Pavlović (1)
  • Bojan Radoš (2)
  • Bojan Radun (1)
  • Bojan Šćepanović (517)
  • Bojana Pejčić (26)
  • Bojana Radović (1)
  • Bojana Vesić Anić (1)
  • Bojana Vesić Antić (1)
  • Branislav Lončar (3)
  • Branislav Vujović (1)
  • Branislav Zobenica (1)
  • Cmilja Tucaković (1)
  • Dalibor Pajić (1)
  • Daniela Laketic (31)
  • Danijela Medić (1)
  • Danijela Popović (1)
  • Danijela Resimić (1)
  • Danijela Tuco (1)
  • Darko Bjelić (2)
  • Darko Samardžija (1)
  • Darko Vlajkovic (27)
  • David Momčilović (1)
  • Davor Sakač (1)
  • Dejan Marković (1)
  • Dejan Đorđević (1)
  • Dejana Manić Kavgić (2)
  • Denis Trbović (3)
  • Desa Ćuk (1)
  • Dimitrije Stojanović (1)
  • Vojislav Marjanović (3)
  • Dragan Radosavljević (2)
  • Dragan Ranisavljević (1)
  • Dragan Vještica (3)
  • Dragana Gondžo (1)
  • Dragana Guzina (1)
  • Dragana Lukić (1)
  • Dragana Nerandžić (1)
  • Dragana Ovčarov (1)
  • Dragana Stojanović (2)
  • Dragana Đorđević (1)
  • Draško Marković (1)
  • Dražan Planinić (1)
  • Dušan Krejaković (1)
  • Excel genije! (151)
  • Fabrizio Renzi (1)
  • Goran Petrović (1)
  • Goran Popović (4)
  • Goran Slijepčević (1)
  • Goran Vasić (1)
  • Goran Vujasinović (1)
  • Gorana Golubovic Vuksanovic (1)
  • Goranka Radojčić (2)
  • Gordana Jankov (1)
  • Gostujuci autor (514)
  • Ilija Vujović (1)
  • Irina Zdravković (2)
  • Istok Pavlović (1)
  • Ivan Anđelković (1)
  • Ivan Bešker (1)
  • Ivan Ćirković (1)
  • Ivan Dačković (1)
  • Ivan Kostadinović (1)
  • Ivan Veselinović (3)
  • Ivan Đurić (1)
  • Ivana Dagović (1)
  • Ivana Jevtić (1)
  • Ivana Milić (1)
  • Ivana Popović (1)
  • Ivana Savović (1)
  • Ivana Višnjić (3)
  • Ivana Vlahović (1)
  • Jasenka Stekić (1)
  • Jelena Jovanović (1)
  • Jelena Lečić Mirčetić (1)
  • Jelena Radovanac (1)
  • Jelena Ristić (1)
  • Jelena Stojmenović (1)
  • Jelena Vučković (1)
  • Jovan Gligorijević (1)
  • Jovan Krstić (1)
  • Jovana Kondić (2)
  • Jovana Stanojčić (1)
  • Jovo Stokić (2)
  • Katarina Ćevriz (2)
  • Kristina Bojović (1)
  • Ksenija Karić (1)
  • Lazar Džamić (1)
  • Leo Pandžić (2)
  • Lidija Latinović (1)
  • Ljubomir Skupek (1)
  • Lucija Vesić (1)
  • Maja Mrkalj (1)
  • Maja Nikčević Rmandić (1)
  • Maja Petrović (1)
  • Maja Šobot (1)
  • Maja Verlašević (1)
  • Maja Zikic (1)
  • Mari Glavonjić (2)
  • Marija Đurović (1)
  • Marija Kostić (1)
  • Marija Rokvić (1)
  • Marija Švigir (32)
  • Marija Tatarević (3)
  • Marijana Krička (1)
  • Marina Mitić Jekić (1)
  • Mariora Andraš Tomić (1)
  • Marko Agatonović (2)
  • Marko Bekić (1)
  • Matjaž Fajfar (2)
  • Milan Listeš (2)
  • Milan Maglov (1)
  • Milan Manić (1)
  • Milan Marković (1)
  • Milan Milenković (1)
  • Milan Radivojevic (1)
  • Milan Simić (1)
  • Milan Zaletel (1)
  • Milan Đorđević (1)
  • Mile Mitrović (47)
  • Milena Janjić (3)
  • Milena Nikolov (1)
  • Milena Radosavljević Đorić (1)
  • Milena Rajić (1)
  • Milenko Dželetović (1)
  • Milica Altgelt (1)
  • Milica Ivic (1)
  • Milica Đorđević (1)
  • Miloš Cvetković (7)
  • Miloš Jauković (1)
  • Miloš Jeličić (2)
  • Miloš Nedeljković (1)
  • Miloš Pucarević (1)
  • Miloš Puzić (1)
  • Miloš Simić (1)
  • Miloš Vasić (1)
  • Miloš Zeković (1)
  • Miona Živkov-Ivanišević (4)
  • Mirjana Pašalić (2)
  • Jelena Jovanović (5)
  • Mladen Ranković (1)
  • Natalija Jegdić (1)
  • Natalija Mihajlović (1)
  • Natalija Pešić (2)
  • Natalija Popovic (1)
  • Nataša Kuzmanovski (1)
  • Nataša Stamenković (2)
  • Nataša Žikić Buha (1)
  • Nebojša Vlatković (1)
  • Neda Čičarević Tepić (1)
  • Neda Jovanović (1)
  • Nemanja Knežević (1)
  • Nemanja Nikolić (3)
  • Nenad Dlačić (1)
  • Nenad Kovačević (1)
  • Nenad Radunović (1)
  • Nenad Trajkovski (1)
  • Nevena Dokmanović (1)
  • Nevena Radovanović (1)
  • Nevena Vračar (1)
  • Nikola Avram (1)
  • Nikola Cvijanović (1)
  • Nikola Petreski (1)
  • Nikola Stokić (2)
  • Nikola Turkan (1)
  • Nikša Vušurović (1)
  • Olga Mirković (1)
  • Olivera Dostanić (1)
  • Olivera Krneta (1)
  • Olivera Nikodijević (1)
  • Petar Ćurčić (1)
  • Petar Popović (1)
  • Petar Živković (3)
  • Predrag Krasojevic (7)
  • Predrag Kudra (1)
  • Predrag Micić (1)
  • Predrag Petrović (5)
  • Rade Hajder (1)
  • Rade Radanović (2)
  • Rade Stojisavljević (1)
  • Radomir Petronijević (1)
  • Rajko Vasojević (1)
  • Roland Seeliger (1)
  • Sandra Rapo (1)
  • Sanja Jevđenijević (1)
  • Sanja Mrđanov (2)
  • Saša Stamenković (1)
  • Silvia Tomić (1)
  • Slavica Vučetić (1)
  • Slavica Vujičić (1)
  • Slaviša Lečić (2)
  • Slavko Vujnovic (7)
  • Slobodan Anić (2)
  • Slobodan Radoičić (1)
  • Slobodan Roksandić (1)
  • Slobodan Žepinić (1)
  • Snežana Glavonjić (1)
  • Snežana Trajkovski (1)
  • Snježana Pivaš (1)
  • Sonja Nenić Andreev (1)
  • Srdjan Grbić (1)
  • Srđan Grubić (1)
  • Srđan Pavlović (1)
  • Stefanija Georgieva (8)
  • Stevan Ćomić (1)
  • Svetlana Mirković Borčić (1)
  • Svetlana Pajić (1)
  • Svetlana Žikić (2)
  • Tamara Dragašević (1)
  • Tamara Lazić (3)
  • Tamara Matović (2)
  • Tatjana Bolpačić (22)
  • Tatjana Jovanović (1)
  • Tatjana Lukić (2)
  • Tijana Anđelić (1)
  • Tijana Drljević (1)
  • Tijana Rauš (1)
  • Tomislav Mimica (1)
  • Vedran Babik (1)
  • Vedrana Božić (2)
  • Vedrana Vukša (1)
  • Velibor Ilić (1)
  • Veljko Stanojković (1)
  • Veljko Žarić (1)
  • Veroljub Zmijanac (1)
  • Vesna Stojanvić (1)
  • Violeta Kovačević (1)
  • Vladan Buha (1)
  • Vladan Matović (1)
  • Vladimir Petković (2)
  • Vladimir Popović (1)
  • Vladimir Stojković (1)
  • Yeghishe Avagyan (1)
  • Žaklina Teofilović (2)
  • Žarko Milovanović (1)
  • Žarko Savić (1)
  • Željko Ćulibrk (1)
  • Željko Vidojević (1)
  • Živka Mutlak (1)
  • Živorad Radovanović (1)
  • Zoran Blagojević (1)
  • Zoran Daljević (1)
  • Zorica Dinić (1)
  • Zorica Popović (1)
  • Đorđe Koprivica (2)
  • Đorđe Milinković (1)
  • Đorđe Živanović (1)
  • Trenutno nema magazina