Statistik

1.Statistische Kennzahlen

Hinweise

Die Übersicht der statistischen Funktionen ist etwas versteckt:



  • Es ist unerlässlich zu den besprochenen Funktionen auch die Office Referenz zu lesen!
  • Datentypen der Statistik: Arten von Skalen:

Übung

Informieren sie sich in den Unterlagen über die angesprochenen Kennzahl-Funktionen und ihre Anwendung. Bearbeiten sie die Aufgabe Kennzahlen Tabellen Stichprobe und Urliste!

2.Fertigpackungsverordnung FPVO

Hinweise

  • Begriffe der FPVO
    Nennfüllmenge
    Minusabweichung
    Standardabweichung
    Mittelwert
    technische Untergrenze TU1
    technische Untergrenze TU2
  • Ordnen sie die Begriffe einer Abfüllung für 250g-Packungen zu!

Aufgaben

Kennzahlen Abfüllstatistik Tabellen
    Urliste,
    Abfüllung75 und
    Abfüllung375
  • Berechnen sie die Daten der Abfüllstatistik entsprechend der Spalte Abfüllprotokoll!
  • NF=?, MA=?, TU1=?, TU2=?
  • Zählen sie die Anzahl der Packungsgewichte leichter als TU1 und TU2 und berechnen sie jeweils den Prozentsatz!
  • Programmieren sie die Formeln für die Abfüllstatistik der Füllersimulation
    (Unterlagen Seite 9)
  • Formel Formel Formel Formel Formel Formel Formel Formel Formel Formel Formel Formel
  • Entsprechen die Abfüllungen der FPVO?

3.Exkurs Binomialverteilung

Zufall

  • in der Tabellenkalkulation
  • =ZUFALLSBEREICH(von;bis) {diskretes Ergebnis eine zufällige Zahl zwischen von .. bis}
  • =ZUFALLSBEREICH(1;6) simuliert einen Würfel
  • Berechenbar ist der Zufall durch die Betrachtung von Wahrscheinlichkeiten - die Wahrscheinlichkeit eines Ereignisses liegt zwischen 0 (unmöglich) und 1 (sicher oder alle möglichen Ereignisse).
  • Beim Würfel sind die Ereignisse E={1,2,3,4,5,6} und P(6)=1/6,
    die Wahrscheinlichkeit einen 6er zu Würfeln ist 1/6 (günstige/mögliche Ereignisse).
  • Führen sie in 'Verteilung von Wahrscheinlichkeiten' die Simulation Galtonbrett und 10Würfel durch!

Anwendung

Ein Instrument der Wareneingangskontrolle:
  • n Stichprobengröße: Untersuche eine Stichprobe n der gelieferten Teile auf Fehler.
  • p ist eine Spezifikation des Lieferers: 1 Lieferteil von 150 entspricht nicht der Spezifikation
  • Wie groß wählen sie die Stichprobe n?
  • Wie groß ist die Wahrscheinlichkeit ein Defektteil in der gewählten Stichprobe zu finden?
Verschaffen sie sich einen Überblick wie die Berechnung Binomialverteilung in der Tabellenkalkulation umgesetzt wird. Eine Einführung finden sie in den Unterlagen. Aufgaben auf der letzten Seite der Unterlagen.
Binomialverteilungsrechner

4.Normalverteilung

Hinweise

Im Gegensatz zur Binomialverteilung (diskrete Verteilung) ist die Normalverteilung stetig - z.B. bei einer Abfüllung kann jedes beliebige Packungsgewicht um den Mittelwert abgefüllt werden. Für die Binomialverteilung B(n,p) betrachtet man n Versuche mit einer Wahrscheinlichkeit p, die für große n in die Normalverteilung N(µ,σ) übergeht:
  µ=n*p
 µ Mittelwert
 σ Standardabweichung
Bearbeiten sie die Unterlagen Statistik ab Seite 5 zur Anwendung der Normalverteilung in der Tabellenkalkulation!

Anwendung

Beschreibungsmodell für Abfüllprozesse
Kennzahlen Abfüllstatistik Tabelle FPVO
  • Lesen sie die oben beschriebenen Kennzahlen der Abfüllstatistik an der Abfüllverteilung ab
  • Wie hoch darf der Anteil an Packungen leichter als TU1 sein? Wie hoch ist er?
  • 2% aller Packungen wiegen weniger als ....g ?
  • 96% aller Packungen wiegen zwischen ...g und ...g
  • Welche Stellgrößen verändern den Anteil an Packungen leichter als TU1?
  • Verändern sie den Mittelwert so, dass 2% aller Packungen weniger als TU1 wiegen!
  • Stellen sie den Mittelwert wieder auf 251,5g und verändern sie die Standardabweichung so, dass 2% aller Packungen weniger als TU1 wiegen!
  • Berechnen sie für alle dargestellten Fälle
    (Mittelwert - TU1)/Standardabweichung - Ergebnis?

Übung

Bearbeiten sie die Aufgaben in Tabelle Normalverteilung und Abfüllstatistik!
    Die Werte der Funktion NORMVERT (Kumuliert=1) geben den Flächenanteil unter der Dichtefunktion der Normalverteilung wieder. Gesamtfläche unter der Glockenkurve 100%=1!


NORMVERT(41,5;50;4;1) berechnet den roten Flächenanteil <41,5g zu ca. 1,67%
der Anteil an Packungen mit weniger als 41,5 g beträgt ca. 1,67%
1-NORMVERT(55;50;4;1) berechnet den blauen Flächenanteil >55 g zu ca. 10,6%
der Anteil an Packungen mit mehr als 55 g beträgt ca. 10,6%
NORMVERT und Normalverteilungstabellen geben also immer die Flächenanteile <=x (linker Flächenanteil unter der Glockenkurve) an. Werden Flächenanteile >=x gesucht so wird der Anteil <=x von 100%=1 abgezogen!




