vsebina
Vrtilne tabele je eno najmočnejših orodij v Excelu. Omogočajo analizo in seštevek različnih povzetkov velikih količin podatkov z le nekaj kliki miške. V tem članku se bomo seznanili z vrtilnimi tabelami, razumeli, kaj so, se naučili, kako jih ustvariti in prilagoditi.
Ta članek je bil napisan z uporabo programa Excel 2010. Koncept vrtilnih tabel se z leti ni veliko spremenil, vendar je način njihovega ustvarjanja nekoliko drugačen v vsaki novi različici Excela. Če imate različico Excela, ki ni 2010, bodite pripravljeni, da se bodo posnetki zaslona v tem članku razlikovali od tistega, kar vidite na zaslonu.
Malo zgodovine
V zgodnjih dneh programske opreme za preglednice je veljalo pravilo Lotus 1-2-3. Njegova prevlada je bila tako popolna, da so se Microsoftova prizadevanja za razvoj lastne programske opreme (Excel) kot alternative Lotusu zdela izguba časa. Zdaj pa hitro naprej v leto 2010! Excel prevladuje nad preglednicami bolj kot koda Lotus kadar koli v svoji zgodovini in število ljudi, ki še vedno uporabljajo Lotus, je skoraj nič. Kako se je to lahko zgodilo? Kaj je bil razlog za tako dramatičen razvoj dogodkov?
Analitiki prepoznavajo dva glavna dejavnika:
- Najprej se je Lotus odločil, da je ta novonastala platforma GUI, imenovana Windows, le mimobežna modna muha, ki ne bo trajala dolgo. Zavrnili so izdelavo različice Lotus 1-2-3 za Windows (vendar le za nekaj let), saj so predvidevali, da bo DOS različica njihove programske opreme vse, kar bodo uporabniki potrebovali. Microsoft je naravno razvil Excel posebej za Windows.
- Drugič, Microsoft je v Excelu predstavil orodje, imenovano vrtilne tabele, ki ni bilo na voljo v Lotusu 1-2-3. Vrtilne tabele, ekskluzivne za Excel, so se izkazale za tako nadvse uporabne, da so ljudje raje vztrajali pri novi programski zbirki Excel, kot da bi nadaljevali z Lotusom 1-2-3, ki jih ni imel.
Vrtilne tabele so skupaj s podcenjevanjem uspeha sistema Windows na splošno igrale smrtni pohod za Lotus 1-2-3 in uvedle uspeh Microsoft Excela.
Kaj so vrtilne tabele?
Torej, kakšen je najboljši način za opredelitev vrtilnih tabel?
Preprosto povedano, vrtilne tabele so povzetki nekaterih podatkov, ustvarjeni za lažjo analizo teh podatkov. V nasprotju z ročno ustvarjenimi vsotami so Excelove vrtilne tabele interaktivne. Ko jih ustvarite, jih lahko enostavno spremenite, če ne dajejo slike, ki ste jo pričakovali. Z le nekaj kliki miške lahko vsote obrnete tako, da naslovi stolpcev postanejo naslovi vrstic in obratno. Z vrtilnimi tabelami lahko počnete marsikaj. Namesto da bi z besedami poskušali opisati vse lastnosti vrtilnih tabel, jih je lažje pokazati v praksi ...
Podatki, ki jih analizirate z vrtilnimi tabelami, ne morejo biti naključni. To bi morali biti surovi surovi podatki, kot nekakšen seznam. To je lahko na primer seznam prodaje, ki jo je podjetje opravilo v zadnjih šestih mesecih.
Oglejte si podatke, prikazane na spodnji sliki:
Upoštevajte, da to niso surovi neobdelani podatki, saj so bili že povzeti. V celici B3 vidimo 30000 $, kar je verjetno skupni rezultat, ki ga je James Cook naredil januarja. Kje so potem originalni podatki? Od kod številka 30000 $? Kje je prvotni seznam prodaje, iz katerega je bila izpeljana ta mesečna vsota? Jasno je, da je nekdo opravil odlično delo, ko je vse podatke o prodaji zadnjih šestih mesecev uredil in razvrstil ter jih pretvoril v tabelo skupnih vrednosti, ki jo vidimo. Kaj mislite, koliko časa je trajalo? ura? XNUMX:XNUMX?
Dejstvo je, da zgornja tabela ni vrtilna tabela. Izdelan je bil ročno iz neobdelanih podatkov, shranjenih drugje, obdelava pa je trajala vsaj nekaj ur. Prav takšno zbirno tabelo lahko ustvarite s pomočjo vrtilnih tabel v le nekaj sekundah. Ugotovimo, kako…
Če se vrnemo na prvotni prodajni seznam, bi bil videti nekako takole:
Morda boste presenečeni, da lahko iz tega seznama poslov s pomočjo vrtilnih tabel v samo nekaj sekundah ustvarimo mesečno poročilo o prodaji v Excelu, ki smo ga analizirali zgoraj. Da, zmoremo to in še več!
Kako ustvariti vrtilno tabelo?
Najprej se prepričajte, da imate nekaj izvornih podatkov v Excelovem listu. Seznam finančnih transakcij je najbolj tipičen, ki se pojavlja. Pravzaprav je to lahko seznam česar koli: kontaktni podatki zaposlenih, zbirka CD-jev ali podatki o porabi goriva vašega podjetja.
Torej, zaženemo Excel ... in naložimo tak seznam ...
Ko ta seznam odpremo v Excelu, lahko začnemo ustvarjati vrtilno tabelo.
Izberite katero koli celico s tega seznama:
Nato na zavihku vstavljanje (Vstavi) izberite ukaz Vrteča miza (Vrteča miza):
Prikaže se pogovorno okno Ustvari vrtilno tabelo (Ustvarjanje vrtilne tabele) z dvema vprašanjema za vas:
- Katere podatke uporabiti za ustvarjanje nove vrtilne tabele?
- Kam postaviti vrtilno tabelo?
Ker smo v prejšnjem koraku že izbrali eno od celic seznama, bo celoten seznam samodejno izbran za ustvarjanje vrtilne tabele. Upoštevajte, da lahko izberemo drugačen obseg, drugo tabelo in celo zunanji vir podatkov, kot je tabela baze podatkov Access ali MS-SQL. Poleg tega moramo izbrati, kam bomo postavili novo vrtilno tabelo: na nov list ali na enega od obstoječih. V tem primeru bomo izbrali možnost – Nov delovni list (na nov list):
Excel bo ustvaril nov list in nanj postavil prazno vrtilno tabelo:
Takoj, ko kliknemo katero koli celico v vrtilni tabeli, se prikaže drugo pogovorno okno: Seznam polj vrtilne tabele (Polja vrtilne tabele).
Seznam polj na vrhu pogovornega okna je seznam vseh naslovov iz izvirnega seznama. Štiri prazna območja na dnu zaslona vam omogočajo, da vrtilni tabeli poveste, kako želite povzeti podatke. Dokler so ta področja prazna, tudi v tabeli ni ničesar. Vse kar moramo storiti je, da povlecemo naslove iz zgornjega območja v prazna področja spodaj. Hkrati se samodejno generira vrtilna tabela po naših navodilih. Če se zmotimo, lahko odstranimo naslove iz spodnjega območja ali povlečemo druge, da jih nadomestimo.
Območje Vrednote (Pomeni) je verjetno najpomembnejši od štirih. Kateri naslov je v tem območju, določa, kateri podatki bodo povzeti (vsota, povprečje, največ, najmanj itd.). To so skoraj vedno številske vrednosti. Odličen kandidat za mesto na tem območju je podatek pod naslovom znesek (Stroški) naše prvotne tabele. Povlecite ta naslov na območje Vrednote (Vrednote):
Upoštevajte, da naslov znesek je zdaj označen s kljukico in v območju Vrednote (Vrednosti) se je pojavil vnos Vsota zneska (Znesek polja Znesek), ki označuje, da stolpec znesek povzel.
Če pogledamo samo vrtilno tabelo, bomo videli vsoto vseh vrednosti iz stolpca znesek originalna miza.
Tako je naša prva vrtilna tabela ustvarjena! Priročno, a ne posebej impresivno. Verjetno želimo pridobiti več informacij o svojih podatkih, kot jih trenutno imamo.
Obrnimo se k izvirnim podatkom in poskusimo identificirati enega ali več stolpcev, ki jih je mogoče uporabiti za razdelitev te vsote. Svojo vrtilno tabelo lahko na primer oblikujemo tako, da se skupni znesek prodaje izračuna za vsakega prodajalca posebej. Tisti. v našo vrtilno tabelo bodo dodane vrstice z imenom vsakega prodajalca v podjetju in njihovim skupnim zneskom prodaje. Če želite doseči ta rezultat, preprosto povlecite naslov prodajalec (komercialist) v regijo Oznake vrstic (Strings):
Postane bolj zanimivo! Naša vrtilna tabela začenja dobivati obliko ...
Vidite prednosti? V nekaj klikih smo ustvarili tabelo, ki bi jo ročno ustvarili zelo dolgo.
Kaj še lahko storimo? No, v nekem smislu je naša vrtilna tabela pripravljena. Ustvarili smo uporaben povzetek izvirnih podatkov. Pomembne informacije smo že prejeli! V nadaljevanju tega članka si bomo ogledali nekaj načinov za ustvarjanje bolj zapletenih vrtilnih tabel in se naučili, kako jih prilagoditi.
Nastavitev vrtilne tabele
Najprej lahko ustvarimo dvodimenzionalno vrtilno tabelo. Naredimo to z uporabo naslova stolpca Plačilna metoda (Način plačila). Samo povlecite naslov Plačilna metoda na območje Oznake stolpcev (Stolpci):
Dobimo rezultat:
Izgleda zelo kul!
Sedaj pa naredimo tridimenzionalno mizo. Kako bi izgledala takšna miza? Pa poglejmo…
Povlecite glavo paket (Kompleks) na območje filtri poročil (Filtri):
Upoštevajte, kje je ...
To nam daje možnost filtriranja poročila na podlagi "Kateri počitniški kompleks je bil plačan." Tako lahko na primer vidimo razčlenitev po prodajalcih in načinih plačila za vse komplekse ali pa v nekaj klikih z miško spremenimo pogled vrtilne tabele in prikažemo enako razčlenitev samo za tiste, ki so naročili kompleks. Iskalci sonca.
Torej, če to pravilno razumete, lahko našo vrtilno tabelo imenujemo tridimenzionalna. Nadaljujmo s postavitvijo…
Če se nenadoma izkaže, da mora biti v vrtilni tabeli prikazano samo plačilo s čekom in kreditno kartico (torej brezgotovinsko plačilo), potem lahko izklopimo prikaz naslova Denar (gotovina). Za to, poleg Oznake stolpcev kliknite puščico navzdol in počistite polje v spustnem meniju Denar:
Poglejmo, kako je zdaj videti naša vrtilna tabela. Kot lahko vidite, stolpec Denar izginila iz nje.
Oblikovanje vrtilnih tabel v Excelu
Vrtilne tabele so očitno zelo zmogljivo orodje, vendar so zaenkrat rezultati videti nekoliko navadni in dolgočasni. Na primer, številke, ki jih seštejemo, niso videti kot zneski v dolarjih – so samo številke. Popravimo to.
Mamljivo je narediti tisto, česar ste vajeni v takšni situaciji in preprosto izbrati celotno tabelo (ali celoten list) in uporabiti standardne gumbe za oblikovanje številk v orodni vrstici, da nastavite želeno obliko. Težava s tem pristopom je, da če boste kdaj v prihodnosti spremenili strukturo vrtilne tabele (kar se zgodi z 99-odstotno verjetnostjo), bo oblikovanje izgubljeno. Kar potrebujemo, je način, da postane (skoraj) trajno.
Najprej poiščimo vnos Vsota zneska in Vrednote (Vrednosti) in kliknite nanj. V meniju, ki se prikaže, izberite element Nastavitve polja vrednosti (Možnosti polja vrednosti):
Prikaže se pogovorno okno Nastavitve polja vrednosti (Možnosti polja vrednosti).
pritisnite Oblika številk (Oblika števila), se odpre pogovorno okno. Oblikuj celice (oblika celice):
S seznama Kategorija (Oblike števil) izberite računovodstvo (Finančni) in nastavite število decimalnih mest na nič. Zdaj nekajkrat pritisnite OKda se vrnemo k naši vrtilni tabeli.
Kot lahko vidite, so številke oblikovane kot zneski v dolarjih.
Ko smo že pri oblikovanju, nastavimo obliko za celotno vrtilno tabelo. To lahko storite na več načinov. Uporabljamo enostavnejšo …
Kliknite Orodja za vrtilne tabele: Oblikovanje (Delo z vrtilnimi tabelami: konstruktor):
Nato razširite meni s klikom na puščico v spodnjem desnem kotu razdelka Slogi vrtilne tabele (Slogi vrtilne tabele), če si želite ogledati obsežno zbirko slogov v vrstici:
Izberite kateri koli primeren slog in si oglejte rezultat v vrtilni tabeli:
Druge nastavitve vrtilne tabele v Excelu
Včasih morate podatke filtrirati po datumih. Na primer, na našem seznamu poslov je veliko, veliko datumov. Excel ponuja orodje za združevanje podatkov po dnevu, mesecu, letu itd. Poglejmo, kako se to naredi.
Najprej odstranite vnos. Plačilna metoda iz regije Oznake stolpcev (Stolpci). Če želite to narediti, ga povlecite nazaj na seznam naslovov in na njegovo mesto premaknite naslov Datum rezervacije (datum rezervacije):
Kot lahko vidite, je zaradi tega naša vrtilna tabela začasno postala neuporabna. Excel je ustvaril ločen stolpec za vsak datum, ko je bila sklenjena trgovina. Kot rezultat smo dobili zelo široko mizo!
Če želite to popraviti, z desno tipko miške kliknite kateri koli datum in izberite v kontekstnem meniju SKUPINA (Skupina):
Prikaže se pogovorno okno za združevanje. Mi izbiramo Mesecev (Mesec) in kliknite OK:
Voila! Ta tabela je veliko bolj uporabna:
Mimogrede, ta tabela je skoraj enaka tisti, prikazani na začetku članka, kjer so bile prodajne vsote sestavljene ročno.
Obstaja še ena zelo pomembna točka, ki jo morate vedeti! Ustvarite lahko ne eno, ampak več ravni naslovov vrstic (ali stolpcev):
... in izgledalo bo takole ...
Enako lahko storite z naslovi stolpcev (ali celo filtri).
Vrnimo se k prvotni obliki tabele in poglejmo, kako namesto vsot prikazati povprečja.
Za začetek kliknite na Vsota zneska in v meniju, ki se prikaže, izberite Nastavitve polja vrednosti (Možnosti polja vrednosti):
Seznam Povzemite polje vrednosti po (Operacija) v pogovornem oknu Nastavitve polja vrednosti (Možnosti polja vrednosti) izberite Povprečje (Povprečje):
Hkrati, ko smo že tukaj, se spremenimo Ime po meri (Ime po meri) z Povprečje zneska (Znesek polje Znesek) na nekaj krajšega. V to polje vnesite nekaj podobnega Povpr:
Pritisnite OK in poglej kaj se zgodi. Upoštevajte, da so se vse vrednosti spremenile iz vsot v povprečja, glava tabele (v zgornji levi celici) pa se je spremenila v Povpr:
Če želite, lahko takoj dobite znesek, povprečje in število (prodaje) v eni vrtilni tabeli.
Tukaj je vodnik po korakih, kako to storiti, začenši s prazno vrtilno tabelo:
- Povlecite glavo prodajalec (komercialist) v regijo Oznake stolpcev (Stolpci).
- Trikrat povlecite naslov znesek (Cena) na območje Vrednote (Vrednote).
- Za prvo polje znesek spremenite naslov v Skupaj za plačilo (Znesek), oblika števila v tem polju pa je računovodstvo (finančni). Število decimalnih mest je nič.
- Drugo polje znesek Ime Povprečjee, nastavite operacijo zanj Povprečje (Povprečje) in oblika števila v tem polju se prav tako spremeni v računovodstvo (Finančni) z nič decimalnimi mesti.
- Za tretje polje znesek nastavite naslov Grof in operacija zanj – Grof (količina)
- v Oznake stolpcev Samodejno ustvarjeno polje (Stolpci). Σ Vrednosti (Σ vrednosti) – povlecite na območje Oznake vrstic (vrstice)
Tukaj je tisto, kar bomo na koncu dobili:
Skupni znesek, povprečna vrednost in število prodaj – vse v eni vrtilni tabeli!
zaključek
Vrtilne tabele v Microsoft Excelu vsebujejo veliko funkcij in nastavitev. V tako majhnem članku niti približno ne zajamejo vseh. Za popoln opis vseh možnosti vrtilnih tabel bi bila potrebna majhna knjiga ali veliko spletno mesto. Drzni in radovedni bralci lahko nadaljujejo z raziskovanjem vrtilnih tabel. Če želite to narediti, z desno miškino tipko kliknite skoraj kateri koli element vrtilne tabele in si oglejte, katere funkcije in nastavitve se odprejo. Na traku boste našli dva zavihka: Orodja vrtilne tabele: možnosti (analiza) in Oblikovanje (Konstruktor). Ne bojte se narediti napake, vrtilno tabelo lahko vedno izbrišete in začnete znova. Imate priložnost, ki je dolgoletni uporabniki DOS-a in Lotusa 1-2-3 nikoli niso imeli.