ADZ základní statistické funkce

KMA/ADZ, Tomáš Ťoupal 2015

ADZ – základní statistické funkce Základní statistické funkce a znaky v softwaru Excel Znak + * / Ctrl+c Ctrl+v Ctrl+f = A1 A1:B2 $A$1: $B$2 $A$1 $A1 A$1

Stručný popis Sčítání buněk Odčítání buněk Násobení buněk Dělení buněk Vyjmutí buňky Vložení vyjmuté buňky Hledání (čísla, textu, …) Aktivace vzorce Označení buňky Označená oblast buněk Ukotvení oblasti Ukotvení buňky Ukotvení sloupce Ukotvení řádku

Funkce

Stručný popis Počet (oblast dat) Počet vyplněných buněk Suma (oblast dat) Suma hodnot buněk ve vybrané oblasti Průměr (oblast dat) Aritmetický průměr z vybrané oblasti Geomean (oblast dat) Geometrický průměr z vybrané oblasti Median (oblast dat) Odhad mediánu (50 % kvantil) Modus (oblast dat) Výpočet modusu (nejčastěji se vyskytující hodnota) Odmocnina (hodnota) Druhá odmocnina z vybrané hodnoty Power (hodnota; mocnina) Umocnění i odmocnění na daný exponent Svyhledat(co;oblast dat;posunutí, Vyhledávání hodnot v tabulkách dle sloupců podmínka) (posunutí = počet sloupců doprava, podmínka: 0 = přesně, 1 = přibližně) Sumif (oblast dat; podmínka; oblast sčítání) Sčítání obsahu po splnění logických hodnot Countif (oblast dat; podmínka; oblast Počet buněk po splnění logických hodnot počtu) RANK.EQ (hodnota; oblast dat; pořadí) Pořadová hodnota u hledané hodnoty buňky Small (oblast dat; pořadí) Výpis nejmenšího čísla pro dané pořadí Large (oblast dat; pořadí) Výpis největšího čísla pro dané pořadí Percentrank.inc (oblast dat; hodnota) Procentuálně vyjádřené pořadí v seznamu

1

KMA/ADZ, Tomáš Ťoupal 2015 Percentil.inc (oblast dat; procenta) Quartil.inc (oblast dat; požadovaný kvartil) Max (oblast dat) Min (oblast dat) Var.výběr (oblast dat) Smodch.výběr (oblast dat) Zaokrouhlit (číslo, číslice)

Výpis kvantilu dle zadané pravděpodobnosti Výpis hodnot jednotlivých kvartilů Nalezení maximální hodnoty v souboru Nalezení minimální hodnoty v souboru Výběrový rozptyl Výběrová směrodatná odchylka Matematické zaokrouhlení požadovaného čísla, číslice = počet desetinných míst) Zaokr.nahoru (číslo, významnost) Zaokrouhlení nahoru daného čísla, významnost = násobky, tj. 2 = násobky 2) Zaokr.dolu (číslo, významnost) Zaokrouhlení dolu daného čísla, významnost = násobky, tj. 2 = násobky 2) Log (číslo) Logaritmus čísla při zadaném základu Četnosti (oblast dat, hodnota) Matice absolutních četností, která roztřídí data na základě zadaných horních mezí intervalů. Četnosti (oblast dat, oblast hodnot) Počty (četnosti) z naměřeného souboru dat pro dané hodnoty (ctrl + shift + enter) Binomdist (počet úspěchů, pokusy celkem, Binomické rozdělení pravděpodobnosti pravděpodobnost, kumulace hodnot) s parametry n (pokusy celkem) a p (pravděpodobnost), kde pro kumulace = 1 jde o distribuční funkci a pro = 0 jde o pravděpodobnostní funkci Nyní () Aktuální datum a čas Dnes () Aktuální datum CONCATENATE(„text“ nebo F8; „text“ nebo Spojení více buněk (textu) do jedné F8)

2

KMA/ADZ, Tomáš Ťoupal 2015

