Instant dva u jedan – Kako spojiti dve pivot tabele u jednu?

Instant dva u jedan – Kako spojiti dve pivot tabele u jednu?

TrikovINSTANT DVA U JEDAN – KAKO UZ POMOĆ GETPIVOTDATA SPOJITI DVE PIVOT TABELE U JEDNU

Siguran sam da će se većina naprednih korisnika programa Excel složiti sa mnom da su pivot tabele jedan od najkorisnijih alata. On se najćešće koristi za unakrsne analize, ali ponekad nam čak ni pivot nije dovoljan sam po sebi, već samo služi kao prelazna kalkulacija koju koristimo da bismo popunili neku drugu tabelu u već zadatoj formi. Tu na scenu stupa funkcija GETPIVOTDATA, koja preuzima podatke uskladištene u pivotu. U nastavku teksta ću vam pokazati kako sam rešio konkretan problem uz pomoć funkcije GETPIVOTDATA, odnosno kako sam spojio dva pivota u jednu tabelu.

 

Problem:

Radi analize marketinškog budžeta na mesečnom nivou za svaki brend pojedinačno, pojavila se potreba za izveštajem koji treba da prikaže realizaciju (“ACT”), plan (“BUD”), apsolutnu varijansu (“ACT-BUD”) kao i relativnu varijansu (“ACT-BUD”)% marketinških troškova. Dimenzije za poređenje su tržišta, vrste i podvrste troškova i brendovi. Problem je u tome što se ostvareni i budžetirani troškovi nalaze u različitim bazama podataka, pa se samim tim i izveštaji nalaze u dve različite pivot tabele! Ne postoji mogućnost da se podaci spoje u jednu tabelu, odnosno treba da nađemo način da ukrstimo podatke iz dva pivota. Pitanje je kako da uparim podatke iz dva pivota? Na Slici 1 je prikazan pojednostavljen prikaz dve pivot tabele.

 

Dve pivot tabele

Dve pivot tabele

 

Oba pivota napravljena su u programu Excel 2010, “Compact form (Design→Reports Layout→Show in Compact form) a potpuno je isto raditi i sa “običnim” Excel-om 2003 (“Tabular form”). Originalna baza podataka za levi pivot (“Budget”) izgleda ovako:

 

Originalna baza podataka

Originalna baza podataka

 

Rešenje:

Potrebno je izvući podatke iz pivota uz pomoć funkcije GETPIVOTDATA. Finalni izveštaj bi izgledao ovako:

 

Finalno-resenje

Finalno-resenje

 

Sada dolazimo i do ključnog dela, odnosno funkcije GETPIVOTDATA koja treba da povlači podatke iz pivota. Pokazaću formulu na primeru ćelije B4 (“ATL activities, BUD”, 10.150):

 

= GETPIVOTDATA (“BUD 13 “; J6; “Tržište”; “Tržište 1″; “Brend”; “Brend A”; “Activity”; A4)

 

Prvi argument (“BUD 13″) je ime polja iz pivota (pogledaj Sliku 1). Ovaj argument označava šta želite da sabirate. Drugi argument ($J$6) je bilo koja tačka u pivot tabeli (neophodno je da bude fiksirana ćelija). Ovaj argument označava pivot tabelu i dovoljno je stati na bilo koje polje pivota. Sledeći argument se koristi po principu parova: 3 i 4, 5 i 6, 7 i 8. Oni predstavljaju parove podataka koje želimo da izvučemo. Na primer, da smo stavili samo argument 3 (Tržište) i argument 4 (Tržište 1), dobili bismo rezultat 66.180.

Obratite pažnju na poslednji, 8. argument (ćelija A4). Sa ovim argumentom smo dobili univerzalnu formulu za celu kolonu “B”, što znači da je još samo potrebno iskopirati ćeliju B4 “na dole” i onda dobijamo kompletan izveštaj – za troškove na tržištu 1, za brend A. Imajući u vidu da u realnom primeru izveštaj ima dosta redova, jasno je koliki je značaj ovakve formule. Na isti način je urađena i kolona “C” sa ACT podacima, s tim što je jedina razlika u izvoru podataka (koristi se desni pivot za ACT podacima).

Ako u pivotu ne postoji neka pozicija, tada će formula da vrati rezultat #REF. Ovo ne znači da postoji greška, već da je vrednost nula. Zato treba koristiti i funkciju IFERROR(,0) i onda bi konačna formula glasila:

= IFERROR(GETPIVOTDATA(“BUD 13 “;$J$6;”Tržište”;”Tržište 1″; “Brend”;”Brend A”;”Activity”;A4);0)

 

Na kraju, evo i dela jednog izveštaja koje operativno koristim u Grand kafi uz pomoć GETPIVOTDATA. Kompletan izveštaj ima preko 700 linija i dobija se iz dva potpuno različita pivota. Za ažuriranje kompletne tabele potrebno mi je najviše pet minuta. S obzirom na to da realni izveštaj prikazuje troškove za više tržišta i više brendova, primetićete da formula ima više promenljivih nego u prethodnom primeru u kojem su prikazani podaci za tržište 1 i brend A. Važno je pomenuti i da su kolone E:H pomoćne i ne prikazuju se u krajnjem izveštaju, ali neophodne su da bi bilo moguće kopiranje formule do kraja tabele.

 

 Deo izveštaja od 700 redova

Deo izveštaja od 700 redova

 

Da bi funkcija GETPIVOTDATA ispravno radila, svi podaci koje želite da izvučete iz pivota moraju biti vidljivi. Tačnije, ako biste pomoću filtriranja sakrili deo podataka, funkcija bi pokazala grešku ili nulu. Moj savet je da pivot iz kog funkcija izvlači podatke čuvate u skrivenom radnom listu kako biste izbegli eventualne greške.

Napomenuo bih još da se osim klasičnog načina, GETPIVOTDATA može napisati i na jednostavniji način i to tako što u “Formula bar” upišemo znak “=” i kliknemo na bilo koje vrednosno polje u pivot tabeli – tada će se pojaviti cela funkcija. Nakon toga potrebno je samo da izmenite delove koji treba da budu promenljivi, tako što ćete umesto teksta upisati određenu referencu, kao što je već prikazano u primerima.

Cilj ovog teksta jeste da vam ukaže na značaj automatizacije izveštaja u Excelu. Moje mišljenje je da GETPIVOTDATA u mnogim situacijama može da vam ubrza i poboljša proces. Kao krajnji rezultat dobio sam izveštaj koji je kombinacija dva pivota. Za njegovo ažuriranje je potrebno samo osvežiti podatke u pivotu, a sve ostalo vreme raspoloživo je za analizu varijansi i tumačenje rezultata, odnosno za bavljenje suštinom controllinga.

 

Autor teksta je Ivan Veselinović, planer analitičar GRAND PROM.

Tekst je preuzet iz Controlling magazina 01.

Autor

Ivan  Veselinović

Senior Planning Analyst

Atlantic Grupa


Napiši komentar

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 (19)
  • 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ć (300)
  • 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)
  • Marko Devrnja (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 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)
  • Tomo Djekovic (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)
  • Najnoviji Broj Controlling Magazina

    Controlling magazin #12

    Tema:
    "SUPPLY CHAIN"

    Autori:
    Preko 60 autora: Actavis, Elektromreža Srbije, BASF, Uniqa osiguranje...

    Preuzmi elektronsko izdanje