Clarence Ricci: So erhalten Sie eine Zufallsstichprobe in Excel ohne Duplikate

Das Tutorial zeigt, wie Sie in Excel Stichproben ohne Wiederholungen durchführen. Sie finden Lösungen für alle Versionen von Excel 365, Excel 2019, Excel 2016, Excel 2013 und früher.

Vor einiger Zeit haben wir einige verschiedene Möglichkeiten beschrieben zufällig in Excel auswählen. Die meisten dieser Lösungen basieren auf den Funktionen RAND und RANDBETWEEN, die möglicherweise doppelte Zahlen erzeugen. Folglich kann Ihre Zufallsstichprobe sich wiederholende Werte enthalten. Wenn Sie eine zufällige Auswahl ohne Duplikate benötigen, verwenden Sie die in diesem Lernprogramm beschriebenen Ansätze.

Excel zufällige Auswahl aus der Liste ohne Duplikate

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

Verwenden Sie diese generische Formel, um eine zufällige Auswahl aus einer Liste ohne Wiederholungen zu treffen:

INDEX (SORTBY (Daten, RANDARRAY (REIHEN (Daten))), REIHENFOLGE(n))

Wo n ist die gewünschte Auswahlgröße.

Um beispielsweise 5 eindeutige Zufallsnamen aus der Liste in A2: A10 zu erhalten, verwenden Sie die folgende Formel:

=INDEX(SORTBY(A2:A10, RANDARRAY(ROWS(A2:A10))), SEQUENCE(5))

Der Einfachheit halber können Sie die Stichprobengröße in eine vordefinierte Zelle eingeben, z. B. C2, und die Zellreferenz für die SEQUENCE-Funktion angeben:

=INDEX(SORTBY(A2:A10, RANDARRAY(ROWS(A2:A10))), SEQUENCE(C2))

Excel zufällige Auswahl aus der Liste ohne Duplikate

Wie diese Formel funktioniert:

Hier ist eine allgemeine Erklärung der Logik der Formel: die RANDARRAY Funktion erstellt ein Array von Zufallszahlen, SORTIERE NACH sortiert die ursprünglichen Werte nach diesen Zahlen und INDEX Ruft so viele Werte ab, wie von SEQUENCE angegeben.

Eine detaillierte Aufschlüsselung folgt unten:

Die ROWS-Funktion zählt, wie viele Zeilen Ihr Datensatz enthält, und übergibt die Anzahl an die RANDARRAY-Funktion, sodass dieselbe Anzahl von zufälligen Dezimalstellen generiert werden kann:

RANDARRAY(ROWS(A2:C10))

Dieses Array von zufälligen Dezimalstellen wird von der SORTBY-Funktion als „Sortieren nach“ -Array verwendet. Infolgedessen werden Ihre Originaldaten zufällig gemischt.

Aus den zufällig sortierten Daten extrahieren Sie eine Stichprobe einer bestimmten Größe. Dazu geben Sie das gemischte Array an die INDEX-Funktion weiter und fordern an, das erste abzurufen N. Werte mit Hilfe der SEQUENCE-Funktion, die eine Folge von Zahlen von 1 bis erzeugt N.. Da die Originaldaten bereits in zufälliger Reihenfolge sortiert sind, ist es uns eigentlich egal, welche Positionen abgerufen werden sollen, nur die Menge spielt eine Rolle.

Wählen Sie zufällige Zeilen in Excel ohne Duplikate aus

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

Um zufällige Zeilen ohne Wiederholungen auszuwählen, erstellen Sie eine Formel auf folgende Weise:

INDEX (SORTBY (Daten, RANDARRAY (REIHEN (Daten))), REIHENFOLGE(n), {1,2,…})

Wo n ist die Stichprobengröße und {1,2,…} sind zu extrahierende Spaltennummern.

Wählen wir als Beispiel zufällige Zeilen aus A2: C10 ohne doppelte Einträge aus, basierend auf der Stichprobengröße in F1. Da sich unsere Daten in 3 Spalten befinden, geben wir diese Array-Konstante an die Formel an: {1,2,3}

=INDEX(SORTBY(A2:C10, RANDARRAY(ROWS(A2:C10))), SEQUENCE(F1), {1,2,3})

Und erhalten Sie folgendes Ergebnis:
Zufällige Zeilen in Excel ohne Duplikate auswählen

Wie diese Formel funktioniert:

Die Formel arbeitet mit genau der gleichen Logik wie die vorherige. Eine kleine Änderung, die einen großen Unterschied macht, ist, dass Sie beide angeben row_num und column_num Argumente für die INDEX-Funktion: row_num wird von SEQUENCE und geliefert column_num durch die Array-Konstante.

So führen Sie Stichproben in Excel 2010 – 2019 durch

Da nur Microsoft 365-Abonnements dynamische Arrays unterstützen, ist das neue dynamische Array-Funktionen Die in den vorherigen Beispielen verwendeten Funktionen funktionieren nur in Excel 365. Für andere Excel-Versionen müssen Sie eine andere Lösung ausarbeiten.

Angenommen, Sie möchten eine zufällige Auswahl aus der Liste in A2: A10. Dies kann mit 2 separaten Formeln erfolgen:

Generieren Sie Zufallszahlen mit der Rand-Formel. In unserem Fall geben wir es in B2 ein und kopieren es dann nach B10:
= RAND ()
Extrahieren Sie den ersten Zufallswert mit der folgenden Formel, die Sie in E2 eingeben:
= INDEX ($ A $ 2: $ A $ 10, RANK.EQ (B2, $ B $ 2: $ B $ 10) + COUNTIF ($ B $ 2: B2, B2) – 1)
Kopieren Sie die obige Formel in so viele Zellen, wie Sie auswählen möchten. In diesem Beispiel möchten wir 4 Namen, also kopieren wir die Formel von E2 bis E5.

Getan! Unsere Zufallsstichprobe ohne Duplikate sieht wie folgt aus:
Zufallsstichprobe in Excel 2010 - 2019 ohne Wiederholungen

Wie diese Formel funktioniert:

Wie im ersten Beispiel verwenden Sie die INDEX-Funktion, um Werte aus Spalte A basierend auf zufälligen Zeilennummern abzurufen. Der Unterschied besteht darin, wie Sie diese Zahlen erhalten:

Das RAND Funktion füllt den Bereich B2: B10 mit zufälligen Dezimalstellen.

Das RANK.EQ Funktion berechnet den Rang einer Zufallszahl in einer bestimmten Zeile. In E2 beispielsweise ordnet RANK.EQ (B2, $ B $ 2: $ B $ 10) die Zahl in B2 gegen alle Zahlen in B2: B10. Beim Kopieren nach E3 wird die relative Referenz B2 wechselt zu B3 und gibt den Rang der Zahl in B3 usw. zurück.

Das COUNTIF Die Funktion ermittelt, wie viele Vorkommen einer bestimmten Zahl in den obigen Zellen vorhanden sind. In E2 überprüft COUNTIF ($ B $ 2: B2, B2) beispielsweise nur eine Zelle – B2 selbst und gibt 1 zurück. In E5 ändert sich die Formel in COUNTIF ($ B $ 2: B5, B5) und gibt 2 zurück, weil B5 enthält den gleichen Wert wie B2 (bitte beachten Sie, dass dies nur zur besseren Erläuterung der Logik der Formel dient; bei einem kleinen Datensatz besteht die Wahrscheinlichkeit, dass doppelte Zufallszahlen erhalten werden, nahe Null).

Infolgedessen gibt COUNTIF für alle ersten Vorkommen 1 zurück, von dem Sie 1 subtrahieren, um die ursprüngliche Rangfolge beizubehalten. Bei zweiten Vorkommen gibt COUNTIF 2 zurück. Durch Subtrahieren von 1 erhöhen Sie die Rangfolge um 1 und verhindern so doppelte Ränge.

