Entfernen Sie Werte, die größer als die Standardabweichung sind, aus Excel. Berechnung von Streuung, Standardabweichung (Standard), Variationskoeffizient in Excel

Anweisungen

Es gebe mehrere Zahlen, die homogene Größen charakterisieren. Zum Beispiel die Ergebnisse von Messungen, Wägungen, statistischen Beobachtungen usw. Alle angegebenen Größen müssen mit der gleichen Messung gemessen werden. Um die Standardabweichung zu ermitteln, gehen Sie wie folgt vor:

Bestimmen Sie das arithmetische Mittel aller Zahlen: Addieren Sie alle Zahlen und dividieren Sie die Summe durch die Gesamtzahl der Zahlen.

Bestimmen Sie die Streuung (Streuung) der Zahlen: Addieren Sie die Quadrate der zuvor gefundenen Abweichungen und dividieren Sie die resultierende Summe durch die Anzahl der Zahlen.

Auf der Station liegen sieben Patienten mit Temperaturen von 34, 35, 36, 37, 38, 39 und 40 Grad Celsius.

Es ist erforderlich, die durchschnittliche Abweichung vom Mittelwert zu ermitteln.
Lösung:
„auf der Station“: (34+35+36+37+38+39+40)/7=37 ºС;

Temperaturabweichungen vom Durchschnitt (in diesem Fall dem Normalwert): 34-37, 35-37, 36-37, 37-37, 38-37, 39-37, 40-37, daraus ergibt sich: -3, - 2, -1, 0, 1, 2, 3 (ºС);

Teilen Sie die Summe der zuvor erhaltenen Zahlen durch ihre Anzahl. Für genaue Berechnungen ist es besser, einen Taschenrechner zu verwenden. Das Ergebnis der Division ist das arithmetische Mittel der addierten Zahlen.

Achten Sie auf alle Phasen der Berechnung, da bereits ein Fehler in einer der Berechnungen zu einem falschen Endindikator führt. Überprüfen Sie Ihre Berechnungen in jeder Phase. Der arithmetische Durchschnitt hat den gleichen Zähler wie die summierten Zahlen, das heißt, wenn Sie die durchschnittliche Anwesenheit ermitteln, sind alle Ihre Indikatoren „Person“.

Diese Methode Berechnungen werden nur in mathematischen und statistischen Berechnungen verwendet. Beispielsweise hat das arithmetische Mittel in der Informatik einen anderen Berechnungsalgorithmus. Das arithmetische Mittel ist ein sehr relativer Indikator. Es zeigt die Wahrscheinlichkeit eines Ereignisses an, sofern es nur einen Faktor oder Indikator hat. Für eine tiefgreifende Analyse müssen viele Faktoren berücksichtigt werden. Zu diesem Zweck wird die Berechnung allgemeinerer Größen verwendet.

Das arithmetische Mittel ist eines der zentralen Tendenzmaße, das in der Mathematik und in statistischen Berechnungen weit verbreitet ist. Das Ermitteln des arithmetischen Mittels für mehrere Werte ist sehr einfach, aber jede Aufgabe hat ihre eigenen Nuancen, die man einfach kennen muss, um korrekte Berechnungen durchführen zu können.

Quantitative Ergebnisse ähnlicher Experimente.

So ermitteln Sie das arithmetische Mittel

Um das arithmetische Mittel für eine Reihe von Zahlen zu ermitteln, sollte man zunächst die algebraische Summe dieser Werte bestimmen. Wenn das Array beispielsweise die Zahlen 23, 43, 10, 74 und 34 enthält, beträgt ihre algebraische Summe 184. Beim Schreiben wird das arithmetische Mittel mit dem Buchstaben μ (mu) oder x (x mit a) bezeichnet Bar). Als nächstes sollte die algebraische Summe durch die Anzahl der Zahlen im Array geteilt werden. Im betrachteten Beispiel gab es fünf Zahlen, sodass das arithmetische Mittel 184/5 beträgt und 36,8 beträgt.

Merkmale der Arbeit mit negativen Zahlen

Wenn das Array negative Zahlen enthält, wird das arithmetische Mittel mit einem ähnlichen Algorithmus ermittelt. Der Unterschied besteht nur bei der Berechnung in einer Programmierumgebung oder wenn das Problem darin besteht zusätzliche Bedingungen. In diesen Fällen ist das Ermitteln des arithmetischen Mittels von Zahlen mit verschiedene Zeichen läuft auf drei Schritte hinaus:

1. Ermitteln des allgemeinen arithmetischen Durchschnitts mithilfe der Standardmethode;
2. Ermitteln des arithmetischen Mittels negativer Zahlen.
3. Berechnung des arithmetischen Mittels positiver Zahlen.

Die Antworten für jede Aktion werden durch Kommas getrennt geschrieben.

Natürliche und dezimale Brüche

Wenn ein Array von Zahlen dargestellt wird Dezimalstellen, die Lösung erfolgt nach der Methode der Berechnung des arithmetischen Mittels ganzer Zahlen, das Ergebnis wird jedoch entsprechend den Anforderungen des Problems an die Genauigkeit der Antwort reduziert.

Bei der Arbeit mit natürlichen Brüchen sollten diese auf einen gemeinsamen Nenner reduziert werden, der mit der Anzahl der Zahlen im Array multipliziert wird. Der Zähler der Antwort ist die Summe der angegebenen Zähler der ursprünglichen Bruchelemente.

Guten Tag

In diesem Artikel habe ich beschlossen, mir anzusehen, wie die Standardabweichung in Excel mithilfe der Funktion STANDARDEVAL funktioniert. Ich habe es einfach schon sehr lange nicht mehr beschrieben oder kommentiert, und das auch einfach, weil es für diejenigen, die studieren, eine sehr nützliche Funktion ist Höhere Mathematik. Und Schülern zu helfen ist heilig; ich weiß aus Erfahrung, wie schwierig es ist, es zu meistern. In Wirklichkeit können Standardabweichungsfunktionen verwendet werden, um die Stabilität der verkauften Produkte zu bestimmen, Preise zu erstellen, ein Sortiment anzupassen oder zusammenzustellen und andere ebenso nützliche Analysen Ihrer Verkäufe durchzuführen.

Excel verwendet mehrere Variationen dieser Varianzfunktion:

Mathematische Theorie

Zunächst ein wenig zur Theorie, wie eine Funktion in mathematischer Sprache beschrieben werden kann Standardabweichung zur Verwendung in Excel, zum Beispiel zur Analyse von Verkaufsstatistikdaten, aber dazu später mehr. Ich warne Sie sofort, ich werde viele unverständliche Wörter schreiben...)))), wenn unten im Text etwas steht, suchen Sie sofort nach einer praktischen Anwendung im Programm.

Was genau bewirkt die Standardabweichung? Es wird eine Schätzung der Standardabweichung erstellt Zufallsvariable X relativ zu seiner mathematischen Erwartung basierend auf einer unvoreingenommenen Schätzung seiner Varianz. Stimmen Sie zu, es klingt verwirrend, aber ich denke, die Schüler werden verstehen, wovon wir eigentlich reden!

Zuerst müssen wir die „Standardabweichung“ ermitteln, um anschließend die „Standardabweichung“ zu berechnen, dabei hilft uns die Formel: Die Formel kann wie folgt beschrieben werden: Sie wird in denselben Einheiten gemessen wie die Messungen einer Zufallsvariablen und wird bei der Berechnung des arithmetischen Standardfehlers bei der Erstellung von Konstruktionen verwendet Konfidenzintervalle, beim Testen von Hypothesen für Statistiken oder bei der Analyse einer linearen Beziehung zwischen unabhängigen Größen. Die Funktion ist definiert als Quadratwurzel aus der Varianz der unabhängigen Variablen.

Jetzt können wir und definieren Standardabweichung ist eine Analyse der Standardabweichung einer Zufallsvariablen X relativ zu ihrer mathematischen Perspektive basierend auf einer unvoreingenommenen Schätzung ihrer Varianz. Die Formel ist so geschrieben:
Ich stelle fest, dass alle beiden Schätzungen verzerrt sind. Bei allgemeine Fälle Es ist nicht möglich, eine unvoreingenommene Schätzung zu erstellen. Aber eine Schätzung, die auf einer Schätzung der erwartungstreuen Varianz basiert, wird konsistent sein.

Praktische Umsetzung in Excel

Lassen Sie uns nun von der langweiligen Theorie Abstand nehmen und in der Praxis sehen, wie die Funktion STANDARDEVAL funktioniert. Ich werde nicht alle Variationen der Standardabweichungsfunktion in Excel betrachten; eine reicht aus, aber in Beispielen. Schauen wir uns als Beispiel an, wie Verkaufsstabilitätsstatistiken ermittelt werden.

Schauen Sie sich zunächst die Schreibweise der Funktion an. Wie Sie sehen, ist sie ganz einfach:

STANDARDABWEICHUNG.G(_number1_;_number2_; ....), wobei:

Lassen Sie uns nun eine Beispieldatei erstellen und darauf aufbauend überlegen, wie diese Funktion funktioniert. Da für analytische Berechnungen mindestens drei Werte verwendet werden müssen, wie im Prinzip bei jeder statistischen Analyse, habe ich bedingt 3 Zeiträume verwendet, dies kann ein Jahr, ein Quartal, ein Monat oder eine Woche sein. In meinem Fall - einen Monat. Für maximale Zuverlässigkeit empfehle ich, so viele wie möglich einzunehmen große Zahl Perioden, jedoch nicht weniger als drei. Alle Daten in der Tabelle sind sehr einfach gehalten, um die Funktionsweise und Funktionalität der Formel zu verdeutlichen.

Zuerst müssen wir den Durchschnittswert pro Monat berechnen. Wir verwenden hierfür die Funktion AVERAGE und erhalten die Formel: = AVERAGE(C4:E4).
Jetzt können wir tatsächlich die Standardabweichung mithilfe der Funktion STANDARDEVAL.G ermitteln, in deren Wert wir die Verkäufe des Produkts für jeden Zeitraum eingeben müssen. Das Ergebnis ist eine Formel der folgenden Form: =STANDARDABWEICHUNG.Г(C4;D4;E4).
Nun, die Hälfte der Arbeit ist getan. Der nächste Schritt besteht darin, eine „Variation“ zu bilden. Diese wird durch Division durch den Durchschnittswert, die Standardabweichung und die Umrechnung des Ergebnisses in Prozentsätze erhalten. Wir erhalten die folgende Tabelle:
Nun, die Grundberechnungen sind abgeschlossen, es bleibt nur noch herauszufinden, ob der Umsatz stabil ist oder nicht. Nehmen wir als Bedingung an, dass Abweichungen von 10 % als stabil gelten, von 10 bis 25 % sind kleine Abweichungen, aber alles über 25 % ist nicht mehr stabil. Um das Ergebnis gemäß den Bedingungen zu erhalten, verwenden wir eine logische Eins und um das Ergebnis zu erhalten, schreiben wir die Formel:

WENN(H4<0,1;"стабильно";ЕСЛИ(H4<0,25;"нормально";"не стабильно"))

Alle Bereiche dienen der Übersichtlichkeit; Ihre Aufgaben können völlig unterschiedliche Bedingungen haben.
Um die Datenvisualisierung zu verbessern, sollten Sie bei Tausenden von Positionen in Ihrer Tabelle die Möglichkeit nutzen, bestimmte Bedingungen anzuwenden, die Sie benötigen, oder bestimmte Optionen mit einem Farbschema hervorzuheben. Dies wird sehr deutlich sein.

