U II T lnstitut U II 1 biostatistiky r n a analýz Aplikace MS Office, podzim 2022 Základy práce s aplikací MS Excel Mgr. Renata Chloupková (chloupkova@iba.muni.cz) Organizační informace Rozvrh - podzim 2022 —19. září 2022 Or.—26. září 2022 3. 10. října 2022 4. 17. října 2022 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 - Vzorce - Absolutní / relativní odkazy - Závislosti - předchůdci / následníci - Knihovna funkcí - Užitečné funkce: - KDYŽ() - ROKQ,... M T Institut M lil í biostatistikv Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková t ^ analýz Samostatné cvičení - vyhodnocení -Vyhodnocení: - 3 studenti neodevzdali (náhradní úkol?), ostatní OK - Poznámky k řešení: - úkol V (délka hospitalizace) - šlo vypočítat i jen jako prostý rozdíl obou hodnot, případně využití vzorce DAYS() - úkol IX (BMI) - druhá mocnina se dá v excelu spočítat pomocí funkce POWER() - zapisuje se jako POWER(číslo; mocnina) - tj. druhá mocnina = POWER(číslo; 2) - úkol XIII (osoby se jménem Josef) -využití vzorců SUMIFQ; COUNTIFQ 4 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková 1)1 U N I MED Institut biostatistiky a analýz Vzorec COUNTIFQ - Zápis: = COUNTIFQ - Vráti počet buněk, které splňují určité kritérium. klíčové slovo oblast hledání \ podmínka / kritérium pro vyhledání buněk = COUNTIF(A:A; „Josef") uvozeni vzorce • Konkrétní řetězec („XXX") • Odkaz na buňku (B2) • Matematická podmínka („>0") U N I lns,i,u, ED biostatistiky a analýz Vzorec SUMIFQ - Zápis: = SUMIFQ - Vrátí součet hodnot buněk v oblasti, která splňuje určité kritérium. klíčové slovo oblast hledání podmínka / kritérium pro vyhledání buněk uvozeni vzorce = SUMIF A:A; „Josef"; C:C \ • Konkrétní řetězec („XXX") • Odkaz na buňku (B2) • Matematická podmínka („>0") oblast součtu (nepovinný argument) uui ED biostatistiky a analýz II |\| T lnstitut U l\l 1 biostatistiky r q a analýz 3.1. Grafy Vytváření různých typů grafů. Formátování grafů. Minigrafy. 7 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková 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. Sol bor D om ů rozložen ľ strán ky Vzorce Data Revize Z-obrazen ľ Vývojář Inquire Nová karta XY Chart Labels Sešit Q Řek Kontingentní1" Doporučené Tabulka tabulka kontingencnftabulky Tabulky [□Qí? 1 U Obrázky Online Obrazce SmartArt Snímek obrázky T obrazovkyT Ilustrace El Store A Moje doplňky T Doplňky Doporučene ]^^^^^yřontingencni grafy T ä graf t Grafy ri M\ 3D Map T Prohlídky Vložřt kombinovaný graf Umožňuje použit tento typ grafu ke zvýraznění různých 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 a analýz Spojnice trendu v grafu - Do bodového grafu lze přidat spojnici definovanou matematickým vztahem veličin na osách x a y. Graf s lineárni spojnici 20 18 16 14 12 10 4 \ p k* % * t 2003 2005 2007 2009 2011 2013 2015 2017 201S Výběr matematického vztahu Název spojnice trendu Extrapolace trendu Zobrazení dalších parametrů spojnice Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková Formát spojnice trendu Možnosti spojnice trendu T & O ill t x Ä Možnosti spojnice trendu y_ k) Exponenciálni + Lineárni Logaritmická Polynom i c ká Mocninná Perioda 2 Klouzavý průměr Název spojnice trendu ® Automaticky O Vlastní Odhad Dopředu Dozadu □ Hodnota Y Lineární [Řadyl) 0,0 období 0,0 období 0,0 Zobrazit rovnici v grafu Zobrazit hodnotu spolehlivosti R UNI lns,i,u, ED biostatistiky a analýz 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: lli. Spojnicový Sloupcový Vzestupy/ poklesy Minigrafy V MS Excel 2016 jsou na výběr 3 typy minigrafu. Oblast zdrojových dat (řádek) pro minigraf. Buňka, do které bude minigraf umístěn Vytvořit minigrafy Zvolte požadovaná data, Oblast dat: X Zvolte požadované umístěni'minigrafu. Oblast umístěni': OK Storno 10 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 U|\| T >nstitut l\l 1 biostatistiky r£ q a analýz 3.2. Kontingenční tabulky a grafy Kontingenční tabulky. Kontingenční grafy. 11 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková 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: 2x2 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. Výsledek vyšetření Pohlaví Nemocný Zdravý Celkem Muž 45 11 56 Žena 25 6 31 Celkem__70_17__87 12 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? 11 U II ED biostatistiky a analýz Ukázka kontingenční tabulky Nemocný Zdravý Celkem Muž 45 11 56 Žena 25 6 31 Celkem 70 * 17 87 Kontingenční tabulka absolutních četností Větší počet nemocných mužů, který je dán pouze vyšším zastoupení mužů v celkovém vzorku (56 z 87) Nemocný Zdravý Celkem Muž 80,4 % 19,6% 100,0% Žena 80,6 % 19,4% 100,0% Kontingenční tabulka řádkových procent Po výpočtu relativních četností vidíme, že se muži a ženy neliší ve výskytu onemocnění Jsou více nemocní muži nebo ženy? 13 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková um t institut U l\l ± biostatistiky r£ Q a analýz 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 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 Vytvoření kontingenční tabulky v Excelu - Karta Vložení —► Kontingenční tabulka domů vložení soubor ROZLOŽÍ tal Kontingenční' Doporučené Tabulka Ol tabulka kontingenčnftabulky Tabulky x v fx Vytvořit kontingenční'tabulku Zvolte data, která chcete analyzovat: ® Vybrat tabulku či oblast labulka/oblast: | O E01-1^^ externí zdroj dat [zvolit připojeni,,, | Název připojeni': Zvolte umístěni'sestavy kontingenčnftabulky: O Nový list ® Bti stuj i cf I i st Umístěni': Listí !SAS1 Zvolte, jestli chcete analyzovat víc tabulek. I I Přidat tahle data do datového modelu X Zdroj dat (kromě Excelu i např. externí databáze) Zdrojová oblast dat Umístění tabulky OK Sto rno U |\| lnstitut biostatistiky 15 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková n /l l- Pi a analýz ED Kontingenční tabulky - rozvržení Kontingenční tabulka ] Chcete-li vytvořit sestavu, zvolte pole ze seznamu polí kontingenční tabulky. parametry na řádcích Pole kontingenční tabulky Vyberte pole, která chcete přidat do sestavy: □ Model I I Cílena léčba I I Linie léčby Centrum I I Mutace I I Rok nasazenrna léčbu I I Rok-měsíc nasazenína léč □ Počet záznamů Počet pacientu na léčběv I I Počet pacientů na léčběv I I Počet pacientů na léčběv I I Počet pacientů na léčběv I I Počet pacientů na léčběv I I Počet pacientů na léčběv I I Počet pacientů na léčběv I I Počet oacientů na léčbě parametry, které je možné zobrazit v kontingenční tabulce roce 2016 roce 2017 období 2016_01 období 2016_02 období 2016_0Í období 2016_04 období 2016_05 období 2016 06 Přetáhnete pole do jednéz následujících oblast T FILTRY parametry ve sloupcích = ŘÁDKY Z HODNOTY parametry n Odložit aktualizaci rozložení AKTTUALIZA.CE Pole kontingenční tabulky Vyberte pole, která chcete přidat do sestavy: Cílená léčba I I Linie léčby 0 Centrum 1 I Mutace I I Rok nasazeni'na léčbu I I Rok-měsíc nasazenína léčebu 0 Počet záznamů 1 I Počet pacientů na léčběv roceZG16 I I Počet pacientů na léčběv roceZG17 I I Počet pacientů na léčběv období' Z01601 I I Počet pacientů na léčběv období' 2D16_02 I I Počet pacientů na léčběv období' Z016_03 Počet pacientů na léčběv období' ZDI 6_C4 Počet pacientů na léčběv období' 2016_05 I I Počet pacientů na léčběv období' Z01606 B ™kí l/Íl R ÍYT Přetáhněte pole do jednéz následujících oblastí: T FILTRY lili SLOUPCE Cílena léčba ^~~| = ŘÁDKY ±^^r T. HODNOTY Centrum ' | Pocetz PoČEtzáznamů T Q Odložit aktualizaci rozložení AKTUALIZACE 16 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 Kontingenční tabulky - nastavení Pole kontingenční tabulky Vyberte pole, která chcete pŕidat do sestavy: 0 Cílená léčba 1 I Linie léčby 0 Centrum 1 I Mutace I | Rok nasazeni' na léčbu Rok-mésíc nasazeni na léčebu 0 Počet záznamů 0 Počet pacientu na léčbév roce 2016 □ Počet pacientů na léčbév roce 2017 Q Počet pacientů na léčbě v období' 2015_01 Q Počet pacientů na léčbév období' 2015_02 □ Počet pacientů na léčbév období' 2015_03 □ Počet pacientů na léčbév období' 2016_04 □ Počet pacientů na léčbév období' 2016.05 Q Počet pacientů na léčbév období' 2016_06 1 I PhFbI mimilitiii U!*ti» ■■ ntirlntif TfíftU ÍY7 Přetáhnete pole do jedné: následujících oblasti": T FILTRY Přesunout nahoru Přesunout dolu Přesunout na začátek Přesunout na Iconec Přejit k filtru sestavy Přejit k popiskům řádků Přejit k popiskům sloupe Přejit k hodnotám Odstranit pole Nastaveni'poli hodnot... :t z Počet záznamu Způsob sumarizace položky N a staven ľ p o I ľ h o d n ot Název zdroje: Počet záznamu X Vlastni'název: Počet z Počet záznamu Souhrn dat Zobrazit hodnotyjako Kritéria pro shrnutí hodnot pole Zvolte typ výpočtu, který chcete použit pro shrnutí datzvybraného pole: Součet _ _ A Počet Průměr Maximum Minimum Součin V Formát čísla OK Storno □ . Počet z Počet záznamů Popisky sloupců ■ Popisky řádků léčivo A léčivo B léčivo C léčivo D Celkový součet Nemocnice 1 216 102 75 36 429 Nemocnice 2 150 54 ee 105 375 Nemocnice 3 19 S 117 30 105 450 Nemocnice 4 111 iC 30 171 Nemocnice 5 168 96 102 366 Celkový součet 843 369 201 378 1791 □ Odložit aktualizaci rozloženi 17 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková UNI E D Výsledná kontingenční tabulka Institut biostatistiky a analýz 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 a, Karta Analýza (Kontingenční tabulka) N-STPOJE KGNTÉ^KlČNI TABULKY „ .. , , au- ľ h t- ▲ Kontingenční graf ) b razení vývojář doplňky analýza ll™u AKtUailZaCe dat ^ návrh^^ Aktualizace dat -* Seskupit výběr ÍI Oddělit Vložit Vložit Připojeni' Aktualizovat Změnit Vymazat Vybrat Přesunout Pole, položky Nástroje Relace Kontingenční' Doporučené Seznam Tlačítka Záhlaví L[T] Seskupit pole průřez časovou osu filtru ' zdroj dat" ~ ~ asady OLAP~ graf kontingenčnítabulky polí +/- polí Skupina I Filtr I Data I Akce I Výpočty I Nástroje I Zobrazit U II T lnstitut U U í biostatistiky ED 18 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková n /i r- Pi a analýz 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 19 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková n /l l- Pi a analýz M T Institut U l\l ± biostatistiky ED U|\| T >nstitut l\l 1 biostatistiky p q a analýz 3.3. Pokročilé vzorce Funkce SVYHLEDATQ. 20 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková Funkce SVYHLEDATQ - 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). =SVYHLEDAT(G5;$A$2:$C$5;2;NEPRAVDA) M T Institut u li x biostatistikv 21 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková „ „ _ _ „ * r ľ] 3 analýz