Microsoft Excel: Pivotní tabulky
Přehledné tabulky Excel poskytují uživatelům možnost na jednom místě seskupit významné množství informací obsažených v těžkopádných tabulkách a také sestavovat složité přehledy. V takovém případě se hodnoty souhrnných tabulek aktualizují automaticky, když se změní hodnota libovolné přidružené tabulky. Zjistíme, jak vytvořit kontingenční tabulku v aplikaci Microsoft Excel.
Obsah
Vytvořte kontingenční tabulku obvyklým způsobem
Přesto budeme uvažovat o procesu vytváření kontingenční tabulky pomocí příkladu aplikace Microsoft Excel 2010, ale tento algoritmus je použitelný pro jiné moderní verze této aplikace.
Za základ se budeme věnovat tabulce plateb mzdy zaměstnancům podniku. Označuje jména zaměstnanců, pohlaví, kategorii, datum platby a výši platby. To znamená, že každá epizoda platby jednotlivému zaměstnanci odpovídá samostatnému řádku tabulky. Chaoticky umístěné údaje seskupíme do této tabulky do jedné souhrnné tabulky. V takovém případě budou data převzata pouze pro třetí čtvrtletí roku 2016. Podívejme se, jak to udělat na konkrétním příkladu.
Nejprve převedeme původní tabulku na dynamickou tabulku. To je nutné, pokud přidáte řádky a další data, jsou automaticky přitahovány do kontingenční tabulky. K tomu se stáváme kurzor na libovolné buňce tabulky. Poté v bloku "Styly" na pásu karet klikněte na tlačítko "Formát jako tabulka". Vyberte si libovolný styl, který se vám líbí.
Dále se otevře dialogové okno, které nás vyzývá k zadání souřadnic umístění v tabulce. Ve výchozím nastavení jsou však souřadnice, které program nabízí a pokrývá tak celou tabulku. Takže můžeme souhlasit pouze a kliknout na tlačítko "OK". Ale uživatelé by měli vědět, že pokud chtějí, mohou změnit parametry pokrytí oblasti tabulky.
Poté se tabulka změní na dynamickou a automatickou změnu. Získá také jméno, které může uživatel kdykoli změnit na to, co mu vyhovuje. Název tabulky můžete zobrazit nebo změnit na kartě Návrhář.
Chcete-li začít vytvářet kontingenční tabulku přímo, přejděte na kartu "Vložit". Klikněte na první tlačítko v pásu, které se nazývá "Kontingenční tabulka". Poté se otevře nabídka, v níž byste měli vybrat to, co vytvoříme, tabulku nebo graf. Klikněte na tlačítko "Souhrnná tabulka".
Otevře se okno, v němž opět potřebujeme vybrat rozsah nebo název tabulky. Jak můžete vidět, program již utišil název našeho stolu, takže už není nic co dělat. V dolní části dialogového okna můžete vybrat, kde vytvořit souhrnnou tabulku: na novém listu (ve výchozím nastavení) nebo na stejném listu. Samozřejmě je ve většině případů mnohem pohodlnější použít souhrnnou tabulku na samostatném listu. Ale toto je individuální záležitost každého uživatele, který závisí na jeho preferencích a úkolech. Stačí kliknout na tlačítko "OK".
Poté se na novém listu otevře formulář vytvoření kontingenční tabulky.
Jak vidíte, v pravé části okna je seznam polí tabulky a méně než čtyři oblasti:
- Názvy řádků;
- Názvy sloupců;
- Hodnoty;
- Filtrování sestavy.
Jednoduše přetahujeme pomocí myši pole tabulky, které potřebujeme, v oblastech odpovídajících našim potřebám. Neexistuje jasné pravidlo o tom, které pole se mají přesunout, protože vše závisí na zdrojové tabulce a na specifických úkolech, které se mohou změnit.
V tomto konkrétním případě jsme do polí "Filtr sestav", políčko "Kategorie osob" v poli "Názvy sloupců", pole "Jméno" v poli "Názvy řádků", pole "Částka" mzdy "do oblasti" Hodnoty ". Je třeba poznamenat, že všechny aritmetické výpočty dat získaných z jiné tabulky jsou možné pouze v poslední oblasti. Jak vidíte, během doby, kdy jsme provedli tyto manipulace s přenosem polí v oblasti, změnila se odpovídající tabulka v levé části okna.
Toto je souhrnná tabulka. Nad tabulkou se filtry zobrazují podle pohlaví a data.
Konfigurace kontingenční tabulky
Ale jak si pamatujeme, v tabulce by měly zůstat údaje pouze za třetí čtvrtletí. Zatím jsou zobrazena data za celé období. Chcete-li tabulku přinést do požadované formy, klikněte na tlačítko vedle filtru "Datum". V zobrazeném okně zaškrtněte políčko naproti nápisu "Vybrat několik prvků". Poté odstraňte zaškrtnutí ze všech dat, které se ve třetím čtvrtletí nezachytí. V našem případě je to jen jeden den. Klikněte na tlačítko "OK".
Stejným způsobem můžeme použít filtr na podlaze a zvolit pro sestavu například pouze některé muže.
Poté získala tato souhrnná tabulka tento formulář.
Chcete-li ukázat, že můžete spravovat data v tabulce podle libosti, znovu otevřete formulář seznamu polí. Chcete-li to provést, přejděte na kartu "Parametry" a klikněte na tlačítko "Seznam polí". Poté přesuňte pole "Datum" z oblasti "Filtr sestav" do oblasti "Název řádku" a mezi oblastmi "Personální kategorie" a "Pohlaví" vyměňte oblasti. Všechny operace jsou prováděny jednoduchým přetažením prvků.
Nyní má tabulka zcela odlišný vzhled. Sloupce jsou rozděleny podle pohlaví, řady byly rozděleny podle měsíců a tabulka může být nyní filtrována podle kategorie zaměstnanců.
Pokud přesunete název řádků do seznamu polí a uveďte datum vyšší než jméno, budou data placení rozdělena na jména zaměstnanců.
Rovněž můžete zobrazit číselné hodnoty tabulky jako histogram. Chcete-li to provést, vyberte v tabulce buňku s číselnou hodnotou, přejděte na kartu "Domov", klikněte na tlačítko "Podmíněné formátování", přejděte na položku "Histogram" a vyberte typ histogramu, který se vám líbí.
Jak je vidět, histogram se zobrazí pouze v jedné buňce. Chcete-li použít pravidlo histogramu pro všechny buňky v tabulce, klikneme na tlačítko zobrazené vedle histogramu a v otevřeném okně přepneme přepínač na pozici "Všechny buňky".
Náš souhrnný stůl nyní získal prezentovatelný vzhled.
Vytvořte kontingenční tabulku pomocí Průvodce kontingenční tabulkou
Pomocí kontingenčního průvodce můžete vytvořit kontingenční tabulku. Ale pro to stačí přenést tento nástroj na panel nástrojů Rychlý přístup. Přejděte do položky nabídky "Soubor" a klikněte na tlačítko "Možnosti".
V okně otevřených parametrů přejděte na panel nástrojů Rychlý přístup. Vyberte příkazy z příkazů na pásku. V seznamu položek vyhledejte průvodce "PivotTable and Pie Chart". Vyberte jej, klikněte na tlačítko "Přidat" a klikněte na tlačítko "OK" v pravém dolním rohu okna.
Jak vidíte, po našich akcích se na panelu nástrojů Rychlý přístup objevila nová ikona. Klepněte na ni.
Poté se otevře Průvodce kontingenční tabulkou. Jak vidíte, máme čtyři varianty zdroje dat, z něhož bude vytvořena souhrnná tabulka:
- v seznamu nebo v databázi Microsoft Excel;
- v externím zdroji dat (jiný soubor);
- v několika oblastech konsolidace;
- v jiné souhrnné tabulce nebo v souhrnné tabulce.
Níže vyberte, co budeme vytvářet, souhrnnou tabulku nebo graf. Vyberte si a klikněte na tlačítko "Další".
Poté se zobrazí okno s rozsahem tabulky s údaji, které můžete změnit, pokud chcete, ale to nemusíme dělat. Stačí kliknout na tlačítko "Další".
Další informace naleznete v Průvodci kontingenčními kartami, kde můžete vybrat místo, kde se nová tabulka umístí na stejný list nebo na novou. Proveďte výběr a klikněte na tlačítko Hotovo.
Poté se otevře nový list s přesně stejným tvarem jako obvyklý způsob vytvoření kontingenční tabulky. Proto nemá smysl zastavit se odděleně.
Všechna další opatření jsou prováděna podle stejného algoritmu, jak je popsáno výše.
Jak můžete vidět, můžete vytvořit kontingenční tabulku v aplikaci Microsoft Excel dvěma způsoby: obvyklým způsobem pomocí tlačítka na kazetě a pomocí Průvodce kontingenční tabulkou. Druhá metoda poskytuje další možnosti, ale ve většině případů je pro první úkol dostatečná funkčnost první možnosti. Souhrnné tabulky mohou generovat data v přehledech téměř na jakákoli kritéria, která uživatel určí v nastaveních.