ANOVA in Excel: die einfaktorielle Varianzanalyse
Ein Händler testet drei Ladenlayouts. Die Wochenumsätze unterscheiden sich, aber das tun sie auch innerhalb jedes Layouts von Woche zu Woche. Die ANOVA prüft, ob die Unterschiede zwischen den Gruppen größer sind, als es dieses normale Schwanken erklärt.
Kurz gesagt: Die einfaktorielle ANOVA findest du unter Daten, Datenanalyse, Anova: Einfaktorielle Varianzanalyse (vorher die Analyse-Funktionen aktivieren). Sie vergleicht die Streuung zwischen den Gruppen mit der Streuung innerhalb der Gruppen. Das Verhältnis ist der F-Wert. Ist der p-Wert kleiner als 0,05, unterscheidet sich mindestens ein Gruppenmittelwert von den anderen.
Probier es gleich aus: den F-Wert berechnen
Die Tabelle ist der Kern jeder ANOVA-Ausgabe: Quadratsummen (SS) und Freiheitsgrade (df) zwischen und innerhalb der Gruppen. Daraus berechnest du den F-Wert. Klick in die gelbe Zelle und schreib die Formel.
Aufgabe Die Tabelle stammt aus einer einfaktoriellen ANOVA mit drei Gruppen: SS ist die Quadratsumme, df die Zahl der Freiheitsgrade. Berechne in F2 den F-Wert: die mittlere Quadratsumme zwischen den Gruppen geteilt durch die mittlere Quadratsumme innerhalb der Gruppen.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Streuung | SS | df | |||
| 2 | Zwischen den Gruppen | 166,53 | 2 | F-Wert | ||
| 3 | Innerhalb der Gruppen | 86,4 | 12 |
Was F bedeutet
Die Zahlen stammen aus dem Layout-Test: drei Layouts mit je fünf Wochen, Mittelwerte 43,4, 51,4 und 46,0 T€, Gesamtmittel 46,93 T€.
| Teil | Bedeutung | Im Beispiel |
|---|---|---|
| SS zwischen | Wie weit die Gruppenmittelwerte vom Gesamtmittel abweichen, quadriert und mit der Gruppengröße gewichtet | 166,53 |
| SS innerhalb | Wie weit die Einzelwerte von ihrem eigenen Gruppenmittel abweichen, quadriert und summiert | 86,4 |
| df | Gruppen minus 1 (zwischen) und Werte minus Gruppen (innerhalb) | 2 und 12 |
| MS | Mittlere Quadratsumme: SS geteilt durch df | 83,27 und 7,2 |
| F | MS zwischen geteilt durch MS innerhalb | 11,56 |
| p-Wert | Wahrscheinlichkeit für ein mindestens so großes F, wenn alle Gruppen in Wahrheit gleich wären | 0,0016 |
Gäbe es keinen echten Unterschied zwischen den Layouts, lägen beide MS ungefähr gleich hoch und F wäre um 1. Hier streuen die Gruppenmittel mehr als elfmal so stark. Der p-Wert von 0,0016 sagt: Ein so großes F wäre bei gleichen Layouts sehr unwahrscheinlich. Der kritische Wert für 5 % liegt bei 2 und 12 Freiheitsgraden bei 3,89. Alles darüber ist signifikant.
ANOVA mit den Analyse-Funktionen
- Einmalig aktivieren: Datei, Optionen, Add-Ins. Bei Verwalten Excel-Add-Ins wählen, auf Los klicken und den Haken bei Analyse-Funktionen setzen. In der Microsoft-Hilfe heißt das Add-In auch Analysis ToolPak. Auf dem Mac: Extras, Excel-Add-Ins.
- Daten in Spalten anordnen, eine Spalte je Gruppe, Überschriften in der ersten Zeile.
- Registerkarte Daten, Datenanalyse, Anova: Einfaktorielle Varianzanalyse wählen.
- Als Eingabebereich die ganze Tabelle mit Überschriften markieren, Spalten als Anordnung wählen und das Häkchen für Beschriftungen in der ersten Zeile setzen.
- Alpha bei 0,05 lassen, einen Ausgabebereich wählen und mit OK bestätigen.
Excel schreibt zwei Tabellen. Die erste fasst jede Gruppe zusammen: Anzahl, Summe, Mittelwert und Varianz. Die zweite ist die ANOVA-Tabelle mit den Zeilen zwischen den Gruppen, innerhalb der Gruppen und gesamt sowie den Spalten SS, df, MS, F, P-Wert und kritischem F-Wert. Die Ausgabe besteht aus festen Zahlen und rechnet sich nicht neu, wenn du Daten änderst.
Noch eine Übung: die Quadratsumme innerhalb
Wer verstehen will, woher die Zahlen kommen, rechnet sie mit Formeln nach. Die Quadratsumme innerhalb jeder Gruppe ist (n-1) mal die Stichprobenvarianz, also das, was VAR.S vor dem Teilen berechnet. Mehr zur Varianz steht im Kapitel Standardabweichung.
Aufgabe Ein Händler testet drei Ladenlayouts, die Tabelle zeigt den Wochenumsatz in T€. Berechne in G2 die Quadratsumme innerhalb der Gruppen. Tipp aus der Statistik: Pro Gruppe ist das (n-1) mal die Stichprobenvarianz, hier also 4 mal VAR.S der Spalte.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Woche | Layout A | Layout B | Layout C | |||
| 2 | 1 | 42 | 51 | 46 | SS innerhalb | ||
| 3 | 2 | 47 | 48 | 43 | |||
| 4 | 3 | 39 | 55 | 49 | |||
| 5 | 4 | 45 | 50 | 47 | |||
| 6 | 5 | 44 | 53 | 45 |
Diese Übung gehört zu Premium.
Mit Premium öffnest du alle Übungen jeder Lektion, übst unbegrenzt mit neuen Aufgaben und kannst den Abschlusstest mit Zertifikat machen.
Premium ansehen Schon Premium? Anmelden
Die erste Übung dieser Lektion ist kostenlos.
Die Quadratsumme zwischen den Gruppen rechnest du so: =5*((MITTELWERT(B2:B6)-MITTELWERT(B2:D6))^2+(MITTELWERT(C2:C6)-MITTELWERT(B2:D6))^2+(MITTELWERT(D2:D6)-MITTELWERT(B2:D6))^2) ergibt 166,53. Daraus folgt F wie in der ersten Übung, und den p-Wert liefert im echten Excel =F.VERT.RE(11,56;2;12), rund 0,0016. In den Übungen hier im Browser gibt es F.VERT.RE nicht. Den p-Wert nimmst du deshalb aus der Ausgabe der Analyse-Funktionen.
Was die ANOVA nicht sagt
Ein signifikantes Ergebnis heißt nur: Mindestens ein Layout unterscheidet sich. Welches, verrät die einfaktorielle ANOVA nicht. Ein einfacher Weg sind paarweise t-Tests mit Bonferroni-Korrektur: Bei drei Vergleichen gilt dann 0,05 / 3, also rund 0,017 als Grenze. Statistikprogramme bieten dafür eigene Verfahren wie den Tukey-Test, Excel hat sie nicht eingebaut. Hier liegt Layout B mit 51,4 T€ klar vorn.
Voraussetzungen
- Die Messungen sind unabhängig voneinander.
- Die Werte jeder Gruppe sind ungefähr normalverteilt, oder die Gruppen sind nicht zu klein.
- Die Gruppen streuen ähnlich stark. Vergleiche die Varianzen in der ersten Ausgabetabelle.
Typische Fehler
Mehrere t-Tests statt einer ANOVA
Mit jedem weiteren Test steigt die Chance, zufällig ein signifikantes Ergebnis zu bekommen. Bei mehr als zwei Gruppen kommt zuerst die ANOVA.
Text im Eingabebereich
Außer den Überschriften dürfen im Eingabebereich nur Zahlen stehen. Ohne das Häkchen für Beschriftungen behandelt Excel die Überschriften als Daten und meldet einen Fehler.
Datenanalyse fehlt im Menüband
Die Schaltfläche erscheint erst, wenn die Analyse-Funktionen aktiviert sind.
ANOVA auf Englisch
| Deutsch | Englisch |
|---|---|
| Anova: Einfaktorielle Varianzanalyse | Anova: Single Factor |
| Analyse-Funktionen, Datenanalyse | Analysis ToolPak, Data Analysis |
=F.VERT.RE(11,56;2;12) | =F.DIST.RT(11.56,2,12) |
| F.INV.RE, F.TEST, VAR.S | F.INV.RT, F.TEST, VAR.S |
In welchen Excel-Versionen gibt es die ANOVA?
Die Analyse-Funktionen sind ein Add-In, das mit Excel für Windows und Mac ausgeliefert wird. Microsoft beschreibt das Aktivieren für beide Systeme. F.VERT.RE, F.INV.RE und F.TEST gibt es laut Microsoft seit Excel 2010.
Häufige Fragen
Was ist der Unterschied zwischen ANOVA und F-Test?
Die ANOVA vergleicht Mittelwerte von zwei oder mehr Gruppen. Die Funktion F.TEST und das F-Test-Tool der Analyse-Funktionen vergleichen die Varianzen von genau zwei Gruppen. Beide nutzen die F-Verteilung.
Wann nehme ich die zweifaktorielle Varianzanalyse?
Wenn zwei Einflussgrößen gleichzeitig wirken, etwa Layout und Wochentag. Die Analyse-Funktionen bieten sie mit und ohne Messwiederholung an.
Ist ANOVA mit zwei Gruppen dasselbe wie ein t-Test?
Bei zwei Gruppen ergibt die einfaktorielle ANOVA denselben p-Wert wie T.TEST mit Typ 2, zweiseitig. F ist dann das Quadrat des t-Werts.
Wie vergleiche ich Häufigkeiten statt Mittelwerte?
Mit dem Chi-Quadrat-Test.