Wählen Sie zunächst diejenigen aus, auf die Sie die bedingte Formatierung anwenden möchten. Wählen Sie in der Systemsteuerung „Home“ „Bedingte Formatierung“ und im Dropdown-Menü „Regeln zum Hervorheben von Zellen“ und klicken Sie dann auf den Menüpunkt „Text enthält ...“. Es erscheint ein Dialogfenster, in dem Sie Ihre Konditionen eingeben.

Nachdem Sie die Bedingungen aufgeschrieben haben, zum Beispiel „stabil“ – grün, „normal“ – gelb und „instabil“ – rot, erhalten wir eine schöne und verständliche Tabelle, in der Sie sehen können, worauf Sie zuerst achten sollten.

Verwendung von VBA für die STDEV.Y-Funktion

Jeder Interessierte kann seine Berechnungen mithilfe von Makros automatisieren und die folgende Funktion nutzen:

Funktion MyStDevP(Arr) Dim x, aCnt&, aSum#, aAver#, tmp# Für jedes x In Arr aSum = aSum + x „berechne die Summe der Array-Elemente aCnt = aCnt + 1“ berechne die Anzahl der Elemente Next x aAver = aSum / aCnt "Durchschnittswert für jedes x In Arr tmp = tmp + (x - aAver) ^ 2 "Berechnen Sie die Summe der Quadrate der Differenz zwischen den Array-Elementen und den Durchschnittswert Next x MyStDevP = Sqr(tmp / aCnt ) "Berechnen Sie die Endfunktion STANDARDEV.G()

Funktion MyStDevP(Arr) Dim x, aCnt &, aSum #, aAver#, tmp# Für jedes x in Arr aSumme = aSumme + x „Berechnen Sie die Summe der Array-Elemente

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 in 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 werden die Funktion verwenden: =AVERAGEIF().

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

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

Das Ergebnis der Verwendung der AVERAGEIF-Funktion unter der Bedingung „>=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“ (anstelle des Links A7 können Sie 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.

In diesem Artikel werde ich darüber sprechen wie man die Standardabweichung ermittelt. Dieses Material ist für ein umfassendes Verständnis der Mathematik äußerst wichtig, daher sollte ein Mathematiklehrer dem Studium eine oder sogar mehrere Unterrichtsstunden widmen. In diesem Artikel finden Sie einen Link zu einem ausführlichen und verständlichen Video-Tutorial, das erklärt, was Standardabweichung ist und wie man sie findet.

Standardabweichung ermöglicht die Auswertung der Streuung der Werte, die sich aus der Messung eines bestimmten Parameters ergeben. Angezeigt durch das Symbol (griechischer Buchstabe „Sigma“).

Die Berechnungsformel ist recht einfach. Um die Standardabweichung zu ermitteln, müssen Sie die Quadratwurzel der Varianz ziehen. Nun müssen Sie sich fragen: „Was ist Varianz?“

Was ist Varianz?

Die Definition der Varianz lautet wie folgt. Die Streuung ist das arithmetische Mittel der quadrierten Abweichungen der Werte vom Mittelwert.

Um die Varianz zu ermitteln, führen Sie nacheinander die folgenden Berechnungen durch:

• Bestimmen Sie den Durchschnitt (einfaches arithmetisches Mittel einer Reihe von Werten).
• Subtrahieren Sie dann den Durchschnitt von jedem Wert und quadrieren Sie die resultierende Differenz (Sie erhalten). quadrierte Differenz).
• Der nächste Schritt besteht darin, das arithmetische Mittel der resultierenden quadrierten Differenzen zu berechnen (Warum genau die Quadrate, erfahren Sie weiter unten).

Schauen wir uns ein Beispiel an. Nehmen wir an, Sie und Ihre Freunde beschließen, die Größe Ihres Hundes (in Millimetern) zu messen. Als Ergebnis der Messungen haben Sie folgende Höhenmaße (Widerristhöhe) erhalten: 600 mm, 470 mm, 170 mm, 430 mm und 300 mm.

Berechnen wir den Mittelwert, die Varianz und die Standardabweichung.

