Kontingenčná tabuľka Excelu je jedným z najvýkonnejších nástrojov, ktoré vám môžu pomôcť zhrnúť a analyzovať veľké množiny údajov efektívnym spôsobom.
Kontingenčná tabuľka je jedným z najvýkonnejších a najužitočnejších nástrojov na sumarizáciu údajov v Exceli, ktorý vám umožňuje rýchlo sumarizovať, triediť, reorganizovať, analyzovať, zoskupovať a počítať veľké množiny údajov.
Kontingenčná tabuľka vám umožňuje otáčať (alebo otáčať) údaje uložené v tabuľke tak, aby ste ich videli z rôznych uhlov pohľadu a aby ste mali jasnú predstavu o veľkých súboroch údajov.
Tento tutoriál vám poskytne podrobné pokyny na vytváranie a používanie kontingenčných tabuliek v Exceli.
Usporiadajte si údaje
Ak chcete vytvoriť kontingenčnú tabuľku, vaše údaje by mali mať štruktúru tabuľky alebo databázy. Takže musíte usporiadať údaje do riadkov a stĺpcov. Ak chcete previesť rozsah údajov na tabuľku, vyberte všetky údaje, prejdite na kartu „Vložiť“ a kliknite na položku „Tabuľka“. V dialógovom okne Vytvoriť tabuľku kliknite na tlačidlo „OK“, aby ste skonvertovali súbor údajov na tabuľku.
Ak použijete excelovú tabuľku ako zdrojovú množinu údajov na vytvorenie kontingenčnej tabuľky, bude vaša kontingenčná tabuľka dynamická. Keď pridáte alebo odstránite položky v tabuľke Excel, údaje v kontingenčnej tabuľke sa aktualizujú spolu s ňou.
Predpokladajme, že máte veľký súbor údajov, ako je uvedené nižšie, pozostáva z viac ako 500 záznamov a 7 polí. Dátum, Región, Typ maloobchodníka, Spoločnosť, Množstvo, Výnosy a Zisk.
Vložiť kontingenčnú tabuľku
Najprv vyberte všetky bunky, ktoré obsahujú údaje, prejdite na kartu „Vložiť“ a kliknite na položku „Kontingenčný graf“. Potom z rozbaľovacej ponuky vyberte možnosť „Kontingenčný graf a kontingenčná tabuľka“.
Otvorí sa dialógové okno Vytvoriť kontingenčnú tabuľku. Excel automaticky identifikuje a vyplní správny rozsah v poli Tabuľka/Rozsah, inak vyberie správnu tabuľku alebo rozsah buniek. Potom zadajte cieľové umiestnenie vašej kontingenčnej tabuľky Excel, môže to byť „Nový pracovný hárok“ alebo „Existujúci pracovný hárok“ a kliknite na „OK“.
Ak vyberiete možnosť „Nový pracovný hárok“, nový hárok s prázdnou kontingenčnou tabuľkou a kontingenčným grafom sa vytvorí v samostatnom hárku.
Zostavte si svoju kontingenčnú tabuľku
V novom hárku uvidíte prázdnu kontingenčnú tabuľku na ľavej strane okna Excel a tablu „Polia kontingenčnej tabuľky“ na pravom okraji okna Excel, kde nájdete všetky možnosti konfigurácie kontingenčnej tabuľky.
Tabla Polia kontingenčnej tabuľky je rozdelená na dve vodorovné časti: časť Polia (horná časť tably) a časť Rozloženie (spodná časť tably)
- The Sekcia poľa uvádza všetky polia (stĺpce), ktoré ste pridali do tabuľky. Tieto názvy polí sú všetky názvy stĺpcov z vašej zdrojovej tabuľky.
- The Sekcia rozloženia má 4 oblasti, tj Filtre, Stĺpce, Riadky a Hodnoty, pomocou ktorých môžete polia usporiadať a preusporiadať.
Pridať polia do kontingenčnej tabuľky
Ak chcete vytvoriť kontingenčnú tabuľku, presuňte polia zo sekcie Pole do oblastí v sekcii Rozloženie. Polia môžete presúvať aj medzi oblasťami.
Pridať riadky
Začneme pridaním poľa „Spoločnosť“ do sekcie Riadky. Do oblasti Riadok rozloženia sa zvyčajne pridávajú nečíselné polia. Stačí pretiahnuť pole „Spoločnosť“ do oblasti „Riadok“.
Všetky názvy spoločností zo stĺpca „Spoločnosť“ v zdrojovej tabuľke sa pridajú ako riadky do kontingenčnej tabuľky a zoradia sa vzostupne, ale poradie môžete zmeniť kliknutím na rozbaľovacie tlačidlo v bunke Označenia riadkov.
Pridať hodnoty
Pridali ste riadok, teraz do tabuľky pridajte hodnotu, aby ste z nej urobili jednorozmernú tabuľku. Jednorozmernú kontingenčnú tabuľku môžete vytvoriť tak, že do oblastí pridáte iba označenia riadkov alebo stĺpcov a ich príslušné hodnoty. Oblasť hodnôt je miesto, kde sú uložené výpočty/hodnoty.
Vo vyššie uvedenom príklade obrazovky máme rad spoločností, ale chceme zistiť celkový príjem každej spoločnosti. Ak to chcete získať, jednoducho presuňte pole „Výnosy“ do poľa „Hodnota“.
Ak chcete odstrániť niektoré polia zo sekcie Oblasti, jednoducho zrušte začiarknutie políčka vedľa poľa v sekcii Polia.
Teraz máme jednorozmernú tabuľku spoločností (označenie riadkov) spolu so súčtom tržieb.
Pridať stĺpec
Dvojrozmerný stôl
Riadky a stĺpce spolu vytvoria dvojrozmernú tabuľku a vyplnia bunky treťou dimenziou hodnôt. Predpokladajme, že chcete vytvoriť kontingenčnú tabuľku uvedením názvov spoločností ako riadkov a použitím stĺpcov na zobrazenie dátumov a vyplnením buniek celkovým výnosom.
Keď pridáte pole „Dátum“ do oblasti „Stĺpec“, Excel automaticky pridá do polí stĺpca „Štvrťročné“ a „Roky“ na výpočet a lepšie zhrnutie údajov.
Teraz máme dvojrozmernú tabuľku s tromi rozmermi hodnôt.
Pridať filtre
V prípade, že chcete filtrovať údaje podľa „Región“, môžete pretiahnuť pole „Región“ do oblasti Filter.
Tým sa nad kontingenčnú tabuľku pridá rozbaľovacia ponuka s vybratým „Pole filtra“. Vďaka tomu môžete odfiltrovať príjmy spoločností za každý rok podľa regiónu.
V predvolenom nastavení sú vybraté všetky regióny, zrušte ich začiarknutie a vyberte iba región, podľa ktorého chcete filtrovať údaje. Ak chcete tabuľku filtrovať podľa viacerých položiek, začiarknite políčko vedľa položky „Vybrať viacero položiek“ v spodnej časti rozbaľovacej ponuky. A vyberte viacero oblastí.
Výsledok:
Triedenie
Ak chcete zoradiť hodnotu tabuľky vo vzostupnom alebo zostupnom poradí, kliknite pravým tlačidlom myši na ľubovoľnú bunku v stĺpci Súčet výnosov, potom rozbaľte položku „Zoradiť“ a vyberte poradie.
Výsledok:
Zoskupovanie
Povedzme, že máte v kontingenčnej tabuľke údaje uvedené podľa mesiacov, ale nechcete ich vidieť mesačne, namiesto toho chcete údaje usporiadať podľa finančných štvrťrokov. Môžete to urobiť vo svojej kontingenčnej tabuľke.
Najprv vyberte stĺpce a kliknite na ne pravým tlačidlom myši. Potom z rozbaľovacej ponuky vyberte možnosť „Skupina“.
V okne Zoskupovanie vyberte „Štvrťroky“ a „Roky“, pretože ich chceme usporiadať do finančných štvrťrokov každého roka. Potom kliknite na tlačidlo „OK“.
Teraz sú vaše údaje usporiadané do finančných štvrťrokov každého roka.
Nastavenia poľa hodnôt
Kontingenčná tabuľka štandardne sumarizuje číselné hodnoty pomocou funkcie Súčet. V oblasti Hodnoty však môžete zmeniť typ výpočtu, ktorý sa používa.
Ak chcete zmeniť funkciu súhrnu, kliknite pravým tlačidlom myši na ľubovoľné údaje v tabuľke, kliknite na položku „Summarize Values By“ a vyberte svoju možnosť.
Prípadne môžete kliknúť na šípku nadol vedľa položky „Súčet ..“ v oblasti hodnôt v časti poľa a vybrať položku „Nastavenia poľa hodnoty“.
V časti „Nastavenia poľa hodnôt“ vyberte svoju funkciu na zhrnutie údajov. Potom kliknite na tlačidlo „OK“. Pre náš príklad volíme „Počet“ na počítanie počtu ziskov.
Výsledok:
Kontingenčné tabuľky Excelu vám tiež umožňujú zobrazovať hodnoty rôznymi spôsobmi, napríklad zobraziť celkové súčty ako percentá alebo súčet stĺpcov ako percentá alebo súčet riadkov ako percentá alebo hodnoty zoradiť od najmenšej po najväčšiu a naopak, mnoho ďalších.
Ak chcete zobraziť hodnoty v percentách, kliknite pravým tlačidlom myši kdekoľvek v tabuľke, potom kliknite na „Zobraziť hodnoty ako“ a vyberte svoju možnosť.
Keď zvolíme „% z celkového počtu stĺpcov“, výsledok bude takýto,
Obnovte kontingenčnú tabuľku
Hoci je zostava kontingenčnej tabuľky dynamická, pri vykonávaní zmien v zdrojovej tabuľke Excel automaticky neobnovuje údaje v kontingenčnej tabuľke. Na aktualizáciu údajov je potrebné ho manuálne „obnoviť“.
Kliknite kdekoľvek v kontingenčnej tabuľke a prejdite na kartu „Analýza“, kliknite na tlačidlo „Obnoviť“ v skupine Údaje. Ak chcete obnoviť aktuálnu kontingenčnú tabuľku v pracovnom hárku, kliknite na možnosť „Obnoviť“. Ak chcete obnoviť všetky kontingenčné tabuľky v zošite, kliknite na „Obnoviť všetko“.
Prípadne môžete kliknúť pravým tlačidlom myši na tabuľku a vybrať možnosť „Obnoviť“.
to je všetko. Dúfame, že tento článok vám poskytne podrobný prehľad kontingenčných tabuliek programu Excel a pomôže vám ich vytvoriť.