Práca s kontingenčnými tabuľkami v programe Microsoft Excel

Kontingenčné tabuľky sú jednou z najvýkonnejších funkcií programu Microsoft Excel. Umožňujú analyzovať veľké množstvo údajov a zhrnúť ich len niekoľkými kliknutiami myši. V tomto článku skúmame kontingenčné tabuľky, pochopíme, aké sú, a naučili sa ich vytvárať a upravovať.

Poznámka: Tento článok je napísaný pomocou programu Excel 2010 (Beta). Koncepcia kontingenčnej tabuľky sa v priebehu rokov menila málo, ale spôsob vytvorenia jedného sa zmenil takmer v každej iterácii programu Excel. Ak používate verziu programu Excel, ktorá nie je 2010, očakávajte rôzne obrazovky od obrazoviek, ktoré vidíte v tomto článku.

Malá história

V prvých dňoch programov tabuľkových procesov Lotus 1-2-3 ovládal hrad. Jeho dominancia bola taká úplná, že ľudia si mysleli, že je to strata času, aby sa spoločnosť Microsoft obťažovala vývojom vlastného softvéru na tabuľkový procesor (Excel), ktorý by konkuroval Lotusu. Flash-forward do roku 2010 a dominancia Excelu na trhu s tabuľkovými procesormi je väčšia ako vôbec Lotus, zatiaľ čo počet používateľov, ktorí stále používajú Lotus 1-2-3, sa blíži k nule. Ako sa to stalo? Čo spôsobilo také dramatické zvrátenie bohatstva?

Priemyselní analytici položili to na dva faktory: Po prvé, Lotus sa rozhodol, že táto fantastická nová platforma GUI s názvom "Windows" bola prechodná výstava, ktorá by nikdy nezačala. Oni odmietli vytvoriť Windows verziu Lotus 1-2-3 (na niekoľko rokov, rovnako), predpovedal, že ich DOS verzia softvéru bol všetko, koľko by niekedy potreboval. Microsoft prirodzene vyvinul Excel výhradne pre Windows. Po druhé, Microsoft vyvinul funkciu Excel, ktorú Lotus neposkytol v 1-2-3, a to kontingenčnej tabuľky. Funkcia kontingenčných tabuliek, exkluzívna pre program Excel, bola považovaná za ohromne užitočnú, že ľudia boli ochotní naučiť sa úplne nový softvérový balík (Excel), než aby sa držali programu (1-2-3), ktorý ho nemal. Táto jedna funkcia, spolu s nesprávnym posúdením úspechu systému Windows, bolo pre spoločnosť Lotus 1-2-3 smrteľný a začiatok úspechu programu Microsoft Excel.

Pochopenie kontingenčných tabuliek

Takže čo je to presne kontingenčná tabuľka?

Jednoducho povedané, kontingenčná tabuľka je súhrn niektorých údajov vytvorených tak, aby umožňovali jednoduchú analýzu uvedených údajov. Na rozdiel od ručne vytvoreného zhrnutia sú však kontingenčné tabuľky programu Excel interaktívne. Akonáhle ste vytvorili jeden, môžete ho ľahko zmeniť, ak neponúka presné informácie o vašich údajoch, ktoré ste dúfali. Pri niekoľkých kliknutiach sa súhrn môže "otočiť" - otočiť tak, že nadpisy stĺpcov sa stávajú riadkami riadkov a naopak. Je tu ešte oveľa viac, čo sa dá urobiť. Skôr než sa pokúsite popísať všetky funkcie kontingenčných tabuliek, jednoducho ich ukážeme ...

Údaje, ktoré analyzujete pomocou kontingenčnej tabuľky, nemôžu byť jednoduché akýkoľvek údaje - to musí byť surový údaje, ktoré boli predtým nespracované (bezvýtvarné) - zvyčajne zoznam nejakého druhu. Príkladom toho môže byť zoznam predajných transakcií v spoločnosti za posledných šesť mesiacov.

Preskúmajte údaje uvedené nižšie:

Všimnite si, že to je nie nespracované dáta. V skutočnosti je to už zhrnutie. V bunke B3 môžeme vidieť 30 000 dolárov, čo zrejme predstavuje celkový predaj Jamesa Cooka za január. Takže kde sú surové údaje? Ako sme dosiahli výšku 30 000 USD? Kde je pôvodný zoznam predajných transakcií, z ktorých bol tento údaj vytvorený? Je jasné, že niekto musí niekto prejsť do problémov so zlučovaním všetkých obchodných transakcií za posledných šesť mesiacov do súhrnu, ktorý vidíme vyššie. Ako dlho to predpokladáte? Hodina? desať?

