Plody Excelu

Pro tuto chvíli proto není předmětem zájmu tvorba grafů "pro oko". Jak můžou grafy vypadat z hlediska barevnosti, uspořádání nebo třeba velikostí písma, ukazují samotné příklady a je na čtenáři, aby s ukázkami experimentoval. Na webových stránkách (a možná i někdy na tomto místě) je námět designu v Excelu samostatně zpracován. Zde jen stručně a ryze prakticky:

1. Není jednodušší cesty vytvoření grafu, než vybrat na listu oblast buněk s daty (případně i s popisky) a stisknout klávesu F11. Excel vytvoří samostatný list s výchozím grafem (sloupcový). Pravým tlačítkem myši pak s využitím příručního menu jednoduše změníte typ grafu a umístění. Editovat graf můžete s pomocí panelu nástrojů Graf (zobrazuje se při vybraném grafu, najdete jej ale také pod menu Zobrazit / Panely nástrojů / Graf. Menu se navíc zobrazí i v hlavním panelu, kde nahradí (při vybraném grafu) menu Data. Tento způsob je úžasně rychlý a nemusí se za něj stydět ani začátečník, ani profesionál. Všímejte si také popisků, které se objeví po najetí kurzoru na jednotlivé elementy grafu.
2. Druhou možností je využití průvodce tvorbou grafu. Ten se skrývá pod ikonou v panelu nástrojů. Jeho možnosti a volby raději nastudujte z druhého konce, totiž pod příručním menu v již existujícím grafu z přiloženého sešitu vyberte Zdrojová data.

Věřím, že už znáte alespoň ty základní typy grafů, které Excel nabízí. Kdy jaký použít? Pokusím se alespoň nastínit některá použití:

• Sloupcové a pruhové grafy jsou nejčastější a slouží k porovnávání hodnot, například produkce divizí společnosti. Zobrazen může být navíc u každé divize i podíl produktů na celé její výrobní produkci (podtyp skládaný graf). Často vzniká potřeba ještě srovnání v čase (růst, pokles). Tehdy se uplatní podtyp 3D sloupcový. Grafy Válcový, Kuželový, Jehlanový jsou pouze vizuální odnoží uvedených typů a nepřikládám jim velký význam. 3D efektu válcovitosti v prvním příkladu dosáhnete volbou grafu Formát datové řady / karta Vzorky, tlačítko Vzhled výplně a nastavením barev dle ukázky.

• XY bodový graf (míněn první podtyp, s nespojenými body) slouží především jako podklad pro vyhodnocování závislosti proměnné Y na X. Body až následně prokládáme námi definovanou křivkou (tzv. "spojnice trendu", nejčastěji lineární a exponenciální). Osobně tomuto grafu dávám přednost před jakýmkoliv typem spojnicového grafu a považuji jej za výchozí pro zpracování experimentálních dat, kdy je znám teoretický tvar funkce Y=f(X). Ukázka znázorňuje realizaci dvou závislostí v jednom grafu a zobrazením tzv. "vedlejší osy y" (zde dráha). Dodávám, že za určitých podmínek typy grafů mohou být pro každou řadu rozdílné! O spojnicích trendu více v části věnované regresi.

• Výsečový a prstencový graf je volbou pro situace, kdy zdrojová data vyhodnocujeme jako "podíl na celku". Výsečový graf také zdomácněl díky prezentaci "koláčových" grafů sledovanosti televizního vysílání. I zde je možné graficky znázornit "podíly podílu" použitím podtypu s dílčí výsečí, resp. dílčími pruhy. Prstencový graf může obsahovat i více datových řad. Pozn. Procentuální vyjádření podílu na celku není nutné vypočítávat v listu. Stačí zatrhnout patřičnou volbu pro popisky dat, jak ukazuje obrázek.

• Paprskový graf (hovorově pavučina, radar) je navržen pro zobrazení odchylek dat od středu. Je jakousi vizuální podobou vyváženosti dat (v příkladu uvedeném níže jde o kruhovitost). Počet paprsků je roven počtu datových bodů a hodnoty jsou nanášeny od středu směrem ven. Otázka ke cvičení: Lze s pomocí paprskového grafu vyhodnocovat střelbu na terč?

• Bublinový graf je obdobou bodového grafu s tím, že datový bod smí obsahovat další parametr, jenž je prezentován velikostí kruhu. Lze takto v kartézském souřadném systému zobrazovat například polohy planet. Pozn. Zde je ovšem jiný problém - rozdílnost velikosti (průměru) planet, a to v celých řádech. Zobrazit v jednom grafu Pluto a Jupiter může být problém...

• Povrchový graf zobrazuje data jako topologickou mapu povrchu, kdy dvěma souřadnicím je přiřazena výška. Výškové body pak vytváří vrstvu, která je zobrazena jednou barvou pro stejný rozsah hodnot. Počet vrstev je dán hlavní jednotkou na svislé ose. Podtyp obrysový pak vykresluje pohled shora, kdy se v podstatě jedná o vykreslení vrstevnic. Charakter grafu je předurčen pro zobrazení zavislosti veličin z oblasti nauky o materiálu (zkoušky pevnosti, závislosti teplot, deformací, časy tuhnutí sloučenin apod.) Bohužel, jediný způsob, jak měnit barvu vrstev, je zasáhnout do barevné palety v menu Nástroje / Možnosti / karta Barva. Pozn. Pro zvýšení čitelnosti tohoto typu grafu vřele doporučuji snížit optickou výšku grafu (volba Trojrozměrný z příručního menu a dialog 3D pohled) - viz obrázek.

• Spojnicové a burzovní grafy mají svým charakterem asi neblíže k pojmu statistika a často se v tomto směru také uplatňují. Vrátíme se k nim ovšem někdy jindy, neboť v dnešním povídání si užijeme samotné statistiky v podobě čísel až dost.

• Každá změna dat v listu se automaticky promítne i do grafu. Přidání datové řady musíte doplnit v dialogu Zdrojová data, karta Řada, tlačítko Přidat nebo graf vytvořit znovu. Pomocí vzorců listu a VBA lze dosáhnout velmi zajímavých efektů a dokonce animačních prvků, které grafům přidávají na dynamičnosti. To však není předmětem dnešního pojednání.
• Datovou řadu v grafu odstraňujte jejím výběrem a stiskem klávesy Del, nezbavujte se jí v tabulce listu. Je to elegantnější a vyhnete se chybovému hlášení Excelu o neplatném odkazu.
• Výchozím grafem je sloupcový. Změnit jej můžete kdykoliv při zobrazeném dialogu Typ grafu, výběrem nového typu a klepnutím na tlačítko Nastavit jako výchozí.
• Ja uložit svůj vlastní graf? V dialogu Typ grafu přejděte na kartu Vlastní typy a zaškrtněte volbu Definované uživatelem. Až poté se objeví tlačítko Přidat... (Zmatenou logiku Vlastní typy - Integrované / Definované uživatelem raději ponechám nekomentovanou.)
• Velmi často se vám prvek grafu povede myší vybrat až na několikátý pokus a to opakovaným klikáním. V tom případě nejprve najetím kurzoru nad element zkontrolujte jeho název ze zobrazeného popisku. Dále vyhledejte název v rolovacím seznamu panelu nástrojů Graf a klepněte na vedlejší ikonku pro vlastnosti elementu. Ne všechny části grafu jsou zde ale uvedeny. K těm nejmenším elementům, např. klíčům legendy (značky v okénku legendy), se budete muset trpělivě proklikat.
• Pozor na možné vizuální zkreslení sklonu křivky téhož grafu (růst, pokles). Zobrazení je ovlivněno číselným rozsahem na osách, které navíc může být logaritmicky odstupňováno. Více viz nastavení Formát osy / karta Měřítko - viz obrázek. Je nanejvýš důležité také vědět, zda-li křivka grafu prochází počátkem soustavy souřadnic.

• U názvu grafu lze s výhodou použít jeden trik. Text může být dynamicky propojen na obsah buňky v listu. Pokud název grafu nemáte zobrazen, zvolte z příručního menu Možnosti grafu a na kartě Názvy vložte libovolný text. Následně vyberte v grafu oblast s názvem, klepněte do řádků vzorců a vepište znak "=". Myškou poté klepněte na zdrojovou buňku.
  
• U mřížek grafu doporučuji volit přerušovanou čáru o barvě mírně odlišné od pozadí pod mřížkou. Zpřehlední se tak oblast grafu.
• I u grafu můžete nastavit zámek. Nabídka se skrývá v příručním menu Formát oblasti grafu / karta Vlastnosti. Tato volba se zobrazí, pokud je kurzor myši poblíž vnějších okrajů vybraného grafu (v souladu s názvoslovím bych měl mluvit o vybrané Oblasti grafu).
• Pro výběr barev a velikost písma nemusíte vždy "chodit" dovnitř dialogů grafu, používejte ikony panelu nástrojů Formát.
• Další trik pro barevnost: Jistě jste si již všimli, že pro graf se používá rozšířená paleta barev (další 2 řady na spodní části nabídek). Pokud tyto barvy budete chtít použít i kupříkladu pro tabulku s grafem spojenou, změňte barvu pro libovolný element grafu prostřednictvím ikon výplně a písma na panelu nástrojů Formát a aplikujte barvu z rozšířené palety. Bezprostředně poté přejděte do tabulky, vyberte buňku a znovu klepněte na střed ikonky (ne na šipku rozevíracího seznamu). Použije se naposled aplikovaná barva, jakoby z paměti...
• Jako příklad elegance se uvádí texturování povrchu objektů grafu kliparty (Formát datové řady / Vzhled výplně / karta Obrázek). Podle mého úsudku ale kliparty často znepřehlední graf, navíc, přizpůsobují se velikosti objektů a tím degradují. A to nemluvím o tom, že převážná část dostupných klipartů je poněkud "crazy". Stručně - nepřehánějte to.

Excel umí v grafech naložit s prázdnými buňkami - viz Nástroje / Možnosti / karta Graf při vybraném(!) grafu. Čekám, že někoho z vývojářů v Microsoftu napadne zařadit možnost "Vynechat bez mezer"... Zakopaný pes je ale někde jinde a netýka se jen grafů. Jak již víte, buňka obsahující prázdný znak ("") není považována za prázdnou. Nelze ale ani říci, že ji Excel jednoznačně bere jako nulovou (viz funkce JE.PRAZDNE, JE.TEXT, COUNTBLANK).

Přístup I.

1. Nulové výsledky vzorce neovlivní další výpočet (statistiku především).
2. Vzorec nevrací chybovou hodnotu.

Excel pak pouze přidá místo na ose x, což je jediná nevýhoda zmíněné volby Nástroje / Možnosti / karta Graf / nekreslit (nechat mezery).

Přístup II.

Prázdné buňky ošetříme například ve smyslu =KDYŽ(A2="";""; ...). Dále použijeme dynamicky pojmenované oblasti coby zdrojová data pro graf. Zdroj pro graf se mění buď v dialogu grafu, nebo přímo při vybrané datové řadě grafu a v řádku vzorců (druhý a třetí parametr funkce SADA).

Více osvětlí situaci obrázek a především přiložený sešit grafy_nuly.xls.

Poznámka: V Excelu pravděpodobně není cesty, jak primitivně vytvořit graf funkce y = konstanta. Pro každou hodnotu x musíme opětovně psát i onu konstantu.

Předpokládejme, že máme za úkol vyhodnotit sadu dat, o které nám někdo řekl, že je náhodným výběrem hodnot z jediného určitého měření - nic víc, nic míň. K uspokojení sebe samých i zadavatele úlohy obyčejně stačí málo - říci, že hodnoty jsou (čtenář promine) "takové či makové plus minus trolejbus". Ono plus minus z pohledu statistiky znamená stanovit rozsah hodnot, ve kterém se všechny hodnoty budou nacházet, a to s určitou pravděpodobností.

V daném případě nemusíme hloubat nad tím, jak data zprůměrovat - poslouží nám funkce PRŮMĚR pro aritmetický průměr. Hravě kupříkladu také dokážeme s pomocí funkcí MAX a MIN určit maximální a minimální hodnotu. Funkce MODE vrací nejčastější hodnotu (při absenci duplicitních hodnot vrací chybovou hodnotu #N/A). Ke stanovení tolerance, jež vymezí rozsah hodnot, slouží v Excelu funkce CONFIDENCE(alfa; směrodatná odchylka; počet). Hladina významnosti alfa (nejčastěji 0,05) znamená, že si přejeme získat toleranci vymezující takový interval, do něhož obecně všechny hodnoty budou spadat s pravděpodobností 100*(1-alfa)%. Směrodatná odchylka (v daném případě se jedná o výběr hodnot) se vypočte funkcí SMODCH.VÝBĚR. Pro kontrolu celkového počtu hodnot měření uplatníme funkci POČET. X-tou nejmenší a největší hodnotu obstarávají funkce LARGE a SMALL a zbytek můžeme zajistit již známými postupy pro podmíněný počet a součet.

To podstatné...

• Výsledkem předchozího příkladu je odpověď: S pravděpodobností 95% bude většina hodnot ležet v intervalu (PRŮMĚR - CONFIDENCE; PRŮMĚR + CONFIDENCE).
• U většiny zpracovávaných hodnot používáme výběrovou směrodatnou odchylku a výběrový rozptyl (zmíněná SMODCH.VÝBĚR a funkce pro výběrový rozptyl VAR.VÝBĚR).
• Místo funkce CONFIDENCE je lépe stavět na metodách respektujících počet zpracovávaných hodnot a pravidel pro hledání intervalů spolehlivosti - viz přiložený sešit.

Poznámka: Neberte prostý průměr jako samozřejmost. Je důležité znát alespoň význam váženého průměru (typické příklady průměrování dat ze skupin "hrušky a švestky", kdy je třeba zohlednit charakter "hrušek a švestek") a geometrického průměru (používaný např. ve finančnictví).

Ukázka výše se týká Popisné statistiky, což je téma, kterým doporučuji začít. Volba Popisné statistiky plně nahrazuje "ručně" získané hodnoty popsané v prvních krůčcích. Ovšem pozor! Výsledkem nejen této, ale všech nabídek Analýzy dat je tabulka statických hodnot, které se nemění při změně zpracovávaných hodnot! A co víc, při omylu a přepisu dat není funkční notoricky známé "Undo", nebo-li krok zpět. Domnívám se, že v tomto směru Excel (vyzkoušen 2000 a XP) zůstává v době kamenné.

Stručně k dalším volbám:

• ANOVA je analýzou rozptylu dvou soborů dat a potencionálního vlivu určitého faktoru
• Korelace, kovariance analyzuje vazbu lineárního charakteru mezi dvěma soubory dat.
• Exponencionální vyrovnání je metoda k minimalizování potencionálních chyb měření.
• Dvouvýběrový ... test ... (párový, F-test, z-test, pro střední hodnotu, rozptyl) vychází z teorie a testování hypotéz. Nejpoužívanější jsou všechny druhy t-testů.
• Histogram slouží k roztřídění dat do tříd (třída představuje rozsah hodnot; četnost je pak počet hodnot ze sledovaného souboru, které do tohoto rozsahu spadají). Graf (Excel vyplivne bez prominutí paskvil) raději nepoužívejte (u hranic sice docílíte doléhnutí tříd volbou Formát datové řady / Možnosti / Šířka mezery: 0, popisky tříd ovšem nikdy nebudou na hranicích, u volby kumulativních četnosti se nejedná o používaný polygon četností atd.). Jak tedy zpracovat histogram? Mohu jen vypálit od boku: Zkusit postavit graf na středu tříd, aplikovat spojnicový graf, zobrazit vynášecí čáry... (V době, kdy čtete tyto řádky, bych již měl vědět více a doplněk tohoto článku snad již visí na internetu). Funkce listu ČETNOSTI slouží pro sestavení podkladů histogramu.
• Klouzavý průměr podobně jako Exponencionální vyrovnání je metodou vyhlazující data.
• Generátor pseudonáhodných čísel je tím, nástrojem, pomocí něhož vygenerujete "ta správná" data ze zvoleného rozdělení.
• Regrese je určena pro analýzu budoucích stavů na základě stávajících údajů a metod regresní analýzy.
• Vzorkování slouží k vybrání náhodného vzorku dat z objemných databází.

Jsem si vědom snad až přílišné stručnosti v prostém vyjmenování metod. Není ovšem možné rozebrat kupříkladu t-testy. Bylo by nutné mluvit o vlastním Studentově rozdělení, říci, co se myslí rozdělením, mluvit o hypotézách, o jednostranných a oboustranných testech, o kvantilech, stupních volnosti, kritickém oboru, testovacích charakteristikách atd. A ani se nemohu tvářit, že tomu všemu rozumím na základě jednoho semestru pravděpodobnosti a statistiky :-). Bez výkladu teorie není dobré ani zmiňovat použití jednotlivých testů. V přiloženém sešitu je ovšem najdete. Tento článek také chápejte víceméně jako nastartování debaty a jistě se někdy dočká pokračování. Ano, objeví se v něm i grafy určené výhradně pro statistiku (krabicové a další).

To podstatné...

• To hlavní se nachází v knihách :-( viz literatura na konci.
• Běžné statistické postupy najdete na cca 10 listech přiloženého sešitu, proto experimentujte, zkoušejte, učte se a opravujte mě...
• Uživatelská přívětivost statistického doplňku pod Excelem je mizerná. A český překlad? Nápověda je hrozná, pojmy jsou často v hrubém nesouladu s teorií ("překladatelský oříšek"?), výsledky testů aby člověk luštil a rozeznat, zda-li funkce TINV vrací hodnotu jednostranného či dvoustranného testu, je pro laika záležitostí pokus-omyl na celý den.
• Jak je to s přesností a správností? Hodnocením a porovnáním softwaru se zabývá mimo jiné Česká statistická společnost a Excel na tom není nejlépe (za určitých podmínek selhává už při výpočtu směrodatné odchylky a rozptylu). Ostatní programy mají ale rovněž své mouchy. Závěr je jednoduchý. Důvěřujte, ale prověřujte.
• V souvislostí s předchozím povzdechnutím - pokud máte zájem o práci v oboru statistiky, podívejte se po programech určených pro statistiku. Do budoucna chystám na webových stránkách zveřejnit přehled dostupných alternativ k Excelu. Existují i doplňky napravující Excelu reputaci. Jako tip předkládám software UNISTAT (www.unistat.cz, www.unistat.com, lokalizovaný do češtiny) či program ALSTAT (www.alstat.com). V českých vodách pak začněte u samostatných produktů společnosti TriloByte (www.trilobyte.cz). Jako rozcestník může posloužit i stránka badame.vse.cz/clanky/statsoft.php

Tímto bych se rád vrátil ke krátce zmíněným spojnicím trendu u grafů typu XY. Ty jsou totiž vytvářeny právě na základě regresní analýzy.

Spojnice trendu se vytváří při vybrané řadě datových bodů s využitím nabídky Přidat spojnici trendu v příručním menu. Na kartě Typ volíme druh regrese a na kartě Možnosti pak definujeme možnosti odhadu dalších hodnot směrem vpřed i vzad. Zpravidla stojíme o zápis rovnice proložené křivky a proto si zde zaškrtneme políčko Zobrazit rovnici regrese. Pozn. Hodnota spolehlivosti R je ve statistice nazývána koeficientem determinace.

K hodnotám regrese a předpovědi hodnot můžeme přistoupit i funkcemi přímo z listu. Odhady budoucích hodnot lineární regrese vrací funkce LINTREND a FORECAST, pro zjištění koeficientů regresní přímky y=ax+b aplikujte funkce LINREGRESE a SLOPE, resp. INTERCEPT. U exponenciální regrese se užívají funkce LOGLINTREND a LOGLINREGRESE.

Tato tématika v sobě obsahuje odpovědi na otázky typu "co by, kdyby", totiž sledujeme změnu finálního výsledku na základě změn proměnné či proměnných. První z podtémat se jmenuje Citlivostní analýza a její funkci najdete pod menu Data / Tabulka. Vyplněný dialog dvouvstupové citlivostní analýzy je uveden níže. Buňka F9 přitom obsahuje vzorec =G4+G5*$G$3.

Narozdíl od Analýzy dat tato metoda vrací matici funkce TABELOVAT, jejíž obsah (výsledky závislosti proměnných podle předpisu v buňce F9) se přizpůsobuje změně vstupů.

Druhou možností je použít Nástroje / Správce scénářů. Zjednodušeně řečeno, jedná se o zásobník, do něhož se ukládají sady hodnot, pro které je možné zobrazit přehled s výsledky výpočtu (tlačítko Zpráva). Nové scénáře se přidávají tlačítkem Přidat, následným výběrem měněných buněk a sadou hodnot pro daný scénář.

Upřímně řečeno, nespatřuji ani ve funkcích citlivostní analýzy, ani ve Správci scénářů vyjímečné schopnosti.

Méně výkonná grafická, ale použitelná je i metoda Nástroje / Hledat řešení. Vyzkoušejte ji.