Kontingenčná tabuľka v Exceli: návod krok za krokom
Kontingenčná tabuľka zhrnie dlhú tabuľku na pár kliknutí: súčty podľa miest, produktov alebo mesiacov bez jediného vzorca. Návod ukazuje, ako ju vytvoriť a ako fungujú jej štyri oblasti. Na konci si vedomosti overíš v krátkom kvíze.
Čo je kontingenčná tabuľka
Kontingenčná tabuľka (po anglicky PivotTable, hovorovo aj pivot tabuľka) je súhrn údajov, ktorý Excel vytvorí z tabuľky s riadkami záznamov. Ty vyberieš, podľa čoho sa majú údaje zoskupiť a čo sa má spočítať. Excel nájde všetky rôzne hodnoty, napríklad mestá, a pre každú vypočíta súčet, počet alebo priemer.
Príklad: kaviareň s pobočkami v troch mestách zapisuje každý predaj do riadka. Takto vyzerajú zdrojové údaje:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Mesto | Produkt | Predaj | Tržba |
| 2 | Bratislava | Káva | na mieste | 420 |
| 3 | Košice | Čaj | rozvoz | 180 |
| 4 | Žilina | Káva | na mieste | 310 |
| 5 | Bratislava | Koláč | rozvoz | 260 |
| 6 | Košice | Káva | na mieste | 390 |
| 7 | Bratislava | Čaj | na mieste | 150 |
| 8 | Žilina | Koláč | na mieste | 220 |
| 9 | Košice | Koláč | rozvoz | 240 |
| 10 | Bratislava | Káva | rozvoz | 380 |
| 11 | Žilina | Čaj | rozvoz | 140 |
| 12 | Košice | Káva | rozvoz | 270 |
| 13 | Žilina | Káva | rozvoz | 190 |
Kontingenčná tabuľka z nich za pár sekúnd urobí súčet tržieb za každé mesto:
| Označenia riadkov | Tržba (Súčet) |
|---|---|
| Bratislava | 1210 |
| Košice | 1080 |
| Žilina | 860 |
| Celkový súčet | 3150 |
Bratislava má spolu 1 210 €, všetky pobočky 3 150 €. Rovnaké číslo pre Bratislavu by dal
vzorec =SUMIF(A2:A13;"Bratislava";D2:D13) z lekcie o funkcii SUMIF. Rozdiel je
v tom, že kontingenčná tabuľka spočíta všetky mestá naraz a pri inom pohľade na údaje ju len
preusporiadaš myšou.
Ako pripraviť údaje
Kontingenčná tabuľka je len taká dobrá ako údaje pod ňou. Pred vložením skontroluj:
- Jeden riadok hlavičky. Každý stĺpec má v prvom riadku názov, z neho bude názov poľa.
- Žiadne zlúčené bunky a žiadne dvojriadkové hlavičky.
- Bez prázdnych riadkov a stĺpcov uprostred údajov.
- V stĺpci jeden druh údajov. V stĺpci s tržbou sú len čísla, nie text ako „nezaplatené“, v stĺpci s dátumom len dátumy.
- Bez medzisúčtov. Súčty vypočíta až kontingenčná tabuľka.
Najlepšie je údaje naformátovať ako tabuľku (klávesová skratka Ctrl + T). Nové riadky, ktoré do nej pridáš, potom kontingenčná tabuľka zahrnie pri obnovení sama.
Ako urobiť kontingenčnú tabuľku v Exceli
- Klikni do ľubovoľnej bunky v údajoch (alebo údaje označ).
- Na karte Vložiť vyber Kontingenčná tabuľka.
- V okne skontroluj rozsah údajov a vyber, kam kontingenčnú tabuľku umiestniť: Nový hárok alebo Existujúci hárok s bunkou, kde má začínať. Potvrď tlačidlom OK.
- Excel vloží prázdnu kontingenčnú tabuľku a vpravo otvorí tablu Polia kontingenčnej tabuľky so zoznamom stĺpcov tvojej tabuľky.
- Začiarkni polia, ktoré chceš vidieť, alebo ich myšou potiahni do oblastí v dolnej časti tably. Tabuľka sa pri každej zmene hneď prekreslí.
Pri začiarknutí poľa Excel sám odhadne oblasť: textové polia dá do Riadkov, číselné do Hodnôt a dátumové a časové hierarchie do Stĺpcov. Ak máš Excel pre Microsoft 365, môžeš skúsiť aj odporúčané kontingenčné tabuľky na karte Vložiť. Excel ponúkne hotové návrhy a vyberieš ten, ktorý sa hodí.
Keď klikneš mimo kontingenčnej tabuľky, tabla s poliami zmizne. Ukáže sa znova po kliknutí do kontingenčnej tabuľky.
Štyri oblasti: Filtre, Stĺpce, Riadky, Hodnoty
To, kam pole potiahneš, určuje tvar celej tabuľky:
- Riadky: každá rôzna hodnota poľa dostane vlastný riadok (napríklad každé mesto).
- Stĺpce: každá rôzna hodnota dostane vlastný stĺpec (napríklad každý produkt).
- Hodnoty: čísla, ktoré sa zhrnú. Číselné pole Excel predvolene sčíta, textové spočíta.
- Filtre: pole sa ukáže nad tabuľkou a vyberieš v ňom, s ktorými riadkami sa má počítať.
Schéma tably pre tabuľku vyššie: Mesto je v Riadkoch, Tržba v Hodnotách.
- Mesto (začiarknuté)
- Produkt (nezačiarknuté)
- Predaj (nezačiarknuté)
- Tržba (začiarknuté)
Schémy na tejto stránke sú zjednodušené a nevyzerajú ako Excel. Hlavičku stĺpca s hodnotami Excel pomenuje sám podľa súhrnnej funkcie a poľa, zmeniť ju môžeš v Nastaveniach poľa hodnôt (políčko Vlastný názov).
Kontingenčná tabuľka: príklady
Mestá v riadkoch, produkty v stĺpcoch
Keď pole Produkt potiahneš do oblasti Stĺpce, z jednoduchého zoznamu vznikne krížová tabuľka. Na prvý pohľad vidíš, ktorý produkt sa kde predáva najlepšie. Keby niektorá kombinácia mesta a produktu v údajoch chýbala, bunka by ostala prázdna.
- Mesto (začiarknuté)
- Produkt (začiarknuté)
- Predaj (nezačiarknuté)
- Tržba (začiarknuté)
| Tržba (Súčet) | Označenia stĺpcov | |||
|---|---|---|---|---|
| Označenia riadkov | Čaj | Káva | Koláč | Celkový súčet |
| Bratislava | 150 | 800 | 260 | 1210 |
| Košice | 180 | 660 | 240 | 1080 |
| Žilina | 140 | 500 | 220 | 860 |
| Celkový súčet | 470 | 1960 | 720 | 3150 |
Len rozvoz: pole v oblasti Filtre
Pole Predaj v oblasti Filtre sa ukáže nad tabuľkou. Keď v ňom vyberieš „rozvoz“, tabuľka počíta len s predajmi rozvozom. Mestá v riadkoch ostanú.
- Mesto (začiarknuté)
- Produkt (nezačiarknuté)
- Predaj (začiarknuté)
- Tržba (začiarknuté)
| Predaj | rozvoz |
| Označenia riadkov | Tržba (Súčet) |
|---|---|
| Bratislava | 640 |
| Košice | 690 |
| Žilina | 330 |
| Celkový súčet | 1660 |
Počet predajov namiesto súčtu
Keď do oblasti Hodnoty dáš textové pole, napríklad Produkt, Excel nesčítava, ale počíta riadky. Tak zistíš, koľkokrát sa ktorý produkt predal.
| Označenia riadkov | Produkt (Počet) |
|---|---|
| Čaj | 3 |
| Káva | 6 |
| Koláč | 3 |
| Celkový súčet | 12 |
Ako zmeniť súčet na priemer alebo počet
- V oblasti Hodnoty klikni na šípku vedľa poľa a vyber Nastavenia poľa hodnôt.
- Na karte Zhrnúť hodnoty podľa vyber funkciu: Súčet, Počet, Priemer, Maximum, Minimum a ďalšie.
- Ak chceš, prepíš Vlastný názov a tlačidlom formátu čísla nastav napríklad eurá.
Rýchlejšie to ide pravým tlačidlom myši na číslo v kontingenčnej tabuľke a príkazom Zhrnúť hodnoty podľa. Na karte Zobraziť hodnoty ako v tom istom okne zmeníš súčty napríklad na % z celkového súčtu: uvidíš podiel každého mesta na celkovej tržbe.
To isté pole môžeš do Hodnôt potiahnuť aj dvakrát. Raz ho necháš ako súčet a pri druhom nastavíš percentá, takže v tabuľke budú vedľa seba eurá aj podiel.
Obnovenie po zmene údajov
Kontingenčná tabuľka sa po zmene zdrojových údajov sama neprepočíta. Klikni do nej pravým tlačidlom myši a vyber Obnoviť. Ak máš v zošite viac kontingenčných tabuliek, na karte Analýza kontingenčnej tabuľky klikni na šípku pod tlačidlom Obnoviť a vyber Obnoviť všetko.
Ak pribudli riadky pod pôvodný rozsah a údaje nie sú naformátované ako tabuľka, obnovenie ich nezahrnie. Vtedy treba zmeniť zdrojové údaje kontingenčnej tabuľky, alebo údaje najprv premeniť na tabuľku cez Ctrl + T.
Zoskupenie dátumov a čísel
Keď máš v riadkoch dátumy, každý deň by mal vlastný riadok. Zoskup ich: klikni pravým tlačidlom na dátum v kontingenčnej tabuľke a vyber Zoskupiť. V okne Zoskupiť skontroluj Začiatok a Koniec a v zozname Podľa vyber napríklad mesiace a roky.
Čísla sa zoskupujú rovnako, len namiesto obdobia zadáš veľkosť intervalu. Napríklad vek zákazníkov po 10 rokoch. Zoskupenie zrušíš príkazom Oddeliť v tej istej ponuke.
Kontingenčný graf
Z kontingenčnej tabuľky sa dá urobiť kontingenčný graf. Ukazuje tie isté súhrny a keď v kontingenčnej tabuľke zmeníš polia alebo filter, zmení sa aj graf. Dá sa z neho urobiť skoro každý typ grafu, okrem bodového XY, burzového a bublinového.
Vzorce v kontingenčnej tabuľke: vypočítavané pole
Do kontingenčnej tabuľky sa nepíšu bežné vzorce, ale dá sa pridať vypočítavané pole.
Klikni do kontingenčnej tabuľky, na karte Analýza kontingenčnej tabuľky (v starších verziách
Analyzovať) v skupine Výpočty vyber Polia, položky a množiny a potom
Vypočítavané pole. Zadaj názov a vzorec, napríklad províziu 10 % z tržby
=Tržba*10%.
Vypočítavané pole počíta so súčtami, nie s jednotlivými riadkami: najprv sa sčítajú tržby a až súčet sa vynásobí. Na zložitejšie výpočty po riadkoch je lepšie pridať stĺpec so vzorcom priamo do zdrojových údajov.
Kontingenčná tabuľka z viacerých hárkov
Ak máš rovnaké údaje rozdelené na viac hárkov, napríklad každý mesiac zvlášť, najjednoduchšie je skopírovať ich pod seba do jednej tabuľky a pridať stĺpec s mesiacom. Kontingenčná tabuľka ich potom zhrnie spolu a mesiac môžeš dať do Stĺpcov alebo Filtrov.
Ak ide o rôzne tabuľky, ktoré spája spoločný stĺpec (napríklad kód produktu v predajoch aj v cenníku), pri vkladaní kontingenčnej tabuľky začiarkni Pridať tieto údaje do dátového modelu a tabuľky v Exceli prepoj vzťahom. To je už pokročilejšia práca s dátovým modelom.
Časté problémy
Hodnoty sa počítajú namiesto sčítania
V oblasti Hodnoty je Počet namiesto Súčtu, keď sú v stĺpci prázdne bunky alebo text, napríklad čísla uložené ako text. Oprav zdrojové údaje a kontingenčnú tabuľku obnov, prípadne prepni funkciu v Nastaveniach poľa hodnôt.
Kontingenčná tabuľka ukazuje staré čísla
Nebola obnovená. Pravé tlačidlo myši v kontingenčnej tabuľke a Obnoviť.
Excel kontingenčnú tabuľku nechce vytvoriť
Najčastejšie niektorý stĺpec v označenom rozsahu nemá v prvom riadku názov. Doplň hlavičku a skús to znova.
Ako kontingenčnú tabuľku odstrániť
Označ celú kontingenčnú tabuľku a stlač Delete. Ostatné údaje v hárku to neovplyvní. Ak je na samostatnom hárku, stačí odstrániť hárok.
Kvíz: kontingenčná tabuľka
Päť otázok k návodu. Po každej odpovedi uvidíš vysvetlenie.
-
Otázka 1
Aké údaje sa hodia ako zdroj kontingenčnej tabuľky?
Každý stĺpec sa stane poľom a jeho názov berie Excel z hlavičky. Zlúčené bunky, prázdne riadky a medzisúčty vo zdroji robia problémy, súčty vypočíta až kontingenčná tabuľka.
-
Otázka 2
Chceš súčet tržieb za každé mesto. Do ktorej oblasti dáš pole Mesto?
V oblasti Riadky dostane každé mesto vlastný riadok. Pole Tržba patrí do oblasti Hodnoty, kde sa sčíta.
-
Otázka 3
Pole Tržba sa v oblasti Hodnoty zobrazuje ako Počet, nie ako Súčet. Prečo?
Číselné pole Excel predvolene sčíta. Keď sú v stĺpci aj iné hodnoty, napríklad čísla uložené ako text, použije Počet. Súčet nastavíš v Nastaveniach poľa hodnôt, ale lepšie je opraviť zdrojové údaje.
-
Otázka 4
V zdrojovej tabuľke sa zmenila suma, ale kontingenčná tabuľka ukazuje staré číslo. Čo urobíš?
Kontingenčná tabuľka sa po zmene zdroja neprepočíta sama, treba ju obnoviť. Viac kontingenčných tabuliek naraz obnoví príkaz Obnoviť všetko.
-
Otázka 5
Tabuľka ukazuje tržby podľa miest. Chceš ich vidieť len za rozvoz, nie za predaj na mieste. Kam dáš pole Predaj?
Pole v oblasti Filtre sa ukáže nad tabuľkou a vyberieš v ňom položku, napríklad rozvoz. Tabuľka potom počíta len s riadkami, ktoré filtru zodpovedajú.
Výsledok
0 z 5
Otázky a odpovede
Čo je kontingenčná tabuľka v Exceli?
Nástroj, ktorý z dlhej tabuľky záznamov urobí súhrn: súčty, počty alebo priemery podľa zvolených stĺpcov. Nemení pôvodné údaje a jej tvar zmeníš presunutím polí medzi oblasťami.
Ako sa povie kontingenčná tabuľka po anglicky?
PivotTable. Preto sa jej hovorovo hovorí aj pivot tabuľka. Kontingenčný graf je po anglicky PivotChart.
Kedy použiť kontingenčnú tabuľku a kedy SUMIF?
Kontingenčná tabuľka je rýchlejšia, keď chceš prehľad podľa všetkých hodnôt naraz a pozerať sa na údaje z rôznych strán. Vzorce SUMIF a SUMIFS či COUNTIF sa hodia, keď potrebuješ jedno číslo na pevnom mieste v tabuľke, ktoré sa prepočíta samo bez obnovenia.
Kde nájdem kontingenčnú tabuľku v Exceli?
Na karte Vložiť, tlačidlo Kontingenčná tabuľka. Klávesmi sa na kartu Vložiť dostaneš cez Alt a N.