Príprava zdrojových údajov pre kontingenčnú tabuľku

Mať údaje v správnom formáte je zásadným krokom pri vytváraní robustnej a bezchybnej kontingenčnej tabuľky. Ak to neurobíte správne, môžete mať s kontingenčnou tabuľkou veľa problémov.

Aký je vhodný návrh zdrojových údajov pre kontingenčnú tabuľku?

Pozrime sa na príklad dobrých zdrojových údajov pre kontingenčnú tabuľku.

Tu je dôvod, prečo je to dobrý návrh zdrojových údajov:

• Prvý riadok obsahuje hlavičky, ktoré popisujú údaje v stĺpcoch.
• Každý stĺpec predstavuje jedinečnú kategóriu údajov. Stĺpec C napríklad obsahuje iba údaje o výrobkoch a stĺpec D a údaje iba o mesiaci.
• Každý riadok je záznam, ktorý by predstavoval jednu inštanciu transakcie alebo predaja.
• Dátové hlavičky sú jedinečné a nikde v súbore údajov sa neopakujú. Ak máte napríklad predajné čísla na štyri štvrťroky v roku, NEMALI by ste ich všetky pomenovať ako tržby. Namiesto toho dajte týmto hlavičkám stĺpcov jedinečné názvy, ako napríklad Predaj Q1, Predaj Q2 atď.…
  • Ak nemáte jedinečné názvy, môžete pokračovať a vytvoriť kontingenčnú tabuľku a Excel ich automaticky urobí jedinečnými pridaním prípony (napríklad Predaj, Predaj2, Predaj3). Bol by to však hrozný spôsob prípravy a používania kontingenčnej tabuľky.

Bežné nástrahy, ktorým sa treba vyhnúť pri príprave zdrojových údajov

• V zdrojových údajoch by nemali byť žiadne prázdne stĺpce. Tento je ľahké rozpoznať. Ak máte v zdrojových údajoch prázdny stĺpec, kontingenčnú tabuľku by ste nemohli vytvoriť. Zobrazí chybu, ako je uvedené nižšie.
• V zdrojových údajoch by nemali byť prázdne bunky/riadky. Aj keď môžete úspešne vytvoriť kontingenčnú tabuľku, napriek tomu, že máte prázdne bunky alebo riadky, existuje veľa vedľajších účinkov, ktoré vás môžu neskôr v priebehu dňa kousnúť.
  • Povedzme napríklad, že máte v stĺpci predaja prázdnu bunku. Ak vytvoríte kontingenčnú tabuľku pomocou týchto údajov a vložíte pole predaja do oblasti stĺpcov, zobrazí sa vám COUNT a nie SUM. Dôvodom je, že Excel interpretuje celý stĺpec ako textový údaj (len kvôli jednej prázdnej bunke).
• Na bunky v zdrojových dátach použite príslušný formát. Ak máte napríklad dátumy (ktoré sú uložené ako sériové čísla na serveri backend v programe Excel), použite jeden z prijateľných formátov dátumu. Pomohlo by vám to vytvoriť kontingenčnú tabuľku a použiť dátum ako jedno z kritérií na zhrnutie, zoskupenie a zoradenie údajov.
  • Ak máte pár sekúnd, skúste to. Formátujte dátumy v kontingenčnej tabuľke ako čísla a potom pomocou týchto údajov vytvorte kontingenčnú tabuľku. Teraz v kontingenčnej tabuľke vyberte pole s dátumom a uvidíte, čo sa stane. Automaticky ho vloží do oblasti hodnôt. Dôvodom je, že vaša kontingenčná tabuľka nevie, že ide o dátumy. Interpretuje to ako čísla.
• Neuvádzajte žiadne súčty stĺpcov, súčty riadkov, priemery atď. Ako súčasť zdrojových údajov. Keď máte kontingenčnú tabuľku, neskôr ich môžete ľahko získať.
• Vždy vytvorte tabuľku programu Excel a potom ju použite ako zdroj pre kontingenčnú tabuľku. Toto je skôr dobrá prax, a nie pasca. Vaša kontingenčná tabuľka bude fungovať dobre aj so zdrojovými údajmi, ktoré tiež nie sú tabuľkou programu Excel. Výhodou tabuľky programu Excel je, že môže upravovať rozširujúce sa údaje. Ak do množiny údajov pridáte ďalšie riadky, nemusíte zdrojové údaje upravovať znova a znova. Kontingenčnú tabuľku môžete jednoducho aktualizovať a automaticky by zohľadňovala nové riadky pridané do zdrojových údajov.

