Práce s kontingenčními tabulkami v aplikaci Microsoft Excel

Kontingenční tabulky jsou jednou z nejvýkonnějších funkcí aplikace Microsoft Excel. Umožňují analyzovat velké množství dat a jejich shrnutí pomocí několika kliknutí myší. V tomto článku zkoumáme kontingenční tabulky, rozumíme tomu, čím jsou, a dozvíme se, jak je vytvářet a přizpůsobovat.

Poznámka: Tento článek je napsán pomocí aplikace Excel 2010 (Beta). Koncepce kontingenční tabulky se v průběhu let značně změnila, ale způsob vytvoření jednoho se změnil téměř v každé iteraci aplikace Excel. Pokud používáte verzi aplikace Excel, která není 2010, očekávejte různé obrazovky od těch, které vidíte v tomto článku.

Malá historie

V raných dnech tabulkových procesů Lotus 1-2-3 vládl. Jeho nadvláda byla tak úplná, že si lidé mysleli, že je ztrátou času, aby se Microsoft obtěžoval vývojem vlastního softwaru pro práci s tabulkami (Excel), aby mohl konkurovat Lotusu. Flash-forward do roku 2010 a dominance programu Excel na trhu s tabulkami je větší než Lotus, kdy počet uživatelů stále běží Lotus 1-2-3 se blíží nule. Jak se to stalo? Co způsobilo tak dramatické obrácení bohatství?

Průmysloví analytici uvedli dva faktory: Za prvé se Lotus rozhodl, že tato fantazijní nová platforma GUI nazvaná "Windows" byla přejíždějící mód, který by nikdy nevyrazil. Oni odmítli vytvořit Windows verzi Lotus 1-2-3 (na pár let, stejně), předpovídat, že jejich verze softwaru DOS byl všichni někdo někdy bude potřebovat. Společnost Microsoft samozřejmě vyvinula aplikaci Excel výlučně pro Windows. Za druhé Microsoft vyvinul funkci pro Excel, kterou Lotus neposkytoval v 1-2-3, konkrétně Kontingenční tabulky.  Funkce kontingenčních tabulek, exkluzivně pro aplikaci Excel, byla považována za tak ohromující, že lidé byli ochotni se naučit úplně nový softwarový balík (Excel), než aby se drželi programu (1-2-3), který jej neměl. Tato jediná funkce, spolu s nesprávným úsudkem o úspěchu systému Windows, byla smrt pro Lotus 1-2-3 a začátek úspěchu aplikace Microsoft Excel.

Porozumění kontingenčním tabulkám

Takže co je to kontingenční tabulka přesně?

Jednoduše řečeno, kontingenční tabulka je souhrn některých údajů vytvořených pro snadnou analýzu uvedených dat. Ale na rozdíl od manuálně vytvořeného souhrnu jsou kontingenční tabulky aplikace Excel interaktivní. Jakmile jste vytvořili jednu, můžete ji snadno změnit, pokud nenabízí přesné nahlédnutí do vašich dat, které jste doufali. Po několika kliknutích může být souhrn "otočen" - otočen tak, aby se záhlaví sloupců staly řádky řady a naopak. Je toho mnohem víc, co lze udělat. Než se pokusíme popsat všechny funkce kontingenčních tabulek, jednoduše je předvedeme ...

Údaje, které analyzujete pomocí kontingenční tabulky, nemohou být správné žádný data - to musí být drsný data, dosud nezpracovaná (bezvýrazná) - typicky seznam nějakého druhu. Příkladem může být seznam prodejních transakcí ve společnosti za posledních šest měsíců.

Zkontrolujte následující údaje:

Všimněte si, že toto je ne nezpracované údaje. Ve skutečnosti je to již nějaký souhrn. V buňce B3 vidíme 30 000 dolarů, což je zřejmě celkový objem prodeje Jamese Cooka za měsíc leden. Takže kde jsou hrubé údaje? Jak jsme dospěli k číslu 30 000 dolarů? Kde je původní seznam prodejních transakcí, ze kterých bylo toto číslo vytvořeno? Je zřejmé, že někde někdo musel za posledních šest měsíců přejít na problém soustředění všech prodejních transakcí do shrnutí, které jsme viděli výše. Jak dlouho myslíš, že to trvalo? Hodina? Deset?

Nejspíš ano. Vidíte, tabulka výše je ve skutečnosti ne kontingenční tabulka. Byla vytvořena ručně ze surových dat uložených jinde a skutečně to trvalo několik hodin, než bylo možné sestavit. Nicméně je to právě shrnutí mohl být vytvořen pomocí kontingenčních tabulek, v takovém případě by to trvalo jen několik sekund. Zjistíme, jak ...

Pokud bychom měli sledovat původní seznam prodejních transakcí, mohlo by to vypadat takto:

Možná vás překvapí, že pomocí funkce kontingenční tabulky v aplikaci Excel můžeme během několika sekund vytvářet měsíční souhrnný přehled o prodeji, jen několik kliknutí myší. Můžeme to udělat - a mnohem více!

Jak vytvořit kontingenční tabulku

Nejprve se ujistěte, že máte nějaké nespracované údaje v listu v aplikaci Excel. Seznam finančních transakcí je typický, ale může to být seznam téměř všech údajů: kontaktní údaje zaměstnance, vaše sbírka CD nebo údaje o spotřebě paliva pro vozový park Vaší společnosti.

Takže začneme Excel ... a načteme takový seznam ...

Jakmile máme seznam otevřený v aplikaci Excel, jsme připraveni začít vytvářet kontingenční tabulku.

Klikněte na jednu jednu buňku v seznamu:

Potom, od Vložit klepněte na kartu Kontingenční tabulka ikona:

The Vytvořit kontingenční tabulku zobrazí se dvěma otázkami: Jaké údaje by měla být založena na vaší nové kontingenční tabulce a kde by měla být vytvořena? Protože jsme již klikli na buňku v seznamu (v kroku výše), celý seznam kolem této buňky je pro nás již vybrán ($ A $ 1: $ G $ 88 na Platby list v tomto příkladu). Všimněte si, že bychom mohli vybrat seznam v libovolné jiné oblasti libovolného jiného listu nebo dokonce nějakého externího zdroje dat, jako je databázová tabulka aplikace Access nebo dokonce i databázová tabulka MS-SQL Server. Také je třeba zvolit, zda chceme, aby byla naše nová kontingenční tabulka vytvořena na a Nový pracovního listu nebo na existující jeden. V tomto příkladu vybereme a Nový jeden:

Nový pracovní list je vytvořen pro nás a na tomto listu je vytvořen prázdný kontingenční tabulka:

Zobrazí se také další pole: Seznam polí kontingenčního pole.  Tento seznam polí se zobrazí vždy, když klikneme na libovolnou buňku v kontingenční tabulce (výše):

Seznam polí v horní části pole je vlastně sbírka nadpisů sloupců z původního listu nespracovaných dat. Čtyři prázdné políčka ve spodní části obrazovky nám umožňují vybrat způsob, jakým bychom chtěli, aby naše kontingenční tabulka shrnula surové údaje. Zatím není v těchto polích nic, takže kontingenční tabulka je prázdná. Jediné, co musíme udělat, je přetahovat pole ze seznamu výše a přetáhnout je do dolních políček. Pak se automaticky vytvoří kontingenční tabulka, která odpovídá našim pokynům. Pokud to uděláme špatně, stačí pouze přetáhnout políčka tam, odkud pocházejí a / nebo přetahovat Nový zaškrtněte pole pro jejich nahrazení.

The Hodnoty Box je pravděpodobně nejdůležitější ze čtyř. Pole, které je do tohoto pole přetaženo, představuje údaje, které je třeba shrnout nějakým způsobem (součtem, průměrem, nalezením maxima, minima apod.). Je to téměř vždycky numerické data. Dokonalým kandidátem na toto pole v ukázkových datech je pole / částka "Částka". Přetáhněte toto pole do pole Hodnoty box:

Všimněte si, že (a) pole "Částka" v poli políček je nyní zaškrtnuto a "Suma částky" byla přidána do Hodnoty že sloupec částky byl sčítán.

Pokud přezkoumáme samotnou kontingenční tabulku, skutečně najdeme součet všech hodnot "Množství" z listu surových dat:

Vytvořili jsme první kontingenční tabulku! Praktické, ale ne působivé. Je pravděpodobné, že potřebujeme trochu víc informací o našich datech než o tom.

