Statistik Rechnerübungen

Labor Statistik - Beispiele für die
Einführungsveranstaltung, mit Lösungsweg
Prof. Dr. Peter Plappert
Fakultät Grundlagen
Achtung: Die vorliegende Datei ist für Excel 2010 bestimmt.
Für alle Beispiele benötigen Sie die Datei einf_d_2010.xlsx.
Für Beispiel 1 ist zusätzlich die Datei einf_b1.txt erforderlich.
Wenn Sie die nur die Fragestellungen (ohne Lösungswege) nachlesen wollen:
Datei einf_b_2010.pdf.
Wenn Sie die Endergebnisse vergleichen wollen:
Die Datei einf_d_2010_mit_ergebnissen.xlsx sieht so aus, wie Ihre Datendatei
einf_d_2010.xlsx nach Bearbeitung der vier Beispiele aussehen sollte.
Damit keine Missverständnisse auftreten: Die hier genannten Beispiele sind nicht die TestatAufgaben sr_aufg_1 bis sr_aufg_4, die Sie selbständig bearbeiten sollen. Beachten Sie auch,
dass zwar in hier vorliegenden Beispielen einige wichtige Statistik-Funktionen von Excel
erläutert werden, aber nicht alle Funktionen und Optionen angesprochen werden, die bei der
Bearbeitung der Testataufgaben benötigt werden. Im Rahmen der Einführungsstunde kann
auch nicht auf die grundlegende Bedienung von Excel eingegangen werden.
Im Folgenden wird für die meisten Fragestellungen nur ein möglicher Lösungsweg
vorgestellt. In vielen Fällen kann man die Beispiele auch auf andere Arten lösen, die hier nicht
alle erwähnt werden können.
Die Lösungsvorschläge sind eingerückt und eingerahmt. Müssen Sie aus einem
Menü auswählen, ist dies durch > Pfeil und Unterstreichung gekennzeichnet.
Beispiel 1
In diesem Beispiel sollen die Noten und Punktzahlen einer Klausur ausgewertet werden.
Die dazu erforderlichen Daten stehen liegen noch nicht in Excel-Form vor. Sie stehen in der Datei
einf_b1.txt in drei Feldern (laufende Nummer, Punktzahl, Note), die durch Leerzeichen getrennt
sind. Die Daten sollen in die Datei einf_d_2010.xlsx kopiert und dort weiterbearbeitet werden.
Folgende Schritte sollen durchgeführt werden:
Textdatei einlesen
a) Starten Sie Excel und öffnen Sie die Datei einf_d_2010.xlsx.
Wählen Sie das Verzeichnis, in dem sich die Datei einf_d_2010.xlsx befindet, und
> doppelklicken Sie auf die Datei.
Labor Statistik Einführungsveranstaltung
einf_b_2010_mit_loesungsweg.pdf, Seite 1/15
© P. Plappert 26.09.2011
b) Öffnen Sie die Datei einf_b1.txt mit Excel und kopieren Sie die Daten in das Tabellenblatt
„Noten-Daten“ der Datei einf_d_2010.xls, und zwar in die Felder A2 bis C43.
> Datei > Öffnen. Wählen Sie im sich öffnenden Fenster in der zweiten Zeile von
unten ganz rechts (das Feld, das sich rechts vom Eingabefeld für den Dateinamen
befindet) > Textdateien aus. Bei Windows-Versionen vor Vista müssen Sie (unten im
Fenster) > Dateityp > Textdateien auswählen.
Wählen Sie anschließend das Verzeichnis, in dem sich die Datei einf_b1.txt befindet,
und doppelklicken Sie auf die Datei. Es öffnet sich der Textkonvertierungs-Assistent.
In Schritt 1 müssen Sie entscheiden, ob die Daten in Spalten fester Breite stehen oder
ob die Felder mit bestimmten Trennzeichen getrennt sind. Hier ist letzteres der Fall;
wählen Sie also Dateityp > Getrennt; anschließend > Weiter.
In Schritt 2 muss das Trennzeichen zwischen den Feldern ausgewählt werden.
Entsprechend den vor a) gemachten Angaben wählen Sie Trennzeichen
> Leerzeichen; deaktivieren Sie „Tabstopp“ durch > Anklicken des Häkchens; dann
> weiter.
In Schritt 3 können Sie den Datentyp für jede Spalte festlegen. Wenn Sie die
Formatierung der laufenden Nummer behalten wollen, wählen Sie für die Spalte 1
> Text; (sonst wird z. B. aus Nummer 07 die Zahl 7). Die anderen Spalten können in
unserem Beispiel bei Datentyp „Standard“ belassen werden. Dann > Fertig stellen.
Markieren Sie nun die Felder mit den Notendaten, wählen Sie (im Menü > Start)
> Kopieren, wechseln Sie zur Datei einf_d_2010.xls, klicken Sie im Tabellenblatt
„Noten-Daten“ auf das Feld A2 und wählen Sie
> Einfügen (auf den kleinen Pfeil unter „Einfügen“ klicken!) > Werte einfügen
> Werte (ganz links im Untermenü „Werte einfügen“).
Labor Statistik Einführungsveranstaltung
einf_b_2010_mit_loesungsweg.pdf, Seite 2/15
© P. Plappert 26.09.2011
Häufigkeiten und Prozentanteile berechnen
c) Berechnen Sie die Häufigkeiten der Noten „sehr gut“ (1,0 und 1,3), „gut“ (1,7 bis 2,3),
„befriedigend“ (2,7 bis 3,3), „ausreichend“ (3,7 und 4,0) und „mangelhaft“ (4,7 und 5,0).
Die Ergebnisse sollen in den Feldern G3 bis G7 des Tabellenblatts „Noten-Daten“ stehen.
Wir benutzen den Excel-Befehl HÄUFIGKEIT. Damit Excel weiß, welche Klassen
wir bilden wollen, müssen wir zunächst eine Tabelle mit den Obergrenzen der
gewünschten Klassen anlegen. Geben Sie dazu in die Felder J3 bis J7 die in der
Abbildung gezeigten Werte von 1,3 bis 5 ein.
Markieren Sie anschließend die Felder, in die die Häufigkeiten eingetragen werden
sollen, also die Felder G3 bis G7 neben „sehr gut“ ... „mangelhaft“.
Wählen Sie dann > Formeln > Mehr Funktionen (Symbol
hier benötigte Funktion > HÄUFIGKEIT.
) > Statistisch und die
Geben Sie im Feld „Daten“ C2:C43 ein. Klicken Sie dann in das Feld „Klassen“ und
geben Sie dort J3:J7 ein.
Achtung: Klicken Sie nicht auf OK, denn HÄUFIGKEIT ist eine Matrix-Funktion,
die mehrere Felder gleichzeitig ausfüllt. Die Eingabe muss für Matrix-Funktionen
abgeschlossen werden durch gleichzeitiges Betätigen der Tasten
>Strg + Shift + Enter. (Shift = Hochstelltaste, Enter = Eingabetaste).
d) Ergänzen Sie bei c) die Summe.
Klicken Sie auf das Feld G8 und anschließend auf das Symbol
(AutoSumme)
Akzeptieren Sie den Vorschlag der zu summierenden Felder G3:G7 durch Betätigen
der Eingabetaste.
Labor Statistik Einführungsveranstaltung
einf_b_2010_mit_loesungsweg.pdf, Seite 3/15
© P. Plappert 26.09.2011
e) Berechnen Sie, welche prozentualen Anteile auf die Notenstufen „sehr gut“ bis „mangelhaft“
entfallen. Geben Sie die Prozentzahlen als ganze Zahlen (ohne Nachkommastellen) an.
Wir benutzen eine selbst eingegebene Berechnungsformel. Eine solche beginnt
immer mit einem Gleichheitszeichen =. Geben Sie in das Feld H3 die Formel
=100*G3/G8 ein, in das Feld H4 die Formel =100*G4/G8 usw. bis zu Feld H7.
Anschließend streichen wir der Übersichtlichkeit halber die Nachkommastellen, z. B.
indem wir die Felder H3 bis H7 markieren, im Menü > Start bei
> Format > Zellen formatieren im Register > Zahlen die Kategorie > Zahl wählen
und bei > Dezimalstellen 0 eingeben. Dann > OK.
Im Feld H8 können wir (so wie bei d) beschrieben) die Summe ergänzen.
Säulendiagramm erstellen
f) Stellen Sie die Häufigkeiten aus c) mit einem Säulendiagramm dar. Geben Sie dem Diagramm
einen passenden Titel, beschriften Sie die Achsen und entfernen Sie die Legende. Fügen Sie das
Diagramm als neues Tabellenblatt in die Datei einf_d_2010.xlsx ein.
Markieren Sie die Felder F3 bis G7. Wählen Sie > Einfügen > Säule, wobei Sie den
Untertyp ganz links bei „2D-Säule“ wählen Es wird ein Säulendiagramm erstellt.
Im Menü > Diagrammtools, das sich öffnet, wählen Sie > Layout > Diagrammtitel
> Über Diagramm und geben als Diagrammtitel z. B. „Notenverteilung“ ein.
Wählen Sie > Achsentitel > Titel der horizontalen Primärachse > Titel unter Achse
und beschriften Sie die x-Achse, indem Sie „Note“ eintippen und die > Eingabetaste
betätigen. Dann wählen Sie > Achsentitel > Titel der vertikalen Primärachse
> gedrehter Titel und beschriften Sie die y-Achse, indem Sie „Anzahl“ eintippen und
die > Eingabetaste betätigen.
Anschließend wählen Sie > Legende > Keine.
Schließlich ändern Sie den Speicherort des Diagramms, indem Sie im Menü
> Diagrammtools > Entwurf > Diagramm verschieben auswählen, > Neues Blatt
anklicken und dem Diagramm am besten einen „sprechenden“ Namen wie
„Diagramm Noten“ geben sowie auf > OK klicken.
g) Führen Sie in dem Diagramm die folgenden Umformatierungen durch:
g1) Die Farbe der Säulen soll dunkelblau sein.
In Excel-Diagrammen können Sie viele Elemente (z. B. Achsen, Säulen,
Beschriftungen...) umformatieren. In vielen Fällen öffnet sich ein passendes Menü,
wenn Sie mit der rechten Maustaste auf das zu formatierende Element klicken,.
Klicken Sie also im neuen Tabellenblatt „Diagramm Noten“ mit der > rechten
Maustaste auf eine der Säulen, dann auf den > kleinen Pfeil rechts neben dem
Symbol „Fülleffekt“
und schließlich auf das gewünschte > Farbkästchen
(dunkelblau).
Labor Statistik Einführungsveranstaltung
einf_b_2010_mit_loesungsweg.pdf, Seite 4/15
© P. Plappert 26.09.2011
g2) Jede Säule soll mit der zugehörigen Häufigkeit (wie oft gab es diese Note?) beschriftet
sein.
Klicken Sie mit der > rechten Maustaste auf eine der Säulen, dann auf
> Datenbeschriftungen hinzufügen.
g3) Die Beschriftung aus g2) soll in Schriftgröße 14pt in Fettschrift erscheinen.
Klicken Sie mit der > rechten Maustaste auf eine der Säulenbeschriftungen, wählen
Sie bei > Schriftgrad 14 und klicken Sie auf das > Symbol
für Fettdruck.
Kennzahlen berechnen
h) Berechnen Sie Mittelwert, empirische Varianz und empirische Standardabweichung der
Punktzahlen. Die Ergebnisse sollen in den Feldern H11 bis H13 des Tabellenblatts „NotenDaten stehen“ und mit 4 Nachkommastellen angegeben werden.
Gehen Sie wieder in das Tabellenblatt „Noten-Daten“ auf das Feld H11.
Wählen Sie > Formeln > Mehr Funktionen > Statistisch und die Funktion
> MITTELWERT.
Geben Sie bei Zahl1 den Datenbereich B2:B43 ein. > OK.
Für empirische Varianz und empirische Standardabweichung gehen Sie analog vor.
Die passenden Excel-Funktionen heißen VAR.S bzw. STABW.S.
Achtung: Da es mehrere Funktionen mit ähnlichen Namen gibt, müssen Sie Acht
geben, dass Sie nicht aus Versehen eine falsche Funktion anklicken.
Anschließend verringern Sie der Übersichtlichkeit halber die Anzahl der Nachkommastellen, z. B. indem Sie die Felder H11 bis H13 markieren, im Menü
> Start > Format > Zellen formatieren im Register > Zahlen die Kategorie > Zahl
wählen und bei > Dezimalstellen 4 eingeben. Dann > OK.
i) Berechnen Sie Median und Spannweite der Punktzahlen, ohne die Punktzahlenliste zu sortieren.
(Spannweite = größter Datenwert minus kleinster Datenwert.) Die Ergebnisse sollen in den
Feldern H14 und H15 des Tabellenblatts „Noten-Daten“ stehen.
Gehen Sie für den Median analog zu h) vor. Die Excel-Funktion heißt MEDIAN.
Für die Spannweite geben Sie in das Feld H15 die Berechnungsformel
=MAX(B2:B43)-MIN(B2:B43) ein.
j) Speichern Sie die geänderte Datei einf_d_2010.xlsx in Ihr persönliches Verzeichnis ab.
Wählen Sie > Datei > Speichern unter; wechseln Sie (falls erforderlich) das
Verzeichnis, in das Sie die Datei speichern möchten, und klicken Sie auf
> Speichern.
Labor Statistik Einführungsveranstaltung
einf_b_2010_mit_loesungsweg.pdf, Seite 5/15
© P. Plappert 26.09.2011
Beispiel 2
Die Daten, die diesem Beispiel zugrunde liegen, sind Angaben über die Weltproduktion von Mais
(Körnermais) in Millionen Tonnen. Sie stehen im Tabellenblatt „Mais-Daten“ der Datei
einf_d_2010.xls. (Quelle der Daten: Deutsches Maiskomitee; Stand: Oktober 2005.)
Streudiagramm zeichnen
a) Erstellen Sie ein Streudiagramm. Fügen Sie dabei das Diagramm als neues Tabellenblatt in die
Datei einf_d_2010.xlsx ein. Geben Sie dem Diagramm einen passenden Titel, beschriften Sie
die Achsen und entfernen Sie die Legende.
Markieren Sie die Felder A1 bis B10. Wählen Sie > Einfügen > Punkt, wobei Sie den
Untertyp ganz links wählen Es wird ein Streudiagramm erstellt, das diesmal schon
einen Diagrammtitel hat, da wir die Spaltenüberschriften mitmarkiert haben.
Klicken Sie in den Diagrammtitel hinein und ändern Sie ihn auf
„Weltmaisproduktion 1960 - 2004“ ab.
Wählen Sie im Menü > Diagrammtools > Layout > Achsentitel > Titel der
horizontalen Primärachse > Titel unter Achse und beschriften Sie die x-Achse, indem
Sie „Jahr“ eintippen und die > Eingabetaste betätigen.
Dann wählen Sie > Achsentitel > Titel der vertikalen Primärachse > gedrehter Titel
und beschriften Sie die y-Achse, indem Sie „Millionen Tonnen“ eintippen und die
> Eingabetaste betätigen.
Anschließend wählen Sie > Legende > Keine.
Ändern Sie den Speicherort des Diagramms, indem Sie im Menü > Diagrammtools
> Entwurf > Diagramm verschieben auswählen, > Neues Blatt anklicken und dem
Diagramm den Namen „Diagramm Mais“ geben sowie auf > OK klicken.
Regressionsgerade einzeichnen
b) Zeichnen Sie in Ihr Diagramm aus a) die lineare Regressionsgerade ein. Im Diagramm sollen
außerdem die Gleichung der Regressionsgeraden und das Bestimmtheitsmaß R 2 erscheinen.
Wechseln Sie in das neue Tabellenblatt „Diagramm Mais“ und wählen Sie im Menü
> Diagrammtools > Layout > Analyse > Trendlinie > Weitere Trendlinienoptionen.
(Je nach Installation kann „Trendlinie“ auch direkt im Menü „Layout“ zu finden
sein.) Im Feld > Trend-/Regressionstyp belassen Sie den Vorschlag > Linear.
Klicken Sie dann die Kontrollkästchen an bei > Formel im Diagramm anzeigen und
bei > Bestimmtheitsmaß im Diagramm darstellen. Dann > Schließen.
c) Ändern Sie die Schriftgröße der Gleichung der Regressionsgeraden auf 12pt.
Klicken Sie mit der > rechten Maustaste auf die Gleichung und wählen Sie bei
> Schriftgrad 12.
Labor Statistik Einführungsveranstaltung
einf_b_2010_mit_loesungsweg.pdf, Seite 6/15
© P. Plappert 26.09.2011
Korrelationskoeffizienten berechnen
d) Berechnen Sie den empirischen Korrelationskoeffizienten r zwischen Jahr und produzierter
Maismenge. r soll im Feld G1 stehen. Geben Sie r mit 4 Nachkommastellen an.
Gehen Sie wieder in das Tabellenblatt „Mais-Daten“ auf das Feld G1.
Wählen Sie > Formeln > Mehr Funktionen > Statistisch und die Funktion
> KORREL.
Geben Sie bei Matrix1 den Datenbereich A2:A10 und bei Matrix 2 den Datenbereich
B2:B10 ein. > OK.
Anschließend verringern Sie der Übersichtlichkeit halber die Anzahl der Nachkommastellen, z. B. indem Sie das Feld G1 markieren, bei im Menü > Start > Format
> Zellen formatieren im Register > Zahlen die Kategorie > Zahl wählen und bei
Dezimalstellen 4 eingeben. Dann > OK.
e) Speichern Sie die geänderte Datei einf_d_2010.xlsx in Ihr persönliches Verzeichnis ab.
Wenn Sie die Datei bereits in Beispiel 1 j) abgespeichert haben, genügt zur
Sicherung der Änderungen > Datei > Speichern. Anderenfalls wie bei 1 j) vorgehen.
Andere Regressionskurven ausprobieren
f) Ändern Sie den Typ der Regressionskurve von linear in quadratisch.
Wechseln Sie wieder in das Tabellenblatt „Diagramm Mais“. Klicken Sie zum
Formatieren mit der > rechten Maustaste auf die Regressionsgerade und wählen Sie
> Trendlinie formatieren. Im Feld > Trend-/Regressionstyp wählen Sie
> Polynomisch und bei „Reihenfolge“ den Grad 2. > Schließen.
g) Das Bestimmtheitsmaß R 2 gibt an, wie gut die Regressionskurve die Punktewolke beschreibt
(0 = gar nicht, 1 = alle Datenpunkte liegen auf der Regressionskurve). Bei quadratischer
Regression ist R 2 größer als bei linearer Regression. Warum ist bei diesem Datensatz trotzdem
eine lineare Regression sinnvoller als eine quadratische?
Das Bestimmtheitsmaß R 2 ist bei quadratischer Regression nur geringfügig größer
als bei linearer Regression (0,9792 gegenüber 0,9788).
Unter mehreren ansonsten annähernd gleich guten Regressionskurven sollte man
stets die einfachste nehmen:
1.) Wichtigster Grund: Der Denkökonomie wegen sollten Zusammenhänge
grundsätzlich so einfach wie möglich beschrieben werden.
(Fortsetzung auf der nächsten Seite)
Labor Statistik Einführungsveranstaltung
einf_b_2010_mit_loesungsweg.pdf, Seite 7/15
© P. Plappert 26.09.2011
2.) Bei etwa gleicher Erklärungsgüte sind einfache statistische Modelle zuverlässiger
und weniger fehleranfällig als kompliziertere. Dies zeigt sich hier z. B. daran,
dass bei linearer Regression y = mx + k nur zwei Parameter (m, k) geschätzt
werden müssen, bei quadratischer Regression y = ax 2 + bx + c aber drei (a, b, c).
Jede Parameterschätzung verursacht zwangsläufig einen Schätzfehler, daher ist
(bei sonst gleich guten Modellen) eines mit weniger Parametern vorzuziehen.
3.) Bei höheren Potenzen ist meistens auch mit größeren Rundungsfehlern zu
rechnen.
4.) Es besteht die Gefahr des „Overfitting“, siehe bei i) unten.
h) Machen Sie die Änderung aus f) wieder rückgängig.
Mit der > Schaltfläche
sind, rückgängig machen.
können Sie Änderungen, die noch nicht abgespeichert
i) Probieren Sie außerdem eine Regression mit einem Polynom sechsten Grades. Was stellen Sie
hier fest? Machen Sie auch diese Änderung anschließend wieder rückgängig.
Gehen Sie wie bei f) und h) vor.
Wir haben hier einen Fall von „Overfitting“ (= Überanpassung an den Datensatz)
vorliegen. Das Regressionspolynom vom Grad 6 läuft fast exakt durch alle
Datenpunkte, was man auch am sehr hohen Bestimmtheitsmaß R 2 von 0,9982
erkennen kann. Es liefert aber außerhalb der Datenpunkte unsinnige Werte (z. B.
negative Werte zwischen 1962 und 1965) und ist daher für Prognose- oder
Trendaussagen unbrauchbar.
Bei Polynomen höheren Grades ist die Gefahr von „Overfitting“ groß. Solche
Polynome sind oft (wie auch in unserem Beispiel) stark oszillierend (d. h., sie
schwanken heftig nach oben und nach unten) und geben in diesem Fall einen den
Daten zugrundeliegenden Trend kaum noch wieder.
An diesem Beispiel kann man sehen, dass die Regression mit dem höchsten R 2
nicht unbedingt die beste ist. Das Bestimmtheitsmaß ist also nicht das einzige
Kriterium zur Auswahl eines Regressionsmodells!
Labor Statistik Einführungsveranstaltung
einf_b_2010_mit_loesungsweg.pdf, Seite 8/15
© P. Plappert 26.09.2011
Beispiel 3
In diesem Beispiel lernen Sie einige Statistik-Funktionen kennen, mit denen man Berechnungen bei
den wichtigsten diskreten Wahrscheinscheinlichkeitsverteilungen (hypergeometrische
Verteilung, Binomialverteilung, Poissonverteilung) durchführen kann.
Genaueres über diese Verteilungen erfahren Sie später in der Vorlesung.
Hypergeometrische Verteilung
a) Sie erhalten eine Lieferung von 50 Glühbirnen. Daraus entnehmen Sie eine Stichprobe von 20
Glühbirnen und testen diese 20 Birnen auf Funktionsfähigkeit. Die Zufallsvariable X gebe die
Anzahl der defekten Birnen unter den 20 Glühbirnen der Stichprobe an.
Angenommen, in der Lieferung sind 5 defekte Glühbirnen. Unter unseren Annahmen folgt
X einer so genannten hypergeometrischen Verteilung X ~ H (20; 50; 5) . Berechnen Sie hierfür
a1) die Wahrscheinlichkeit, dass in Ihrer Stichprobe gar keine defekte Glühbirne ist;
Gehen Sie in das Tabellenblatt „Diskrete Verteilungen“ auf das Feld D3.
Wählen Sie > Formeln > Mehr Funktionen > Statistisch und die Funktion
> HYPGEOM.VERT.
Eine hypergeometrisch verteilte Zufallsvariable X beschreibt das Ziehen einer
Stichprobe vom Umfang n ohne Zurücklegen aus N Objekten, von denen M eine
bestimmte Eigenschaft haben. Bei uns ist n = 20, N = 50 und M = 5 (besondere
Eigenschaft: Glühbirne defekt). In der Excel-Funktion HYPGEOM.VERT werden
die Objekte mit der bestimmten Eigenschaft „Erfolge“ genannt. [Bei uns müsste
man eigentlich eher von „Misserfolgen“ sprechen...]
Die Excel-Funktionen für eine diskreten Zufallsvariable X bieten die Möglichkeit
zu wählen zwischen der Berechung von
• P( X = k )
„genau k Erfolge“ und
• P( X ≤ k )
„höchstens k Erfolge“.
Bei der zweiten Möglichkeit werden die Wahrscheinlichkeiten für 0 Erfolge, 1
Erfolg, 2 Erfolge usw. bis einschließlich k Erfolge aufsummiert. Man spricht auch
von „kumulieren“. Will man das haben, muss man für „Kumuliert“ den Wert WAHR
oder 1 eingeben, will man nicht kumulieren, muss man für „Kumuliert“ den Wert
FALSCH oder 0 eingeben.
Wenn wir für eine hypergeometrisch verteilte Zufallsvariable X ~ H (n; N ; M ) die
Wahrscheinlichkeit P ( X = k ) berechnen wollen (das ist in unserem Beispiel die
Wahrscheinlichkeit, genau k defekte Stücke zu ziehen, in Excel-Sprache: k „Erfolge
in der Stichprobe“), müssen wir also eingeben:
bei > Erfolge_S den Wert k,
bei > Umfang_S den Wert n,
bei > Erfolge_G den Wert M,
bei > Umfang_G den Wert N,
bei > Kumuliert, ob kumuliert werden soll,
für a1) also 0,
für a1) also 20,
für a1) also 5,
für a1) also 50,
für a1) also 0.
Anschließend mit > OK bestätigen.
Labor Statistik Einführungsveranstaltung
einf_b_2010_mit_loesungsweg.pdf, Seite 9/15
© P. Plappert 26.09.2011
a2) die Wahrscheinlichkeit, dass in Ihrer Stichprobe genau 1 defekte Glühbirne ist;
Dies ist die gleiche Fragestellung wie in a1), nur dass hier P ( X = 1) gesucht ist.
Wählen Sie in Feld D4 > Formeln > Mehr Funktionen > Statistisch und die Funktion
> HYPGEOM.VERT. Geben Sie hier ein:
bei > Erfolge_S den Wert 1,
bei > Umfang_S den Stichprobenumfang n = 20
bei > Erfolge_G die Anzahl M = 5 der Objekte in der Grundgesamtheit, die die uns
interessierende Eigenschaft haben („defekt“)
bei > Umfang_G den Umfang der Grundgesamtheit N = 50 und
bei > Kumuliert wiederum 0 (oder FALSCH). Bestätigen mit > OK.
a3) die Wahrscheinlichkeit, dass in Ihrer Stichprobe genau 2 defekte Glühbirnen sind;
a4) die Wahrscheinlichkeit, dass in Ihrer Stichprobe genau 3 defekte Glühbirnen sind;
Gehen Sie wie bei a1) und a2) vor. Hier ist lediglich für > Erfolge_S der Wert 2 bzw.
3 einzugeben.
a5) die Wahrscheinlichkeit, dass in Ihrer Stichprobe höchstens 3 defekte Glühbirnen sind.
Die Ergebnisse sollen in den Feldern D3 bis D7 des Tabellenblatts „Diskrete Verteilungen“ der
Datei einf_d_2010.xlsx stehen und 4 Nachkommastellen haben.
Die Wahrscheinlichkeit für „höchstens 3“ defekte Glühbirnen ist eine kumulierte
Wahrscheinlichkeit. Wählen Sie bei der Funktion HYPGEOM.VERT
bei > Erfolge_S den Wert 3,
bei > Umfang_S den Wert 20,
bei > Erfolge_G den Wert 5,
bei > Umfang_G den Wert50 und nun im Gegensatz zu a4)
bei > Kumuliert den Wert 1 (oder WAHR). Bestätigen mit > OK.
Hinweis: entsprechend der Definition von „kumulieren“ würden wir in diesem
Beispiel das gleiche Ergebnis erhalten, wenn wir im Feld D7 auf das Summensymbol
klicken würden.
Ändern der Anzahl der Nachkommastellen: Markieren Sie die Felder D3 bis D7,
wählen Sie bei > Start > Format > Zellen formatieren im Register > Zahlen die
Kategorie > Zahl und geben Sie bei > Dezimalstellen 4 ein. Dann > OK.
Binomialverteilung
b) Bei der Massenproduktion bestimmter elektronischer Kleinteile entsteht eine Ausschussquote
von 10 %. Sie entnehmen der laufenden Produktion eine Stichprobe vom Umfang 20. Man kann
davon ausgehen, dass hierbei verschiedene Stichprobenteile unabhängig voneinander defekt
sind.
Die Zufallsvariable X gebe die Anzahl der defekten Kleinteile unter diesen 20 Glühbirnen an.
Unter den genannten Annahmen folgt X einer so genannten Binomialverteilung
X ~ B (20; 0,1) . Berechnen Sie hierfür
Labor Statistik Einführungsveranstaltung
einf_b_2010_mit_loesungsweg.pdf, Seite 10/15
© P. Plappert 26.09.2011
b1) die Wahrscheinlichkeit, dass in Ihrer Stichprobe genau 3 defekte Kleinteile sind;
Gehen Sie in das Tabellenblatt „Diskrete Verteilungen“ auf das Feld D11.
Wählen Sie > Formeln > Mehr Funktionen > Statistisch und die Funktion
> BINOM.VERT.
Eine binomialverteilte Zufallsvariable X beschreibt, wie oft bei n unabhängigen
Durchführungen eines Zufallsexperiments ein bestimmtes Ereignis vorkommt, das
bei einmaliger Durchführung des Zufallsexperiments mit Wahrscheinlichkeit p
auftritt. Bei uns wird das Zufallsexperiment „ein Kleinteil aus der laufenden
Produktion ziehen“ n = 20-mal durchgeführt, und uns interessiert das Ereignis
„Kleinteil defekt“, das beim Ziehen eines Kleinteils mit Wahrscheinlichkeit p = 0,1
auftritt.
In der Sprache von Excel heißt das Eintreten des untersuchten Ereignisses auch hier
„Erfolg“.
Wenn wir für eine binomialverteilte Zufallsvariable X ~ B(n; p) eine
Wahrscheinlichkeit P ( X = k ) berechnen wollen, (das ist in unserem Beispiel die
Wahrscheinlichkeit, genau k defekte Stücke zu ziehen, in Excel-Sprache ist k die
„Zahl der Erfolge“), müssen wir also eingeben:
bei > Zahl_Erfolge den Wert k,
für b1) also 3,
für b1) also 20,
bei > Versuche den Wert n,
bei > Erfolgswahrsch den Wert p, für b1) also 0,1,
bei > Kumuliert den Wert 0, d. h. nicht kumulieren.
Anschließend mit > OK bestätigen.
b2) die Wahrscheinlichkeit, dass in Ihrer Stichprobe höchstens 3 defekte Kleinteile sind.
Die Ergebnisse sollen in den Feldern D11 und D12 stehen und 4 Nachkommastellen haben.
Wählen Sie in Feld D12 wieder > Formeln > Mehr Funktionen > Statistisch und die
Funktion > BINOM.VERT.
Der Unterschied zu b1) besteht wiederum darin, dass wir dort die Wahrscheinlichkeit
P( X = 3) , genau 3 Defektstücke zu ziehen, berechnet haben, während wir uns hier
für die kumulierte Wahrscheinlichkeit P ( X ≤ 3) interessieren, also die
Wahrscheinlichkeit, höchstens 3 Defektstücke zu ziehen.
Wenn wir für eine binomialverteilte Zufallsvariable X ~ B(n; p) eine „kumulierte“
Wahrscheinlichkeit P( X ≤ k ) berechnen wollen, müssen wir eingeben:
bei > Zahl_Erfolge den Wert k,
für b2) also 3,
für b2) also 20,
bei > Versuche den Wert n,
bei > Erfolgswahrsch den Wert p, für b2) also 0,1,
bei > Kumuliert den Wert 1, d. h. kumulieren.
Anschließend mit > OK bestätigen.
Formatieren der Zahlen (Anzahl der Nachkommastellen) wie oben bei a5).
Labor Statistik Einführungsveranstaltung
einf_b_2010_mit_loesungsweg.pdf, Seite 11/15
© P. Plappert 26.09.2011
Poissonverteilung
c) Bei der Produktion einer bestimmten Textilart entstehen zufallsbedingt Gewebefehler. Im Mittel
sind es 2 Gewebefehler auf 1 m².
Sie entnehmen zufällig ein Textilstück von 1 m² und zählen, wie viele Gewebefehler auf diesem
Stück sind. Die Zufallsvariable X gebe die Anzahl festgestellten Gewebefehler an. Unter den
genannten Annahmen folgt X einer so genannten Poissonverteilung X ~ Po(2) , dabei ist
λ = 2 der Erwartungswert von X (mittlere, d. h. erwartete Anzahl von Fehlern).
Berechnen Sie hierfür
c1) die Wahrscheinlichkeit, dass in auf Ihrem Textilstück genau 3 Gewebefehler sind;
Gehen Sie in das Tabellenblatt „Diskrete Verteilungen“ auf das Feld D16.
Wählen Sie > Formeln > Mehr Funktionen > Statistisch und die Funktion
> POISSON.VERT.
Zum Berechnen der Wahrscheinlichkeit P ( X = k ) für eine poissonverteilte
Zufallsvariable X ~ Po(λ ) (das ist in unserem Beispiel die Wahrscheinlichkeit,
genau k Gewebefehler vorzufinden), müssen wir eingeben:
bei > X den Wert k,
für c1) also 3,
für c1) also 2,
bei > Mittelwert den Wert λ ,
bei > Kumuliert den Wert 0, d. h. nicht kumulieren.
Anschließend mit > OK bestätigen.
c2) die Wahrscheinlichkeit, dass in auf Ihrem Textilstück höchstens 3 Gewebefehler sind.
Die Ergebnisse sollen in den Feldern D16 und D17 stehen und 4 Nachkommastellen haben.
Wählen Sie in Feld D17 wieder > Formeln > Mehr Funktionen > Statistisch und die
Funktion > POISSON.VERT.
Zum Berechnen der Wahrscheinlichkeit P( X ≤ k ) für eine poissonverteilte
Zufallsvariable X ~ Po(λ ) (das ist in unserem Beispiel die Wahrscheinlichkeit,
höchstens k Gewebefehler vorzufinden), müssen wir eingeben:
bei > X den Wert k,
für c2) also 3,
für c2) also 2,
bei > Mittelwert den Wert λ ,
bei > Kumuliert den Wert 1, d. h. kumulieren.
Anschließend mit > OK bestätigen.
Formatieren der Zahlen (Anzahl der Nachkommastellen) wie oben bei a5).
d) Speichern Sie die geänderte Datei einf_d_2010.xlsx in Ihr persönliches Verzeichnis ab.
> Datei > Speichern.
Labor Statistik Einführungsveranstaltung
einf_b_2010_mit_loesungsweg.pdf, Seite 12/15
© P. Plappert 26.09.2011
Beispiel 4
In diesem Beispiel lernen Sie einige Statistik-Funktionen kennen, mit denen man Berechnungen bei
der wichtigsten stetigen Wahrscheinscheinlichkeitsverteilung, nämlich der Normalverteilung,
durchführen kann. Genaueres über die Normalverteilungen erfahren Sie später in der Vorlesung.
Eine Maschine füllt Zucker in Packungen. Die Füllmenge variiert zufällig. Die Zufallsvariable X
gebe die Füllmenge [in g] einer zufällig ausgewählten Zuckerpackung an.
Wir gehen in diesem Beispiel davon aus dass die Zufallsvariable X einer Normalverteilung
X ~ N (1000; 9) folgt. In diesem Beispiel ist also der Erwartungswert der Füllmenge µ = 1.000 [g],
Varianz der Füllmenge σ 2 = 9 [g²] und Standardabweichung der Füllmenge σ =
9 = 3 [g].
a) Berechnen Sie die Wahrscheinlichkeit, dass die Füllmenge einer zufällig ausgewählten
Zuckerpackung bei höchstens 994 g liegt. Das Ergebnis soll im Feld D3 des Tabellenblatts
„Normalverteilung“ der Datei ein_daten.xlsx stehen und 4 Nachkommastellen haben.
Wählen Sie in Feld D3 des Tabellenblatts „Normalverteilung > Formeln
> Mehr Funktionen > Statistisch und die Funktion > NORM.VERT.
Zum Berechnen der Wahrscheinlichkeit P( X ≤ k ) für eine normalverteilte
Zufallsvariable X ~ N ( µ ; σ 2 ) (das ist in unserm Beispiel die Wahrscheinlichkeit,
dass die Füllmenge einer zufällig ausgewählten Packung höchstens k g beträgt),
müssen wir eingeben:
bei > X den Wert k,
bei > Mittelwert den Erwartungswert µ ,
also hier 994,
also hier 1000,
bei > Stabwn die Standardabweichung σ , also hier 3,
bei > Kumuliert den Wert 1, d. h. kumuliert (Verteilungsfunktion).
Anschließend mit > OK bestätigen. Formatieren (Nachkommastellen) wie oben.
Wählt man bei NORMVERT für „Kumuliert“ den Wert 1, so wird die so genannte
„Verteilungsfunktion“ der normalverteilten Zufallsvariablen X berechnet.
Das heißt, in diesem Fall werden die „kumulierten“ Wahrscheinlichkeiten P( X ≤ k )
berechnet, wobei allerdings „Kumulieren“ hier nicht Aufsummieren, sondern
Integrieren bedeutet. Dies wird später in der Vorlesung genauer besprochen.
b) Berechnen Sie das 1-%-Quantil der Normalverteilung N (1000; 9) . Das ist diejenige Füllmenge,
die von einer zufällig ausgewählten Zuckerpackung nur mit einer Wahrscheinlichkeit von 0,01
unterschritten wird. Das Ergebnis soll im Feld D5 stehen und 4 Nachkommastellen haben.
Quantile der Normalverteilung berechnet die Funktion NORM.INV.
Wählen Sie > Formeln > Mehr Funktionen > Statistisch und die Funktion
> NORM.INV. Geben Sie dann ein:
bei > Wahrsch
bei > Mittelwert den Erwartungswert µ ,
den Wert 0,01,
also hier 1000,
bei > Stabwn die Standardabweichung σ , also hier 3.
Formatieren (Nachkommastellen) wie oben.
Labor Statistik Einführungsveranstaltung
einf_b_2010_mit_loesungsweg.pdf, Seite 13/15
© P. Plappert 26.09.2011
c) Schauen Sie in der Excel-Hilfe nach, mit welcher Funktion kann die Quantile der so genannten
t-Verteilung berechnen kann. (Es ist nur nach dem Namen der Funktion gefragt. Die Quantile
der t-Verteilung werden in Kapitel 5 der Vorlesung genauer erläutert.)
Die Hilfefunktion wird aufgerufen, indem Sie in der Hauptmenüzeile auf das
> Symbol
klicken. Lassen Sie dort nach „Quantile“ suchen.
Unter den angebotenen Funktionen ist T.INV die richtige.
Zusatzinformation In Excel 2003 hatte (unlogischerweise) TINV eine andere Syntax
als NORMINV. Dieser Defekt ist in Excel 2010 behoben.
d) Zeichnen Sie die Dichtefunktion der Zufallsvariablen X im Intervall [990, 1010].
Excel berechnet die Dichtefunktion einer Normalverteilung, wenn bei
NORM.VERT für „Kumuliert“ der Wert 0 (nicht kumulieren) gewählt wird.
Die Funktionswerte der Dichtefunktion werden in diesem Beispiel an mehreren
Stellen im Intervall [990, 1010] benötigt, um die Funktionskurve zeichnen zu
können.
Dazu muss zunächst eine Wertetabelle erstellt werden, was am schnellsten mit dem
„automatischen Ausfüllen“ wie folgt geht. Geben Sie in die Felder B8 und B9 die
Werte 990 und 991 ein. Markieren Sie die beiden Felder. Klicken Sie mit der linken
Maustaste auf das kleine Kästchen rechts unten im markierten Bereich (Ausfüllkästchen; in der Abbildung zeigt ein roter Pfeil darauf).
Ziehen Sie das Kästchen bei gedrückter linker Maustaste nach unten bis zum Feld
B28. Excel erhöht dabei die Werte in der Spalte B jeweils um die Differenz der
beiden markierten Felder, also jeweils um 1.
Nach den x-Werten benötigen wir nun die Funktionswerte. Markieren Sie das Feld
C8 und wählen Sie > Formeln > Mehr Funktionen > Statistisch und die Funktion
> NORM.VERT.
Zum Berechnen der Dichtefunktion geben Sie ein:
bei > X nicht den Zahlenwert, sondern den Namen des linken Nachbarfeldes B8,
bei > Mittelwert den Erwartungswert µ , also 1000,
bei > Stabwn die Standardabweichung σ , also 3,
bei > Kumuliert den Wert 0, d. h. nicht kumuliert (Dichtefunktion).
Anschließend mit > OK bestätigen.
Labor Statistik Einführungsveranstaltung
einf_b_2010_mit_loesungsweg.pdf, Seite 14/15
© P. Plappert 26.09.2011
Excel interpretiert die so eingegebene Formel als „berechne die Dichtefunktion einer
Normalverteilung N (1000; 9) an der Stelle, die durch den Wert im linken
Nachbarfeld gegeben ist“.
Wir können die Formel daher für die anderen Stellen kopieren, z. B. dadurch, dass
wir C8 markieren, wiederum das Ausfüllkästchen (roter Pfeil) anklicken und es bei
gedrückter linker Maustaste bis zum Feld C28 nach unten ziehen.
Nachdem die Wertetabelle so erstellt ist, zeichnen wir den Funktionsgraphen als ein
Streudiagramm, bei dem die Punkte durch eine „glatte“ Linie verbunden werden, die
Punkte selbst aber nicht angezeigt werden.
Markieren Sie dazu die Felder B7 bis C28. Wählen Sie > Einfügen > Punkt, wobei
Sie den Untertyp in der ersten Zeile ganz rechts wählen Es wird ein Streudiagramm
(Punkte mit Linien verbunden) erstellt, das ebenfalls schon einen Diagrammtitel hat,
da wir die Spaltenüberschriften mitmarkiert haben.
Klicken Sie in den Diagrammtitel hinein und ändern Sie ihn auf „Dichte der
Normalverteilung N(1000; 9)“ ab.
Wählen Sie im Menü > Diagrammtools > Layout > Achsentitel > Titel der
horizontalen Primärachse > Titel unter Achse und beschriften Sie die x-Achse, indem
Sie „x“ eintippen und die > Eingabetaste betätigen.
Dann wählen Sie > Achsentitel > Titel der vertikalen Primärachse > gedrehter Titel
und beschriften Sie die y-Achse, indem Sie „p“ eintippen und die > Eingabetaste
betätigen.
Anschließend wählen Sie > Legende > Keine.
Ändern Sie den Speicherort des Diagramms, indem Sie im Menü > Diagrammtools
> Entwurf > Diagramm verschieben auswählen, > Neues Blatt anklicken und dem
Diagramm den Namen „Diagramm Dichte NV“ geben sowie auf > OK klicken.
Das Diagramm zeigt eine „Gaußsche Glockenkurve“ als Dichte der Normalverteilung. Später wird besprochen werden, dass sich die Wahrscheinlichkeiten bei
Normalverteilungen als Flächen unter der Dichtkurve ergeben.
Excel hat das Anzeigeintervall auf der x-Achse etwas vergrößert. Um das Diagramm
auf das Intervall [990, 1010] einzuschränken, klicken Sie mit der > rechten
Maustaste auf die x-Achse, wählen > Achse formatieren, wählen bei > Minimum
> Fest und geben den Wert 990 ein, sodann wählen Sie bei > Maximum ebenfalls
> Fest und geben den Wert 1010 ein. > Schließen.
e) Speichern Sie die geänderte Datei einf_d_2010.xlsx in Ihr persönliches Verzeichnis ab.
> Datei > Speichern.
Labor Statistik Einführungsveranstaltung
einf_b_2010_mit_loesungsweg.pdf, Seite 15/15
© P. Plappert 26.09.2011