Příprava zdrojových dat pro kontingenční tabulku

Mít data ve správném formátu je zásadním krokem při vytváření robustní a bezchybné kontingenční tabulky. Pokud to neuděláte správně, můžete mít s kontingenční tabulkou mnoho problémů.

Jaký je dobrý návrh zdrojových dat pro kontingenční tabulku?

Podívejme se na příklad dobrých zdrojových dat pro kontingenční tabulku.

Díky tomu je dobrý návrh zdrojových dat:

• První řádek obsahuje záhlaví, která popisují data ve sloupcích.
• Každý sloupec představuje jedinečnou datovou kategorii. Například sloupec C obsahuje pouze produktová data a sloupec D a pouze měsíční data.
• Každý řádek je záznam, který by představoval jednu instanci transakce nebo prodeje.
• Datová záhlaví jsou jedinečná a nikde v datové sadě se neopakují. Pokud například máte prodejní čísla za čtyři čtvrtletí v roce, NEMĚLI byste je všechny pojmenovat jako prodeje. Místo toho dejte těmto záhlavím sloupců jedinečné názvy, jako například Prodej Q1, Prodej Q2 atd.…
  • Pokud nemáte jedinečné názvy, můžete pokračovat a vytvořit kontingenční tabulku a Excel je automaticky učiní jedinečnými přidáním přípony (například Prodej, Prodej2, Prodej3). To by však byl hrozný způsob přípravy a používání kontingenční tabulky.

Společná úskalí, kterým je třeba se vyhnout při přípravě zdrojových dat

• Ve zdrojových datech by neměly být žádné prázdné sloupce. Tenhle je snadno rozpoznatelný. Pokud máte ve zdrojových datech prázdný sloupec, nebudete moci vytvořit kontingenční tabulku. Zobrazí se chyba, jak je uvedeno níže.
• Ve zdrojových datech by neměly být prázdné buňky/řádky. I když můžete úspěšně vytvořit kontingenční tabulku, přestože máte prázdné buňky nebo řádky, existuje mnoho vedlejších účinků, které vás mohou v průběhu dne kousnout.
  • Řekněme například, že máte ve sloupci prodeje prázdnou buňku. Pokud pomocí těchto dat vytvoříte kontingenční tabulku a do oblasti sloupců vložíte prodejní pole, zobrazí se vám POČET a nikoli SUM. Důvodem je, že Excel interpretuje celý sloupec jako textová data (jen kvůli jedné prázdné buňce).
• Použijte příslušný formát na buňky ve zdrojových datech. Máte -li například data (která jsou v backendu v Excelu uložena jako sériová čísla), použijte jeden z přijatelných formátů data. To by vám pomohlo vytvořit kontingenční tabulku a použít Datum jako jedno z kritérií pro shrnutí, seskupení a třídění dat.
  • Pokud máte pár sekund, zkuste to. Formátujte data v kontingenční tabulce jako čísla a poté pomocí těchto dat vytvořte kontingenční tabulku. Nyní v kontingenční tabulce vyberte pole data a uvidíte, co se stane. Automaticky jej vloží do oblasti hodnot. Důvodem je, že vaše kontingenční tabulka neví, že se jedná o data. Interpretuje je jako čísla.
• Nezahrnujte žádné součty sloupců, součty řádků, průměry atd. Jako součást zdrojových dat. Jakmile máte kontingenční tabulku, můžete je snadno získat později.
• Vždy vytvořte tabulku aplikace Excel a poté ji použijte jako zdroj pro kontingenční tabulku. Toto je spíše dobrá praxe a ne úskalí. Vaše kontingenční tabulka bude fungovat dobře i se zdrojovými daty, která také nejsou tabulkou aplikace Excel. Výhodou tabulky Excel je, že dokáže upravit rozšiřující data. Pokud do datové sady přidáte další řádky, nemusíte zdrojová data upravovat znovu a znovu. Jednoduše můžete aktualizovat kontingenční tabulku a automaticky by zohledňovala nové řádky přidané do zdrojových dat.

Příklady návrhů špatných zdrojových dat

Podívejme se na některé špatné příklady návrhů zdrojových dat.

Špatný návrh zdrojových dat - příklad 1

Toto je běžný způsob, jak udržovat data, protože je lze snadno sledovat a pochopit. S tímto uspořádáním dat jsou dva problémy:

• Nedostanete úplný obrázek. Můžete například vidět, že tržby za Mid West ve čtvrtletí 1 jsou 2924300. Jde ale o jeden prodej nebo o několik prodejů. Pokud máte každý záznam k dispozici v samostatném řádku, můžete provést lepší analýzu.
• Pokud budete pokračovat a vytvoříte kontingenční tabulku pomocí tohoto (což můžete), získáte různá pole pro různá čtvrtletí. Něco, jak je uvedeno níže:

Špatný návrh zdrojových dat - příklad 2

Tato reprezentace dat může být dobře přijata managementem a publikem prezentací PowerPoint, ale není vhodná pro vytváření kontingenční tabulky.

Opět je to ten druh souhrnu, který můžete snadno vytvořit pomocí kontingenční tabulky. Takže i když nakonec budete chtít takový vzhled pro svá data, udržujte zdrojová data ve formátu připraveném pro kontingenci a vytvořte toto zobrazení pomocí kontingenční tabulky.

Špatný návrh zdrojových dat - příklad 3

Toto je opět výstup, který lze snadno získat pomocí kontingenční tabulky. Nelze ho však použít k vytvoření kontingenční tabulky.