S odkazem na naše ukázkové údaje musíme určit jednu nebo více záhlaví sloupců, které bychom mohli použít pro rozdělení tohoto součtu. Můžeme se například rozhodnout, že bychom rádi viděli souhrn našich údajů, kde máme nadpis řádku pro každého z různých prodejců v naší společnosti a celkem pro každého. Abychom toho dosáhli, stačí přetáhnout pole "Prodejce" do pole Řádky box:

Nyní, konečně se věci začínají zajímat! Náš kontingenční tabule se začíná formovat ... .

S několika kliknutími jsme vytvořili tabulku, která by trvala dlouhá doba, než to udělat ručně.

Takže co jiného můžeme dělat? V tom smyslu je náš kontingenční tabulka kompletní. Vytvořili jsme užitečný přehled našich zdrojových dat. Důležité věci se již naučily! Pro zbytek článku budeme zkoumat některé způsoby, jak lze vytvořit složitější kontingenční tabulky a způsoby, jak lze tyto kontingenční tabulky přizpůsobit.

Za prvé, můžeme vytvořit a dva-rozměrné tabulky. Uděláme to tak, že použijeme "Způsob platby" jako záhlaví sloupce. Jednoduše přetáhněte položku "Způsob platby" na položku Štítky sloupců box:

Což vypadá takto:

Začal se dostat velmi chladný!

Uděláme to tři-rozměrné tabulky. Co by mohlo vypadat taková tabulka? No, podívejme ...

Přetáhněte sloupec / balíček "balíček" na položku Nahlásit filtr box:

Všimněte si, kde to skončí ... .

To nám umožňuje filtrovat náš přehled podle toho, který "balíček" byl zakoupen. Například můžeme vidět rozpis prodejce vůči způsobu platby pro Všechno balíčky nebo s několika kliknutími jej změňte tak, aby zobrazoval stejný rozpis balíčku "Sunseekers":

A tak, pokud o tom uvažujete správně, naše kontingenční tabulka je nyní trojrozměrná. Uděláme si přizpůsobení ...

Pokud se ukáže, řekněme, že chceme pouze vidět check a kreditní kartou transakcí (tj. žádné hotovostní transakce), pak můžeme z okruhu sloupců zrušit výběr položky "Hotovost". Klikněte na rozbalovací šipku vedle Štítky sloupců, a zrušit "hotovost":

Podívejme se, jak to vypadá ... Jak vidíte, "Cash" je pryč.

Formátování

To je samozřejmě velmi mocný systém, ale zatím výsledky vypadají velmi jasně a nudně. Na začátek čísla, která sumujeme, nevypadají jako dolarové částky - prostě stará čísla. Opravte to.

Pokušení by mohlo vést k tomu, co jsme za takových okolností zvyklíme, a jednoduše vybrat celou tabulku (nebo celý list) a použít formátovací tlačítka standardního čísla na panelu nástrojů k dokončení formátování. Problém s tímto přístupem je, že pokud někdy změníte strukturu kontingenční tabulky v budoucnu (což je 99% pravděpodobné), pak se tyto formáty čísel ztratí. Potřebujeme způsob, který by je (poloviční) trvalý.

Nejprve nalezneme položku "Sum Sum" v položce Hodnoty a klikněte na něj. Objeví se menu. Vybíráme Nastavení pole hodnot ... z nabídky:

The Nastavení pole hodnot Zobrazí se okno.

Klepněte na tlačítko Formát čísel tlačítko a standard Formát pole buňky objeví se:

Z Kategorie seznam, vyberte (řekněme) Účetnictví, a zadejte počet desetinných míst na 0. Klepněte na tlačítko OK několikrát se vrátit na kontingenční tabulku ...

Jak můžete vidět, čísla byla správně formátována jako částky dolaru.

Zatímco jsme se zabývali formátováním, zformátujeme celý kontingenční tabulku. Existuje několik způsobů, jak to udělat. Použijeme jednoduchý ...

Klepněte na tlačítko Nástroje / návrh kontingenčních tabulek záložka:

Pak spusťte šipku vpravo dole Styly kontingenčních tabulek Seznamte se s rozsáhlou kolekcí vestavěných stylů:

Zvolte někdo, kdo se odvolá a podívejte se na výsledek ve vaší kontingenční tabulce:

Jiné možnosti

Můžeme pracovat i s daty. Nyní obvykle existuje mnoho, mnoho dat v seznamu transakcí, jako je ten, který jsme začali. Aplikace Excel však poskytuje možnost seskupovat datové položky dohromady denně, týden, měsíc, rok atd. Podívejme se, jak se to dělá.