Probrané dovednosti v software Excel 1. Hodina Tvorba studentského konta na KMA/ZČU, Seznámení se softwarem Excel, základní operace, výběr a tvorba funkcí (např. SUMA). Odkazy na text: 1) praktické cvičení ADZ, 2) nápověda Microsoft Excel (F1), 3) http://office.microsoft.com/cs-cz/excel-help/, 4) Pecinovský J., Pecinovský R.: Excel 2010 – podrobný průvodce, GRADA 2010. 5) Král M.: Excel 2010 – snadno a rychle, GRADA, 2010.                 

Popis uživatelského rozhraní Excel 2010 Nový soubor (běžný, ukázkové šablony, plán amortizace půjčky, …) Otevření souboru Uložení souboru, uložení jako Výběr oblasti buněk  matice, vektor Volba formátu buněk (včetně přednastavené nabídky) Kopírování hodnot buněk i vzorců Tvorba funkce (suma, součet, rozdíl, podíl…) Výběr vytvořených funkcí, jejich syntaxe a vkládání přes nabídku v Excelu, selekce funkcí (matematické, statistické, finanční, …) Vložit prázdný řádek, sloupec, buňku Vyjmout, vložit buňku Kopírování formátu vybrané oblasti buněk Přizpůsobení šířky řádků a sloupců Postupné načítání hodnot v buňkách (1, 2, 3, 4, 5, …) Ukotvení buněk (konkrétní buňky, sloupce, řádku) Vkládání klipartů (obrázků), martArtů a jejich úprava, vzhled, použití Zacyklení funkce (tvorba chyb, odkaz na „sama sebe“)

3

KMA/ADZ, Tomáš Ťoupal 2015

2. Hodina Seznámení se softwarem Excel, základní operace, vlastnosti funkcí, tvorba vlastních tabulek, formátování, úprava buněk, řazení, vkládání komentářů atd. Odkaz na text: cvičení ADZ, nápověda Microsoft Excel (F1) Odkazy na text: 1) praktické cvičení ADZ, 2) nápověda Microsoft Excel (F1), 3) http://office.microsoft.com/cs-cz/excel-help/, 4) Pecinovský J., Pecinovský R.: Excel 2010 – podrobný průvodce, GRADA 2010. 5) Král M.: Excel 2010 – snadno a rychle, GRADA, 2010. 6) Přednáška KMA/ADZ č. 1.         

        

Změna typu a velikosti písma, formátu (viz Word) Tvorba tabulek (vlastnoruční), zbarvení, vypnutí a zapnutí mřížky, kresba ohraničení, kopírování formátu tabulky Formát tabulek přednastavený (vlastnosti), pojmenování, přidávání sloupců a řádků, filtrování, operace s vytvořenou tabulkou Vytvořené funkce (přednastavené), syntaxe funkcí Vkládání již vytvořených funkcí přes Excel (funkce Suma), selekce funkcí (matematické, statistické, finanční, …) Použití nápovědy včetně uvedených příkladů Pojmenování buňky a použití ve vzorcích Slučování a zalamování buněk Podmíněný formát  Datové pruhy  Barevné škály  Sady ikon  Tvorba vlastních podmíněných formátů (pravidel např. označení čísel > 10) Vkládání a úprava komentářů Používání a tvorba vlastního stylu buněk nebo použití přednastaveného „Stylu buňky“ Konkrétní příklad z praxe – průměrné mzdy Plzeňský kraj Hledání textu či čísel na jednotlivých listech v buňkách (ctrl + F) Nalezení pořadové statistiky ve vybrané oblasti dat (pomocí příkazu RANK.EQ nalézt pořadí požadované hodnoty v seřazeném souboru) Parametry funkcí odkazem (RANK.EQ) Použití překladové slovníku v textech a buňkách (CZ -> ANG), použití slovníku synonym (Tezaurus) a kontrola pravopisu a gramatiky Tisk s mřížkou i bez mřížky Příkazy:  Rank.EQ  Počet  Průměr

4

KMA/ADZ, Tomáš Ťoupal 2015

