Gleichmäßig verteilte Zufallszahlen


Standard-Funktionen der Tabellen-Kalkulation


Leider ist derzeit nur die Funktion =ZUFALLSZAHL() ohne Einschränkungen verwendbar. Es wird allerdings gezeigt, wie man mit ihrer Hilfe alle anderen in der Praxis benötigten Zufalls-Funktionen berechnen kann.


Funktion ZUFALLSBEREICH()

Diese Funktion wird (nur) in neueren Versionen von Kalkulations-Programmen geboten.

Je nach Programm-Version wird sie entweder bei jeder Änderung neu berechnet, oder nur einmal bzw. bei Änderung ihrer Argumente. Man kann diese Funktion daher nicht als portabel bezeichnen.

Wegen des unsicheren Verhaltens wird von der Verwendung abgeraten: An Stelle dieser Funktion verwendet man besser jene portable (!) Formel, die im Kapitel ↓ Gleichverteilte Ganze Zufallszahlen gezeigt wird.

Algorithmen



Ein Pseudo-Zufalls-Generator wird initialisiert, d.h. auf einen definierten Anfangswert gesetzt. Danach wird aus dem Anfangswert die erste Zufallszahl berechnet, aus dieser die nächste usw. Im laufenden Betrieb wird eine Kette von Zufallszahlen berechnet.



In den meisten praktischen Anwendungen ist erwünscht, 'wirklich' zufällige Zahlen zu erzeugen. Dazu initialisiert man den Pseudo-Zufalls-Generator meist mit einer Zeit, z.B. mit der aktuellen Anzahl von Millisekunden oder Microsekunden.
Mit einer Anfangs-Zahl von 0...999 (Millisekunden) kann man allerdings nur genau 1000 verschiedene Folgen von Pseudo-Zufallszahlen erzeugen.

Die Tabellen-Kalkulation bietet mit der Funktion ZUFALLSZAHL() einen Zufalls-Generator, den man allerdings nicht selbst initialisieren kann: Wenn das erwünscht ist, muss man die Generator-Funktion selbst programmieren.



Die von einem Pseudo-Zufalls-Generator erzeugten Zahlen-Folgen wiederholen sich zwangsweise. Bei einem guten Generator ist die Periode der Wiederholung groß, z.B. Wiederholung erst nach einigen Mio erzeugter Zahlen.

Der Zufalls-Algorithmus sollte Zahlen erzeugen, die im jeweiligen Intervall (Untergrenze ... Obergrenze) absolut gleich verteilt sind. In der Praxis enthalten die Verteilungs-Funktionen aller Pseudo-Zufalls-Generatoren mehr oder wenige viele Lücken.
Diese Lücken sind allerdings für die meisten Anwendungen der Tabellen-Kalkulation vernachlässigbar schmal.



Im nächsten Kapitel wird ein ↓  LCG Live vorgestellt.
Man kann ihn relativ einfach selbst programmieren: Damit verfügt man auch in der Tabellen-Kalkulation über einen Zufalls-Generator, den man selbst initialisieren kann.



Gleich verteilte Zufallszahlen im Bereich 0..1 sind deshalb besonders wichtig, weil die meisten Algorithmen (Rechen-Regeln) und Formeln genau solche Zufallszahlen verwenden.

Zur Berechnung fast aller anderen Zufalls-Daten verwendet man als Werkzeug solche Standard-Zufallszahlen, z.B. usw.

Linear kongruenter Zufalls-Generator (LCG)



Live Ganzzahlige Ergebnisse:
x[1] = 0
x[2] = 0
x[3] = 0
usw.
Die erzeugten Werte liegen gleich verteilt im Bereich 0<=x<m

Erst im letzten Schritt werden die Ganzzahligen Ergebnisse normalisiert, d.h. in den (Gleitkomma)-Bereich 0...1 umgerechnet:
z[n] = x[n] / m
Das ergibt die Standard-Zufallszahlen
z[1] = 0
z[2] = 0
z[3] = 0
usw.
Die erzeugten Werte liegen gleich verteilt im Bereich 0<=z<1



Die angegebenen Werte der Vorgabe-Variablen a, c, m werden in einigen Programmiersprachen verwendet.

Zur Initialisierung kann man in Zelle B4 jede beliebige Zahl eingeben.
Bei gegebenem Anfangswert wird stets die gleiche Folge von Pseudo-Zufallszahlen erzeugt.

Die Modulo-Funktion hat in der Tabellen-Kalkulation den Namen REST()

Von Zelle B8 abwärts werden Pseudo-Zufallszahlen im Bereich 0...z...1 erzeugt, genauso wie mit der Funktion ZUFALLSZAHL()
Hier kann man jedoch mit den Vorgaben experimentieren und jede Zufalls-Folge bei Bedarf exakt reproduzieren.
Eine Tabellen-Kalkulation unterscheidet nicht zwischen Ganzen und Gleitkomma-Zahlen und rechnet daher diesen Algorithmus mit Gleitkomma-Zahlen.

