Günter Redl, Höhere technische Bundeslehr- und Versuchsanstalt Mödling
 
Statistische Verteilungen mit Excel

Kurzzusammenfassung:

An 3 einfachen Beispielen aus der Wahrscheinlichkeitsrechnung werden drei der in Excel eingebauten Verteilungsfunktionen vorgestellt.
Lehrplanbezug:
3. und 4. Jahrgang: Wahrscheinlichkeitsrechnung; Statistik
Zeitaufwand:
1 bis 2 Doppelstunden
Mediales Umfeld:
PC oder Laptop mit Excel
Datei zum Herunterladen: Verteilung.xls
1. Hypergeometrische Verteilung:
 
a. Anwendung:
    Von einer Grundgesamtheit N ist die Anzahl d der fehlerhaften Einheiten bekannt. Eine Stichprobe vom Umfang n wird ohne Zurücklegen entnommen. P(x) ist die Wahrscheinlichkeit, in der Stichprobe genau x fehlerhafte Einheiten zu finden. Es gelten folgende Formeln und Zusammenhänge:

    Wahrscheinlichkeitsfunktion g(x): 

    Verteilungsfunktion: 

    Erwartungswert: wobei  ist

    Varianz: 

    b. Musterbeispiel:

    Ein Junggeselle kauft im Sonderangebot 100 Eier, darunter 15 verdorbene. Er bereitet sich aus 4 Eiern eine Eierspeise zu. Berechne die Wahrscheinlichkeitsfunktion g(x) sowie die Verteilungsfunktion G(x) für die Wahrscheinlichkeiten, dass sich unter den 4 Eiern 0,1,2,3 oder 4 verdorbene befinden. Berechne ferner den Mittelwert und die Streuung. Wie groß ist die Wahrscheinlichkeit, dass der Junggeselle am nächsten Tag krank ist (mindestens 1 Ei war verdorben)?

    c. Lösung mit Excel:

Zunächst werden die Grunddaten 100, 15 und 4 eingegeben. Weiters wird die Tabelle vorbereitet. Ausserdem werden m und s durch einfache Formeln berechnet:

In der Zelle B8 wird nun die Funktion eingegeben. Man ruft mit Hilfe des Funktionsassistenten die Funktion "HYPGEOMVERT" auf und trägt im entstehenden Dialogfenster die entsprechenden Zelladressen ein. Achten Sie auf die Hilfstexte sowie auf absolute und relative Zelladressierung!

In die Zelle C8 (1. Wert der Spalte für G(x)) kommt folgende Formel: "=SUMME($B$8:B8)". Achten Sie auch hier auf die absolute und relative Adressierung der Bereichsgrenzen! Die Tabelle wurde noch um die Spalte "1-G(x)" erweitert und die entsprechende Formel in die Zelle D8 eingegeben (=1-B8).Durch Kopieren der Zellen B8 bis D8 nach unten vervollständigt man nun die Tabelle.

Die Frage nach der Wahrscheinlichkeit, dass mindestens 1 Ei verdorben war, wird durch 1-G(0) beantwortet. Ergebnis: Die Wahrscheinlichkeit, dass der Junggeselle krank ist, beträgt 48,4%!

2. Binomialverteilung:

a. Anwendung:
    Von einer Grundgesamtheit ist der Prozentsatz p fehlerhaften Einheiten bekannt. Eine Stichprobe vom Umfang n wird entnommen.

    2. Möglichkeit: Von einer Grundgesamtheit N ist die Anzahl d der fehlerhaften Einheiten bekannt. Eine Stichprobe vom Umfang n wird mit Zurücklegen entnommen. In diesem Fall gilt .
    P(x) ist die Wahrscheinlichkeit, in der Stichprobe genau x fehlerhafte Einheiten zu finden. Es gelten folgende Formeln und Zusammenhänge:
    Wahrscheinlichkeitsfunktion g(x): 
    Verteilungsfunktion: 
    Erwartungswert: 

    Varianz: 

    b. Beispiel:

    Polizeilichen Statistiken zufolge beträgt der Anteil der Autolenker, die während der Fahrt keinen Sicherheitsgurt tragen, 15%. Diese werden in Folge als Gurtenmuffel bezeichnet. 20 Lenker werden überprüft. Wie groß ist die Wahrscheinlichkeit:

    I) mindestens 4 Gurtenmuffel zu ertappen ?
    II) dass mehr als 12 Lenker angegurtet sind ?

    c. Lösung mit Excel:

Zunächst wird wieder die Tabelle vorbereitet, die Grunddaten werden eingegeben und Erwartungswert und Varianz werden durch einfache Formeln berechnet.

Um die Tabelle zu vervollständigen, wird in der Zelle B6 die Funktion "BINOMVERT" mit Hilfe des Funktionsassistenten aufgerufen und das Dialogfenster vervollständigt.

Neu ist hier das Feld "Kumuliert". Der Eingabewert "falsch" bewirkt das Berechnen der Wahrscheinlichkeitsfunktion, "wahr" hingegen das Berechnen der Verteilungsfunktion. Man kann also auch in der Spalte der Verteilungsfunktion die Funktion "BINOMVERT" benutzen. In C6 wird die Funktion nochmals eingegeben:

Durch Kopieren der Zellen B6 bis D6 nach unten wird die Tabelle vervollständigt.

Antworten:

Die Wahrscheinlichkeit, mindestens 4 Gurtenmuffel zu ertappen, beträgt 35,2% (1-G(3)).
Die Wahrscheinlichkeit, dass mehr als 12 Lenker angegurtet sind (also höchstens 8 Gurtenmuffel ertappt werden), beträgt 99,9% (G(8)).

3. Normalverteilung:

a. Anwendung:
    Messwerte können stetige Zufallsvariablen aufgefasst werden. Sie sind im allgemeinen normalverteilt (Gauß-Glockenkurve). Für eine Normalverteilung mit Mittelwert m und Standardabweichung s gelten folgende Formeln:

    Dichtefunktion:  (Gleichung der Glockenkurve)

    Verteilungsfunktion: 
    b. Beispiel:

Die Roheinwaagen von Gemüsekonserven sind normalverteilt mit m = 400g und s = 8g. Wieviel Ausschuss ist zu erwarten, wenn die Roheinwaage
I) mindestens 385g haben soll ?
II) maximal 12g vom Mittelwert abweichen darf ?
III) In welchem Bereich rund um den Mittelwert liegen 95% aller Konserven ?

c. Lösung mit Excel:

Der Aufbau der Tabelle erfolgt ähnlich wie bei den ersten Beispielen.

Die Spalten g(x) (Dichtefunktion) sowie 1-G(x) wurden nur der Vollständigkeit halber eingetragen. In den Zellen C6 und D6 wird die Funktion "NORMVERT" eingetragen und anschließend nach unten kopiert.

Kumuliert "Falsch" liefert als Ergebnis die Dichtefunktion g(x), "WAHR" die Verteilungsfunktion G(x).


 

d. Ergebnisse:
I) Der Ausschuss bei einem Mindestgewicht von 385g wird durch G(385) = 3% beantwortet.
II) Ein maximale Abweichung um 12g vom Mittelwert bedeutet mindestens 388g und höchstens 412g. Man nützt die Symmetrie der Glockenkurve aus und rechnet 2*G(388) = 13,4%. In der Zelle D8 steht "=2*D7".
III) Zunächst wird in der Zelle B10 ein beliebieger (vernünftiger)Wert eingetragen. In B9 steht die Formel "=800-B10". Im Bereich 400± 10g liegen jedoch nur G(410)-G(390) = 78,9%.

Man ruft nun über das menu "Extras" den Befehl "Zielwertsuche" auf. Im Dialogfenster wird folgendes eingetragen:

Nach dem Klick auf OK erhalten wir:

Damit ist als Lösung der Bereich 400± 15,7g gefunden.

4. Bemerkungen:

Es wurden nur 3 der vielen in Excel vorhandenen Statistik- und Wahrscheinlichkeitsfunktionen vorgestellt.
Weiters soll auf die Möglichkeiten hingewiesen werden, die Verteilungen auch graphisch darzustellen.