3. Hodina Pokročilé funkce v software Excel, vkládání hodnot odkazy, charakteristiky polohy, operace s daty (řazení, …), procenta, atd. Odkazy na text: 1) praktické cvičení ADZ, 2) nápověda Microsoft Excel (F1), 3) http://office.microsoft.com/cs-cz/excel-help/, 4) Kožíšek J., Stieberová B.: Statistika v příkladech, 5) Pecinovský J., Pecinovský R.: Excel 2010 – podrobný průvodce, GRADA 2010. 6) Král M.: Excel 2010 – snadno a rychle, GRADA, 2010. 7) Přednáška KMA/ADZ č. 1. Stručný popis probrané látky, více viz přednáška „adz_1“: Popisná statistika – kvantitativní popis zkoumaných empirických jevů a procesů pomocí statistických charakteristik. Charakteristiky polohy – základní ukazatele, které charakterizují soubory statistických údajů a vyjadřují úroveň statistického souboru z hlediska velikosti sledovaného znaku. Jsou děleny podle toho, jestli se opírají o celý soubor statistických údajů (průměry) nebo jen část hodnot, případně jsou konstruovány zvláštním způsobem (modus, medián, kvantily). 

        

Odhady střední (očekávané) hodnoty  Aritmetický průměr (PRŮMĚR)  Medián (MEDIAN)  Modus (MODE) Součet obsahu více buněk (SUMA)  Součet s logickou podmínkou „>, <, =, …“ (SUMIF) Počet buněk s obsahem (POČET)  Počet s logickou podmínkou „>, <, =, …“ (COUNTIF) Řazení dat podle velikosti vzestupně (SMALL) Řazení dat podle velikosti sestupně (LARGE) Řazení dat z nabídky Excel, včetně použití „interaktivního“ filtru pro „rozevírací“ nabídku Nalezení, kolik % dat z výběrového souboru je menší než uvažovaná hodnota (PRECENTRANK.INC) Nalezení tzv. kvantilu (hodnotu při zadaném %, pro kterou je právě % hodnot menších, PERCENTIL.INC) Nalezení kvartilů (dolního a horního kvartilu, mediánu, příklad viz mzdy) Nalezení minimální a maximální hodnoty v souboru

4. Hodina Grafické zpracování dat – jedná se o velmi účinný způsob, jak prezentovat statistické údaje. Grafy nejsou tak přesné jako tabulky, ale rychle a lépe mohou poskytnout názornou představu o důležitých tendencích a souvislostech. Pomocí grafů můžeme například

5

KMA/ADZ, Tomáš Ťoupal 2015 odhadovat trendy a kolísání časových řad nebo několik časových řad vzájemně srovnávat. Nevhodné použití grafického vyjádření však může též svádět k chybným úvahám a interpretacím. Graf představuje přepsání číselných údajů do soustavy geometrických obrazců. Základní smysl číselných údajů interpretujeme pomocí souřadnic, stupnic a grafické sítě. Charakteristiky variability – základní ukazatele, které představují koncentraci (variabilitu) hodnot okolo odhadu střední hodnoty (průměru). Odkazy na text: 1) praktické cvičení ADZ, 2) nápověda Microsoft Excel (F1), 3) http://office.microsoft.com/cs-cz/excel-help/, 4) Kožíšek J., Stieberová B.: Statistika v příkladech, 5) Pecinovský J., Pecinovský R.: Excel 2010 – podrobný průvodce, GRADA 2010. 6) Král M.: Excel 2010 – snadno a rychle, GRADA, 2010. 7) Přednáška KMA/ADZ č. 1, 2.               

Databázové funkce (SVYHLEDAT, VVYHLEDAT) Tvorba bodového grafu, včetně přesunu na samostatný list Tvorba spojnicového grafu Tvorba výsečového grafu Tvorba sloupcového a dvou-osového grafu Tvorba bublinového grafu + chybové úsečky Tvorba a úprava nadpisu, legendy, popis os (včetně odkazů na buňky) Přidávání a odebírání řady v grafu Popis osy 𝑥 odkazem (např. vykreslené roky) Nastavení os (automatické, pevné měřítko, volba jednotek např. tisíce) Grafické úpravy v grafech (pozadí, barvy, písmo, …) Vykreslení a matematický popis trendové křivky (𝑦 = 𝑎𝑥 + 𝑏) Změna typu již vytvořeného grafu Tvorba „minigrafů“ Výpočet výběrového rozptylu a směrodatné odchylky (více viz 1. Přednáška) Odhad střední hodnoty (statistické míry polohy): 𝑛 1 𝐸(𝑋) = 𝑥̅ = ∑ 𝑥𝑖 . 𝑛 𝑖=1

