U II T lnstitut U II 1 biostatistiky r n a analýz Aplikace MS Office, podzim 2021 Základy práce s aplikací MS Excel Mgr. Renata Chloupková (chloupkova@iba.muni.cz) Organizační informace Rozvrh - podzim 2021 —1. listopadu 2021 2. 8. listopadu 2021 3. 15. listopadu 2021 // PŘESUN 4. 22. listopadu 2021 Ukončení bloku „MS Excel" - řádná docházka - aktivita v hodinách - samostatná cvičení lili U l\l I biostatistikv 2 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity _ J J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková M F D & 3 yZ Shrnutí předchozí lekce - Import / export dat -Tipy a triky: - Editace listů, ukotvení příček - Kopírování / vkládání - Klávesové zkratky - Formulář |^^^2| - Automatické seznamy https://kahoot.it/ - Filtr dat - Ověřování dat, podmíněné formátování i hledání duplicit Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková U íl T lnstitut U II 1 biostatistiky r Q a analýz 2.2. Práce se vzorci a funkcemi v Excelu Práce se vzorci v interaktivním režimu listu - zadávání vzorců, jejich zobrazení, skrytí, kopírování a úpravy. Pojmenování oblastí buněk pomocí řádku názvů. Správa pojmenovaných oblastí. Absolutní a relativní odkazy na buňky a oblasti. Funkce a knihovny funkcí. 4 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková Vzorce - vpisují se do buněk sešitu - vzorce jsou vždy uvozeny = (lze též + -) - aritmetické operátory + zabudované funkce Excelu - pro „sčítání" nečíselných položek se používá & (klávesová zkratka: alt gr + c) - výpočet je založen buď na číselných konstantách nebo odkazech na buňky konstanta zabudovaný vzorec Excelu = 3 * odmocnina(Al) uvození vzorce \ ^ aritmetický operátor odkaz na buňku lili U l\l I biostatistikv 5 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková „ „ _ _ „ ' r ľ] 3 analýz Vzorce - relativní odkaz na buňku - A1 = buňka 1. řádku sloupci A - A1 :B6 = blok buněk - levý horní roh je v 1. řádku, sloupec A, pravý dolní na řádku 6, sloupec B - A:A = blok sloupců -11:11 = blok řádků - relativní odkaz se při automatickém vyplnění buněk vzorcem posune - mění se s kopírováním, při vložení a odstranění řádku nebo sloupce - blok buněk lze pojmenovat vepsáním názvu do pole názvů: m ; DOMŮ VLOŽENÍ ROZLOŽENÍ STRÁNKY VZORCE gfcj & Vyjmout ^ Ěl Kopírovat -Vložit ^* Kopírovat formát Calibri 11 - A'a = b r u - __ - & * A - ^ Schránka ri Písmo Průtok X s/ fX Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková U NI lns,i,u, ED biostatistiky a analýz Vzorce - absolutní odkaz na buňku - odkaz na buňku je pevně dán, při kopírování nebo automatickém vyplnění se nemění - lze uzamknout jak řádky, tak sloupce samostatně uzamčení řádku uzamčení sloupce $A$1 Pamatuj: odkaz upevníme pomocí znaku $ (klávesová zkratka: altr gr + ů nebo F4) M T Institut v lil x biostatistikv Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková „ „ _ _ „ * r_ n 3 analýz Vzorce - tipy a triky I - Závislosti vzorců - karta Vzorce označenia odznačenípředchozích a následných vzorců +*n Předchůdci °C| Následníci 1^ Odebrat šipky ^Zobrazit vzorce Y\ Kontrotfc^hyb T (fxj vyhodnocenN^ojce Závislosti vzorcu sledování změn hodnot i ve skrytých a neviditelných sloupcích kontrola chyb zobrazení vzorců namísto hodnot v buňkách -Zpřehlednění vzorců - Vložit konec řádku alt + enter 8 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková U NI lns,i,u, ED biostatistiky a analýz Vzorce - tipy a triky II -Vkládání komentářů, změny listu - karta Revize označení a odznačenípředchozích a následných vzorců ^ ~~j ^ 2? Zobrazit nebo skrýt komentár IÔ Zobrazit všechny komentáre Nový Odstranit Předchozí Další .... komentár Zobrazit rukopis Komentár možnost zamknout list či sešit dvojicí hesel nastavení oblasti s možností úprav i-^j Za rn kn aiJfa sd íl et sešit Povolit uživatelům úpravy oblastí Zamknout ZSmknout Sdílet list sešit sešit Sled ování zrn en Změny sledování změn a jejich schvalování nebo zamítání UIH Institut biostatistiky Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková Vzorce - tipy a triky III Pojmenované oblasti - oblast pojmenujeme v poli názvů: - ve vzorcích využíváme tyto názvy př. = SUMA(Průtok) = MAX(Průtok) DOMŮ VLOŽENÍ ROZLOŽENÍ STRÁN KV VZORCE 0; „větší než 0"; „<0") uvozeni vzorce jj" výsledek, pokud je podmínka splněna 13 um t institut U l\l ± biostatistiky Textové a statistické funkce v MS Excel - Od verze 2007 obsahuje MS Excel některé pokročilé statistické funkce - ZLEVA, ZPRAVA, ČÁST - funkce pro ořezávání textových řetězců; - Standardní matematické funkce (SIN, COS, LOG, LOGZ, EXP) - a mnoho dalších. - CONFIDENCE - výpočet intervalu spolehlivosti (při normálním rozdělení); - CORREL, PEARSON - výpočet Pearsonova korelačního koeficientu; - COVAR - výpočet kovariance dvou množin dat; - COUNTIF - počet hodnot oblasti splňujících zadanou podmínku; - DEVSQ - součet čtverců odchylek od výběrového průměru; - FDIST, GAMMADIST, CHIDIST, TDIST, NORMDIST aj. - různá rozdělení pravděpodobnosti; - PRŮMODCHYLKA - průměrná hodnota absolutních odchylek; - SLOPE - směrnice lineárního modelu; - TTEST, ZTEST, CHITEST - statistické testy shodnosti; - Řadu dalších funkcí však Excel postrádá a je potřeba využít silnějšího nástroje 14 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková y UN i MED Institut biostatistiky a analýz Samostatné cvičení - úkoly 1 - Datové podklady: - 2 vzorce excel zadáni.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) 15 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková y UN i MED Institut biostatistiky a analýz 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 leukocytu (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. 16 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková y UN i MED Institut biostatistiky a analýz