Lassen Sie uns zunächst den Durchschnittswert ermitteln. Wie Sie bereits wissen, müssen Sie dazu alle Messwerte addieren und durch die Anzahl der Messungen dividieren. Berechnungsfortschritt:

Durchschnittlich mm.

Der Durchschnitt (arithmetisches Mittel) beträgt also 394 mm.

Jetzt müssen wir feststellen Abweichung der Körpergröße jedes Hundes vom Durchschnitt:

Endlich, Varianz berechnen, quadrieren wir jede der resultierenden Differenzen und ermitteln dann das arithmetische Mittel der erhaltenen Ergebnisse:

Streuung mm 2 .

Somit beträgt die Streuung 21704 mm 2.

So ermitteln Sie die Standardabweichung

Wie können wir nun die Standardabweichung berechnen, wenn wir die Varianz kennen? Wie wir uns erinnern, ziehen Sie daraus die Quadratwurzel. Das heißt, die Standardabweichung ist gleich:

Mm (auf die nächste ganze Zahl in mm gerundet).

Mit dieser Methode haben wir herausgefunden, dass einige Hunde (z. B. Rottweiler) sehr große Hunde sind. Es gibt aber auch sehr kleine Hunde (zum Beispiel Dackel, aber das sollte man ihnen nicht sagen).

Das Interessanteste ist, dass die Standardabweichung nützliche Informationen enthält. Jetzt können wir zeigen, welche der erhaltenen Höhenmessergebnisse innerhalb des Intervalls liegen, das wir erhalten, wenn wir die Standardabweichung vom Durchschnitt (zu beiden Seiten davon) auftragen.

Das heißt, mithilfe der Standardabweichung erhalten wir eine „Standard“-Methode, mit der wir herausfinden können, welcher der Werte normal (statistisch durchschnittlich) und welcher außergewöhnlich groß oder umgekehrt klein ist.

Was ist Standardabweichung?

Aber... alles wird ein wenig anders sein, wenn wir es analysieren Probe Daten. In unserem Beispiel haben wir darüber nachgedacht allgemeine Bevölkerung. Das heißt, unsere 5 Hunde waren die einzigen Hunde auf der Welt, die uns interessierten.

Handelt es sich bei den Daten jedoch um eine Stichprobe (aus einer großen Grundgesamtheit ausgewählte Werte), müssen die Berechnungen anders durchgeführt werden.

Wenn es Werte gibt, dann:

Alle weiteren Berechnungen erfolgen analog, auch die Ermittlung des Durchschnitts.

Wenn unsere fünf Hunde beispielsweise nur eine Stichprobe der Hundepopulation (alle Hunde auf dem Planeten) darstellen, müssen wir durch dividieren 4, nicht 5, nämlich:

Stichprobenvarianz = mm 2.

In diesem Fall ist die Standardabweichung für die Stichprobe gleich mm (auf die nächste ganze Zahl gerundet).

Wir können sagen, dass wir einige „Korrekturen“ vorgenommen haben, wenn es sich bei unseren Werten nur um eine kleine Stichprobe handelt.

Notiz. Warum genau quadrierte Differenzen?

Aber warum nehmen wir bei der Berechnung der Varianz genau die quadrierten Differenzen? Nehmen wir an, Sie haben beim Messen eines Parameters die folgenden Werte erhalten: 4; 4; -4; -4. Wenn wir einfach die absoluten Abweichungen vom Mittelwert (Differenzen) zusammenzählen... heben sich die negativen Werte mit den positiven auf:

.

Es stellt sich heraus, dass diese Option nutzlos ist. Dann lohnt es sich vielleicht, die Absolutwerte der Abweichungen (also die Module dieser Werte) auszuprobieren?

Auf den ersten Blick sieht es gut aus (der resultierende Wert wird übrigens als mittlere absolute Abweichung bezeichnet), aber nicht in allen Fällen. Versuchen wir es mit einem anderen Beispiel. Lassen Sie die Messung zu folgendem Wertesatz führen: 7; 1; -6; -2. Dann beträgt die durchschnittliche absolute Abweichung:

