Выбрать случайный вариант
Нечастая, но интересная задача: выбрать из массива данных (списка) случайным образом N элементов. Причин для ее возникновения может быть несколько, например:
- Объем данных слишком велик, поэтому мы удовлетворяемся анализом случайной выборки из полного набора данных.
- Выбор победителей из числа участников какого-либо конкурса или лотереи.
В любом случае перед нами стоит задача отобрать случайным образом заданное количество элементов из какого-либо набора (например, вот такого):
Способ 1. Случайная сортировка
Добавить к нашему списку еще один столбец и вставить в него функцию генерации случайных чисел СЛЧИС . Затем отсортировать наш список по добавленному столбцу (Данные — Сортировка) и взять N первых элементов из получившейся таблицы:
Минусы такого способа очевидны — придется вручную каждый раз пересортировывать список, если нам необходимо будет сделать другую случайную выборку. В плюсах — простота и доступность.
Способ 2. Функция НАИМЕНЬШИЙ
Этот способ заключается в использовании функции НАИМЕНЬШИЙ для выбора из списка N позиций с наименьшим случайным числом в столбце А:
После выбора пяти (в нашем примере) наименьших случайных чисел из столбца А, мы вытаскиваем имена, которые соответствуют этим числам с помощью функции ВПР (VLOOKUP).
Способ 3. Случайная выборка без повторов — функция Lotto на VBA
Можно создать простую функцию на VBA, которая будет выдавать заданное количество случайных чисел из нужного интервала. Откроем редактор Visual Basic (ALT+F11 или в старых версиях Excel через меню Сервис — Макрос — Редактор Visual Basic), вставим новый модуль через меню Insert — Module и скопируем туда текст вот такой функции:
У этой функции будет три аргумента:
- Bottom — нижняя граница интервала случайных чисел
- Top — верхняя граница интервала случайных чисел
- Amount — количество случайных чисел, которое мы хотим отобрать из интервала
Т.е., например, чтобы отобрать 5 случайных чисел от 10 до 100, нужно будет ввести =Lotto(10;100;5)
Теперь эту функцию легко использовать для отбора случайных значений. Добавим к нашему списку столбец с нумерацией и будем отбирать людей по случайным номерам, которые генерирует функция Lotto:
Обратите внимание, что наша функция Lotto должна быть введена как формула массива, т.е. сначала необходимо выделить диапазон ячеек результатов (D2:D6) затем ввести нашу функцио Lotto и, после ввода аргументов функции, нажать Ctrl+Shift+Enter, чтобы ввести эту функцию именно как функцию массива во все выделенные ячейки.
Ну, а дальше останется при помощи уже знакомой функции ВПР вытащить имена из списка, соответствующие случайным номерам.
Ссылки по теме
- Использование функции ВПР (VLOOKUP) для подстановки значений
- Создание макросов и пользовательских функций на VBA
- Функция RandomSelect из надстройки PLEX
