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 •Rozvrh – podzim 2018 logo-IBA 4.0. Opakování Samostatná práce 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 —Grafy ¡Vytváření grafů ÷Graf se dvěma osami ¡Formátování grafů ¡Minigrafy ¡ —Funkce SVYHLEDAT() 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: ¡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 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.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./2. linie, 99 = bez ohledu na linii léčby. VIII.Vytvořte list „Grafy“ – na list vložce následující grafy: IX.1. graf: zesumarizujete počty ve sloupci „Léčba“ X.2. graf: zobrazte vývoj sloupce „Rok_léčba_začátek“ jako trend v letech XI.Pomocí minigrafů se podívejte na vývoj ve sloupcích „ Počet pacientů na léčbě v roce 2016“ a „ Počet pacientů na léčbě v roce 2017“; obdobně na vývoj v rámci sloupců „Počet pacientů na léčbě v období 2016_01“ až „Počet pacientů na léčbě v období 2016_01“ – v grafech zobrazte extrémy XII. XII. XII. — ¡ logo-IBA Samostatné cvičení – hodnocení Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, J. Kalina —Hodnocení — ¡Výsledný excel vložit do úschovny v IS ¡Část „maticové vzorce“ není povinná ¡Komentáře vkládat do speciálního listu „Komentář“ — VII. VII. VII. — ¡ logo-IBA Funkce SVYHLEDAT(). Maticové (CSE) vzorce. 4.1. Pokročilé vzorce Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, J. Kalina logo-IBA 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. — — — — Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Kalina Složená závorka označuje, že jde o CSE vzorec. {=SUMA(A1:B2*D1:E2)} logo-IBA Maticové vzorce —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}} ÷ ÷ — — — — Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Kalina > > > logo-IBA Kontingenční tabulky. Kontingenční grafy. 4.2. Kontingenční tabulky a grafy Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, J. Kalina logo-IBA Kontingenční tabulka Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová •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: 2 x 2 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. Pohlaví Výsledek vyšetření Nemocný Zdravý Celkem Muž 45 11 56 Žena 25 6 31 Celkem 70 17 87 Jsou více nemocní muži nebo ženy? C:\Users\brozova\Desktop\red-question-mark-cartoon-character-with-a-confused-expression_150426020.j pg 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 Jsou více nemocní muži nebo ženy? Nemocný Zdravý Celkem Muž 80,4 % 19,6 % 100,0 % Žena 80,6 % 19,4 % 100,0 % Větší počet nemocných mužů, který je dán pouze vyšším zastoupení mužů v celkovém vzorku (56 z 87) C:\Users\brozova\Desktop\happy-red-question-mark-cartoon-character-pointing-with-finger_150257549.j pg Po výpočtu relativních četností vidíme, že se muži a ženy neliší ve výskytu onemocnění Kontingenční tabulka řádkových procent Kontingenční tabulka absolutních četností logo-IBA Kontingenční tabulky v Excelu: zdroj dat a příprava dat Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová 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 6. 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 logo-IBA logomuni Vytvoření kontingenční tabulky v Excelu Zdroj dat (kromě Excelu i např. externí databáze) Graf nebo tabulka Zdrojová oblast dat Umístění tabulky logo-IBA logomuni Kontingenční tabulky – rozvržení 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í II. Kontingenční tabulka Způsob sumarizace položky logo-IBA Aktualizace dat v kontingenční tabulce Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová Při změně dat v tabulce se zdrojovými daty nedojde automaticky k aktualizaci dat v kontingenční tabulce. Musíte provést aktualizaci dat. 1.Stůjte kdekoliv v kontingenční tabulce 2.Na kartě Možnosti 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 1. 1. Aktualizace dat Možnosti tabulky Kontingenční graf logo-IBA Rozložení kontingenční tabulky Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová 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 logo-IBA Stručná historie maker v MS Excel. Význam maker, oblasti jejich použití. Dvě formy maker - funkce a metody, rozdíly mezi nimi. Nahrávání vlastního makra. Vytvoření a úpravy vlastní funkce/metody. 4.3. Makra v MS Excel Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, J. Kalina logo-IBA Z historie —Možnost napsat vlastní funkci/makro je v Excelu od první verze v roce 1985. —Do roku 1993 (verze 5) byla makra zaznamenávána ve vlastním jazyce Excelu a ukládána jakou soubory .xlm. — — — http://upload.wikimedia.org/wikipedia/en/d/d0/VBDOS-icon.PNG —Starší verze maker jsou zpětně kompatibilní, ale není doporučné jejich použití z hlediska bezpečnosti. —Od verze 5 je možné makra zaznamenávat v jazyce Visual Basic. —Visual Basic byl vyvinut v roce 1991 kombinací staršího jazyka Basic (1964) a prostředí Ruby společnosti Tripod. — — Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Kalina logo-IBA Visual Basic makro —Účelem maker v Excelu je buď usnadnění opakujících se činností nebo zpřístupnění složitějších funkcí, kterých není možné dosáhnout při rozumné složitosti ručně, případně kombinace obojího. http://www.planet-source-code.com/vb/2010Redesign/images/LangugeHomePages/VisualBasic6.gif —Pomocí maker lze rovněž vkládat do listů Excelu interaktivní prvky. —„Všechno, co jde udělat ručně, lze udělat také pomocí makra.“ —Existují dva režimy zadávání maker – záznam přímo v prostředí Excelu a ruční zápis makra v jazyce Visual Basic. — — Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Kalina logo-IBA Záznam makra —Nejprve je nutné zpřístupnit v Excelu kartu Vývojář (od verze 2010): Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Kalina „Zobrazit na pásu kartu Vývojář“. Položka seznamu „Oblíbené“. logo-IBA Záznam makra —Jednoduchý způsob vytvoření makra. K dispozici jsou pouze standardně přístupné funkce, ale lze je pomocí makra opakovat jako proceduru. Tlačítko pro zahájení záznamu makra. Otevírá dialogové okno se seznamem maker. Přepíná do prostředí Visual Basic Tlačítko pro zastavení záznamu makra. Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Kalina Přepíná mezi absolutními a relativními odkazy v makru. logo-IBA Záznam makra —Před spuštěním záznamu makra: Uživatelský název makra. Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Kalina Klávesová zkratka neodporující standardním zkratkám. Musí jít o písmeno nebo příbuzný znak. V případě kolize navrhuje Excel varianty Ctrl nebo Ctrl+Shift. Místo pro uložení makra. Volitelný popis makra. logo-IBA Záznam makra —Okno pro spouštění maker: Úprav makra v prostředí VB. Krokování makra v prostředí VB. Spuštění vybraného makra. Změna popisu a klávesové zkratky. Seznam vytvořených maker. logo-IBA Visual Basic —Integrated development environment (IDE): Okno pro psaní kódu Project explorer Properties window logo-IBA Visual Basic Několik úvodních poznámek k jazyku Visual Basic —jazyk není case sensitive (nerozlišuje malá a velká písmena), —do kódu lze vepisovat komentáře uvozené apostrofem ', —mezery a odsazení nemají vliv na interpretaci kódu, —důležité je rozdělení řádků – jedna funkce na jeden řádek, —více funkcí na řádku je možné spojit pomocí dvojtečky :, —dlouhé řádky lze rozdělit pomocí kombinace , _, — — — logo-IBA Visual Basic —Dvě základní entity, které lze vytvářet v prostředí visual Basic jsou metody a funkce. —Vytvořené funkce se automaticky přenáší do prostředí Excelu (konkrétního sešitu typu .xlsm, ke kterému je makro připojeno). —Funkce se od metody liší tím, že má definovánu nějakou návratovou hodnotu. —Makra nahraná pomocí záznamu maker v Excelu jsou automaticky považována za metody. —Funkce i metody se zadávají jako zdrojový kód psaný uživatelem nebo generovaný programem do okna kódu a uvozují se speciálními výrazy. logo-IBA Visual Basic - funkce —Každá funkce je uvozena a uzavřena specifickými příkazy: Function nazev_funkce(arg1, arg2,…) As typ tělo funkce End Function ●Tělo funkce se skládá z operací, v nichž jsou pro výpočet využity proměnné specifikované na vstupu do funkce (argumenty z 1. řádku funkce) a funkce jazyka Visual Basic. ●Návratová hodnota funkce je určena přiřazením hodnoty do názvu funkce. nazev_funkce = arg1 + arg2 logo-IBA Visual Basic - metody —Každá metoda je uvozena a uzavřena specifickými příkazy: Sub nazev_metody(arg1, arg2,…) tělo metody End Sub ●Tělo metody se skládá z operací, v nichž jsou pro výpočet využity proměnné specifikované na vstupu do metody a funkce jazyka Visual Basic. logo-IBA Visual Basic — — — Jméno Popis Velikost Rozsah Integer Celé číslo 32 bitů -231 až 231 Long Celé číslo, ale větší rozsah 64 bitů -263 až 263 Boolean Logická hodnota (pravda, nepravda) 8 bitů True nebo False String Textová hodnota 16 bitů pro každý znak --- Char Znak 16 bitů 0 až 2-16 Double Desetinné číslo s dvojitou přesností 64 bitů ± 5 x 10-324 až ± 1,7 x 10308 Primitivní datové typy jazyka Visual Basic logo-IBA Visual Basic Některé užitečné funkce jazyka Visual Basic vIf podmínka Then příkaz (blok příkazů) End If (v případě bloku), vWhile podmínka příkaz (blok příkazů) Wend vFor i = a To b příkaz Next – for cyklus pro předem daný počet kroků, vSheets("název listu").Select – výběr označeného listu, vRange("buňka1:buňka2").Select – výběr oblasti buněk, vRange(buňka1, buňka2).Select – totéž zadáno číselně, vActiveCell.Offset(radky,sloupce) – přesun do zadané buňky va Mod b – zbytek po celočíselném dělení čísla a číslem b, vSqr(a) – druhá odmocnina z čísla a, v — logo-IBA Visual Basic – objekty a vlastnosti —Objektově orientované programování pracuje s objekty, které mají určité specifikované vlastnosti. —Visual Basic považuje v Excelu za objekt celý soubor, list, buňku, graf, ovládací prvek (tlačítko, zatržítko, fromulář aj.). —V editoru IDE lze měnit vlastnosti objektů v okně Properties window; některé lze měnit také přímo v Excelu (např. pojmenování listu, vybarvení buňky) a také samotnými makry. —Vlastnost objektu lze odkazovat přes tečku .. —Např. nastavení barvy buňky A1 na červenou se provede následujícím příkazem: Range("A1").Interior.Color = Red — logo-IBA Visual Basic – události —Kromě vlastností se k objektu pojí také konkrétní události, které mohou být impulzem pro aktivaci funkce nebo metody. —Každý objekt má svoji specifickou sadu událostí, kterých jsou desítky. —Důležité události mohou být např.: vActivate – aktivace sešitu (otevření uloženého souboru), vSheetActivate – aktivace požadovaného listu, vClick – kliknutí na ovládací prvek, vChange – změna hodnoty prvku, vShow – zviditelnění prvku, vHide – zneviditelnění prvku. v logo-IBA Visual Basic – kam dál —Visual Basic je plnohodnotný programovací jazyk, k jeho obsažení by nestačil ani celý předmět MOF011, — —existuje celá řada elektronických i klasických učebnic ve všech jazycích, — —řada věcí je intuitivních a lze na ně přijít i bez odborného základu. v http://www.computermedia.cz/knihy/programovani-ve-visual-basicu-2010-CD_big.jpeg http://www.ucebnice.com/img/auto/138/0/K1611_nahledK1611.jpg