Wow! Auch hier haben wir ein Ergebnis von 4 erhalten, obwohl die Unterschiede deutlich größer sind.

Sehen wir uns nun an, was passiert, wenn wir die Differenzen quadrieren (und dann die Quadratwurzel aus ihrer Summe ziehen).

Für das erste Beispiel wird es sein:

.

Für das zweite Beispiel wird es sein:

Jetzt ist es eine ganz andere Sache! Je größer die Streuung der Unterschiede ist, desto größer ist die Standardabweichung ... und genau das haben wir angestrebt.

Tatsächlich basiert diese Methode auf derselben Idee wie bei der Berechnung des Abstands zwischen Punkten, wird jedoch auf andere Weise angewendet.

Und aus mathematischer Sicht bietet die Verwendung von Quadraten und Quadratwurzeln mehr Vorteile, als wir mit absoluten Abweichungswerten erzielen könnten, sodass die Standardabweichung auf andere mathematische Probleme anwendbar ist.

Sergey Valerievich hat Ihnen erklärt, wie Sie die Standardabweichung ermitteln

Die Standardabweichungsfunktion gehört bereits zur Kategorie der höheren Mathematik mit Bezug zur Statistik. Es gibt mehrere Möglichkeiten, die Standardabweichungsfunktion in Excel zu verwenden:

• STANDARDEV-Funktion.
• Funktion STANDARDABWEICHUNG.
• STDEV-Funktion

Wir werden diese Funktionen in der Verkaufsstatistik benötigen, um die Stabilität der Verkäufe zu ermitteln (XYZ-Analyse). Diese Daten können sowohl für die Preisgestaltung als auch für die Erstellung (Anpassung) der Sortimentsmatrix und für andere nützliche Verkaufsanalysen verwendet werden, über die ich in zukünftigen Artikeln auf jeden Fall sprechen werde.

Vorwort

Schauen wir uns die Formeln zunächst in mathematischer Sprache an und analysieren dann (unten im Text) die Formel in Excel im Detail und wie das resultierende Ergebnis bei der Analyse von Verkaufsstatistiken verwendet wird.

Die Standardabweichung ist also eine Schätzung der Standardabweichung einer Zufallsvariablen X bezüglich seiner mathematischen Erwartung basierend auf einer unvoreingenommenen Schätzung seiner Varianz)))) Haben Sie keine Angst vor unverständlichen Worten, seien Sie geduldig und Sie werden alles verstehen!

Beschreibung der Formel: Die Standardabweichung wird in Einheiten der Zufallsvariablen selbst gemessen und bei der Berechnung des Standardfehlers des arithmetischen Mittels, bei der Konstruktion von Konfidenzintervallen, bei der statistischen Prüfung von Hypothesen und bei der Messung der linearen Beziehung zwischen Zufallsvariablen verwendet. Definiert als Quadratwurzel der Varianz der Zufallsvariablen

Nun ist die Standardabweichung eine Schätzung der Standardabweichung einer Zufallsvariablen X relativ zu seiner mathematischen Erwartung basierend auf einer unvoreingenommenen Schätzung seiner Varianz:

Streuung;

- ich tes Element der Auswahl;

Probengröße;

Arithmetisches Mittel der Stichprobe:

Es ist zu beachten, dass beide Schätzungen verzerrt sind. Im Allgemeinen ist es unmöglich, eine unvoreingenommene Schätzung zu erstellen. Die auf der unverzerrten Varianzschätzung basierende Schätzung ist jedoch konsistent.

Drei-Sigma-Regel() – fast alle Werte einer normalverteilten Zufallsvariablen liegen im Intervall. Genauer gesagt liegt der Wert einer normalverteilten Zufallsvariablen mit einer Wahrscheinlichkeit von etwa 0,9973 im angegebenen Intervall (vorausgesetzt, der Wert ist wahr und wurde nicht durch Stichprobenverarbeitung ermittelt). Wir verwenden ein gerundetes Intervall von 0,1

