Delo z vrtljivimi tabelami v programu Microsoft Excel

Vrtilne tabele so ena najmočnejših funkcij programa Microsoft Excel. Omogočajo analizo velikih količin podatkov in njihovo povzemanje v samo nekaj klikih z miško. V tem članku raziskujemo vrtilne tabele, razumemo, kaj so, in jih naučimo ustvarjati in prilagajati.

Opomba: Ta članek je napisan v programu Excel 2010 (Beta). Koncept vrtilne tabele se je skozi leta malo spremenil, vendar se je metoda ustvarjanja spremenila v skoraj vsaki iteraciji Excela. Če uporabljate različico programa Excel, ki ni 2010, pričakujte različne zaslone od tistih, ki jih vidite v tem članku.

Malo zgodovine

V prvih dneh programa za razpredelnice je Lotus 1-2-3 odločil za to. Njegov prevladujoči položaj je bil tako popoln, da so ljudje mislili, da je izguba časa za Microsoft, da se trudi razvijati lastno programsko opremo za preglednice (Excel), da bi tekmovala z Lotusom. Flash-forward do leta 2010, in Excelov prevladujoči položaj na trgu za preglednice je večji od Lotusovega doslej, medtem ko se število uporabnikov, ki še vedno uporabljajo Lotus 1-2-3, približuje ničli. Kako se je to zgodilo? Kaj je povzročilo tako dramatično spremembo usode?

Industrijski analitiki so se strinjali z dvema dejavnikoma: Prvič, Lotus se je odločil, da je ta elegantna nova platforma GUI, ki se imenuje »Windows«, prehodna moda, ki se nikoli ne bi dvignila. Odločili so se, da bi izdelali različico programa Lotus 1-2-3 za Windows (vsaj za nekaj let) in napovedali, da je njihova različica programske opreme DOS vse, kar bi kdor koli potreboval. Microsoft je seveda razvil Excel izključno za Windows. Drugič, Microsoft je razvil funkcijo za Excel, ki je Lotus ni zagotovil v 1-2-3, namreč Vrtilne tabele.  Funkcija PivotTables, ekskluzivno za Excel, je bila tako izjemno koristna, da so se ljudje pripravljeni učiti celotnega novega programskega paketa (Excel), namesto da bi se držali programa (1-2-3), ki ga ni imel. Ta značilnost, skupaj z napačno presojo uspeha sistema Windows, je bila smrtna zveza za Lotus 1-2-3 in začetek uspeha programa Microsoft Excel.

Razumevanje vrtilnih tabel

Torej, kaj je vrtilna tabela, točno?

Preprosto povedano, vrtilna tabela je povzetek nekaterih podatkov, ustvarjenih za preprosto analizo navedenih podatkov. Toda za razliko od ročno izdelanega povzetka, so vrtilne tabele programa Excel interaktivne. Ko ga ustvarite, ga lahko preprosto spremenite, če ne ponuja natančnega vpogleda v vaše podatke, za katere ste upali. V nekaj klikih se lahko povzetek "obrne" - zavrti tako, da naslovi stolpcev postanejo naslovi vrstic in obratno. Veliko več je mogoče storiti tudi. Namesto da bi opisali vse funkcije vrtilnih tabel, jih bomo preprosto prikazali ...

Podatki, ki jih analizirate s pomočjo vrtilne tabele, ne morejo biti samo kaj podatkov - mora biti surov podatki, ki so bili prej neobdelani (brez povzetka) - običajno nekakšen seznam. Primer tega je lahko seznam prodajnih transakcij v podjetju v zadnjih šestih mesecih.

Preglejte spodnje podatke:

Opazite, da je to ne surovi podatki. Pravzaprav je že nekakšen povzetek. V celici B3 lahko vidimo 30.000 dolarjev, kar je očitno skupna prodaja Jamesa Cooka za mesec januar. Kje so torej surovi podatki? Kako smo prišli do zneska 30.000 $? Kje je izvirni seznam prodajnih transakcij, iz katerega je bila ta številka ustvarjena? Jasno je, da se je nekje moral nekdo sprijazniti z vsemi prodajnimi transakcijami v zadnjih šestih mesecih v povzetku, ki ga vidimo zgoraj. Kako dolgo misliš, da je to trajalo? Ura? Deset?

Najverjetneje, da. Vidite, zgornja preglednica je dejansko ne vrtilna tabela. Ustvarjen je bil ročno iz neobdelanih podatkov, ki so bili shranjeni drugje, in dejansko je trajalo nekaj ur za pripravo. Vendar pa je to natanko tak povzetek lahko ustvarili s pomočjo vrtilnih tabel, v tem primeru bi to trajalo le nekaj sekund. Ugotovimo, kako…

Če bi izsledili prvotni seznam prodajnih transakcij, bi lahko izgledal takole:

Morda boste presenečeni, ko boste izvedeli, da lahko s pomočjo funkcije vrtilne tablice programa Excel v nekaj sekundah ustvarimo mesečni povzetek prodaje, podoben zgornjemu, s samo nekaj kliki z miško. To lahko naredimo - in še veliko več!

Kako ustvariti vrtilno tabelo

Najprej se prepričajte, da imate nekaj neobdelanih podatkov v delovnem listu v Excelu. Seznam finančnih transakcij je tipičen, vendar je lahko seznam skoraj ničesar: kontaktni podatki zaposlenih, zbirka CD-jev ali podatki o porabi goriva za vozni park vašega podjetja..

Zato začnemo z Excelom ... in naložimo tak seznam ...

Ko je seznam odprt v Excelu, smo pripravljeni začeti ustvarjati vrtilno tabelo.

Kliknite na eno samo celico na seznamu:

Potem, iz Vstavi kliknite zavihek Vrteča miza ikona:

The Ustvari vrtilno tabelo se prikaže okno, ki vam zastavi dve vprašanji: Na katere podatke naj temelji vaša nova vrtilna tabela in kje naj bo ustvarjena? Ker smo že kliknili celico na seznamu (v zgornjem koraku), je za nas že izbran celoten seznam, ki obkroža to celico ($ A $ 1: $ G $ 88 na Plačila v tem primeru). Upoštevajte, da lahko izberemo seznam v kateri koli drugi regiji katerega koli drugega delovnega lista ali celo nek zunanji vir podatkov, kot je tabela zbirke podatkov Access ali celo tabela zbirke podatkov MS-SQL Server. Prav tako moramo izbrati, ali želimo, da bo naša nova vrtilna tabela ustvarjena na novo na delovnem listu ali na obstoječih eno. V tem primeru bomo izbrali a novo ena:

Novo delovno mesto je ustvarjeno za nas in na tem delovnem listu je ustvarjena prazna tabela vrtilne tabele:

Pojavi se tudi drugo polje: Seznam polj vrtilne tabele.  Ta seznam polj bo prikazan vsakič, ko kliknemo katero koli celico v vrtilni tabeli (zgoraj):

Seznam polj v zgornjem delu polja je dejansko zbirka naslovov stolpcev iz izvirnega delovnega lista neobdelanih podatkov. Štiri prazna polja v spodnjem delu zaslona nam omogočajo, da izberemo način, na katerega bi želeli, da naša vrtilna tabela povzame neobdelane podatke. Doslej v teh poljih ni ničesar, zato je vrtilna tabela prazna. Vse kar moramo storiti je, da povlečete polja z zgornjega seznama navzdol in jih spustite v spodnja polja. Vmesna tabela se nato samodejno ustvari, da se ujema z našimi navodili. Če se zmotimo, moramo polja povleči le nazaj, od koder so prišli in / ali povlecite novo polja, da jih nadomestijo.

The Vrednosti škatla je verjetno najpomembnejša izmed štirih. Polje, ki se vleče v to polje, predstavlja podatke, ki jih je treba na nek način povzeti (s seštevanjem, povprečenjem, iskanjem največjega, najmanjšega itd.). Skoraj vedno je numerično podatkov. Popoln kandidat za to polje v naših vzorčnih podatkih je polje / stolpec »Količina«. Povlecimo to polje v Vrednosti škatla:

Upoštevajte, da je (a) polje "Znesek" na seznamu polj označeno in "Vsota zneska" je bila dodana Vrednosti označuje, da je bil stolpec zneska seštet.

Če pregledamo samo vrtilno tabelo, res najdemo vsoto vseh vrednosti »Amount« iz delovnega lista neobdelanih podatkov:

Ustvarili smo našo prvo vrtilno tabelo! Priročen, vendar ne posebej impresiven. Verjetno potrebujemo malo več vpogleda v naše podatke.

