Trikovi u Excelu 137. deo – “Koncept šablonske pivot tabele”

Da li mogu da sačuvam formatiranje u nekoj šablonskoj tabeli?

PROBLEM KORISNIKA: Svakog meseca moram da menjam format pivot tabele. Da li mogu da kreiram šablonsku tabelu koja će zapamtiti moje omiljene postavke?

REŠENJE: "Koncept šablonske pivot tabele" javlja se kao čest zahtev. Predviđam da Microsoft to neće dodati jer su ljudi iz Microsoft-a prihvatili formiranje šablonske tabele pomoću komande GetPivotData.

    Tu tehniku sam naučio od bivšeg službenika Microsoft-a. To je stvarno sjajan način da unapredite svoj rad na izradi mesečnih izveštaja. Tokom prvog meseca će vam biti potrebno dodatnih 15 minuta ali će vam ta tehnika ubuduće samo štediti vreme.

1. Napravite jednu ružnu, neformatiranu pivot tabelu sa svim poljima koji će vam biti potrebni u finalnom izveštaju.

2. Na novoj radnoj tabeli napravite skelet izveštaja bez pivot tabela koji sadrži sva formatiranja koja hoćete da koristite.

3. Počnite sa jednom ćelijom skeleta izveštaja. Unesite znak "=". Idite u pivot tabelu i pronađite ćeliju koja sadrži odgovarajuće podatke. Kliknite na tu ćeliju i pritisnite Enter. Microsoft će za vas napraviti formulu GETPIVOTDATA.

PAŽNJA: Polja u formuli su nepromenljiva i ne mogu da se kopiraju u druge ćelije u     izveštaju.

1. Prepravite prvu formulu tako da koristi oznake u vašem izveštaju.

2. Iskopirajte formulu u ceo izveštaj.

    Svakog meseca, tok rada će biti sledeći: Dodajte nove podatke u skup podataka. Osvežite ružnu pivot tabelu. Odštampajte lepo formatiran izveštaj koji podatke izvlači iz pivot tabele.

    Evo nekoliko primera:

    Slika 1 prikazuje jednu veoma ružnu pivot tabelu. Excel u kolonu D dodaje planirane i stvarne iznose, što je beskorisno. Nema načina da se dobiju stvarni podaci za period januar-maj i plan za jun-decembar a da se ne prikažu oba polja za svaki mesec.

    Vi biste svakog meseca obično pravili ovu pivot tabelu, kopirali je i umetnuli kao vrednosti. Zatim biste se otarasili kolona koje vam nisu potrebne. Ponovo biste poređali prodavnice po geografskom kriterijumu. Bilo bi potrebno pola sata da se iskopirani izveštaj formatira.

    Umesto toga, napravite skelet izveštaja na novoj radnoj tabeli. Formatirajte izveštaj onako kako želite da bude prikazan. Ako želite podvlačenje ili duplo podvlačenje podataka, dodajte ga. Ako želite da stavite znak dolara u 1. red i red sa zbirom, učinite to. Ako želite prazan red, dodajte ga. Možete uraditi šta god želite jer ovo nije pivot tabela (Slika 2). To je samo obična Excel tabela.

    Prva ćelija s podacima u izveštaju odnosi se na stvarne podatke za januar za Baybrook Mall. Izaberite tu ćeliju. Unesite znak "=". Pređite u pivot tabelu i pronađite ćeliju sa stvarnim podacima za januar za Baybrook Mall. Kliknite na tu ćeliju pa pritisnite OK.

    Najčešće kada Excel ubacuje formulu GETPIVOTDATA ovo je dosadan sporedni efekat pravljenja formule pomoću miša. Ovog puta to je presudno za pravljenje ovog izveštaja.

    Osnovni problem sa automatski generisanom funkcijom GETPIVOTDATA jeste taj da su vrednosti oznaka u formuli nepromenljive umesto da pokazuju na ćelije u radnoj tabeli (Slika 3).

    Računovođe u Microsoft-u koje redovno koriste ovaj trik nazivaju ovaj sledeći korak "parametriziranje formule". Promenite tri tekstualne vrednosti u formuli tako da pokazuju na adrese ćelija. Pobrinite se da upotrebite odgovarajuće oznake dolara. Zamenite "Baybrook" oznakom $D6, "Jan" oznakom E$3 i "Actual" oznakom E$4 (Slika 4).

    Sada možete da iskopirate tu prvu formulu i da je umetnete pomoću komande Paste Special Formulas u sve druge ćelije izveštaja (Slika 5).

    Rezultat je divno formatiran izveštaj koji preuzima podatke iz pivot tabele. On ne izgleda kao pivot tabela ali koristi svu njenu snagu (Slika 6). Sledećeg meseca dodajte stvarne podatke za maj, osvežite pivot tabelu i izveštaj će se ažurirati novim vrednostima.

JOŠ POJEDINOSTI: Celija P1 u izveštaju jeste datum koji ja ručno unosim svakog meseca. Taj datum koriste formule u 4. redu da na osnovu njega prikažu stvarne ili planirane iznose.

=IF(MONTH(DATEVALUE(E3&"1,2014"))<=MONTH($P$1),"Actual","Plan").