Praktični vodič za pivot tablice u Microsoft Excelu

Zamisli scenarij – dobila si na stol gomilu podataka od svog šefa koji želi da ih uneseš u Microsoft Excel, analiziraš i pošalješ mu izvještaj o određenim trendovima koji ga zanimaju. Istina, napisala si u životopisu da znaš raditi u paketu Microsoft Office, ali svi napišu da poznaju Excel, Word, PowerPoint i nisi očekivala ovakav test svojih sposobnosti.

Ako ti se vrti u glavi od same pomisli na ovu situaciju, ne brini jer ti u nastavku donosimo detaljne upute kako ćeš obaviti taj zadatak bez puno muke i usput impresionirati nadređene svojim informatičkim i analitičkim sposobnostima.

Kako napraviti tablicu u Excelu

Krenimo od početka – da bi mogla analizirati zadane podatke i uočiti obrasce, prvo ih sve trebaš unijeti u Microsoft Excel u obliku tablice.

Otvori Excel i odaberi praznu radnu knjigu. U programu Excel postoji mnogo unaprijed definiranih stilova tablica pa prvo provjeri možeš li primijeniti neki od njih da bi si dodatno skratila posao.

• To ćeš učiniti tako da prvo odabereš raspon ćelija u radnoj knjizi koje želiš oblikovati kao tablicu. Ako nisi sigurna koliki će ti točno raspon ćelija trebati, ne brini jer tablicu možeš lako kasnije proširiti. Zatim na kartici Polazno klikni na Oblikuj kao tablicu i odaberi stil koji ti odgovara. U malom prozorčiću koji ti se izbaci stavi kvačicu na Moja tablica ima zaglavlja i potvrdi.
  

• Unesi u zaglavlja stupaca informaciju koji podaci će se nalaziti u ostatku stupaca. U ovom primjeru izradit ćemo tablicu s podacima o prodajama i ostvarenom profitu prodajnih predstavnika tako da ću u zaglavlja stupaca unijeti redom: Prodavač, Regija, Radni sati, Broj prodaja, Ukupna zarada.
• Sada slijedi onaj dosadniji dio posla kad u tablicu treba ručno unijeti sve one podatke koje ti je šef poslao. Zato upali glazbu ako smiješ, opusti se i kreni lagano prepisivati. Pripazi da su svi podaci koje uneseš točni jer sada gradiš temelj na kojem ćemo raditi sve daljnje analize.
• Naša gotova tablica izgleda ovako:
  

Ako te pogled na sve te stupce, retke i brojeve lagano uzrujava, ne brini se. U programu Excel postoji vrlo jednostavan način na koji možeš iz tih unosa izdvojiti ono što te zanima. Prije nego krenemo na upute za taj dio procesa, donosimo ti još jedan zadatak ako osjećaš potrebu da bolje uvježbaš izradu Excel tablica. Ako ti to nije potrebno, preskoči sljedeći odlomak i prijeđi direktno na pivot tablice koje će ti pomoći u analizi podataka koji si unijela u Excel.

Excel tablice za vježbu

Za utvrđivanje svakog znanja potrebna je vježba, a to je pogotovo primjenjivo kad se radi o upotrebi računalnog programa. Zato ti u nastavku donosimo primjer pomoću kojeg možeš izraditi Excel tablicu za vježbu.

Zadatak: Izradi tablicu obročne otplate za klijente.

Upute:

1. Otvori novu radnu knjigu.
2. U ćeliju A1 upiši tekst „Uplate za program“.
3. Unos podataka u drugi redak:
4. U ćeliju A2 upiši „Redni broj“.
5. U ćeliju B2„Ime i prezime“.
6. U ćeliju C2 upiši „1. rata“. Automatskom ispunom unesi podatke desno od ćelije C2 do „5. rata“.
7. U ćeliju H2 upiši „Ukupno:“.
8. U ćeliju A3 upiši broj 1 i potvrdi unos. U ćelije stupca A unesi redne brojeve do broja 10 koristeći automatsku ispunu - drži pritisnutu tipku Ctrl i vuci držač za ispunu dok ne uneseš sve brojeve.
9. U ćelije stupca B upiši imena klijenata, a u ostale stupce vrijednosti kao što je prikazano na slici:
   
