logo-IBA MS EXCEL MGR. RENATA CHLOUPKOVÁ (CHLOUPKOVA@IBA.MUNI.CZ) Aplikace MS Office Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, J. Kalina logo-IBA Organizační informace Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, J. Kalina •(změna vůči původnímu rozvrhu, náhrada za 22. 10.) •Rozvrh – podzim 2018 logo-IBA 1.1. Opakování Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, J. Kalina logo-IBA Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, J. Kalina Shrnutí předchozí lekce —Vzorce ¡Relativní / absolutní odkazy ¡Závislosti ¡Revize – komentáře, uzamknutí listu / sešitu ¡Seznamy vzorců ¡Knihovna funkcí ¡Užitečné funkce ¡ logo-IBA Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, J. Kalina Samostatné cvičení – úkoly 1 —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 89. 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) ¡ logo-IBA Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, J. Kalina Samostatné cvičení – úkoly 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. — ¡ logo-IBA Vytváření různých typů grafů. Formátování grafů. Minigrafy. 3.1. Grafy Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, J. Kalina logo-IBA Graf se dvěma osami Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, J. Kalina —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. — — — — logo-IBA Spojnice trendu v grafu Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, J. Kalina — — — — — — —Do bodového grafu lze přidat spojnici definovanou matematickým vztahem veličin na osách x a y — — Výběr matematického vztahu Zobrazení dalších parametrů spojnice Extrapolace trendu Název spojnice trendu logo-IBA Minigrafy Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, J. Kalina —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 logo-IBA Kontingenční tabulky. Kontingenční grafy. 3.2. Kontingenční tabulky a grafy Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, J. Kalina logo-IBA Kontingenční tabulka Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová •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? C:\Users\brozova\Desktop\red-question-mark-cartoon-character-with-a-confused-expression_150426020.j pg logo-IBA Ukázka kontingenční tabulky Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová Nemocný Zdravý Celkem Muž 45 11 56 Žena 25 6 31 Celkem 70 17 87 Jsou více nemocní muži nebo ženy? 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) C:\Users\brozova\Desktop\happy-red-question-mark-cartoon-character-pointing-with-finger_150257549.j pg 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í logo-IBA Kontingenční tabulky v Excelu: zdroj dat a příprava dat Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová 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 6. 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 logo-IBA logomuni Vytvoření kontingenční tabulky v Excelu Zdroj dat (kromě Excelu i např. externí databáze) Graf nebo tabulka Zdrojová oblast dat Umístění tabulky logo-IBA logomuni Kontingenční tabulky – rozvržení parametry na řádcích parametry dat parametry ve sloupcích parametry, které je možné zobrazit v kontingenční tabulce filtr logo-IBA logomuni Kontingenční tabulky – nastavení II. Kontingenční tabulka Způsob sumarizace položky logo-IBA Aktualizace dat v kontingenční tabulce Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová Při změně dat v tabulce se zdrojovými daty nedojde automaticky k aktualizaci dat v kontingenční tabulce. Musíte provést aktualizaci dat. 1.Stůjte kdekoliv v kontingenční tabulce 2.Na kartě Možnosti 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 1. 1. Aktualizace dat Možnosti tabulky Kontingenční graf logo-IBA Rozložení kontingenční tabulky Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová 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 logo-IBA Funkce SVYHLEDAT(). Maticové (CSE) vzorce. Cyklické odkazy a iterace v MS Excel. Podmíněné formátování. Tvorba heatmap. 3.3. Pokročilé vzorce, podmíněné formátování Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, J. Kalina logo-IBA 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). ÷ — — — — Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Kalina =SVYHLEDAT($G5;$A$2:$C$5;2;NEPRAVDA)