logo-IBA Úvod do práce s tabulkovým procesorem MS Excel. Stručná historie vývoje MS Excel. Zakládání, otevírání, ukládání a zavírání souborů. Pracovní prostředí MS Excel, přizpůsobení a rozšíření. Formátování buněk, vyjmutí, vložení a kopírování. Spolupráce mezi aplikacemi balíku MS Office. 1.1. Úvod do MS Excel, základní typy dat 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, M. Cvanová Anotace —Současná statistická analýza se neobejde bez zpracování dat pomocí statistického software. Předpokladem úspěchu je správné uložení dat v definované formě. —Nejčastěji jde o databázové tabulky umožňující zpracování dat v celé škále různých aplikací. —Neméně důležité je věnovat pozornost čištění dat předcházejícímu vlastní analýze. Každá chyba, která vznikne nebo není nalezena ve fázi přípravy dat, se promítne do všech dalších kroků a může zapříčinit neplatnost výsledků a nutnost opakování analýzy. logo-IBA Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, J. Kalina Typy proměnných (dat) Binární = dummy data Proměnná, která může nabývat pouze dvou hodnot. Bývá definovaná odpovědí na otázku (např. TRUE × FALSE, 1 × 0). Nominální = kategoriální data Proměnná, která může nabývat počtu hodnot (n ∊ ℕ), pro které neexistuje přirozené pořadí (např. barvy vzorků). Ordinální data Nominální proměnná, pro kterou ale existuje jasné pořadí kategorií (např. velikost oděvů S, M, L, XL). Kardinální data Ordinální proměnná, u které lze určit rozdíl mezi kategoriemi. Ty jsou stejně vzdálené (např. počet dětí v rodině). Intervalová data Spojitá proměnná, u které můžeme určit rozdíl mezi kategoriemi – obvykle jde o počet (např. teplota ve °C, čas). Poměrová data Intervalová proměnná, u které má smysl určovat podíly jednotlivých kategorií (např. hmotnost, vzdálenost). logo-IBA Spojitá data Diskrétní data Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, J. Kalina Jak vznikají informace ? – různé typy dat znamenají různou informaci Kolikrát ? Podíl hodnot větší/menší než specifikovaná hodnota ? O kolik ? Větší, menší ? Rovná se ? Procenta odvozené hodnoty Data poměrová Data intervalová Data kardinální Data ordinální Data nominální Data binární Kategoriální otázky Otázky „Ano/Ne“ Samotná znalost typu dat ale na dosažení informace nestačí… logo-IBA Spojitá data Diskrétní data Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, J. Kalina Jak vznikají informace ? – různé typy dat znamenají různou informaci Data poměrová Data intervalová Data kardinální Data ordinální Data nominální Data binární Samotná znalost typu dat ale na dosažení informace nestačí… PRŮMĚR MEDIÁN MODUS X Y = f logo-IBA Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová —Tabulkový procesor. —První verze programu 30. 9. 1985 (Macintosh). —Součást balíku kancelářských aplikací MS Office. —Aktualizace každé 2 až 3 roky; nové funkce, rozšíření počtu řádků a sloupců, změna formátu. —Nejnovější formát Office XML je zazipovaný XML dokument, přípona .xlsx. —Aktuální verze 2016 umožňuje ukládat tabulku až o 1 048 576 řádcích a 16 384 sloupcích. —Maximální velikost buňky je 32 767 znaků. —Excel umožňuje práci se širokou škálou dalších formátů. — MS Excel logo-IBA Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová Možnosti MS Excel — Správa a práce s tabulárními daty. — Řazení dat, výběry z dat, přehledy dat. — Formátování a přehledné zobrazení dat. — Zobrazení dat ve formě grafů. — Různé druhy výpočtů pomocí zabudovaných funkcí. — Tvorba tiskových sestav. — Makra – zautomatizování častých činností. — Tvorba aplikací (Visual Basic for Aplications). — 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 Tipy pro práci s Wordem Automatické titulky ve Wordu Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová —Automatické titulky ve Wordu pro snazší úpravy pravý klik na obrázek pravý klik na styl „Titulek“ úprava stylu dle potřeby logo-IBA Automatické seznamy ve Wordu Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová —Máme-li vytvořené automatické titulky grafů a tabulek, pak lze vytvořit automatické seznamy Každý objekt, který chceme zahrnout do automatického seznamu, musí mít automatický titulek Aktualizace čísel titulků – pravý klik na označený titulek (obdobně aktualizace již vytvořeného seznamu) logo-IBA Základní typy dat. Import dat ze souborů různých formátů. Import dat z webové stránky (tabulky). Import dat z databází pomocí ODBC. Tipy a triky pro práci v MS Excel, klávesové zkratky. Rozvržení a dělení oken, ukotvení příček. Kontrola a čištění dat. 1.2. Import, export dat, jejich uložení a čištění 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, M. Cvanová —Správné a přehledné uložení dat je základem jejich pozdější analýzy. —Je vhodné rozmyslet si předem jak budou data ukládána. —Pro počítačové zpracování dat je vhodné ukládat data v tabulární formě. —Nejvhodnějším způsobem je uložení dat ve formě databázové tabulky: ¡každý sloupec obsahuje pouze jediný typ dat, identifikovaný hlavičkou sloupce; ¡každý řádek obsahuje minimální jednotku dat (např. pacient, měření apod.); ¡je nepřípustné kombinovat v jednom sloupci číselné a textové hodnoty; ¡komentáře jsou uloženy v samostatných sloupcích; ¡u textových (kategoriálních) dat je nezbytné kontrolovat překlepy v názvech kategorií; ¡specifickým typem dat jsou kalendářní data u nichž je nezbytné kontrolovat, zda jsou uložena v korektním formátu (dle aplikace). —Takto uspořádaná data je v tabulkových nebo databázových programech možné převést na libovolnou výstupní tabulku. —Pro základní uložení a čištění dat menšího rozsahu je možné využít aplikací MS Office. Zásady pro ukládání dat logo-IBA Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová Parametry (znaky) DATA – ukázka uspořádání datového souboru obr1.jpg logo-IBA Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová Import a export dat —Import dat ¡manuální zadávání; ¡import – podpora importu ze starších verzí Excelu, textových souborů, databází apod.; ¡kopírování přes schránku Windows – vkládání z nejrůznějších aplikací – MS Office, Statistica, přímo z HTML apod.; ¡využití textových souborů jako kompatibilního formátu pro přenos dat mezi různými aplikacemi. — —Export dat ¡ukládáním souborů ve formátech podporovaných jinými SW, časté jsou textové soubory, .dbf soubory nebo starší verze Excelu; ¡přímé kopírování přes schránku Windows. — logo-IBA Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová Import a export dat —Nejčastější datové formáty používané v MS Excel ¡.xlsx – současný Office Open XML formát od verze MS Excel 2007, má několik podverzí jen částečně kompatibilních; ¡.xls – starší binární varianta listů MS Excel (více verzí), stále používaná, ¡.csv – comma separated values, nejjednodušší tabulkový formát, 2 varianty, ¡.dbf – formát dBase, široce využívaný formát pro velké databáze; ¡.db – Paradox database, starší databázový systém; ¡.slk – SYmbolic LinK (SYLK) formát pro výměnu dat mezi aplikacemi Microsoft, neveřejný; ¡.txt – základní textový formát, často jediná možnost výměny dat s MS Excel. 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 —Výběr buněk ¡CTRL+HOME – přesunutí na levý horní roh tabulky; ¡CTRL+END – přesunutí na pravý dolní roh tabulky; ¡CTRL+A – výběr celého listu; ¡CTRL + klepnutí myší do buňky – výběr jednotlivých buněk ; ¡SHIFT + klepnutí myší na jinou buňku – výběr bloku buněk; ¡SHIFT + šipky – výběr sousedních buněk ve směru šipky; ¡SHIFT+CTRL+END (HOME) – výběr do konce (začátku) oblasti dat v listu; ¡SHIFT+CTRL+šipky – výběr souvislého řádku nebo sloupce buněk; ¡SHIFT + klepnutí na objekty – výběr více objektů. — Kopírování a vkládání ¡CTRL+C – zkopírování označené oblasti buněk; ¡CTRL+V – vložení obsahu schránky – oblast buněk, objekt, data z jiné aplikace; —Myš a okraje buňky ¡Chycení myší za okraj umožňuje přesun buňky nebo bloku buněk ¡ ¡ ¡Při chycení čtverečku v pravém dolním rohu výběru je tažením možno vyplnit více buněk hodnotami původní buňky (ve vzorcích se mění relativní odkazy, je také možné vyplnění hodnotami ze seznamu – např. po sobě jsoucí názvy měsíců. ¡ Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová Tipy a triky logo-IBA Ukotvení příček Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová —Umožňuje ukotvení libovolných řádků a sloupců pro pohodlné vkládání a prohlížení dat v tabulce. —Umožňuje číst řádky/sloupce ze začátku tabulky i po přesunutí se dále. — —Záložka „Zobrazení“ → „Ukotvit příčky“. — —Nabízené možnosti: oUkotvit příčky – ukotví řádky nad označenou buňkou a sloupce vlevo od označené buňky. oUkotvit horní řádek. oUkotvit první sloupec. o oUkotvení zrušíme opětovným odkliknutím možnosti ukotvení příček. logo-IBA Dva typy práce s listy v MS Excel. Zadávací formulář. Seznamy. Filtr a rozšířený filtr. Automatické opravy a dokončování. 2.1. Správa dat 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, M. Cvanová Databázová struktura dat v Excelu Jednotlivé záznamy (taxon, lokalita, měření, pacient atd.) Sloupce tabulky = parametry záznamů, hlavička udává obsah sloupce – stejný údaj v celém sloupci Excel neumožňuje pojmenování řádků a sloupců vlastními názvy. logo-IBA Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová Automatický zadávací formulář I. —Slouží k usnadnění zadávání dat do databázových tabulek —Načítá automaticky hlavičky sloupců jako zadávané položky Názvy sloupců Obsah dané buňky - editovatelný Nový záznam Vyhledávání Microsoft Office 2003 a starší logo-IBA Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová Automatický zadávací formulář II. —Aplikaci automaticky zadávaného formuláře je nutné aktivovat ¡„Tlačítko Office“ → „Možnosti aplikace Excel“ ¡ ¡ ¡ ¡ ¡ ¡ ¡ ¡ ¡ ¡ ¡ ¡ ¡ ¡ ¡Automatický zadávací formulář spustíme pomocí nové ikonky na panelu nástrojů Rychlý přístup; dále stejné Microsoft Office 2007 a novější logo-IBA Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová Automatické seznamy —Vytváří se z hodnot buněk v daném sloupci a umožňují vložit hodnotu výběrem ze seznamu již zadaných hodnot – usnadnění zadávání — Sloupec z nějž je seznam vytvořen a pro který platí Buňka, do níž se vloží vybraná hodnota logo-IBA Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová Automatická kontrola dat —Umožňuje ověřit typ, rozsah nebo povolit pouze určitý seznam hodnot zadávaných do sloupce databázové tabulky Co je povoleno – definiční obory čísel, seznamy, vzorce atd. Rozsahy hodnot, načtení seznamů apod. komunikace s uživatelem Microsoft Office 2007 logo-IBA Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová Seznamy I. —Skupiny hodnot zachovávající logické pořadí, některé jsou zabudované (např. dny v týdnu, měsíce v roce), další je možné uživatelsky vytvořit, slouží pro účely řazení a automatického vyplňování dat moznosti1 Výběr buněk pro nový seznam Načtení nového seznamu Existující seznamy Microsoft Office 2003 a starší logo-IBA Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová Seznamy II. ¡„Tlačítko Office“ → „Možnosti aplikace Excel“ ¡ ¡ ¡ ¡ ¡ ¡ ¡ ¡ ¡ ¡ ¡ ¡ ¡ ¡ ¡Vlastní seznamy dále stejné (viz předchozí slide) — Microsoft Office 2007 logo-IBA Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová Řazení dat —Řazení dat je nejjednodušším způsobem jejich zpřehlednění, užitečným hlavně u menších/výsledkových tabulek — ¡ Zkontrolujte, zda seřazení nezničí vazby mezi buňkami = kontrola oblasti, kterou řadíte. ¡ Využít první řádek oblasti jako záhlaví Další možnosti – řazení řádků, řazení podle seznamu Podle čeho řadit Směr řazení – vzestupně, sestupně logo-IBA Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová Automatický filtr —Pomocí automatického filtru je snadné vybírat úseky dat pro další zpracování na základě hodnot ve sloupcích databázové tabulky, výběr je možný i podle více sloupců (např. určitá skupina pacientů). —Funkce automaticky rozezná hlavičky sloupců v souvislé oblasti buněk. —Čísla filtrovaných řádků jsou zobrazena modře. —Výhodné pro čištění dat (vyhledávání překlepů, kombinace textu a čísel). Výběr hodnot pro filtraci Rozbalení seznamu hodnot nalezených ve sloupci logo-IBA Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová Rozšířený filtr —Funguje podobně jako automatický filtr, ale seznam povolených hodnot není nutné vybírat ručně – je uveden v oblasti jinde na listu (nebo i na jiném listu). —Podmínkou jsou shodná záhlaví filtrované oblasti a oblasti povolených hodnot. —Prázdné buňky odpovídají prázdné podmínce – tj. je-li v oblasti povolených hodnot nějaká buňka prázdná, splní podmínku libovolná buňka filtrované oblasti. —Čísla řádků filtrované oblasti jsou zobrazena modře. Tlačítko Upřesnit na kartě Data Výběr oblasti cílových hodnot (přefiltrovaných) Původní seznam včetně záhlaví Oblast kritérií včetně záhlaví logo-IBA Automatické dokončování hodnot buněk Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová —Vhodné pro textová pole; následně není nutné vypisovat celé slovo či slovní spojení, ale jen zvolit nabízené, již dříve použité slovo či slovní spojení —Automatické dokončování hodnot buněk je nutné nastavit ¡„Tlačítko Office“ → „Možnosti aplikace Excel“ — — logo-IBA 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í. 2.2. Práce se vzorci a funkcemi v Excelu 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 Anotace —Realitu můžeme popisovat různými typy dat, každý z nich se specifickými vlastnostmi, výhodami, nevýhodami a vlastní sadou využitelných statistických metod – od binárních přes kategoriální, ordinální až po spojitá data roste míra informace v nich obsažené. —Základním přístupem k popisné analýze dat je tvorba frekvenčních tabulek a jejich grafických reprezentací – histogramů. 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ý vzorec 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; • PERCENTILE – 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 Maticové (CSE) vzorce. Cyklické odkazy a iterace v MS Excel. Funkce SVYHLEDAT(). Podmíněné formátování. Tvorba heatmap. 3.1. Pokročilé vzorce, podmíněné formátování 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 Cyklické odkazy a iterativní výpočty —Cyklický odkaz je odkaz, který okazuje na proměnnou (oblast), ve které je obsažena i buňka s odkazem. —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 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). ÷ — — — — Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Kalina =SVYHLEDAT($G5;$A$2:$C$5;2;NEPRAVDA) logo-IBA Vytváření různých typů grafů. Formátování grafů. Minigrafy. Kontingenční tabulky. Kontingenční grafy. 3.2. Grafy, kontingenční tabulky a grafy Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, J. Kalina 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. — — — — 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 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.1. 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 Bi7541, —existuje celá řada elektronických i klasických učebnic ve všech jazycích, —příjemnou učebnici lze nalézt např. zde: http://www.gvp.cz/local/new/ucebnice/VisBas/obsah.htm, —ř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