Sadržaj
Zakretni stolovi je jedan od najmoćnijih alata u Excelu. Omogućuju vam analizu i zbrajanje različitih sažetaka velikih količina podataka uz samo nekoliko klikova mišem. U ovom članku ćemo se upoznati sa zaokretnim tablicama, razumjeti što su, naučiti kako ih izraditi i prilagoditi.
Ovaj je članak napisan pomoću programa Excel 2010. Koncept zaokretnih tablica nije se mnogo promijenio tijekom godina, ali način na koji ih stvarate malo je drugačiji u svakoj novoj verziji programa Excel. Ako imate verziju programa Excel koja nije 2010, budite spremni da će se snimke zaslona u ovom članku razlikovati od onoga što vidite na zaslonu.
Malo povijesti
U ranim danima softvera za proračunske tablice, lopta pravila Lotus 1-2-3. Njegova je dominacija bila toliko potpuna da su se Microsoftovi napori da razvije vlastiti softver (Excel) kao alternativu Lotusu činili kao gubitak vremena. Sada brzo naprijed u 2010! Excel dominira proračunskim tablicama više nego što je to Lotusov kod ikada učinio u svojoj povijesti, a broj ljudi koji još uvijek koriste Lotus blizu je nule. Kako se ovo moglo dogoditi? Što je bio razlog tako dramatičnog raspleta događaja?
Analitičari identificiraju dva glavna čimbenika:
- Prvo, Lotus je odlučio da je ova nova GUI platforma nazvana Windows samo prolazna moda koja neće dugo trajati. Odbili su izraditi 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či 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 tablice, ekskluzivne za Excel, pokazale su se toliko nevjerojatno korisnim da su se ljudi radije držali novog softverskog paketa Excel nego da nastave s Lotusom 1-2-3, koji ih nije imao.
Zaokretne tablice, uz podcjenjivanje uspjeha Windowsa općenito, odigrale su smrtni marš za Lotus 1-2-3 i dovele do uspjeha Microsoft Excela.
Što su zaokretne tablice?
Dakle, koji je najbolji način da se okarakterizira što su zaokretne tablice?
Jednostavno rečeno, zaokretne tablice su sažeci nekih podataka, stvoreni kako bi se olakšala analiza tih podataka. Za razliku od ručno stvorenih ukupnih iznosa, zaokretne tablice programa Excel interaktivne su. Jednom kada ih izradite, možete ih jednostavno izmijeniti ako ne daju sliku kojoj ste se nadali. Sa samo nekoliko klikova mišem, ukupni zbrojevi se mogu okrenuti tako da naslovi stupaca postanu naslovi redaka i obrnuto. Možete učiniti mnogo stvari sa zaokretnim tablicama. Umjesto da pokušavate riječima opisati sve značajke pivot tablica, lakše je to pokazati u praksi...
Podaci koje analizirate pomoću zaokretnih tablica ne mogu biti nasumični. To bi trebali biti sirovi neobrađeni podaci, poput neke vrste popisa. Na primjer, to može biti popis prodaje tvrtke u posljednjih šest mjeseci.
Pogledajte podatke prikazane na slici ispod:
Imajte na umu da ovo nisu sirovi neobrađeni podaci, jer su već sažeti. U ćeliji B3 vidimo 30000 30000 dolara, što je vjerojatno ukupni rezultat koji je James Cook napravio u siječnju. Gdje su onda izvorni podaci? Odakle cifra od XNUMX XNUMX dolara? Gdje je izvorni popis prodaje iz kojeg je izveden ovaj mjesečni ukupni iznos? Jasno je da je netko napravio odličan posao organiziranja i sortiranja svih podataka o prodaji za zadnjih šest mjeseci i pretvaranja u tablicu ukupnih iznosa koju vidimo. Što mislite koliko je trajalo? Sat? Deset sati?
Činjenica je da gornja tablica nije stožerna tablica. Ručno je izrađen od neobrađenih podataka pohranjenih negdje drugdje i za obradu je bilo potrebno najmanje nekoliko sati. Upravo takvu zbirnu tablicu moguće je izraditi pomoću pivot tablica u samo nekoliko sekundi. Shvatimo kako...
Ako se vratimo na izvorni prodajni popis, izgledat će otprilike ovako:
Možda ćete se iznenaditi da iz ovog popisa obrta uz pomoć pivot tablica iu samo nekoliko sekundi možemo napraviti mjesečno izvješće o prodaji u Excelu, koje smo analizirali gore. Da, možemo to i više!
Kako napraviti stožernu tablicu?
Prvo provjerite imate li neke izvorne podatke u Excel tablici. Popis financijskih transakcija najtipičniji je koji se događa. Zapravo, to može biti popis bilo čega: podaci za kontakt zaposlenika, kolekcija CD-a ili podaci o potrošnji goriva vaše tvrtke.
Dakle, pokrećemo Excel ... i učitavamo takav popis ...
Nakon što smo otvorili ovaj popis u Excelu, možemo krenuti s izradom pivot tablice.
Odaberite bilo koju ćeliju s ovog popisa:
Zatim na tab Umetanje (Umetni) odabir naredbe Zaokretna tablica (Zaokretna tablica):
Pojavit će se dijaloški okvir Stvorite zaokretnu tablicu (Izrada zaokretne tablice) s dva pitanja za vas:
- Koje podatke koristiti za izradu nove zaokretne tablice?
- Gdje postaviti stožernu tablicu?
Budući da smo u prethodnom koraku već odabrali jednu od ćelija popisa, cijeli će popis biti automatski odabran za izradu zaokretne 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 tablica baze podataka. Osim toga, moramo odabrati gdje ćemo postaviti novu pivot tablicu: na novi list ili na neki od postojećih. U ovom primjeru odabrat ćemo opciju – Novi radni list (na novi list):
Excel će stvoriti novi list i na njega postaviti praznu zaokretnu tablicu:
Čim kliknemo na bilo koju ćeliju u zaokretnoj tablici, pojavit će se drugi dijaloški okvir: Popis polja zaokretne tablice (Polja zaokretne tablice).
Popis polja na vrhu dijaloškog okvira je popis svih naslova s izvornog popisa. Četiri prazna područja na dnu zaslona omogućuju vam da zaokretnoj tablici kažete kako želite sažeti podatke. Sve dok su ta područja prazna, ni u tablici nema ničega. Sve što trebamo učiniti je povući naslove iz gornjeg područja u prazna područja ispod. Istodobno 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.
Površina Vrijednosti (Značenja) vjerojatno je najvažnije od četiri. Naslov koji se nalazi u ovom području određuje koji će podaci biti sažeti (zbroj, prosjek, maksimum, minimum itd.). To su gotovo uvijek numeričke vrijednosti. Odličan kandidat za mjesto u ovoj oblasti je podatak pod naslovom Iznos (Cijena) naše originalne tablice. Povucite ovaj naslov na područje Vrijednosti (vrijednosti):
Imajte na umu da naslov Iznos sada je označeno kvačicom, au području Vrijednosti (Vrijednosti) pojavio se unos Zbroj iznosa (Amount polje Amount), što ukazuje na to da stupac Iznos sažeo.
Ako pogledamo samu pivot tablicu, vidjet ćemo zbroj svih vrijednosti iz stupca Iznos izvorni stol.
Dakle, naša prva pivot tablica je stvorena! Zgodno, ali ne osobito impresivno. Vjerojatno želimo dobiti više informacija o našim podacima nego što trenutno imamo.
Okrenimo se izvornim podacima i pokušajmo identificirati jedan ili više stupaca koji se mogu koristiti za dijeljenje ovog zbroja. Na primjer, našu stožernu tablicu možemo formirati na način da se ukupan iznos prodaje izračuna za svakog prodavača pojedinačno. Oni. retci će biti dodani u našu zaokretnu tablicu s imenom svakog prodavača u tvrtki i njihovim ukupnim iznosom prodaje. Da biste postigli ovaj rezultat, samo povucite naslov prodavač (prodajni predstavnik) u regiju Oznake redaka (žice):
Postaje zanimljivije! Naša zaokretna tablica počinje poprimati oblik...
Vidite prednosti? U par klikova napravili smo tablicu za čiju bi ručnu izradu trebalo jako puno vremena.
Što još možemo učiniti? Pa, u određenom smislu, naša stožerna tablica je spremna. Napravili smo koristan sažetak izvornih podataka. Važne informacije već su primljene! U ostatku ovog članka pogledat ćemo neke načine za stvaranje složenijih zaokretnih tablica, kao i naučiti kako ih prilagoditi.
Postavljanje zaokretne tablice
Prvo, možemo stvoriti dvodimenzionalnu stožernu tablicu. Učinimo to pomoću zaglavlja stupca Metoda plaćanja (Način plaćanja). Samo povucite naslov Metoda plaćanja na područje Oznake stupaca (Stupci):
Dobijamo rezultat:
Izgleda jako cool!
Sada napravimo trodimenzionalni stol. Kako bi izgledao takav stol? Da vidimo…
Povucite zaglavlje Paket (Kompleks) na područje filtri izvješća (Filtri):
Zabilježite gdje je…
To nam daje priliku filtrirati izvješće na temelju "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 Tragači sunca.
Dakle, ako ovo ispravno razumijete, onda se naša stožerna tablica može nazvati trodimenzionalnom. Nastavimo s postavljanjem…
Ako se odjednom pokaže da u pivot tablici treba biti prikazano samo plaćanje čekom i kreditnom karticom (odnosno bezgotovinsko plaćanje), tada možemo isključiti prikaz naslova Unovčiti (Unovčiti). Za ovo, pored Oznake stupaca kliknite strelicu prema dolje i poništite okvir u padajućem izborniku Unovčiti:
Pogledajmo kako sada izgleda naša pivot tablica. Kao što vidite, stupac Unovčiti nestao s nje.
Oblikovanje zaokretnih tablica u Excelu
Zaokretne tablice očito su vrlo moćan alat, ali za sada rezultati izgledaju pomalo jednostavno i dosadno. Na primjer, brojevi koje zbrajamo ne izgledaju kao iznosi u dolarima – to su samo brojevi. Popravimo ovo.
Primamljivo je učiniti ono na što ste navikli u takvoj situaciji i jednostavno odabrati cijelu tablicu (ili cijeli list) i koristiti standardne gumbe za formatiranje brojeva na alatnoj traci za postavljanje željenog formata. Problem s ovim pristupom je taj što ako ikada promijenite strukturu zaokretne tablice u budućnosti (što se događa s 99% vjerojatnošću), oblikovanje će biti izgubljeno. Ono što nam treba je način da to učinimo (gotovo) trajnim.
Prvo, pronađimo unos Zbroj iznosa in Vrijednosti (Vrijednosti) i kliknite na njega. U izborniku koji se pojavi odaberite stavku Postavke polja vrijednosti (Opcije polja vrijednosti):
Pojavit će se dijaloški okvir Postavke polja vrijednosti (Opcije polja vrijednosti).
Pritisnite gumb Format broja (Format broja), otvorit će se dijaloški okvir. Format Cells (format ćelije):
S popisa Kategorija (Formati brojeva) odaberite računovodstvo (Financijski) i postavite broj decimalnih mjesta na nulu. Sada pritisnite nekoliko puta OKda se vratimo na našu stožernu tablicu.
Kao što vidite, brojevi su oblikovani kao iznosi u dolarima.
Dok smo već kod oblikovanja, postavimo format za cijelu zaokretnu tablicu. Postoji nekoliko načina za to. Koristimo onu koja je jednostavnija…
kliknite Alati zaokretne tablice: dizajn (Rad sa zaokretnim tablicama: konstruktor):
Zatim proširite izbornik klikom na strelicu u donjem desnom kutu odjeljka Stilovi zaokretne tablice (Stilovi zaokretne tablice) da vidite opsežnu zbirku ugrađenih stilova:
Odaberite bilo koji prikladan stil i pogledajte rezultat u svojoj zaokretnoj tablici:
Ostale postavke zaokretne tablice u Excelu
Ponekad je potrebno filtrirati podatke prema datumima. Na primjer, na našem popisu obrta postoji mnogo, mnogo datuma. Excel nudi alat za grupiranje podataka po danu, mjesecu, godini i tako dalje. Da vidimo kako se to radi.
Prvo uklonite unos. Metoda plaćanja iz regije Oznake stupaca (Stupci). Da biste to učinili, povucite ga natrag na popis naslova i na njegovo mjesto premjestite naslov Datum rezerviranja (datum rezervacije):
Kao što vidite, ovo je privremeno učinilo našu zaokretnu tablicu beskorisnom. Excel je stvorio zaseban stupac za svaki datum na koji je napravljena trgovina. Kao rezultat toga, dobili smo vrlo širok stol!
Da biste to popravili, desnom tipkom miša kliknite bilo koji datum i odaberite iz kontekstnog izbornika GRUPA (Skupina):
Pojavit će se dijaloški okvir za grupiranje. Mi biramo Mjeseci (Mjeseci) i kliknite OK:
Voila! Ova tablica je mnogo korisnija:
Usput, ova tablica je gotovo identična onoj prikazanoj na početku članka, gdje su ukupni iznosi prodaje sastavljeni ručno.
Postoji još jedna vrlo važna stvar koju morate znati! Možete stvoriti ne jednu, već nekoliko razina zaglavlja redaka (ili stupaca):
...i izgledat će ovako...
Isto se može učiniti s naslovima stupaca (ili čak filtrima).
Vratimo se na izvorni oblik tablice i vidimo kako prikazati prosjeke umjesto zbrojeva.
Za početak kliknite na Zbroj iznosa i iz izbornika koji se pojavi odaberite Postavke polja vrijednosti (Opcije polja vrijednosti):
Popis Sažmi polje vrijednosti prema (Operacija) u dijaloškom okviru Postavke polja vrijednosti (Opcije polja vrijednosti) odaberite prosjek (prosjek):
Istovremeno, dok smo tu, promijenimo se CustomName (Prilagođeno ime) sa Prosjek iznosa (Polje Iznos Iznos) na nešto kraće. Unesite u ovo polje nešto poput Prosječni:
tisak OK i vidjeti što će se dogoditi. Imajte na umu da su se sve vrijednosti promijenile iz ukupnih u prosječne, a zaglavlje tablice (u gornjoj lijevoj ćeliji) promijenilo se u Prosječni:
Ako želite, možete odmah dobiti iznos, prosjek i broj (prodaje) smješten u jednu pivot tablicu.
Evo korak po korak vodiča kako to učiniti, počevši s praznom zaokretnom tablicom:
- Povucite zaglavlje prodavač (prodajni predstavnik) u regiju Oznake stupaca (Stupci).
- Povucite naslov tri puta Iznos (Cijena) prema području Vrijednosti (Vrijednosti).
- Za prvo polje Iznos promijeniti naslov u ukupno (Iznos), a format broja u ovom polju je računovodstvo (Financijski). Broj decimalnih mjesta je nula.
- Drugo polje Iznos ime Prosječnoe, postavite operaciju za to prosjek (Prosjek) i format broja u ovom polju također se mijenjaju u računovodstvo (Financijski) s nula decimalnih mjesta.
- Za treće polje Iznos postaviti naslov Računati i operacija za njega – Računati (Količina)
- u Oznake stupaca Polje (Stupci) automatski se stvara Σ Vrijednosti (Σ vrijednosti) – povucite ga na područje Oznake redaka (Linije)
Evo što ćemo dobiti na kraju:
Ukupan iznos, prosječna vrijednost i broj prodaja – sve u jednoj pivot tablici!
Zaključak
Zaokretne tablice u programu Microsoft Excel sadrže puno značajki 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 opisale sve mogućnosti pivot tablica. Odvažni i radoznali čitatelji mogu nastaviti s istraživanjem zaokretnih tablica. Da biste to učinili, desnom tipkom miša kliknite gotovo bilo koji element zaokretne tablice i pogledajte koje se funkcije i postavke otvaraju. Na vrpci ćete pronaći dvije kartice: Alati zaokretne tablice: Opcije (analiza) i dizajn (Konstruktor). Nemojte se bojati pogriješiti, zaokretnu tablicu uvijek možete izbrisati i krenuti ispočetka. Imate priliku koju dugogodišnji korisnici DOS-a i Lotusa 1-2-3 nikada nisu imali.