Aplikace MS Office, podzim 2019
Základy práce s aplikací MS Excel
Mgr. Renata Chloupková (chloupkova@iba.muni.cz)
1. 4. listopadu. 2019
2. 11. listopadu. 2019
3. 18. listopadu. 2019
4. 25. listopadu. 2019
2
Rozvrh – podzim 2019
Organizační informace
̶ řádná docházka
̶ aktivita v hodinách – samostatná cvičení
Ukončení bloku „MS Excel“
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
3
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í
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
4
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í.
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
5
Vzorce
= 3 * odmocnina(A1)
uvození vzorce
konstanta zabudovaný vzorec Excelu
odkaz na buňkuaritmetický operátor
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
̶ 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
6
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ů:
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
7
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ě
$A$1uzamčení sloupce
uzamčení řádku
Pamatuj: Adresu upevníme pomocí znaku $ (klávesová zkratka: altr gr + ů nebo F4)
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
̶ Zpřehlednění vzorců
̶ Vložit konec řádku alt + enter
8
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
zobrazení vzorců namísto
hodnot v buňkách
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
9
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
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
10
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)
̶ oblasti spravujeme ve správci (karta Vzorce):
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
11
Vzorce – využití seznamu vzorců
̶ Knihovna funkcí:
vyhledání funkce
nápověda k zadání
zadání funkce (průvodce)
průběžná
kontrola
výsledku
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
funkce a její
stručný popis
kategorie vzorců
12
Vzorce – užitečné funkce
̶ 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;
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
13
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
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
14
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)
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
15
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.
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková