Jak na test ANOVA v Excelu?

Test ANOVA je jedním z klíčových nástrojů při vyhodnocováním měření a testování vlivu jednotlivých parametrů na výsledek měřené veličiny. Chci Vám ukázat, že tuto metodu můžete použít i bez speciálního statistického softwaru a vystačíte s Excelem.

Zadání příkladu

Zjišťujeme, jestli mezi stroji A, B a C existuje statisticky významný rozdíl ve výkonnosti. Během 2 týdnů (tj. 10 prac. dnů) byly naměřeny tyto hodnoty (deset hodnot na každém stroji):

Pro toto řešení, tj. testování statisticky významné závislosti mezi atributem (= neměřitelný znak, zde stroj: A, B, C) a číslem (= měřitelný znak, zde hodnoty výkonu: např. 249ks/směnu) se využívá test ANOVA (= analýza variability)

Postup

1. krok 

Pro každou skupinu hodnot A, B, C spočítejte průměr (,, ) a následně druhou mocninu odchylky od tohoto průměru pro každou hodnotu: (ā - a)2(b̄ - b)2, (c̄  - c)2.

Vzorec pro průměr skupiny A (buňka C13):

=PRŮMĚR(C3:C12)

Vzorec pro druhou mocninu odchylek jednotlivých hodnot A od průměru (sloupec F). Pozn.: znak pro mocninu "stříška" vložíte pomocí "PravýAlt + š" a následně stisknete nějaké číslo (zde dvojku).

=(C$13-C3)^2

Toto proveďte pro všechny skupiny A, B, C.

2. krok

Spočítejte "součet čtverců", tj. součet každého sloupce F, G, H (obsahující druhé mocniny odchylek pro skupiny hodnot A, B, C, na obr. žlutě)

3. krok

 

Nyní to samé provedeme pro všechny hodnoty dohromady. Zkopírujte hodnoty A, B, C pod sebe (označme je X), spočítejte průměr pro všechny hodnoty (nazývejme ho ), a pak druhou mocninu odchylky od tohoto průměru: (x̄ - x)2

Vzorec pro průměr všech hotnot X (buňka C47):

=PRŮMĚR(C17:C46)

Vzorec pro druhou mocninu odchylek jednotlivých hodnot X a průměru všech hodnot x̄:

=(C$47-C17)^2

A opět spočítejte součet těchto "čtverců odchylek" (na obr. zeleně)

4. krok

Nejdříve trochu teorie a pak už jen dosadíme pár vzorců do Excelu. Budeme počítat postupně:

  • DF = degree of freedom (stupně volnosti),
  • SS = sum of squares (součet čtverců) 
  • MS = mean square (průměrný čtverec)
  • F = hodnota F rozdělení
  • P = p-value (pravděpodobnost "nulové hypotézy")
Stupeň volnosti

Stupeň volnosti je počet možných různých změn hodnoty. Tj. Mám-li celkově naměřeno 30 hodnot, může se od první hodnoty lišit max. 29 hodnot. To je celkový stupeň volnosti. V našem příkladu v modré tabulce hodnota v buňce H21.

DF = "celkový počet všech naměřených hodnot" - 1
DF = N - 1

Stupeň volnosti faktoru "stroj" (faktor nabývá třech hodnot: A, B, C), je stupeň volnosti 2, neboť od průměrného výkonu stroje A, se mohou ostatní průměrné výkony lišit max. ve dvou hodnotách. V našem příkladu v modré tabulce hodnota v buňce H19

DF = "počet skupin" - 1
DF = G - 1

Stupeň volnosti připadající na tzv. šum (angl. Error), je rozdílem celkového DF a DF zkoumaného faktoru. Zde: 29 - 2 = 27

DFcelkový = DFfaktoru + DFšumu
DFšumu = DFcelkový - DFfaktoru

Faktor šumu je v Excelu v buňce H20 jako rozdíl H21 a H19.

Mean square - průměrný čtverec

V analýze ANOVA budeme rozlišovat vliv faktoru "stroj" na variabilitu, tzv. šum (Error), tj. zdroje náhodné variability a celkovou variabilitu.

