Generische Formel
=INDEX(data,MATCH(ROWS(exp_range),sort_values,0))
Zusammenfassung
Um vorhandene Werte mit einer Formel zufällig zu sortieren, können Sie eine INDEX- und eine MATCH-Formel zusammen mit Hilfsspalten verwenden, wie im Screenshot gezeigt. In dem gezeigten Beispiel lautet die Formel in E5:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
Dabei ist "Namen" der benannte Bereich B5: B11, "rand" der benannte Bereich C5: C11 und "sort" der benannte Bereich D5: D11.
Erläuterung
Diese Formel hängt von zwei Hilfsspalten ab. Die erste Hilfsspalte enthält zufällige Werte, die mit der Funktion RAND () erstellt wurden. Die kopierte Formel in C5 lautet:
=RAND()
Die RAND-Funktion generiert in jeder Zeile einen Zufallswert.
Hinweis: RAND ist eine flüchtige Funktion und generiert bei jeder Änderung des Arbeitsblatts neue Werte.
Die zweite Hilfsspalte enthält die Zahlen, die zum Sortieren von Daten verwendet werden und mit einer Formel generiert wurden. Die Formel in D5 lautet:
=RANK(C5,rand)+COUNTIF($C$5:C5,C5)-1
Auf dieser Seite finden Sie eine Erläuterung dieser Formel.
Die Formel in E5 lautet:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
Hier wird die INDEX-Funktion verwendet, um Werte im benannten Bereich "Namen" abzurufen, wobei die Sortierwerte im benannten Bereich "Sortieren" verwendet werden. Die eigentliche Arbeit, um herauszufinden, welcher Wert abgerufen werden soll, erfolgt über die MATCH-Funktion in diesem Snippet:
MATCH(ROWS($D$5:$D5),sort,0)
In MATCH erhält die ROWS-Funktion einen Erweiterungsbereich als Suchwert, der als eine Zelle beginnt und sich beim Kopieren der Formel in die Spalte erweitert. Dadurch wird der Suchwert von 1 bis 7 erhöht. MATCH gibt dann die Position des Suchwerts in der Liste zurück.
Die Position wird INDEX als Zeilennummer zugeführt, und INDEX ruft den Namen an dieser Position ab.