Glede na naše vzorčne podatke moramo identificirati enega ali več naslovov stolpcev, ki bi jih lahko uporabili za razdelitev tega skupnega zneska. Lahko se na primer odločimo, da želimo videti povzetek naših podatkov, kjer imamo a naslov vrstice za vsakega od različnih prodajalcev v našem podjetju in skupaj za vsakega. Da bi to dosegli, moramo le vleči polje "Prodajalec" v Nalepke vrstice škatla:

Zdaj, končno, stvari se začnejo zanimati! Naša vrtilna tabela se začne oblikovati ... .

Z nekaj kliki smo ustvarili tabelo, ki bi jo potrebovali dolgo ročno.

Torej, kaj še lahko storimo? No, v nekem smislu je naša vrtilna tabela popolna. Ustvarili smo uporaben povzetek naših izvornih podatkov. Pomembne stvari so se že naučili! V preostalem članku bomo preučili nekaj načinov za ustvarjanje bolj zapletenih vrtilnih tabel in načinov, kako jih je mogoče prilagoditi..

Najprej lahko ustvarimo dva-dimenzijska tabela. Naredimo to z uporabo načina plačila kot naslova stolpca. Preprosto povlecite naslov »Način plačila« na naslov Oznake stolpcev škatla:

Ta izgleda tako:

Začenjam zelo kul!

Naredimo to tri-dimenzijska tabela. Kako bi lahko izgledala taka miza? No, poglejmo ...

Povlecite stolpec / naslov Package v naslov Filter filtra škatla:

Obvestilo, kje se konča ... .

To nam omogoča, da filtriramo naše poročilo, na podlagi katerega je bil kupljen "počitniški paket". Na primer, vidimo razčlenitev prodajalca vs način plačila za vse paketov ali z nekaj kliki spremeni, da prikaže enako razčlenitev za paket »Sunseekers«:

Torej, če razmišljate o tem na pravi način, je naša vrtilna tabela zdaj tridimenzionalna. Nadaljuj s prilagajanjem ...

Če se izkaže, recimo, da samo želimo videti ček in kreditno kartico transakcij (tj. brez gotovinskih transakcij), potem lahko iz naslova stolpcev prekličemo postavko »gotovina«. Kliknite spustno puščico zraven Oznake stolpcev, in odznačite »Cash«:

Poglejmo, kako to izgleda ... Kot lahko vidite, »Cash« ni več.

Oblikovanje

To je očitno zelo močan sistem, vendar so rezultati doslej zelo navadni in dolgočasni. Za začetek, številke, ki jih povzamemo, ne izgledajo kot zneski v dolarjih - samo navadne stare številke. Popravimo to.

Skušnjava je, da naredimo to, kar smo v takih okoliščinah navadili, in preprosto izberemo celotno tabelo (ali celoten delovni list) in uporabimo standardne gumbe za oblikovanje številk v orodni vrstici, da zaključimo oblikovanje. Težava pri tem pristopu je, da če boste v prihodnosti spremenili strukturo vrtilne tabele (kar je verjetno 99%), bodo te številske oblike izgubljene. Potrebujemo način, ki bo (pol) trajne.

Najprej poiščemo vnos »Vsota zneska« v Vrednosti in ga kliknite. Prikaže se meni. Izberemo Nastavitve polja vrednosti ... iz menija:

The Nastavitve polja vrednosti se prikaže polje.

Kliknite gumb Oblika številk in standard Oblikuj polje Celice prikaže:

Iz Kategorija seznam, izberite (recimo) Računovodstvo, in pustite število decimalnih mest na 0. Kliknite v redu nekajkrat, da se vrnete na vrtilno tabelo ...

Kot lahko vidite, so bile številke pravilno oblikovane v dolarskih zneskih.

Medtem ko smo na temo oblikovanja, oblikovajmo celotno vrtilno tabelo. Obstaja nekaj načinov za to. Uporabimo preprosto ...

Kliknite gumb Orodja vrtilne tabele / Oblikovanje zavihek:

Nato spustite puščico v spodnjem desnem kotu Slogi vrtilne tabele seznamu za prikaz velike zbirke vgrajenih slogov:

Izberite tistega, ki se pritoži, in si oglejte rezultat v vaši vrtilni tabeli:

Druge možnosti