5.Hypergeometrische Verteilung

Hinweis

Die Gewinnchancen beim Lotto können mit der
Funktion Hypergeometrische Verteilung
HYPGEOMVERT(Erfolge_S; Umfang_S; Erfolge_G; Umfang_G)
berechnet werden.
Umfang_G (Umfang/Anzahl der Grundgesamtheit) entspricht den 49 Kugeln.
Erfolge_G ist die Anzahl der möglichen Erfolge, die Anzahl der Gewinnkugeln 6.
Umfang_S ist der Umfang der Stichprobe, die Anzahl der Kugeln einer Ziehung 6.
Erfolge_S ist die Anzahl der in der Stichprobe erzielten Erfolge (Richtigen),
d.h. ein Spiel kann 0, 1, 2 , 3, 4, 5 oder 6 (angestrebtes Ergebnis) Gewinnzahlen aufweisen.
Die Lotto-Situation ist vergleichbar einer Wareneingangsprüfung:
Ein LKW liefert 10000 Dosen (Umfang_G) darunter sind 0,5% = 50 (Erfolge_G) schadhafte Dosen (Herstellerangabe). Sie kontrollieren beim Wareneingang 10 (Umfang_S) Dosen.
Welche Abnahmebedingung formulieren sie im Liefervertrag?



Aufgabe

Stellen sie die Berechnung der Lottochancen der Berechnung der gegebenen Wareneingangsprüfung gegenüber.
  • Wie groß wählen sie die Stichprobe?
  • Wie groß ist dann die Change 1 oder mehrere schadhafte Dosen in der Stichprobe zu finden?
  • Können sie diese Aufgabe auch mit der Binomialverteilung lösen?
    Wo liegt der Unterschied der beiden Verteilungen?
Binomialverteilungsrechner

6.Regressionsrechnung

Hinweise

Im Versuch ermitteln sie Wertepaare (x|y) — z.B. Messwerte (pH, Temperatur, Druck, Kosten uä.) — und suchen eine Funktion f(x), bei der für alle Wertepaare möglichst genau f(x)=y gelten soll.
Die Gauß'sche Methode der kleinsten Fehlerquadrate ermittelt Funktionen F, die sich den gegebenen Wertepaaren optimal, d.h. mit kleinstmöglicher Abweichung annähern.

In dem Beispiel unten haben sie die Produktionsmenge x mit den zugeordneten Kosten y. Um fehlende Zwischenwerte auszurechnen oder um einen Bezug zwischen fixen und variablen Kosten herzustellen benötigen sie eine Funktion, die diesen Zusammenhang möglichst genau beschreibt:

7.Optimieren mit dem Solver

Optimieren von Rechenmodellen

Dazu wird das Add-In Solver verwendet (muss beim MS-Office Setup gesondert installiert werden). Libre-Office setzt das vergleichbare Addon Calc Optimization Solver ein.
Untersuchen wir das Modell an einer Aufgabe, wie sie oft als Denksportproblem gestellt wird:
Man kaufe x Stück Schinken zu a=15.-€, y Stück Wiener zu b=1.-€ und z Stück Brötchen zu c=0,30 € unter der Bedingung 100 Stücke (x+y+z=100) zu einem Gesamtpreis von 100.-€ (ax+by+cz=100).
Der Lernschritt Solver Einführung zeigt die Anwendung von VBA Optimizer Tabelle Einführung.
Die Formeln für die Preisberechnung und Summen müssen noch eingetragen werden!
Im VBA Optimizer habe ich einen Menü-Tab (Optimierung) zur Bedienung des Solvers eingebaut, der von links nach rechts abgearbeitet wird.
 
  • Zielzelle auswählen - die Zelle die optimiert werden soll markieren und Button klicken
  • Zelle Min/Max - Zielzelle maximieren oder minimieren (Klicken bis gewünschte Funktion)
  • Veränderbare Zellen auswählen - die Zellen markieren, die solange verändert werden sollen bis das Optimum erreicht wird (ggf. mit STRG mehrere Bereiche auswählen)
  • Bereiche für Bedingungen - sind Bereiche mit drei Spalten oder Zeilen die einen berechneten Wert, die Vergleichsoperation (<= oder >=) und den Grenzwert dafür enthalten (z.B. Fettgehalt berechnet <= 30%).
  • Einstellung Solver zeigt die Einstellungen, die aus der Tabelle in das Solvermodell übernommen wurden.
  • SOLVE - überträgt die unter Zielzelle/Zelle Min/Max/Veränderbare Zellen/Bereiche für Bedingungen/ vereinbarten Optionen in den Solver und führt einen Optimierungsdurchlauf aus (kann einige Zeit dauern: ESC Abbrechen). Wird keine Lösung gefunden, müssen sie ihre Bedingungen überprüfen und ggf. für eine Lösung abändern!

Rezepturen optimieren

Die Optimizer Tabellen enthalten einige Rezepturen die für die Optimierungen von besonderem Interesse sind.
Grundsätzlich benötigen sie ein Rechenblatt mit einer Berechnung des Rezepturpreises (Zielzelle für eine Minimierung). Als veränderbare Zellen werden die Rezepturmengen definiert. Daneben müssen die einzuhaltenden Randbedingungen berechnet werden: z.B. Gehalt Fleischeiweiß - BEFFE - Fett bzw. Fett/Eiweiß-Verhältnis - Wasser/Eiweiß-Verhältnis uä. Neben oder unter den Bedingungsfeldern werden 1 Zelle für den Vergleichsoperator und 1 Zelle für den Grenzwert benötigt: