Wie man die Standardabweichung in Excel verwendet, um fehlerhafte Daten zu erkennen
Vielleicht haben Sie schon vom Sprichwort gehört, bei dem jemand die Füße im Gefrierfach und den Kopf im Ofen hat: Im Durchschnitt fühlt er sich dabei völlig wohl. In Excel machen wir das jeden Tag mit unseren Daten. Durchschnittswerte sind zwar gute Zusammenfassungen, doch sie können Ihre Entscheidungen insgeheim untergraben.
Aber keine Sorge - die Lösung ist einfach. Weisen Sie jedem Mittelwert einen Standardabweichungswert sowie ein einfaches Volatilitätsverhältnis zu (ein schneller Zuverlässigkeitswert). Wenn die Varianz hoch ist, verwenden Sie stattdessen den Median oder den getrimmten Mittelwert.
Ein dynamischer Tracker hält die wichtigsten Metriken im Blickfeld.
Fügen Sie Ihrem Arbeitsblatt ein „Zuverlässigkeits-Dashboard“ hinzu.
Angenommen, Sie verfolgen die Leistung von zwei Vertriebsteams in einer Excel-Tabelle namens T_Totals. Team A ist äußerst konstant in seiner Leistung, während Team B auf einen einzigen Star spielt, der den Rest der Gruppe mitzieht.
Um diesem Leitfaden Schritt für Schritt folgen zu können, laden Sie eine kostenlose Kopie der Excel-Arbeitsmappe herunter. Nach dem Klicken auf den Link finden Sie die Download-Schaltfläche in der oberen rechten Ecke Ihres Bildschirms.
Wenn man den Durchschnitt berechnet, scheinen die beiden Teams ähnlich zu performen - doch das verdeckt enorme Unterschiede in den Einzelleistungen. Deswegen müssen wir dem Tabellenblatt einige einfache Sicherheitsmaßnahmen hinzufügen - Werkzeuge, die zeigen, wie zuverlässig der jeweilige Durchschnittswert tatsächlich ist.
Beginnen Sie mit der Funktion AVERAGE in B1, um den Durchschnittswert der Punktzahlen von Team A zu ermitteln:
=AVERAGE(T_Totals[Team A])Und ziehen Sie die Formel in die Zelle C1 für Team B.
Berechnen Sie nun die Standardabweichung für Team A in Zelle B2 unter Verwendung der Funktion STDEV.S:
=STDEV.S(T_Totals[Team A])Und ziehen Sie diese zweite Formel in die Zelle C2 für Team B.
Als Sie Ihre STDEV.S-Formel eingegeben haben, ist Ihnen vermutlich die Option STDEV.P (Population) aufgefallen. Verwenden Sie diese nur, wenn Sie die „gesamte Datenmenge“ zur Verfügung haben - mit anderen Worten: alle Transaktionen aus der gesamten Unternehmensgeschichte. In den meisten Fällen sollten Sie STDEV.S (Stichprobe) verwenden. Dabei wird durch (n-1) statt durch n dividiert; dies erhöht die Schätzung geringfügig, um die Unsicherheit der Stichprobenziehung zu berücksichtigen.
Anschließend berechnen Sie das Volatilitätsverhältnis für Team A in Zelle B3 mit folgender Formel:
=B2/B1Und ziehen Sie es in die Zelle C3 für Team B.
Abschließend stellen Sie die Anzeige der Ergebnisse in den Zellen B1:C2 auf zwei Dezimalstellen ein und formatieren Sie die Zellen B3:C3 als Prozentwerte - indem Sie die Symbole in der Gruppe Zahl auf der Registerkarte Start verwenden - um Ihr Dashboard zur Zuverlässigkeit aufzuräumen.
Identische Durchschnittswerte verbergen oft gegensätzliche Extreme in Ihren Daten.
Verwenden Sie das Volatilitätsverhältnis als Zuverlässigkeitswert.
Wenn man sich die Durchschnittswerte ansieht, liegen beide Teams bei 5.000. Ohne weitere Analyse könnte man annehmen, dass sie auf dem gleichen Leistungsniveau sind. Doch die Standardabweichung sowie das Volatilitätsverhältnis (wissenschaftlich bekannt als Variationskoeffizient) erzählen eine andere Geschichte:
- Team A (konsistent): Die Abweichung beträgt lediglich 158,11. Dies gibt die typische Distanz an, um die sich die Verkaufszahlen jedes Einzelnen vom Mittelwert von 5.000 entfernen - da dieser Wert gering ist, zeigt er, dass fast alle Verkäufer in der Nähe des Durchschnitts liegen. Mit einer Volatilitätsrate von 3 % ist dieser Durchschnittswert äußerst zuverlässig.
- Team B (unbeständig): Weist eine erhebliche Abweichung von 11.180,34 auf. Dies deutet darauf hin, dass der Durchschnittswert lediglich ein mathematischer Zufall ist, der durch einen einzigen Ausreißer entsteht - und somit kein Abbild der typischen Leistung darstellt. Das Volatilitätsverhältnis beträgt 224%; folglich ist der Durchschnittswert nicht zuverlässig.
Das ist die Empfindlichkeit gegenüber Ausreißern - wie eine einzige große Zahl den Durchschnitt vom üblichen Wert abweichen lässt. Diese Schwellenwerte sind keine festen statistischen Gesetze, aber sie stellen praktische Richtwerte für Ihre Dashboards dar:
- 0 %-15 % (geringe Varianz): Der Durchschnitt ist eine hervorragende Repräsentation der Daten.
- 16%-50% (mäßige Varianz): Der Durchschnittswert ist nützlich, doch die Spanne ist groß genug, sodass Ausreißer auftreten können.
- 51 %+ (hohe Varianz): Der Durchschnitt ist statistisch gesehen „unzuverlässig“ und möglicherweise irreführend. Sie sollten stattdessen den Median verwenden, um den wahren Mittelwert zu ermitteln.
Um die Verteilung der Daten zu visualisieren, verwenden Sie ein Box-Plot - ein weniger bekanntes Diagrammtyp, das in Excel verfügbar ist. Die „Box“ zeigt die mittleren 50 Prozent Ihrer Daten an; die darin befindliche Linie markiert den Median. Die „Whisker“ erstrecken sich bis zu den Minimum- und Maximumwerten, wodurch Ausreißer leicht erkennbar sind und die Variabilität der Daten auf einen Blick ersichtlich wird.
Die Lösung: Ersetzen Sie Ihre Formeln durch zuverlässigere Logik.
Verwenden Sie den Median oder den getrimmten Mittelwert für Daten mit hoher Varianz.
Falls Ihre Volatilität im Bereich „hohe Varianz“ liegt (über 50 %), sollten Sie sich nicht mehr ausschließlich auf die Funktion =AVERAGE verlassen. Um einen ehrlicheren Mittelweg zu finden, verwenden Sie diese beiden Methoden.
Methode 1: Den Median finden
Die Funktion MEDIAN konzentriert sich auf den mittleren Wert - daher verfälschen extreme Ausreißer das Ergebnis nicht. Sie können dies als neue Zeile in Ihrem Dashboard hinzufügen. In Zelle A4 geben Sie Median ein, und in Zelle B4 schreiben Sie:
=MEDIAN(T_Totals[Team A])Und ziehen Sie es hinüber.
Bei Team B deutet der Durchschnitt auf eine solide Leistung von 5.000 hin, doch der Median beträgt 0. Dies spiegelt viel genauer die Situation wider: Vier von fünf Teammitgliedern haben nämlich nichts verkauft. Die Tatsache, dass Durchschnitt und Median bei Team A identisch sind, zeigt, dass die Daten dieses Teams konsistent und zuverlässig sind.
Methode 2: Verwenden Sie den standardmäßigen getrimmten Mittelwert
Falls Sie ein großes Datenset haben (mindestens Dutzende oder Hunderte von Zeilen) und die extremen Werte am Anfang und Ende ignorieren möchten, verwenden Sie TRIMMEAN:
=TRIMMEAN(T_Totals[Team A],0.2)Eine gängige Einstellung ist 0,2 - damit weist man Excel an, 20 Prozent der Datenpunkte zu entfernen (10 Prozent von oben und 10 Prozent von unten). Dadurch werden Ausreißer verhindert, die den Durchschnittswert von der Realität abweichen lassen könnten. Für unser Datenset mit nur fünf Zeilen funktioniert diese Methode jedoch nicht: Bei so wenigen Datenpunkten verfügt Excel nicht über genügend Informationen, um die Extremwerte zu entfernen, ohne das gesamte Datenset zu verlieren. Nutzen Sie diese Methode lieber für Ihre größten und unübersichtlichsten Tabellen.
Wann ist der Durchschnitt eigentlich sicher?
Identifizieren Sie normale Daten, für die kein Warnhinweis erforderlich ist.
Ich sage ja nicht, dass Sie AVERAGE komplett nicht mehr verwenden sollen. Sie können einem Mittelwert vertrauen, wenn:
- Die Gleichförmigkeit ist hoch: Ihr Volatilitätsverhältnis liegt unter 10 %.
- Sie haben große Stichprobenumfänge: Tausende von Zeilen verringern die Auswirkung eines einzelnen Ausreißers.
- Sie verwenden kontrollierte Prozesse: Umgebungen, die dafür konzipiert sind, Schwankungen zu vermeiden - wie eine Fabriklinie oder standardisierte Tests - sorgen dafür, dass der Durchschnittswert äußerst stabil bleibt.
Vertrauen Sie keiner Zahl nur deswegen, weil sie „sauber“ aussieht. Eine Tabelle, die nur Durchschnittswerte anzeigt, erzeugt im digitalen Kontext dasselbe Problem wie ein Gefrier- und Backofen-Szenario. Wenn Sie zu Ihren Tabellen außerdem die Standardabweichung sowie das Volatilitätsverhältnis hinzufügen, erstellen Sie ein Frühwarnsystem, das Ihnen genau anzeigt, wann Sie Ihren Daten vertrauen können.
Doch das ist ein reaktiver Ansatz. Eine proaktivere Lösung besteht darin, die Datenvalidierung zu nutzen, um zu steuern, was Nutzer überhaupt eingeben dürfen - damit extreme Ausreißer niemals Ihre Durchschnittswerte verfälschen können.