Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 21:51:33
Idee für Grafik
26.04.2024 21:29:07
PQ zufällige Reihenfolge
26.04.2024 17:18:47
Große Datenmenge in komplexes Diagramm verpacken
26.04.2024 16:22:47
Aktualisierung Verknüpfung bei geschlossener Quelldatei
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
400to404
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
400to404
400to404
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Zufallszahlen gleichverteilt - normalverteilt ?

Zufallszahlen gleichverteilt - normalverteilt ?
18.03.2004 16:19:08
Alex
Hallo Excel Forum!
Ich erzeuge Zufallszahlen mit der Funktion „=ZUFALLSZAHL()“, von der ich denke, dass diese eine Gleichverteilung unterliegt.
Kann mir jemand sagen, ob meine Annahme richtig ist und ob die Möglichkeit besteht normalverteilte Zufallszahlen zu simulieren und wenn ja wie?
Besten dank für eine mögliche Antwort!
Gruß
Alex

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zufallszahlen gleichverteilt - normalverteilt ?
18.03.2004 17:10:18
Karl-Otto Reimann
Hallo Alex
Wenn Du eine gößere Menge Zufallszahlen erzeugst (größer 1000),
dann wird mit zunehmender Menge der absolute Unterschied
zwischen den einzelnen Häufigkeiten der einzelnen Zahlen
größer, der prozentuale aber kleiner. In diesem Sinne
sind die Häufigkeiten normalverteilt (im weiteste Sinne
gleichverteilt). Erst bei sehr großen Mengen wird es Dir
gelingen, aus der 3-fachen Standardabweichung rauszukommen.
Allerdings erzeugt Excel auch !negative Zufallszahlen.
Ein Blick ins Archiv von Heise.de hilft weiter.
Gruß
Karl-Otto
neg. Zufallszahlen in Excel03 durch Hotfix behoben
18.03.2004 17:39:10
Axel
Das mit den negativen Zahlen betrifft aber nur Excel 2003, da dort ein neuer Algorithmus zum Tragen kommt.
Mircosoft hat das Problem mittlerweile behoben:
http://support.microsoft.com/default.aspx?kbid=833618
Gruß
Axel
Anzeige
AW: Zufallszahlen gleichverteilt - normalverteilt ?
18.03.2004 17:42:13
Uwe
Hallo, Axel!
Die Zufallszahlen, die die EXCEL-Funktion =ZUFALLSZAHL() erzeugt, sind gleichverteilt, so wie Karl-Otto schon erwähnt, mit zunehmender Anzahl von Ergebnissen, ergibt sich eine zunehmend gleiche Anzahl von Zahlen je Rang.
Wenn Du eine Normalverteilung der Zufallszahlen erzielen willst und die Standardabweichung Dir vorgibst (besser aus bekannter Realisierung ermittelt), dann dürfte die Zellenformel
=NORMVERT(ZUFALLSZAHL();0;sigma;)
Hier wird die Wahrscheinlichkeit errechnet, die sich auf der Grundlage einer Normalverteilung mit einer einfachen Standarsabweichung sigma und dem Mittelwert Null ergibt (ca. 68?% aller Werte liegen im Wertebereich von +/-sigma um den Mittelwert.
Die mit dieser Formel erzeugte Zufallszahlen, sollte normalverteilt sein.
Gleichbedeutend hierzu ist die Transformierung einer gleichverteilten Zufallszahl n der Summenkurve einer Normalverteilung.
Hoffe mit dieser Skizze Ideen zur Umsetzung geliefert zu haben.
Gruß,
Uwe
en Mittelwert ,
Anzeige
AW: Zufallszahlen gleichverteilt - normalverteilt ?
18.03.2004 18:55:30
Ralf
Wie könnte denn eine Normverteilung aussehen?? Oder wie ist diese zu ermitteln?
AW: Zufallszahlen gleichvert -T- normalvert
18.03.2004 19:29:33
Uwe
Das ganze sieht grafisch so aus:

Userbild

Mit der gleichverteiten Zufallszahl =ZUFALLSZAHL() wir auf der Ordinatenachse (senkrechte) der Wert fixiert. Nun wird die horizontale gezeichnet bis zum Schnittpunkt mit der Summenkurve. Das Lot auf die Absizze liefert die Differenz zum Mittelwert des normvert. Zufallswertes.
Eine Serie von Zufallszahlen, die mit dieser Vorgehensweise zusammengestelt wird, liefert bei hinreichend großer Anzahl der Datenpunkte, die statischten Kenngrößen der unterstellten Normalverteilung.
Natürlich läßt sich dies alles wesentlich besser in VBA-Code formulieren als mit Zellenformeln. Wenn Zeit und Interesse vorhanden, kann es ja einmal im Konzept von mir oder jemanden hier entworfen werden, sofern es nicht bereits fertigte Lösungen dazu gibt, was ich mir durchaus vorstellen könnte.
Gruß,
Uwe
Anzeige
AW: Danke, Karl-Otto, ...
18.03.2004 21:45:08
Uwe
...für die Demo-Datei (in der z.Z. geladenen Fassung fehlt die Funktion ZUFALLSBEREICH(x0, x1), die ich wie folgt ergänzt habe:

Function ZUFALLSBEREICH(a0 As Integer, a1 As Integer)
ZUFALLSBEREICH = (a1 - a0 + 1) * Rnd()
End Function

Sie zeigt sehr schön, dass die generierte Zufallszahl annähernd gleichverteilt ist.
Eine (standard)normaverteilte Zufallszahl tendiert zur Bildung der "Glockenkurve" (auf dem ehemaligen 10DM dargestellt bzw. rote Kurve in meinem Diagramm zuvor), also zur Häufung im Bereich des Mittelwertes mit einer definirbaren Standardabweichung. Je größer diese wird, umso mehr nähert sich die Verteilung der gleichverteilung. In meinem Diagramm würde die bedeuten, dass die grüne Summenkurve, sich immer mehr an eine 45°Grad-Geraden anschmiegt.
Gruß,
Uwe
Anzeige
AW: Zufallszahlen normalverteilt (Näherung)
18.03.2004 23:05:30
Uwe
Hallo, Karl-Otto,
ich habe mir erlaubt, eine einfache Näherungsformel für normalverteilte Zufallszahlen in Deine Arbeitsmappe einzubauen.

Function ZUFALLSBEREICH_NV(a0 As Integer, a1 As Integer)
Dim z1 As Double, z2 As Double, w As Double
Dim r As Double, phi As Double
Dim x As Double, y As Double
Dim pi As Double
pi = 4 * Atn(1)
w = -1
While w < 0
z1 = (a1 - a0 + 1) * Rnd()
w = -2 * Log(z1) / Log(10)
Wend
r = Sqr(w)
z2 = (a1 - a0 + 1) * Rnd()
phi = 2 * pi * z2
x = r * Cos(phi)
y = r * Sin(phi)
ZUFALLSBEREICH_NV = (a1 - a0 + 1) * Sqr(x * x + y * y) / 2
End Function

Der Aufruf der Funktion muss entsprechend angepasst werden. Ggf. kann der Wertebereich der Zufallszahl noch erhöht werden um die Glockenform zu verdeutlichen, obwohl diese auch bei den Grenzen [0,10) erkennbar wird.
Natürlich könnte man nun noch ein Wahlschalter in das Arbeitsblatt einbauen, so dass man deutlich den Unterschied zwischen Gleichverteilung und Normalverteilung sieht (die Fehlerindikatoren solten gelöscht werden), aber es wird ja, dank Deiner Vorarbeit auch wohl so deutlich, was zu beachten ist.
Gruß,
Uwe
P.S.
Wenn weitere Fragen, versuche ich Sie gerne mit Euch zu klären, doch vielleicht gleiten wir hier langsam in das Fachgebiet der Statisik ab und verlieren EXCEL aus den Augen.
Anzeige
AW: Zufallszahlen gleichverteilt - normalverteilt ?
19.03.2004 01:43:45
Uwe
Lesen, bildet; und so hat auch mich das Studium der Liste der in EXCEL verfügbaren Statistik-Funktionen fündig werden lassen.
Es gibt die Funktion x = NOMINV( y, m, s ), die das invertierte Gegenstück zur Verteilungsfunktion y = NOMVERT( x, m, s ) darstellt, und die zu vorgegeben p=Element[0,1) das p-Quantil liefert. Matematisch geschrieben:
F(X)N(m;s^2 = NOMVERT(x,m,s^2) := Verteilungsfkt. der N(m;s^2)-verteilte Zufallszahl X.
F-1(p) = NOMINV( y, m, s ) mit y = element [0;1)
Der NOMINV-Graph entspricht dem Graphen, der in den bisherigen Diagrammen in gün gezeichnet ist.
y stellt nun gleichverteilte Zufallszahl aus der Funktion ZUFALLSZAHL() dar, so dass die Auswertung der Formel unmittelbar zum gesuchten normalverteiltem Schwankungswert um den Mittelwert der Reihe führt. Es gilt also:
x0 = m + NOMINV( ZUFALLSZAHL(), m , s) mit m für den Mittelwert und s der zugehörigen Standardabweichung.
Trägt man in die Zelle A1 den Mittelwert ein, in die Zelle A2 die Standardabweichung, dann kann man in Spalte B1 die Formel
=NOMINV(ZUFALLSZAHL(); $A$1; $A$2)
eintragen. Kopiert man diese Formel über eine hinreichende Anzahl von Zellen in der Spalte B, so können Klassenhäufigkeiten festgestellt werden (rote Linie im Diagramm als ergebnis von ca. 12000 Realisierung). Die Summenkurve wird annähern zur Verteilungsfunktion und ist in grün dargestellt.
Da diese Graphen sich jedoch auf die Schwankung um den Mittelwert beziehen, ergibt sich eine normalverteilte Zufallsvariable letztlich zu:
=$A$1+NOMINV(ZUFALLSZAHL();$A$1;$A$2)
Userbild

Damit sollte das Thema zu einem zufriedenstellenden Abschluss gebracht sein, wenn mir kein Fehler bei der ausarbeitung unterlaufen ist.
Gruß!
Anzeige
AW: Zufallszahlen normalverteilt (exakte Lösung)
19.03.2004 01:45:01
Uwe
Lesen, bildet; und so hat auch mich das Studium der Liste der in EXCEL verfügbaren Statistik-Funktionen fündig werden lassen.
Es gibt die Funktion x = NOMINV( y, m, s ), die das invertierte Gegenstück zur Verteilungsfunktion y = NOMVERT( x, m, s ) darstellt, und die zu vorgegeben p=Element[0,1) das p-Quantil liefert. Matematisch geschrieben:
F(X)N(m;s^2 = NOMVERT(x,m,s^2) := Verteilungsfkt. der N(m;s^2)-verteilte Zufallszahl X.
F-1(p) = NOMINV( y, m, s ) mit y = element [0;1)
Der NOMINV-Graph entspricht dem Graphen, der in den bisherigen Diagrammen in gün gezeichnet ist.
y stellt nun gleichverteilte Zufallszahl aus der Funktion ZUFALLSZAHL() dar, so dass die Auswertung der Formel unmittelbar zum gesuchten normalverteiltem Schwankungswert um den Mittelwert der Reihe führt. Es gilt also:
x0 = m + NOMINV( ZUFALLSZAHL(), m , s) mit m für den Mittelwert und s der zugehörigen Standardabweichung.
Trägt man in die Zelle A1 den Mittelwert ein, in die Zelle A2 die Standardabweichung, dann kann man in Spalte B1 die Formel
=NOMINV(ZUFALLSZAHL(); $A$1; $A$2)
eintragen. Kopiert man diese Formel über eine hinreichende Anzahl von Zellen in der Spalte B, so können Klassenhäufigkeiten festgestellt werden (rote Linie im Diagramm als ergebnis von ca. 12000 Realisierung). Die Summenkurve wird annähern zur Verteilungsfunktion und ist in grün dargestellt.
Da diese Graphen sich jedoch auf die Schwankung um den Mittelwert beziehen, ergibt sich eine normalverteilte Zufallsvariable letztlich zu:
=$A$1+NOMINV(ZUFALLSZAHL();$A$1;$A$2)
Userbild

Damit sollte das Thema zu einem zufriedenstellenden Abschluss gebracht sein, wenn mir kein Fehler bei der ausarbeitung unterlaufen ist.
Gruß!
Anzeige
AW: Zufallszahlen normalverteilt (exakte Lösung)
19.03.2004 09:53:18
Karl-Otto Reimann
Hallo Uwe
Eigentlich alles kalter Kaffee. Viel interessanter ist folgende Frage:
Lassen sich für =ZUFALLSBEREICH(0;36) mit Hilfe der Binomial- oder
Poissonverteilung hinreichend genaue Vorhersagen für eine Rotation
bei Zugrundelegung des Rotationsmittelwertes (63,72%) machen.
Mit Lösung der Frage verbindet sich automatisch Freibier für alle.
Gruß
Karl-Otto
AW: Nein, Karl-Otto, kalter Kaffee ...
19.03.2004 10:08:25
Uwe
...ist dies bestimmt nicht, denn es ist m.E. die Antwort auf die gestellte Ausgangsfrage, wohingegen die vorherigen Hinweise an "Antasten" an die Lösung war!
Das Bier ist nicht mein Ding, so dass ich am Wettbewerb wohl nicht teilnehmen werde :-), dennoch läßt sich meines erachtens nahezu jede Verteilung mit dem beschriebenen Verfahren simulieren, wenn nur eine Verteilungsfunktion dargestellt werden kann.
Schönes Wochenende und Gruß!
Anzeige
Besten Dank
19.03.2004 12:22:26
Alex
Hallo Ich bin derjenige, der die Ausgangsfrage gestellt hatte. Also besten Dank für die zahlreichen und umfangreichen Beiträge!! Versuche jetzt mal in Excel eine normalverteilte Zufallsvariable zu erzeuge.
Mit freundlichen Grüßen
Alex
AW: Zufallszahlen normalverteilt (exakte Lösung)
19.03.2004 14:02:48
Alex
Hallo Uwe!
ich habe deinen Vorschlag = m + NOMINV(ZUFALLSZAHL(),m,s) gerade mal ausprobiert mit einem Mittelwert von 0,5 und einer Standardabweichung von 0,5 und habe aber leider keine Glockenkurve erhalten. Die höheren Werte kamen häufiger vor als geringere Werte. Kann es daran liegen, dass meine Excelversion überaltet ist? Bei mir heißt die Funktion auch nicht NOMINV sondern NORMINV
=$G$1+NORMINV(ZUFALLSZAHL();$G$1;$G$2) Bei 30.000 Durchläufen habe ich diese Häufigkeiten erhalten:
Intervall Häufigkeit
0,00 0,10 388,00
0,10 0,20 568,00
0,20 0,30 755,00
0,30 0,40 1020,00
0,40 0,50 1284,00
0,50 0,60 1631,00
0,60 0,70 1839,00
0,70 0,80 2048,00
0,80 0,90 2284,00
0,90 1,00 2331,00
Anzeige
AW: Hallo, Alex, eigentlich ...
19.03.2004 20:37:18
Uwe
... sollte sich die Glockenkurve bereits bei ca. 200 Werten erahnen lassen.
In der Formel direkt über dem Diagramm ist jedoch die Addition des Mittelwertes fals, da diese Größe ja natürlich bereits bei den Parametern der unterstellten Normalverteilung erfasst ist.
Schau also einmal, ob Du mit dieser Datei klarkommst und sie weiterentwickeln kannst

Die Datei https://www.herber.de/bbs/user/4494.zip wurde aus Datenschutzgründen gelöscht

(Zip ca. 270kb)
Gruß,
Uwe

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige