Sco in Excel. Excel. Berechnung der Standardabweichung für Trenddaten

Standardabweichung ist einer dieser statistischen Begriffe in der Unternehmenswelt, der Menschen Glaubwürdigkeit verleiht, denen es in einem Gespräch oder einer Präsentation gut gelingt, während er bei denen, die nicht wissen, was es ist, sich aber zu sehr dafür schämt, eine vage Verwirrung hinterlässt fragen. Tatsächlich verstehen die meisten Manager das Konzept der Standardabweichung nicht und wenn Sie einer von ihnen sind, ist es an der Zeit, dass Sie aufhören, eine Lüge zu leben. Im heutigen Artikel erzähle ich Ihnen, wie dieses unterschätzte statistische Maß Ihnen helfen kann, die Daten, mit denen Sie arbeiten, besser zu verstehen.

Was misst die Standardabweichung?

Stellen Sie sich vor, Sie sind Eigentümer von zwei Geschäften. Und um Verluste zu vermeiden, ist eine klare Kontrolle der Lagerbestände wichtig. Um herauszufinden, welcher Manager den Lagerbestand besser verwaltet, analysieren Sie den Lagerbestand der letzten sechs Wochen. Die durchschnittlichen wöchentlichen Lagerkosten für beide Filialen sind ungefähr gleich und betragen etwa 32 herkömmliche Einheiten. Die durchschnittliche Stichwahl zeigt auf den ersten Blick, dass beide Manager ähnlich abschneiden.

Schaut man sich jedoch die Aktivitäten des zweiten Ladens genauer an, wird man überzeugt sein, dass der Durchschnittswert zwar stimmt, die Schwankung des Bestands jedoch sehr hoch ist (von 10 bis 58 USD). Daraus können wir schließen, dass der Durchschnitt die Daten nicht immer richtig auswertet. Hier kommt die Standardabweichung ins Spiel.

Die Standardabweichung zeigt, wie die Werte relativ zum Mittelwert in unserem Fall verteilt sind. Mit anderen Worten: Sie können nachvollziehen, wie groß die Streuung des Abflusses von Woche zu Woche ist.

In unserem Beispiel haben wir die STDEV-Funktion von Excel verwendet, um die Standardabweichung zusammen mit dem Mittelwert zu berechnen.

Im Fall des ersten Managers betrug die Standardabweichung 2. Dies sagt uns, dass jeder Wert in der Stichprobe im Durchschnitt um 2 vom Mittelwert abweicht. Ist es gut? Betrachten wir die Frage aus einem anderen Blickwinkel – eine Standardabweichung von 0 sagt uns, dass jeder Wert in der Stichprobe seinem Mittelwert entspricht (in unserem Fall 32,2). Somit unterscheidet sich eine Standardabweichung von 2 nicht wesentlich von 0, was darauf hinweist, dass die meisten Werte nahe am Mittelwert liegen. Je näher die Standardabweichung bei 0 liegt, desto zuverlässiger ist der Durchschnitt. Darüber hinaus weist eine Standardabweichung nahe 0 auf eine geringe Variabilität der Daten hin. Das heißt, ein Runoff-Wert mit einer Standardabweichung von 2 weist auf eine unglaubliche Konstanz des ersten Managers hin.

Bei der zweiten Filiale betrug die Standardabweichung 18,9. Das heißt, die Abflusskosten weichen im Durchschnitt von Woche zu Woche um 18,9 % vom Durchschnittswert ab. Verrückte Verbreitung! Je weiter die Standardabweichung von 0 entfernt ist, desto ungenauer ist der Durchschnitt. In unserem Fall deutet der Wert von 18,9 darauf hin, dass man dem Durchschnittswert (32,8 USD pro Woche) einfach nicht trauen kann. Es zeigt uns auch, dass der wöchentliche Abfluss sehr unterschiedlich ist.

Dies ist das Konzept der Standardabweichung auf den Punkt gebracht. Obwohl sie keinen Einblick in andere wichtige statistische Messungen (Modus, Median usw.) bietet, spielt die Standardabweichung in den meisten statistischen Berechnungen tatsächlich eine entscheidende Rolle. Das Verständnis der Prinzipien der Standardabweichung wird Aufschluss über viele Ihrer Geschäftsprozesse geben.

Wie berechnet man die Standardabweichung?

Jetzt wissen wir also, was die Standardabweichungszahl aussagt. Lassen Sie uns herausfinden, wie es berechnet wird.

Schauen wir uns den Datensatz von 10 bis 70 in 10er-Schritten an. Wie Sie sehen können, habe ich den Standardabweichungswert für sie bereits mit der Funktion STANDARDEV in Zelle H2 (in Orange) berechnet.

Nachfolgend sind die Schritte aufgeführt, die Excel unternimmt, um zu 21.6 zu gelangen.

Bitte beachten Sie, dass alle Berechnungen zum besseren Verständnis visualisiert sind. Tatsächlich erfolgt die Berechnung in Excel sofort und alle Schritte bleiben im Hintergrund.

Zunächst ermittelt Excel den Stichprobenmittelwert. In unserem Fall lag der Durchschnitt bei 40, der im nächsten Schritt von jedem Stichprobenwert abgezogen wird. Jede erhaltene Differenz wird quadriert und aufsummiert. Wir haben eine Summe von 2800 erhalten, die durch die Anzahl der Stichprobenelemente minus 1 geteilt werden muss. Da wir 7 Elemente haben, müssen wir 2800 durch 6 teilen. Aus dem erhaltenen Ergebnis ermitteln wir die Quadratwurzel, dies Die Zahl ist die Standardabweichung.

Für diejenigen, die sich über das Prinzip der Berechnung der Standardabweichung mithilfe der Visualisierung nicht ganz im Klaren sind, gebe ich eine mathematische Interpretation zur Ermittlung dieses Wertes.

Funktionen zur Berechnung der Standardabweichung in Excel

Excel verfügt über verschiedene Arten von Standardabweichungsformeln. Alles, was Sie tun müssen, ist =STDEV einzugeben und Sie werden es selbst sehen.

Es ist erwähnenswert, dass die Funktionen STDEV.V und STDEV.G (die erste und zweite Funktion in der Liste) die Funktionen STDEV und STDEV (die fünfte und sechste Funktion in der Liste) duplizieren, die aus Kompatibilitätsgründen mit früheren Funktionen beibehalten wurden Versionen von Excel.

Im Allgemeinen gibt der Unterschied in den Endungen der Funktionen .B und .G das Prinzip der Berechnung der Standardabweichung einer Stichprobe oder Population an. Den Unterschied zwischen diesen beiden Arrays habe ich bereits im vorherigen erklärt.

Eine Besonderheit der Funktionen STANDARDEV und STANDDREV (die dritte und vierte Funktion in der Liste) besteht darin, dass bei der Berechnung der Standardabweichung eines Arrays logische und Textwerte berücksichtigt werden. Text und echte boolesche Werte sind 1 und falsche boolesche Werte sind 0. Ich kann mir keine Situation vorstellen, in der ich diese beiden Funktionen benötigen würde, daher denke ich, dass sie ignoriert werden können.

Um den Durchschnittswert in Excel zu ermitteln (egal ob es sich um einen Zahlen-, Text-, Prozent- oder anderen Wert handelt), gibt es viele Funktionen. Und jeder von ihnen hat seine eigenen Eigenschaften und Vorteile. Tatsächlich können bei dieser Aufgabe bestimmte Bedingungen festgelegt werden.

Beispielsweise werden die Durchschnittswerte einer Zahlenreihe in Excel mithilfe statistischer Funktionen berechnet. Sie können Ihre eigene Formel auch manuell eingeben. Betrachten wir verschiedene Optionen.

Wie finde ich das arithmetische Mittel von Zahlen?

Um das arithmetische Mittel zu ermitteln, müssen Sie alle Zahlen der Menge addieren und die Summe durch die Menge dividieren. Zum Beispiel die Noten eines Studenten in Informatik: 3, 4, 3, 5, 5. Was im Quartal enthalten ist: 4. Das arithmetische Mittel haben wir mit der Formel ermittelt: =(3+4+3+5+5) /5.

Wie geht das schnell mit Excel-Funktionen? Nehmen wir zum Beispiel eine Reihe von Zufallszahlen in einer Zeichenfolge:

Oder: Machen Sie die aktive Zelle und geben Sie die Formel einfach manuell ein: =AVERAGE(A1:A8).

Sehen wir uns nun an, was die AVERAGE-Funktion sonst noch tun kann.

Lassen Sie uns das arithmetische Mittel der ersten beiden und letzten drei Zahlen ermitteln. Formel: =DURCHSCHNITT(A1:B1,F1:H1). Ergebnis:

Zustand durchschnittlich

Die Bedingung für die Bildung des arithmetischen Mittels kann ein numerisches Kriterium oder ein Textkriterium sein. Wir verwenden die Funktion: =AVERAGEIF().

Ermitteln Sie das arithmetische Mittel von Zahlen, die größer oder gleich 10 sind.

Funktion: =AVERAGEIF(A1:A8,">=10")

Das dritte Argument – ​​„Durchschnittsbereich“ – wird weggelassen. Erstens ist es nicht erforderlich. Zweitens enthält der vom Programm analysierte Bereich NUR numerische Werte. Die im ersten Argument angegebenen Zellen werden gemäß der im zweiten Argument angegebenen Bedingung durchsucht.

Aufmerksamkeit! Das Suchkriterium kann in der Zelle angegeben werden. Und verknüpfen Sie es in der Formel.

Lassen Sie uns den Durchschnittswert der Zahlen mithilfe des Textkriteriums ermitteln. Zum Beispiel der durchschnittliche Umsatz des Produkts „Tische“.

Die Funktion sieht folgendermaßen aus: =AVERAGEIF($A$2:$A$12,A7,$B$2:$B$12). Bereich – eine Spalte mit Produktnamen. Das Suchkriterium ist ein Link zu einer Zelle mit dem Wort „Tabellen“ (Sie können anstelle des Links A7 auch das Wort „Tabellen“ einfügen). Mittelungsbereich – die Zellen, aus denen Daten zur Berechnung des Durchschnittswerts entnommen werden.

Als Ergebnis der Berechnung der Funktion erhalten wir folgenden Wert:

Aufmerksamkeit! Für ein Textkriterium (Bedingung) muss der Mittelungsbereich angegeben werden.

Wie berechnet man den gewichteten Durchschnittspreis in Excel?

Wie haben wir den gewichteten Durchschnittspreis ermittelt?

Formel: =SUMPRODUCT(C2:C12,B2:B12)/SUM(C2:C12).

Mit der SUMMENPRODUKT-Formel ermitteln wir den Gesamtumsatz nach dem Verkauf der gesamten Warenmenge. Und die SUMME-Funktion summiert die Warenmenge. Indem wir den Gesamtumsatz aus dem Verkauf von Waren durch die Gesamtzahl der Wareneinheiten dividierten, ermittelten wir den gewichteten Durchschnittspreis. Dieser Indikator berücksichtigt das „Gewicht“ jedes Preises. Sein Anteil an der Gesamtmasse der Werte.

Standardabweichung: Formel in Excel

Es gibt Standardabweichungen für die Gesamtbevölkerung und für die Stichprobe. Im ersten Fall ist dies die Wurzel der allgemeinen Varianz. Im zweiten Fall aus der Stichprobenvarianz.

Zur Berechnung dieses statistischen Indikators wird eine Streuungsformel erstellt. Daraus wird die Wurzel gewonnen. Aber in Excel gibt es eine vorgefertigte Funktion zum Ermitteln der Standardabweichung.

Die Standardabweichung ist an den Maßstab der Quelldaten gebunden. Für eine bildliche Darstellung der Variation des analysierten Bereichs reicht dies nicht aus. Um den relativen Grad der Datenstreuung zu erhalten, wird der Variationskoeffizient berechnet:

Standardabweichung / arithmetisches Mittel

Die Formel in Excel sieht so aus:

STDEV (Wertebereich) / AVERAGE (Wertebereich).

Der Variationskoeffizient wird in Prozent berechnet. Daher legen wir das Prozentformat in der Zelle fest.

Rechnen wir einMSAUSGEZEICHNETStichprobenvarianz und Standardabweichung. Wir berechnen auch die Varianz einer Zufallsvariablen, wenn ihre Verteilung bekannt ist.

Lassen Sie uns zunächst überlegen Streuung, Dann Standardabweichung.

Stichprobenvarianz

Stichprobenvarianz (Stichprobenvarianz,ProbeVarianz) charakterisiert die Streuung der Werte im Array relativ zu .

Alle 3 Formeln sind mathematisch äquivalent.

Aus der ersten Formel geht das klar hervor Stichprobenvarianz ist die Summe der quadrierten Abweichungen jedes Werts im Array vom Durchschnitt, geteilt durch Stichprobengröße minus 1.

Abweichungen Proben die Funktion DISP() wird verwendet, Englisch. der Name VAR, d.h. VARIANTE. Ab Version MS EXCEL 2010 wird empfohlen, dessen Analogon DISP.V(), Englisch, zu verwenden. der Name VARS, d.h. Beispielvarianz. Darüber hinaus gibt es ab der Version von MS EXCEL 2010 eine Funktion DISP.Г(), Englisch. der Name VARP, d.h. Bevölkerungsvarianz, die berechnet wird Streuung Für Bevölkerung. Der ganze Unterschied liegt im Nenner: Anstelle von n-1 wie DISP.V() hat DISP.G() nur n im Nenner. Vor MS EXCEL 2010 wurde die Funktion VAR() zur Berechnung der Varianz der Grundgesamtheit verwendet.

Stichprobenvarianz
=QUADROTCL(Probe)/(COUNT(Probe)-1)
=(SUMME(Probe)-ANZAHL(Probe)*DURCHSCHNITT(Probe)^2)/ (ANZAHL(Probe)-1)– übliche Formel
=SUM((Probe -AVERAGE(Probe))^2)/ (COUNT(Probe)-1) –

Stichprobenvarianz ist nur dann gleich 0, wenn alle Werte einander gleich und dementsprechend gleich sind Durchschnittswert. Normalerweise gilt: Je größer der Wert Abweichungen, desto größer ist die Streuung der Werte im Array.

Stichprobenvarianz ist eine Punktschätzung Abweichungen Verteilung der Zufallsvariablen, aus der sie erstellt wurde Probe. Über den Bau Vertrauensintervalle bei der Beurteilung Abweichungen kann im Artikel nachgelesen werden.

Varianz einer Zufallsvariablen

Berechnen Streuung Zufallsvariable, Sie müssen es wissen.

Für Abweichungen Die Zufallsvariable X wird oft als Var(X) bezeichnet. Streuung gleich dem Quadrat der Abweichung vom Mittelwert E(X): Var(X)=E[(X-E(X)) 2 ]

Streuung berechnet nach der Formel:

Dabei ist x i der Wert, den eine Zufallsvariable annehmen kann, und μ der Durchschnittswert (), p(x) ist die Wahrscheinlichkeit, dass die Zufallsvariable den Wert x annehmen wird.

Wenn eine Zufallsvariable hat, dann Streuung berechnet nach der Formel:

Abmessungen Abweichungen entspricht dem Quadrat der Maßeinheit der ursprünglichen Werte. Wenn die Werte in der Stichprobe beispielsweise Teilgewichtsmessungen (in kg) darstellen, wäre die Varianzdimension kg 2 . Dies kann schwierig zu interpretieren sein. Um die Streuung der Werte zu charakterisieren, ist ein Wert erforderlich, der der Quadratwurzel entspricht Abweichungen – Standardabweichung.

Einige Eigenschaften Abweichungen:

Var(X+a)=Var(X), wobei X eine Zufallsvariable und a eine Konstante ist.

Var(aХ)=a 2 Var(X)

Var(X)=E[(X-E(X)) 2 ]=E=E(X 2)-E(2*X*E(X))+(E(X)) 2 =E(X 2)- 2*E(X)*E(X)+(E(X)) 2 =E(X 2)-(E(X)) 2

Diese Dispersionseigenschaft wird in verwendet Artikel über lineare Regression.

Var(X+Y)=Var(X) + Var(Y) + 2*Cov(X;Y), wobei X und Y Zufallsvariablen sind, Cov(X;Y) die Kovarianz dieser Zufallsvariablen.

Wenn Zufallsvariablen unabhängig sind, dann sind sie Kovarianz ist gleich 0 und daher Var(X+Y)=Var(X)+Var(Y). Diese Eigenschaft der Dispersion wird bei der Ableitung genutzt.

Zeigen wir, dass für unabhängige Größen Var(X-Y)=Var(X+Y) ist. Tatsächlich ist Var(X-Y)= Var(X-Y)= Var(X+(-Y))= Var(X)+Var(-Y)= Var(X)+Var(-Y)= Var( X)+(- 1) 2 Var(Y)= Var(X)+Var(Y)= Var(X+Y). Diese Dispersionseigenschaft wird zum Konstruieren verwendet.

Standardabweichung der Stichprobe

Standardabweichung der Stichprobe ist ein Maß dafür, wie stark die Werte in einer Stichprobe im Verhältnis zu ihrem Wert streuen.

A-Priorat, Standardabweichung gleich der Quadratwurzel von Abweichungen:

Standardabweichung berücksichtigt nicht die Größe der Werte in Probe, sondern nur der Grad der Streuung der Werte um sie herum Durchschnitt. Um dies zu veranschaulichen, geben wir ein Beispiel.

Berechnen wir die Standardabweichung für zwei Stichproben: (1; 5; 9) und (1001; 1005; 1009). In beiden Fällen ist s=4. Es ist offensichtlich, dass sich das Verhältnis der Standardabweichung zu den Array-Werten zwischen den Stichproben erheblich unterscheidet. Für solche Fälle wird es verwendet Der Variationskoeffizient(Variationskoeffizient, CV) – Verhältnis Standardabweichung zum Durchschnitt Arithmetik, ausgedrückt als Prozentsatz.

