Загальна формула
=INDEX(characters,RANDARRAY(n,1,1,count,TRUE))
Резюме
Щоб сформувати список випадкових текстових рядків, ви можете використовувати формулу на основі INDEX, RANDARRAY та TEXTJOIN. У наведеному прикладі формула в D5 має вигляд:
=TEXTJOIN("",1,INDEX(chars,RANDARRAY(6,1,1,26,TRUE)))
де символом є названий діапазон B5: B30, що містить літери AZ. Під час копіювання формули вниз по стовпці, вона генерує новий 6-символьний текстовий рядок у кожному рядку.
Пояснення
Нові формули динамічного масиву в Excel 365 значно полегшують вирішення певних складних задач за допомогою формул.
У цьому прикладі метою є створення списку випадкових 6-символьних кодів. Випадковість обробляється функцією RANDARRAY, новою функцією в Excel 365. RANDARRAY повертає 6 випадкових чисел до INDEX, а потім отримує 6 випадкових значень із названих символів діапазону. Потім результати з INDEX об'єднуються разом із функцією TEXTJOIN.
У наведеному прикладі формула в D5 має вигляд:
=TEXTJOIN("",1,INDEX(chars,RANDARRAY(6,1,1,26,TRUE)))
Працюючи зсередини, функція RANDARRAY використовується для створення масиву, що містить шість випадкових чисел між 1-26:
RANDARRAY(6,1,1,26,TRUE) // return array like (14;5;21;7;25;3)
Зверніть увагу, що повернутий масив буде змінюватися залежно від кожного екземпляра функції RANDARRAY. Крім того, оскільки RANDARRAY є нестабільною функцією, він буде перераховуватись при кожній зміні робочого аркуша.
Цей масив випадкових чисел повертається безпосередньо до функції INDEX як аргумент рядків:
INDEX(chars,(14;5;21;7;25;3))
Оскільки ми запитуємо INDEX для 6 рядків, ми отримуємо 6 результатів у такому масиві:
("N","E","U","G","Y","C")
Цей масив повертається до функції TEXTJOIN як аргумент text1:
=TEXTJOIN("",1,("N","E","U","G","Y","C")) // returns "NEUGYC"
TEXTJOIN налаштовано на використання порожнього рядка як роздільника та ігнорування порожніх значень. За допомогою цієї конфігурації TEXJOIN просто об'єднує всі значення разом і повертає 6-символьний текстовий рядок, наприклад "NEUGYC".
Підраховуйте символи програмно
Замість жорсткого кодування розміру символів безпосередньо у функції RANDARRAY, ви можете використовувати функцію COUNTA для підрахунку елементів у масиві та повернення цих значень до RANDARRAY:
RANDARRAY(6,1,1,COUNTA(chars),TRUE)
Це передбачає, що символи символів не містять порожніх комірок.
Генеруйте символи програмно
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))
У цій версії RANDBETWEEN повертає значення між 65 і 90 (включно), яке відповідає значенню ASCII для літер AZ (великі літери). Функція CHAR переводить числове значення на букву. Як і вище, усі результати об’єднуються в єдиний текстовий рядок.