Wenn der wahre Wert unbekannt ist, sollten Sie not, but verwenden S. Somit wird die Drei-Sigma-Regel in die Drei-Regel umgewandelt S. Anhand dieser Regel können wir die Stabilität des Umsatzes bestimmen, aber dazu später mehr ...

Jetzt Standardabweichungsfunktion in Excel

Ich hoffe, ich habe dich mit Mathe nicht zu sehr gelangweilt? Vielleicht benötigt jemand diese Informationen für einen Aufsatz oder für andere Zwecke. Schauen wir uns nun an, wie diese Formeln in Excel funktionieren ...

Um die Stabilität des Umsatzes zu bestimmen, müssen wir uns nicht mit allen Optionen für die Standardabweichungsfunktionen befassen. Wir werden nur eines verwenden:

STDEV-Funktion

STABW(Nummer1;Nummer2;... )

Nummer1, Nummer2,..- 1 bis 30 numerische Argumente entsprechend der Gesamtbevölkerung.

Schauen wir uns nun ein Beispiel an:

Lasst uns ein Buch und einen provisorischen Tisch erstellen. Sie werden dieses Beispiel in Excel am Ende des Artikels herunterladen.

Fortgesetzt werden!!!

Hallo nochmal. Also!? Ich hatte eine freie Minute. Machen wir weiter?

Und so die Stabilität des Umsatzes mit der Hilfe STDEV-Funktionen

Nehmen wir zur Verdeutlichung ein paar improvisierte Dinge:

In der Analytik, sei es eine Prognose, eine Recherche oder irgendetwas anderes im Zusammenhang mit Statistiken, ist es immer notwendig, drei Zeiträume zu berücksichtigen. Dies kann eine Woche, ein Monat, ein Vierteljahr oder ein Jahr sein. Es ist möglich und sogar am besten, so viele Stunden wie möglich zu nehmen, jedoch nicht weniger als drei.

Ich habe speziell übertriebene Verkäufe gezeigt, bei denen mit bloßem Auge erkennbar ist, was sich regelmäßig verkauft und was nicht. Dies erleichtert das Verständnis der Funktionsweise der Formeln.

Da wir also Verkäufe haben, müssen wir nun die durchschnittlichen Verkaufswerte pro Zeitraum berechnen.

Die Formel für den Durchschnittswert lautet DURCHSCHNITT (Periodendaten), in meinem Fall sieht die Formel so aus = DURCHSCHNITT (C6: E6)

Wir wenden die Formel auf alle Produkte an. Dies erreichen Sie, indem Sie die rechte Ecke der ausgewählten Zelle anfassen und an das Ende der Liste ziehen. Oder platzieren Sie den Cursor auf der Spalte mit dem Produkt und drücken Sie die folgenden Tastenkombinationen:

Strg + Ab bewegt den Cursor an den Anfang der Liste.

Strg + Rechts, der Cursor bewegt sich auf die rechte Seite der Tabelle. Noch einmal nach rechts und wir gelangen zur Spalte mit der Formel.

Jetzt klemmen wir

Strg + Umschalt und drücken Sie nach oben. Auf diese Weise wählen wir den Bereich aus, in dem die Formel gezeichnet werden soll.

Und die Tastenkombination Strg + D zieht die Funktion dorthin, wo wir sie benötigen.

Denken Sie an diese Kombinationen. Sie erhöhen die Geschwindigkeit in Excel erheblich, insbesondere wenn Sie mit großen Arrays arbeiten.

Die nächste Stufe, die Standard-Abfahrtsfunktion selbst, wie ich bereits sagte, werden wir nur eine verwenden STABW

Wir schreiben die Funktion und tragen die Verkaufswerte jeder Periode in die Funktionswerte ein. Wenn Sie nacheinander Umsätze in der Tabelle haben, können Sie einen Bereich verwenden, wie in meiner Formel =STABW(C6:E6) oder die erforderlichen Zellen durch Semikolons getrennt auflisten =STABW(C6;D6;E6)