Pravdepodobne áno. Vidíte, hore uvedená tabuľka je skutočne nie kontingenčnej tabuľky. Bola vytvorená ručne z nespracovaných údajov uložených inde, a naozaj trvalo niekoľko hodín, kým sa kompilovali. Napriek tomu je to presne ten druh zhrnutia mohol byť vytvorené pomocou kontingenčných tabuliek, v takom prípade by to trvalo len niekoľko sekúnd. Poďme zistiť, ako ...

Ak by sme mali sledovať pôvodný zoznam predajných transakcií, mohlo by to vyzerať takto:

Možno vás prekvapí, že pomocou funkcie kontingenčnej tabuľky v programe Excel môžeme vytvoriť mesačné súhrnné prehľady podobné vyššie uvedenému za niekoľko sekúnd, a to len s niekoľkými kliknutiami myši. Môžeme to urobiť - a oveľa viac!

Ako vytvoriť kontingenčnú tabuľku

Po prvé, uistite sa, že máte nejaké nespracované údaje v pracovnom hárku v programe Excel. Zoznam finančných transakcií je typický, ale môže to byť zoznam takmer čokoľvek: údaje o kontakte s zamestnancom, vaša zbierka CD alebo údaje o spotrebe paliva pre vozový park vašej spoločnosti.

Takže začneme Excel ... a načítavame takýto zoznam ...

Keď máme zoznam otvorený v programe Excel, sme pripravení začať vytvárať kontingenčnú tabuľku.

Kliknite na ľubovoľnú jednu bunku v zozname:

Potom, z insert kliknite na kartu kontingenčnej ikona:

Vytvoriť kontingenčnú tabuľku zobrazí sa dvoma otázkami: Aké údaje by mala byť vaša nová kontingenčná tabuľka založená a kde by mala byť vytvorená? Pretože sme už klikli na bunku v zozname (v kroku vyššie), celý zoznam okolo tejto bunky je pre nás už vybraný ($ A $ 1: $ G $ 88 na platby list v tomto príklade). Všimnite si, že môžeme vybrať zoznam v ľubovoľnej inej oblasti ľubovoľného iného pracovného hárka alebo dokonca nejakého externého zdroja údajov, ako napríklad databázovú tabuľku programu Access alebo dokonca databázovú tabuľku MS-SQL Server. Musíme tiež vybrať, či chceme, aby naša nová kontingenčná tabuľka bola vytvorená na a Nový pracovný hárok alebo na existujúce jedna. V tomto príklade vyberieme a Nový one:

Nový pracovný hárok je pre nás vytvorený a na tomto pracovnom hárku je vytvorená prázdna kontingenčná tabuľka:

Zobrazí sa tiež ďalšia políčka: Zoznam polia kontingenčnej tabuľky. Tento zoznam polí sa zobrazí vždy, keď klikneme na akúkoľvek bunku v kontingenčnom tabuľke (hore):

Zoznam polí v hornej časti poľa je vlastne zbierka hlavičiek stĺpcov z pracovného hárka pôvodných nespracovaných údajov. Štyri prázdne políčka v spodnej časti obrazovky nám umožňujú vybrať si spôsob, akým by sme chceli, aby naša kontingenčná tabuľka zhrnula nespracované údaje. Zatiaľ nie je nič v týchto poliach, takže kontingenčná tabuľka je prázdna. Všetko, čo musíme urobiť, je presunúť polia dole zo zoznamu vyššie a odložiť ich do dolných políčok. Potom sa automaticky vytvorí kontingenčná tabuľka, ktorá zodpovedá našim pokynom. Ak sa to nepodarí, stačí len presunúť polia späť na miesto, odkiaľ pochádzajú a / alebo pretiahnuť Nový polia na ich výmenu.

hodnoty box je pravdepodobne najdôležitejší zo štyroch. Pole, ktoré sa vtiahne do tohto poľa, predstavuje údaje, ktoré je potrebné zhrnúť nejakým spôsobom (súčet, spriemerovanie, nájdenie maxima, minima atď.). Je to takmer vždy číselný dát. Perfektným kandidátom na toto pole v našich vzorkových údajoch je pole / stĺpec "Suma". Presuňme toto pole do okna hodnoty box:

Všimnite si, že (a) pole "Suma" v zozname polí je teraz zaškrtnuté a "Suma sumy" bola pridaná k hodnoty že stĺpec sumy bol zhrnutý.

Ak preskúmame samotnú kontingenčnú tabuľku, načítame sumu všetkých hodnôt "Suma" z pracovného hárka nespracovaných údajov:

Vytvorili sme našu prvú kontingenčnú tabuľku! Praktické, ale nie pôsobivé. Je pravdepodobné, že potrebujeme trochu viac nahliadnuť do našich údajov ako na to.

