Jak na histogram v Excelu?

Histogram je jedním se základních nástrojů kontroly jakosti. Je vhodný např. pro zobrazení časových údajů, kde "průměrná hodnota" je spíše zavádějící a je užitečné spíše vnímat strukturu hodnot. Např. dodací doba, průběžná doba provozu, časy cyklu, atd. Tady je malý návod jak na to.

Zadání příkladu

Sestrojte histogram z 50 čísel, náhodně vygenerovaných v intervalu <1;20> s přesností 1 desetinné místo. Histogram je vlastně sloupcový graf četnosti jednotlivých hodnot.

Excel obsahuje sice přímo průvodce, který vytvoří histogram. Bohužel graf potom nebude online reagovat na změny hodnot. Pokud nám to vadí, tady je řešení, jak si histogram uděláme sami bez průvodce.

Postup

Vytvoříme si 50 hodnot funkcí =RANDBETWEEN(10;200)/10. Na obrázku buňky B14:K18. 

Poznámka: Zvolené hodnoty se vám budou při každém úkonu znovu přepočítávat a generovat znovu a jiné. Pokud tomu chcete zamezit, stačí buňky skopírovat do schvánky windows a pak z kontextového menu "Vložit jinak" zvolit "Hodnoty". Funkce RANDBETWEEN v buňkách bude nahrazena jejími hodnotami a ty už se měnit nebudou. Vyzkoušejte. Snadné.

Výpočet parametrů histogramu

K pohodlnější tvorbě parametrů histogramu si spočítáme pár hodnot (modrá oblast):

• N = počet analyzovaných hodnot (v našem příkladě 50). Spočítat se dá ale i funkcí z vybrané oblasti hodnot B14:K18

  = POČET(B14:K18)

• Doporučeno intervalů. Počet intervalů = počet sloupečků diagramu.
  Vhodný počet intervalů závisí na počtu hodnot. Jeden ze vhodných vztahů je tzv. "yuleovo pravidlo". Počet intervalů je tedy 2,5 krát čtvrtá odmocnina z počtu hodnot N. Funkce POWER v Excelu se používá pro obecnou funkci mocniny. N je v buňce D20.

  = 2,5 * POWER(D20;1/4)

   Zde by vyšlo asi 6,6478. Počet intervalů musí být logicky celé číslo, takže zaokrouhlíme nahoru tak, že "kolem funkce" přidáme funkce "zaokrouhli nahoru", = ROUNDUP s parametrem 0 (tj. bez desetinných míst).

  = ROUNDUP(2,5 * POWER(D20;1/4);0)

• Minimum, maximum: slouží k vypočtení kroku, tj. šířky intervalu.

  = MIN(B14:K18)
  = MAX(B14:K18)

• Krok je pak rozdíl max a min dělený počtem intervalů, tj. šířka sloupečku grafu.

  =(D23-D22)/D21

Úprava parametrů histogramu

Tyto vypočtené hodnoty (žlutá oblast v obrázku) by mohly být použity hned k tvorbě grafu. Nicméně často je vhodné trochu pravit krok, případně posunout začátek či konec grafu, aby popisky grafu byly "hezčí".  Hodnotu kroku tedy upravíme na 2,5. Popisky histogramu budou potom přehlednější. Začátek grafu, tj. min. upravíme na nulu. Maximum, tj. konec grafu pak spočítáme tak, že k minimu (začátku) přičteme počet kroků krát šířku intervalu:

= G22 + G24*G21

Pokud vyjde číslo menší než maximum v modré oblasti, tj. některé hodnoty po úpravě našich hodnot by "utekly" z grafu. Přidáme jeden sloupec. Zde  upravené Maximum=17,5, proto přidáme osmý interval. Při počtu 8 intervalů (sloupců) se upravené Maximum posune na 20, což nám stačí.

Poznámka: Mohli bychom také šířku intervalu zvolit 3 a počet intervalů by zůstal 7. Záleží jen na naší volbě.

Vytvoření tabulky intervalů a četností

Vytvoříme si osm řádků pro osm intervalů. Sloupec B26, "DM>", znamená "dolní mez intervalu" a "větší než" znamená, že hodnoty přesně se rovnající DM sem započteny nebudou. "HM" a "<=" znamená, "horní mez intervalu" a "menší nebo rovno", tj. hodnoty přesně se rovnající mezi sem započteny budou. Matematicky se to dá zapsat intervalem: (DM;HM>.

Samotné hodnoty, začínají od minima (žlutého) a zvyšují se o krok (taky žlutý). Četnosti můžeme zístak pomocí funkce COUNTIFS, která spočítá počet hodnot ze zvolené oblasti (zde oblasti B14:K18) vyhovující podmínkám. Funkce vypadá takto (vždy obsahuje dvojice oblast a podmínka).:

= COUNTIFS("oblast1";"podmínka1";"oblast2";"podmínka2")

Podmínka se zapisuje jako textový parametr v uvozovkách. Pokud bychom v podmínce uvedli rovnou hodnotu s kterou oblast povovnáváme, byl by tvar snadný:

= COUNTIFS($B$14:$K$18; ">0"; $B$14:$K$18; "<=2,5")

Pokud však chceme porovnat s hodnotou buňky např. B27, která se má do funkce načíst, je třeba k textu ">" přidat hodnotu buňky takto: &B27. Excel si přečte obsah B27 a k řetězci ">" přidá (to mu říká znak &), hodnotu buňky B27. Tj. v paměti vznikne něco jako ">0". Funkce tedy bude pro buňku D27 vypadat takto:

=COUNTIFS($B$14:$K$18;">"&B27;$B$14:$K$18;"<="&C27)

Pro ostatní buňky D28 až D34 stačí vzorec skopírovat přetažením za dolní pravý úchopový bod. Tj. poslední D34 bude obsahovat:

=COUNTIFS($B$14:$K$18;">"&B34;$B$14:$K$18;"<="&C34)

Vytvoření grafu

Nejjednodušší způsob vytvoření histogramu je označit datové řady pro osy X, Y v tabulce. V našem příkladě C27:D34. A v menu karty "Vložení" vybrat "Bodový". Ten později změníme na sloupcový a upravíme mezery mezi sloupci.

Úprava grafu

Kliknutím na libovolný bod (malý modrý kosočtverec) na grafu vyberete datovou řadu. V kontextovém menu (pravé tlačítko myši) zvolíme "Změnit typ grafu řady" a zvolíme první ikonu sloupcového grafu.

Poté opět vybereme datovou řadu kliknutým na libovolný modrý sloupec a v kontextovém menu zvolíme "Formát datové řady". Upravíme šířku mezery z výchozí hodnoty 150% na např. 10%, aby sloupce byly těsněji u sebe. Výsledek je na obrázku. A histogram je na světě.