Jetzt sind alle Berechnungen fertig. Aber woher wissen Sie, was sich dauerhaft verkauft und was nicht? Setzen wir einfach die Konvention XYZ ein:

X ist stabil

Y - mit kleinen Abweichungen

Z – nicht stabil

Dazu verwenden wir Fehlerintervalle. Bei Schwankungen innerhalb von 10 % gehen wir von einem stabilen Umsatz aus.

Liegt der Wert zwischen 10 und 25 Prozent, ist es Y.

Und wenn der Variationswert 25 % überschreitet, handelt es sich nicht um Stabilität.

Um die Buchstaben jedem Produkt korrekt zuzuordnen, verwenden wir die IF-Formel. Erfahren Sie mehr über. In meiner Tabelle sieht diese Funktion folgendermaßen aus:

WENN(H6<0,1;"X";ЕСЛИ(H6<0,25;"Y";"Z"))

Dementsprechend erweitern wir alle Formeln für alle Namen.

Ich werde versuchen, die Frage sofort zu beantworten: Warum die Intervalle von 10 % und 25 %?

Tatsächlich können die Intervalle unterschiedlich sein, alles hängt von der konkreten Aufgabe ab. Ich habe Ihnen ausdrücklich überhöhte Verkaufswerte gezeigt, bei denen der Unterschied für das Auge sichtbar ist. Offensichtlich wird Produkt 1 nicht regelmäßig verkauft, aber die Dynamik zeigt eine Umsatzsteigerung. Wir lassen dieses Produkt in Ruhe...

Aber hier ist Produkt 2, es gibt bereits eine offensichtliche Destabilisierung. Und unsere Berechnungen zeigen Z, was uns sagt, dass die Verkäufe nicht stabil sind. Produkt 3 und Produkt 5 zeigen eine stabile Leistung. Bitte beachten Sie, dass die Abweichung innerhalb von 10 % liegt.

Diese. Produkt 5 mit den Werten 45, 46 und 45 weist eine Variation von 1 % auf, was einer stabilen Zahlenreihe entspricht.

Produkt 2 mit den Indikatoren 10, 50 und 5 weist jedoch eine Variation von 93 % auf, was KEINE stabile Zahlenreihe ist.

Nach allen Berechnungen können Sie einen Filter einsetzen und die Stabilität herausfiltern. Wenn Ihre Tabelle also aus mehreren tausend Artikeln besteht, können Sie ganz einfach hervorheben, welche Artikel nicht stabil im Verkauf sind oder welche im Gegenteil stabil sind.

„Y“ hat in meiner Tabelle nicht geklappt, ich denke, aus Gründen der Klarheit der Zahlenreihe muss es hinzugefügt werden. Ich werde Produkt 6 zeichnen...

Sie sehen, die Zahlenreihen 40, 50 und 30 weisen eine Abweichung von 20 % auf. Es scheint kein großer Fehler vorzuliegen, aber die Streuung ist dennoch erheblich ...

Und so zusammenfassend:

10.50.5 – Z ist nicht stabil. Abweichung mehr als 25 %

40,50,30 - Y Sie können diesem Produkt Aufmerksamkeit schenken und seinen Umsatz steigern. Abweichung weniger als 25 %, aber mehr als 10 %

45,46,45 - X ist Stabilität, Sie müssen mit diesem Produkt noch nichts tun. Abweichung weniger als 10 %

Das ist alles! Ich hoffe, ich habe alles klar erklärt. Wenn nicht, fragen Sie, was nicht klar ist. Und ich bin Ihnen für jeden Kommentar dankbar, sei es Lob oder Kritik. Auf diese Weise weiß ich, dass Sie mich lesen und dass Sie, was sehr WICHTIG ist, interessiert sind. Und dementsprechend werden neue Lektionen erscheinen.