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
̶ Grafy
̶ Graf se dvěma osami
̶ Spojnice trendu
̶ Minigrafy
̶ Kontingenční tabulky
̶ Tvorba + nastavení
̶ Kontingenční grafy
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
4
4.1. Pokročilé vzorce, podmíněné
formátování
Funkce SVYHLEDAT().
Podmíněné formátování.
Maticové (CSE) vzorce.
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
5
Funkce SVYHLEDAT()
̶ 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).
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
=SVYHLEDAT(G5;$A$2:$C$5;2;NEPRAVDA)
Podmíněné formátování
̶ Záložka „Domů“ → „Podmíněné formátování“.
̶ Barevné označení buněk nebo výplň buňky symbolem podle námi zadaných
kritérií, např.:
̶ numerická hodnota větší/menší než průměr
̶ datum z konkrétního období
̶ podobná slova
̶ duplicitní údaje
̶ Co s barevnými buňkami?
̶ Použijeme filtr!
6 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
7
Maticové vzorce
̶ Maticové vzorce umožňují počítat s pravoúhlými oblastmi na listech MS Excel
jako s maticemi (sčítání, násobení apod.).
̶ Říká se jim také CSE vzorce, protože se po zadání vzorce do řádku vzorců
potvrzují klávesovou zkratkou Ctrl + Shift + Enter.
̶ Maticový vzorec je celý uzavřen ve složené závorce.
Složená závorka
označuje, že jde
o CSE vzorec.
{=SUMA(A1:B2*D1:E2)}
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
8
Maticové vzorce – zápis
̶ Maticové konstanty umožňují pomocí vzorce zadat řádkový/sloupcový vektor
nebo celou matici.
̶ Zápis řádkového vektoru (konstanty):
{={1\2\3\4\5}}
̶ Zápis sloupcového vektoru (konstanty):
{={1;2;3;4;5}}
̶ Zápis matice (5 sloupců, 3 řádky):
{={1\2\3\4\5;6\7\8\9\10;11\12\13\14\15}}
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
9
Samostatné cvičení – úkoly 1
̶ Datové podklady:
̶ 4_data.xlsx
̶ Zadání:
I. Vytvořte kopii listu „data“ a nazvěte ji výsledky, nastavte žlutou barvu karty
II. Ukotvěte horní řádek tabulky
III. Vytvořte proměnnou „léčivo_nemocnice“ jako spojení sloupců „Léčba“ a „Nemocnice“ (jako oddělovač využijte podtržítko)
IV. Vytvořte sloupce „Linie léčby“ a „Mutace“ vždy za sloupce „Linie léčby_kód“ a „Mutace_kód “.
V. Pomocí funkce SVYHLEDAT() nahraďte číselníky ve sloupcích „Linie léčby_kód“ a „Mutace_kód “ do nově vzniklých proměnných v
bodě IV.
VI. Všechny buňky doplněné pomocí SVYHLEDAT() převeďte na hodnoty
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
10
Samostatné cvičení – úkoly 2
̶ Zadání – pokračování
VII. Vytvořte sloupec „První nebo druhá linie léčby“, do sloupce překódujte linii léčby (využijte sloupec „Linie léčby“ nebo „Linie
léčby_kód“) pomocí funkce „když“ následovně: 12 = 1. nebo 2. linie, 99 = bez ohledu na linii léčby.
VIII. Pomocí minigrafů se podívejte na vývoj ve sloupcích „Počet pacientů na léčbě v období 2016_01“ až „Počet pacientů na léčbě v
období 2016_12“ – v grafech zobrazte extrémy
IX. Vytvořte list „Grafy“ – na list vložce následující grafy (vhodné vycházet z kontingenční tabulky):
X. 1. graf: zesumarizujete počty ve sloupci „Léčba“
XI. 2. graf: zobrazte vývoj sloupce „Rok_léčba_začátek“ jako trend v letech
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková