Aplikace MS Office, podzim 2019
Základy práce s aplikací MS Excel
Mgr. Renata Chloupková (chloupkova@iba.muni.cz)
1. 4. listopadu. 2019
2. 11. listopadu. 2019
3. 18. listopadu. 2019
4. 25. listopadu. 2019
2
Rozvrh – podzim 2019
Organizační informace
̶ řádná docházka
̶ aktivita v hodinách – samostatná cvičení
Ukončení bloku „MS Excel“
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
3
Shrnutí předchozí lekce
̶ Vzorce
̶ Absolutní / relativní odkazy
̶ Závislosti – předchůdci / následníci
̶ Revize – komentáře
̶ Knihovna funkcí
̶ Užitečné funkce:
̶ KDYŽ()
̶ ROK(),…
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
4
Samostatné cvičení – úkoly 1 (z lekce 2)
̶ Datové podklady:
̶ 2_vzorce_excel_zadani.xlsx
̶ Zadání:
I. Vytvořte kopii listu zadání a nazvěte ji výsledky, nastavte zelenou barvu karty
II. Pomocí roztahování buněk vyplňte proměnnou „ID” čísly od 1 do 88.
III. Ukotvěte ID pacientů a názvy proměnných ve sloupcích.
IV. Spojte „Jméno“ a „Příjmení“ do jednoho sloupce (např. Zdeněk Novák..)
V. Spočítejte délku hospitalizace z „první kontrola“ a „poslední kontrola“. Je získaná hodnota všude reálná? Pokud ne, tak
u kterých pacientů?
VI. Pomocí vzorce vyberte pouze „Rok poslední kontroly“ ze sloupce „poslední kontrola“. Seřaďte datový soubor podle této nové
proměnné. (nápověda: vyberte funkci z Knihovny funkcí – Datum a čas)
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
5
Samostatné cvičení – úkoly 2 (z lekce 2)
̶ Zadání – pokračování:
VII. Sloupec „nemocný“ překódujte pomocí funkce „když“ následovně: 1-nemocný, 0 –zdravý.
VIII. Převeďte „výšku“ na metry.
IX. Vypočítejte „BMI“. (nápověda: vzorec pro index tělesné hmotnosti najdete na internetu)
X. Spočítejte k jaké změně „tepu před“ a „tepu po“ došlo (např. léčbě nebo podání léku) (nápověda: pozor na chybějící hodnoty)
XI. Spočítej „Počet oblíbených činností“ (sloupec U-Y).
XII. Spočítej minimální, maximální a průměrnou hodnotu leukocytů (proměnná „Leukocyty“).
XIII. Spočítej celkovou výšku vše osob v souboru, které se jmenují Josef (proměnná „Jméno“). A dále počet osob, které toto jméno mají.
XIV. Pomocí podmíněného formátování nalezněte duplicitní záznamy dle jména pacienta. Jsou všechny Vámi označené záznamy
skutečně duplicitní? Duplicitní údaj smažte.
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
6
3.1. Grafy
Vytváření různých typů grafů.
Formátování grafů.
Minigrafy.
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
7
Graf se dvěma osami
̶ V případě grafu se dvěma různými zobrazovanými veličinami lze nastavit
jedné řadě zobrazování na vedlejší ose (svislé).
̶ Vedlejší osa má hodnoty nezávislé na hlavní ose – rozsah je optimalizován
podle velikosti grafu.
̶ MS Excel umožňuje vložit pouze jednu hlavní a jednu vedlejší osu.
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
8
Spojnice trendu v grafu
̶ Do bodového grafu lze přidat spojnici definovanou
matematickým vztahem veličin na osách x a y.
8
10
12
14
16
18
20
2003 2005 2007 2009 2011 2013 2015 2017 2019
Graf s lineární spojnicí
Výběr
matematického
vztahu
Zobrazení
dalších
parametrů
spojnice
Extrapolace
trendu
Název
spojnice
trendu
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
9
Minigrafy
̶ Minigrafy jsou od verze MS Excel 2016 novým typem obsahu buňky. Jde o
jednoduché (trendové) grafy se základními možnostmi formátování.
̶ Minigraf se zobrazuje na pozadí buňky, lze tedy přes něj psát text a
nastavovat formát buňky.
̶ Vložení minigrafu:
V MS Excel 2016
jsou na výběr 3
typy minigrafů.
Oblast
zdrojových
dat (řádek)
pro minigraf.
Buňka, do které
bude minigraf
umístěn
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
10
3.2. Kontingenční tabulky a grafy
Kontingenční tabulky.
Kontingenční grafy.
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
11
Kontingenční tabulka
̶ Frekvenční sumarizace dvou kategoriálních proměnných (binárních, nominálních nebo
ordinálních proměnných).
̶ Obecně: R x C kontingenční tabulka (R – počet kategorií jedné proměnné, C – počet
kategorií druhé proměnné).
̶ Speciální případ: 2 x 2 tabulka = čtyřpolní tabulka.
̶ Kontingenční tabulky: absolutních četností, celkových procent, řádkových/sloupcových
četností
̶ Př.: Sumarizace vyšetřených osob podle pohlaví a výsledku diagnostického testu.
Pohlaví
Výsledek vyšetření
Nemocný Zdravý Celkem
Muž 45 11 56
Žena 25 6 31
Celkem 70 17 87
?Jsou více
nemocní muži
nebo ženy?
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
12
Ukázka kontingenční tabulky
Nemocný Zdravý Celkem
Muž 45 11 56
Žena 25 6 31
Celkem 70 17 87
Nemocný Zdravý Celkem
Muž 80,4 % 19,6 % 100,0 %
Žena 80,6 % 19,4 % 100,0 %
Větší počet nemocných mužů, který je dán pouze vyšším
zastoupení mužů v celkovém vzorku (56 z 87)
Po výpočtu relativních četností vidíme, že se muži a ženy neliší ve výskytu onemocnění
Kontingenční
tabulka řádkových
procent
Kontingenční
tabulka absolutních
četností
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
?Jsou více
nemocní muži
nebo ženy?
13
Zdroj a příprava dat pro kontingenční tabulku
̶ Kontingenční tabulka se dá vytvořit:
1. z tabulky v daném sešitě
2. z dat z jiného sešitu Excelu
3. z externích dat (např. MS Access)
4. ze sloučených dat z více oblastí - z různých listů nebo různých sešitů
5. z jiné kontingenční tabulky
̶ Data musí být uspořádána formou standardního databázového seznamu:
̶ V prvním řádku: názvy polí
̶ Další řádky: data
̶ Vzhled tabulky: karta Domů → Formátovat jako tabulku
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
14
Vytvoření kontingenční tabulky v Excelu
̶ Karta Vložení → Kontingenční tabulka
Zdroj dat (kromě
Excelu i např. externí
databáze)
Zdrojová oblast dat
Umístění tabulky
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
15
Kontingenční tabulky – rozvržení
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
parametry
na řádcích
parametry
dat
parametry ve
sloupcích
parametry,
které je možné
zobrazit v
kontingenční
tabulce
filtr
16
Kontingenční tabulky – nastavení
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
Způsob
sumarizace
položky
Výsledná
kontingenční
tabulka
17
Aktualizace dat v kontingenční tabulce
̶ Při změně dat v tabulce se zdrojovými daty nedojde automaticky
k aktualizaci dat v kontingenční tabulce.
̶ Nutno provést aktualizaci dat:
1. Stůjte kdekoliv v kontingenční tabulce
2. Na kartě Analýza ve skupině Data klikněte na Aktualizovat (Alt+F5), nebo
na Aktualizovat vše (Ctrl+Alt+F5)
̶ Data z kontingenční tabulky lze vizualizovat pomocí kontingenčního grafu
Kontingenční graf
Karta Analýza (Kontingenční tabulka)
Aktualizace dat
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
18
Rozložení kontingenční tabulky
̶ Po vytvoření se kontingenční tabulka zobrazí v tzv. kompaktním formátu.
Lze ji zobrazit ale i ve formě tabulky, nebo ve formě osnovy:
1. Stůjte kdekoliv v kontingenční tabulce
2. Na kartě Návrh vyberte tlačítko Rozložení sestavy a volbu Zobrazit ve
formě osnovy nebo Zobrazit ve formě tabulky
̶ Kompaktní formát - uspořádání tabulky aby zabírala co nejméně místa
̶ Forma osnovy - řádková pole nižší úrovně je od vyšších úrovní odsazena, řádky nejsou odděleny čarami
̶ Forma tabulky - klasická forma tabulky, pole nižší úrovně jsou v dalším sloupci
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
19
3.3. Pokročilé vzorce, podmíněné
formátování
Funkce SVYHLEDAT().
Podmíněné formátování.
Maticové (CSE) vzorce.
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
20
Funkce SVYHLEDAT()
̶ Umožňuje vyhledávat v tabulce podle klíčového sloupce – ten musí být vždy
první v zadané tabulce.
̶ Funkce má 4 argumenty:
1. Vyhledávaná hodnota (odpovídá hodnotám v 1. sloupci tabulky).
2. Oblast (tabulka), ve které se nachází vyhledávací hodnota.
3. Pořadové číslo sloupce v oblasti (tabulce), ve kterém je hodnota, která se má vrátit.
4. Volitelně logická hodnota přesné shody: PRAVDA v případě přibližné shody, nebo
NEPRAVDA v případě přesné shody vyhledávané hodnoty s hodnotou v prvním sloupci
oblasti (tabulky).
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
=SVYHLEDAT(G5;$A$2:$C$5;2;NEPRAVDA)