Formuła ogólna
=INDEX(characters,RANDARRAY(n,1,1,count,TRUE))
Podsumowanie
Aby wygenerować listę losowych ciągów tekstowych, możesz użyć formuły opartej na INDEX, RANDARRAY i TEXTJOIN. W przedstawionym przykładzie formuła w D5 to:
=TEXTJOIN("",1,INDEX(chars,RANDARRAY(6,1,1,26,TRUE)))
gdzie znaki to nazwany zakres B5: B30 zawierający litery AZ. Ponieważ formuła jest kopiowana w dół kolumny, generuje nowy 6-znakowy ciąg tekstowy w każdym wierszu.
Wyjaśnienie
Nowe dynamiczne formuły tablicowe w programie Excel 365 znacznie ułatwiają rozwiązywanie pewnych trudnych problemów z formułami.
W tym przykładzie celem jest wygenerowanie listy losowych 6-znakowych kodów. Losowość jest obsługiwana przez funkcję RANDARRAY, nową funkcję w programie Excel 365. RANDARRAY zwraca 6 liczb losowych do INDEX, który następnie pobiera 6 losowych wartości z nazwanych znaków zakresu. Wyniki z INDEKSU są następnie łączone razem z funkcją TEXTJOIN.
W przedstawionym przykładzie formuła w D5 to:
=TEXTJOIN("",1,INDEX(chars,RANDARRAY(6,1,1,26,TRUE)))
Działając od wewnątrz, funkcja RANDARRAY służy do generowania tablicy zawierającej sześć losowych liczb z przedziału 1-26:
RANDARRAY(6,1,1,26,TRUE) // return array like (14;5;21;7;25;3)
Zwróć uwagę, że zwrócona tablica będzie się różnić w przypadku każdego wystąpienia funkcji RANDARRAY. Ponadto, ponieważ RANDARRAY jest funkcją nietrwałą, będzie obliczana ponownie przy każdej zmianie arkusza.
Ta tablica liczb losowych jest zwracana bezpośrednio do funkcji INDEKS jako argument wierszy:
INDEX(chars,(14;5;21;7;25;3))
Ponieważ prosimy INDEKS o 6 wierszy, otrzymujemy 6 wyników w tablicy takiej jak ta:
("N","E","U","G","Y","C")
Ta tablica jest zwracana do funkcji TEXTJOIN jako argument tekst1:
=TEXTJOIN("",1,("N","E","U","G","Y","C")) // returns "NEUGYC"
TEXTJOIN jest ustawione tak, aby używać pustego ciągu jako separatora i ignorować puste wartości. W tej konfiguracji TEXJOIN po prostu łączy wszystkie wartości razem i zwraca 6-znakowy ciąg tekstowy, np. „NEUGYC”.
Policz znaki programowo
Zamiast zakodować rozmiar znaków bezpośrednio w funkcji RANDARRAY, możesz użyć funkcji COUNTA, aby policzyć elementy w tablicy i zwrócić tę liczbę do RANDARRAY:
RANDARRAY(6,1,1,COUNTA(chars),TRUE)
Zakłada się, że znaki nie zawierają żadnych pustych komórek.
Generuj znaki programowo
Since the letters A-Z have underlying numeric code values, it is possible to generate the array of characters used to assemble text strings programmatically, instead of using a range. This can be done with the CHAR function and the SEQUENCE function.
To generate an array with all uppercase letters A-Z, which map to ASCII 65-90:
=CHAR(SEQUENCE(26,1,65,1)) // returns ("A","B","C",… )
To generate lowercase letters a-z, which correspond to ASCII 97-122:
=CHAR(SEQUENCE(26,1,97,1)) // returns ("a","b","c",… )
This code can be dropped into the original formula to replace "chars" like this:
=TEXTJOIN("",1,INDEX(CHAR(SEQUENCE(26,1,65,1)),RANDARRAY(6,1,1,26,TRUE)))
Other characters
The characters in the named range chars can be anything you like. If you add more than 26 characters (or fewer) adjust the number 26 as appropriate, or use COUNTA as explained above.
Without 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))
W tej wersji RANDBETWEEN zwraca wartość między 65 a 90 (włącznie), która odpowiada wartości ASCII dla liter AZ (wielkie litery). Funkcja CHAR przekształca wartość liczbową na literę. Jak wyżej, wszystkie wyniki są łączone w jeden ciąg tekstowy.
