Generische Formel
=INDEX(characters,RANDARRAY(n,1,1,count,TRUE))
Zusammenfassung
Um eine Liste zufälliger Textzeichenfolgen zu erstellen, können Sie eine Formel verwenden, die auf INDEX, RANDARRAY und TEXTJOIN basiert. In dem gezeigten Beispiel lautet die Formel in D5:
=TEXTJOIN("",1,INDEX(chars,RANDARRAY(6,1,1,26,TRUE)))
Dabei ist Zeichen der benannte Bereich B5: B30 mit den Buchstaben AZ. Wenn die Formel in die Spalte kopiert wird, wird in jeder Zeile eine neue 6-stellige Textzeichenfolge generiert.
Erläuterung
Die neuen dynamischen Array-Formeln in Excel 365 erleichtern das Lösen bestimmter kniffliger Probleme mit Formeln erheblich.
In diesem Beispiel besteht das Ziel darin, eine Liste von zufälligen 6-stelligen Codes zu generieren. Die Zufälligkeit wird von der RANDARRAY-Funktion behandelt, einer neuen Funktion in Excel 365. RANDARRAY gibt 6 Zufallszahlen an INDEX zurück, die dann 6 Zufallswerte aus den genannten Bereichszeichen abrufen. Die Ergebnisse von INDEX werden dann zusammen mit der TEXTJOIN-Funktion verkettet.
In dem gezeigten Beispiel lautet die Formel in D5:
=TEXTJOIN("",1,INDEX(chars,RANDARRAY(6,1,1,26,TRUE)))
Mit der RANDARRAY-Funktion wird von innen nach außen ein Array mit sechs Zufallszahlen zwischen 1 und 26 generiert:
RANDARRAY(6,1,1,26,TRUE) // return array like (14;5;21;7;25;3)
Beachten Sie, dass das zurückgegebene Array mit jeder Instanz der RANDARRAY-Funktion variiert. Da RANDARRAY eine flüchtige Funktion ist, wird es bei jeder Änderung des Arbeitsblatts neu berechnet.
Dieses Array von Zufallszahlen wird als Zeilenargument direkt an die INDEX-Funktion zurückgegeben:
INDEX(chars,(14;5;21;7;25;3))
Da wir INDEX nach 6 Zeilen fragen, erhalten wir 6 Ergebnisse in einem Array wie diesem zurück:
("N","E","U","G","Y","C")
Dieses Array wird als text1-Argument an die TEXTJOIN-Funktion zurückgegeben:
=TEXTJOIN("",1,("N","E","U","G","Y","C")) // returns "NEUGYC"
TEXTJOIN ist so eingestellt, dass eine leere Zeichenfolge als Trennzeichen verwendet und leere Werte ignoriert werden. Mit dieser Konfiguration verkettet TEXJOIN einfach alle Werte miteinander und gibt eine 6-stellige Textzeichenfolge wie "NEUGYC" zurück.
Zeichen programmgesteuert zählen
Anstatt die Größe der Zeichen direkt in die RANDARRAY-Funktion fest zu codieren, können Sie die COUNTA-Funktion verwenden, um die Elemente im Array zu zählen und diese Anzahl an RANDARRAY zurückzugeben:
RANDARRAY(6,1,1,COUNTA(chars),TRUE)
Dies setzt voraus, dass Zeichen keine leeren Zellen enthalten.
Generieren Sie Zeichen programmgesteuert
Da den Buchstaben AZ numerische Codewerte zugrunde liegen, ist es möglich, das Array von Zeichen zu generieren, das zum programmgesteuerten Zusammenstellen von Textzeichenfolgen verwendet wird, anstatt einen Bereich zu verwenden. Dies kann mit der CHAR-Funktion und der SEQUENCE-Funktion erfolgen.
So generieren Sie ein Array mit allen Großbuchstaben AZ, die ASCII 65-90 zugeordnet sind:
=CHAR(SEQUENCE(26,1,65,1)) // returns ("A","B","C",… )
So generieren Sie Kleinbuchstaben az, die ASCII 97-122 entsprechen:
=CHAR(SEQUENCE(26,1,97,1)) // returns ("a","b","c",… )
Dieser Code kann in die ursprüngliche Formel eingefügt werden, um "Zeichen" wie folgt zu ersetzen:
=TEXTJOIN("",1,INDEX(CHAR(SEQUENCE(26,1,65,1)),RANDARRAY(6,1,1,26,TRUE)))
Andere Charaktere
Die Zeichen in den benannten Bereich Zeichen kann alles sein , die Sie mögen. Wenn Sie mehr als 26 Zeichen (oder weniger) hinzufügen, passen Sie die Nummer 26 entsprechend an oder verwenden Sie COUNTA wie oben erläutert.
Ohne Excel 365
It is possible to generate random text strings without Excel 365, but the formula is more tedious and redundant. Since we don't have a good way to get 6 random numbers all at once, we use the RANDBETWEEN function to get one random value at a time:
=INDEX(chars,RANDBETWEEN(1,26))& INDEX(chars,RANDBETWEEN(1,26))& INDEX(chars,RANDBETWEEN(1,26))& INDEX(chars,RANDBETWEEN(1,26))& INDEX(chars,RANDBETWEEN(1,26))& INDEX(chars,RANDBETWEEN(1,26))
This formula uses the INDEX function to retrieve one random value at a time from the named range chars, and the 6 results are concatenated together into a single text string. Line breaks added for readability.
It is also possible to generate values A-Z directly with CHAR and RANDBETWEEN like this:
=CHAR(RANDBETWEEN(65,90))& CHAR(RANDBETWEEN(65,90))& CHAR(RANDBETWEEN(65,90))& CHAR(RANDBETWEEN(65,90))& CHAR(RANDBETWEEN(65,90))& CHAR(RANDBETWEEN(65,90))
In dieser Version gibt RANDBETWEEN einen Wert zwischen 65 und 90 (einschließlich) zurück, der dem ASCII-Wert für die Buchstaben AZ (Großbuchstaben) entspricht. Die CHAR-Funktion übersetzt den numerischen Wert in einen Buchstaben. Wie oben werden alle Ergebnisse in einer einzigen Textzeichenfolge zusammengefasst.
