Eida.cz - Excel a histogramy

Tohle je historicky první - doslova pilotní - článek z experimentální série, co konečně nebude úplně na úrovni :). Kdo bude chvíli fikaně googlovat, začne zcela jistě a s přibývajícím časem čím dál více nacházet v diskusních fórech zvěsti, že výuka Microsoft Office je epická ztráta času. A je to pravda, bohužel se toho z nějakého důvodu nedá zbavit, spoustu lidí si v tom vede důležité věci a někteří manažeři si na tom pak honí ego do nevídaných rozměrů. Rozhodně tak nechci skončit, jen tu občas nakousnu pár zdánlivě neřešitelných a především úplně nesmyslných úkolů, se kterými se může kdejaký nebohý student setkat.

Dopoledne se ozvala divoká Romanga a s rudým smajlíkem nakousla v pořadí už druhého excelovského bubáka, se kterým se měl utkat její drahý. A to nakreslit v Excelu pomocí grafu histogram a aby nebyly ma značkách středy tříd. Excel umí určitě spoustu věcí a pokud tohle bylo zadání, určitě to půjde udělat - ovšem co si vzpomínám z nedávné statistiky, náš velitel nám vykládal, že k tomu raději používejte statistické softvéry, vono to v Excelu jde strašně blbě a prostě… nedělejte to v něm.

Co je histogram a proč

Nejdřív trochu nutné teorie, jak jinak. Histogram je na první pohled sice sloupcový graf, ale má ve skutečnosti za úkol pomocí svých stejně širokých sloupců zachytit četnost všech prvků z určitého intervalu, který každý samostatný sloupec představuje, přičemž bývá celá distribuční funkce ve skutečnosti spojitá. Z toho už logicky vyplývá, že jednotlivé sloupce jsou neodděleně nalepené na sebe a svislá osa představuje celočíselnou četnost sledovaných prvků. Nejzajímavější je tady osa vodorovná, znázorňující na jednotlivé intervaly rozsekanou osu reálných čísel.

Nebudu tady teď popisovat, jak se z posbíraných dat pomocí analytických nástrojů a kdovíčeho získá samotné intervalové rozdělení. Ale když už ho máme, existují zhruba tři způsoby, jak data následně graficky reprezentovat. Tím nejjednodušším z nich, ktré obsáhne běžný sloupcový graf, je použití středových bodů intervalů, tzv. midpoints. Středový bod vznikne zprůměrováním dolní a horní hranice intervalu, např. pro interval 10..20 bude středový bod (10+20)/2=15. Sloupeček bude mít šířku 20-10=10, distribuční funkce bude procházet středem jeho vrcholu a popisek na vodorovné ose bude rovněž zarovnán na jeho střed. Druhou možností je vypsání samotných intervalů, třeba použitím pomlčky, doslova 10 - 20, 20 - 30, … Tady už bude potřeba nejspíš vytvořit nový sloupeček v tabulce dat, kde budou jednotlivé buňky ve formátu textu a ne čísla. Histogram pak opět obslouží obyčejný sloupcový graf, kde se pro hodnoty vodorovné osy použije výše zmíněné textové označení. Posledním způsobem je vyznačení samotných intervalů pomocí jejich dolních a horních mezí na samotných hraných sloupců. A to je v Excelu už pain-in-the-ass, co nejde jednoduše obsloužit prostým sloupcovým grafem, který nepracuje se spojitými veličinami.

Řešením je tedy, jako u všeho, použít jako neviditelnou pomůcku takový typ grafu, který se spojitými hodnotami pracovat umí, který pro ně poskytne osu vhodnou i pro reprezentaci popisků hran sloupců, které samy o sobě ponesou ryze diskrétní veličiny - třídy, navzájem oddělené intervaly.

Postup v krocích

Abys peníze vydělal, musíš je utratit. Ze všeho nejdřív je potřeba mít data, se kterými se bude pracovat. Jako ukázku jsem převzal část té Romangou zaslané tabulky, ať už konkrétně zachycuje cokoliv. Sloupec x_d zachycuje všechny dolní meze intervalů, sloupec x_h naopak všechny meze horní. Sloupeček x_i pak nese všechny středové body, zísakné zprůměrováním odpovídajících hodnot x_d a x_h. Zeleně označené n_i je samotná četnost sledovaných intervalů veličiny - jednotlivě pro každý i-tý interval. Celková četnost značená sumičkou nám vychází 82.

Začneme chrlit tím, že stvoříme nejlépe úplně prázdný 2D sloupcový graf. V těch nových Excelech 2007 a vyšších to bude někde nahoře v ribbon záložce Grafy, nebo přinejhorším Vložit - Graf. Zkrátka kdo chce dělat histogram, by měl asi zhruba tušit, jak se vlastně vkládá graf.

Kdo do čerstvě stvořené oblasti klikne pravým myšítkem, měl by vidět položku pro výběr datových řad, něco jako Select data. Přinejhorším to zas bude někde nahoře v kontextové grafové ribbon záložce. Vytvoříme novou datovou řadu. Můžeme si ji pojmenovat třeba Histogram. Její název bude zároveň i samotným názvem grafu.

Jako hodnoty pro svislou osu Y vybereme data ze sloupce četnosti, zeleného n_i.

Hodnotami X-ové osy budou pro tyto samotné sloupce středové body - vybereme tedy sloupec s hodnotami x_i. Vše potvrdíme OK.

V tuto chvíli stačí na sloupečky poklepat a hned v první možnosti formátování zrušíme šířku mezery, neboť intervaly mezi sebou nemají žádné mezery. Ve formátu čáry vybereme barvu čáry. V tomto okamžiku je hotový histogram prvního popsaného typu.

Teď nastane teprve ta správná magie. Klikneme do oblasti grafu a opět přes výběr dat vytvoříme úplně novou datovou řadu. Bude tak trochu výjimečná tím, že její jediná y-ová hodnota bude konstantní, zvolíme Y třeba ={10} (jakékoliv číslo v hodnotách jednotlivých četností n_i - jen aby to bylo v ten okamžik dobře vidět). Pro hodnoty X vybereme jako zdrojová data všechny hodnoty z horních i dolních mezí.

V tuhle chvíli začne graf už konečně trochu divočit, vodorovnou osu zobrazí v odřádkovaných otočených intervalech a druhou datovou řadu zobrazí jako špičku s fixní hodnotou 10.

Špička měla hodnotu takovou, aby šla myší v pohodě a bez ochechulí označit jednoduchým kliknutím. V kontextovém ribbon meníčku se tím výběr změnil na možnost změnit typ grafu pro tuhle danou datovou řadu. S úsměvem a úplně bez váhání vybereme libovolný XY-bodový, třeba hladkou čáru.

A hle, graf najednou dostal další osy, nahoře a vpravo.

Dvakrát poklepeme na horní osu, prootže ta se za malý okamžik stane klíčovou. Nastavíme jí minimum a maximum z nejnižší dolní hodnoty a nejvyšší horní hodnoty. Jako krok zvolíme šířku intervalu, která je v tomto příkladě 2,5. Důležité je sdělit, že graf teď svislou osu protíná v nejnižší dolní hodnotě.

Nová svislá osa, co byla původně vpravo, se přesune úplně doleva. Klikneme na ni a nemilosrdně smažeme a odešleme do pekla pomocí delete.

Jako další poklepe na nyní-spodní-vodorovnou-osu s dvojicí čísel. Je funkčně důležitá, tak jen necháme skrýt její popisky.

Zbývá provést poslední obdobnou operaci s nyní-horní-vodorovnou-osou. Její popisky dáme dolu a v úpravě její čáry zvolíme žádnou - ať nahoře zbytečně není žádná linka s protínátkama.

A tím je dílo zkázy dokončeno. Zbývá už jen doladit barvičky, smazat zbytečnou legendu, korektně vyplnit titulek grafu a je vymalováno. Hotový výsledek taky přikládám ke stažení.

Romanga měla pravdu - nedalo mi to spát. Přeju tedy všem konečně dobrou noc a prosím, netrapte se takovými zbytečnými pitomostmi, jako je plnění naprosto nesmyslných úkolů v Excelu. Stejně to s největší pravděpodobností nebudete už nikdy potřebovat.