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 znovu vybírali data.
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í aplikace Create Kontingenční tabulka volitelný box.
Stiskněte Altbutton 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 a nezlikvidují staré funkce (jako to udělal MS s win 8. Bylo to ubohé). Tento umožňuje staršímu uživateli normálně pracovat na nových verzích, jako dříve pracoval na starších verzích.
Pokud postupně stisknete ALT, D a P na klávesnici se otevře Excel a vytvoří průvodce kontingenční tabulkou.
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 (Očividně)
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).
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.
SLOUPKY: 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, vyberu Položka (vyberte libovolný sloupec, jen se ujistěte, že mezi ním není prázdná buňka) v hodnotových polích.
Vybrat Položka ze seznamu polí a přetáhněte jej do pole Hodnota.
Máme svoji odpověď. Kontingenční tabulky mi říkají, že existuje celkem 43 objednávek. Toto je správně.
Profesionální tip: Měli byste zkontrolovat data, zda jsou správná nebo ne. Pokud se toto číslo neshoduje s daty, znamená to, že jste vybrali nesprávný rozsah nebo že pole obsahuje prázdné buňky.
Informace: Pole hodnoty ve výchozím nastavení počítá počet položek ve sloupci, pokud obsahuje text, a součty, pokud pole obsahuje pouze hodnoty. Můžete to změnit v nastavení hodnotového pole. Jak? Uvidíme se později v tomto kurzu kontingenční tabulky.
Teď, když jsem si vybral Datum objednávky ve Valuesarea, ukazuje 42. Toto 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 Centrální 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.
INFO: Bez ohledu na to, jaké změny ve zdrojových datech provedete, bude kontingenční tabulka nadále fungovat na starých datech dokud ji neobnovíte. Excel vytvoří kontingenční mezipaměť a v této mezipaměti běží kontingenční tabulka. Po aktualizaci se stará mezipaměť změní s novými daty.
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 nabídku Mezisoučty.
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 regiony 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 tabulkový formulář volba. 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 položek sloupce.
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…
- Klikněte pravým tlačítkem kamkoli na součet sloupce Jednotkové náklady v kontingenční tabulce
- Klikněte Nastavení hodnotového pole
- Na základě dostupných možností, vybrat 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 Analyzovat tab
2. Klikněte na Pole, položky a sady ve výpočetní skupině.
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ěr Náklady nebo cokoli se vám líbí, Excelu to nebude vadit. V vzorec vstupního pole, 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.
