Práce s daty v Excelu

                                            podzim 2023
6. Kontingenční tabulka

Jaký typ dat? Taková data, která popisují kategoriální vlastnosti měřených subjektů: jaký druh,
jaký fenotyp, ze kterého prostředí, jaký orgán, pohlaví, atd. Kategoriálních proměnných může být
několik. Zároveň mohu mít i kvantitativní proměnné. Tabulka v Excelu pak umí rozdělit subjekty do
podmnožin podle jedné či několika kategoriálních proměnných a pro tyto podmnožiny počítat součty,
průměry a další charakteristiky.

6A.  Chytrá (smart) tabulka

Excel obyčejnou tabulku s daty převést na „pruhovanou“ tabulku se speciálními vlastnostmi. Často ji
tvoří automaticky při importu dat, potom připojí také datové připojení do zdrojového souboru, aby
byla možná aktualizace.

Pokud s těmito výdobytky pracovat nechceme, zbavíme se jich takto:
a) tabulka: karta Návrh tabulky > Nástroje > Převést na oblast
b) datové připojení: karta Data > Dotazy a připojení > Dotazy a připojení. Objeví se panel v pravé
části obrazovky se seznamem připojení v tomto sešitu. Druhým tlačítkem myši klikneme na vybrané
připojení a volíme Odstranit.

Naopak, pokud chceme z obyčejné tabulky udělat chytrou tabulku, volíme CTRL+T nebo
(karta) Vložení > (panel) Tabulky > (volba) Tabulka.

Karta Návrh tabulky nabízí další možnosti, užitečný je Řádek souhrnů (panel Možnosti stylů tabulek)

Pokud vyplníme sousední řádek nebo sloupec tabulky, tabulka je automaticky integruje!


6B. Kontingenční tabulka

Označit zdrojovou tabulku s daty a klikat (karta) Vložení > Kontingenční tabulka. Pokud je tabulka
vstupních dat větší, doporučuje se tvořit kontingenční tabulku na novém listě – potvrzuji volbou
v dialogovém okně, které se samo otevře.

Objekt kontingenční tabulky je velmi interaktivní – ovládá se na panelu, který se objeví na pravé
straně pracovní plochy. Když kliknu mimo tabulku, panel zmizí, když kliknu do tabulky, zase se
objeví.

Z nabídky vybírám jména sloupečků vstupních dat, která se mají použít.
Do řádků a sloupců jdou sloupce s kategoriemi (vlastnosti, typicky zadané jako slova). Podle nich
se tvoří podmnožiny. Číselné (kvantitativní) hodnoty nejsou vhodné pro pojmenování řádků či
sloupců, většinou obsahují mnoho různých hodnot.

Do „kvadrantu“ Hodnoty musím také něco zadat. Chci-li spočítat sumarizaci nějaké kvantitativní
proměnné, zvolím tu. Pokud chci dostat jen počet řádků, které mají vlastnost A (řádek kont.
tabulky) a vlastnost B (sloupec kont. tabulky), zvolím do kvadrantu Hodnoty jakoukoli volnou
proměnnou. Jak se zobrazila? Jako „Součet z …“ nebo jako „Počet z …“. Kategoriální proměnná
(vlastnosti) by se měly zpracovat jako Počet, protože se slovy nejde počítat. Číselné proměnné by
se měly zpracovat jako Součet. Toto mohu změnit kliknutím na tuto proměnnou v kvadrantu Hodnoty,
např. na „Součet z Metabolism_W“, klik je „normální“ ukazováčkem, volbou Nastavení polí hodnot a
v novém dialogovém okně volbou Součet / Počet / Průměr / a další.

V této nabídce Nastavení polí hodnot jsou ještě další možnosti: Formát čísla (tlačítko) a Zobrazit
hodnoty jako (záložka).
Nastavení formátu čísla (desetinná místa, oddělovače tisíců, měna, čas, datum, …) je vhodné měnit
přes tuto volbu, aby si to tabulka „pamatovala“. Protože je chování tabulky velmi interaktivní,
klasicky nastavené formáty se často ztratí.
V záložce Zobrazit hodnoty jako rozkliknout nabídku pole s textem Žádný výpočet. Mohu vybírat
z výpočtů různého procentuálního zastoupení (celkové, na řádcích, ve sloupcích, …).

Do „kvadrantu“ Filtry mohu zadat další kategoriální proměnnou – tabulka pak umí zobrazit hodnoty
jen pro vybranou kategorii.

Sloupcový graf z aktuální tabulky získám přes kartu Vložení > Grafy nebo přes kartu Analýza
kontingenční tabulky > Nástroje. Volím Kontingenční graf.
Pozor, graf se změní s každou změnou tabulky. Chci-li uložit graf v současné podobě, musím ho
okopírovat (Ctrl+C) a vložit jako obrázek. Potom je to samozřejmě už jen obrázek bez propojení na
kontingenční tabulku a také bez možnosti změn či oprav.

Uložit tabulku. Podobný problém řeším i se samotnou kontingenční tabulkou. Pokud chci nějakou
podobu tabulky uložit např. pro vložení do textu bakalářky, musím kopii tabulky vložit buď jako
hodnoty (zruším výpočetní vzorce v pozadí každé buňky) nebo jako obrázek.


Úkoly k řešení – list savci:

1.      Zjištěte počty záznamů pro jednotlivé řády. Kontingenční tabulku vložte na nový list.

2.      Do stejného kvadrantu (nejlépe kvadrant Řádky) přidejte také proměnnou Group.Změňte pořadí
položek v kvadrantu. Co se stalo?

3.      Rozšiřte tabulku o informaci o potravní preferenci.

4.      K tabulce počtů vytvořte graf.

5.      Na další list vytvořte tabulku průměrného výkonu metabolismu pro řádky a podtřídy (Groups).
Najdete chybu v zařazení řádu do podtřídy?

6.      Vytvořte graf pro tuto tabulku. Co vidíte? Filtrujte data v grafu tak, abyste viděli i
velikost průměru pro další řády.

7.      Logaritmujte (desítkovým logaritmem > LOG(W)) výkon metabolismu v datech (= přidejte novou
proměnnou do zdrojové tabulky). Aktualizujte data pro kontingenční tabulku:
(karta) Analýza kontingenční tabulky > (panel) Data > Změnit zdroj dat  např. tím, že opravíte
písmeno posledního sloupečku.
V kontingenční tabulce použijte novou proměnnou log(W) v kvadrantu Hodnoty a upřesněte na výpočet
průměru.
Logaritmická transformace vstupních dat umí zmenšit rozdíl mezi malými a velkými hodnotami. Na
grafu jsou potom viditelné i menší výsledky. Na logaritmickou škálu osy je dobré upozornit
v popisku grafu.