Clarence Ricci: So generieren Sie Zufallszahlen in Excel ohne Wiederholungen

In diesem Tutorial lernen Sie einige verschiedene Formeln kennen, die Sie in Excel randomisieren können, ohne Zahlen zu wiederholen. Außerdem zeigen wir Ihnen einen universellen Zufallsgenerator, der eine Liste von Zufallszahlen, Datumsangaben und Zeichenfolgen ohne Wiederholungen erstellen kann.

Wie Sie wahrscheinlich wissen, verfügt Microsoft Excel über mehrere Funktionen zum Generieren von Zufallszahlen, z RAND, RANDBETWEEN und RANDARRAY. Es gibt jedoch keine Garantie dafür, dass das Ergebnis einer Funktion frei von Duplikaten ist.

In diesem Tutorial werden einige Formeln zum Erstellen einer Liste eindeutiger Zufallszahlen erläutert. Bitte beachten Sie, dass einige Formeln nur in der neuesten Version von Excel 365 funktionieren, während andere in jeder Version von Excel 2019, Excel 2016, Excel 2013 und früher verwendet werden können.

Holen Sie sich eine Liste eindeutiger Zufallszahlen mit vordefiniertem Schritt

Funktioniert nur in den neuesten Versionen von Excel 365, die dynamische Arrays unterstützen.

Wenn Sie ein Microsoft 365-Abonnement haben, ist dies der einfachste Weg, ein Abonnement zu erhalten Liste eindeutiger Zufallszahlen soll 3 neue dynamische Array-Funktionen kombinieren: SORTBY, SEQUENCE und RANDARRAY:

SORTBY (SEQUENZ (n), RANDARRAY (n))

Wo n ist die Anzahl der zufälligen Werte, die Sie erhalten möchten.

Um beispielsweise eine Liste mit 5 Zufallszahlen zu erstellen, verwenden Sie 5 für n::

=SORTBY(SEQUENCE(5), RANDARRAY(5))

Geben Sie die Formel in die oberste Zelle ein und drücken Sie die Taste Eingeben Wenn Sie die Taste drücken, werden die Ergebnisse automatisch über die angegebene Anzahl von Zellen übertragen.

Wie Sie im folgenden Screenshot sehen können, ist diese Formel tatsächlich sortiert Zahlen von 1 bis 5 in zufälliger Reihenfolge. Wenn Sie einen klassischen Zufallszahlengenerator ohne Wiederholungen benötigen, sehen Sie sich bitte die folgenden Beispiele an.
Einfache Formel zum Erstellen einer Liste eindeutiger Zufallszahlen mit vordefiniertem Schritt

In der obigen Formel definieren Sie nur, wie viele Zeilen gefüllt werden sollen. Alle anderen Argumente werden auf ihren Standardwerten belassen. Dies bedeutet, dass die Liste bei 1 beginnt und um 1 erhöht wird. Wenn Sie eine andere erste Zahl und ein anderes Inkrement wünschen, legen Sie Ihre eigenen Werte für die 3 fest^rd (Anfang) und 4^th (Schritt) Argumente der SEQUENCE-Funktion.

Verwenden Sie beispielsweise diese Formel, um bei 100 zu beginnen und um 10 zu erhöhen:

=SORTBY(SEQUENCE(5, , 100, 10), RANDARRAY(5))

Geben Sie den Startwert und das Inkrement an, um Zufallszahlen ohne Duplikate zu generieren.

Wie diese Formel funktioniert:

Die Formel funktioniert von innen nach außen:

Die SEQUENCE-Funktion erstellt ein Array von fortlaufenden Zahlen basierend auf dem angegebenen oder Standardstartwert und der inkrementierenden Schrittgröße. Diese Sequenz geht an die Array Argument von SORTBY.
Das RANDARRAY Die Funktion erstellt ein Array von Zufallszahlen mit der gleichen Größe wie die Sequenz (in unserem Fall 5 Zeilen, 1 Spalte). Der Min- und Max-Wert spielt keine Rolle, daher können wir diese den Standardeinstellungen überlassen. Dieses Array geht an die by_array Argument von SORTBY.
Das SORTIERE NACH Die Funktion sortiert die von SEQUENCE generierten fortlaufenden Zahlen anhand eines von RANDARRAY erzeugten Arrays von Zufallszahlen.

Bitte beachten Sie, dass diese einfache Formel eine Liste nicht wiederholter Zufallszahlen mit a erstellt vordefinierter Schritt. Verwenden Sie eine erweiterte Version der unten beschriebenen Formel, um diese Einschränkung zu umgehen.

Generieren Sie eine Liste von Zufallszahlen ohne Duplikate

Funktioniert nur in den neuesten Versionen von Excel 365, die dynamische Arrays unterstützen.

Verwenden Sie eine der folgenden generischen Formeln, um Zufallszahlen in Excel ohne Duplikate zu generieren.

Zufällige ganze Zahlen::

INDEX (EINZIGARTIG (RANDARRAY (n* 2, 1, Mindest, max, WAHR)), SEQUENZ (n))

Zufällige Dezimalstellen::

