Pivot tablice su jedna od najmoćnijih značajki programa Microsoft Excel. Omogućuju analizu velikih količina podataka i sažetak u samo nekoliko klikova mišem. U ovom članku istražujemo tablice za ponašanje, razumijemo što jesu i saznali kako ih izraditi i prilagoditi.
Napomena: Ovaj članak je napisan koristeći Excel 2010 (Beta). Koncept tablice zaokreta malo se promijenio tijekom godina, ali način izrade se promijenio u gotovo svakoj iteraciji Excela. Ako koristite verziju programa Excel koja nije 2010, očekujte različite zaslone od onih koje vidite u ovom članku.
Mala povijest
U prvim danima programa za proračunske tablice, Lotus 1-2-3 vladao je u hodniku. Njegova dominacija bila je tako cjelovita da su ljudi mislili da je to gubljenje vremena da bi se Microsoft trudio razviti vlastiti softver za proračunske tablice (Excel) kako bi se natjecao s Lotusom. Bljesak unaprijed do 2010., a Excelova dominacija tržišta proračunskih tablica veća je od Lotusovog, a broj korisnika koji još uvijek izvodi Lotus 1-2-3 približava se nuli. Kako se to dogodilo? Što je izazvalo tako dramatičan preokret sudbine?
Analitičari industrije to spuštaju na dva faktora: Prvo, Lotus je zaključio da je ova nova GUI platforma nazvana "Windows" bila prolazna moda koja nikad ne bi oduzela. Odbili su stvoriti Windows verziju Lotus 1-2-3 (za nekoliko godina, ionako), predviđajući da je njihova DOS verzija softvera bilo tko tko bi ikada trebao. Microsoft, naravno, razvio Excel isključivo za Windows. Drugo, Microsoft je razvio značajku za Excel koji Lotus nije osigurao u 1-2-3 StožerneTablice, Značajka zaokretne tablice, isključivo za Excel, smatrana je tako nevjerojatno korisnom da su ljudi voljni naučiti cijeli novi softverski paket (Excel), a ne držati se programa (1-2-3) koji ga nije imao. Ova jedna značajka, zajedno s pogrešnom procjenom uspjeha sustava Windows, bila je smrtonosna linija za Lotus 1-2-3 i početak uspjeha programa Microsoft Excel.
Razumijevanje tabličnih tablica
Pa što je to točno?
Jednostavno rečeno, PivotTable je sažetak podataka koji su stvoreni kako bi omogućili jednostavnu analizu navedenih podataka. No, za razliku od ručno izrađenog sažetka, Excel PivotTables interaktivno. Kada ga izradite, možete ga jednostavno promijeniti ako ne pruža točne uvide u podatke za koje ste se nadali. U nekoliko klikova sažetak se može "okretati" - rotirati na takav način da naslovi stupaca postanu naslova redaka i obrnuto. Ima još puno toga što se može učiniti. Umjesto da pokušamo opisati sve značajke Pivot Tabela, jednostavno ćemo ih pokazati ...
Podaci koje analizirate pomoću tablice za podešavanje ne mogu biti pravedni bilo koji podaci - to mora biti sirov podaci, prethodno neobrađeni (unsummarized) - obično popis nekih vrsta. Primjer toga može biti popis prodajnih transakcija u tvrtki tijekom proteklih šest mjeseci.
Pregledajte podatke prikazane u nastavku:
Primjetite da je to ne neobrađeni podatci. Zapravo, to je već nekakav sažetak. U ćeliji B3 možemo vidjeti 30.000 dolara, što je očito ukupna prodaja Jamesa Cooka za mjesec siječanj. Dakle, gdje su sirovi podaci? Kako smo došli do brojke od 30.000 dolara? Gdje je originalni popis prodajnih transakcija da je ta brojka generirana? Jasno je da negdje, netko je morao otići u nevolju uspoređivati sve transakcije prodaje tijekom proteklih šest mjeseci u sažetak koji smo vidjeli gore. Koliko dugo mislite da je to bilo? Sat? Deset?
Najvjerojatnije, da. Vidiš, gornja proračunska tablica zapravo jest ne tablicu za podešavanje. Stvorena je ručno iz neobrađenih podataka pohranjenih na drugom mjestu, pa je zaista potrebno nekoliko sati za sastavljanje. Međutim, to je upravo takav sažetak mogla biti izrađen pomoću PivotTables, u kojem slučaju to bi trebalo samo nekoliko sekundi. Otkrijmo kako ...
Ako bismo pronašli izvorni popis prodajnih transakcija, možda će izgledati ovako:
Možda ćete se iznenaditi kada saznate da pomoću značajke Značajka za podešavanje Excela možemo izraditi mjesečni mjesečni sažetak prodaje za nekoliko sekundi, uz samo nekoliko klikova mišem. Možemo to učiniti - i puno više!
Kako stvoriti tablicu za zaokret
Prvo, provjerite imate li nekih neobrađenih podataka u radnom listu u programu Excel. Popis financijskih transakcija je tipično, ali može biti popis samo o bilo čemu: pojedinosti o kontaktima zaposlenika, vašoj kolekciji CD-a ili broju potrošnje goriva za flotu automobila vaše tvrtke.
Stoga pokrećemo Excel ... i učitamo takav popis ...
Kada otvorimo popis u programu Excel, spremni smo za početak izrade tablice za podešavanje.
Kliknite bilo koju pojedinačnu ćeliju unutar popisa:
Zatim, iz Umetnuti Kliknite karticu zaokretne tablice ikona:
Izradite tablicu za poniĹĄtavanje prikazuje se dva pitanja: Koji bi se podaci trebali temeljiti na vašem novom pivot tablici i gdje bi se trebalo stvoriti? Budući da smo već kliknuli na ćeliju unutar popisa (u gornjem koraku), cijeli popis koji okružuje tu ćeliju već je odabran za nas ($ A $ 1: $ G $ 88 na Plaćanja list, u ovom primjeru). Imajte na umu da bismo mogli odabrati popis u bilo kojem drugom području bilo kojeg drugog radnog lista, ili čak i neki vanjski izvor podataka, kao što je tablica Access baza podataka ili čak tablica baze podataka MS-SQL Server. Također trebamo odabrati želimo li da se novo podešavanje tablice stvori na a novi radni list, ili na postojanje jedan. U ovom ćemo primjeru odabrati a novi jedan:
Novi radni list izrađen je za nas, a na tom se radnom listu stvara prazna tablična tablica:
Čini se i još jedan okvir: Popis polja za podešavanje tablice, Ovaj popis polja bit će prikazan svaki put kada kliknemo bilo koju ćeliju unutar tablice za podešavanje (gore):
Popis polja u gornjem dijelu okvira zapravo je zbirka zaglavlja stupaca iz izvornog radnog lista izvornih podataka. Četiri prazna polja u donjem dijelu zaslona omogućuju nam da odaberemo način na koji bismo željeli da naš PivotTable sažimati neobrađene podatke. Do sada, u tim kutijama nema ničega, pa je PivotTable prazno. Sve što trebamo učiniti je povlačiti polja s popisa iznad i ispustiti ih u donje okvire. Zatim se automatski oblikuje tablica za podešavanje kako bi odgovarala našim uputama. Ako pogrešno shvatimo, moramo samo povući polja natrag odakle su došli i / ili povucite novi polja dolje kako bi ih zamijenili.
vrijednosti okvir je vjerojatno najvažniji od četiri. Polje koje se povlači u ovaj okvir predstavlja podatke koji moraju biti sažeti na neki način (zbrajanjem, prosjekom, pronalaženjem maksimalnog, minimalnog, itd.). To je gotovo uvijek numerički podaci. Savršeni kandidat za ovaj okvir u našim uzorcima podataka je polje "stupac" / stupac. Povucimo to polje u vrijednosti kutija:
Primijetite da je (a) polje "Količina" u popisu polja sada označeno i da je "zbroj iznosa" dodan u vrijednosti okvira, što znači da je zbrojni iznos stupca zbrajao.
Ako ispitamo sam PivotTable, doista pronađemo zbroj svih vrijednosti "Iznos" iz radnog lista "sirovih podataka":
Izradili smo prvu tablicu za zaokretanje! Zgodan, ali ne osobito impresivan. Vjerojatno nam je potrebno malo više uvida u naše podatke.
Pozivajući se na naše podatke o uzorku, moramo utvrditi jedno ili više zaglavlja stupaca koje smo mogli zamisliti za podjelu tog ukupnog broja. Na primjer, možemo odlučiti da bismo željeli vidjeti sažetak naših podataka gdje imamo a redak naslova za svakog od različitih prodavača u našoj tvrtki, i ukupno za svaku. Da bismo to postigli, sve što trebamo učiniti je da povučemo polje "prodavač" u Oznake redaka kutija:
Sadakonačno, stvari počinju zanimati! Naš se kuglični tabulator počinje oblikovati ....
S nekoliko klikova stvorili smo tablicu koja bi trebala dugo raditi ručno.
Pa što još možemo učiniti? Pa, u jednom smislu naš je PivotTable potpun. Napravili smo korisni sažetak naših izvornih podataka. Važne stvari već su naučili! Za ostatak članka istražit ćemo neke načine na koje se mogu izraditi složenije tablice za podešavanje i načine na koje se te Pivot tablice mogu prilagoditi.
Prvo, možemo stvoriti a dva-dimenzionalni stol. Učinimo to pomoću "Način plaćanja" kao zaglavlja stupca. Jednostavno povucite naslov "Način plaćanja" u izbornik Oznake stupaca kutija:
Što ovako izgleda:
Počinju dobiti vrlo cool!
Učinimo to a tri-dimenzionalni stol. Što bi takva tablica mogla izgledati? Pogledajmo ...
Povucite stupac / naslov "Paket" na Filtar izvješća kutija:
Primjetite gdje završava ....
To nam omogućuje filtriranje našeg izvješća na temelju kojeg se kupuje "paket za odmor". Na primjer, možemo vidjeti raspodjelu prodavača vs način plaćanja za svi paketi ili, s nekoliko klikova, promijenite ga kako biste prikazali isti slom za paket "Sunseekers":
I tako, ako mislite o tome na pravi način, naš je PivotTable sada trodimenzionalan. Nastavimo prilagođavati ...
Ako se ispostavi, recimo, samo želimo vidjeti ček i kreditnu karticu transakcija (tj. nema gotovinskih transakcija), možemo poništiti odabir stavke "Novac" iz zaglavlja stupaca. Kliknite strelicu na padajućem izborniku pored Oznake stupaca, i odstraniti "Novac":
Pogledajmo kako to izgleda ... Kao što možete vidjeti, "Cash" je nestalo.
oblikovanje
Ovo je očito vrlo moćan sustav, ali do sada rezultati izgledaju vrlo jasno i dosadno. Za početak, brojevi koje zbrajamo ne izgledaju kao količine dolara - samo obični stari brojevi. Ispravimo to.
Iskušenje može biti ono što smo navikli raditi u takvim okolnostima i jednostavno odabrati cijeli stol (ili cijeli radni list) i upotrijebiti gumbe za oblikovanje standardnog broja na alatnoj traci da biste dovršili oblikovanje. Problem s tim pristupom je da ako ikada budete mijenjati strukturu tablice (to je vjerojatno 99%), tada će ti formati brojeva biti izgubljeni. Trebamo način koji će ih (polu-) trajati.
Prvo pronađemo unos "zbroja iznosa" u izborniku vrijednosti i kliknite na njega. Pojavit će se izbornik. Odabrali smo Vrijednosti polja vrijednosti ... iz izbornika:
Vrijednosti polja vrijednosti pojavljuje se okvir.
Kliknite gumb Format broja gumb i standard Kutija Formati ćelija pojavljuje:
Od Kategorija popis, odaberite (recite) Računovodstvo, a broj decimalnih mjesta smanjiti na 0. Kliknite u redu nekoliko puta da biste se vratili na tablicu zaokreta ...
Kao što vidite, brojevi su ispravno oblikovani kao iznosi dolara.
Dok smo u pitanju oblikovanja, formatirajte cijeli tabulator zaokreta. Postoji nekoliko načina za to. Koristimo jednostavnu ...
Kliknite gumb Alati za podešavanje tablice / dizajn kartica:
Zatim spustite strelicu u donjem desnom kutu gumba Stilovi za podešavanje tablice popis da biste vidjeli ogromnu zbirku ugrađenih stilova:
Odaberite bilo koji koji se žali i pregledajte rezultat u svom PivotTableu:
Druge opcije
Možemo raditi i sa datumima. Sada obično postoji mnogo, mnogo datuma na popisu transakcija kao što je onaj s kojim smo započeli. Excel, međutim, pruža mogućnost grupiranja podataka zajedno po danu, tjednu, mjesecu, godini itd. Pogledajmo kako je to učinjeno.
Prvo uklonimo stupac "Način plaćanja" iz Oznake stupaca (jednostavno ga povucite natrag do popisa polja) i zamijenite ga stupcem "Datum bilježenja":
Kao što vidite, to čini našu tablicu za poništavanje odmah beskorisnim, dajući nam jedan stupac za svaki datum na kojem se dogodila transakcija - vrlo široka tablica!
Da biste to ispravili, desnom tipkom miša kliknite bilo koji datum i odaberite Skupina… iz kontekstnog izbornika:
Pojavit će se okvir za grupiranje. Odabrali smo mjeseci i kliknite U redu:
Voila! mnogo korisnija tablica:
(Usput, ova je tablica praktički identična onoj prikazanom na početku ovog članka - originalni sažetak prodaje koji je izrađen ručno.)
Još jedna cool stvar koju morate znati je da možete imati više od jednog niza naslova retka (ili naslove stupaca):
... koja izgleda ovako ....
Možete napraviti sličnu stvar sa naslovima stupaca (ili čak filtrima izvješća).
Zadržavanje stvari opet jednostavno, vidimo kako iscrtati prosječno vrijednosti, umjesto zbrojenih vrijednosti.
Najprije kliknite "zbroj iznosa" i odaberite Vrijednosti polja vrijednosti ... iz kontekstnog izbornika koji se pojavljuje:
U Sažmi vrijednost polja vrijednosti po popis u Vrijednosti polja vrijednosti , odaberite prosječan:
Dok smo ovdje, promijenimo Prilagođeno ime, od "prosječnog iznosa" do nečega nešto jezgrovitog. Upišite nešto poput "Prosj.":
Klik u redu, i vidjeti kako izgleda. Napominjemo da se sve vrijednosti mijenjaju od zbrojenih iznosa do prosjeka, a naslov tablice (gornja lijeva ćelija) promijenjen je u "Prosjek":
Ako vam se sviđa, možemo čak imati iznose, prosjeke i broje (broji = koliko je prodaja bilo) sve na istoj tabeli za podešavanje!
Evo koraka kako biste dobili nešto slično na tom mjestu (počevši od praznog tabličnog okvira):
polje iz Oznake stupaca do Oznake redakaEvo što završimo:
Ukupni, prosječni i računaju se na isti pivot tablici!
Zaključak
Postoji mnogo, mnogo više mogućnosti i opcija za Pivot tablice koje je izradio Microsoft Excel - previše za popis u ovakvom članku. Da bi se u potpunosti pokrio potencijal Pivot Stolova, potrebna bi se mala knjiga (ili velika web stranica). Hrabri i / ili geeki čitatelji mogu vrlo jednostavno proučiti PivotTables: Jednostavno kliknite desnom tipkom miša na gotovo sve i vidjeti koje su vam opcije dostupne. Tu su i dvije vrpce: Alati za podešavanje tablice / Opcije i Dizajn, Nije bitno ako pogriješite - lako je izbrisati tablicu za podešavanje i započeti ponovno - mogućnost da stari DOS korisnici Lotus 1-2-3 nikada nisu imali.
Ako radite u sustavu Office 2007, preporučujemo da pogledate članak o tome kako izraditi tablicu za zakretanje u programu Excel 2007.
Uključili smo Excel radnu knjigu koju možete preuzeti kako biste vježbali vještine za podešavanje tablice. Trebalo bi raditi sa svim inačicama programa Excel od 97 nadalje.
Preuzmite naš Excel radnu knjigu iz prakse
