Obsah
Kontingenční tabulky je jedním z nejvýkonnějších nástrojů v Excelu. Umožňují vám analyzovat a sčítat různé souhrny velkého množství dat pomocí několika kliknutí myší. V tomto článku se seznámíme s kontingenčními tabulkami, pochopíme, co to je, naučíme se je vytvářet a přizpůsobovat.
Tento článek byl napsán pomocí Excelu 2010. Koncept kontingenčních tabulek se v průběhu let příliš nezměnil, ale způsob, jakým je vytváříte, se v každé nové verzi Excelu mírně liší. Pokud máte verzi Excelu ne 2010, připravte se, že snímky obrazovky v tomto článku se budou lišit od toho, co vidíte na obrazovce.
Trochu historie
V počátcích tabulkového softwaru vládl míč Lotus 1-2-3. Jeho dominance byla tak úplná, že snahy Microsoftu vyvinout vlastní software (Excel) jako alternativu k Lotusu vypadaly jako ztráta času. Nyní rychle vpřed do roku 2010! Excel dominuje tabulkovým procesorům více než kód Lotusu ve své historii a počet lidí, kteří stále používají Lotus, se blíží nule. Jak se to mohlo stát? Co bylo důvodem tak dramatického zvratu událostí?
Analytici identifikují dva hlavní faktory:
- Za prvé, Lotus se rozhodl, že tato nová platforma GUI nazvaná Windows je jen přechodný výstřelek, který nebude mít dlouhého trvání. Odmítli sestavit verzi Lotusu 1-2-3 pro Windows (ale pouze na několik let), protože předpovídali, že verze jejich softwaru pro DOS bude vše, co spotřebitelé kdy budou potřebovat. Microsoft přirozeně vyvinul Excel speciálně pro Windows.
- Za druhé, Microsoft představil nástroj v Excelu nazvaný kontingenční tabulky, který nebyl k dispozici v Lotus 1-2-3. Kontingenční tabulky, exkluzivně pro Excel, se ukázaly být tak ohromně užitečné, že lidé měli tendenci zůstat u nové softwarové sady Excel, než aby pokračovali v Lotusu 1-2-3, který je neměl.
Kontingenční tabulky spolu s podceněním úspěchu Windows obecně sehrály pochod smrti pro Lotus 1-2-3 a předznamenaly úspěch Microsoft Excelu.
Co jsou kontingenční tabulky?
Jaký je tedy nejlepší způsob, jak charakterizovat, co jsou kontingenční tabulky?
Jednoduše řečeno, kontingenční tabulky jsou souhrny některých dat, vytvořené za účelem usnadnění analýzy těchto dat. Na rozdíl od ručně vytvořených součtů jsou kontingenční tabulky Excel interaktivní. Jakmile je vytvoříte, můžete je snadno upravit, pokud neposkytují obrázek, ve který jste doufali. Pouhými několika kliknutími myši lze součty převrátit tak, aby se záhlaví sloupců stalo záhlavím řádků a naopak. S kontingenčními tabulkami můžete dělat mnoho věcí. Místo abyste se snažili slovy popsat všechny vlastnosti kontingenčních tabulek, je snazší je demonstrovat v praxi…
Data, která analyzujete pomocí kontingenčních tabulek, nemohou být náhodná. Měla by to být nezpracovaná nezpracovaná data, jako nějaký seznam. Může to být například seznam prodejů uskutečněných společností za posledních šest měsíců.
Podívejte se na data uvedená na obrázku níže:
Upozorňujeme, že se nejedná o nezpracovaná nezpracovaná data, jak již bylo shrnuto. V buňce B3 vidíme 30000 30000 $, což je pravděpodobně celkový výsledek, který James Cook udělal v lednu. Kde jsou tedy původní údaje? Kde se vzala částka XNUMX XNUMX $? Kde je původní seznam prodejů, ze kterého byl tento měsíční součet odvozen? Je jasné, že někdo odvedl skvělou práci, když uspořádal a seřadil všechna prodejní data za posledních šest měsíců a převedl je do tabulky součtů, které vidíme. Jak dlouho to podle vás trvalo? Hodina? Deset?
Faktem je, že výše uvedená tabulka není kontingenční tabulkou. Byl ručně vytvořen z nezpracovaných dat uložených jinde a jeho zpracování trvalo nejméně několik hodin. Právě takovou souhrnnou tabulku lze vytvořit pomocí kontingenčních tabulek během několika sekund. Pojďme zjistit, jak…
Pokud se vrátíme k původnímu seznamu prodejů, vypadal by asi takto:
Možná vás překvapí, že z tohoto seznamu obchodů s pomocí kontingenčních tabulek a během pár sekund vytvoříme měsíční přehled prodejů v Excelu, který jsme rozebrali výše. Ano, dokážeme to a ještě mnohem víc!
Jak vytvořit kontingenční tabulku?
Nejprve se ujistěte, že máte nějaká zdrojová data v excelovém listu. Seznam finančních transakcí je nejtypičtější, ke kterému dochází. Ve skutečnosti to může být seznam čehokoli: kontaktní údaje na zaměstnance, sbírka CD nebo údaje o spotřebě paliva vaší společnosti.
Spustíme tedy Excel… a načteme takový seznam…
Po otevření tohoto seznamu v Excelu můžeme začít vytvářet kontingenční tabulku.
Vyberte libovolnou buňku z tohoto seznamu:
Poté na kartě Vložení (Vložit) vyberte příkaz Kontingenční tabulka (kontingenční tabulka):
Zobrazí se dialogové okno Vytvořte kontingenční tabulku (Vytvoření kontingenční tabulky) se dvěma otázkami pro vás:
- Jaká data použít k vytvoření nové kontingenční tabulky?
- Kam umístit kontingenční tabulku?
Protože v předchozím kroku jsme již vybrali jednu z buněk seznamu, bude pro vytvoření kontingenční tabulky automaticky vybrán celý seznam. Všimněte si, že můžeme vybrat jiný rozsah, jinou tabulku a dokonce i nějaký externí zdroj dat, jako je databázová tabulka Accessu nebo MS-SQL. Kromě toho musíme vybrat, kam umístit novou kontingenční tabulku: na nový list nebo na jeden ze stávajících. V tomto příkladu vybereme možnost – Nový pracovní list (na nový list):
Excel vytvoří nový list a umístí na něj prázdnou kontingenční tabulku:
Jakmile klikneme na libovolnou buňku v kontingenční tabulce, objeví se další dialogové okno: Seznam polí kontingenční tabulky (pole kontingenční tabulky).
Seznam polí v horní části dialogového okna je seznam všech titulů z původního seznamu. Čtyři prázdné oblasti v dolní části obrazovky vám umožňují sdělit kontingenční tabulce, jak chcete shrnout data. Dokud jsou tyto oblasti prázdné, není nic ani v tabulce. Jediné, co musíme udělat, je přetáhnout nadpisy z horní oblasti do prázdných oblastí níže. Současně se automaticky vygeneruje kontingenční tabulka podle našich pokynů. Pokud uděláme chybu, můžeme odstranit nadpisy ze spodní oblasti nebo přetáhnout jiné a nahradit je.
Oblast Hodnoty (Významy) je pravděpodobně nejdůležitější ze čtyř. To, který nadpis je v této oblasti umístěn, určuje, jaká data budou sumarizována (součet, průměr, maximum, minimum atd.) Téměř vždy se jedná o číselné hodnoty. Výborným kandidátem na místo v této oblasti jsou údaje pod hlavičkou Množství (Cena) našeho původního stolu. Přetáhněte tento titul do oblasti Hodnoty (Hodnoty):
Vezměte prosím na vědomí, že název Množství je nyní označeno zaškrtnutím a v oblasti Hodnoty (Hodnoty) objevil se záznam Součet částky (pole Částka Částka), což znamená, že sloupec Množství shrnul.
Pokud se podíváme na samotnou kontingenční tabulku, uvidíme součet všech hodnot ze sloupce Množství originální stůl.
Takže naše první kontingenční tabulka je vytvořena! Pohodlné, ale nijak zvlášť působivé. Pravděpodobně chceme získat více informací o našich datech, než aktuálně máme.
Vraťme se k původním datům a pokusme se identifikovat jeden nebo více sloupců, které lze použít k rozdělení tohoto součtu. Můžeme například sestavit naši kontingenční tabulku tak, že se celková částka tržeb vypočítá pro každého prodejce individuálně. Tito. do naší kontingenční tabulky budou přidány řádky se jménem každého prodejce ve společnosti a jeho celkovou částkou prodeje. Chcete-li dosáhnout tohoto výsledku, stačí přetáhnout název prodavač (obchodní zástupce) do regionu Řádkové štítky (struny):
Stává se to zajímavější! Naše kontingenční tabulka se začíná formovat...
Vidíte výhody? Za pár kliknutí jsme vytvořili tabulku, jejíž ruční vytvoření by trvalo velmi dlouho.
Co ještě můžeme udělat? V jistém smyslu je naše kontingenční tabulka připravena. Vytvořili jsme užitečné shrnutí původních dat. Důležité informace již byly obdrženy! Ve zbytku tohoto článku se podíváme na některé způsoby vytváření složitějších kontingenčních tabulek a také se naučíme, jak je přizpůsobit.
Nastavení kontingenční tabulky
Nejprve můžeme vytvořit dvourozměrnou kontingenční tabulku. Udělejme to pomocí záhlaví sloupce Způsob platby (Způsob platby). Stačí přetáhnout název Způsob platby do oblasti Sloupcové štítky (Sloupce):
Dostáváme výsledek:
Vypadá skvěle!
Nyní vytvoříme trojrozměrnou tabulku. Jak by takový stůl vypadal? Uvidíme…
Přetáhněte záhlaví Balíček (Komplex) do oblasti filtry přehledů (Filtry):
Všimněte si, kde je…
To nám dává možnost filtrovat přehled na základě „Za který rekreační komplex byl zaplacen“. Můžeme například vidět rozdělení podle prodejců a platebních metod pro všechny komplexy nebo několika kliknutími myši změnit pohled na kontingenční tabulku a zobrazit stejné rozdělení pouze pro ty, kteří si objednali komplex Sunseekers.
Pokud tomu tedy správně rozumíte, pak lze naši kontingenční tabulku nazvat trojrozměrnou. Pokračujme v nastavení…
Pokud se najednou ukáže, že by se v kontingenční tabulce měla zobrazovat pouze platba šekem a kreditní kartou (tedy bezhotovostní platba), pak můžeme zobrazování titulku vypnout Hotovost (Hotovost). K tomu vedle Sloupcové štítky klikněte na šipku dolů a zrušte zaškrtnutí políčka v rozevírací nabídce Hotovost:
Pojďme se nyní podívat, jak naše kontingenční tabulka vypadá. Jak vidíte, sloup Hotovost zmizel z ní.
Formátování kontingenčních tabulek v Excelu
Kontingenční tabulky jsou samozřejmě velmi mocný nástroj, ale zatím výsledky vypadají trochu jednoduše a nudně. Například čísla, která sečteme, nevypadají jako částky v dolarech – jsou to jen čísla. Pojďme to napravit.
Je lákavé udělat v takové situaci to, na co jste zvyklí a jednoduše vybrat celou tabulku (nebo celý list) a pomocí standardních tlačítek formátování čísel na panelu nástrojů nastavit požadovaný formát. Problém s tímto přístupem je, že pokud někdy v budoucnu změníte strukturu kontingenční tabulky (což se stane s 99% pravděpodobností), formátování se ztratí. Potřebujeme způsob, jak to udělat (téměř) trvalé.
Nejprve najdeme záznam Součet částky in Hodnoty (Hodnoty) a klikněte na něj. V zobrazené nabídce vyberte položku Nastavení hodnotového pole (Možnosti pole hodnoty):
Zobrazí se dialogové okno Nastavení hodnotového pole (Možnosti pole hodnot).
lis Formát čísla (Formát čísla), otevře se dialogové okno. Formát buněk (formát buňky):
Ze seznamu Kategorie (Číselné formáty) vyberte Účetnictví (Finanční) a nastavte počet desetinných míst na nulu. Nyní několikrát stiskněte OKvrátit se k naší kontingenční tabulce.
Jak vidíte, čísla jsou formátována jako částky v dolarech.
Když už jsme u toho formátování, pojďme nastavit formát pro celou kontingenční tabulku. Existuje několik způsobů, jak to udělat. Používáme ten, který je jednodušší…
Klepněte na tlačítko Nástroje kontingenční tabulky: Návrh (Práce s kontingenčními tabulkami: Konstruktor):
Dále rozbalte nabídku kliknutím na šipku v pravém dolním rohu sekce Styly kontingenčních tabulek (Styly kontingenční tabulky), abyste viděli rozsáhlou sbírku vložených stylů:
Vyberte si jakýkoli vhodný styl a podívejte se na výsledek v kontingenční tabulce:
Další nastavení kontingenční tabulky v Excelu
Někdy je potřeba filtrovat data podle data. Například v našem seznamu obchodů je mnoho, mnoho dat. Excel poskytuje nástroj pro seskupování dat podle dne, měsíce, roku a tak dále. Pojďme se podívat, jak se to dělá.
Nejprve odstraňte záznam. Způsob platby z regionu Sloupcové štítky (Sloupce). Chcete-li to provést, přetáhněte jej zpět do seznamu titulů a na jeho místo přesuňte titul Datum rezervace (datum rezervace):
Jak vidíte, naše kontingenční tabulka je dočasně zbytečná. Excel vytvořil samostatný sloupec pro každé datum, kdy byl obchod uskutečněn. V důsledku toho jsme dostali velmi široký stůl!
Chcete-li to opravit, klikněte pravým tlačítkem na libovolné datum a vyberte z kontextové nabídky GROUP (Skupina):
Zobrazí se dialogové okno seskupení. Vybíráme si Měsíců (Měsíce) a klikněte OK:
Voila! Tato tabulka je mnohem užitečnější:
Mimochodem, tato tabulka je téměř totožná s tou uvedenou na začátku článku, kde byly součty prodejů sestavovány ručně.
Existuje další velmi důležitý bod, který musíte vědět! Můžete vytvořit ne jednu, ale několik úrovní záhlaví řádků (nebo sloupců):
…a bude to vypadat takto…
Totéž lze provést s nadpisy sloupců (nebo dokonce filtry).
Vraťme se k původní podobě tabulky a podívejme se, jak místo součtů zobrazit průměry.
Chcete-li začít, klikněte na Součet částky a ze zobrazené nabídky vyberte Nastavení hodnotového pole (Možnosti pole hodnoty):
Seznam Shrňte pole hodnot podle (Operace) v dialogovém okně Nastavení hodnotového pole (Možnosti pole hodnoty) vyberte Průměrný (Průměrný):
Zároveň, když už jsme tady, změňme se CustomName (Vlastní název) s Průměr částky (pole Částka Částka) na něco kratšího. Zadejte do tohoto pole něco jako Průměrné:
Stisknout OK a uvidíte, co se stane. Všimněte si, že všechny hodnoty se změnily z celkových na průměry a záhlaví tabulky (v levé horní buňce) se změnilo na Průměrné:
Pokud chcete, můžete okamžitě získat částku, průměr a počet (tržby) umístěné v jedné kontingenční tabulce.
Zde je krok za krokem průvodce, jak to udělat, počínaje prázdnou kontingenční tabulkou:
- Přetáhněte záhlaví prodavač (obchodní zástupce) do regionu Sloupcové štítky (Sloupce).
- Přetáhněte název třikrát Množství (Cena) na oblast Hodnoty (Hodnoty).
- Pro první pole Množství změnit název na Celková cena (Částka) a formát čísla v tomto poli je Účetnictví (Finanční). Počet desetinných míst je nula.
- Druhé pole Množství název Průměre, nastavte pro něj operaci Průměrný (Průměr) a formát čísla v tomto poli se také změní na Účetnictví (Finanční) s nulovými desetinnými místy.
- Pro třetí pole Množství nastavit titulek Počítat a operace pro něj – Počítat (Množství)
- v Sloupcové štítky Automaticky vytvořeno pole (Sloupce). Σ Hodnoty (Σ Hodnoty) – přetáhněte jej do oblasti Řádkové štítky (čáry)
Zde je to, co skončíme:
Celková částka, průměrná hodnota a počet prodejů – vše v jedné kontingenční tabulce!
Proč investovat do čističky vzduchu?
Kontingenční tabulky v aplikaci Microsoft Excel obsahují mnoho funkcí a nastavení. V tak malém článku ani zdaleka nepokrývají všechny. Úplné popsání všech možností kontingenčních tabulek by vyžadovalo malou knihu nebo velký web. Odvážní a zvídaví čtenáři mohou pokračovat v průzkumu kontingenčních tabulek. Chcete-li to provést, klikněte pravým tlačítkem myši na téměř jakýkoli prvek kontingenční tabulky a podívejte se, jaké funkce a nastavení se otevřou. Na pásu karet najdete dvě záložky: Nástroje kontingenční tabulky: Možnosti (analýza) a Design (Konstruktor). Nebojte se udělat chybu, vždy můžete kontingenční tabulku smazat a začít znovu. Máte příležitost, kterou dlouholetí uživatelé DOS a Lotus 1-2-3 nikdy neměli.