INDEX (EINZIGARTIG (RANDARRAY (n* 2, 1, Mindest, max, FALSE)), SEQUENCE (n))

Wo:

N. ist die Anzahl der zu generierenden Werte.
Mindest ist der minimale Wert.
Max ist der Maximalwert.

Zum Beispiel, um eine Liste von 5 zu erstellen zufällige ganze Zahlen Verwenden Sie diese Formel von 1 bis 100 ohne Wiederholungen:

=INDEX(UNIQUE(RANDARRAY(10, 1, 1, 100, TRUE)), SEQUENCE(5))

Excel-Formel zum Generieren zufälliger Ganzzahlen ohne Wiederholungen

Um 5 eindeutige Zufallszahlen zu generieren Dezimal Zahlen, setzen Sie FALSE in das letzte Argument von RANDARRAY oder lassen Sie dieses Argument weg:

=INDEX(UNIQUE(RANDARRAY(10, 1, 1, 100)), SEQUENCE(5))

Excel-Formel zum Erstellen einer Liste eindeutiger Dezimalzahlen

Wie diese Formel funktioniert:

Auf den ersten Blick mag die Formel etwas knifflig aussehen, aber bei näherer Betrachtung ist ihre Logik sehr einfach:

Die RANDARRAY-Funktion erstellt ein Array von Zufallszahlen basierend auf den von Ihnen angegebenen Min- und Max-Werten. Um zu bestimmen, wie viele Werte generiert werden sollen, multiplizieren Sie die gewünschte Zahl mindestens mit 2 (da das resultierende Array möglicherweise weiß, wie viele Duplikate später entsorgt werden). In diesem Beispiel benötigen wir nur 5 eindeutige Zufallszahlen, erzwingen jedoch, dass RANDARRAY 10 erzeugt, damit UNIQUE über genügend Werte zur Auswahl verfügt.
Das EINZIGARTIG Die Funktion entfernt alle Duplikate und „füttert“ INDEX mit einem duplikationsfreien Array.
Aus dem von UNIQUE übergebenen Array wird das INDEX Funktion extrahiert die erste n Werte wie in SEQUENCE angegeben (in unserem Fall 5 Zahlen). Da die Werte bereits in zufälliger Reihenfolge vorliegen, spielt es keine Rolle, welche überleben.

Erstellen Sie eine Reihe nicht wiederholter Zufallszahlen in Excel

Funktioniert nur in den neuesten Versionen von Excel 365, die dynamische Arrays unterstützen.

Ändern Sie die obige Formel folgendermaßen, um einen Bereich von Zufallszahlen ohne Wiederholungen zu generieren:

INDEX (EINZIGARTIG (RANDARRAY (Reihen* 2, Säulen, Mindest, max)), REIHENFOLGE(Reihen), {1,2,…})

Wo:

Reihen ist die Anzahl der zu füllenden Zeilen.
Säulen ist die Anzahl der zu füllenden Spalten.
Mindest ist der niedrigste Wert.
Max ist der höchste Wert.
{1,2,…} ist eine Array-Konstante, die die Zahlen / Indizes aller Spalten enthält, die mit Zufallswerten gefüllt werden sollen. Verwenden Sie zum Füllen von 2 Spalten beispielsweise die Array-Konstante {1,2}. Um 3 Spalten zu füllen, verwenden Sie {1,2,3} und so weiter.

Verwenden Sie diese Formel, um beispielsweise einen Bereich von 10 Zeilen und 3 Spalten mit eindeutigen Zufallszahlen von 1 bis 100 zu füllen:

=INDEX(UNIQUE(RANDARRAY(20, 3, 1, 100)), SEQUENCE(10), {1,2,3})

Und es wird eine Reihe von zufälligen Dezimalstellen erzeugt, ohne Zahlen zu wiederholen:
Eine Excel-Formel zum Erstellen eines Bereichs nicht wiederholter zufälliger Dezimalzahlen

Wenn Sie ganze Zahlen benötigen, setzen Sie das letzte Argument von RANDARRAY auf TRUE:

=INDEX(UNIQUE(RANDARRAY(20, 3, 1, 100, TRUE)), SEQUENCE(10), {1,2,3})

Generieren eines Bereichs nicht wiederholter zufälliger Ganzzahlen

So verhindern Sie, dass sich Zufallszahlen ändern

Alle Randomisierungsfunktionen in Excel, einschließlich RAND, RANDBETWEEN und RANDARRAY, sind volatil, dh sie werden bei jeder Änderung der Tabelle neu berechnet. Infolgedessen werden bei jeder Änderung neue Zufallswerte erzeugt. Verwenden Sie die Taste, um zu verhindern, dass automatisch neue Nummern generiert werden Inhalte einfügen> Werte Funktion zum Ersetzen von Formeln durch statische Werte. Hier ist wie:

Wählen Sie alle Zellen mit Ihrer Zufallsformel aus und drücken Sie Strg + C. um sie zu kopieren.
Klicken Sie mit der rechten Maustaste auf den ausgewählten Bereich und klicken Sie auf Paste Special > Werte. Alternativ können Sie drücken Umschalt + F10 und dann V.Dies ist die Verknüpfung für diese Option.

Die detaillierten Schritte finden Sie unter So ändern Sie Formeln in Excel in Werte.

So generieren Sie eindeutige Zufallszahlen in Excel 2019, 2016 und früher

Da keine andere Version als Excel 365 dynamische Arrays unterstützt, funktioniert keine der oben genannten Lösungen in früheren Versionen von Excel. Dies bedeutet jedoch nicht, dass es überhaupt keine Lösung gibt. Sie müssen nur noch ein paar Schritte ausführen:

Erstellen Sie eine Liste mit Zufallszahlen. Verwenden Sie je nach Ihren Anforderungen Folgendes:
- Das RAND Funktion zum Erzeugen zufälliger Dezimalstellen zwischen 0 und 1 oder
- Das RANDBETWEEN Funktion zum Erzeugen zufälliger Ganzzahlen in dem von Ihnen angegebenen Bereich.
Stellen Sie sicher, dass Sie mehr Werte generieren, als Sie tatsächlich benötigen, da einige davon Duplikate sind und Sie sie später löschen werden.

In diesem Beispiel erstellen wir eine Liste mit 10 zufälligen Ganzzahlen zwischen 1 und 20 mithilfe der folgenden Formel:

=RANDBETWEEN(1,20)

Um die Formel in mehreren Zellen gleichzeitig einzugeben, wählen Sie alle Zellen aus (in unserem Beispiel A2: A15), geben Sie die Formel in die Formelleiste ein und drücken Sie Strg + Eingabetaste. Oder Sie können die Formel wie gewohnt in die erste Zelle eingeben und sie dann auf beliebig viele Zellen nach unten ziehen.

Wie auch immer, das Ergebnis wird ungefähr so aussehen:

Wie Sie vielleicht bemerken, haben wir die Formel in 14 Zellen eingegeben, obwohl wir schließlich nur 10 eindeutige Zufallszahlen benötigen.
Ändern Sie Formeln in Werte. Da sowohl RAND als auch RANDBETWEEN bei jeder Änderung im Arbeitsblatt neu berechnet werden, ändert sich Ihre Liste der Zufallszahlen kontinuierlich. Um dies zu verhindern, verwenden Sie Inhalte einfügen> Werte Formeln in Werte konvertieren, wie in erläutert So verhindern Sie, dass Zufallszahlen neu berechnet werden.
Um sicherzustellen, dass Sie es richtig gemacht haben, wählen Sie eine beliebige Zahl aus und sehen Sie sich die Formelleiste an. Es sollte jetzt einen Wert anzeigen, keine Formel:
Duplikate löschen. Um dies zu tun, wählen Sie alle Zahlen aus und gehen Sie zu Daten Registerkarte> Datenwerkzeuge Gruppe, und klicken Sie auf Duplikate entfernen. In dem Duplikate entfernen Klicken Sie im angezeigten Dialogfeld einfach auf OK, ohne etwas zu ändern. Die detaillierten Schritte finden Sie unter So entfernen Sie Duplikate in Excel.

Getan! Alle Duplikate sind verschwunden, und Sie können jetzt die überschüssigen Nummern löschen.

Zufallszahlengenerator für Excel ohne Wiederholungen

Die Benutzer unserer Ultimative Suite brauchen keine der oben genannten Lösungen wirklich, da sie bereits einen universellen Zufallsgenerator in ihrem Excel haben. Dieses Tool kann auf einfache Weise eine Liste nicht wiederholter Ganzzahlen, Dezimalzahlen, Datumsangaben und eindeutiger Kennwörter erstellen. Hier ist wie:

Auf der Ablebits Werkzeuge Klicken Sie auf die Registerkarte Randomisieren > Zufallsgenerator.
Wählen Sie den Bereich aus, der mit Zufallszahlen gefüllt werden soll.
Auf der Zufallsgenerator Gehen Sie wie folgt vor:
- Wählen Sie den gewünschten Wertetyp: Ganzzahl, reelle Zahl, Datum, Boolescher Wert, benutzerdefinierte Liste oder Zeichenfolge (ideal zum Generieren sicherer eindeutiger Kennwörter!).
- Richten Sie die ein Von und Zu Werte.
- Wähle aus Einzigartige Werte Kontrollkästchen.
- Klicken Generieren.

Das ist es! Der ausgewählte Bereich wird sofort mit sich nicht wiederholenden Zufallszahlen gefüllt:
Zufallszahlengenerator für Excel ohne Wiederholungen

Wenn Sie neugierig sind, dieses Tool auszuprobieren und andere faszinierende Funktionen zu entdecken, die in unserem Tool enthalten sind Ultimative SuiteGern geschehen Laden Sie eine 14-Tage-Testversion herunter. Wenn Ihnen die Tools gefallen und Sie sich für eine Lizenz entscheiden, verpassen Sie nicht den 15% Rabatt-Gutscheincode, den wir exklusiv unseren Blog-Lesern zur Verfügung stellen: AB14-BlogSpo

So können Sie Zahlen in Excel ohne Duplikate randomisieren. Ich danke Ihnen für das Lesen und hoffe, Sie nächste Woche auf unserem Blog zu sehen!