Sammlung ==> #21 VBA: RANDOM-Tool

VBA: RANDOM-Tool:

Gastbeitrag

In speziellen Situationen benötigt man eine Reihe von Zufallszahlen. Die Generierung von Zufallszahlen über Excelfunktionen hat gewisse Schwächen. Dagegen ist es mit relativ wenig VBA-Code möglich, eine beliebige Anzahl von Zufallszahlen zu erhalten, die für individuelle Zwecke verwendet werden können. Gastautor Hans Bon stellt excelfunktionen.de ein entsprechendes Tool zur Verfügung. Der VBA Code ist transparent und das File stellen wir als Download zur Verfügung.

Vorbemerkung: RANDOM-Verfahren bei der Excelfunktion ZUFALLSZAHL() mit entsprechenden Nachteilen

Die Excelfunktion =ZUFALLSZAHL wirft eine Zufallszahl aus, die zwischen 0 und 1 liegt. Durch Multiplikation dieser Zahl mit bspw. 10, 100, 1000, 10000 kann die Zahl auf entsprechende Werte über Null hochgesetzt werden. Durch die Funktion RUNDEN können Dezimalstellen abgeschnitten werden. Wenn zum Beispiel in der Zelle A1 die Funktion =ZUFALLSZAHL() einen Wert 0,13707076 zufällig gezogen hätte, kann dieser in der Zelle B1 durch die Funktion =RUNDEN(A1*10000;0) auf die vierstellige Zufallszahl 1371 hochgesetzt werden. Wenn man entsprechend mehrere Zufallszahlen generieren möchte, können die Funktionen in A1 und B1 entsprechend weit nach unten ausgefüllt werden. Die Ergebnisse mehrerer Zufallszahlen müssen auf mögliche Mehfachergebnisse einzelner Werte getestet werden, sofern man den Anspruch in speziellen Situationen hat, dass eine gezogene Zufallzahl nur einmal vorkommen soll. Dies kann mittels ANZAHL-Funktion geschehen. Das Ziehen von Zufallszahlen mittels ZUFALLSZAHL() hat jedoch einen Nachteil: Denn bei jeder erneuten Funktionseingabe an beliebigen Stellen im Excel-Sheet werden die Zufallszahlen durch eine interne Refresh-Aktion immer wieder aktualisiert. Auch beim Abspeichern und Wiederöffnen der Datei sind somit immer wieder neue Werte gezogen - den Effekt kann man auch durch das wiederholte Drücken der F9-Taste nachvollziehen. Diesen Nachteil kann man nur aufheben, in dem die gezogenen Zufallszahlen in die Zwischenablage kopiert werden und an eine beliebige Stelle mittels "Inhalte einfügen" (dort dann nur die Werte einfügen) aus der Zwischenablage in neue Zellen eingefügt werden. Eine Lösung über VBA (Makros) ist hier wesentlich komfortabler - deswegen unser RANDOM-Tool mittels VBA.

Eigenschaften des RANDOM-Tools:

• Es können beliebig viele Zufallszahlen in einer Spalte generiert werden.
• Die Zufallszahlen können auf eine bestimmte Stelligkeit hin gezogen werden (z.B. 6 stellige Zufallszahlen), es gibt also definierbare Wertebereiche.
• Jede Zufallszahl wird innerhalb einer Serie nur einmal gezogen. Aus Anschauungsgründen zeigt das Tool, wie oft Zahlen von der internen Randomfunktion mehrfach gezogen wurden, bei derartigen Mehrfachziehungen erfolgen Neuziehungen zum Ausschluß doppelter Ausgaben.
• Das Tool ist klein, kompakt, anpassbar und übrigens auch für VBA-Schulungszwecke gut geeignet.

Der Code:

Sub makro1()
' Blatt vorbereiten, Zellen löschen und mit Buchstaben belegen
X = 45 ' Anzahl der Maximalversuche
Y = 30 ' Anzahl der zu ziehenden Zahlen
A = 0 ' Anzahl der gezogenen Zahlen
S = 0 ' Anzahl der doppelt gezogenen Zahlen
[e3] = ""
Range("A1").Select
For i = 1 To Y ' Anzahl der Zahlen eingeben + Ende mit F setzen )
ActiveCell = "L"
ActiveCell.Offset(1, 0).Activate
Next i
ActiveCell = "F"

' Zufallszahl ziehen und abgleichen, damit keine doppelt
Range("A1").Activate
[a1] = Int((50 * Rnd) + 1) '[a1] = Int((900000 * Rnd) + 99999)
Do While ActiveCell <> "F" And A < X
z1 = Int((50 * Rnd) + 1) '[a1] = Int((900000 * Rnd) + 99999)
A = A + 1
[e1] = A
Range("A1").Activate
Do While ActiveCell <> z1 And ActiveCell <> "L"
ActiveCell.Offset(1, 0).Activate
If ActiveCell = "F" Then GoTo ab
If ActiveCell = "L" Then ActiveCell = z1
Loop
Loop
ab:

Range("A1").Activate ' doppelt gezogene Zahlen ermitteln
Do While ActiveCell <> "F"
If ActiveCell <> "L" And ActiveCell <> "F" Then S = S + 1
ActiveCell.Offset(1, 0).Activate
Loop
[e3] = A - S

Range("A1").Activate ' F aus der letzten Zelle löschen
ActiveCell.Offset(Y, 0).Activate
ActiveCell = ""
Range("A1").Activate
End Sub

In diesem Einstellungsbeispiel werden 30 Zufallszahlen gezogen, welche jeweils zwischen 0 und 50 liegen. Dafür werden maximal 45 Ziehversuche zur Verfügung gestellt, dies entspricht einer Art Puffer für maximal 15 doppelt gezogene Werte. An diversen Stellen ist dieser Code schnell anpassbar, die wichtigen Stellen sind folgende:

X = 45 ' Anzahl der Maximalversuche
Y = 30 ' Anzahl der zu ziehenden Zahlen

Hier muss X immer deutlich größer Y gesetzt werden, der Größenabstand kann reduziert werden, wenn die Zahlenmenge, aus der gezogen wird, relativ groß ist.

[a1] = Int((50 * Rnd) + 1)

z1 = Int((50 * Rnd) + 1)

Hier kann der Zahlenbereich eingestellt werden. Wünscht man bspw. 6stellige Zufallszahlen, so muss der Code wie folgt sein:

[a1] = Int((900000 * Rnd) + 99999)

z1 = Int((99999 * Rnd) + 99999)

Wir stellen Ihnen das Sheet als ZIP-File Zahl2.zip [9 kb] zum herunterladen hier zur Verfügung!

Für Rückfragen steht Ihnen der Gastautor gerne zur Verfügung. Senden Sie eine Mail an uns, wie bemühen uns dann um Hilfestellung.

Vielen Dank an Hans Bon. Er ist unter anderem auch Autor des Programms Einstufungstest - basierend auf MS-Excel von Basel & Bon.

excelfunktionen.de | Okt. 2003