Rad s pivot tablicama u programu Microsoft Excel

Stožerne tablice jedna su od najmoćnijih značajki programa Microsoft Excel. Oni omogućuju analizu velikih količina podataka i sažimanje u samo nekoliko klikova mišem. U ovom članku istražujemo stožerne tablice, razumijemo što su i naučimo kako ih stvoriti i prilagoditi.

Napomena: Ovaj je članak napisan pomoću programa Excel 2010 (Beta). Koncept stožerne tablice se tijekom godina malo promijenio, ali se metoda stvaranja promijenila 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 ranim danima programa za proračunske tablice, Lotus 1-2-3 je vladao skloništem. Njegova dominacija bila je toliko potpuna da su ljudi mislili da je gubitak vremena za Microsoft da se trudi razviti vlastiti softver za proračunske tablice (Excel) kako bi se natjecali s Lotusom. Flash-naprijed do 2010, i Excelova dominacija na tržištu proračunskih tablica veća je nego što je Lotus ikada bio, dok se broj korisnika koji još uvijek vode Lotus 1-2-3 približava nuli. Kako se to dogodilo? Što je izazvalo tako dramatičan preokret sudbine?

Industrijski analitičari su se usredotočili na dva čimbenika: Prvo, Lotus je odlučio da je ova elegantna nova GUI platforma nazvana “Windows” prolazna moda koja nikada ne bi uzletjela. Oni su odbili stvoriti verziju sustava Lotus 1-2-3 za Windows (barem nekoliko godina), predviđajući da će njihova DOS verzija softvera biti sve što će ikada trebati. Microsoft je, naravno, razvio Excel isključivo za Windows. Drugo, Microsoft je razvio značajku za Excel koju Lotus nije pružio u 1-2-3, naime StožerneTablice. Značajka PivotTables, ekskluzivno za Excel, smatrana je tako zapanjujuće korisnom da su ljudi bili voljni učiti cijeli novi softverski paket (Excel) umjesto da se drže programa (1-2-3) koji ga nisu imali. Ova jedna značajka, zajedno s pogrešnom procjenom uspjeha sustava Windows, bila je zvijer za Lotus 1-2-3 i početak uspjeha programa Microsoft Excel.

Razumijevanje stožernih tablica

Dakle, što je točno izvedena tablica?

Jednostavno rečeno, zaokretna tablica je sažetak nekih podataka, stvorenih da omoguće jednostavnu analizu navedenih podataka. Ali za razliku od ručno izrađenog sažetka, Excel PivotTable su interaktivne. Jednom kada ga napravite, možete ga jednostavno promijeniti ako ne nudi točne uvide u vaše podatke za koje ste se nadali. U nekoliko klikova sažetak se može "zakretati" - rotirati na takav način da zaglavlja stupaca postaju zaglavlja retka, i obrnuto. Postoji još mnogo toga što se može učiniti. Umjesto da pokušamo opisati sve značajke zaokretnih tablica, jednostavno ćemo ih demonstrirati ...

Podaci koje analizirate pomoću izvedene tablice ne mogu biti pravedni bilo koji podaci - to mora biti sirov podaci, prethodno neobrađeni (summarized) - obično neki popis. Primjer za to može biti popis prodajnih transakcija u tvrtki u posljednjih šest mjeseci.

Pregledajte niže prikazane podatke:

Primijetite da je ovo ne neobrađeni podatci. Zapravo, to je već neki sažetak. U ćeliji B3 možemo vidjeti 30.000 dolara, što je očito ukupna prodaja Jamesa Cooka za mjesec siječanj. Gdje su sirovi podaci? Kako smo došli do brojke od 30.000 dolara? Gdje je izvorni popis prodajnih transakcija iz kojeg je ta brojka generirana? Jasno je da je negdje netko morao pokušati usporediti sve prodajne transakcije u posljednjih šest mjeseci sa sažetkom koji smo vidjeli gore. Koliko dugo mislite da je to trajalo? Sat? Deset?

Najvjerojatnije da. Vidite, proračunska tablica iznad je zapravo ne stožerna tablica. Stvoren je ručno iz neobrađenih podataka pohranjenih na drugom mjestu, a za kompilaciju je doista trebalo nekoliko sati. Međutim, to je upravo takav sažetak mogla izraditi pomoću izvedenih tablica, u kojem slučaju bi to potrajalo samo nekoliko sekundi. Otkrijmo kako ...

Ako bismo pronašli izvorni popis prodajnih transakcija, to bi moglo izgledati ovako:

Možda ćete se iznenaditi kada saznate da pomoću značajke stožerne tablice u Excelu možemo izraditi mjesečni sažetak prodaje sličan gore navedenom za nekoliko sekundi, sa samo nekoliko klikova mišem. Možemo to učiniti - i još mnogo toga!

Kako stvoriti zaokretnu tablicu

Prvo provjerite imate li neke neobrađene podatke u radnom listu u Excelu. Popis financijskih transakcija je tipičan, ali to može biti popis gotovo svih: pojedinosti o kontaktima zaposlenika, vašoj zbirci CD-a ili potrošnji goriva za vaš automobilski park automobila.

Tako smo pokrenuli Excel ... i učitali smo takav popis ...

Kada otvorimo popis u Excelu, spremni smo za početak izrade izvedbene tablice.

Kliknite bilo koju pojedinačnu ćeliju na popisu:

Zatim, iz Umetnuti kliknite karticu zaokretne tablice ikona:

Stvori zaokretnu tablicu pojavljuje se okvir, postavljajući vam dva pitanja: Na koje se podatke treba oslanjati vaša nova izvedbena tablica i gdje bi se trebala stvoriti? Budući da smo već kliknuli na ćeliju na popisu (u gornjem koraku), za nas je već odabran cijeli popis koji okružuje tu ćeliju ($ A $ 1: $ G $ 88 na Plaćanja u ovom primjeru). Imajte na umu da bismo mogli odabrati popis u bilo kojoj drugoj regiji bilo kojeg drugog radnog lista, ili čak neki vanjski izvor podataka, kao što je tablica Access baze podataka, ili čak i tablica baze podataka MS-SQL Server. Također moramo odabrati želite li da naša stožerna tablica bude kreirana na a novi radni list, ili na postojanje jedan. U ovom primjeru odabrat ćemo novi jedan:

Novi radni list za nas je stvoren, a na tom je radnom listu stvorena prazna Tablica stožera:

Pojavljuje se i drugi okvir: Popis polja izvedene tablice. Ovaj popis polja bit će prikazan kad god kliknemo na bilo koju ćeliju unutar izvedene tablice (gore):

Popis polja u gornjem dijelu okvira zapravo je zbirka naslova stupaca iz izvornog sirovog radnog lista podataka. Četiri prazna polja u donjem dijelu zaslona omogućuju nam da odaberemo način na koji bismo željeli da naša PivotTable sažima neobrađene podatke. Za sada nema ništa u tim okvirima, pa je PivotTable prazna. Sve što trebamo je povući polja s gornjeg popisa i ispustiti ih u donje okvire. Stožerna se tablica automatski stvara kako bi odgovarala našim uputama. Ako to pogrešno shvatimo, potrebno je samo povući polja natrag odakle su došli i / ili povući novi da biste ih zamijenili.

vrijednosti kutija je vjerojatno najvažnija od četiri. Polje koje se uvlači u ovaj okvir predstavlja podatke koje treba sažeti na neki način (zbrajanjem, usrednjavanjem, pronalaženjem maksimuma, minimuma, itd.). To je gotovo uvijek numerički podaci. Savršen kandidat za ovaj okvir u našim uzorcima podataka je polje / stupac "Iznos". Povucimo to polje u vrijednosti kutija:

Primijetite da je (a) polje "Iznos" u popisu polja označeno i da je "Zbroj iznosa" dodan u vrijednosti označava da je stupac iznosa zbrojen.

Ako pregledamo samu izvedenu tablicu, uistinu nalazimo zbroj svih "iznosa" vrijednosti iz radnog lista neobrađenih podataka:

Stvorili smo našu prvu zaokretnu tablicu! Zgodno, ali ne osobito impresivno. Vjerojatno je potrebno malo više uvida u naše podatke.

Pozivajući se na naše uzorke podataka, moramo identificirati jedan ili više naslova stupaca koje bismo mogli upotrijebiti za podjelu ovog ukupnog broja. Na primjer, možemo odlučiti da bismo željeli vidjeti sažetak naših podataka gdje imamo a zaglavlja retka za svakog od različitih prodavača u našoj tvrtki, i ukupno za svakog. Da bismo to postigli, potrebno je samo povući polje "Prodavač" u Oznake retka kutija:

Sada, konačno, stvari postaju zanimljive! Naša zaokretna tablica počinje oblikovati ... .

S nekoliko klikova stvorili smo tablicu koja bi trebala dugo raditi ručno.

Što još možemo učiniti? Pa, u jednom smislu, naša PivotTable je dovršena. Izradili smo koristan sažetak naših izvornih podataka. Važne stvari su već naučene! Za ostatak članka ispitat ćemo neke od načina na koje se mogu stvoriti složenije tablice i načine na koje se te stožerne tablice mogu prilagoditi.

Prvo, možemo stvoriti dva-dimenzionalna tablica. Učinimo to pomoću "Načina plaćanja" kao naslova stupca. Jednostavno povucite naslov "Način plaćanja" u Oznake stupaca kutija:

Koji izgleda ovako:

Počinjem dobivati vrlo svjež!

Učinimo to tri-dimenzionalna tablica. Kako bi takav stol mogao izgledati? Pa, da vidimo ...

Povucite stupac / naslov "Paket" u Filtar izvješća kutija:

Primijetite 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 raščlambu prodavača vs način plaćanja za svi paketa, ili, s nekoliko klikova, promijenite ga kako biste prikazali istu raščlambu za paket "Sunseekers":

I tako, ako razmišljate o tome na pravi način, naša PivotTable je sada trodimenzionalna. Nastavimo prilagođavati ...

Ako se ispostavi, recimo, to samo želimo vidjeti ček i kreditnu karticu transakcije (tj. nema gotovinskih transakcija), tada možemo poništiti odabir stavke "Gotovina" iz naslova stupaca. Kliknite strelicu padajućeg izbornika pored Oznake stupaca, i odznačite "Gotovina":

Da vidimo kako to izgleda ... Kao što možete vidjeti, "Cash" je nestao.

oblikovanje

To je očito vrlo moćan sustav, ali do sada rezultati izgledaju vrlo jednostavno i dosadno. Za početak, brojevi koje sumiramo ne izgledaju kao dolarski iznosi - samo stari brojevi. Ispravimo to.

Iskušenje bi moglo biti učiniti ono što smo navikli raditi u takvim okolnostima i jednostavno odabrati cijelu tablicu (ili cijeli radni list) i koristiti tipke za oblikovanje standardnih brojeva na alatnoj traci da biste dovršili oblikovanje. Problem s tim pristupom je da ako ikada mijenjate strukturu zaokretne tablice (koja je vjerojatna 99%), tada će se ti formati brojeva izgubiti. Trebamo način koji će ih učiniti (polu) trajnim.

Prvo, nalazimo stavku "Zbroj iznosa" u vrijednosti i kliknite na njega. Pojavi se izbornik. Odabiremo Postavke polja vrijednosti ... s izbornika:

Postavke polja vrijednosti pojavljuje se okvir.

Kliknite gumb Format brojeva i standard Okvir Format ćelija pojavljuje:

Od Kategorija popis, odaberite (recimo) Računovodstvo, i ispusti broj decimalnih mjesta na 0. Kliknite u redu nekoliko puta da biste se vratili na stožernu tablicu ...

Kao što možete vidjeti, brojevi su ispravno formatirani u dolarskim iznosima.

Dok smo na temu oblikovanja, formatirajmo cijelu izvedenu tablicu. Postoji nekoliko načina da to učinite. Iskoristimo jednostavan ...

Kliknite gumb Alati / dizajn zaokretne tablice kartica:

Zatim spustite strelicu u donjem desnom kutu Stilovi izvedene tablice popis da biste vidjeli ogromnu zbirku ugrađenih stilova:

Odaberite bilo koju osobu koja vam se sviđa i pogledajte rezultat na svojoj izvedenoj tablici:

Druge opcije

Možemo raditi i sa datumima. Sada se u popisu transakcija obično nalazi mnogo, mnogo datuma kao što je onaj s kojim smo počeli. No, Excel nudi mogućnost grupiranja stavki podataka po danima, tjednima, mjesecima, godinama itd. Pogledajmo kako se to radi.

