Gewichtete Zufallszahlen mittels WorksheetFunction.RandBetween erzeugen

[Schülerin]

Hallo liebe Excel Profis,

ich erzeuge in meinem VBA Code Zufallszahlszahlen.  Also: Zufahlszahl = WorksheetFunction.RandBetween(1,10). Diese Zufahllszahlen sind aber leider normalverteilt. Wie
kann ich mit dieser Funktion gewichtete Zufahlszahlen erzeugen. Also die 2 soll z.B. doppelt so häufig erzeugt werden wie normal und die 6 nur mit einer Wahrscheinlichkeit von 50 %.

Wie sieht dafür der Code aus?

Vielen Dank für eure Bemühungen im voraus.

[schauan]

Hallo Schülerin,

auf excelformeln.de gibt es dazu ein paar Lösungen im Beitrag
http://www.herber.de/excelformeln und bitte suchen .../formeln.html?welcher=401

In VBA würde die Vergleichslösung =VERGLEICH(...) z.B. o aussehen:
WorksheetFunction.Match(Rnd(), Array(0, 0.05, 0.1, 0.2, 0.3, 0.6, 0.8))

Du müsstest jetzt schauen, dass Du die Verteilung mit Hilfe des Array auf Deine Zwecke anpasst. Bei 1 bis 10 müsstest Du 10 Arrayelemente einbauen und dann entsprechend gewichten. Im Beitrag steht am Anfang, dass die Summe der Gewichte 1 ergeben soll - bei dem Vergleichsbeispiel ist das dort nicht der Fall. Ich vermute, dass die Gewichtung sich dann auf die Gesamtsumme der Anteile bezieht, was für den Anwender dann nicht so leicht zu durchschauen wäre.

[RPP63]

Guten Morgen!
Schöne Denksportaufgabe, André!
Formel ist: =VERGLEICH(ZUFALLSZAHL();{0;0,05;0,1;0,2;0,3;0,6;0,8})
Ich habe mal versucht, das Formelkonstrukt aus Excelformeln zu verstehen.
(wenn die eine Formel als "Formel-Knallfrosch" bezeichnen, ist das deren Award)
Vorweg: Diese Variante benötigt keine Array-Quersumme von 1, sie wäre hier sogar kontraproduktiv.

Um das Prinzip zu verstehen, betrachten wir zunächst mal die normalverteilte Zufallszahl (im folgenden als ZZ bezeichnet) zwischen 1 und 10:
Dies geht per "normaler" Formel ja einfach:
=ZUFALLSBEREICH(1;10)
oder auch per:
=RUNDEN(ZUFALLSZAHL()*10;0)
obige Vergleich-Formel ermittelt zunächst eine ZZ zwischen 0 und 1 und sucht die Zahl aus dem Array, die kleiner oder gleich dem Wert für ZZ ist.
Um zunächst die norm.vert. ZZ zwischen 1 und n zu ermitteln, müssen die Abstände im Array gleich sein, nämlich 1/n, hier also 1/10 = 0,1:

	A	B	C	D	E	F	G	H	I	J	K	L
1		Wahrscheinlichkeit normalverteilt										2
2	Array	0,0	0,1	0,2	0,3	0,4	0,5	0,6	0,7	0,8	0,9	4
3	Ergebnis	1	2	3	4	5	6	7	8	9	10	8

Formeln der Tabelle

Zelle Formel L1 =RUNDEN(ZUFALLSZAHL()*10;0) L2 =ZUFALLSBEREICH(1;10) L3 =VERGLEICH(ZUFALLSZAHL();B2:K2)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Für gewichtete ZZ zwischen 1 und 10 ist die folgende Logik ja noch "einfach":
Der "Grundterm" 1/10 bleibt bestehen.
Damit die 2 doppelt so häufig "gezogen" wird, muss der Abstand zwischen dem zweiten und dritten Term des Array doppelt so groß als "normal" sein, also 2*1/n, also 0,2.
Die 6 soll ja nur halb so häufig wie die "normalen" Zahlen sein, also folgt, dass der Abstand zwischen dem sechsten und siebten Term nur halb so groß sein darf, also 0,05.
Weiter geht es wieder mit 0,1.
Führt zur Lösung:

	A	B	C	D	E	F	G	H	I	J	K	L
5		Wahrscheinlichkeit gewichtet
6	Array	0,0	0,1	0,3	0,4	0,5	0,6	0,65	0,75	0,85	0,95
7	Ergebnis	1	2	3	4	5	6	7	8	9	10	4

Formeln der Tabelle

Zelle Formel L7 =VERGLEICH(ZUFALLSZAHL();B6:K6)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Diese Logik kann man jetzt auf eine beliebige (allerdings fortlaufende) ZZ-Menge mit beliebiger Wichtung für beliebige %-Werte anwenden ...

Hat Spaß gemacht, "denkt" sich

Ralf

[RPP63]

Mir fiel gerade auf, dass die 10 in obigem Beispiel ja nur die halbe Wahrscheinlichkeit besitzt ...
Damit ihr weiterhin der Range von 0,1 zusteht muss die ZZ mit 1,05 multipliziert werden:
=VERGLEICH(ZUFALLSZAHL()*1,05;B6:K6)

Gruß Ralf

[WillWissen]

Hi Ralf,

ich glaube, die Frage nach gewichteten ZZ wurde schon mal gestellt - bin mir aber nicht sicher.

Da du gerade eine sehr schöne Ausarbeitung des Themas "abgeliefert" hast - vor allem mit den nachvollziehbaren mathematischen Erklärungen, bitte ich dich zu überlegen, ob du diese nicht hier zur Verfügung stellen möchtest.

[RPP63]

Hallo Günter!
Danke für die Blumen!  :05:
Vorher müsste ich das Ganze noch mathematisch beweisen, bisher ist es ja nicht viel mehr als try and error.

Aber ich behalte dies mal im Auge. :21:

Gruß Ralf

[RPP63]

Tatsächlich!
Mein Magengrummeln hat sich bewahrheitet!
Meinen Beitrag #4 möchte ich vergessen machen, aber:
das ungute Gefühl hat mich erneut motiviert.
Deshalb zunächst die Lösung:

	A	B	C	D	E	F	G	H	I	J	K
1		ZZgew: 3									Prüfung100%
2	Array	0%	10,5263%	21,0526%	42,1053%	57,8947%	68,4211%	78,9474%	84,2105%	94,7368%	100,0000%
3	Ergebnis	1	2	3	4	5	6	7	8	9	Hilfszelle
4	Häufigkeit	2	2	4	3	2	2	1	2	1	5,2632%
5	Aufschrift	A	C	F	I	L	O	Q	T	Y
6		Aufschrift: F

Zelle	Format	Wert
C2	0,0000%	0,105263157894737
K4	0,0000%	5,26315789473684E-02
B1	"ZZgew: "0	3
B6	"Aufschrift: "@	F

Zelle	Formel
C2	=B2+B4*$K4
K2	=J2+K4*J4
K4	=1/SUMME(B4:J4)
B1	=VERGLEICH(ZUFALLSZAHL();B2:J2)
B6	=INDEX(B5:J5;VERGLEICH(ZUFALLSZAHL();B2:J2))

Der Rest mit mathematischer Logik (by the way: bis auf die geniale Excel-Formeln-Lösung rest by me) folgt ...

Gruß Ralf