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 —Import / export dat —Tipy a triky: ¡Editace listů, ukotvení příček ¡Kopírování / vkládání ¡Klávesové zkratky ¡Formulář ¡Automatické seznamy ¡Filtr dat ¡Ověřování dat, podmíněné formátování ¡ ¡ logo-IBA Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, J. Kalina Samostatné cvičení —Zadání: ¡1_priprava_dat_excel_zadani.xlsx — —Datové podklady: ¡1_priprava_dat_excel_data.csv ¡1_priprava_dat_excel_data.txt ¡ — ¡ logo-IBA 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í. 2.2. Práce se vzorci a funkcemi v Excelu 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 Anotace —Realitu můžeme popisovat různými typy dat, každý z nich se specifickými vlastnostmi, výhodami, nevýhodami a vlastní sadou využitelných statistických metod – od binárních přes kategoriální, ordinální až po spojitá data roste míra informace v nich obsažené. —Základním přístupem k popisné analýze dat je tvorba frekvenčních tabulek a jejich grafických reprezentací – histogramů. logo-IBA logomuni 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 — =3*odmocnina(A1) uvození vzorce konstanta zabudovaný vzorec Excelu odkaz na buňku aritmetický operátor logo-IBA logomuni Vzorce – relativní odkaz na buňku —Relativní odkazy ¡ 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ů: ¡ — logo-IBA logomuni Vzorce – absolutní odkaz na buňku — —Absolutní odkaz ¡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ě — $A$1 uzamčení sloupce uzamčení řádku Pamatuj: Adresu upevníme pomocí znaku $ (klávesová zkratka: altr gr + ů nebo F4) logo-IBA logomuni vzorce_1.jpg Vzorce – tipy a triky I Závislosti vzorců – karta Vzorce kontrola chyb označení a odznačení předchozích a následných vzorců sledování změn hodnot i ve skrytých a neviditelných sloupcích Zpřehlednění vzorců Vložit konec řádku ALT+ENTER zobrazení vzorců namísto hodnot v buňkách logo-IBA logomuni 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ů možnost zamknout list či sešit dvojicí hesel sledování změn a jejich schvalování nebo zamítání nastavení oblasti s možností úprav logo-IBA logomuni Vzorce – tipy a triky III Kontrola chyb Výběr funkce z knihoven Označení a odznačení předchozích a následných vzorců Vkládání komentářů a poznámek do vytvořeného souboru se vzorci logo-IBA logomuni Vzorce – využití seznamu vzorců Funkce a její stručný popis Kategorie vzorců průvodce funkcí logo-IBA logomuni Vzorce – užitečné funkce Celkem 408 funkcí ve verzi MS Excel 2010, ve verzi 2013 přidáno 50 nových funkcí, ve verzi 2016 přibude 5 nových funkcí. • SUMA – součet číselných hodnot oblasti; • SUMIF – podmíněný součet (podmínky v doplňkové oblasti); • PRŮMĚR – aritmetický průměr číselných hodnot oblasti; • GEOMEAN – geometrický průměr číselných hodnot oblasti; • COUNTIF – počet hodnot oblasti splňujících zadanou podmínku; • KDYŽ – logická podmínka (if); • MAX, MIN – maximum/minimum číselných hodnot oblasti; • MEDIAN – výpočet mediánu; • PERCENTILE – výpočet percentilů; • DATUM (ROK, MĚSÍC, DEN) – práce s kalendářními daty; • ABS – absolutní hodnota; logo-IBA logomuni 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 TŘEBA VYUŽÍT SILNĚJŠÍHO NÁSTROJE. 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. — ¡