Abstract

Sie haben 11.256 Weihnachtsbäume. Ein Kunde möchte Ihnen 1.500 davon abkaufen. Die einzige Bedingung: die durchschnittliche Länge soll 6,50 Meter betragen.

Sie würden nun gern die Restmenge Ihrer Weihnachtsbäume möglichst normalverteilt haben:

Wie können Sie dies erreichen?

Eine Beispielrechnung

Angenommen, Sie haben die oben gezeigte Menge an Bäumen mit den angegebenen Längen.

Eine erste interessante Rechnung ist sicherlich, inwieweit Ihre Ausgangsmenge bereits normalverteilt ist. Die Schiefe ermitteln wir mittels der Funktion sbSWV: sie beträgt gerundet =sbSWV(“SKEW.P”;$A$4:$A$21;B$4:B$21) = -0,35. Der Exzess (Maß für die Wölbung) beträgt gerundet =sbSWV(“KURT”;$A$4:$A$21;B$4:B$21) = 0,95. Wie wir am oben gezeigten Diagramm am gelb-orangen Graphen sehen können, ist diese Ausgangsmenge bereits “einigermaßen” normalverteilt.

Idealerweise wäre diese Stichprobe allerdings verteilt wie in Spalte C gezeigt mit der Formel =MTRANS(RoundToSum(NORM.VERT(A4:A21;B24;B25;FALSCH)*B22/10;0)): die Schiefe und der Exzess wären gleich Null (die vorgenommenen Rundungen führen zu leichten Abweichungen).

Spalte H zeigt die ideale Restmengenverteilung nach Entnahme.

Mit der in Spalte D gezeigten automatischen Entnahme versuchen wir, diese ideale Restmenge möglichst zu erreichen. Dies kann natürlich nur gelingen, wenn wir ausreichend viele Bäume in den jeweiligen Längen zur Verfügung haben. Wo dies nicht der Fall ist können wir leider keine Bäume hinzufügen und müssen als Entnahmemenge 0 eintragen - im Diagramm sehen Sie z. B., dass die ideale Verteilung bei Länge 6,10 m höher ist als die tatsächliche Restverteilung.

Die ursprünglichen Formeln in Spalte F sollten =D4 bis =D21 lauten.

Diese überschreiben wir nun mit manuellen Werten, um

• auf eine Gesamtentnahme von genau 1.500 Bäumen,
• auf einen Mittelwert von 6,5 Länge der Bäume,
• auf etwa die gleiche Standardabweichung (Streuung) der Restmenge wie der Originalmenge,
• auf eine betragsmäßig kleinere Schiefe als bei der Originalmenge
• und auf einen betragsmäßig kleineren Exzess als bei der Originalmenge

zu kommen.

In der unten angebotenen Beispieldatei werden erhöhte Abweichungen durch bedingte Formatierungen angezeigt.

Anmerkung: Es ist nicht immer möglich, eine “hinreichend” normal verteilte Restmenge zu erhalten. Wie man einfach sehen kann, ist manchmal nicht einmal eine gewünschte Durchschnittslänge zu erreichen - fragen Sie bei der oben gezeigten Menge z. B. nach 21 Bäumen mit der Durchschnittslänge 5,60 m.

Hilfsfunktionen

Excel verfügt zwar über viele statistische Grundfunktionen. Diese sind jedoch nicht in der Lage, gewichtete Werte zu verarbeiten. Die hier verwendete benutzerdefinierte Funktion sbSWV (engl. statistics for weighted values) ermöglicht eine einfache und schnelle Anzeige, wie gut die Stichproben normalverteilt sind.

Damit die Summen der ganzzahligen Idealverteilungen der Stichproben genau mit den Summen der originalen Stichproben übereinstimmen, wurde die benutzerdefinierte Funktion RoundToSum verwendet. Man beachte, dass hierbei der Parameter 2 für den Fehlertyp zur Minimierung des relativen Fehlers gewählt wurde. Dies verhindert künstliche Rundungen zur “falschen” Seite in den Außenbereichen der Verteilungen.

Download

Bitte den Haftungsausschluss im Impressum beachten.

stichprobe_normalverteilen.xlsm [41 KB Excel Datei, Download und Nutzung auf eigene Gefahr]