Für B2 gibt RANK.EQ beispielsweise 1 zurück. Da dies das erste Mal ist, gibt COUNTIF auch 1 zurück. RANK.EQ + COUNTIF ergibt 2. Und – 1 stellt den Rang 1 wieder her.

Nun sehen Sie, was im Fall der 2 passiert^nd Auftreten. Für B5 gibt RANK.EQ ebenfalls 1 zurück, während COUNTIF 2 zurückgibt. Wenn Sie diese addieren, erhalten Sie 3, von denen Sie 1 subtrahieren. Als Endergebnis erhalten Sie 2, was den Rang der Zahl in B5 darstellt.

Der Rang geht an die row_num Argument der INDEX-Funktion, und es wählt den Wert aus der entsprechenden Zeile (die column_num Das Argument wird weggelassen, daher wird standardmäßig 1) verwendet. Dies ist der Grund, warum es so wichtig ist, doppelte Ranglisten zu vermeiden. Ohne die COUNTIF-Funktion würde RANK.EQ sowohl für B2 als auch für B5 1 ergeben, wodurch INDEX den Wert aus der ersten Zeile (Andrew) zweimal zurückgibt.

So verhindern Sie, dass sich die Excel-Zufallsstichprobe ändert

Da alle Randomisierungsfunktionen in Excel wie RAND, RANDBETWEEN und RANDARRAY flüchtig sind, werden sie bei jeder Änderung im Arbeitsblatt neu berechnet. Infolgedessen ändert sich Ihre Zufallsstichprobe kontinuierlich. Um dies zu verhindern, verwenden Sie die Inhalte einfügen> Werte Funktion zum Ersetzen von Formeln durch statische Werte. Führen Sie dazu folgende Schritte aus:

Wählen Sie alle Zellen mit Ihrer Formel aus (jede Formel, die die Funktionen RAND, RANDBETWEEN oder RANDARRAY enthält) 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 drücken Sie Umschalt + F10 und dann V.Dies ist die Verknüpfung für die oben genannte Funktion.

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

Excel-Zufallsauswahl: Zeilen, Spalten oder Zellen

Funktioniert in allen Versionen von Excel 365, Excel 2019, Excel 2016, Excel 2013 und Excel 2010.

Wenn Sie unsere haben Ultimative Suite Wenn Sie es in Excel installiert haben, können Sie anstelle einer Formel eine zufällige Stichprobe mit einem Mausklick erstellen. Hier ist wie:

Auf der Ablebits Werkzeuge Klicken Sie auf die Registerkarte Randomisieren > Wählen Sie Zufällig.
Wählen Sie den Bereich aus, aus dem Sie eine Probe auswählen möchten.
Gehen Sie im Bereich des Add-Ins wie folgt vor:
- Wählen Sie aus, ob Sie zufällige Zeilen, Spalten oder Zellen auswählen möchten.
- Definieren Sie die Stichprobengröße: Dies kann ein Prozentsatz oder eine Zahl sein.
- Drücke den Wählen Taste.

Das ist es! Wie in der Abbildung unten gezeigt, wird eine Zufallsstichprobe direkt in Ihrem Datensatz ausgewählt. Wenn Sie es irgendwo kopieren möchten, drücken Sie einfach eine reguläre Kopierverknüpfung (Strg + C).

Wenn Sie daran interessiert sind, dies und mehr als 300 weitere fantastische Funktionen zu testen, die in unserem Programm 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, sollten Sie den exklusiven Rabatt von 15% für unsere Blog-Leser nicht verpassen! Ihr Gutscheincode: AB14-BlogSpo

So wählen Sie eine Zufallsstichprobe in Excel ohne Duplikate aus. Ich danke Ihnen für das Lesen und hoffe, Sie nächste Woche auf unserem Blog zu sehen!