Zufallszahlen ermitteln

Zufallszahlen ohne Wiederholung in Excel VBA

Mit diesem VBA Makro erzeugen Sie in Excel Zufallszahlen ohne Wiederholung. Sie geben die gewünschte Obergrenze ein, das Makro liefert eine zufällige Reihenfolge aller ganzen Zahlen ab 1 bis zu dieser Obergrenze. Jede Zahl erscheint dabei genau einmal. So erstellen Sie schnell eindeutige Ziehungen, durchmischen Zahlenreihen oder bereiten Stichproben vor.

Was das Makro leistet und wann es sinnvoll ist

Das Makro fragt beim Start nach der gewünschten Menge an Zufallszahlen. Geben Sie beispielsweise 20 ein, erzeugt es die Zahlen 1 bis 20 in zufälliger Reihenfolge. Die Ausgabe erfolgt in Spalte B ab Zelle B1. Damit eignet es sich für Auslosungen, Reihenfolgen ohne Dopplungen, zufällige Permutationen von Indexwerten oder das Mischen eines Zahlenbereichs. Die Kernaussage des Verfahrens lautet, dass doppelte Werte ausgeschlossen werden und jede Zahl genau einmal vorkommt.

Schritt für Schritt zum Ergebnis

1. Öffnen Sie die Arbeitsmappe und drücken Sie Alt F11, um den VBA Editor zu starten.
2. Fügen Sie über Einfügen, Modul ein neues Standardmodul hinzu.
3. Kopieren Sie den untenstehenden Code vollständig in das Modul.
4. Kehren Sie zu Excel zurück und starten Sie das Makro über Ansicht, Makros, Makros anzeigen, zufallszahl, Ausführen.
5. Geben Sie in der Eingabeaufforderung die Obergrenze als ganze Zahl ein, zum Beispiel 20.
6. Prüfen Sie die Ausgabe in Spalte B, dort stehen die eindeutigen Zufallszahlen in zufälliger Reihenfolge.

VBA Code für Zufallszahlen ohne Wiederholung

Public Sub zufallszahl()
Dim zahl(10000) As Integer
Dim zahlneu As Integer
anzahl = InputBox("Wieviele Zufallszahlen sollen ermittelt werden?")
'Zufallszahl 1 bis 28 ermitteln
For i = 1 To anzahl
zahl(i) = Int((anzahl * Rnd) + 1)
zahlneu = zahl(i)
    'Auf doppelte Zahlen prüfen und neue Zahl berechnen
    For n = 1 To i - 1
        If zahl(n) = zahlneu Then
            zahl(i) = Int((anzahl * Rnd) + 1)
            i = i - 1
        End If
    Next n
'Zahlen in Spalte B schreiben
Sheets(1).Cells(i, 2).Value = zahl(i)
Next i
End Sub

So funktioniert der Algorithmus

Das Makro erstellt ein Integer Array mit Platz für bis zu 10000 Werte. Für jede Position i wird eine Zufallszahl zwischen 1 und der gewünschten Obergrenze ermittelt. Danach prüft eine innere Schleife, ob diese Zahl bereits zuvor vorkam. Falls ja, wird für die aktuelle Position eine neue Zufallszahl ermittelt und die Prüfung wiederholt, bis ein eindeutiger Wert gefunden ist. Abschließend wird jeder gefundene Wert in Spalte B geschrieben. Auf diese Weise entsteht eine zufällige Permutation ohne doppelte Zahlen.

Praxisbeispiel, Zufallszahlen 1 bis 20

Starten Sie das Makro und geben Sie 20 ein. In Spalte B erscheinen die Zahlen 1 bis 20 in zufälliger Reihenfolge. Jede Zahl kommt nur einmal vor. Dies ist nützlich, wenn Sie Sitzreihenfolgen vergeben, Startnummern mischen oder eine zufällige Ziehung durchführen möchten.

Typische Fehlerquellen und wie Sie diese vermeiden

Geben Sie nur ganze Zahlen ein. Nicht numerische Eingaben führen zu Laufzeitfehlern. Verwenden Sie nur positive Werte größer oder gleich 1. Die maximale sinnvolle Obergrenze beträgt 10000, weil das Array bis Index 10000 gefüllt wird. Größere Werte führen zu Fehlern bei der Arrayadressierung. Denken Sie daran, dass das Makro in Spalte B der ersten Tabelle schreibt. Sichern Sie wichtige Daten oder verwenden Sie ein separates Blatt, damit keine Inhalte überschrieben werden. Wenn Sie die Abfrage abbrechen oder 0 eingeben, wird keine Zahl geschrieben. Prüfen Sie bei Bedarf, ob tatsächlich Werte in Spalte B erzeugt wurden.

Leistung und Alternativen für große Datenmengen

Das Verfahren prüft jede neu erzeugte Zahl gegen alle zuvor erzeugten Zahlen. Das ist nachvollziehbar und einfach, kann bei sehr großen Obergrenzen jedoch mehr Rechenzeit beanspruchen. Für große Datenmengen bieten sich alternative Ansätze an, etwa das Erzeugen einer sortierten Liste 1 bis n und das anschließende Mischen mit einem bekannten Algorithmus wie dem Fisher Yates Shuffle. Ohne zusätzlichen Code lässt sich in Excel zudem eine Hilfsspalte mit Zufallswerten erzeugen und die Liste 1 bis n danach sortieren. Beide Varianten liefern ebenfalls Zufallszahlen ohne Wiederholung.

Bewährte Hinweise für Excel VBA Zufallszahlen

Wenn die erzeugte Reihenfolge zwischen Sitzungen variieren soll, verwenden viele Anwender vor dem ersten Aufruf der Zufallsfunktion eine Initialisierung über Randomize. Wenn stattdessen eine reproduzierbare Reihenfolge gewünscht ist, kann auf diese Initialisierung verzichtet werden. Aktivieren Sie Option Explicit in eigenen Projekten, damit alle Variablen sauber definiert werden. Beschleunigen Sie umfangreiche Läufe, indem Sie vor dem Start der Routine Bildschirmaktualisierung und automatische Berechnung vorübergehend deaktivieren und danach wieder aktivieren. Platzieren Sie die Ausgabe auf dem gewünschten Blatt und leeren Sie die Zielspalte vor dem Schreiben, damit keine Altwerte stehen bleiben.

Fazit

Mit dem gezeigten Makro erzeugen Sie in Excel VBA Zufallszahlen ohne Wiederholung schnell und zuverlässig. Die Lösung ist leicht einzurichten, liefert eine echte Permutation des Bereichs 1 bis n und eignet sich für Auslosungen, Stichproben und alle Szenarien, in denen doppelte Werte ausgeschlossen sein müssen.