Image
15.1.2020 2 Comments

Business Intelligence v Exceli – kontingenčná tabuľka a graf

Veľa manažérov, ekonómov a marketingových špecialistov v podnikovej sfére volá po kvalitných a dostupných nástrojoch pre BI analýzy, ktoré by im umožnili analyzovať údaje s ktorými pracujú. Potrebujú vytvárať reporty bez toho, aby potrebovali asistenciu analytikov a špecialistov z IT oddelení. Často  však majú požadované nástroje na dosah ruky, presnejšie vo svojom počítači v podobe kancelárskeho balíka Office.

Kontingenčná tabuľka (pivot table) má na rozdiel od klasickej tabuľky niekoľko špeciálnych vlastností. Umožňuje výmenu riadkov a stĺpcov, ale hlavne riadky a stĺpce môžu mať hierarchickú štruktúru. To poskytuje široké možnosti pri zoskupovaní údajov. Funkcia kontingenčnej tabuľky spočíva v tom, že zoberie údaje a vytvorí z nich súhrn tak, aby dávali zmysel. Netreba pritom zadávať žiadne vzorce.

Video názorne ukazuje možnosti práce s kontingenčnou tabuľkou a grafom v Exceli



Rozdiel medzi klasickou a kontingenčnou tabuľkou najlepšie pochopíte, ak ich porovnáme:

Klasická tabuľka

rok
kvartal 
zisk                  
2018
1      
6300
2018
2      
6900
2018
3      
7100
2019
4      
7150
2019
1      
7300
2019
2      
7700
2019
3      
8000
2019
4      
8100

Kontingenčná tabuľka

rok  
Q1       
Q2       
Q3       
Q4        
Spolu za rok          
2018 
6300.0000 
6900.0000 
7100.0000 
7150.0000 
27450.0000
2019 
7300.0000 
7700.0000 
8000.0000 
8100.0000 
31100.0000

V ďalšom príklade máme tabuľku, ktorá pre jednotlivé krajiny udáva jednak celkový počet obyvateľov a taktiež počet obyvateľov žijúcich v mestách. Príklad údajov

Země
Kontinent
Populace (v tisících)
Lidé  ve městech (v tisících)
Zimbabwe
Africa
16529,9043
5328,745167
Zambia
Africa
17094,13086
7346,373975
Uganda
Africa
42862,95703
9942,491147
Tunisia
Africa
11532,12695
7915,882386
Togo
Africa
7797,693848
3209,686644
Sudan
Africa
40533,32813
13931,30444
South Sudan
Africa
12575,71387
2432,897689
South Africa
Africa
56717,15625
37348,24653

Ak kontingenčné tabuľky dobre nepoznáte alebo neviete, ako začať, skvelou voľbou je odporúčaná kontingenčná tabuľka. Pri použití tejto funkcie Excel priradí údaje k najvhodnejším oblastiam v kontingenčnej tabuľke, a určí tak zmysluplné rozloženie. Získate tak počiatočný bod na ďalšie experimentovanie. Po vytvorení odporúčanej kontingenčnej tabuľky môžete preskúmať rôzne orientácie a zmeniť usporiadanie polí, aby ste dosiahli konkrétne výsledky.

Návrhový formulár kontingenčnej tabuľky je umiestnený priamo na ploche hárku programu Excel v pravej časti. Vaša činnosť pri návrhu tabuľky je zredukovaná na presúvanie ikoniek s názvami atribútov z hornej časti panelu do jednotlivých polí návrhového formulára. Tieto polia sú označené ako Hodnoty, Riadky, Stĺpce a Filtre. Našou úlohou je povedzme zistiť pre jednotlivé kontinenty celkový počet obyvateľov a taktiež, koľko obyvateľov z toho žije v mestách. Takže do poľa hodnoty presunieme atribúty Populácia a Ludia-v-mestách a do stĺpca Riadky atribút Kontinent.

Získate prehľadnú tabuľku s agregovanými údajmi pre jednotlivé kontinenty. Polia kontingenčnej tabuľky umiestnené v oblasti Hodnoty sa predvolene zobrazia ako SÚČET. Ak Excel údaje interpretuje ako text, zobrazia sa ako POČET. Keď presuniete pole do oblasti Hodnoty, v dolnej časti zoznamu polí kontingenčnej tabuľky sa zobrazí položka „Súčet z ...“. (Ak Excel nemá dostatok miesta, názov položky sa niekedy nemusí zobraziť celý.)

Označenia riadkov
Súčet z Populace (v tisících)
Súčet z Lidé žijící ve městech (v tisících)
Africa
1061991,023
446744,0028
Asia
4522757,774
2250806,231
Europe
597596,2602
443724,0873
North America
565082,0344
445083,3802
Oceania
39718,89455
27070,097
South America
381079,3098
320150,357
Celkový súčet
7168225,297
3933578,155

Kontingenčné tabuľky začnú byť zaujímavé, keď pridáte podmienky, pomocou ktorých rozdelíte pole hodnoty. Ak atribút Kontinent presuniete do poľa Filtre, môžete údaje filtrovať pre jednotlivé kontinenty

Kontinent
Europe
 
 
Súčet z Populace (v tisících)
Súčet z Lidé žijící ve městech (v tisících)
597596,2602
443724,0873

Ak do poľa Riadky presuniete viac atribútov, ktoré tvoria prirodzenú hierarchiu, v našom prípade je to Kontinet a Krajina, môžete potom túto hierarchickú štruktúru rozbaľovať. Môžete si napríklad nechať zobraziť údaje pre jednotlivé kontinenty, avšak Európu môžete rozbaliť tak, že sa zobrazia údaje pre jednotlivé krajiny.

Jednotlivé hierarchie rozbaľujete pomocou ikonky „+“ vľavo od názvu atribútu

Označenia riadkov
Súčet z Populace (v tisících)
Súčet z Lidé žijící ve městech (v tisících)
Africa
1061991,023
446744,0028
Asia
4522757,774
2250806,231
Europe
597596,2602
443724,0873
Albania
2930,187012
1740,032936
Austria
8735,453125
5074,774293
Belarus
9468,337891
7397,991382
Belgium
11429,33594
11196,29161
Bosnia and Herzegovina
3507,01709
1679,019483
Bulgaria
7084,570801
5289,97808
Croatia
4189,353027
2373,980672
Czech Republic
10618,30273
7823,034864
Denmark
5733,550781
5031,592376
Estonia
1309,631958
899,9398302

Z tejto množiny údajov by bolo zaujímavé vedieť percento ľudí, ktorí na jednotlivých kontinentoch a v jednotlivých krajinách žijú v mestách.  Do kontingenčnej tabuľky preto vložíme Vypočítavané pole. 

V ňom (klikaním na názvy atribútov) vytvoríme jednoduchý vzorec na výpočet percent v desatinnom vyjadrení, v našom prípade vzorec bude:

= 'Lidé žijící ve městech (v tisících)'/ 'Populace (v tisících)'

Zostáva len naformátovať pole, aby sa hodnoty zobrazovali v percentách a skryť atribúty celkový počet populácie a počet ľudí žijúcich v mestách. Aj v tejto tabuľke môžete rozbaľovať jednotlivé úrovne hierrchie

Označenia riadkov
Súčet z Percento ve měste
Africa
42,07%
Asia
49,77%
Europe
74,25%
North America
78,76%
Oceania
68,15%
Australia
85,90%
Fiji
55,74%
Kiribati
53,26%
Marshall Islands
76,63%
Nauru
100,00%
New Zealand
86,47%
Palau
79,36%
Papua New Guinea
13,10%
Samoa
18,45%
Solomon Islands
23,29%
Tonga
23,17%
Tuvalu
61,53%
Vanuatu
25,16%
South America
84,01%
Celkový súčet
54,88%

Ak chcete vytvoriť dobrú kontingenčnú tabuľku, treba najprv údaje správne naformátovať. Predtým, ako začnete vytvárať kontingenčnú tabuľku, vykonajte najprv tieto kroky:

Zadajte výstižné názvy stĺpcov

Skontrolujte, či tabuľka neobsahuje duplicitné riadky ani prázdne bunky

Ak k zdroju údajov kontingenčnej tabuľky pridáte nové údaje, všetky kontingenčné tabuľky vytvorené z tohto zdroja údajov bude potrebné obnoviť. Ak chcete obnoviť iba jednu kontingenčnú tabuľku, kliknite pravým tlačidlom myši na ľubovoľné miesto v rozsahu kontingenčnej tabuľky a potom vyberte položku Obnoviť. Ak máte viacero kontingenčných tabuliek, najprv vyberte ľubovoľnú bunku v kontingenčnej tabuľke, potom na páse s nástrojmi prejdite na položku Analýza kontingenčnej tabuľky, kliknite na šípku pod tlačidlom Obnoviť a vyberte možnosť Obnoviť všetko.

Zobrazit Galériu

Nechajte si posielať prehľad najdôležitejších správ emailom

Mohlo by Vás zaujímať

Ako na to

Business intelligence v Exceli – analýza hypotéz (What-if analýza)

12.01.2020 00:10

Analýza hypotéz je what-if proces zmeny hodnôt v bunkách tabuľky, vďaka ktorému môžete vidieť, ako tieto zmeny ovplyvnia výsledky vzorcov v hárku. Pomocou nástrojov na analýzu hypotéz v Exceli môžete ...

Ako na to

Tipy, triky a návody Ako resetovať a preinštalovať natívne aplikácie Windows 10

30.12.2019 14:34

V rámci Windows 10 možno naraziť na tzv. univerzálne aplikácie (označovaných aj ako moderné alebo aplikácie Metro), ktoré sú z hľadiska systémovej architektúry odlišné od tých klasických, reprezentova ...

Ako na to

Tipy, triky a návody: Ako rýchlo zablokovať aplikácii prístup do internetu

30.12.2019 14:32

Ako rýchlo zablokovať aplikácii prístup do internetu Ľubovoľný softvér inštalovaný na naše počítače dnes považuje internetový prístup za úplnú samozrejmosť, a nech už aplikácia slúži na akýkoľvek úče ...

2 Comments

  1. Lol reakcia na: Business Intelligence v Exceli – kontingenčná tabuľka a graf
    15.1.2020 20:01
    Kontingecne tabulky? Teraz? Ved to je stareeee...su novsie lahsie a lepsie nastroje zadarmo
    Reagovať
    • RE: Lol reakcia na: Lol
      Autor: Lubo
      16.1.2020 07:01
      Nezabudli sme ani na modernejšie nástroje, ešte tento týždeň predstavíme aj Power BI. BTW kontingenčná tabuľka ako veľmi názorný a interaktívny spôsob prezentácie agregovaných údajov sa používa aj v najmodernejších analytických nástrojoch
      Reagovať

Vyhľadávanie

Najnovšie videá