10. Popis klijenata spremit ćemo kao korisničku listu za automatsku ispunu.
11. Označi ćelije B3:B12.Klikni na gumb Datoteka - Mogućnosti - Dodatno. Pomakni se prema dolje do odjeljka Općenito, a zatim klikni na Uredi prilagođene popise…
12. Provjeri nalazi li se u polju Uvezi popis iz ćelija: raspon $B$3:$B$12 (ako nije, ponovno ga označi dok je otvoren ovaj prozor). Klikni na gumb Uvezi. Gore s desne strane prozora trebala bi se nalaziti imena klijenata s popisa. Klikni U redu i zatvori prozor.
13. Provjeri jesi li ispravno unijela podatke za automatsku ispunu. U ćeliju C3 unesi prvo ime s liste, „Ivana Horvat“. Ostatak imena unesi pomoću automatske ispune (vuci držač ispune do ćelije C12). Imena u stupcu B i C trebaju biti identična. Poništi unos u stupcu C tako da dva puta na tipkovnici pritisneš CTRL+Z.
14. Označi raspon A1:H1, spoji ga u jednu ćeliju i centriraj sadržaj.
15. U ćeliji A2 prelomi tekst tako da dva puta klikneš unutar ćelije i pomakneš kursor ispred riječi „broj“ te pritisneš Alt+Enter. Tekst „Redni broj“ mora biti ispisan u dva retka unutar iste ćelije. Sadržaj stupca A centriraj.
16. Stupcu B prilagodi širinu tako da dva puta klikneš na desni rub polja stupca sa slovom B.
17. Sadržaj ćelija A2:H2 poravnaj okomito i vodoravno po sredini.
18. U ćelijama C3:H12 gdje su brojčani podaci, dodijeli oblik računovodstvenog broja kn.
19. U ćeliju B13 upiši „Cijena programa:“ (proširi stupac B tako da se vidi sadržaj), a u ćeliju C13 upiši iznos 1.150,00 kn.
20. Sadržaj u ćeliji H2 promijeni u „Ukupno plaćeno:“. Sadržaj ćelije ispiši u dva retka.
21. U stupcu H (Ukupno plaćeno:) izračunaj koliko je novaca za program uplatio svaki klijent (koristi funkciju SUM).
22. Sada bi tvoj rad trebao izgledati ovako:
    
23. Spojenim ćelijama u prvom retku dodijeli stil ćelije Naslov 1.
24. Ćelijama A2:H2 dodijeli stil ćelije 20% - Isticanje 1 (svijetloplavo). Ćelijama dodijeli donji rub identičan gornjem rubu ćelija (pomoću gumba Obrubi - Više obruba otvori dijaloški okvir i odredi donji rub označenim ćelijama).
25. Ćelijama A12:H12 dodijeli debeli donji rub plave boje
26. Sadržaju ćelija B13:C13 dodijeli tamnoplavu boju, tekst2 (četvrti stupac, prvi redak palete boje). Sadržaj ćelija podebljaj. Završena tablica bi trebala izgledati ovako:
    
27. Spremi radnu knjigu pod nazivom Uplate2021.xlsx.

Čemu služe pivot tablice?

Pivot tablice ili zaokretne tablice iznimno su efikasan alat za izračun, sažimanje i analizu podataka koji omogućuje prikaz usporedba, uzoraka i trendova u podacima.

Pomoću pivot tablica u programu Excel možeš izdvojiti podatke iz velike proračunske tablice koju si izradila na početku kako bi ih lakše razumjela i to bez upotrebe formula. Tako možeš grupirati i organizirati podatke prema određenim kriterijima ili uvjetima zbog čega ćeš moći analizirati velike količine podataka (što će sigurno impresionirati tvog šefa).

Pivot tablice vrlo su jednostavne za upotrebu, a možeš ih izraditi ako tvoja početna proračunska tablica sadrži dvije osi – stupce i retke.

Podaci u pivot tablicama nisu fiksirani pa ih možeš mijenjati, premještati, zbrajati, filtrirati itd. kako bi na vrlo brz način mogla dobiti pregledniji prikaz podataka. Osim pivot tablica, za još bolji vizualan prikaz podataka možeš koristiti i zaokretni grafikom u Excelu, no danas se nećemo njime baviti.

Izrada pivot tablice u Excelu

Recimo da tvoj šef traži da provjeriš koja regija je ostvarila najviše prodaja. Sad ćemo se vratiti na našu tablicu prodavača koju smo izradili na početku i iz nje kreirati pivot tablicu.

Napomena: Prije nego krenemo s uputama, zapamti da se u pivot tablici stupac naziva polje što je uvažen naziv prilikom rada s podacima i aplikacijama baza podataka.

• Odaberi ćelije ispunjene podacima u početnoj tablici koje želiš sažeti i analizirati u pivot tablici sa zaglavljima stupaca, ali pripazi da podaci imaju samo jedan redak zaglavlja. To ćeš učiniti tako što ćeš kliknuti mišem na prvu ćeliju i povući preko svih ostalih koje želiš uključiti u pivot tablicu.

