Trikovi u Excelu 26. deo – Računanje izvan pivot tabela.
PROBLEM KORISNIKA:
U zoni izvan pivot tabele treba da dodam jedan obračun koji upućuje na ćelije u toj tabeli. Kad god iskopiram formulu, dobijem potpuno isti rezultat u svim ćelijama.
REŠENJE:
Problem s kojim se srećete počeo je da se javlja kada je uveden Excel 2002. Ja ga zovem "greška GetPivotData". Ona veoma nervira korisnike, i ja ću sada objasniti tu grešku i kako je zaobići.
[caption id="" align="aligncenter" width="343"]
Slika 1.[/caption]
Na Slici 1, dnevni podaci su već grupisani po mesecima i godinama. U normalnom slučaju biste mogli da dodate neku izračunatu stavku da izračunate stopu rasta kao (2015/2014)-1. Međutim, u grupisanim pivot tabelama nisu dozvoljene izračunate stavke. Zato Vi umesto toga idete na ćeliju D5, unosite znak "jednako", pa idete na ćeliju C5, unosite kosu crtu, idete na ćeliju B5 pa pritisnete "Enter". Rezultat je stopa rasta od 14,54%, što je tačno. Zatim tu formulu iskopirate u donje redove. Ali se nekako dešava da je stopa rasta za svaki mesec identična. Možete da vidite da je od marta 2014. do 2015. godine promet opadao pa je stoga nemoguće da je stopa rasta tokom tog perioda ostala nepromenljiva.
Kada pogledate formulu (izaberite ćeliju D5 i pogledajte u traku sa formulom), vidite sledeće:
=GETPIVOTDATA("Revenue", $A$3,"Date",1, "Years", 2015) / GETPIVOTDATA ( "Revenue", $A$3,"Date",1, "Years",2014)-1.
Nemoguće je da ste Vi uneli išta od ovoga. Vi ste samo koristili miša kada ste pravili formulu ili ste možda koristili tastere sa strelicama i putem tastature redom uneli sledeće: znak "jednako", levo, kosa crta, levo, levo, minus, 1, Enter. U tom slučaju ćete obično napraviti formulu =C5/B5-1.Ali ne i ovaj put. Ovaj redosled unosa vam je ostavio niz koji sadrži dve formule GETPIVOTDATA funkcije.
Šta je funkcija GETPIVOTDATA i kako je dospela u Vašu radnu tabelu (worksheet)? Excel tim se nada da ćete primetiti funkciju GETPIVOTDATA pa ćete otkriti šta je to i zavoleti je i stalno koristiti. Ali to se nikada ne dešava. Umesto toga, ona ljude prosto nervira.
Uzgred, osam godina sam mrzeo funkciju GETPIVOTDATA. Sada je razumem a povremeno čak i koristim.
Glavno pitanje je kako uneti neku formulu a da se ne generiše formula GETPIVOTDATA. Jedan brz i lak način je da se formula ukuca bez upotrebe miša ili tastera sa strelicama. Samo ukucajte =C5/B5-1. Time ćete napraviti formulu koja će moći da se kopira.
Drugi metod je da se trajno isključi opcija koja generiše formulu GETPIVOTDATA. Da biste to uradili, izaberite opcije File, Options pa Formulas. Postoji polje za izbor za Use GetPivotData Functions For PivotTable References (Koristi funkcije GetPivotData za reference pivot tabele). Isključite ga.
[caption id="" align="aligncenter" width="431"]
Slika 2.[/caption]
NAPOMENA:
Još jedan čest problem s formulama izvan pivot tabela jeste taj da se ne snalaze dobro s promenljivom veličinom pivot formula. U primeru na Slici 2 kolona označena kao Q1 prikazuje šest šifara razloga pa formula "% od ukupnog zbira" upućuje na G$11.
[caption id="" align="aligncenter" width="391"]
Slika 3.[/caption]
Formula funkcioniše kada ima šest proizvoda ali zakazuje u Q2, kada ima samo tri šifre razloga. Ukupan zbir se premešta iz reda br. 11 u red br. 8 a formula još uvek deli ćelijom G11 (Slika 3).
[caption id="" align="aligncenter" width="455"]
Slika 4.[/caption]
Rešenje je da se primeni jedan trik u Excelu za dobijanje poslednje vrednosti iz kolone G pomoću funkcije VLOOKUP (Slika 4). Takođe primenite i namenski numerički (custom number) format gde je treća zona prazna kako biste sakrili sve vrednosti "nula" koje se pojavljuju ispod tabele.