Tipy a triky v Excelu Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová  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é tabulky či 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  Vkládání řádků a sloupců a odstranění buněk  ALT+I+K – vložení řádku  ALT+I+S – vložení sloupce  ALT+A+S – odstranění vybraných buněk  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, jak se v datech pohybovat 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:  Odstranění ukotvení: Po ukotvení příček se automaticky možnost „Ukotvit příčky “ změní na „Uvolnit příčky“ Ukotví řádky nad označenou buňkou a sloupce vlevo od označené buňky 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 filtraci1. 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 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 Formáty buněk Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová • Obecný (bez formátu) • Číslo (desetinná místa, oddělení 1000) • Měna (desetinná místa, jednotky - symbol) • Datum (různé formátování – 24.9.2015, 24. září 2015, 24-9-15, ...) • Čas • Procenta • Text • Další (vč. nastavení vlastního formátu)... 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 • Co s barevnými buňkami? • Použijeme filtr! Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová Automatické seznamy  Umožňují vložit hodnotu výběrem ze seznamu hodnot – usnadnění zadávání: 2. Označíme buňky, v nichž chceme mít rozevírací seznam → Ověření dat 1. V jiném listu, než je datová tabulka, vytvoříme seznam hodnot 3. 4. 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 Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová Seznamy I.  „Soubor“ → „Možnosti“  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 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 !!! 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  „Soubor“ → „Možnosti“ 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. 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 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) Komentáře, sledování změn Komentáře k jednotlivým buňkám možnost zamknout list či sešit dvojicí hesel sledování změn a jejich schvalování nebo zamítání  karta „Revize“ Kontingenční tabulky Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová Vytvoření kontingenční tabulky v Excelu Zdroj dat (kromě Excelu i např. externí databáze) Krok 1 Zdrojová oblast dat Umístění tabulky Krok 2 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 Kontingenční tabulky – nastavení II. Kontingenční tabulka Způsob sumarizace položky 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ě Analýza ve skupině Data klikněte na Aktualizovat (Alt+F5), nebo na Aktualizovat vše (Ctrl+Alt+F5) Aktualizace dat 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 Kontingenční graf Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová Data z kontingenční tabulky lze vizualizovat pomocí kontingenčního grafu. Návod: Na kartě Analýza ve skupině Data klikněte na Kontingenční graf. Kontingenční graf Příklad kontingenčního grafu: 0 10 20 30 40 50 60 Počet aut dané značky Vzorce Vytvořil Institut biostatistiky a analýz, Masarykova univerzita J. Jarkovský, L. Dušek, M. Cvanová Vzorce  vpisují se do buněk sešitu  vzorce jsou vždy uvozeny = (lze též + -)  aritmetické operátory + zabudované funkce Excelu  pro „sčítání“ nečíselných položek se používá & (klávesová zkratka: alt gr + c)  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ňkuaritmetický operátor Vzorce – odkaz na buňku  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  relativní odkaz se při automatickém vyplnění buněk vzorcem posune  mění se s kopírováním, při vložení a odstranění řádku nebo sloupce  Absolutní odkaz  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 Pamatuj: Adresu upevníme pomocí znaku $ (klávesová zkratka: altr gr + ů nebo F4) Vzorce – využití seznamu vzorců Funkce a její stručný popis Kategorie vzorců průvodce funkcí Tvorba a závislosti vzorců Výběr funkce z konkrétních knihoven Označení a odznačení předchozích a následných vzorců Zpřehlednění vzorců (zalomení řádku): ALT+ENTER Výběr funkce Zobrazí místo výsledné hodnoty vzorec Vzorce – užitečné funkce Celkem 408 funkcí ve verzi MS Excel 2010, ve verzi 2013 přidáno 50 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; • SVYHLEDAT – spojování tabulek podle identifikátoru - řádku. 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; • 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.