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í. 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, J. Kalina 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 • Typy proměnných (dat) —Kvalitativní (kategoriální) proměnná —lze ji řadit do kategorií, ale nelze ji kvantifikovat — Příklad: ?? — —Kvantitativní (numerická) proměnná —můžeme ji přiřadit číselnou hodnotu — Příklad: ?? — logo-IBA Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek Typy proměnných (dat) —Kvalitativní (kategoriální) proměnná —lze ji řadit do kategorií, ale nelze ji kvantifikovat —Příklady: pohlaví, HIV status, barva vlasů ... — —Kvantitativní (numerická) proměnná —můžeme ji přiřadit číselnou hodnotu —Příklady: výška, váha, teplota, počet hospitalizací ... logo-IBA •Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek • Kvalitativní znaky —Binární znaky: dvě kategorie, obvykle se kódují pomocí číslic 1 (přítomnost sledovaného znaku) a 0 (nepřítomnost sledovaného znaku). —Příklad: ?? — —Nominální znaky: několik kategorií (A, B, C), které nelze uspořádat. —Příklad: ?? — —Ordinální znaky: několik kategorií, které lze vzájemně seřadit, tedy můžeme se ptát, která je větší/menší (1<2<3). —Příklad: ?? — logo-IBA •Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek • Kvalitativní znaky —Binární znaky: dvě kategorie, obvykle se kódují pomocí číslic 1 (přítomnost sledovaného znaku) a 0 (nepřítomnost sledovaného znaku). —Příklady: Diabetes (1-ano, 0-ne), — Pohlaví (1-muž, 0-žena). —Nominální znaky: několik kategorií (A, B, C), které nelze uspořádat. —Příklad: krevní skupiny (A/B/AB/0). — —Ordinální znaky: několik kategorií, které lze vzájemně seřadit, tedy můžeme se ptát, která je větší/menší (1<2<3). —Příklady: stupeň bolesti (mírná/střední/velká), — stadium maligního onemocnění (I/II/III/IV). — logo-IBA •Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek • Kvantitativní znaky —Intervalové znaky: interpretace rozdílu dvou hodnot (stejný interval mezi jednou a druhou dvojicí hodnot vyjadřuje i stejný rozdíl v intenzitě zkoumané vlastnosti). Společný znak intervalových znaků: nula byla stanovena uměle, tedy pouhou konvencí. Příklad: teplota měřená ve stupních Celsia, letopočet. — — — — — — — —Poměrové znaky: kromě rozdílu interpretujeme i podíl dvou hodnot. — Příklady: výška v cm, váha v kg, ... — — Den Teplota Rozdíl 1 Podíl 1 1. 2 °C - - 2. 4 °C +2 2x 3. 6 °C +2 1.5x 1 Srovnání s měřením z předchozího dne 1.5krát vyšší teplota ve srovnání s 2. dnem, přičemž došlo ke stejnému nárůstu teploty jako při srovnání 2. a 1. dne logo-IBA Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, J. Kalina —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, J. Kalina 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 Editace listů Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová —Excelovský soubor (sešit) se skládá z listu(ů) (List1, List2, ...), které je možné libovolně pojmenovat, obarvit, kopírovat, přesouvat jejich pořadí na liště atd. —Ve vzorcích lze odkazovat na jiné listy než ve kterém se nacházíme. —V jednotlivých listech lze ukládat např. různé datové tabulky, číselníky, seznamy atd. — http://www.efektivne.eu/images/stories/images/obrazky/Excel/ms-excel-2007-manual/BarvaKarty.png Klik pravým tlačítkem myši na záložku listu —Vložení listu: —Záložkou na spodní liště —Klávesovou zkratkou: Shift + F11 logo-IBA Kopírování / Vkládání Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, J. Kalina —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 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 nezbytné 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, jedna návštěva pacienta 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 dat je nezbytné kontrolovat překlepy v názvech kategorií; ¡Specifickým typem dat jsou data, u nichž je nezbytné kontrolovat, zda jsou uloženy v korektním formátu. — —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 Excel. Zásady pro ukládání dat logo-IBA Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, J. Kalina 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, J. Kalina 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, J. Kalina 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 ¡.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, J. Kalina Tipy a triky logo-IBA Ukotvení příček Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, J. Kalina —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, J. Kalina 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. —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 logo-IBA Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová Automatický zadávací formulář II. —Slouží k usnadnění zadávání dat do databázových tabulek —Po označení načítá automaticky hlavičky sloupců jako zadávané položky 1. Označíme názvy sloupců datové matice 2. Klikneme na novou ikonu Formulář v panelu nástrojů 3. Vyplníme údaje pro hodnocený subjekt 4. Do datové tabulky se doplní zadané údaje Vyzkoušej !!! 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í — Vyzkoušej !!! 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 Vyzkoušej !!! logo-IBA Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová Seznamy I. ¡„Tlačítko Office“ → „Možnosti aplikace Excel“ ¡ —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 logo-IBA Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová Seznamy II. Zápis jedné hodnoty ze seznamu a protažení do dalších buněk Automaticky byly doplněny následující složky seznamu ¡Upravit vlastní seznamy → 2. Využití při tvorbě dat 1. Definice seznamu Vyzkoušej !!! 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. Proměnná podle které bude soubor seřazen Styl seřazení: sestupně/ vzestupně/vlastní seznam 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 —Výhodné pro čištění dat (vyhledávání překlepů, kombinace textu a čísel) Výběr hodnot pro filtraci 1. Zapnutí filtru (alternativa klávesová zkratka Crtl+Shift+L) 2. Objeví se rozbalovací šipka s výčtem všech unikátních hodnot v daném sloupci dat 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 Podmíněné formátování Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová —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 o •Co s barevnými buňkami? •Použijeme filtr! logo-IBA Automatické dokončování hodnot buněk Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, J. Kalina —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“ — —