Ak čítate tento návod, je veľká šanca, že ste už počuli (alebo dokonca použili) kontingenčnú tabuľku programu Excel. Je to jedna z najvýkonnejších funkcií v Exceli (bez srandy).
Najlepšia časť o používaní kontingenčnej tabuľky je, že aj keď nič neviete v programe Excel, stále s ním môžete robiť celkom úžasné veci s veľmi základným porozumením.
Začnime.
Kliknite tu stiahnite si vzorové údaje a postupujte podľa nich.
Čo je to kontingenčná tabuľka a prečo by vás to malo zaujímať?
Kontingenčná tabuľka je nástroj v programe Microsoft Excel, ktorý vám umožňuje rýchlo zhrnúť obrovské množiny údajov (niekoľkými kliknutiami).
Aj keď ste vo svete Excelu úplným nováčikom, kontingenčnú tabuľku môžete ľahko použiť. Vytváranie prehľadov je také jednoduché ako presúvanie hlavičiek riadkov/stĺpcov.
Predpokladajme, že máte množinu údajov, ako je uvedené nižšie:
Jedná sa o údaje o predaji, ktoré pozostávajú z ~ 1 000 riadkov.
Obsahuje údaje o predaji podľa regiónu, typu maloobchodníka a zákazníka.
Teraz môže váš šéf chcieť z týchto údajov vedieť niekoľko vecí:
- Aké boli celkové tržby v južnom regióne v roku 2016?
- Akých je päť najlepších maloobchodníkov podľa tržieb?
- Ako sa výkonnosť Home Depot porovnávala s inými maloobchodníkmi na juhu?
Môžete pokračovať a používať funkcie Excelu, ktoré vám poskytnú odpovede na tieto otázky, ale čo keď zrazu váš šéf príde so zoznamom ďalších piatich otázok.
Vždy, keď dôjde k zmene, budete sa musieť vrátiť k údajom a vytvoriť nové vzorce.
Tu sú kontingenčné tabuľky programu Excel skutočne užitočné.
Kontingenčná tabuľka odpovie na všetky tieto otázky (ako sa dozviete nižšie) v priebehu niekoľkých sekúnd.
Skutočnou výhodou však je, že sa môže prispôsobiť vášmu citlivému šéfovi založenému na dátach tým, že okamžite odpovie na jeho otázky.
Je to tak jednoduché, môžete si urobiť pár minút a ukázať svojmu šéfovi, ako to urobiť sám.
Našťastie teraz máte predstavu o tom, prečo sú kontingenčné tabuľky také úžasné. Pokračujme a vytvoríme kontingenčnú tabuľku pomocou množiny údajov (zobrazenej vyššie).
Vloženie kontingenčnej tabuľky do Excelu
Tu sú kroky na vytvorenie kontingenčnej tabuľky pomocou vyššie uvedených údajov:
- Kliknite kdekoľvek v množine údajov.
- Prejdite na položku Vložiť -> Tabuľky -> Kontingenčná tabuľka.
- V dialógovom okne Vytvoriť kontingenčnú tabuľku fungujú predvolené možnosti vo väčšine prípadov dobre. Tu je niekoľko vecí, ktoré je potrebné skontrolovať:
- Tabuľka/rozsah: Štandardne sa vypĺňa na základe vašej množiny údajov. Ak vaše údaje neobsahujú žiadne prázdne riadky/stĺpce, Excel automaticky identifikuje správny rozsah. V prípade potreby to môžete manuálne zmeniť.
- Ak chcete kontingenčnú tabuľku vytvoriť na konkrétnom mieste, v možnosti „Vyberte, kam chcete umiestniť prehľad kontingenčnej tabuľky“ zadajte umiestnenie. V opačnom prípade sa pomocou kontingenčnej tabuľky vytvorí nový pracovný hárok.
- Kliknite na tlačidlo OK.
Hneď ako kliknete na tlačidlo OK, vytvorí sa nový pracovný hárok s kontingenčnou tabuľkou.
Kým bola kontingenčná tabuľka vytvorená, neuvidíte v nej žiadne údaje. Všetko, čo by ste videli, je názov kontingenčnej tabuľky a jednoriadková inštrukcia vľavo a polia kontingenčnej tabuľky vpravo.
Teraz, než sa pustíme do analýzy údajov pomocou tejto kontingenčnej tabuľky, porozumieme tým, čo sú matice a skrutky, ktoré robia kontingenčnú tabuľku programu Excel.
Matice a skrutky v kontingenčnej tabuľke programu Excel
Na efektívne používanie kontingenčnej tabuľky je dôležité poznať komponenty, ktoré kontingenčnú tabuľku vytvárajú.
V tejto sekcii sa dozviete o:
- Pivot Cache
- Oblasť hodnôt
- Oblasť riadkov
- Oblasť stĺpcov
- Oblasť filtrov
Pivot Cache
Hneď ako vytvoríte kontingenčnú tabuľku pomocou údajov, v backende sa niečo stane. Excel urobí snímku údajov a uloží ich do svojej pamäte. Táto snímka sa nazýva Pivot Cache.
Keď vytvárate rôzne zobrazenia pomocou kontingenčnej tabuľky, Excel sa nevracia späť k zdroju údajov, ale používa kontingenčnú vyrovnávaciu pamäť na rýchlu analýzu údajov a poskytnutie súhrnu/výsledkov.
Dôvodom generovania medzipamäte je optimalizácia fungovania kontingenčnej tabuľky. Aj keď máte tisíce riadkov údajov, kontingenčná tabuľka je pri súhrne údajov super rýchla. Položky môžete presúvať a presúvať do riadkov/stĺpcov/hodnôt/filtrov a výsledky sa okamžite aktualizujú.
Poznámka: Jednou z nevýhod pivot cache je, že zväčšuje veľkosť vášho zošita. Keďže ide o repliku zdrojových údajov, pri vytváraní kontingenčnej tabuľky sa kópia týchto údajov uloží do kontingenčnej pamäte cache.
Čítaj viac: Čo je to medzipamäť Pivot a ako ju najlepšie používať.
Oblasť hodnôt
Výpočty/hodnoty obsahuje oblasť hodnôt.
Na základe sady údajov zobrazenej na začiatku tutoriálu, ak chcete rýchlo vypočítať celkové tržby podľa regiónov v každom mesiaci, môžete získať kontingenčnú tabuľku, ako je uvedené nižšie (uvidíme, ako to vytvoríme neskôr v tutoriále) .
Oblasť zvýraznená oranžovou farbou je oblasť hodnôt.
V tomto prípade má celkové tržby za každý mesiac pre štyri regióny.
Oblasť riadkov
Nadpisy vľavo od oblasti Hodnoty robia oblasť Riadky.
V nižšie uvedenom príklade obsahuje oblasť Riadky oblasti (zvýraznené červenou farbou):
Oblasť stĺpcov
Nadpisy v hornej časti oblasti Hodnoty tvoria oblasť Stĺpce.
V nižšie uvedenom príklade obsahuje oblasť Stĺpce mesiace (zvýraznené červenou farbou):
Oblasť filtrov
Oblasť Filtre je voliteľný filter, ktorý môžete použiť na ďalšie hĺbkové skúmanie množiny údajov.
Ak napríklad chcete vidieť iba predaje pre viacriadkových maloobchodníkov, môžete túto možnosť vybrať z rozbaľovacej ponuky (zvýraznené na obrázku nižšie) a kontingenčná tabuľka sa aktualizuje s údajmi iba pre maloobchodných predajcov s viacerými riadkami.
Analýza údajov pomocou kontingenčnej tabuľky
Teraz sa pokúsime zodpovedať otázky pomocou kontingenčnej tabuľky, ktorú sme vytvorili.
Kliknite tu stiahnite si vzorové údaje a postupujte podľa nich.
Ak chcete analyzovať údaje pomocou kontingenčnej tabuľky, musíte sa rozhodnúť, ako má súhrn údajov vyzerať v konečnom výsledku. Môžete napríklad chcieť všetky regióny vľavo a celkový predaj hneď vedľa neho. Hneď ako si spomeniete na túto jasnosť, môžete príslušné polia v kontingenčnej tabuľke jednoducho pretiahnuť.
V sekcii Polia kontingenčnej tabuľky máte polia a oblasti (ako je zvýraznené nižšie):
Polia sa vytvárajú na základe údajov o koncovom serveri použitých pre kontingenčnú tabuľku. V časti Oblasti umiestnite polia a podľa toho, kam pole ide, sa vaše údaje aktualizujú v kontingenčnej tabuľke.
Je to jednoduchý mechanizmus drag and drop, kde môžete pole jednoducho pretiahnuť a vložiť do jednej zo štyroch oblastí. Hneď ako to urobíte, zobrazí sa v kontingenčnej tabuľke v pracovnom hárku.
Teraz sa pokúsme odpovedať na otázky, ktoré mal váš manažér, pomocou tejto kontingenčnej tabuľky.
Q1: Aké boli celkové tržby v južnom regióne?
Potiahnite pole Región v oblasti Riadky a pole Príjmy v oblasti Hodnoty. Automaticky by sa aktualizovala kontingenčná tabuľka v pracovnom hárku.
Všimnite si toho, že akonáhle zrušíte pole Príjmy v oblasti Hodnoty, stane sa súčtom výnosov. Excel predvolene sčíta všetky hodnoty pre daný región a zobrazí ich súčet. Ak chcete, môžete to zmeniť na metriky Počet, Priemer alebo iné štatistické údaje. V tomto prípade je súčet to, čo sme potrebovali.
Odpoveď na túto otázku by bola 21225800.
Q2 Čo je päť najlepších maloobchodníkov podľa tržieb?
Potiahnite pole Zákazník v oblasti Riadky a pole Výnosy v oblasti hodnôt. V případě, že jsou v sekci oblasti ještě nějaké další pole a chcete ho odstranit, jednoduše ho vyberte a potiahnite z neho.
Kontingenčnú tabuľku získate podľa nižšie uvedeného obrázku:
Upozorňujeme, že položky (v tomto prípade zákazníci) sú predvolene zoradené podľa abecedy.
Ak chcete získať päť najlepších maloobchodníkov, stačí zoradiť tento zoznam a použiť päť najlepších mien zákazníkov. Urobiť toto:
- Kliknite pravým tlačidlom myši na ľubovoľnú bunku v oblasti Hodnoty.
- Prejdite na možnosť Zoradiť -> Zoradiť od najväčšej po najmenšiu.
Získate tak zoradený zoznam na základe celkových tržieb.
Otázka 3: Ako sa porovnala výkonnosť Home Depot s inými maloobchodníkmi na juhu?
Na túto otázku môžete urobiť veľa analýz, ale tu sa pokúsime porovnať tržby.
Potiahnite pole oblasti v oblasti Riadky. Teraz presuňte pole Zákazník v oblasti Riadky pod poľom Región. Keď to urobíte, Excel pochopí, že chcete údaje kategorizovať najskôr podľa regiónov a potom podľa zákazníkov v rámci regiónov. Budete mať niečo, ako je uvedené nižšie:
Teraz presuňte pole Výnosy do oblasti Hodnoty a budete mať tržby pre každého zákazníka (ako aj pre celý región).
Maloobchodníkov môžete zoradiť na základe údajov o predajoch podľa nasledujúcich krokov:
- Kliknite pravým tlačidlom myši na bunku, ktorá má predajnú hodnotu pre akéhokoľvek predajcu.
- Prejdite na možnosť Zoradiť -> Zoradiť od najväčšej po najmenšiu.
To by okamžite zoradilo všetkých maloobchodníkov podľa hodnoty predaja.
Teraz môžete rýchlo prehľadať južný región a zistiť, že predaj Home Depot bol 3004600 a darilo sa mu to lepšie ako u štyroch maloobchodníkov v južnom regióne.
Teraz existuje viac ako jeden spôsob, ako mačku stiahnuť z kože. Môžete tiež vložiť oblasť do oblasti filtra a potom vybrať iba južnú oblasť.
Kliknite tu na stiahnutie vzorových údajov.
Dúfam, že vám tento návod poskytne základný prehľad o kontingenčných tabuľkách programu Excel a pomôže vám s ním začať.
Tu je niekoľko ďalších návodov pre kontingenčné tabuľky, ktoré by sa vám mohli páčiť:
- Príprava zdrojových údajov pre kontingenčnú tabuľku.
- Ako použiť podmienené formátovanie v kontingenčnej tabuľke v programe Excel.
- 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.
- Ako filtrovať údaje v kontingenčnej tabuľke v programe Excel.
- Použitie krájačov v kontingenčnej tabuľke programu Excel.
- Ako nahradiť prázdne bunky nulami v kontingenčných tabuľkách programu Excel.
- Ako pridať a používať polia vypočítané v kontingenčnej tabuľke programu Excel.
- Ako obnoviť kontingenčnú tabuľku v programe Excel.