Príklady návrhov zlých zdrojov údajov

Pozrime sa na niektoré zlé príklady návrhov zdrojových údajov.

Zlý návrh údajov zdroja - príklad 1

Toto je bežný spôsob uchovávania údajov, pretože je ľahké ich sledovať a porozumieť im. S týmto usporiadaním údajov existujú dva problémy:

• Nedostanete úplný obraz. Môžete napríklad vidieť tržby za Mid West v štvrťroku 1 2924300. Ide však o jeden predaj alebo o niekoľko predajov. Ak máte každý záznam k dispozícii v samostatnom riadku, môžete urobiť lepšiu analýzu.
• Ak budete pokračovať a vytvoríte kontingenčnú tabuľku pomocou tohto (čo môžete), získate rôzne polia pre rôzne štvrťroky. Niečo, ako je uvedené nižšie:

Zlý návrh údajov zdroja - príklad 2

Túto reprezentáciu údajov môže manažment a publikum prezentácií programu PowerPoint dobre prijať, ale nie je vhodná na vytvorenie kontingenčnej tabuľky.

Opäť je to ten druh súhrnu, ktorý môžete ľahko vytvoriť pomocou kontingenčnej tabuľky. Takže aj keď budete nakoniec chcieť taký vzhľad pre svoje údaje, udržujte zdrojové údaje vo formáte pripravenom na kontingenciu a vytvorte toto zobrazenie pomocou kontingenčnej tabuľky.

Zlý návrh údajov zdroja - príklad 3

Toto je opäť výstup, ktorý je možné ľahko získať pomocou kontingenčnej tabuľky. Nedá sa však použiť na vytvorenie kontingenčnej tabuľky.

V súbore údajov sú prázdne bunky a štvrtiny sú rozložené ako hlavičky stĺpcov.

Región je uvedený aj na začiatku, pričom by mal byť súčasťou každého záznamu.

[PRÍPADOVÁ ŠTÚDIA] Konverzia zle naformátovaných údajov na zdrojové údaje pripravené z kontingenčnej tabuľky

Niekedy môžete získať množinu údajov, ktorá nie je vhodná na použitie ako zdrojové údaje pre kontingenčnú tabuľku. V takom prípade vám môže neostať nič iné, ako previesť údaje do formátu údajov kompatibilného s Pivotom.

Tu je príklad zlého návrhu údajov:

Teraz môžete pomocou funkcií Excelu alebo kontingenčného dotazu tieto údaje previesť na formát, ktorý možno použiť ako zdrojové údaje pre kontingenčnú tabuľku.

Pozrime sa, ako fungujú obe tieto metódy.

Metóda 1: Použitie vzorcov programu Excel

Pozrime sa, ako pomocou funkcií Excelu prevádzať tieto údaje do formátu pripraveného pre kontingenčnú tabuľku.

• Vytvorte jedinečnú hlavičku stĺpca pre všetky kategórie v pôvodnej množine údajov. V tomto prípade by to bol región, štvrťrok a predaj.
• V bunke pod hlavičkou regiónu použite nasledujúci vzorec: = INDEX ($ A $ 2: $ A $ 5, ROUNDUP (ROWS ($ A $ 2: A2)/COUNTA ($ B $ 1: $ E $ 1), 0))
  • Potiahnutím vzorca nadol sa budú opakovať všetky oblasti.
• V bunke pod hlavičkou štvrťroka použite nasledujúci vzorec: = INDEX ($ B $ 1: $ E $ 1, ROUNDUP (MOD (ROWS ($ A $ 2: A2), COUNTA ($ B $ 1: $ E $ 1) +0,1)) , 0))
  • Potiahnite vzorec nadol a zopakuje všetky štvrtiny.
• V hlavičke nižšie Predaje použite nasledujúci vzorec: = INDEX ($ B $ 2: $ E $ 5, MATCH (G2, $ A $ 2: $ A $ 5,0), MATCH (H2, $ B $ 1: $ E $ 1,0 )))
  • Potiahnutím nadol získate všetky hodnoty. Tento vzorec používa ako vyhľadávacie hodnoty údaje regiónu a štvrťroka a vracia hodnotu predaja z pôvodnej množiny údajov.

Teraz môžete tieto výsledné údaje použiť ako zdrojové údaje pre kontingenčnú tabuľku.

Kliknutím sem stiahnete vzorový súbor.

Metóda 2: Použitie Power Query

Power Query má funkciu, ktorá dokáže tento typ údajov jednoducho previesť do formátu údajov pripraveného pre Pivot.

Ak používate Excel 2016, funkcie Power Query by boli k dispozícii na karte Údaje v skupine Získať a transformovať. Ak používate Excel 2013 alebo predchádzajúce verzie, môžete ho použiť ako doplnok.

Tu je vynikajúci sprievodca inštaláciou Power Query od Jona z Excelu Campus.

Opäť, vzhľadom na to, že máte údaje naformátované nasledujúcim spôsobom:

Tu sú kroky na konverziu zdrojových údajov do formátu pripraveného pre kontingenčnú tabuľku:

• Skonvertujte údaje na tabuľku programu Excel. Vyberte množinu údajov a prejdite na položku Vložiť -> Tabuľky -> Tabuľka.
• V dialógovom okne Vložiť tabuľku skontrolujte, či je vybratý správny rozsah, a kliknite na tlačidlo OK. Tým sa tabuľkové údaje skonvertujú na tabuľku programu Excel.
• V Exceli 2016 prejdite na Údaje -> Získať a transformovať -> Z tabuľky.
  • Ak používate doplnok Power Query v predchádzajúcej verzii, prejdite na Power Query -> Externé údaje -> Z tabuľky.
• V editore dotazu vyberte stĺpce, ktoré chcete zrušiť otočenie. V tomto prípade sú to tie na štyri štvrtiny. Ak chcete vybrať všetky stĺpce, podržte kláves Shift a potom vyberte prvý a potom posledný stĺpec.
• V editore dopytov prejdite na položku Transformovať -> Akýkoľvek stĺpec -> Zrušiť otočenie stĺpcov. Tým sa údaje stĺpca prevedú do formátu kompatibilného s kontingenčnou tabuľkou.
• Power Query dáva stĺpcom všeobecné názvy. Zmeňte tieto názvy na požadované. V takom prípade zmeňte atribút na štvrťrok a hodnotu predaja.
• V editore dotazov choďte na Súbor -> Zatvoriť a načítať. Tým sa zatvorí dialógové okno Editora Power Query a vytvorí sa samostatný pracovný hárok, v ktorom budú údaje s neotočenými stĺpcami.

Teraz, keď viete, ako pripraviť zdrojové údaje pre kontingenčnú tabuľku, ste pripravení na Excel vo svete kontingenčných tabuliek.

Tu je niekoľko ďalších návodov pre kontingenčnú tabuľku, ktoré môžu byť užitočné:

• Ako obnoviť kontingenčnú tabuľku v programe Excel.
• Používanie krájačov v kontingenčnej tabuľke programu Excel - príručka pre začiatočníkov.
• Ako zoskupiť dátumy v kontingenčných tabuľkách v programe Excel.
• Ako zoskupiť čísla v kontingenčnej tabuľke v programe Excel.
• Kontingenčná vyrovnávacia pamäť v programe Excel - čo to je a ako ju najlepšie použiť.
• Ako filtrovať údaje v kontingenčnej tabuľke v programe Excel.
• Ako pridať a používať vypočítané pole kontingenčnej tabuľky programu Excel.
• Ako použiť podmienené formátovanie v kontingenčnej tabuľke v programe Excel.
• Ako nahradiť prázdne bunky nulami v kontingenčných tabuľkách programu Excel.