V datové sadě jsou prázdné buňky a čtvrtiny jsou rozloženy jako záhlaví sloupců.

Oblast je také uvedena nahoře, zatímco by měla být součástí každého záznamu.

[PŘÍPADOVÁ STUDIE] Převod špatně formátovaných dat na zdrojová data připravená pro kontingenční tabulku

Někdy můžete získat datovou sadu, která není vhodná k použití jako zdrojová data pro kontingenční tabulku. V takovém případě vám možná nezbude nic jiného, ​​než data převést do datového formátu, který je přátelský k Pivot.

Zde je příklad špatného návrhu dat:

Nyní můžete použít funkce Excelu nebo kontingenční dotaz k převodu těchto dat do formátu, který lze použít jako zdrojová data pro kontingenční tabulku.

Podívejme se, jak obě tyto metody fungují.

Metoda 1: Použití vzorců aplikace Excel

Podívejme se, jak pomocí funkcí Excelu převést tato data do formátu připraveného pro kontingenční tabulku.

• Vytvořte jedinečné záhlaví sloupce pro všechny kategorie v původní datové sadě. V tomto případě by to byla oblast, čtvrtletí a prodej.
• V buňce pod hlavičkou Regionu použijte následující vzorec: = INDEX ($ A $ 2: $ A $ 5, ROUNDUP (ROWS ($ A $ 2: A2)/COUNTA ($ B $ 1: $ E $ 1), 0))
  • Přetáhněte vzorec dolů a bude opakovat všechny oblasti.
• V buňce pod záhlavím čtvrtletí použijte následující vzorec: = INDEX ($ B $ 1: $ E $ 1, ROUNDUP (MOD (ROWS ($ A $ 2: A2), COUNTA ($ B $ 1: $ E $ 1) +0,1)) , 0))
  • Přetáhněte vzorec dolů a bude opakovat všechny čtvrtiny.
• V záhlaví pod Prodejem použijte následující vzorec: = INDEX ($ B $ 2: $ E $ 5, MATCH (G2, $ A $ 2: $ A $ 5,0), MATCH (H2, $ B $ 1: $ E $ 1,0 )))
  • Přetažením dolů získáte všechny hodnoty. Tento vzorec používá jako vyhledávací hodnoty data Region a Quarter a vrací prodejní hodnotu z původní datové sady.

Nyní můžete tato výsledná data použít jako zdrojová data pro kontingenční tabulku.

Kliknutím sem stáhnete ukázkový soubor.

Metoda 2: Použití Power Query

Power Query má funkci, která dokáže tento typ dat snadno převést do datového formátu připraveného pro Pivot.

Pokud používáte Excel 2016, funkce Power Query by byly k dispozici na kartě Data ve skupině Získat a transformovat. Pokud používáte Excel 2013 nebo předchozí verze, můžete jej použít jako doplněk.

Zde je vynikající průvodce instalací Power Query od Jon z Excel Campus.

Opět platí, že vzhledem k tomu, že máte data naformátovaná níže:

Zde jsou kroky pro převod zdrojových dat do formátu připraveného pro kontingenční tabulku:

• Převeďte data do tabulky aplikace Excel. Vyberte datovou sadu a přejděte na Vložit -> Tabulky -> Tabulka.
• V dialogovém okně Vložit tabulku zkontrolujte, zda je vybrán správný rozsah, a klikněte na OK. Tím se převedou tabulková data na tabulku aplikace Excel.
• V aplikaci Excel 2016 přejděte na Data -> Získat a transformovat -> Z tabulky.
  • Pokud používáte doplněk Power Query v předchozí verzi, přejděte na Power Query -> Externí data -> Z tabulky.
• V editoru dotazů vyberte sloupce, které chcete zrušit. V tomto případě jsou to ty za čtyři čtvrtiny. Chcete -li vybrat všechny sloupce, podržte klávesu Shift a poté vyberte první sloupec a poté poslední sloupec.
• V editoru dotazů přejděte na Transformace -> Jakýkoli sloupec -> Zrušit sloupce. Tím se data sloupce převedou do formátu vhodného pro kontingenční tabulku.
• Power Query dává sloupcům obecná jména. Změňte tato jména na požadovaná. V takovém případě změňte atribut na čtvrtletí a hodnotu prodeje.
• V editoru dotazů přejděte na Soubor -> Zavřít a načíst. Tím se zavře dialogové okno Power Query Editor a vytvoří se samostatný list, který bude obsahovat data s neotočenými sloupci.

Nyní, když víte, jak připravit zdrojová data pro kontingenční tabulku, jste připraveni na Excel ve světě kontingenčních tabulek.

Zde jsou některé další výukové programy pro kontingenční tabulky, které mohou být užitečné:

• Jak aktualizovat kontingenční tabulku v aplikaci Excel.
• Používání kráječů v kontingenční tabulce aplikace Excel - příručka pro začátečníky.
• Jak seskupit data v kontingenčních tabulkách v aplikaci Excel.
• Jak seskupit čísla v kontingenční tabulce v aplikaci Excel.
• Kontingenční mezipaměť v aplikaci Excel - co to je a jak ji nejlépe použít.
• Jak filtrovat data v kontingenční tabulce v aplikaci Excel.
• Jak přidat a používat vypočítané pole kontingenční tabulky aplikace Excel.
• Jak použít podmíněné formátování v kontingenční tabulce v aplikaci Excel.
• Jak nahradit prázdné buňky nulami v kontingenčních tabulkách aplikace Excel.