Platí:

Variabilitacelková = variabilitazkoumaného_faktoru + variabilitašum
SScelková = SSfaktoru + SSšumu
SStotal = SSfactor + SSerror

SScelková je v příkladu v zelené buňce D47 a SSšumu vznikne součtem žlutých hodnot F13, G13 a H13. SSfaktoru je pak jejich rozdíl.

Průměrný čtverec je pak pro každou složku ovlivňující variabilitu:

MSfactor = SSfactor / DFfactor
MSerror = SSerror / DFerror

Význam vlivu faktoru (zde "stroje") na výkon je pak poměr MSfaktoru a MSšumu.

F = MSfactor / MSerror

F je hodnota Fisherova rozdělení (F-distribution). Křivka F rozdělení vypadá pro každou kombinaci stupňů volnosti (faktoru a šumu) trochu jinak. Viz obrázek vlevo níže (převzatý z Wikipedie):

p-value

P nebo taky p-value je pravděpodobnost, že námi pozorovaný rozdíl hodnot mezi jednotlivými stroji A, B, C je dán spíše šumem (součtem mnoha náhodných vlivů) než samotným faktorem (strojem).

Hodnotu p-value si lze představit jako obsah světlemodré plochy od hodnoty F doprava (tj. do plus nekonečna). Viz obrázek vpravo výše. Samozřejmě pro správně vybranou křivku (dle DFfaktoru a DFšumu)

Běžně se hodnoty p-value hledaly v tabulkách. Naštěstí Excel disponuje funkcí FDIST, která nám z hodnot F, DFfaktoru a DFšumu spočítá hodnotu p-value rovnou.

= FDIST(F; DFfaktoru; DFšumu)
= FDIST(3,50; 2; 27)

Výsledek je 0,045, tj 4,5%. Jelikož (obvykle) nultou hypotézu zamítáme, je-li p-value < 5%, a to zde nastalo. Zamítáme hypotézu, že se nám rozdíly mezi stroji A, B a C "jen zdají" a jsou ve skutečnosti způsobeny víc šumem, který ovlivnil výsledky než stroji samotnými. A přijímáme alternativní hypotézu, že vliv stroje na výkonnost reálně existuje.

Závěr a interpretace výsledků

  • ANOVA test na jedné straně "smíchá" měření ze všech skupin dohromady a spočítá celkovou variabilitu bez ohledu na jakýkoli vliv faktoru (její míru ukazuje SScelkový).
  • Na straně druhé provede totéž pro jednotlivé skupiny. Zjistí tedy variabilitu uvnitř skupin. Ty pro jednotlivé skupiny sečte. To je variabilita způsobená velkým množstvím neidentifikovatelných vlivů (šum) = SS šum
  • Rozdíl celkové variability a šumu pak logicky připadá na náš zkoumaný faktor. SSfaktoru  = SScelkový - SSšumu
  • Pro předchozí hodnoty (SScelkový a SSšum) pak spočítá průměrnou variabilitu MS (MS = SS / DF)
  • Spočítáme F = MSfaktor / MSšum (= poměr vlivu faktoru a šumu)
    • Je-li F  "malé", znamená to vlastně: "Výkony jednotlivých strojů jsou natolik nestabilní, že malý rozdíl mezi jejich průměrnou výkonnosti nelze brát moc vážně.".
    • Je-li F "velké", znamená to: "Výkon jednotlivého stroje kolísá jen málo a vůči tomuto kolísání jsou rozdíly průměrných výkonností dostetečně větší, že je stojí za to brát v úvahu."
  • P-value je pak jen kvantitativní vyjádření toho, na jakou stranu z předcházejících dvou bodů se přikláníme:
    • P-value < 5% - zamítáme nultou hypotézu H0, že vše je dáno hlavně šumem a považujeme vliv faktoru za přijatý. Tj. přijímáme alternativní hypotézu HA (= vliv faktoru existuje)
    • P-value >= 5% - nezamítáme nultou hypotézu H0 a vliv faktoru považujeme za neprokázaný.
2007 - 2017 © Ing. Vladimír Volko - poradenství pro zvyšování výkonnosti podniku