Jak vytvořit kontingenční tabulku v Excelu. Jak pracovat s daty

Kontingenční tabulka MS Excel je výkonný nástroj pro analýzu dat. Tento nástroj vám umožňuje extrahovat informace z velkého množství dat v jakémkoli kontextu pomocí několika kliknutí myší. V tomto článku se pokusím jednoduchým a srozumitelným jazykem krok za krokem vysvětlit, co a jak dělat, abyste tento mimořádně pohodlný nástroj MS Excel měli k dispozici.

Požadavky na zdrojová data.

Základem každé kontingenční tabulky je tedy správně sestavené pole dat – „správná tabulka“. Na obrázku níže můžete vidět příklad správně vytvořeného datového pole:

Podívejme se, co je ve skutečnosti „správnost“ této tabulky? Správnost je taková, že:

• Každý sloupec obsahuje pouze stejný typ dat, sloupec A pouze data, ve sloupci V pouze Dokumenty, ve sloupci S pouze Zákazníci, Peníze v penězích, Dodavatelé v Dodavatelích, Kategorie produktů v kategoriích a tak dále... V takové tabulce můžeme velmi jednoduše použít Filtr;
• Sloupce se stejným typem dat se neopakují;
• V tabulce nejsou žádné celkové řádky, pouze „čisté údaje“;
• V textových datech v tabulce nejsou prázdné buňky, každý řádek obsahuje jméno Klienta, Produktu, Dodavatele, Manažera atd...

Mimochodem, v poli vůbec nezáleží na tom, v jakém pořadí jsou sloupce umístěny a podle jakého pole jsou data řazena. To nijak neovlivňuje konstrukci kontingenční tabulky.

A zde je příklad „špatné tabulky“, ze které, i když cracknete, se kontingenční tabulka nepostaví a i když se sestaví, bude zcela nemožné s daty pracovat... Naše „ oblíbené“ účetní systémy nás obvykle „potěší“ takovými tabulkami, které nám je poskytnou ve formě sestav, které jsou pro další analýzu zcela nevhodné:

Zde je další příklad "neplatného pole":

Je zde celá řada "nesrovnalostí":

• Za prvé, sloupce „Dodavatel“ a „Kategorie“ mají prázdné buňky, takže nemůžeme použít filtr;
• Za druhé, téměř v každém sloupci jsou řádky „Celkem...“, pro sestavení kontingenční tabulky jsou zcela zbytečné, navíc budou jen překážet;
• Za třetí, sloupec "Grand Total" také není potřeba;
• Za čtvrté, stejný typ dat, a to „Peníze“, je až ve třech sloupcích: „Leden“, „Únor“ a „březen“, což výrazně zkomplikuje konstrukci kontingenční tabulky, což znamená, že budeme muset udělej s tím něco...

Ale to je vše na samostatný rozhovor, pokud se chcete naučit, jak rychle převést takové „pokřivené tabulky“ na „správná pole“, přečtěte si články: „Jak rychle vytvořit kontingenční tabulku ze sestavy 1C nebo SAP?“ a "Jak rychle převést tabulku na pole pro kontingenční tabulky?"

Ve skutečnosti vytváření kontingenční tabulky:

Vezměte „správné pole“, umístěte kurzor do libovolné buňky pole, v hlavní nabídce vyberte kartu „Vložit“, v levém rohu v části „Tabulky“ klikněte na tlačítko „Kontingenční tabulka“:

V dialogovém okně „Vytvořit kontingenční tabulku“, které se otevře, klikněte na „OK“:

MS Excel vytvoří nový list, na kterém označí místo, kam bude kontingenční tabulka vložena, a vpravo zobrazí okno pro nastavení polí kontingenční tabulky, ve kterém uvidíte všechny názvy sloupců vaše pole:

Začněme s nastavením kontingenční tabulky. Jaký chceme střih? Kategorie produktů podle manažera - prosím. Najeďte myší na pole „Kategorie“, klikněte na něj levým tlačítkem myši a přetáhněte jej do pole „ŘÁDKY“. Pole „Správce“ se přetáhne do „SLOUPCE“ a „Částka“ se přetáhne do „HODNOTY“:

Kontingenční tabulky jsou jedním z nejúčinnějších nástrojů v MS Excel. S jejich pomocí můžete během několika sekund přeměnit milion řádků dat na stručnou zprávu. Kromě rychlého shrnutí výsledků vám kontingenční tabulky umožňují doslova změnit způsob analýzy za běhu přetahováním polí z jedné oblasti sestavy do druhé.

Kontingenční tabulky jsou také jedním z nejvíce podceňovaných nástrojů Excelu. Většina uživatelů si neuvědomuje možnosti, které jsou v jejich rukou. Představme si, že kontingenční tabulky ještě nebyly vynalezeny. Pracujete pro společnost, která prodává své produkty různým zákazníkům. Pro zjednodušení jsou v sortimentu pouze 4 položky. Produkty pravidelně nakupuje několik desítek klientů v různých regionech. Každá transakce je zapsána do databáze a představuje samostatný řádek.

Váš ředitel vám dává pokyn, abyste udělali stručnou zprávu o prodeji veškerého zboží podle regionu (regionu). Problém lze vyřešit následovně.

Nejprve vytvořte rozvržení tabulky, tedy záhlaví skládající se z jedinečných hodnot pro produkty a regiony. Vytvoříme kopii sloupce produktu a odstraníme duplikáty. Poté pomocí insert special transponujeme sloupec do řádku. Totéž děláme s regiony, pouze bez transpozice. Obdržíme záhlaví zprávy.

Tento štítek musí být vyplněn, tzn. shrnout tržby za příslušné produkty a regiony. To lze snadno provést pomocí funkce SUMIFS. Přidejme také výsledky. Získáte souhrnnou zprávu o prodeji podle regionu a produktu.

Splnili jste úkol a ukažte zprávu řediteli. Při pohledu na tabulku vygeneruje několik skvělých nápadů najednou.

— Je možné vytvořit zprávu nikoli o tržbách, ale o zisku?

— Je možné zobrazovat produkty podle řádků a regiony po sloupcích?

— Je možné vytvořit takové tabulky pro každého manažera zvlášť?

I když jste zkušený uživatel Excelu, vytváření nových sestav zabere spoustu času. O možných chybách ani nemluvě. Pokud však znáte kontingenční tabulky, pak odpověď zní: ano, potřebuji 5 minut, možná méně.

Zde je návod, jak se to dělá. Otevřete zdrojová data. Kontingenční tabulku lze sestavit pomocí běžného rozsahu, ale správnější by bylo ji transformovat. Tím se okamžitě vyřeší problém s automatickým zachycováním nových dat. Vyberte libovolnou buňku a přejděte na kartu Vložit. Na levé straně pásu karet jsou dvě tlačítka: a Doporučené kontingenční tabulky.

Pokud nevíte, jak uspořádat existující data, můžete použít příkaz Doporučené kontingenční tabulky. Excel zobrazí miniatury možných rozložení na základě vašich dat.

Klikněte na příslušnou možnost a kontingenční tabulka je připravena. Nezbývá než si to připomenout, protože... Je nepravděpodobné, že by se standardní příprava zcela shodovala s vašimi přáními. Pokud potřebujete vytvořit kontingenční tabulku od začátku nebo máte starou verzi programu, klikněte na tlačítko. Objeví se okno, kde je potřeba zadat zdrojový rozsah (pokud aktivujete libovolnou buňku excelové tabulky, určí se sama) a umístění budoucí kontingenční tabulky (ve výchozím nastavení bude vybrán nový list).

Velmi často zde není potřeba nic měnit. Po kliknutí na OK se vytvoří nový list Excelu s prázdným rozložením kontingenční tabulky.

Než přejdeme k nastavení, seznamme se s rozhraním a základními pojmy. Rozložení tabulky je na panelu přizpůsobitelné Pole kontingenční tabulky, který se nachází na pravé straně listu.

V horní části panelu je seznam všech dostupných polí, tedy sloupců ve zdrojových datech. Pokud potřebujete do rozvržení přidat nové pole, můžete zaškrtnout políčko vedle něj – Excel sám určí, kam má být toto pole umístěno. Ne vždy je to ale správné, proto je lepší přetáhnout myš na požadované místo na layoutu. Pole lze také odstranit zrušením zaškrtnutí nebo přetažením zpět.

Kontingenční tabulka se skládá ze 4 oblastí, které jsou umístěny ve spodní části panelu: hodnoty, řádky, sloupce, filtry. Pojďme se blíže podívat na jejich účel.

Rozsah hodnot– toto je centrální část kontingenční tabulky s hodnotami, které se získávají agregací zdrojových dat pomocí zvolené metody.
Ve většině případů dochází k agregaci pomocí Shrnutí. Pokud jsou všechna data ve vybraném poli v číselném formátu, Excel přiřadí výchozí součet. Pokud zdrojová data obsahují alespoň jeden text nebo prázdnou buňku, pak se místo součtu vypočítá Množství buňky. V našem příkladu je každá buňka součtem všech odpovídajících produktů v odpovídající oblasti.

V buňkách kontingenční tabulky můžete použít další metody výpočtu. Existuje asi 20 typů (průměr, minimální hodnota, podíl atd.). Existuje několik způsobů, jak změnit metodu výpočtu. Nejjednodušší je kliknout pravým tlačítkem na libovolnou buňku požadovaného pole v samotné kontingenční tabulce a vybrat jinou metodu agregace.

Oblast čáry– názvy řádků, které jsou umístěny ve sloupci zcela vlevo. Toto jsou všechny jedinečné hodnoty vybraného pole (sloupce). V oblasti řádků může být několik polí, pak se tabulka stane víceúrovňovou. Obvykle jsou zde umístěny kvalitativní proměnné jako názvy produktů, měsíce, regiony atd.

Oblast sloupců– podobně jako řádky, zobrazuje jedinečné hodnoty vybraného pole, pouze ve sloupcích. Názvy sloupců jsou také obvykle kvalitativním ukazatelem. Například roky a měsíce, skupiny produktů.

Oblast filtru– používá se, jak název napovídá, pro filtrování. Například samotná sestava zobrazuje produkty podle regionu. Kontingenční tabulku musíte omezit na konkrétní odvětví, období nebo manažera. Poté se do oblasti filtru umístí pole filtru a z rozevíracího seznamu se vybere požadovaná hodnota.

Přidáním a odebráním polí do určených oblastí si můžete během několika sekund přizpůsobit libovolný úsek vašich dat.

Podívejme se, jak to funguje v praxi. Prozatím vytvoříme stejnou tabulku, jaká již byla vytvořena pomocí funkce SUMIFS. Chcete-li to provést, přetáhněte do oblasti Hodnoty pole "Příjmy", v oblasti Struny přetáhněte pole „Region“ (prodejní oblast) na Sloupce- "Produkt".

V důsledku toho získáme skutečnou kontingenční tabulku.

Jeho vybudování trvalo doslova 5-10 sekund. Úprava stávající kontingenční tabulky je také snadná. Pojďme se podívat, jak se režisérova přání snadno převedou do reality.

Nahraďme příjmy ziskem.

Produkty a oblasti lze také zaměnit tažením myši.

Pro filtrování kontingenčních tabulek je k dispozici několik nástrojů. V tomto případě jednoduše umístíme pole „Manager“ do oblasti filtru.

Vše trvalo několik sekund. Zde si ukážeme, jak snadná je skutečně práce s kontingenčními tabulkami. Samozřejmě ne všechny úkoly jsou tak triviální. Existují i ​​případy, kdy je potřeba použít sofistikovanější metodu agregace, přidávání počítaných polí, podmíněné formátování atp. Toto je ale pokročilejší použití kontingenčních tabulek.

Počáteční údaje

Pro úspěšnou práci s kontingenčními tabulkami musí zdrojová data splňovat řadu požadavků. Předpokladem je přítomnost jmen nad každým polem (sloupcem), podle kterého budou tato pole identifikována. Nyní několik užitečných tipů.

1. Nejlepší formát pro data je tabulka Excel. Je to dobré, protože každé pole má svůj název a při přidání nových řádků se automaticky zahrnou do kontingenční tabulky.

2. Vyhněte se opakování skupin ve sloupcích. Všechna data by například měla být v jednom poli a neměla by být rozdělena například podle měsíců v samostatných sloupcích.

3. Odstraňte mezery a prázdné buňky, jinak může tento řádek z analýzy vypadnout.

4. Použijte správné formátování polí. Čísla musí být v číselném formátu, data musí být data. Jinak vzniknou problémy při seskupování a matematickém zpracování. Tady vám ale pomůže Excel, protože... Docela dobře určuje formát dat.

Obecně platí, že požadavků je málo, ale měli byste je znát.

Pokud provedete změny ve zdroji (například přidání nových řádků), kontingenční tabulka se nezmění, dokud ji neobnovíte pomocí pravého tlačítka myši

nebo
pomocí příkazu v záložce Data – Obnovit vše.

To bylo provedeno záměrně, protože kontingenční tabulka zabírá hodně místa v paměti RAM. Aby se tedy zdroje počítače využívaly hospodárněji, nepracuje se přímo se zdrojem, ale s cache, kde je umístěn snímek zdrojových dat.

Pomocí kontingenčních tabulek i na této základní úrovni můžete výrazně zvýšit rychlost a kvalitu zpracování velkého množství dat. Pokud je něco nejasné, napište do komentářů.

Kontingenční tabulky je jedním z nejvýkonnějších nástrojů Excelu. Umožňují vám analyzovat a shrnout různé výsledky pro velké objemy dat pomocí pouhých několika kliknutí. V tomto článku se seznámíme s kontingenčními tabulkami, pochopíme, co to je, naučíme se, jak je vytvářet a konfigurovat.

Při psaní tohoto článku jsem použil Excel 2010. Koncepce kontingenčních tabulek zůstala po mnoho let v podstatě nezměněna, ale způsob jejich tvorby se v každé nové verzi Excelu mírně liší. Pokud máte jinou verzi Excelu než 2010, pak se připravte, že snímky obrazovky v tomto článku se budou lišit od toho, co vidíte na obrazovce.

Trochu historie

Na úsvitu vývoje programů pro tvorbu tabulek byla pravidla Lotusu 1-2-3. Jeho dominance byla tak úplná, že snahy Microsoftu vyvinout vlastní software (Excel) jako alternativu k Lotusu se zdály být ztrátou času. Nyní rychle vpřed do roku 2010! Excel dominuje odvětví tabulkových procesorů více než kdy Lotus 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 vytvořit verzi Lotus 1-2-3 pro Windows (ale jen na několik let), protože předpovídali, že verze jejich softwaru pro DOS je vše, co budou spotřebitelé potřebovat. Microsoft samozřejmě vyvinul Excel speciálně pro Windows.
• Za druhé, Microsoft vyvinul nástroj v Excelu nazvaný kontingenční tabulky, který nebyl k dispozici v Lotusu 1-2-3. Kontingenční tabulky, exkluzivně pro Excel, byly tak neuvěřitelně užitečné, že lidé byli nakloněni naučit se nový software Excel, než aby zůstali u Lotusu 1-2-3, který je neměl.

Kontingenční tabulky, spojené s podceňováním úspěchu Windows obecně, hrály pochod smrti pro Lotus 1-2-3 a znamenaly začátek úspěchu Microsoft Excel.

Co jsou kontingenční tabulky?

Jaký je tedy nejlepší způsob, jak popsat, 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 změnit, pokud neposkytují obrázek, který jste doufali, že získáte. Pouhými několika kliknutími 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 různých věcí. Místo abychom se snažili slovy popsat všechny možnosti kontingenčních tabulek, je snazší je demonstrovat v praxi...

Data, která analyzujete pomocí kontingenčních tabulek, nemohou být žádným typem dat. Měla by to být nezpracovaná data, jako nějaký seznam. Může to být například seznam dokončených prodejů ve společnosti 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á data, protože již byla shrnuta. V buňce B3 vidíme 30 000 dolarů, což je pravděpodobně celková částka, kterou James Cook vydělal v lednu. Kde jsou tedy zdrojová data? Kde se vzala částka 30 000 $? Kde je původní seznam prodejů, ze kterého byl tento měsíční součet odvozen? Je jasné, že někdo vynaložil velké úsilí, aby uspořádal a seřadil všechna prodejní data za posledních šest měsíců a převedl je do souhrnné tabulky, kterou vidíme. Jak dlouho to podle vás trvalo? Hodina? Deset?

Faktem je, že výše uvedená tabulka není souhrnnou tabulkou. Byl vytvořen ručně z nezpracovaných dat uložených jinde a jeho zpracování trvalo nejméně několik hodin. Toto je přesně ten druh souhrnné tabulky, kterou lze vytvořit pomocí kontingenčních tabulek a strávit nad ní jen několik 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 obchodních transakcí, pomocí kontingenčních tabulek a během pár sekund, můžeme vytvořit měsíční zprávu o prodeji v Excelu, o které jsme hovořili výše. Ano, tohle a mnohem víc umíme!

Jak vytvořit kontingenční tabulku?

Nejprve se ujistěte, že máte některá zdrojová data na listu aplikace Excel. Seznam finančních transakcí je nejtypičtější věc, která se najde. Ve skutečnosti to může být seznam čehokoli: kontaktní informace na zaměstnance, sbírka CD nebo údaje o spotřebě paliva vaší společnosti.

Takže spustíme Excel... a načteme tento 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žit(Vložit) vyberte příkaz Kontingenční tabulka(Souhrnná 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 mám použít k vytvoření nové kontingenční tabulky?
• Kam mám umístit kontingenční tabulku?

Vzhledem k tomu, ž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 nebo dokonce 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ém listu):

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ů v původním seznamu. Čtyři prázdné oblasti ve spodní části obrazovky vám umožňují sdělit kontingenční tabulce, jak chcete shrnout data. Zatímco tyto oblasti jsou prázdné, v tabulce nic není. Jediné, co musíme udělat, je přetáhnout nadpisy z horní oblasti do prázdných oblastí níže. V tomto případě se automaticky vygeneruje kontingenční tabulka podle našich pokynů. Pokud jsme udělali chybu, můžeme odstranit nadpisy ze spodní oblasti nebo přetáhnout jiné a nahradit je.

Kraj Hodnoty(Významy) je pravděpodobně nejdůležitější ze čtyř. To, jaké záhlaví je v této oblasti umístěno, určuje, jaké údaje budou sumarizovány (součet, průměr, maximum, minimum atd.) Téměř vždy se jedná o číselné hodnoty. Výborný kandidát na pozici v této oblasti - ú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 Objevila se položka (Hodnoty). Součet Částky(pole Součet podle částky), 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 více informací o našich datech, než aktuálně máme.

Podívejme se na zdrojová data a pokusme se identifikovat jeden nebo více sloupců, které lze použít k rozdělení tohoto součtu. Můžeme například vytvořit naši kontingenční tabulku tak, že se celková částka prodeje vypočítá pro každého prodejce zvlášť. Tito. Do naší kontingenční tabulky budou přidány řádky se jménem každého prodejce společnosti a jeho celkovou částkou prodeje. Chcete-li dosáhnout tohoto výsledku, stačí přetáhnout název Prodavač Štítky řádků(Řádky):

Je to čím dál zajímavější! Náš kontingenční stůl 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 již připravena. Vytvořili jsme užitečné shrnutí nezpracovaný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 naučíme se, 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 regionu Štítky sloupců(Sloupce):

Dostáváme výsledek:

Vypadá to velmi cool!

Nyní vytvoříme trojrozměrnou tabulku. Jak může takový stůl vypadat? Pojďme se podívat…

Přetáhněte název Balík(Komplex) do oblasti Filtr přehledů(Filtry):

Všimněte si, kde skončil...

To nám dává možnost filtrovat sestavu podle atributu „Který rekreační areál 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 změnit pohled na souhrnnou 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 konfiguraci...

Pokud se najednou ukáže, že by se v kontingenční tabulce měly zobrazovat pouze platby šekem a kreditní kartou (tedy bankovním převodem), můžeme zobrazení záhlaví deaktivovat Hotovost(Hotovost). Chcete-li to provést vedle Štítky sloupců klikněte na šipku dolů a v rozevírací nabídce zrušte zaškrtnutí položky 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

Je zřejmé, že kontingenční tabulky jsou velmi mocným nástrojem, 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 obvyklé akce 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 (a je 99% pravděpodobnost, že se to stane), formátování se ztratí. Potřebujeme způsob, jak to udělat (téměř) trvalé.

Nejprve najdeme záznam Součet Částky v oblasti Hodnoty(Hodnoty) a klikněte na něj. V zobrazené nabídce vyberte položku Nastavení pole hodnot(Možnosti pole hodnoty):

Zobrazí se dialogové okno Nastavení pole hodnot(Parametry hodnotového pole).

Klepněte na tlačítko Formát čísla(Formát čísla), zobrazí se dialogové okno Formát buněk(Formát buňky):

Ze seznamu Kategorie(Formáty čísel) vyberte Účetnictví(Finanční) a nastavte počet desetinných míst na nulu. Nyní několikrát stiskněte OK vrátit se k naší kontingenční tabulce.

Jak vidíte, čísla byla nakonec formátována jako částky v dolarech.

Nyní, když jsme s formátováním hotovi, nastavíme formát pro celou kontingenční tabulku. Existuje několik způsobů, jak to udělat. Použijme ten jednodušší...

Otevřete kartu Nástroje kontingenční tabulky: Návrh(Práce s kontingenčními tabulkami: Návrhář):

Vyberte jakýkoli vhodný styl a podívejte se na výsledek v kontingenční tabulce:

Další nastavení pro kontingenční tabulky v Excelu

Někdy musíte data filtrovat podle dat. Například náš seznam obchodních transakcí obsahuje mnoho a mnoho dat. Excel poskytuje nástroj pro seskupování dat podle dne, měsíce, roku atd. Pojďme se podívat, jak se to dělá.

Nejprve odstraňte záznam. Způsob platby z regionu Štítky sloupců(Sloupce). Chcete-li to provést, přetáhněte jej zpět do seznamu nadpisů a na jeho místo přesuňte nadpis 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 Skupina(Skupina):

Zobrazí se dialogové okno seskupení. Vybíráme si měsíce(Měsíce) a stiskně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 výsledky prodeje sestaveny ručně.

Je tu ještě jeden 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 se záhlavími sloupců (nebo dokonce filtry).

Vraťme se k původnímu zobrazení tabulky a podívejme se, jak zobrazit průměrné hodnoty místo součtů.

Chcete-li začít, klikněte na Součet Částky a ze zobrazené nabídky vyberte Nastavení pole hodnot(Možnosti pole hodnoty):

Na seznamu Shrnout pole hodnoty podle(Operace) v dialogovém okně Nastavení pole hodnot(Volby pole hodnoty) vyberte Průměrný(Průměrný):

Zároveň, když už jsme tady, pojďme se změnit Vlastní název(Vlastní název) s Průměr částky(pole Množství podle množství) na něco kratšího. Do tohoto pole zadejte něco takového Prům:

Klikněte OK a podívejte se, co se stalo. Všimněte si, že všechny hodnoty se změnily z celkových na průměry a název tabulky (v levé horní buňce) se změnil na Prům:

Pokud chcete, můžete okamžitě získat součet, průměr a množství (tržby) umístěné v jedné kontingenční tabulce.

Zde je podrobný návod, jak to udělat, počínaje prázdnou kontingenční tabulkou:

1. Přetáhněte název Prodavač(Obchodní zástupce) do regionu Štítky sloupců(Sloupce).
2. Přetáhněte název třikrát Množství(Cena) na oblast Hodnoty(Hodnoty).
3. Pro první pole Množství změnit název na Celkový(Částka) a formát čísla v tomto poli je Účetnictví(Finanční). Počet desetinných míst je nula.
4. Druhé pole Množství název Průměr e, nastavte pro něj operaci Průměrný(Průměr) a změňte formát čísla v tomto poli na Účetnictví(Finanční) s počtem desetinných míst rovným nule.
5. Pro třetí pole Množství nastavit název Počet a operace pro něj - Počet(Množství)
6. V oblasti Štítky sloupců Automaticky vytvořeno pole (Sloupce). ΣHodnoty(Σ Hodnoty) – přetáhněte jej do oblasti Štítky řádků(struny)

To je to, co nakonec dostaneme:

Celkový, průměr a počet prodejů - vše v jedné kontingenční tabulce!

Závěr

Kontingenční tabulky v aplikaci Microsoft Excel obsahují mnoho, mnoho funkcí a nastavení. Tak krátký článek se nemůže ani přiblížit, aby je všechny pokryl. Úplný popis všech možností kontingenčních tabulek by vyžadoval malou knihu nebo velkou webovou stránku. Odvážní a zvědaví čtenáři mohou pokračovat ve zkoumání 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, kontingenční tabulku můžete kdykoli smazat a začít znovu. Máte příležitost, kterou dlouholetí uživatelé DOS a Lotus 1-2-3 nikdy neměli.

Kontingenční tabulky v Excelu umožňují analyzovat data, která jsou v jednom velkém rozsahu. Zdrojovou tabulku, která bude použita pro analýzu, lze navíc vytvořit buď v Excelu, nebo v jiné databázi či jiném dokumentu.

Vše, co musíte udělat, je vybrat potřebná záhlaví pro řádky a sloupce ze stávajících. Můžete také použít filtr na celou kontingenční tabulku jako celek nebo pouze na hodnoty, které jsou uvedeny v názvech řádků a sloupců.

Nyní o požadavcích, které je třeba dodržovat při jeho vytváření v Excelu. Zdrojová data musí být prezentována ve formě tabulky, která musí mít nadpis sloupců, tedy záhlaví. Pro tyto účely je vytvoření chytré tabulky v Excelu perfektní. Také by neměl obsahovat prázdné řádky, sloupce nebo buňky. Neměly by zde být žádné skryté řádky/sloupce ani sloučené buňky.

Jak vytvořit

Nyní se podívejme na příklad vytvoření kontingenční tabulky. Předpokládejme, že máme údaje o tržbách obchodu s oblečením: který prodejce, jaké číslo, jaké položky prodal a za jakou částku.

Chcete-li vytvořit kontingenční tabulku, vyberte libovolnou buňku z původní, přejděte na kartu „Vložit“ a klikněte na tlačítko "kontingenční tabulka".

Zobrazí se následující dialogové okno. V něm musíte uvést rozsah nebo název tabulky se zdrojovými daty a zde je můžete vybrat z jiného zdroje. Poté označte fixem, kam umístit sestavu, která se má vytvořit. Udělejme to na novém listu. Klepněte na tlačítko OK.

V otevřeném sešitu Excelu se vytvoří nový list, na který se umístí dosud prázdná kontingenční tabulka.

Na pravé straně se zobrazí seznam polí a oblastí. Pole jsou všechna záhlaví sloupců, která byla v původním rozsahu. Pomocí myši je přetáhneme do jedné ze čtyř níže uvedených oblastí. Tím se vytvoří kontingenční tabulka.

Přidaná pole budou označena zaškrtnutím. V oblastech tabulky můžete změnit jejich umístění, abyste dosáhli vzhledu, který je nejvhodnější pro analýzu vašich zdrojových dat.

Jak uspořádat pole

Nyní se rozhodneme, na jakém principu budeme data analyzovat. Potřebujete například zjistit, který prodejce prodal který produkt v každém měsíci a za jakou částku.

Údaje vyfiltrujeme podle vybraného sortimentu prodejců. To znamená, že vybereme prodejce a v tabulce se zobrazí zboží, které prodává. Klikněte levým tlačítkem na pole „Vendor“ a přetáhněte jej do oblasti „Filtr sestav“. Tabulka se změnila a přidané pole je nyní označeno zaškrtnutím.

Pro řádky vyberte „Produkty“. Podobně přetáhněte požadované pole do oblasti "Názvy řádků".

Upozorňujeme, že do jedné oblasti můžete vložit více polí. Například v řádcích vybereme produkty a uvedeme cenu. Produkty se staly rozbalovacími seznamy, které zobrazují cenu. Pokud nejprve zadáte cenu a poté produkty, z rozbalovacího seznamu se stane cena. Zde záleží na pořadí polí.

Pokud bychom měli v původní tabulce sloupec „Units“. Pak by tato souhrnná tabulka ukázala, který prodejce, ve kterém měsíci, kolik jednotek zboží prodal za určitou cenu.

Do regionu "Názvy sloupců" Přetáhněte pole „Datum“. Chcete-li zobrazit tržby ne za každý den, ale například po měsíci, klikněte pravým tlačítkem na libovolné datum a z nabídky vyberte „Skupina“.

Tabulka bude mít následující podobu.

Nyní přetáhněte pole „Částka“ do oblasti „Hodnoty“.

Jak vidíte, zobrazovala se pouze čísla, i když v původním rozsahu byl nastaven číselný formát buněk pro tento sloupec, může být i peněžní nebo finanční. Ve sloupci „Datum“ byl také vhodný formát buňky – datum.

Chcete-li to opravit, vyberte požadovaný rozsah buněk v souhrnu a stiskněte pravé tlačítko myši. poté vyberte z nabídky "Formát čísel".

V dalším okně vyberte „Numerical“, můžete zaškrtnout políčko "Oddělovač bitových skupin" a klikněte na "OK".

Jak pracovat s daty

Poté, co vezmeme v úvahu všechna potřebná pole, můžeme začít pracovat s kontingenční tabulkou v Excelu. Výběr prodejce: zaškrtnutím políčka můžete vybrat jednoho, několik nebo všechny najednou "Vybrat více položek".

Můžete také použít filtr pro řádky a sloupce. V příkladu se jedná o produkty a měsíce. Například zaškrtnutím políček Oblek a Kalhoty můžete zjistit, za kolik je prodali všichni prodejci nebo konkrétní prodejce.

V oblasti Hodnoty můžete nakonfigurovat nastavení pro pole. Příklad zobrazuje součet hodnot: Romové prodali v únoru košile v hodnotě 1 800,00. uvidíme kolik to bude kusů. Klikněte levým tlačítkem myši na řádek "Součet podle polí..." a vyberte z nabídky "Možnosti hodnotového pole".

V dalším okně vyberte ze seznamu „Množství“, vyberte, co je vhodné konkrétně pro váš případ, a klikněte na „OK“.

Nyní, když se podíváme na hodnoty, můžeme pochopit, že Romové prodali v únoru dvě košile.

Nyní udělejme obecný filtr pro tabulku podle měsíce. Změníme oblasti: ve „Filtru sestav“ přetáhneme pole „Datum“ do "Názvy sloupců"- "Prodavač".

Taková souhrnná tabulka ukáže, kolik a jaké zboží každý prodejce prodal za celé období nebo za konkrétní měsíc.

Pozor také na pásku. Když vyberete libovolné buňky z kontingenční tabulky, zobrazí se v ní karta "Práce s kontingenčními tabulkami" se dvěma podzáložkami „Parametry“ a „Návrhář“.

Problém, kterému všichni čelíme, není nedostatek dat; naopak – jde o obrovské množství dat! To je důvod, proč doporučuji používat skvělou funkci Excel - Kontingenční tabulky shrnout a analyzovat vaše data.

Prohlédnout si sílu kontingenčních tabulek je stejně jednoduché jako porovnání původních dat s verzí kontingenční tabulky; Po několika kliknutích jsem byl schopen vidět průměrnou cenu položky podle státu.

Jako finanční profesionál jsem geneticky nakloněn milování tabulek. Ale také jsem zjistil, že používám tabulky k organizaci své kreativní práce a práce na volné noze. Bez ohledu na to, k čemu tabulky používáte, kontingenční tabulka vám může pomoci lépe pochopit vaše data.

Tuto lekci postavíme podle našeho, pro lepší práci s daty. Ukážu vám pět mých oblíbených osvědčených postupů kontingenční tabulky.

V tomto tutoriálu budu používat ukázková data poskytnutá společností Microsoft na této stránce. Tato data použijte k opětovnému vytvoření příkladů nebo k testování funkcí, které předvádím.

Jak používat pokročilé techniky kontingenčních tabulek v Excelu (krátké video)

Rád učím pomocí screencastů, které vám dávají možnost sledovat, jak krok za krokem používám funkce. Podívejte se na krátké video níže, které pokrývá pět mých oblíbených pokročilých funkcí kontingenční tabulky Excel:

5 Pokročilých triků kontingenční tabulky v Excelu

Pokračujte ve čtení a zjistěte, jak používat každou z těchto pěti funkcí v lekci níže, včetně: Průřezy, Časová osa, Zobrazení tabulky, Vypočítaná pole a Vybrané kontingenční tabulky. Začněme.

1. Sekce

Plátky- Nástroj typu point-and-click pro upřesnění dat obsažených v kontingenční tabulce Excelu. Vložte výřez a můžete snadno změnit data obsažená v kontingenční tabulce.

V tomto příkladu jsem vložil řez pro typ Položka. Poté, co kliknu na Batoh, kontingenční tabulka zobrazuje pouze tento parametr v tabulce.

Mnohokrát vytvářím sestavy dashboardu, které budou používat ostatní. Přidání průřezů může pomoci koncovému uživateli přizpůsobit sestavu podle svých představ.

Chcete-li přidat průřez, klikněte do kontingenční tabulky a vyhledejte kartu Analýza na pásu karet Excelu.

Zaškrtnutím více políček zahrnete tyto sloupce, z nichž každý bude řezem.

Držet Ctrl na klávesnici vyberte více položek průřezu, které budou zahrnovat více výběrů ze sloupce jako data kontingenční tabulky.

2. Časová osa

Časové osy Jedná se o speciální typ průřezu, který se používá k úpravě dat zahrnutých jako součást dat kontingenční tabulky. Pokud vaše data obsahují data, opravdu musíte vyzkoušet časovou osu jako způsob, jak vybrat data z konkrétních časových období.

Přejděte do sekce Analyzovat > Vložit časovou osu pro přidání časové osy, speciálního typu průřezu, který spravuje data obsažená v pokročilé kontingenční tabulce Excelu na základě data.

Tip: Pokud vám tato funkce nefunguje, ujistěte se, že vaše zdrojová data mají datum ve formátu v tabulce jako datum.

Chcete-li přidat časovou osu, vyberte kontingenční tabulku (klikněte do ní) a poté klikněte na tlačítko Analyzovat > Vložit časovou osu na pásu karet Excelu. Ve vyskakovacím okně vyberte sloupec data (nebo více sloupců) a klikněte OK vytvořit časovou osu.

Kliknutím a přetažením v okně Časová osa vyberte konkrétní časové období pro kontingenční tabulku.

Po vložení časové osy můžete kliknout a posouvat úchyty na časové ose, abyste změnili, co je součástí kontingenční tabulky.

Způsob fungování časové osy můžete změnit kliknutím na rozevírací seznam v pravém dolním rohu. Časovou osu můžete změnit, aby se například místo konkrétních dat zobrazovala čtvrtletní nebo roční data.

3. Tabulkový pohled

Standardní zobrazení kontingenčních tabulek v Excelu vypadá jako vodopád; Jak přetahujete více polí do řádků, Excel vytváří více „vrstev“ v datech.

Problém je v tom, že je obtížné vměstnat vzorce do kontingenčních tabulek ve standardním zobrazení. Pokud máte data v kontingenční tabulce, ale chcete je zobrazit jako běžnou tabulku, musíte pro kontingenční tabulku použít zobrazení tabulky.

Vybrat Návrhář > Rozvržení sestavy > Zobrazit ve formě tabulky pracovat s kontingenční tabulkou jako s běžnou tabulkou.

Proč byste měli používat zobrazení tabulky? Převedení dat kontingenční tabulky do klasického tabulkového stylu vám umožní snadněji zadávat datové vzorce nebo je vkládat do samostatné sestavy.

Chcete-li, aby kontingenční tabulka vypadala více jako standardní řádky a sloupce, použijte zobrazení tabulky.

Většinu času raději používám tabulkové zobrazení Excelu. Vypadá spíše jako standardní tabulka a je snazší v ní psát vzorce a pracovat s daty. Mohl bych také vzít tento pohled a vložit jej na novou kartu.

4. Vypočítaná pole

Vypočítaná pole představují způsob, jak přidat sloupec do kontingenční tabulky, který není ve zdrojových datech. Standardní matematické operace můžete použít k vytvoření zcela nových polí, se kterými budete pracovat. Vezměte dva existující sloupce a pomocí matematiky vytvořte úplně nové.

Řekněme, že máme údaje o prodeji v tabulce. U každého zboží máme počet prodaných kusů a prodejní cenu. Toto je ideální čas použít počítané pole k výpočtu součtu objednávky.

Chcete-li začít pracovat s vypočítanými poli, začněte kliknutím do kontingenční tabulky a poté vyhledejte na pásu karet Analýza. Klikněte na nabídku Pole, prvky a množiny a poté vyberte Vypočítané pole.

Použití Analýza > Pole, členové a sady > Vypočítané pole vložit vypočítané pole do kontingenční tabulky.

V novém vyskakovacím okně začněte zadáním názvu vypočítaného pole. v mém případě to zavolám Celková objednávka. Celková hodnota objednávky je množství vynásobené cenou každé jednotky. Poté jsem poklepal na první pole názvu (množství) v seznamu polí v tomto okně.

Pro výpočet celkových nákladů na objednávku jsem vynásobil stávající pole jednotkové ceny ( Jednotková cena) v poli množství ( Množství).

Po přidání názvu tohoto pole přidám znaménko násobení * a poté dvakrát klikněte na celkové množství (množství). Pojďme dál a klikneme OK.

Nyní Excel aktualizoval mou rozšířenou kontingenční tabulku a přidal nové počítané pole. V seznamu polí také uvidíte seznam kontingenčních tabulek, takže je můžete přetáhnout kamkoli do sestavy, kdykoli to budete potřebovat.

Pokud nechcete používat dvousloupcovou aritmetiku, můžete do vypočítaného pole zadat také vlastní aritmetické hodnoty. Pokud bych například chtěl ke každé objednávce jednoduše přidat 5% daň z obratu, mohl bych do vypočítaného pole napsat následující:

Vynásobil jsem celkovou cenu objednávky 1,05, abych vypočítal náklady včetně daně z obratu; můžete použít číselné hodnoty spolu s existujícími poli.

V zásadě mohou vypočítaná pole obsahovat libovolné standardní matematické operátory, jako je sčítání, odčítání, násobení a dělení. Tato vypočítaná pole použijte, pokud nechcete aktualizovat původní data.

Jít do Vložit > vyzkoušet tuto funkci.

Tato funkce je tak snadno použitelná, že je samozřejmostí. Můžete jej použít k rychlému vytváření pokročilých kontingenčních tabulek v Excelu. Stačí zvýraznit svá data a přejít na kartu Vložit na pásu karet Excel a vyberte Doporučené kontingenční tabulky.

Vyskakovací okno obsahuje mnoho možností pro vytvoření kontingenční tabulky ze zdrojových dat. Kliknutím na miniatury na levé straně tohoto okna zobrazíte doporučené možnosti kontingenční tabulky poskytované aplikací Excel.

Funkce doporučení kontingenční tabulky nabízí mnoho možností, jak analyzovat data jedním kliknutím.

I když se jedná o pokročilou funkci, kterou si málokdo uvědomuje, je to také skvělý nástroj pro spouštění kontingenčních tabulek. Nic vám nebrání upravit kontingenční tabulku tím, že si pole nahradíte sami, ale je to dobrý výchozí bod.

Také se mi tato funkce líbí jako nástroj pro průzkum dat. Pokud nevím, co hledám, když začnu zkoumat data, doporučené kontingenční tabulky Excel jsou často prozíravější než já!

Opakujte a pokračujte v učení (s ještě více lekcemi Excelu)

Tato pokročilá lekce Excelu vám pomohla ponořit se hlouběji do kontingenčních tabulek, jedné z mých oblíbených funkcí pro analýzu a zobrazení tabulky Excel. Kontingenční tabulky používám k nalezení významu ve velkých souborech dat, abych se mohl lépe rozhodovat a přijímat opatření.

Tyto lekce posunou vaše dovednosti s Excelem a kontingenčními tabulkami na další úroveň. Zkontroluj je:

• ExcelZoo má skvělý přehled o technikách kontingenčních tabulek ve svém článku 10 lekcí pro zvládnutí kontingenčních tabulek (v angličtině).
• My v Envato Tuts+ jsme pokryli kontingenční tabulky výukovým programem pro začátečníky.
• Pro snazší úvod do aplikace Microsoft Excel si prohlédněte naši sérii výukových programů.

Co ještě chcete vědět o kontingenčních tabulkách? Dejte mi vědět své nápady nebo dotazy v komentářích pod tímto návodem.