Ak odkazujeme na naše vzorové údaje, musíme identifikovať jednu alebo viaceré nadpisy stĺpcov, ktoré by sme mohli použiť na rozdelenie tohto súčtu. Napríklad sa môžeme rozhodnúť, že by sme chceli vidieť súhrn našich údajov tam, kde máme riadok riadku pre každého z rôznych predajcov v našej spoločnosti a celkom pre každého. Aby sme to dosiahli, stačí len presunúť pole "Predajca" do poľa Riadkové štítky box:

teraz, nakoniec, veci začínajú byť zaujímavé! Naša kontingenčná tabuľka sa začína formovať ... .

S niekoľkými kliknutiami sme vytvorili tabuľku, ktorá by trvala dlho, kým by sme to robili ručne.

Takže čo ešte môžeme urobiť? No, v určitom zmysle je naša kontingencia úplná. Vytvorili sme užitočný súhrn našich zdrojových údajov. Dôležité veci sa už naučili! Pre zvyšok článku budeme skúmať niektoré spôsoby, ako vytvoriť zložitejšie kontingenčné tabuľky a spôsoby, akými je možné prispôsobiť tieto kontingenčné tabuľky.

Po prvé, môžeme vytvoriť dva-rozmerná tabuľka. Urobme to tak, že použijeme ako spôsob stĺpca metódu platby. Jednoducho presuňte nadpis "Spôsob platby" na položku Štítky stĺpcov box:

Čo vyzerá takto:

Začal sa dostať veľmi chladný!

Urobme to tri-rozmerná tabuľka. Čo by mohla takáto tabuľka vyzerať? No, uvidíme ...

Presuňte stĺpec / balík "Balík" do nadpisu Filtrovať prehľad box:

Všimnite si, kde to skončí ... .

Umožní nám to filtrovať prehľad, na základe ktorého bol zakúpený "dovolenkový balík". Napríklad vidíme rozdelenie predajcu voči platobnej metóde pre všetko balíky alebo niekoľkými kliknutiami ju zmeňte tak, aby zobrazoval rovnaký rozpis balíka "Sunseekers":

Takže ak si o tom myslíte správnym spôsobom, naša kontingencia je teraz trojrozmerná. Urobme si prispôsobenie ...

Ak sa ukáže, povedzme, že chceme len vidieť šekom a kreditnou kartou transakcie (to znamená žiadne hotovostné transakcie), potom môžeme zrušiť výber položky "Hotovosť" zo záhlaví stĺpcov. Kliknite na rozbaľovaciu šípku vedľa položky Štítky stĺpcov, a zrušiť "hotovosť":

Pozrime sa, ako to vyzerá ... Ako vidíte, "hotovosť" je preč.

formátovanie

To je samozrejme veľmi silný systém, ale doterajšie výsledky vyzerajú veľmi jasne a nudné. Na začiatok čísla, ktoré sumarizujeme, nevyzerajú ako sumy v dolároch - stačí len staré čísla. Opravte to.

Pokusom môže byť, aby sme v takýchto situáciách robili to, čo sme zvyknutí robiť, a jednoducho vybrať celú tabuľku (alebo celý pracovný hárok) a pomocou formátovacích tlačidiel štandardného čísla na paneli s nástrojmi dokončiť formátovanie. Problém s týmto prístupom je, že ak v budúcnosti niekedy zmeníte štruktúru kontingenčnej tabuľky (čo je pravdepodobne 99%), potom sa tieto formáty čísel stratia. Potrebujeme spôsob, ktorý ich bude (pol-) trvalý.

Najprv nájdeme položku "Sum of Amount" v položke hodnoty a kliknite na ňu. Zobrazí sa menu. Vyberáme Nastavenia polí hodnoty ... z ponuky:

Nastavenia polí hodnoty zobrazí sa políčko.

Kliknite na tlačidlo Formát čísel tlačidlo a štandard Pole Formát buniek Zobrazí sa:

Z kategórie zoznam, vyberte (povedzte) Účtovné, a znížte počet desatinných miest na 0. Kliknite na OK niekoľkokrát sa vráťte do kontingenčnej tabuľky ...

Ako môžete vidieť, čísla boli správne naformátované ako čiastky v dolároch.

Zatiaľ čo sme v oblasti formátovania, formátme celý kontingenčný tabuľku. Existuje niekoľko spôsobov, ako to urobiť. Použite jednoduchý ...

Kliknite na tlačidlo Kontingenčné nástroje / návrh Záložka:

Potom spustite šípku v pravom dolnom rohu Styly kontingenčnej tabuľky zobraziť zoznam obrovskej zbierky vstavaných štýlov:

Vyberte si ktorýkoľvek, kto sa odvoláva, a pozrite sa na výsledok vo vašej kontingencii:

Ďalšie možnosti

