Instant dva u jedan - Kako 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.

  [caption id="attachment_10508" align="alignnone" width="550"] Dve pivot tabele[/caption]  

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:

  [caption id="attachment_10509" align="alignnone" width="550"] Originalna baza podataka[/caption]   Rešenje:

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

  [caption id="attachment_10510" align="alignnone" width="550"] Finalno-resenje[/caption]  

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.

  [caption id="attachment_10511" align="alignnone" width="550"] Deo izveštaja od 700 redova[/caption]  

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.