excelkurz.sk

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:

ABCD
1 MestoProduktPredajTržba
2 BratislavaKávana mieste420
3 KošiceČajrozvoz180
4 ŽilinaKávana mieste310
5 BratislavaKoláčrozvoz260
6 KošiceKávana mieste390
7 BratislavaČajna mieste150
8 ŽilinaKoláčna mieste220
9 KošiceKoláčrozvoz240
10 BratislavaKávarozvoz380
11 ŽilinaČajrozvoz140
12 KošiceKávarozvoz270
13 ŽilinaKávarozvoz190
Zdrojové údaje: 12 predajov, každý v jednom riadku.

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
Kontingenčná tabuľka: mesto v oblasti Riadky, tržba v oblasti Hodnoty.

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:

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

  1. Klikni do ľubovoľnej bunky v údajoch (alebo údaje označ).
  2. Na karte Vložiť vyber Kontingenčná tabuľka.
  3. 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.
  4. Excel vloží prázdnu kontingenčnú tabuľku a vpravo otvorí tablu Polia kontingenčnej tabuľky so zoznamom stĺpcov tvojej tabuľky.
  5. 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:

Schéma tably pre tabuľku vyššie: Mesto je v Riadkoch, Tržba v Hodnotách.

Polia kontingenčnej tabuľky
  • Mesto (začiarknuté)
  • Produkt (nezačiarknuté)
  • Predaj (nezačiarknuté)
  • Tržba (začiarknuté)
Filtre
prázdne
Stĺpce
prázdne
Riadky
Mesto
Hodnoty
Tržba (Súčet)

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.

Polia kontingenčnej tabuľky
  • Mesto (začiarknuté)
  • Produkt (začiarknuté)
  • Predaj (nezačiarknuté)
  • Tržba (začiarknuté)
Filtre
prázdne
Stĺpce
Produkt
Riadky
Mesto
Hodnoty
Tržba (Súčet)
Tržba (Súčet) Označenia stĺpcov
Označenia riadkov ČajKávaKoláč Celkový súčet
Bratislava 150800260 1210
Košice 180660240 1080
Žilina 140500220 860
Celkový súčet 4701960720 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ú.

Polia kontingenčnej tabuľky
  • Mesto (začiarknuté)
  • Produkt (nezačiarknuté)
  • Predaj (začiarknuté)
  • Tržba (začiarknuté)
Filtre
Predaj
Stĺpce
prázdne
Riadky
Mesto
Hodnoty
Tržba (Súčet)
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
Produkt v Riadkoch aj v Hodnotách, súhrnná funkcia Počet.

Ako zmeniť súčet na priemer alebo počet

  1. V oblasti Hodnoty klikni na šípku vedľa poľa a vyber Nastavenia poľa hodnôt.
  2. Na karte Zhrnúť hodnoty podľa vyber funkciu: Súčet, Počet, Priemer, Maximum, Minimum a ďalšie.
  3. 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.

  1. Otázka 1

    Aké údaje sa hodia ako zdroj kontingenčnej tabuľky?

  2. Otázka 2

    Chceš súčet tržieb za každé mesto. Do ktorej oblasti dáš pole Mesto?

  3. Otázka 3

    Pole Tržba sa v oblasti Hodnoty zobrazuje ako Počet, nie ako Súčet. Prečo?

  4. Otázka 4

    V zdrojovej tabuľke sa zmenila suma, ale kontingenčná tabuľka ukazuje staré číslo. Čo urobíš?

  5. 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?

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.

Súvisiace