sadržaj
Pivot tabele je jedan od najmoćnijih alata u Excelu. Oni vam omogućavaju da analizirate i sumirate različite sažetke velikih količina podataka sa samo nekoliko klikova mišem. U ovom članku ćemo se upoznati sa pivot tablicama, razumjeti šta su, naučiti kako ih kreirati i prilagoditi.
Ovaj članak je napisan pomoću programa Excel 2010. Koncept zaokretnih tabela se nije mnogo promijenio tokom godina, ali način na koji ih kreirate je malo drugačiji u svakoj novoj verziji Excela. Ako imate verziju Excela ne 2010, budite spremni da će se snimci ekrana u ovom članku razlikovati od onoga što vidite na ekranu.
Malo povijesti
U ranim danima softvera za proračunske tablice, Lotus 1-2-3 kuglica pravila. Njegova dominacija bila je toliko potpuna da su napori Microsofta da razvije sopstveni softver (Excel) kao alternativu Lotusu izgledali kao gubljenje vremena. Sada brzo naprijed u 2010! Excel dominira u proračunskim tabelama više nego što je Lotus kod ikada učinio u svojoj istoriji, a broj ljudi koji još uvijek koriste Lotus je blizu nule. Kako se ovo moglo dogoditi? Šta je bio razlog tako dramatičnog preokreta?
Analitičari identifikuju dva glavna faktora:
- Prvo, Lotus je odlučio da je ova novonastala GUI platforma nazvana Windows samo prolazna moda koja neće dugo trajati. Odbili su da naprave Windows verziju Lotusa 1-2-3 (ali samo na nekoliko godina), predviđajući da će DOS verzija njihovog softvera biti sve što će potrošačima ikada trebati. Microsoft je prirodno razvio Excel posebno za Windows.
- Drugo, Microsoft je predstavio alat u Excelu pod nazivom PivotTables koji nije bio dostupan u Lotusu 1-2-3. Zaokretne tabele, ekskluzivne za Excel, pokazale su se toliko korisnim da su se ljudi držali novog Excel softverskog paketa radije nego da nastave sa Lotusom 1-2-3, koji ih nije imao.
Zaokretne tabele su, uz potcenjivanje uspeha Windows-a uopšte, odigrale marš smrti za Lotus 1-2-3 i dovele do uspeha Microsoft Excel-a.
Šta su pivot tabele?
Dakle, koji je najbolji način da okarakterišete šta su zaokretne tabele?
Jednostavno rečeno, zaokretne tabele su rezimei nekih podataka, kreirani da bi se olakšala analiza ovih podataka. Za razliku od ručno kreiranih ukupnih iznosa, Excel zaokretne tabele su interaktivne. Jednom kreirane, možete ih lako modificirati ako ne daju sliku kojoj ste se nadali. Sa samo nekoliko klikova mišem, zbrojevi se mogu preokrenuti tako da naslovi kolona postanu naslovi redova i obrnuto. Sa zaokretnim tabelama možete učiniti mnoge stvari. Umjesto da pokušavate riječima opisati sve karakteristike pivot tablica, lakše je to demonstrirati u praksi…
Podaci koje analizirate pomoću zaokretnih tabela ne mogu biti nasumični. To bi trebali biti neobrađeni podaci, kao neka vrsta liste. Na primjer, to može biti lista prodaje koju je kompanija napravila u posljednjih šest mjeseci.
Pogledajte podatke prikazane na donjoj slici:
Imajte na umu da ovo nisu neobrađeni podaci, jer je već sažeto. U ćeliji B3 vidimo $30000, što je vjerovatno ukupan rezultat koji je James Cook napravio u januaru. Gdje su onda originalni podaci? Odakle cifra od 30000 dolara? Gdje je originalna lista prodaje iz koje je izveden ovaj mjesečni iznos? Jasno je da je neko odlično obavio posao organiziranja i sortiranja svih podataka o prodaji za posljednjih šest mjeseci i pretvaranja u tabelu ukupnih iznosa koju vidimo. Šta mislite koliko je trajalo? Sat? Deset sati?
Činjenica je da gornja tabela nije pivot tabela. Napravljen je ručno od neobrađenih podataka pohranjenih na drugom mjestu i za njegovu obradu bilo je potrebno najmanje nekoliko sati. Upravo takva zbirna tabela može se kreirati pomoću pivot tabela za samo nekoliko sekundi. Hajde da shvatimo kako…
Ako se vratimo na originalnu prodajnu listu, to bi izgledalo otprilike ovako:
Možda ćete se iznenaditi da iz ove liste trgovina uz pomoć pivot tabela i za samo nekoliko sekundi možemo kreirati mjesečni izvještaj o prodaji u Excelu, koji smo analizirali iznad. Da, možemo to i više!
Kako kreirati pivot tabelu?
Prvo, uvjerite se da imate neke izvorne podatke u Excel listu. Lista finansijskih transakcija je najtipičnija koja se dešava. Zapravo, to može biti lista bilo čega: kontakt podaci zaposlenika, kolekcija CD-a ili podaci o potrošnji goriva vaše kompanije.
Dakle, pokrećemo Excel… i učitavamo takvu listu…
Nakon što otvorimo ovu listu u Excel-u, možemo početi kreirati zaokretnu tabelu.
Odaberite bilo koju ćeliju sa ove liste:
Zatim na kartici umetanje (Insert) odaberite komandu Zaokretna tabela (Pivot table):
Pojavit će se okvir za dijalog Kreirajte zaokretnu tabelu (Kreiranje pivot tabele) sa dva pitanja za vas:
- Koje podatke koristiti za kreiranje nove pivot tabele?
- Gdje staviti stožer?
Budući da smo u prethodnom koraku već odabrali jednu od ćelija liste, cijela lista će biti automatski odabrana za kreiranje pivot tablice. Imajte na umu da možemo odabrati drugačiji raspon, drugu tablicu, pa čak i neki vanjski izvor podataka kao što je Access ili MS-SQL tabela baze podataka. Osim toga, moramo odabrati gdje ćemo postaviti novu stožernu tabelu: na novi list ili na jedan od postojećih. U ovom primjeru ćemo izabrati opciju – Novi radni list (na novi list):
Excel će kreirati novi list i na njega postaviti praznu zaokretnu tabelu:
Čim kliknemo na bilo koju ćeliju u zaokretnoj tabeli, pojavit će se drugi dijaloški okvir: Lista polja zaokretne tabele (Polja zaokretne tabele).
Lista polja na vrhu dijaloškog okvira je lista svih naslova sa originalne liste. Četiri prazna područja na dnu ekrana omogućavaju vam da zaokretnoj tabeli kažete kako želite da sumirate podatke. Sve dok su ove oblasti prazne, ni u tabeli nema ništa. Sve što treba da uradimo je da prevučemo naslove iz gornjeg dela u prazna područja ispod. Istovremeno se automatski generira pivot tablica, u skladu s našim uputama. Ako pogriješimo, možemo ukloniti naslove iz donjeg područja ili povući druge da ih zamijenimo.
oblast vrijednosti (Značenja) je vjerovatno najvažnije od četiri. Koji naslov se nalazi u ovoj oblasti određuje koji će podaci biti sumirani (zbir, prosek, maksimum, minimum, itd.) To su skoro uvek numeričke vrednosti. Odličan kandidat za mjesto u ovoj oblasti su podaci pod naslovom iznos (Cijena) našeg originalnog stola. Prevucite ovaj naslov na područje vrijednosti (Vrijednosti):
Imajte na umu da je naslov iznos je sada označena kvačicom iu tom području vrijednosti (Vrijednosti) pojavio se unos Zbroj iznosa (polje Iznos Iznos), što označava da je kolona iznos sažeo.
Ako pogledamo samu stožernu tabelu, videćemo zbir svih vrednosti iz kolone iznos originalni sto.
Dakle, naša prva stožerna tablica je kreirana! Zgodno, ali ne posebno impresivno. Vjerovatno želimo dobiti više informacija o našim podacima nego što ih trenutno imamo.
Okrenimo se originalnim podacima i pokušajmo identificirati jednu ili više kolona koje se mogu koristiti za podjelu ove sume. Na primjer, možemo formirati našu pivot tablicu na način da se ukupan iznos prodaje izračunava za svakog prodavca pojedinačno. One. redovi će biti dodati našoj pivot tabeli sa imenom svakog prodavca u kompaniji i njihovim ukupnim iznosom prodaje. Da biste postigli ovaj rezultat, samo prevucite naslov prodavac (prodajni predstavnik) u regiju Redne oznake (Strings):
Postaje zanimljivije! Naša zaokretna tabela počinje da se oblikuje…
Vidite prednosti? U nekoliko klikova kreirali smo tabelu za koju bi bilo potrebno jako puno vremena da se kreira ručno.
Šta drugo možemo učiniti? Pa, na neki način, naša stožerna tabela je spremna. Napravili smo koristan sažetak originalnih podataka. Važne informacije su već primljene! U ostatku ovog članka, pogledat ćemo neke načine za kreiranje složenijih zaokretnih tablica, kao i naučiti kako ih prilagoditi.
Podešavanje zaokretne tabele
Prvo, možemo kreirati dvodimenzionalnu stožernu tabelu. Uradimo to koristeći naslov kolone Način plaćanja (Način plaćanja). Samo prevucite naslov Način plaćanja na područje Oznake stupaca (kolone):
Dobijamo rezultat:
Izgleda veoma cool!
Sada napravimo trodimenzionalnu tabelu. Kako bi izgledao takav sto? da vidimo…
Povucite zaglavlje paket (kompleks) na područje filteri izvještaja (Filteri):
Obratite pažnju gde se nalazi…
Ovo nam daje priliku da filtriramo izvještaj na osnovu „Koji je kompleks za odmor plaćen“. Na primjer, možemo vidjeti raščlambu po prodavačima i načinima plaćanja za sve komplekse ili u par klikova mišem promijeniti prikaz pivot tablice i prikazati istu raščlambu samo za one koji su naručili kompleks Sunseekers.
Dakle, ako ovo dobro razumijete, onda se naša stožerna tablica može nazvati trodimenzionalnom. Nastavimo sa postavljanjem…
Ako se odjednom ispostavi da samo plaćanje čekom i kreditnom karticom (tj. bezgotovinsko plaćanje) treba da bude prikazano u pivot tabeli, onda možemo isključiti prikaz naslova gotovina (Gotovina). Za ovo, pored Oznake stupaca kliknite na strelicu nadole i poništite izbor u polju za potvrdu u padajućem meniju gotovina:
Hajde da vidimo kako sada izgleda naša stožerna tabela. Kao što vidite, kolona gotovina nestao iz nje.
Formatiranje zaokretnih tabela u Excel-u
Očigledno je da su zaokretne tabele veoma moćan alat, ali za sada rezultati izgledaju pomalo jednostavno i dosadno. Na primjer, brojevi koje sabiramo ne izgledaju kao iznosi u dolarima – oni su samo brojevi. Hajde da popravimo ovo.
Primamljivo je učiniti ono na što ste navikli u takvoj situaciji i jednostavno odabrati cijelu tabelu (ili cijeli list) i koristiti standardne tipke za formatiranje brojeva na traci s alatima za postavljanje željenog formata. Problem sa ovim pristupom je da ako ikada promenite strukturu zaokretne tabele u budućnosti (što se dešava sa šansom od 99%), formatiranje će biti izgubljeno. Ono što nam treba je način da to učinimo (skoro) trajnim.
Prvo, pronađimo unos Zbroj iznosa in vrijednosti (Vrijednosti) i kliknite na njega. U meniju koji se pojavi izaberite stavku Postavke polja vrijednosti (Opcije polja vrijednosti):
Pojavit će se okvir za dijalog Postavke polja vrijednosti (Opcije polja vrijednosti).
Pritisnite dugme Format broja (Format broja), otvoriće se dijaloški okvir. Formatiranje ćelija (format ćelije):
Sa liste kategorija (Formati brojeva) odaberite računovodstvo (Finansijski) i postavite broj decimalnih mjesta na nulu. Sada pritisnite nekoliko puta OKda se vratimo na našu stožernu tabelu.
Kao što vidite, brojevi su formatirani kao iznosi u dolarima.
Dok smo već kod toga s formatiranjem, postavimo format za cijelu zaokretnu tablicu. Postoji nekoliko načina da to učinite. Koristimo onaj koji je jednostavniji…
kliknite Alati za zaokretnu tablicu: Dizajn (Rad sa zaokretnim tabelama: konstruktor):
Zatim proširite meni klikom na strelicu u donjem desnom uglu sekcije Stilovi zaokretne tabele (Stilovi zaokretne tabele) da vidite opsežnu kolekciju inline stilova:
Odaberite bilo koji prikladan stil i pogledajte rezultat u svojoj pivot tablici:
Ostale postavke zaokretne tabele u programu Excel
Ponekad morate filtrirati podatke po datumima. Na primjer, na našoj listi zanata ima mnogo, mnogo datuma. Excel pruža alat za grupisanje podataka po danu, mjesecu, godini itd. Da vidimo kako se to radi.
Prvo uklonite unos. Način plaćanja iz regiona Oznake stupaca (Kolone). Da biste to uradili, prevucite ga nazad na listu naslova i na njegovo mesto pomerite naslov Date Booked (datum rezervacije):
Kao što vidite, ovo je privremeno učinilo našu stožernu tabelu beskorisnom. Excel je kreirao zasebnu kolonu za svaki datum na koji je izvršena trgovina. Kao rezultat, dobili smo veoma širok sto!
Da biste to popravili, kliknite desnom tipkom miša na bilo koji datum i odaberite iz kontekstnog izbornika GROUP (Grupa):
Pojavit će se dijaloški okvir za grupisanje. Mi biramo Mjeseci (Mjeseci) i kliknite OK:
Voila! Ova tabela je mnogo korisnija:
Inače, ova tabela je skoro identična onoj prikazanoj na početku članka, gdje su ukupni prodajni iznosi sastavljeni ručno.
Postoji još jedna veoma važna tačka koju morate znati! Možete kreirati ne jedan, već nekoliko nivoa naslova redova (ili kolona):
…i to će izgledati ovako…
Isto se može učiniti sa naslovima kolona (ili čak filterima).
Vratimo se originalnom obliku tabele i vidimo kako prikazati proseke umesto zbira.
Za početak kliknite na Zbroj iznosa i iz menija koji se pojavi izaberite Postavke polja vrijednosti (Opcije polja vrijednosti):
Lista Sažeti polje vrijednosti po (Operacija) u dijaloškom okviru Postavke polja vrijednosti (Opcije polja vrijednosti) odaberite Prosječne (prosjek):
U isto vrijeme, dok smo ovdje, promijenimo se CustomName (Prilagođeni naziv) sa Prosjek iznosa (polje Iznos Iznos) na nešto kraće. Unesite u ovo polje nešto poput Avg:
pritisnuti OK i vidi šta će se desiti. Imajte na umu da su se sve vrijednosti promijenile iz ukupnih u prosjeke, a zaglavlje tabele (u gornjoj lijevoj ćeliji) se promijenilo u Avg:
Ako želite, možete odmah dobiti iznos, prosjek i broj (prodaja) koji se nalaze u jednoj pivot tabeli.
Evo vodiča korak po korak kako to učiniti, počevši s praznom zaokretnom tablicom:
- Povucite zaglavlje prodavac (prodajni predstavnik) u regiju Oznake stupaca (Kolone).
- Povucite naslov tri puta iznos (Cijena) na područje vrijednosti (Vrijednosti).
- Za prvo polje iznos promijeni naslov u ukupan (Iznos), a format broja u ovom polju je računovodstvo (finansijski). Broj decimalnih mjesta je nula.
- Drugo polje iznos ime Prosjeke, postavite operaciju za to Prosječne (Prosjek) i format broja u ovom polju se također mijenjaju u računovodstvo (Finansijski) sa nula decimalnih mjesta.
- Za treće polje iznos postavite naslov računati i operacija za njega - računati (Količina)
- U Oznake stupaca (Kolone) polje automatski kreirano Σ Vrijednosti (Σ vrijednosti) – prevucite ga na područje Redne oznake (Linije)
Evo šta ćemo završiti:
Ukupan iznos, prosječna vrijednost i broj prodaja – sve u jednoj pivot tabeli!
zaključak
Pivot tabele u Microsoft Excel-u sadrže mnogo funkcija i postavki. U tako malom članku, nisu ni blizu da ih sve pokriju. Bila bi potrebna mala knjiga ili velika web stranica da bi se u potpunosti opisali sve mogućnosti pivot tablica. Odvažni i radoznali čitaoci mogu nastaviti sa istraživanjem zaokretnih tabela. Da biste to učinili, samo kliknite desnom tipkom miša na gotovo bilo koji element zaokretne tablice i pogledajte koje se funkcije i postavke otvaraju. Na traci ćete pronaći dvije kartice: Alati za zaokretnu tabelu: Opcije (analiza) i dizajn (Konstruktor). Nemojte se plašiti da pogrešite, uvek možete da izbrišete zaokretnu tabelu i počnete ispočetka. Imate priliku koju dugogodišnji korisnici DOS-a i Lotusa 1-2-3 nikada nisu imali.