logo-IBA Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová 4. Vzorce v Excelu Kontingenční tabulky v Excelu logo-IBA logomuni Zdroje dat Excelu — Import dat z webu / MS Word pomocí schránky Windows. —Excel umožňuje připojit externí zdroje dat. — — — — — — — — — — —Propojení lze aktualizovat ručně/nastavit interval. —Po zrušení propojení je třeba soubor odpojit. — — — logo-IBA logomuni Zdroje dat Excelu import_dat_0.jpg Žlutý čtverec se šipkou u každé HTML tabulky. logo-IBA logomuni Zdroje dat Excelu import_dat_1.jpg Načtou se veškerá data v tabulce, často včetně balastu. logo-IBA logomuni Vzorce v listu Excelu —vpisují se do buněk sešitu —vzorce jsou vždy uvozeny = (lze též +, -). —aritmetické operátory + zabudované funkce Excelu —pro logické sčítání nečíselných položek se používá & —výpočet je založen buď na číselných konstantách nebo odkazech na buňky — =3*odmocnina(A1) uvození vzorce konstanta zabudovaná funkce Excelu odkaz na buňku logo-IBA logomuni Vzorce – odkaz na buňku stylu A1 —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 blok lze pojmenovat vepsáním názvu do pole názvů: —A:A = blok sloupců —11:11 = blok řádků relativní odkaz se při automatickém vyplnění buněk vzorcem posune —Absolutní odkazy — 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 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 tabulátor CTRL+ALT+TAB Vložit konec řádku ALT+ENTER zobrazení vzorců namísto hodnot v buňkách logo-IBA logomuni komentare.jpg 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ů vzorce_2.jpg možnost zamknout list či sešit dvojicí hesel sledování změn a jejich schvalování nebo zamítání 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; • PERCENTIL – 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 Cyklické odkazy a iterativní výpočty —Cyklický odkaz je vzorec, který odkazuje na proměnnou (oblast), ve které je obsažena i buňka se vzorcem. —Pro správnou funkci cyklického odkazu je třeba nastavit iterativní přepočet (opakované přepočítávání hodnot). — — — — — Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Kalina Možnosti aplikace Excel Povolení iterací, nastavení jejich počtu a přesnosti (může zpomalovat výpočet). logo-IBA Cyklické odkazy a iterativní výpočty —Lze je využít při hledání přibližného (numerického) řešení např. při optimalizaci nebo řešení rovnic. —Užitečné jsou pro tvorbu jednoduchých heatmap přímo v Excelu. — — — — — Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Kalina logo-IBA Cyklické odkazy a iterativní výpočty —Numerické řešení soustavy rovnic pomocí cyklického odkazu. —Je třeba dávat pozor, aby iterativní výpočet byl konvergentní. — — — — Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Kalina Soustavu lze přepsat jako . diverguje (chyba se v každém kroku násobí dvěma) konverguje (chyba se v každém kroku dělí dvěma) logo-IBA Kopírování / Vkládání Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová —Kopírování vzorců, textů, celých sloupců (zkopírování pomocí CTRL+C; dále „Vložit jinak...“) — — — — — — — — — — —Kopírování grafů z Excelu do Wordu: Vložit jinak → Typ: Obrázek (rozšířený metasoubor) — Vyzkoušej logo-IBA Graf se dvěma osami Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová —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 – v nastavení formátu datové řady. — — — — logo-IBA Spojnice trendu v grafu Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová — — — — — — —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, M. Cvanová —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 v Excelu, 1. část 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ž a b a + b Žena c d c + d Celkem a + c b + d a + b + c + d = N Kontingenční tabulka vztahu pohlaví a onemocnění 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 Kontingenční tabulka vztahu pohlaví a onemocnění 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 Kontingenční tabulka vztahu pohlaví a onemocnění do 1 týdne 1 – 2 týdny nad 2 týdny Celkem Základní vzdělání 10 9 5 24 Středoškolské vzdělání 32 18 6 56 Vysokoškolské vzdělání 4 2 2 8 Celkem 46 29 13 88 Hodnocení nesmyslného vztahu: dosažené vzdělání a doba strávená v nemocnici logo-IBA logomuni Kontingenční tabulky Zdroj dat (kromě Excelu i např. externí databáze) Graf nebo tabulka Zdrojová oblast dat Umístění tabulky Microsoft Office 2007 logo-IBA logomuni Kontingenční tabulky – rozvržení Microsoft Office 2007 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í Microsoft Office 2007 Aktualizace dat Kontingenční tabulka Způsob sumarizace položky Možnosti tabulky Kontingenční graf