Môžeme pracovať aj s dátumami. Teraz zvyčajne existuje veľa, veľa dátumov v zozname transakcií, ako je ten, s ktorým sme začali. Ale program Excel poskytuje možnosť zoskupiť dátové položky spoločne podľa dňa, týždňa, mesiaca, roku atď. Pozrime sa, ako sa to deje.

Najprv odstráňte stĺpec "Spôsob platby" z Štítky stĺpcov (jednoducho ho presuňte späť do zoznamu polí) a nahraďte ho stĺpcom "Dátum rezervácie":

Ako môžete vidieť, z tohto dôvodu je naša kontingencia okamžite zbytočná a dáva nám jeden stĺpec pre každý dátum, kedy došlo k transakcii - veľmi široká tabuľka!

Ak to chcete opraviť, kliknite pravým tlačidlom na akýkoľvek dátum a vyberte Skupina ... z kontextového menu:

Zobrazí sa okno zoskupenia. Vyberáme mesiaca a kliknite na tlačidlo OK:

Voila! veľa užitočnejšia tabuľka:

(Mimochodom, táto tabuľka je prakticky totožná s tabuľkou uvedenou na začiatku tohto článku - pôvodné zhrnutie predaja, ktoré bolo vytvorené manuálne.)

Ďalšou zaujímavou vecou je vedieť, že môžete mať viac ako jednu sadu nadpisov riadkov (alebo stĺpcov):

... ktorý vyzerá takto ... .

Môžete urobiť podobnú vec so stĺpcami stĺpcov (alebo dokonca aj s prehľadmi filtrov).

Udržujte veci jednoduché znova, pozrime sa, ako vykresliť priemerne namiesto súčtových hodnôt.

Najskôr kliknite na položku "Suma sumy" a vyberte položku Nastavenia polí hodnoty ... z kontextovej ponuky, ktorá sa zobrazí:

V Zhrňte pole hodnoty pomocou zoznam v zozname Nastavenia polí hodnoty začiarknite políčko priemerný:

Aj keď sme tu, poďme to zmeniť Vlastné meno, z "priemernej sumy" na niečo trochu stručnejšie. Zadajte niečo ako "Avg":

kliknite OK, a uvidíte, ako to vyzerá. Všimnite si, že všetky hodnoty sa menia zo sumárnych súčtov na priemery a názov tabuľky (horná ľavá bunka) sa zmenil na "Avg":

Ak sa nám páči, môžeme mať dokonca aj sumy, priemery a počet (počet - koľko predajov tam bolo), ktoré sú na rovnakej kontingenčnej tabuľke!

Tu sú kroky na získanie niečoho podobného (od prázdnej kontingenčnej tabuľky):

Presuňte "Predajcu" do priečinka Štítky stĺpcov
Presuňte pole "Suma" do poľa hodnoty box trikrát
V prvom poli "Suma" zmeňte jeho vlastné meno na "Celkom" a jeho číselný formát Účtovné (0 desatinných miest)
V druhom poli "Suma" zmeňte jeho vlastné meno na "Priemerné", jeho funkcia na priemerný a je to číselný formát Účtovné (0 desatinných miest)
V treťom poli "Suma" zmeňte jeho názov na "Count" a jeho funkciu na počítať
Presuňte automaticky vytvorené pole od Štítky stĺpcov na Riadkové štítky

Tu je to, čo sme skončili:

Celkom, priemerné a počítajte s rovnakou kontingenčnou tabuľkou!

záver

Existuje mnoho, mnoho ďalších funkcií a možností pre kontingenčné tabuľky vytvorené programom Microsoft Excel - je to príliš veľa na to, aby sa v takomto článku zobrazil. Ak chcete úplne pokryť potenciál kontingenčných tabuliek, bude potrebná malá kniha (alebo veľká webová stránka). Brave a / alebo geeky čitatelia môžu preskúmať kontingenčné tabuľky úplne jednoducho: Jednoducho kliknite pravým tlačidlom myši na všetko a uvidíte, aké možnosti budú k dispozícii. K dispozícii sú tiež dve pásky s páskou: Nástroje / Možnosti kontingenčnej tabuľky a dizajn. Nezáleží na tom, či urobíte chybu - je jednoduché vymazať kontingenčnú tabuľku a začať znova - možnosť, že starí používatelia Lotusu 1-2-3 v systéme DOS nikdy nemali.

Ak pracujete v balíku Office 2007, môžete si pozrieť náš článok o tom, ako vytvoriť kontingenčnú tabuľku v programe Excel 2007.

Zahrnuli sme pracovný zošit programu Excel, ktorý si môžete stiahnuť, aby ste mohli praktizovať svoje kontingenčné zručnosti. Mal by pracovať so všetkými verziami programu Excel od roku 97.

Stiahnite si náš Pracovný zošit Excel