Nejprve odstraníme sloupec "Způsob platby" z Štítky sloupců (jednoduše jej přetáhněte zpět do seznamu polí) a nahraďte jej sloupcem "Datum rezervace":

Jak můžete vidět, dělá to naše kontingenční tabulka okamžitě zbytečné, dává nám jeden sloupec pro každé datum, kdy došlo k transakci - velmi široký tabulka!

Chcete-li to opravit, klepněte pravým tlačítkem na libovolné datum a vyberte Skupina… z kontextového menu:

Zobrazí se okno seskupení. Vybíráme Měsíce a klepněte na tlačítko OK:

Voila! A hodně užitečnější tabulka:

(Mimochodem, tato tabulka je prakticky totožná s tabulkou uvedenou na začátku tohoto článku - původním souhrnem prodeje, který byl vytvořen ručně.)

Další skvělá věc, kterou si musíte uvědomit, je, že můžete mít více než jednu řadu nadpisů řádků (nebo záhlaví sloupců):

... který vypadá takto ... .

Stejnou věc můžete udělat s nadpisy sloupců (nebo dokonce s filtry přehledů).

Udržet věci jednoduché znovu, podívejme se, jak to dělat průměrně namísto součtových hodnot.

Nejprve klikněte na "Sumu částky" a vyberte Nastavení pole hodnot ... z kontextové nabídky, která se zobrazí:

V Sumarizujte pole hodnot podle seznam v seznamu Nastavení pole hodnot zaškrtněte políčko Průměrný:

Zatímco jsme tady, změníme to Vlastní název, od "Průměrné částky" až po něco trochu stručnějšího. Zadejte něco jako "Avg":

Klikněte na OK, a uvidíte, jak to vypadá. Všimněte si, že všechny hodnoty se změní ze součtových součtů na průměry a název tabulky (buňka vlevo nahoře) se změnila na "Avg":

Pokud se nám to líbí, můžeme mít dokonce i součty, průměry a počty (počet, kolik prodejů tam bylo), vše na stejné kontingenční tabulce!

Zde jsou kroky k získání něčeho podobného (od prázdné kontingenční tabulky):

1. Přetáhněte "Salesperson" do Štítky sloupců
2. Přetáhněte pole "Částka" dolů do pole Hodnoty box třikrát
3. U prvního pole "Částka" změňte jeho vlastní název na "Celkem" a jeho formát čísla je Účetnictví (0 desetinných míst)
4. U druhého pole "Částka" změňte jeho vlastní název na "Průměr", jeho funkce na Průměrný a je to číselný formát Účetnictví (0 desetinných míst)
5. Pro třetí pole "Částka" změňte jeho název na "Počítání" a jeho funkce na Spočítat
6. Přetáhněte automaticky vytvořené pole od Štítky sloupců na Řádky

Zde je konec:

Celkem, průměr a počítat na stejném kontingenčním tabulce!

Závěr

Existuje mnoho, mnoho dalších funkcí a možností pro kontingenční tabulky vytvořené aplikací Microsoft Excel - příliš mnoho, aby bylo možné v tomto článku zobrazit seznam. Chcete-li plně pokrýt potenciál kontingenčních tabulek, bude vyžadována malá kniha (nebo velká webová stránka). Odvážní a / nebo podivuhodní čtenáři mohou prozkoumat kontingenční tabulky ještě poměrně snadno: Jednoduše klikněte pravým tlačítkem na všechno a zjistěte, jaké možnosti jsou k dispozici. K dispozici jsou také dvě stužkové karty: Nástroje / volby kontingenčních tabulek a Design.  Nezáleží na tom, pokud uděláte chybu - je snadné smazat kontingenční tabulku a začít znovu - možnost, že starší uživatelé DOSu Lotus 1-2-3 nikdy neměli.

Pokud pracujete v sadě Office 2007, můžete se podívat na náš článek o tom, jak vytvořit kontingenční tabulku v aplikaci Excel 2007.

Zahrnuli jsme sešit aplikace Excel, který si můžete stáhnout, abyste mohli praktikovat své kontingenční dovednosti. Mělo by pracovat se všemi verzemi aplikace Excel od roku 97.

Stáhněte si pracovní sešit aplikace Excel