Общая формула
=INDEX(characters,RANDARRAY(n,1,1,count,TRUE))
Резюме
Чтобы сгенерировать список случайных текстовых строк, вы можете использовать формулу на основе INDEX, RANDARRAY и TEXTJOIN. В показанном примере формула в D5:
=TEXTJOIN("",1,INDEX(chars,RANDARRAY(6,1,1,26,TRUE)))
где chars - именованный диапазон 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(chars,(14;5;21;7;25;3))
Поскольку мы запрашиваем ИНДЕКС для 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)
Это предполагает, что символы не содержат пустых ячеек.
Создавать символы программно
Поскольку буквы AZ имеют базовые значения числового кода, можно сгенерировать массив символов, используемых для программной сборки текстовых строк, вместо использования диапазона. Это можно сделать с помощью функций CHAR и SEQUENCE.
Чтобы сгенерировать массив со всеми прописными буквами AZ, который соответствует ASCII 65-90:
=CHAR(SEQUENCE(26,1,65,1)) // returns ("A","B","C",… )
Чтобы сгенерировать строчные буквы az, которые соответствуют ASCII 97-122:
=CHAR(SEQUENCE(26,1,97,1)) // returns ("a","b","c",… )
Этот код можно вставить в исходную формулу, чтобы заменить символы следующим образом:
=TEXTJOIN("",1,INDEX(CHAR(SEQUENCE(26,1,65,1)),RANDARRAY(6,1,1,26,TRUE)))
Другие персонажи
Символы в именованный диапазон символов может быть все что угодно. Если вы добавляете более 26 символов (или меньше), отрегулируйте число 26 соответствующим образом или используйте COUNTA, как описано выше.
Без 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 переводит числовое значение в букву. Как и выше, все результаты объединяются в одну текстовую строку.