Odhad rozptylu (statistické míry variability):

𝑛

1 𝐷(𝑋) = 𝑠 = ∑(𝑥𝑖 − 𝑥̅ )2 . 𝑛−1 2

𝑖=1

Odhad směrodatné odchylky (statistické míry variability):

6

KMA/ADZ, Tomáš Ťoupal 2015 𝑛

𝑠=

√𝑠 2

1 =√ ∑(𝑥𝑖 − 𝑥̅ )2 . 𝑛−1 𝑖=1

5. Hodina Histogram (sloupcový diagram) – po „základní“ analýze dat, lze vytvořit tzv. histogram, který slouží k lepší představě o vybraném statistickém souboru, resp. jde o možný náhled na statistické chování (možného modelu). Zde je nutné jednotlivé statistické údaje roztřídit do vhodných intervalů a vypočítat příslušné statistické charakteristiky, tj. histogram je sloupcový graf se sloupci stejné šířky, jejichž výška je určena počtem vyskytujících se hodnot v každém sloupci. Odkazy na text: 1) praktické cvičení ADZ, 2) nápověda Microsoft Excel (F1), 3) http://office.microsoft.com/cs-cz/excel-help/, 4) Kožíšek J., Stieberová B.: Statistika v příkladech, 5) Pecinovský J., Pecinovský R.: Excel 2010 – podrobný průvodce, GRADA 2010. 6) Král M.: Excel 2010 – snadno a rychle, GRADA, 2010. 7) Přednáška KMA/ADZ č. 1, 2. 

   

   

Nástroj „Analýza dat“  Pořadová statistika  Popisná statistika Analýza náhodného výběru dat (počet dat, maximální hodnota, minimální hodnota, variační rozpětí naměřených hodnot, …) Zaokrouhlování hodnot v buňkách (zaokrouhlit nahoru, zaokrouhlit dolu, matematické zaokrouhlování) Možnosti vnořování funkcí v Excelu např. Zaokr.nahoru (1+3,3*log(n)), funkce logaritmus Tvorba histogramu – absolutní a kumulativní četnosti, relativní četnosti a kumulativní relativní četnosti  Určení počtu tříd – pro stanovení počtu skupin (intervalů) se používají některá pravidla např. modifikované Sturgesovo pravidlo  Určení šířky třídy – vychází ze znalosti variačního rozpětí a přibližného počtu intervalů  Výpočet četnosti naměřených hodnot v jednotlivých intervalech  Vykreslení histogramu pomocí sloupcového grafu  Absolutní i relativní četnosti (v %, počet ve třídě / počet dat celkem) Použití maticových vzorců (ctrl + shif + enter) např. ČETNOSTI Tvorba histogramu pomocí maticového vzorce Spojování textu v buňkách CONCATENATE(„text“ nebo F8; „text“ nebo F8) Interpretace získaných hodnot (pravděpodobností) v histogramu.

7

KMA/ADZ, Tomáš Ťoupal 2015