In MS EXCEL 2007 und früheren Versionen zur Berechnung Standardabweichung der Stichprobe Es wird die Funktion =STDEVAL() verwendet, Englisch. Name STDEV, d.h. Standardabweichung. Ab der Version von MS EXCEL 2010 wird empfohlen, dessen Analogon =STANDDEV.B() , Englisch, zu verwenden. Name STDEV.S, d.h. Beispiel einer Standardabweichung.

Darüber hinaus gibt es ab der Version von MS EXCEL 2010 eine Funktion STANDARDEV.G(), Englisch. Name STDEV.P, d.h. Bevölkerungsstandardabweichung, die berechnet wird Standardabweichung Für Bevölkerung. Der ganze Unterschied liegt im Nenner: Anstelle von n-1 wie in STANDARDEV.V() hat STANDARDEVAL.G() nur n im Nenner.

Standardabweichung kann auch direkt mit den untenstehenden Formeln berechnet werden (siehe Beispieldatei)
=ROOT(QUADROTCL(Probe)/(COUNT(Probe)-1))
=ROOT((SUM(Probe)-COUNT(Probe)*AVERAGE(Probe)^2)/(COUNT(Probe)-1))

Andere Streumaße

Die Funktion SQUADROTCL() rechnet mit eine Summe quadrierter Abweichungen von Werten von ihrem Durchschnitt. Diese Funktion liefert das gleiche Ergebnis wie die Formel =DISP.G( Probe)*ÜBERPRÜFEN( Probe) , Wo Probe– ein Verweis auf einen Bereich, der ein Array von Beispielwerten enthält (). Berechnungen in der Funktion QUADROCL() erfolgen nach der Formel:

Die Funktion SROTCL() ist auch ein Maß für die Ausbreitung eines Datensatzes. Die Funktion SROTCL() berechnet den Durchschnitt der absoluten Werte der Abweichungen von Werten Durchschnitt. Diese Funktion gibt das gleiche Ergebnis wie die Formel zurück =SUMPRODUCT(ABS(Probe-AVERAGE(Probe)))/COUNT(Probe), Wo Probe– ein Link zu einem Bereich, der ein Array von Beispielwerten enthält.

Berechnungen in der Funktion SROTCL() erfolgen nach der Formel:

Eines der wichtigsten Werkzeuge der statistischen Analyse ist die Berechnung der Standardabweichung. Mit diesem Indikator können Sie die Standardabweichung für eine Stichprobe oder eine Grundgesamtheit schätzen. Erfahren Sie, wie Sie die Standardabweichungsformel in Excel verwenden.

Lassen Sie uns sofort bestimmen, was die Standardabweichung ist und wie ihre Formel aussieht. Diese Größe ist die Quadratwurzel des arithmetischen Mittels der Quadrate der Differenz zwischen allen Größen in der Reihe und ihrem arithmetischen Mittel. Für diesen Indikator gibt es einen identischen Namen: Standardabweichung. Beide Namen sind völlig gleichwertig.

Aber in Excel muss der Benutzer dies natürlich nicht berechnen, da das Programm alles für ihn erledigt. Erfahren Sie, wie Sie die Standardabweichung in Excel berechnen.

Berechnung in Excel

Mit zwei speziellen Funktionen können Sie den angegebenen Wert in Excel berechnen STDEV.V(basierend auf der Stichprobenpopulation) und STDEV.G(basierend auf der Gesamtbevölkerung). Das Funktionsprinzip ist absolut das gleiche, sie können jedoch auf drei Arten aufgerufen werden, auf die wir weiter unten eingehen werden.

Methode 1: Funktionsassistent

Methode 2: Registerkarte „Formeln“.

Methode 3: Manuelle Eingabe der Formel

Es gibt auch eine Möglichkeit, den Aufruf des Argumentfensters überhaupt zu vermeiden. Dazu müssen Sie die Formel manuell eingeben.

Wie Sie sehen, ist der Mechanismus zur Berechnung der Standardabweichung in Excel sehr einfach. Der Benutzer muss lediglich Zahlen aus der Grundgesamtheit oder Verweise auf die Zellen eingeben, die sie enthalten. Alle Berechnungen werden vom Programm selbst durchgeführt. Es ist viel schwieriger zu verstehen, was der berechnete Indikator ist und wie die Berechnungsergebnisse in der Praxis angewendet werden können. Aber das zu verstehen, bezieht sich bereits mehr auf den Bereich der Statistik als auf das Erlernen des Umgangs mit Software.