Gleichmäßig verteilte Gleitkomma-Zufallszahlen


Vorgaben:

Untergrenze: ug
Obergrenze:  og
Für beide Grenzen kann man beliebige Gleitkomma-Zahlen einsetzen.

Formel:

z = zufallszahl() * (og - ug) + ug
In dieser Formel ist zufallszahl() eine Funktion, welche gleich verteilte Zufallszahlen im Bereich 0...1 erzeugt.


Die gewünschten Grenzen werden in den Bereich B1:B2 eingetragen.

In der Formel von Zelle A5 sind die Adressen der Grenzen absolut (mit vorangestelltem $-Zeichen) programmiert:

Daher kann man die Formel von Zelle A5 kopieren und beliebig oft einsetzen, oder - wie im Beispiel angedeutet - beliebig weit nach unten ausfüllen.

In einem Kalkulations-Programm werden mit jedem Klick auf die Taste F9 neue Zufallszahlen erzeugt.


Die Funktion Zufallszahl_Bereich() wird im Kapitel Basic dieser Seite genauer vorgestellt.
Anwendung:
=Zufallszahl_GleitKomma($B$1;$B$2)



Die real berechneten Werte von Mittelwert, Minimum und Maximum nähern sich den theoretischen Werten umso mehr, je größer die Anzahl der erzeugten Zufallszahlen ist.

Gleichmäßig verteilte Ganze Zufallszahlen


Vorgaben:

Untergrenze: ug
Obergrenze:  og
Für beide Grenzen kann man beliebige Ganze Zahlen (ohne Nachkomma-Anteil) einsetzen.

Formel:

z = ganzzahl(zufallszahl() * (og-ug+1)) + ug
In dieser Formel ist zufallszahl() eine Funktion, welche gleich verteilte Zufallszahlen im Bereich 0...1 erzeugt.



In der Formel von Zelle D5 sind die Adressen der Grenzen (E1:E2) absolut (mit vorangestelltem $-Zeichen) programmiert: Daher kann man die Zufalls-Formel von Zelle D5 kopieren und beliebig oft einsetzen.

Die Formel der Zelle D5 wird im Beispiel nach unten ausgefüllt. So kann man beliebig viele Zufallszahlen erzeugen.

In den Ergebnissen sind die beiden Grenz-Werte selbst enthalten. Man kann z.B. einen Würfel mit ug=1 und og=6 programmieren.

In einem Kalkulations-Programm werden mit jedem Klick auf die Taste F9 neue Zufallszahlen erzeugt.


Die Funktion Zufallszahl_Ganz() wird im Kapitel Basic dieser Seite genauer vorgestellt.
Anwendung:
=Zufallszahl_Ganz($E$1;$E$2)



Die real berechneten Werte von Mittelwert, Minimum und Maximum nähern sich den theoretischen Werten umso mehr, je größer die Anzahl der erzeugten Zufallszahlen ist.



Wenn man Ganze Zufallszahlen 0..z..10 in 2 Intervallen zählt, dann enthält der 1. Zähler alle Treffer mit Werten 0..4 und der 2. Zähler alle Treffer 5...9: In diesem Fall sind die Ergebnisse vergleichbar, Treffer mit dem Wert 10 werden jedoch ignoriert.

Man kann das 2. Intervall auf alle Treffer 5..10 ausdehnen. In diesem Fall werden zwar alle Werte gezählt, die beiden Intervalle sind jedoch ungleich breit und daher nicht vergleichbar.

Planen sie beim Zählen von Ganzen Zahlen besonders sorgfältig die Anzahl, Breite und Grenzen der Intervalle.



Beispiel: Die Formel
=ZEICHEN(GANZZAHL(ZUFALLSZAHL()*26)+65)
erzeugt zufällige Zeichen A...Z

Details zur Erzeugung von → Zufalls-Zeichen und Zufalls-Text

Basic-Funktionen


Download des Basic-Moduls Random.bas mit diesen und anderen Funktionen.

Die (gleichnamigen) Basic-Funktionen dieses Moduls sind ungekürzt: Sie sind umfangreicher, besser gegen Sonderfälle abgesichert, jedoch weniger übersichtlich.


Für MS-Excel muss man diese Option mit einem führenden Single-Quote Zeichen abschalten:
' Option VBASupport 1

Für LibreOffice oder OpenOffice muss man diese Option einschalten:
Option VBASupport 1



Basic-Quelltext:
Function Zufallszahl_Ganz(Untergrenze As Integer, Obergrenze As Integer) As Integer
Dim iz As Integer
iz = Int(Rnd * (Obergrenze - Untergrenze + 1)) + Untergrenze
Zufallszahl_Ganz = iz
End Function



Basic-Quelltext:
Function Zufallszahl_GleitKomma(Untergrenze as Double, Obergrenze As Double) As Double
Dim z As Double
z = Rnd * (Obergrenze - Untergrenze) + Untergrenze
Zufallszahl_GleitKomma = z
End Function