Kontingenční tabulky jsou jednou z nejvýkonnějších funkcí v Excelu. I když jste nováček, můžete velké množství dat rozdrtit na užitečné informace. Kontingenční tabulka vám pomůže vytvářet zprávy během několika minut. Jednoduše analyzujte svá čistá data a pokud data nejsou čistá, může vám to pomoci vyčistit data. Nechci vás nudit, tak do toho skočíme a prozkoumáme.
Jak vytvořit kontingenční tabulku
Je to jednoduché. Stačí vybrat data. Přejít na Vložit. Klikněte na kontingenční tabulku a je hotovo.
Ale počkej. Před vytvořením kontingenční tabulky zajistěte, aby všechny sloupce měly nadpis.
Pokud některý nadpis sloupce zůstane prázdný, kontingenční tabulka nebude vytvořena a bude procházet chybovou zprávou.
Požadavek 1: Všechny sloupce by měly mít nadpis, abyste mohli začít s kontingenčními tabulkami v Excelu
Měli byste mít data uspořádaná se správným záhlavím. Jakmile to máte, můžete vložit kontingenční tabulku.
Vložte kontingenční tabulku z pásu karet
Chcete -li vložit kontingenční tabulku z nabídky, postupujte takto:
1. Vyberte rozsah dat
2. Přejděte na kartu Vložit
3. Klikněte na ikonu kontingenční tabulky.
4. Zobrazí se pole pro vytvoření kontingenční tabulky.
5. Zde vidíte rozsah dat, který jste vybrali. Pokud si myslíte, že toto není rozsah, který chcete vybrat, změňte zde přímo rozsah, místo abyste se vraceli a vybírali data znovu.
6. Dále můžete vybrat, kde chcete kontingenční tabulku. Doporučuji použít nový list, ale můžete také použít aktuální list. Stačí definovat umístění v poli Umístění.
7. Nyní, pokud jste s nastavením hotovi, stiskněte tlačítko OK. Kontingenční tabulku budete mít v novém listu. Pro souhrny stačí vybrat pole. Uvidíme, jak vytvoříme souhrn dat pomocí kontingenční tabulky, ale nejprve si ujasníme základy. V tomto tutoriálu o excelové kontingenční tabulce se dozvíte více, než byste čekali.
Vložit zástupce kontingenční tabulky (Alt> N> V)
Toto je sekvenční klávesová zkratka k otevření Vytvořit Kontingenční tabulka volitelný box.
Stiskněte tlačítko Alt a uvolněte jej. Udeřil N. a uvolni to. Udeřil PROTI a uvolni to. Otevře se pole Vytvořit kontingenční tabulku.
Nyní stačí podle výše uvedeného postupu vytvořit kontingenční tabulku v Excelu.
Vložit zástupce kontingenční tabulky pomocí staré zkratky aplikace Excel (Alt> D> P)
Jedna věc, která se mi na Microsoft Excelu nejvíce líbí, je, že v každé nové verzi Excelu zavádějí nové funkce, ale oni nevyhazujte staré funkce (jako to udělal MS s win 8. Bylo to ubohé). Tento umožňuje starým uživatelům pracovat normálně na nových verzích, jako dříve pracovali na starších verzích.
Pokud postupně stisknete ALT, D a P na klávesnici se otevře Excel a vytvoří průvodce průvodcem kontingenční tabulky.
Vyberte příslušnou možnost. Vybraná možnost na výše uvedeném snímku obrazovky nás dovede k vytvoření kontingenční tabulky, jak jsme ji vytvořili dříve.
Chcete -li zkontrolovat vybraný rozsah, stiskněte Enter nebo klikněte na Další.
Znovu stiskněte Enter.
Vyberte nový list nebo kdekoli chcete, aby vaše kontingenční tabulka stiskla Enter. A je hotovo.
Vytvářejte sestavy pomocí kontingenčních tabulek
Nyní víte, jak vložit kontingenční tabulku. Pojďme se připravit na vytváření sestav pomocí kontingenčních tabulek během několika minut.
Máme data pro stacionární objednávku.
Pole sloupců jsou:
Datum objednávky: Datum objednávky (samozřejmě)
Kraj: Oblast pořádku v zemi
Zákazník: Jméno zákazníka (co jiného to může být)
Položka: Objednaná položka
Jednotka: Počet jednotek objednané položky
Jednotková cena: Za jednotkové náklady
Celkový: Celkové náklady na objednávku (jednotka*jednotkové náklady).
K vytváření sestav pomocí kontingenčních tabulek použijeme
Pole kontingenční tabulky: Obsahuje seznam názvů sloupců ve vašich datech.
Pivotní oblasti: Tyto 4 oblasti slouží k zobrazení vašich dat způsobem.
FILTRY: Sem vložte pole, ze kterých chcete v přehledu použít filtry.
SLOUPCE: Sem vložte požadovaná pole do sloupců v sestavě: (Je lepší ukázat než vysvětlit)
ŘADY: Přetáhněte pole, která chcete zobrazit surově, jako na výše uvedeném obrázku, Region jsem ukázal v ŘADECH.
HODNOTY: Vyberte pole a získejte počet, součet, průměr, procento (a mnoho dalších) atd., Které chcete vidět.
Nyní s využitím výše uvedených informací jsme připravili tuto rychlou kontingenční zprávu, která ukazuje, ze které oblasti se zadává počet objednávek pro každou položku.
Nyní, když rozumíte svým datům a kontingenčním polím (koneckonců jste chytří), odpovíme rychle na některé otázky související s těmito daty pomocí kontingenční tabulky.
Q1. Kolik existuje objednávek?
Kontingenční tabulka je zpočátku prázdná, jak ukazuje obrázek níže.
Chcete -li zobrazit souhrn nebo podrobnosti o tomto poli, musíte vybrat pole (názvy sloupců) v příslušných oblastech.
Nyní, abych odpověděl na výše uvedenou otázku, v hodnotových polích vyberu položku (vyberte libovolný sloupec, jen se ujistěte, že mezi nimi není prázdná buňka).
Vyberte položku ze seznamu polí a přetáhněte ji do pole Hodnota.
Máme svoji odpověď. Kontingenční tabulky mi říkají, že existuje celkem 43 objednávek. Toto je správně.
Teď, když jsem si vybral Datum objednávky v oblasti Hodnoty to ukazuje 42. Tento prostředek Datum objednávky má prázdnou buňku od té doby víme, že celkový počet objednávek je 43.
Identifikujte nepravidelná data pomocí kontingenčních tabulek a vyčistěte je.
Kontingenční tabulka vám pomůže najít v datech nesprávné informace.
Většinu času naše data připravují operátoři zadávání dat nebo uživatelé, kteří jsou obvykle nepravidelní a potřebují nějaké vyčištění, aby připravili přesnou zprávu a analýzu.
Než připravíte jakékoli zprávy, měli byste svá data vždy vyčistit a připravit způsobem. Někdy však teprve po přípravě zprávy zjistíme, že naše data mají určitou nesrovnalost. Pojďte, ukážu vám, jak…
Q2: Sdělte počet objednávek z každého regionu
Nyní odpovězte na tuto otázku:
Vybrat Kraj a přetáhněte ji na Řádky Oblast a Položka na Hodnoty Plocha.
Získáme rozdělená data Region-Wise. Můžeme odpovědět, ze kterého regionu přišlo mnoho objednávek.
V našich datech jsou podle kontingenční tabulky celkem 4 oblasti. Ale počkejte, všimněte si toho Central a Centrle kraj. Víme, že Centrle by mělo být Centrální. Dochází k nesrovnalosti. Musíme přejít k našim datům a provést jejich čištění.
Chcete -li vyčistit data v poli regionu, odfiltrujeme nesprávný název regionu (Centrle) a opravíme ho (Central).
Nyní se vraťte k vašim kontingenčním údajům.
Klikněte pravým tlačítkem kamkoli na kontingenční tabulku a klikněte na Obnovit.
Vaše zpráva byla nyní aktualizována.
Nyní vidíte, že ve skutečnosti existují pouze 3 regiony.
Formátování kontingenční sestavy s kategorizovanými řádky.
Někdy budete potřebovat zprávy jako výše uvedený obrázek. Díky tomu je snadné zobrazit data strukturovaně. Můžete snadno zjistit, ze které oblasti je objednáno mnoho položek. Podívejme se, jak to můžete udělat.
Přestěhovat se Kraj a Položka na ŘADY plocha. Ujistěte se, že je oblast nahoře a položky dole, jak je znázorněno na obrázku.
Táhnout Položka pro Hodnota Plocha.
V důsledku toho dostanete tuto zprávu.
To bude stačit Někdy ale váš šéf chce hlásit v tabulkové podobě bez mezisoučtů. K tomu musíme naformátovat naši kontingenční tabulku.
Odebrat mezisoučty z kontingenční tabulky
Následuj tyto kroky:
1. Klikněte kamkoli na svou kontingenční tabulku.
2. Přejděte na Design Tab.
3. Klikněte na mezisoučty Jídelní lístek.
4. Klikněte na Nezobrazovat mezisoučty.
Vidíte, že nyní neexistují žádné mezisoučty.
Pokuta. Ale stále to není v tabulkové podobě. Regiony a položky jsou uvedeny v jednom sloupci. Ukažte je samostatně.
Vytvořte kontingenční tabulku
Chcete -li nyní zobrazit oblasti a položky v různých sloupcích, postupujte takto:
1. Klikněte kamkoli na kontingenční tabulku
2. Přejděte na kartu Design
3. Klikněte na Rozložení sestavy.
4. Klikněte na Zobrazit pro možnost Tabulkový formulář. Konečně budete mít tento propracovaný pohled na vaši zprávu.
Nyní známe celkový počet objednávek zadaných pro každou položku z každé oblasti. Je to ukázáno v Count sloupců položek.
Změňte prosím název sloupce na Objednávky.
Teď to vypadá lépe.
Q3: Kolik jednotek z každé položky je objednáno?
K zodpovězení této otázky potřebujeme součet jednotek. Chcete -li to provést, přesuňte pole Jednotky na Hodnoty. Automaticky sečte počet jednotek pro každou položku. Pokud sloupec v kontingenční tabulce obsahuje pouze hodnoty, kontingenční tabulka ve výchozím nastavení zobrazuje součet těchto hodnot. Lze to však změnit z nastavení pole hodnoty. Jak? Ukážu vám to druhé.
Nastavení pole hodnoty kontingenční tabulky
Q4: Průměrná cena každé položky?
V našich ukázkových datech je cena jedné položky pro různé objednávky odlišná. Viz například pořadače.
Chci znát průměrné náklady na každou položku v kontingenční tabulce. Chcete -li to zjistit, přetáhněte Jednotkové náklady do pole Hodnota. Zobrazí součet jednotkových nákladů.
Nechceme Součet jednotkových nákladů, chceme Průměr jednotkových nákladů. Udělat to tak…
1. Klepněte pravým tlačítkem kamkoli na součet sloupce Jednotkové náklady v kontingenční tabulce
2. Klikněte na Nastavení hodnotového pole
3. Na základě dostupných možností, vyberte Průměrný a stiskněte OK.
Nakonec budete mít tuto kontingenční zprávu:
Pole vypočtená v kontingenční tabulce
Jednou z nejužitečnějších funkcí kontingenční tabulky jsou její vypočtená pole. Vypočítaná pole jsou pole, která jsou získána některými operacemi s dostupnými sloupci.
Pojďme pochopit, jak vložit vypočítaná pole do kontingenční tabulky s jedním příkladem:
Na základě našich údajů jsme připravili tuto zprávu.
Tady máme Součet jednotek a Celkové náklady. Právě jsem přesunul celkový sloupec do pole Hodnoty a poté jej přejmenoval na Celkové náklady. Nyní chci znát průměrnou cenu jednotky každé položky pro každý region. A to by bylo:
Průměrná cena = celkové náklady / celkové jednotky
Vložíme do kontingenční tabulky pole, které ukazuje průměrnou cenu každé položky podle regionu:
Chcete -li vložit vypočítané pole do kontingenční tabulky, postupujte takto
1. Klikněte kamkoli na kontingenční tabulku a přejděte na kartu Analýza
2. Klikněte na pole, položky a sady ve skupině výpočtu.
3. Klikněte na Vypočítaná pole.
Zobrazí se toto vstupní pole pro vaše výpočetní pole:
4. Do pole pro zadání názvu napište průměrné náklady nebo cokoli, co se vám líbí, Excelu to nebude vadit. Do pole pro zadání vzorce napište a stiskněte OK.
= Celkem / Jednotky
Můžete to psát ručně z klávesnice nebo můžete poklepáním na názvy polí uvedených v oblasti Pole provádět operace.
5. Nyní máte vypočítané pole přidáno do kontingenční tabulky. Je pojmenována jako součet průměrných nákladů, ale není to součet. Excel pouze spustí výchozí funkci pro pojmenování sloupce (jako rituál). Přejmenujte tento sloupec a omezte zobrazené desetinné číslice.
A tam máte vypočítané pole. Můžete to udělat tak komplexně, jak chcete. Pro příklad jsem vzal tuto jednoduchou průměrnou operaci.
Seskupení v kontingenční tabulce
Tuto pivotní zprávu máte připravenou.
Nyní chci, aby byla tato zpráva rozdělena každoročně. Podívejte se na snímek níže.
Máme sloupec na Datum objednávky. Přesuňte pole OrderDate do řádků v horní části.
Nevypadá to jako požadovaná zpráva. Musíme mít skupinová data po celý rok.
Chcete -li nyní seskupit pole v kontingenční tabulce aplikace Excel, postupujte takto:
1. Klikněte pravým tlačítkem na pole, které chcete seskupit.
2. Klikněte na Skupina. Tuto možnost budete mít k přizpůsobení. Protože se jedná o datový sloupec, Excel nám podle toho ukazuje seskupení. Můžete si vybrat počáteční a koncové datum.
3. Vybírejte roky a stiskněte OK. Provedli jste roční seskupení v kontingenční tabulce aplikace Excel.
Kráječe kontingenční tabulky
Kráječe byly v aplikaci Excel 2010 zavedeny jako doplněk. V Excelu 2013 a 2016 je ve výchozím nastavení k dispozici stejně jako filtry.
Kráječe nejsou nic jiného než filtry. Na rozdíl od filtrů zobrazuje Slicers všechny dostupné možnosti přímo před vámi. Díky tomu bude váš dashboard interaktivnější.
Jak přidat kráječe do kontingenční tabulky, Excel 2016 a 2013
Kráječe kontingenčních tabulek lze snadno přidat. Následuj tyto kroky:
1. Klikněte kamkoli na kontingenční tabulku a přejděte na kartu Analyzovat.
2. Klikněte na Vložit kráječ. Budete mít seznam polí pro vaše data. Vyberte tolik, kolik chcete.
3. V tomto případě vyberte Region a stiskněte OK.
Do sestavy jste přidali kráječ. Nyní použijte filtr jediným kliknutím.
Vložte časovou osu do Excelu 2016 a 2013
Toto je jedna z mých oblíbených funkcí Excel kontingenčních tabulek. Tato nová funkce funguje pouze s daty. Pomocí toho můžete vizuálně vybrat časové období pro filtrování dat.
Chcete -li vložit kurzor časové osy, postupujte takto:
Jak přidat časovou osu do kontingenční tabulky, Excel 2016 a 2013
1. Klikněte kamkoli na kontingenční tabulku a přejděte na kartu Analyzovat.
2. Klikněte na Vložit časovou osu ze skupiny filtrů. Všechny sloupce obsahující časové hodnoty ve zdrojových datech budou uvedeny v poli možností, ze kterého si můžete vybrat. Tady máme jen jeden. Tak jo…
2. Vyberte své možnosti a stiskněte Enter nebo klikněte na OK. Je hotovo a časovou osu kontingenční tabulky máte přímo před sebou.
Můžete se rozhodnout zobrazovat jej denně, měsíčně, čtvrtletně nebo ročně. Vybral jsem Monthly zde.
V tomto článku jsem se zabýval nejdůležitějšími a nejužitečnějšími funkcemi kontingenční tabulky. Prozkoumali jsme nové funkce kontingenční tabulky v Excelu 2016 a 2013. Dozvěděli jsme se o klasickém používání kontingenční tabulky, která byla spuštěna v Excelu 2007, 2010 a starším. Stále jsou užitečné. Pokud jste zde nenašli odpověď související s vaší kontingenční tabulkou, zeptejte se v sekci komentáře.
Existuje mnoho dalších funkcí, které teprve budou vysvětleny. Pokročilou funkci kontingenční tabulky se naučíme v dalším článku. Do té doby Excel ve všem.