Pokud čtete tento návod, je velká šance, že jste již slyšeli (nebo dokonce používali) kontingenční tabulku aplikace Excel. Je to jedna z nejvýkonnějších funkcí v Excelu (bez legrace).
Nejlepší na používání kontingenční tabulky je to, že i když v aplikaci Excel nic nevíte, můžete s ní stále dělat úžasné věci s velmi základním porozuměním.
Začněme.
Klikněte zde stáhněte si ukázková data a postupujte podle nich.
Co je to kontingenční tabulka a proč by vás to mělo zajímat?
Kontingenční tabulka je nástroj v aplikaci Microsoft Excel, který vám umožňuje rychle shrnout obrovské množiny dat (pomocí několika kliknutí).
I když jste ve světě Excelu úplní nováčci, můžete snadno použít kontingenční tabulku. Vytváření přehledů je stejně snadné jako přetahování záhlaví řádků/sloupců.
Předpokládejme, že máte datovou sadu, jak je uvedeno níže:
Jedná se o údaje o prodeji, které se skládají z ~ 1000 řádků.
Obsahuje data o prodeji podle regionu, typu maloobchodníka a zákazníka.
Nyní může váš šéf chtít z těchto dat vědět několik věcí:
- Jaké byly celkové tržby v jižním regionu v roce 2016?
- Jaké jsou první pětice maloobchodníků podle tržeb?
- Jak byl výkon The Home Depot srovnatelný s ostatními maloobchodníky na jihu?
Můžete pokračovat a používat funkce Excelu, které vám poskytnou odpovědi na tyto otázky, ale co když najednou váš šéf přijde se seznamem dalších pěti otázek.
Pokaždé, když dojde ke změně, budete se muset vrátit k datům a vytvořit nové vzorce.
V tomto případě jsou kontingenční tabulky aplikace Excel opravdu užitečné.
Na všechny tyto otázky odpoví během několika sekund kontingenční tabulka (jak se dozvíte níže).
Ale skutečným přínosem je, že dokáže vyhovět vašemu vybíravému šéfovi založenému na datech tím, že okamžitě odpoví na jeho otázky.
Je to tak jednoduché, můžete také trvat několik minut a ukázat svému šéfovi, jak to udělat sám.
Naštěstí teď máte představu, proč jsou kontingenční tabulky tak úžasné. Pojďme do toho a vytvořme kontingenční tabulku pomocí datové sady (viz výše).
Vložení kontingenční tabulky do Excelu
Zde jsou kroky k vytvoření kontingenční tabulky pomocí výše uvedených dat:
- Klikněte kdekoli v datové sadě.
- Přejděte na Vložit -> Tabulky -> Kontingenční tabulka.
- V dialogovém okně Vytvořit kontingenční tabulku fungují výchozí možnosti ve většině případů dobře. Zde je několik věcí, které je třeba zkontrolovat:
- Tabulka/Rozsah: Ve výchozím nastavení se vyplňuje na základě vaší sady dat. Pokud vaše data neobsahují žádné prázdné řádky/sloupce, Excel by automaticky identifikoval správný rozsah. V případě potřeby to můžete ručně změnit.
- Pokud chcete vytvořit kontingenční tabulku v určitém umístění, v možnosti „Vyberte, kam chcete umístit sestavu kontingenční tabulky“, zadejte umístění. Jinak je nový list vytvořen pomocí kontingenční tabulky.
- Klikněte na OK.
Jakmile kliknete na OK, vytvoří se nový list s kontingenční tabulkou.
Zatímco byla kontingenční tabulka vytvořena, neuvidíte v ní žádná data. Vše, co byste viděli, je název kontingenční tabulky a jednorázová instrukce vlevo a pole kontingenční tabulky vpravo.
Nyní, než přejdeme k analýze dat pomocí této kontingenční tabulky, pojďme pochopit, jaké jsou matice a šrouby, které tvoří kontingenční tabulku aplikace Excel.
Matice a šrouby kontingenční tabulky aplikace Excel
Abyste mohli kontingenční tabulku efektivně využívat, je důležité znát komponenty, které kontingenční tabulku vytvářejí.
V této sekci se dozvíte o:
- Pivot Cache
- Oblast hodnot
- Oblast řádků
- Oblast sloupců
- Oblast filtrů
Pivot Cache
Jakmile vytvoříte kontingenční tabulku pomocí dat, něco se stane v backendu. Excel pořídí snímek dat a uloží je do své paměti. Tento snímek se nazývá Pivot Cache.
Když vytváříte různá zobrazení pomocí kontingenční tabulky, Excel se nevrací zpět ke zdroji dat, ale používá kontingenční mezipaměť k rychlé analýze dat a získání souhrnu/výsledků.
Důvodem generování mezipaměti mezipaměti je optimalizace fungování kontingenční tabulky. I když máte tisíce řádků dat, kontingenční tabulka je při shrnutí dat super rychlá. Položky můžete přetáhnout do řádků/sloupců/hodnot/filtrů a výsledky se okamžitě aktualizují.
Poznámka: Jednou z nevýhod kontingenční mezipaměti je, že zvyšuje velikost sešitu. Protože se jedná o repliku zdrojových dat, při vytváření kontingenční tabulky se kopie těchto dat uloží do kontingenční mezipaměti.
Přečtěte si více: Co je to Pivot Cache a jak jej nejlépe používat.
Oblast hodnot
Oblast hodnot je to, co obsahuje výpočty/hodnoty.
Na základě sady dat zobrazené na začátku kurzu, pokud chcete rychle vypočítat celkové tržby podle oblastí v každém měsíci, můžete získat kontingenční tabulku, jak je uvedeno níže (uvidíme, jak ji vytvořit později v kurzu) .
Oblast zvýrazněná oranžově je oblast hodnot.
V tomto příkladu má celkový prodej v každém měsíci pro čtyři oblasti.
Oblast řádků
Nadpisy nalevo od oblasti Hodnoty tvoří oblast Řádky.
V níže uvedeném příkladu obsahuje oblast Řádky oblasti (zvýrazněné červeně):
Oblast sloupců
Nadpisy v horní části oblasti Hodnoty tvoří oblast Sloupce.
V níže uvedeném příkladu obsahuje oblast Sloupce měsíce (zvýrazněné červeně):
Oblast filtrů
Oblast Filtry je volitelný filtr, který můžete použít k dalšímu procházení v sadě dat.
Pokud například chcete vidět pouze tržby pro víceřádkové maloobchodníky, můžete tuto možnost vybrat z rozevíracího seznamu (zvýrazněno na obrázku níže) a kontingenční tabulka by se aktualizovala s údaji pouze pro víceřádkové maloobchodníky.
Analýza dat pomocí kontingenční tabulky
Nyní zkusme odpovědět na otázky pomocí kontingenční tabulky, kterou jsme vytvořili.
Klikněte zde stáhněte si ukázková data a postupujte podle nich.
Chcete -li analyzovat data pomocí kontingenční tabulky, musíte se rozhodnout, jak bude souhrn dat vypadat v konečném výsledku. Můžete například chtít všechny oblasti vlevo a celkové tržby hned vedle. Jakmile si tuto jasnost uvědomíte, můžete příslušná pole v kontingenční tabulce jednoduše přetáhnout.
V sekci Pole kontingenčních tabulek máte pole a oblasti (jak je zvýrazněno níže):
Pole jsou vytvořena na základě dat backendu použitých pro kontingenční tabulku. Sekce Oblasti je místo, kam umístíte pole, a podle toho, kam pole jde, se vaše data aktualizují v kontingenční tabulce.
Jedná se o jednoduchý mechanismus drag and drop, kde můžete pole jednoduše přetáhnout a umístit do jedné ze čtyř oblastí. Jakmile to provedete, zobrazí se v kontingenční tabulce na listu.
Nyní se pokusme zodpovědět otázky, které měl váš manažer pomocí této kontingenční tabulky.
Q1: Jaké byly celkové tržby v jižním regionu?
Přetáhněte pole Region v oblasti Řádky a pole Výnosy v oblasti Hodnoty. Automaticky by se aktualizovala kontingenční tabulka v listu.
Všimněte si toho, že jakmile zrušíte pole Výnosy v oblasti Hodnoty, stane se součtem výnosů. Ve výchozím nastavení Excel sečte všechny hodnoty pro danou oblast a zobrazí součet. Pokud chcete, můžete to změnit na počet, průměr nebo jiné metriky statistik. V tomto případě je to částka, kterou jsme potřebovali.
Odpověď na tuto otázku by byla 21225800.
Q2 Co je pět nejlepších maloobchodníků podle tržeb?
Přetáhněte pole Zákazník v oblasti Řádek a pole Výnosy v oblasti hodnot. V případě, že jsou v sekci oblasti nějaká další pole a chcete ji odstranit, jednoduše ji vyberte a přetáhněte z ní.
Získáte kontingenční tabulku, jak je uvedeno níže:
Ve výchozím nastavení jsou položky (v tomto případě zákazníci) seřazeny podle abecedy.
Chcete -li získat prvních pět maloobchodníků, můžete tento seznam jednoduše seřadit a použít prvních pět jmen zákazníků. Udělat toto:
- Klikněte pravým tlačítkem na libovolnou buňku v oblasti Hodnoty.
- Přejděte na Seřadit -> Seřadit od největší po nejmenší.
Získáte tak seřazený seznam na základě celkových tržeb.
Otázka 3: Jak byl výkon Home Depot srovnatelný s ostatními maloobchodníky na jihu?
Na tuto otázku můžete udělat spoustu analýz, ale tady se pokusíme tržby porovnat.
Přetáhněte pole Region v oblasti Řádky. Nyní přetáhněte pole Zákazník v oblasti Řádky pod pole Region. Když to uděláte, aplikace Excel pochopí, že chcete svá data kategorizovat nejprve podle oblastí a poté podle zákazníků v těchto oblastech. Budete mít něco, jak je uvedeno níže:
Nyní přetáhněte pole Výnosy v oblasti Hodnoty a budete mít tržby pro každého zákazníka (stejně jako pro celou oblast).
Maloobchodníky můžete seřadit na základě prodejních údajů podle následujících kroků:
- Klikněte pravým tlačítkem na buňku, která má prodejní hodnotu pro kteréhokoli prodejce.
- Přejděte na Seřadit -> Seřadit od největší po nejmenší.
To by okamžitě seřadilo všechny maloobchodníky podle prodejní hodnoty.
Nyní můžete rychle prohledat jižní region a zjistit, že tržby Home Depot činily 3004600 a dařilo se mu to lépe než u čtyř maloobchodníků v jižním regionu.
Nyní existuje více než jeden způsob, jak kočku stáhnout z kůže. Region můžete také vložit do oblasti Filtr a poté vybrat pouze Jižní region.
Klikněte zde ke stažení ukázkových dat.
Doufám, že vám tento návod poskytne základní přehled kontingenčních tabulek aplikace Excel a pomůže vám s jeho začátkem.
Zde je několik dalších výukových programů pro kontingenční tabulky, které by se vám mohly líbit:
- Příprava zdrojových dat pro kontingenční tabulku.
- Jak použít podmíněné formátování v kontingenční tabulce v aplikaci Excel.
- Jak seskupit data v kontingenčních tabulkách v aplikaci Excel.
- Jak seskupit čísla v kontingenční tabulce v aplikaci Excel.
- Jak filtrovat data v kontingenční tabulce v aplikaci Excel.
- Použití průřezů v kontingenční tabulce aplikace Excel.
- Jak nahradit prázdné buňky nulami v kontingenčních tabulkách aplikace Excel.
- Jak přidat a používat vypočítaná pole kontingenční tabulky aplikace Excel.
- Jak aktualizovat kontingenční tabulku v aplikaci Excel.
