Общая формула
=INDEX(names,RANDARRAY(n,1,1,COUNTA(names),TRUE))
Резюме
Чтобы создать случайный список имен, вы можете использовать функцию ИНДЕКС и функцию СЛУЧАЙНЫЙ СЛУЧАЙ, чтобы выбрать случайные имена из существующего списка. В показанном примере формула в D5:
=INDEX(names,RANDARRAY(10,1,1,COUNTA(names),TRUE))
который возвращает 10 случайных значений из именованного диапазона «names» (B5: B104).
Объяснение
По сути, эта формула использует функцию ИНДЕКС для извлечения 10 случайных имен из именованного диапазона, называемого «имена», который содержит 100 имен. Например, чтобы получить пятое имя из списка, мы используем ИНДЕКС следующим образом:
=INDEX(names,5)
Однако уловка в этом случае заключается в том, что нам не нужно одно имя в известном месте, нам нужно 10 случайных имен в неизвестных местах от 1 до 100. Это отличный вариант использования функции RANDARRAY, которая может создать случайный набор целых чисел в заданном диапазоне. Работая изнутри, мы используем RANDARRAY, чтобы получить 10 случайных чисел от 1 до 100 следующим образом:
RANDARRAY(10,1,1,COUNTA(names)
Функция COUNTA используется для получения динамического подсчета имен в списке, но в этом случае мы могли бы заменить COUNTA на жестко запрограммированные 100 с тем же результатом:
=INDEX(names,RANDARRAY(10,1,1,100,TRUE))
В любом случае RANDARRAY вернет 10 чисел в массиве, который выглядит следующим образом:
(64;74;13;74;96;65;5;73;84;85)
Примечание: эти числа являются случайными и не соответствуют показанному примеру.
Этот массив возвращается непосредственно в функцию ИНДЕКС в качестве аргумента строки:
=INDEX(names, (64;74;13;74;96;65;5;73;84;85)
Поскольку мы даем ИНДЕКС 10 номеров строк, будет 10 результатов, каждый из которых соответствует имени в данной позиции. 10 случайных имен возвращаются в диапазоне разлива, начинающемся в ячейке D5.
Примечание. RANDARRAY - это непостоянная функция, которая будет пересчитываться каждый раз при изменении рабочего листа, в результате чего значения будут пересортированы. Чтобы предотвратить автоматическую сортировку значений, вы можете скопировать формулы, а затем использовать «Специальная вставка»> «Значения» для преобразования формул в статические значения.
Предотвратить дублирование
Одна из проблем с приведенной выше формулой (в зависимости от ваших потребностей) заключается в том, что RANDARRAY иногда генерирует повторяющиеся числа. Другими словами, нет никакой гарантии, что RANDARRAY вернет 10 уникальных чисел.
Чтобы обеспечить 10 различных имен из списка, вы можете адаптировать формулу для случайной сортировки полного списка имен, а затем извлекать первые 10 имен из списка. Формула в F5 использует такой подход:
=INDEX(SORTBY(names,RANDARRAY(COUNTA(names))),SEQUENCE(10))
Подход здесь тот же, что и выше - мы используем ИНДЕКС для извлечения 10 значений из списка имен. Однако в этой версии формулы мы случайным образом сортируем список имен перед тем, как передать список в INDEX следующим образом:
SORTBY(names,RANDARRAY(COUNTA(names)))
Здесь функция SORTBY используется для случайной сортировки списка имен со значениями массива, созданными функцией RANDARRAY, как более подробно объясняется здесь.
Наконец, нам нужно получить 10 значений. Поскольку у нас уже есть имена в случайном порядке, мы можем просто запросить первые 10 с помощью массива, созданного функцией SEQUENCE, например:
SEQUENCE(10)
SEQUENCE строит массив последовательных чисел:
(1;2;3;4;5;6;7;8;9;10)
который возвращается в функцию ИНДЕКС в качестве аргумента строки. Затем ИНДЕКС возвращает первые 10 имен в диапазоне разлива, как в исходной формуле.
