Generische Formel
=MATCH(RAND(),cumulative_probability)
Zusammenfassung
Um eine Zufallszahl zu generieren, die mit einer bestimmten Wahrscheinlichkeit gewichtet ist, können Sie eine Hilfstabelle zusammen mit einer Formel verwenden, die auf den Funktionen RAND und MATCH basiert.
In dem gezeigten Beispiel lautet die Formel in F5:
=MATCH(RAND(),D$5:D$10)
Erläuterung
Diese Formel basiert auf der Hilfstabelle, die im Bereich B4: D10 angezeigt wird. Spalte B enthält die sechs Zahlen, die wir als Endergebnis haben möchten. Spalte C enthält das jeder Zahl zugewiesene Wahrscheinlichkeitsgewicht, das als Prozentsatz eingegeben wird. Spalte D enthält die kumulative Wahrscheinlichkeit, die mit dieser Formel in D5 erstellt und nach unten kopiert wurde:
=SUM(D4,C4)
Beachten Sie, dass wir die kumulative Wahrscheinlichkeit absichtlich um eine Zeile nach unten verschieben, sodass der Wert in D5 Null ist. Dies soll sicherstellen, dass MATCH eine Position für alle Werte bis Null finden kann, wie unten erläutert.
Um einen zufälligen Wert unter Verwendung der gewichteten Wahrscheinlichkeit in der Hilfstabelle zu generieren, enthält F5 diese Formel, die nach unten kopiert wurde:
=MATCH(RAND(),D$5:D$10)
In MATCH wird der Suchwert von der RAND-Funktion bereitgestellt. RAND generiert einen zufälligen Wert zwischen Null und 1. Das Lookup-Array ist der Bereich D5: D10, der gesperrt ist, damit er sich nicht ändert, wenn die Formel in die Spalte kopiert wird.
Das dritte Argument für MATCH, Übereinstimmungstyp, wird weggelassen. Wenn der Übereinstimmungstyp weggelassen wird, gibt MATCH die Position des größten Werts zurück, der kleiner oder gleich dem Suchwert * ist. In der Praxis bedeutet dies, dass sich die MATCH-Funktion entlang der Werte in D5: D10 bewegt, bis ein größerer Wert gefunden wird, und dann zur vorherigen Position "zurückspringt". Wenn MATCH auf einen Wert stößt, der größer als der größte letzte Wert in D5: D10 ist (im Beispiel .7), wird die letzte Position zurückgegeben (im Beispiel 6). Wie oben erwähnt, ist der erste Wert in D5: D10 absichtlich Null, um sicherzustellen, dass Werte unter 0,1 von der Nachschlagetabelle "abgefangen" werden und eine Position von 1 zurückgeben.
* Werte im Suchbereich müssen in aufsteigender Reihenfolge sortiert werden.
Zufällig gewichteter Textwert
Um einen zufällig gewichteten Textwert (dh einen nicht numerischen Wert) zurückzugeben, können Sie Textwerte im Bereich B5: B10 eingeben und dann INDEX hinzufügen, um einen Wert in diesem Bereich basierend auf der von MATCH zurückgegebenen Position zurückzugeben:
=INDEX($B$5:$B$10,MATCH(RAND(),D$5:D$10))
Anmerkungen
- Ich bin in einem Forumsbeitrag auf mrexcel.com auf diesen Ansatz gestoßen
- RAND ist eine flüchtige Funktion und wird bei jeder Änderung des Arbeitsblatts neu berechnet
- Wenn Sie zufällige Werte haben, verwenden Sie Einfügen von speziellen> Werten, um die Formel bei Bedarf zu ersetzen
