Aplikace MS Office, podzim 2019
Základy práce s aplikací MS Excel
Mgr. Renata Chloupková (chloupkova@iba.muni.cz)
2
1.1. Úvod do MS Excel, základní typy dat
Ú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í.
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
3
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.
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
4
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: ??
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
5
Typy proměnných (dat)
Kvalitativní (kategoriální) proměnná
̶ lze ji řadit do kategorií, ale nelze ji kvantifikovat
Příklad: pohlaví, HIV status, barva vlasů
Kvantitativní (numerická) proměnná
̶ můžeme ji přiřadit číselnou hodnotu
Příklad: výška, váha, teplota, počet hospitalizací
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
6
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: ??
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
7
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: 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říklad: stupeň bolesti (mírná / střední / velká),
stadium maligního onemocnění (I / II / III / IV).
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
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říklady: 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.
8
Den Teplota Rozdíl 1 Podíl 1
1. 2 °C - -
2. 4 °C +2 2
3. 6 °C +2 1,5
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.
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
MS Excel
̶ 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ů.
9 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
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).
10
0
5
10
15
20
25
1.2011 2.2011 3.2011 4.2011 5.2011 6.2011 7.2011
Concentration(ng/m3)
Time (sampling date)
α-HCH
Digitel
PS-1
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
Editace listů
̶ 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.
11
Klik pravým tlačítkem myši na záložku listu
Vložení listu:
̶ Ikonou „+“ na spodní liště
̶ Klávesovou zkratkou: Shift + F11
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
Kopírování / Vkládání
̶ 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)
12
Vyzkoušej
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
13
1.2. Import, export, uložení a čištění dat
Základní typy dat.
Import dat ze souborů různých formátů / z webové stránky (tabulky).
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.
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
Zásady pro ukládání dat
̶ 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.
14 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
DATA – ukázka uspořádání datového
souboru
15
Parametry (znaky)
Opakování
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
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.
16 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
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.
17 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
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.
18 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
Zdroje dat Excelu
Žlutý čtverec se šipkou u HTML obsahu.
19 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
Zdroje dat Excelu
̶ Načtou se veškerá data v tabulce, často včetně balastu.
20 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
Tipy a triky
̶ 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ů).
21 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
Ukotvení příček
̶ 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:
̶ Ukotvit příčky – ukotví řádky
nad označenou buňkou a
sloupce vlevo od označené
buňky.
̶ Ukotvit horní řádek.
̶ Ukotvit první sloupec.
̶ Ukotvení zrušíme opětovným odkliknutím možnosti ukotvení příček.
22 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
23
2.1. Správa dat
Dva typy práce s listy v MS Excel.
Zadávací formulář.
Seznamy.
Filtr a rozšířený filtr.
Automatické opravy a dokončování.
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
Databázová struktura dat v Excelu
24
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.
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
̶ Aplikaci automaticky zadávaného formuláře je nutné aktivovat
̶ Záložka „SOUBOR“ → „Možnosti“
̶ Automatický zadávací formulář spustíme pomocí nové ikonky na panelu nástrojů Rychlý přístup
Automatický zadávací formulář I.
25 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
̶ 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
Automatický zadávací formulář I.
26
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
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
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í
27 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
Automatická kontrola dat
̶ Umožňuje ověřit typ, rozsah nebo povolit pouze určitý seznam hodnot
zadávaných do sloupce databázové tabulky
28
Co je povoleno – definiční obory
čísel, seznamy, vzorce atd.
Rozsahy hodnot, načtení seznamů apod.
komunikace s uživatelem
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
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
29
̶ Záložka „SOUBOR“ → „Možnosti“
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
Seznamy II.
30
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
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
Ř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
31
Proměnná podle které
bude soubor seřazen
Styl seřazení: sestupně/
vzestupně/vlastní seznam
Zkontrolujte, zda
seřazení nezničí vazby
mezi buňkami =
kontrola oblasti, kterou
řadíte.
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
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)
32
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
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
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.
33
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í
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
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!
34 Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková
Automatické dokončování hodnot buněk
̶ 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
35
̶ Záložka „SOUBOR“ → „Možnosti“
Institut biostatistiky a analýz Lékařské fakulty Masarykovy univerzity; J. Jarkovský, L. Dušek, J. Kalina, R. Chloupková