Nikdy som nevedel pochopiť kontingenčné tabuľky (pivot tables) v Exceli. Všetky príklady boli enormne komplikované alebo manipulovali v troch tisíckach dimenzií naraz. Vyskúšajme si to na jednoduchom príklade dát s dvoma stĺpcami a ukáže sa, že to vôbec nie je také hrozné, ako to vyzerá na prvý pohľad.

V ukážte zoberiem jednoduché dáta z článku o agregačných funkciách v SQL: zopár zvierat nájdených v uliciach a umiestnených do útulku, ktorého “informačný systém” budeme obsluhovať.

Dáta sú uložené krížom-krážom tak, ako prichádzali do databázy. Nadmanažér od nás bude chcieť zopár reportov: na úvod tieto dva. * Koľko zvierat sa našlo spolu? * V ktorých okrskoch sa našlo koľko zvierat?

Počet zvierat vieme zistiť aj bez kontigenčnej tabuľky: vložíme napr. do bunky C1 formulu

=COUNTA(A2:A1000)

A tá nám spočíta deväť dosiaľ vložených zvierat. Pozor, ide o funkciu COUNTA, ktorá počíta výskyt neprázdnych buniek. SQListi, ktorí by chceli použiť COUNT (bez A), zlyhajú — klasická verzia totiž započítava len numerické hodnoty. Divý rozsah A2:A1000 bude rátať prvých tisíc zvierat: to aby sme ho nemuseli s každým novým zvieraťom zväčšovať.

Pri štatistike o zvieratách v okrskoch by sme už museli vyrábať šialené vzorce, ktoré si ani nechcem predstaviť. To je však zbytočné.

Kontingenčná tabuľka je v tomto prípade omnoho elegantnejšia: predovšetkým umožňuje operatívne naklikávať reporty od výmyslu sveta bez zmeny vzorcov či divokého rozbombardovávania celého zošita.

Vytváranie kontingenčných tabuliek

• Začnime výberom oblasti s dátami.
• Následne na karte Vložiť kliknimež na Kontingenčná tabuľka.

• Teraz načim vyplniť prinajmenšom dve veci:
  1. Rozsah dátovej oblasti: ten sa vyplní automaticky podľa výberu Sheet1!$A$1:$B$9
  2. Miesto, kam sa má vložiť tabuľka: zvolil som Existujúci hárok a povedal som, že tabuľka bude začínať v bunke D1. Dôvodom bol hlavne tento tutoriál: aby bolo vidieť dáta vedľa tabuľky. V reálnych prípadoch sa možno bude hodiť tabuľka v samostatnom hárku.

• Potvrdíme OKejom a na vhodnom mieste sa zjaví kontingenčná tabuľka, i keď beztvará a pustá.

Ako naplniť KT?

Rada starých veteránok KTčiek (nechce sa mi písať dokola „kontingenčná tabuľka”) hovorí, že najdôležitejšie je zhlobiť otázku, resp. vec, ktorú sa KTčky opýtame:

• chcem vedieť celkové počty zvierat za jednotlivé okrsky.
• chcem vedieť počty zvierat v jednotlivých druhoch za jednotlivé okrsky.
• atď.

Keď sa viem dobre pýtať (a časom sa budem vedieť pýtať čoraz lepšie), dostanem od KT lepšie odpovede.

Poďme zaradom a ukážme si napĺňanie na prvom príklade.

Chcem vedieť celkové počty nájdených zvierat za jednotlivé okrsky

Inými slovami, chcem vedieť, že v Dúbravke sa našlo toľko a toľko zvierat, v DNV zase iné číslo a v Ružinove možno nič. Ešte je dobré si upresniť predstavu: okrsky chcem vidieť po riadkoch.

Jadrom je panel Polia kontingenčnej tabuľky napravo. Práca s ním je základom budovanie KT — prakticky pôjde o zbesilé presúvanie jednotlivých položiek myšou medzi viacerými sekciami, čím budeme generovať konkrétnu KT. Zatiaľ máme prázdny panel, čomu zodpovedá prázdna KT:

V hornom paneli (v štandarnom zobrazení) vidíme zoznam polí, ktoré chceme pridať do zostavy (= KT). Letmým pohľadom zistíme, že zodpovedajú názvom stĺpcov zo sady dát.

Pod ňou vidíme štyri sekcie: Filter zostavy, Menovky stĺpcov a Menovky riadkov a nakoniec Hodnoty.

Prvú úlohu vyriešime jednoducho: keďže chceme vidieť po riadkoch okrsky (= miesta nálezu), zdrapneme myšou pole Miesto nálezu z horného panela a zatiahneme ho (drag-n-dropneme) do chlievika Menovky riadkov. Ak sa nám to podarí Miesto nálezu v hornom paneli stučnie, zjaví sa vedľa neho kvačka (čo indikuje jeho použitie v zostave), objaví sa v sekcii Menovky riadkov a zároveň sa aktualizuje KT na nejaké (ne)zmysluplné hodnoty:

Tabuľka však nemá žiadne dáta! Napravíme to: len čo si rozpomenieme na pôvodnú otázku. „Chcem vedieť celkové počty zvierat za jednotlivé okrsky.” Položky, ktoré sa majú spočítavať, priemerovať, a vôbec, robiť s nimi matematické zverstvá, patria do sekcie Hodnoty. Zdrapnime teda myšou pole Druh a presuňme ho do sekcie Hodnoty. Po presune i toto pole v hornej sekcii stučnie, začiarkne a KT sa aktualizuje — a v tomto prípade uvidíme konečne požadované dáta.

Pole v sekcii Hodnoty sa automaticky nazvalo Počet z druh, čo je pomerne neumelý slovenský preklad anglického “Count of …” a znamená, že vo výsledku sa spočítavajú jednotlivé zvieratá za okrsky. Máme teda presne to, čo sme chceli: a dosvedčuje to aj výsledná tabuľka.

Chcem vedieť počty zvierat v jednotlivých druhoch za jednotlivé okrsky

V tomto prípade chcem rozšíriť predošlý report o podrobné rozloženie nájdených druhov v okrskoch. Po riadkoch chcem vidieť okrsky a po stĺpcoch vidieť, že v Dúbravke sa našli tri nosorožce a 1 pytón.

Nadviažeme teda na predošlý príklad: v sekcii Menovky riadkov teda majme pole Miesto nálezu, v sekcii Hodnoty zase pole Druh. Po stĺpcoch chceme vidieť druhy? Tak zdrapnime myšou pole Druhy a presuňme ho do sekcie Menovky stĺpcov. Tabuľka sa preorganizuje a máme presne, čo sme chceli.

Chcem vedieť počty zvierat… ale inak!

Ak chceme mať zvieratá po stĺpcoch, a okrsky po riadkoch, stačí ťahaním myšou vymeniť položky v Menovkách riadkov a _Menovkách stĺpcov. Týmto dosiahneme transpozíciu dát.

Sumár

S použitím pár klikov a ťahaní vieme elegantne za pár sekúnd vygenerovať utešené dynamické zostavy. Tu sme si ukázali len tie najjednoduchšie príklady: KTčky toho v skutočnosti vedia omnoho a omnoho viac.