Prvo, uklonimo stupac "Način plaćanja" iz Oznake stupaca okvir (jednostavno ga povucite natrag na popis polja) i zamijenite ga stupcem "Datum rezervirano":

Kao što možete vidjeti, to čini našu stožernu tablicu odmah beskorisnom, dajući nam jedan stupac za svaki datum na kojem se transakcija dogodila - vrlo široku tablicu!

Da biste to ispravili, desnom tipkom miša kliknite bilo koji datum i odaberite Skupina… iz kontekstnog izbornika:

Pojavljuje se okvir za grupiranje. Odabiremo mjeseci i kliknite U redu:

Voila! mnogo korisnija tablica:

(Usput, ova je tablica gotovo identična onoj prikazanoj na početku ovog članka - originalnom sažetku prodaje koji je izrađen ručno.)

Još jedna dobra stvar koju trebate znati je da možete imati više od jednog naslova zaglavlja (ili naslova stupaca):

... koja izgleda ovako ... .

Možete napraviti sličnu stvar sa zaglavljem stupaca (ili čak prijaviti filtre).

Držeći stvari opet jednostavnim, pogledajmo kako se zapletati prosječno vrijednosti, a ne zbrojene vrijednosti.

Prvo, kliknite na "Sum of Amount" i odaberite Postavke polja vrijednosti ... iz kontekstnog izbornika koji se pojavljuje:

U Zbrojite polje vrijednosti po na popisu Postavke polja vrijednosti okvir, odaberite prosječan:

Dok smo ovdje, promijenimo Prilagođeno ime, od "Prosječni iznos" do nečeg malo konciznijeg. Upišite nešto poput "Prosj.":

Klik u redu, i vidjeti kako izgleda. Primijetite da se sve vrijednosti mijenjaju od zbrojenih ukupnih vrijednosti u prosjeke, a naslov tablice (gornja lijeva ćelija) promijenio se u "Prosj.":

Ako nam se sviđa, možemo imati čak i sume, prosjeke i brojanja (broj = broj prodanih) sve na istoj PivotTable!

Evo koraka da biste dobili nešto slično na mjestu (počevši od prazne tablice zaokretne tablice):

Povucite "Prodavač" u Oznake stupaca
Povucite polje "Iznos" prema dolje u polje vrijednosti okvir tri puta
Za prvo polje "Iznos" promijenite njegovo prilagođeno ime u "Ukupno" i njegov format brojeva Računovodstvo (0 decimalnih mjesta)
Za drugo polje "Iznos" promijenite njegovo prilagođeno ime u "Prosječno", njegovu funkciju na prosječan i to je format broja Računovodstvo (0 decimalnih mjesta)
Za treće polje "Iznos" promijenite njegovo ime u "Count" i njegovu funkciju na Računati
Povucite automatski stvorenu polje iz Oznake stupaca do Oznake retka

Evo što ćemo na kraju postići:

Ukupno, prosječno i računa na istu PivotTable!

Zaključak

Postoji mnogo, mnogo više značajki i opcija za izvedene tablice koje je stvorio Microsoft Excel - previše ih je za popis u članku poput ovog. Da biste u potpunosti pokrili potencijal stožernih tablica, potrebna je mala knjiga (ili velika web-lokacija). Hrabri i / ili geeky čitatelji mogu istraživati stožerne tablice prilično jednostavno: Jednostavno kliknite desnom tipkom miša na gotovo sve, i pogledajte koje vam opcije postaju dostupne. Tu su i dvije kartice s vrpcama: Alati / mogućnosti zaokretne tablice i Dizajn. Nije važno ako pogriješite - lako je izbrisati PivotTable i početi iznova - mogućnost da stari DOS korisnici Lotus 1-2-3 nikada nisu imali.

Ako radite u sustavu Office 2007, možda ćete htjeti pogledati naš članak o stvaranju izvedene tablice u programu Excel 2007.

Uključili smo Excelovu radnu knjigu koju možete preuzeti da biste vježbali svoje vještine zaokretne tablice. Trebalo bi raditi sa svim verzijama programa Excel od 97 pa nadalje.

Preuzmite našu radnu knjigu u praksi