Kako pomešati babe i žabe?
Izraz “mešati babe i žabe” koristimo za opisivanje pokušaja da se povežu neke sasvim nespojive stvari. U Excel-u se ovo često odnosi na pravljenje čudnih konglomerata Pivot i tradicionalih tabela. Ovo nikada ne preporučujem, ali ima situacija kada su takvi izveštaji ipak neophodni. Upotrebićemo dovitljivost, uz pomoć funkcija GETPIVOTDATA i OFFSET spojićemo “babe i žabe”. Prilikom pisanja ovog “recepta” nije stradala nijedna baba ni žaba!
Tradicionalan scenario: imate Pivot tabelu sa podacima o ostvarenoj prodaji, a želite da unesete vrednosti planirane prodaje pa da uporedite ova dva parametra i izračunate varijansu. Da bi ste napravili ovakav izveštaj najpre popunite Pivot tabelu. Kada dobijete željeni izveštaj više nema promena! Desno od Pivot tabele najpre treba dodati kolonu sa vrednostima planirane prodaje. Zatim dodajemo jednu praznu kolonu, gde ćemo računati varijansu, a na kraju još jednu kolonu sa vrednostima indeksa: od 0 do n (n korespondira indeksu poslednjeg reda u tabeli).
Kako izračunati varijansu? Najpre se pozicionirajte na prvu ćeliju ispod naslova “Varijansa”, a zatim kliknite na vrednost ostvarene prodaje u prvom redu Pivot tabele. Excel će automatski generisati funkciju GETPIVOTDATA. Zatim dodajemo simbol deljenja, pa kliknemo na ćeliju u kojoj se nalazi vrednost planirane prodaje za prvi artikal i završavamo sa unosom formule.
Kod tradicionalnih tabela bilo bi dovoljno da iskopiramo formulu u preostale ćelije kolone Varijansa i izveštaj bi bio završen. U ovom slučaju to nije moguće, jer se funkcija GETPIVOTDATA referencira tačno na određenu ćeliju. Hajde da analiziramo formulu:
=GETPIVOTDATA(„IZNOS“,$A$3,“NAZIV ARTIKLA“,“Cabernet Sauvignon“)/C4
Funkcija GETPIVOTDATA najpre uzima vrednost izveštajne dimenzije IZNOS, a drugi parametar ($A$3) unosi se da bi smo definisali lokaciju Pivot tabele. Zatim se navodi da se podaci preuzimaju za “NAZIV ARTIKLA” i vrednost “Cabernet Sauvignon”. Da bi Excel mogao automatski da ažurira formulu treba menjati nazive vina, zar ne? Nazivima vina u Pivot tabeli možemo da pristupimo pomoću funkcije OFFSET, pa smo umesto parametra „Cabernet Sauvignon“ mogli i da napišemo formulu:
=OFFSET($A$3,1,0,1,1)
Što označava jednu ćeliju jednu ćeliju dole u odnosu na $A$3. Pošto želimo da se formula automatski ažurira na osnovu različitih vrednosti u redovima iskoristićemo indekse koje smo malopre napravili, pa formulu referenciramo na E4:
=OFFSET($A$3,E4+1,0,1,1)
Na kraju, samo treba iskopirati ovu formulu umesto parametra „Cabernet Sauvignon“ u funkciji GETPIVOTDATA:
=GETPIVOTDATA(„IZNOS“,$A$3,“NAZIV ARTIKLA“,OFFSET($A$3,E4+1,0,1,1))/C4
Ovakvu formulu sada samo treba iskopirati u preostale ćelije u koloni.
Na kraju još samo preostaje da sakrijemo kolonu E, kao i da još malo ulepšamo izveštaj i laik neće moći da uoči da se ovde radi o dva izveštaja koji smo spojili u jedan. Na kraju, ne zaboravite jednu važnu stvar: ovakvi izveštaji imaju smisla sve dok ni na koji način ne menjamo Pivot tabelu.