Napomena: Potrebna su najmanje tri stupca podataka za izradu pivot tablice, a podaci ne smiju sadržavati duplicirane retke ili prazne retke i stupce.

• Kad si odabrala podatke, klikni na karticu Umetanje i odmah na početku alatne trake gore lijevo odaberi Zaokretna tablica. Otvorit će ti se novi prozorčić u kojem možeš odabrati hoćeš li tablicu smjestiti na postojeći radni list na kojem je osnovna tablica ili želiš otvoriti novi radni list. Ako odabereš postojeći, obilježi na radnom listu lokaciju gdje želiš smjestiti pivot tablicu i na kraju sve potvrdi klikom na U redu.
  

• Kad si potvrdila, otvorit će se novi prozor u kojem ćeš kreirati pivot tablicu unosom uvjeta za polja. Ako ti se nije pojavio Popis polja zaokretne tablice s desne strane, desnom tipkom miša klikni područje zaokretne tablice pa Pokaži popis polja.
• Da bi mogla izraditi izvješće pomoću pivot tablice, prvo trebaš utvrditi polje vrijednosti. To može biti polje (stupac) u tvojoj početnoj tablici koje zadrži brojčane vrijednosti i onda njih pivot tablica zbraja. U našem primjeru polje vrijednosti bit će stupac Ukupna zarada.
• Stavi kvačicu pored Ukupna zarada na popisu polja u prozoru za izradu pivot tablice i vidjet ćeš kako će se navedeno polje odmah pojaviti pod Vrijednosti, a ukupan zbroj svih unosa iz tog stupca prikazat će se u pivot tablici:
  

• Polja zaokretne tablice možemo podijeliti u retke po uvjetima po kojima želimo. U ovom slučaju želimo prikaz koliko je zaradila koja regija od ukupne zarade koja čini naše osnovno polje vrijednosti.
• To ćeš učiniti tako što ćeš u istom izborniku gdje si kreirala polje vrijednosti samo staviti kvačicu pored polja Regija ili povući mišem polje Regija u polje Reci. Pivot tablica automatski će se kreirati na području ćelija koje si odabrala za nju:
  

• I to je to, napravila si svoju prvu pivot tablicu, čestitamo!

Bonus trik: VLOOKUP funkcija

VLOOKUP funkcija jedna je od najčešće korištenih formula u programu Excel koja ti može pomoći pronaći specifičnu informaciju kada postoji velik broj podataka u radnom listu. VLOOKUP označava pretragu podataka po vertikali i može ti vrlo dobro doći u procesu izrade pivot tablice ako trebaš pronaći određenu informaciju.

Vratimo se na trenutak našoj staroj tablici prodajnih predstavnika na kojoj ćemo ti pokazati kako koristiti VLOOKUP funkciju. Recimo da na osnovu podataka iz te tablice izrađuješ novu i želiš samo upisati ime i prezime prodajnog predstavnika, a da Excel sam automatski povuče njegov broj prodaja i ukupnu zaradu.

• Klikni na ćeliju gdje želiš da ti Excel pokaže broj prodaja kad uneseš ime i prezime. Unesi =VLOOKUP i u zagradu ćeš unijeti podatke na osnovu kojih pretražuješ.
• Prvo unosiš vrijednost pretraživanja što se odnosi na ćeliju u koju ćeš upisati ime i prezime što znači da upisuješ B15.
• Zatim unosiš podatke iz tablice, u ovom slučaju A2:E10, ili možeš mišem obilježiti tablicu.
• Sljedeća informacija koju trebaš u formuli je indeks retka što označava polje u kojem piše broj prodaja što bi u našem slučaju bilo 4.
• I zadnji podatak bit će TRUE ili FALSE pri čemu TRUE znači da će Excel tražiti približno točnu vrijednost, a FALSE da će tražiti točnu vrijednost.
• Konačna formula bi trebala izgledati ovako:
  

• Potvrdi unos formule pritiskom Entera. Ne brini kad se u polju Broj prodaja u novoj tablici pokaže #N/A, na tom mjestu pojavit će se iznos kad uneseš Ime i prezime prodavača u prvo polje. Ponovi isto za polje Ukupna zarada, samo u formuli umjesto 4 pod indeks retka unesi 5.
• Sad kad uneseš u prvo polje ime i prezime prodavača, npr. Ivan Horvat, u tablici pod Broj prodaja i Ukupna zarada automatski će se prenijeti podaci iz prve tablice.

Nadamo se da ćeš nakon proučavanja ovih uputa optimistično pristupiti zastrašujuće velikim bazama podataka i s lakoćom pronaći i organizirati informacije uz pomoć pivot tablica i VLOOKUP funkcije.