Lahko delamo tudi z datumi. Običajno je na seznamu transakcij veliko, veliko datumov, kot je tisti, s katerim smo začeli. Vendar Excel ponuja možnost združevanja podatkov po dnevih, tednih, mesecih, letih itd. Poglejmo, kako se to stori.

Najprej odstranimo stolpec »Način plačila« v Oznake stolpcev polje (preprosto povlecite nazaj na seznam polj) in ga nadomestite s stolpcem »Datum rezervirano«:

Kot lahko vidite, je naša vrtilna tabela takoj neuporabna, kar nam daje en stolpec za vsak datum, ko se je transakcija zgodila - zelo široka tabela!

Če želite to popraviti, z desno miškino tipko kliknite kateri koli datum in izberite Skupina… iz kontekstnega menija:

Pojavi se okno za združevanje. Izberemo Meseci in kliknite V redu:

Voila! A veliko koristnejša tabela:

(Mimogrede ta tabela je skoraj enaka tisti, ki je prikazana na začetku tega članka - originalni povzetek prodaje, ki je bil ustvarjen ročno.)

Še ena dobra stvar, ki jo je treba upoštevati, je, da lahko imate več nizov naslovov vrstic (ali naslovov stolpcev):

… Ki izgleda tako ... .

Podobno lahko storite tudi z naslovi stolpcev (ali celo filtre poročil).

Če bomo stvari spet preprosti, poglejmo, kako se lotiti povprečje vrednosti, ne pa seštevajo vrednosti.

Najprej kliknite “Sum of Amount” in izberite Nastavitve polja vrednosti ... iz kontekstnega menija, ki se prikaže:

V Vnesite polje vrednosti tako, da v seznamu Nastavitve polja vrednosti izberite Povprečje:

Medtem ko smo tukaj, spremeni Ime po meri, od povprečja zneska do nekaj bolj jedrnatega. Vnesite nekaj podobnega: »Povp.«:

Kliknite v redu, in poglej, kako izgleda. Upoštevajte, da se vse vrednosti spremenijo iz seštevek vsote v povprečje, naslov tabele (zgornja leva celica) pa se spremeni v »Povp.«:

Če nam je všeč, lahko imamo celo zneske, povprečja in štetja (število = število prodanih), vse na isti vrtilni tabeli!

Spodaj so navedeni koraki za vzpostavitev takega mesta (začenši s prazno vrtilno tabelo):

1. Povlecite »Prodajalec« v Oznake stolpcev
2. Povlecite polje »Količina« navzdol v polje Vrednosti trikrat
3. Za prvo polje »Znesek« spremenite njegovo ime po meri v »Skupaj« in obliko številke na Računovodstvo (0 decimalnih mest)
4. Za drugo polje »Količina« spremenite njegovo ime po meri na »Povprečje«, njegovo funkcijo na Povprečje in je format številke Računovodstvo (0 decimalnih mest)
5. Za tretje polje »Količina« spremenite njegovo ime v »Count« in njegovo funkcijo na Štetje
6. Povlecite samodejno ustvarjeno polje iz Oznake stolpcev do Nalepke vrstice

Tukaj dobimo:

Skupno, povprečno in računajte na isto vrtilno tabelo!

Zaključek

Obstaja veliko, veliko več funkcij in možnosti za vrtilne tabele, ki jih je ustvaril Microsoft Excel - kar je preveč za seznam v članku, kot je ta. Da bi v celoti pokrili potencial vrtilnih tabel, bi bila potrebna majhna knjiga (ali velika spletna stran). Pogumni in / ali navdušeni bralci lahko precej preprosto raziskujejo vrtilne tabele: preprosto kliknite z desno miškino tipko na skoraj vse in si oglejte, katere možnosti so vam na voljo. Na voljo sta tudi dve kartici traku: Orodja / možnosti vrtilne tabele in Oblikovanje.  Ni pomembno, če se zmotite - preprosto je izbrisati vrtilno tabelo in začeti znova - možnost, da stari uporabniki DOS-a Lotus 1-2-3 nikoli niso imeli.

Če delate v sistemu Office 2007, si lahko ogledate naš članek o ustvarjanju vrtilne tabele v Excelu 2007.

Vključili smo delovni zvezek programa Excel, ki ga lahko prenesete, da boste lahko vadili svoje spretnosti v vrtilni tabeli. Delovati mora z vsemi različicami Excel od 97 naprej.

Prenesite našo delovno zvezo za prakso Excel