6. Hodina Vícerozměrná data - kontingenční tabulky. Při zpracování vícerozměrných dat, hledáme souvislosti mezi dvěma, případně více náhodnými veličinami. Pro různé typy dat je třeba používat různé matematické postupy vhodné pro zjišťování souvislostí a závislostí. Kontingenční tabulka se užívá k přehledné vizualizaci vzájemného vztahu dvou statistických znaků. V praxi vzniká kontingenční tabulka tak, že se na statistických jednotkách sledují dva znaky. Řádky kontingenční tabulky odpovídají možným hodnotám prvního znaku, sloupce pak možným hodnotám druhého znaku. V příslušné buňce kontingenční tabulky je pak zařazen počet případů, kdy zároveň měl první znak hodnotu odpovídající příslušnému řádku a druhý znak hodnotu odpovídající příslušnému sloupci. Je možné, aby jeden řádek či sloupec odpovídal více možným hodnotám znaku. To se děje v případě, kdy znak nabývá některých hodnot příliš zřídka, takže je vhodné spojit více možných hodnot. Součty (mezisoučty) všech hodnot v každém řádku, resp. sloupci nesou informaci o počtu výskytů jevů, při nichž nabyl první (resp. druhý znak) příslušné hodnoty bez ohledu na hodnotu druhého (resp. prvního) znaku. Kromě prostého popisu četností kombinací hodnot dvou znaků nabízí kontingenční tabulka možnost testovat, zda mezi oběma znaky existuje nějaký vztah. Náhodný pokus – je každý proces, jehož výsledek je při jinak stejných počátečních podmínkách nejistý; výsledek nejsme schopni s jistotou předpovědět; množinu všech možných výsledků náhodného pokusu označujeme Ω . Náhodný jev – je jev 𝐴, který je podmnožinou množiny Ω (𝐴 ⊂ Ω); náhodné jevy značíme velkými latinskými písmeny z počátku abecedy {𝐴, 𝐵, 𝐶, … }; celá množina Ω je jev jistý; prázdná množina ∅ je jev nemožný. Elementární jevy - jsou minimální jevy různé od jevu nemožného a jsou párově neslučitelné. Každý jev 𝐴 lze vyjádřit množinou elementárních jevů. Pravděpodobnost jevu – každému jevu 𝐴 přiřazujeme reálné číslo 𝑃(𝐴); pravděpodobnost (ppst) lze chápat jako předpověď poměrných četností výsledku při mnohonásobném opakování daného pokusu; ppst lze chápat jako kvantitativní ohodnocení stupně jistoty. Existují různé možnosti matematického zavedení pravděpodobnosti - klasická ppst, geometrická ppst, statistická ppst a axiomatická ppst. Klasická definice pravděpodobnosti 𝑃(𝐴) =

𝑁(𝐴) , 𝑁

kde 𝑁(𝐴) je počet výsledků příznivých jevu 𝐴 a 𝑁 je počet všech možných výsledků. Odkazy na text: 1) praktické cvičení ADZ, 2) nápověda Microsoft Excel (F1), 3) http://office.microsoft.com/cs-cz/excel-help/,

8

KMA/ADZ, Tomáš Ťoupal 2015 4) 5) 6) 7)  





 



Kožíšek J., Stieberová B.: Statistika v příkladech, Pecinovský J., Pecinovský R.: Excel 2010 – podrobný průvodce, GRADA 2010. Král M.: Excel 2010 – snadno a rychle, GRADA, 2010. Přednáška KMA/ADZ č. 1, 2, 3, 5.

Kopírování a vkládání grafů do software Word (typy vkládání – sešit, propojení, obrázek) Analýza dat v Excelu  Instalace: Soubor – Možnosti – Doplňky – Spravovat doplňky aplikace Excel (tlačítko „Přejít“) – Analytické nástroje  Použití: Data – Analýza dat Analýza dat  tvorba histogramu  popisné statistiky  pořadová statistika a percentily Kontingenční tabulky (viz ADZ_05)  Countifs  Vložit tabulku Kontingenční graf – tvorba grafu, úprava, … Seznámení s teorií Pravděpodobnosti (viz ADZ_03)  Náhodný pokus  Náhodný jev  Náhodná veličina 𝑁(𝐴)  Základní definice pravděpodobnosti 𝑃(𝐴) = 𝑁  Náhodné veličiny (diskrétní vs. spojitá) Příklad:  Hod kostkou – diskrétní náhodná veličina, rovnoměrné rozdělení pravděpodobnosti

7. Hodina Odkaz na text: cvičení ADZ, nápověda Microsoft Excel (F1), ADZ_03, ADZ_04      

Spojitá a diskrétní náhodná veličina Pravděpodobnostní funkce 𝑷(𝒙) = 𝑷(𝑿 = 𝒙) Distribuční funkce 𝑭(𝒙) = 𝑷(𝑿 ≤ 𝒙) Důraz na pravděpodobnostní a distribuční funkci (příklad hod kostkou) Modelování změny modelu při změně parametrů Operace s grafy (kopírování, změna dat, vkládání,…)

Odkazy:  http://www.kma.zcu.cz/main.php?KMAfile=./CLENOVE/main.php&DRC=./STRUCTURE/0 2_pracovnici/002_foto/&DRL=CZ&DROF=0&nick=ToTou&kam=vyuka.php

9

KMA/ADZ, Tomáš Ťoupal 2015 

http://home.zcu.cz/~friesl/hpsb/tit.html

10