Случайный выбор из списка в excel

Обновлено: 04.07.2024

Нечастая, но интересная задача: выбрать из массива данных (списка) случайным образом N элементов. Причин для ее возникновения может быть несколько, например:

Объем данных слишком велик, поэтому мы удовлетворяемся анализом случайной выборки из полного набора данных.
Выбор победителей из числа участников какого-либо конкурса или лотереи.

В любом случае перед нами стоит задача отобрать случайным образом заданное количество элементов из какого-либо набора (например, вот такого):

Способ 1. Случайная сортировка

Добавить к нашему списку еще один столбец и вставить в него функцию генерации случайных чисел СЛЧИС (RAND) . Затем отсортировать наш список по добавленному столбцу (Данные - Сортировка) и взять N первых элементов из получившейся таблицы:

Минусы такого способа очевидны - придется вручную каждый раз пересортировывать список, если нам необходимо будет сделать другую случайную выборку. В плюсах - простота и доступность.

Способ 2. Функция НАИМЕНЬШИЙ

Этот способ заключается в использовании функции НАИМЕНЬШИЙ (SMALL) для выбора из списка 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) вытащить имена из списка, соответствующие случайным номерам.

Пусть в диапазоне A6:A12 содержится список текстовых значений.

Чтобы выбрать 2 случайных значения из списка (диапазон A6:A12) будем действовать следующим образом (см. Файл примера ):

сопоставим каждому текстовому значению случайное число (используйте функцию СЛЧИС() ), расположив его в столбце С(см. также статью Сортировка в случайном порядке списка ЧИСЛОвых значений в MS EXCEL);
формулы =ИНДЕКС($A$6:$A$12;РАНГ(C6;$C$6:$C$12)) в ячейке D6 и =ИНДЕКС($A$6:$A$12;РАНГ(C7;$C$6:$C$12)) в ячейке D7вернут 2 случайных текстовых значения из исходного списка.

Функция СЛЧИС() пересчитывает свое значение после каждого ввода нового значения в любую ячейку листа (или изменения значения ячейки) или нажатии клавиши F9.

Исходный список может содержать совершенно произвольные значения (текст, числа, даты), т.к. функция ИНДЕКС() выводит значение ячейки исходя лишь из его позиции в списке.

Выбор 1 случайного значения из списка

Формула =ИНДЕКС(A8:A14;СЛУЧМЕЖДУ(1;СЧЁТЗ(A8:A14))) сначала генерирует случайное целое число от 1 до 7 (номер позиции), где 7 количество значений в списке. Затем по номеру позиции (случайное число) выводится значение из исходного списка.

Случайная выборка из списка

Многим пользователям Excel приходится сталкиваться с такой задачей: выбрать из массива данных (списка) случайным образом N элементов. Это может быть связано с тем, что полный объем данных слишком велик, поэтому мы удовлетворяемся анализом случайной выборки из полного набора данных. Или же это может быть выбор, например, победителей из числа участников какого-либо конкурса или лотереи. В любом случае перед нами стоит задача отобрать случайным образом заданное количество элементов из какого-либо набора (например, вот такого):

Способ 1. Случайная сортировка

Способ 2. Функция НАИМЕНЬШИЙ

Способ 3. Функция Lotto на VBA

Function Lotto(Bottom As Integer, Top As Integer, Amount As Integer)
Dim iArr As Variant
Dim i As Integer
Dim r As Integer
Dim temp As Integer
Dim Out(1000) As Variant

ReDim iArr(Bottom To Top)
For i = Bottom To Top
iArr(i) = i
Next i

У этой функции будет три аргумента:

Т.е., например, чтобы отобрать 5 случайных чисел от 10 до 100, нужно будет ввести =Lotto(10;100;5)

[center][Youtube]CLegyQkMkyw[/Youtube][/center]
[center]10:22 [/center]

[center][b]Сон разума народа России [/center]

[center][Youtube]CLegyQkMkyw[/Youtube][/center]
[center]10:22 [/center]

[center][b]Сон разума народа России [/center]

Excel работает за вас

Excel works!

Thanks for Visiting

Случайный выбор из списка по параметрам. Как из вопросов сделать билеты в Excel?

Всегда было интересно, как преподаватели для экзаменов из вопросов делают билеты. Наверное руками разбирают в случайном порядке… Недавно пришлось решать такую же задачу — делюсь результатом. Итак есть список вопросов из него автоматически (случайным образом) формируются билеты в Excel по темам

Описание файла и пример как из вопросов сделать билеты в Excel

Лист Вопросы — содержит все вопросы к опросу, разбитые по темам (ст E), каждому присвоен уникальный код (тема-номер вопроса). Номер вопроса рассчитывается автоматически при помощи простой формулы с Если. Уникальный код вопроса рассчитывается в столбце B при помощи знака сцепить — &.

Лист Билет — содержит случайное распределение вопросов по темам в билете. В столбце В рассчитывает вопросов по темам — нам это потребуется для распределения. Случайный разброс производиться благодаря функции =СЛЧИС, она подбирает рандомное число от 0 до 1. Для того, чтобы получить случайный номер вопроса в рамках имеющихся вопросов, мы должны значение от 0 до 1 (=СЛЧИС) умножить на кол-во вопросов (ст В) и результат округлить до верхнего целого значения, т.к. номер вопроса не может быть равен 0 или дробному числу.

Теперь в столбце D мы формируем (опять же &, но можно и при помощи =СЦЕПИТЬ) уникальный номер вопроса для темы билета (должны совпадать по формату с номерами на листе Вопросы)

В итоге в столбце E возвращаем вопросы из списка с помощью функции =ВПР

Не забываем, что вопросы меняются случайным образом, только если вы нажмете кнопку F9 или выполните какое-то действие на странице.

Note: The other languages of the website are Google-translated. Back to English

Как сгенерировать случайное значение из заданного списка в Excel?

Сгенерировать случайное число легко с помощью функции = RANDBETWEEN (range), но пробовали ли вы когда-нибудь сгенерировать случайное значение из заданного списка, как показано ниже?

Сгенерировать случайное значение из заданного списка по формуле

На самом деле, есть несколько простых формул, которые помогут быстро сгенерировать случайное значение из заданного списка.

Выберите пустую ячейку, в которую будет помещено случайное значение, введите эту формулу = ИНДЕКС ($ A $ 2: $ A $ 16, RANDBETWEEN (2; 16)) , Затем нажмите Enter ключ. Смотрите скриншот:

В формуле A2: A16 - это заданный список, который вы хотите использовать, 2 и 16 - это номер начальной и конечной строки списка.

Если вы хотите сгенерировать несколько случайных значений из списка, вы можете перетащить формулу вниз по мере необходимости. Смотрите скриншот:

Вы можете нажать F9 ключ для обновления значений.

Внимание: Если вы хотите сгенерировать случайное число на основе списка, вы можете использовать эту формулу = ИНДЕКС ($ I $ 2: $ I $ 7, СЛУЧАЙНО (1; 6)) , и нажмите Enter ключ.

Чтобы упростить работу пользователей с листом, добавьте в ячейки раскрывающиеся списки. Раскрывающиеся списки позволяют пользователям выбирать элементы из созданного вами списка.

На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel. Если это не так, список можно быстро преобразовать в таблицу, выделив любую ячейку диапазона и нажав клавиши CTRL+T.

Почему данные следует поместить в таблицу? Потому что в этом случае при добавлении и удалении элементов все раскрывающиеся списки, созданные на основе этой таблицы, будут обновляться автоматически. Дополнительные действия не требуются.

Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.

На ленте откройте вкладку Данные и нажмите кнопку Проверка данных.

Примечание: Если кнопка Проверка данных недоступна, возможно, лист защищен или является общим. Разблокируйте определенные области защищенной книги или отмените общий доступ к листу, а затем повторите шаг 3.

На вкладке Параметры в поле Тип данных выберите пункт Список.

Щелкните поле Источник и выделите диапазон списка. В примере данные находятся на листе "Города" в диапазоне A2:A9. Обратите внимание на то, что строка заголовков отсутствует в диапазоне, так как она не является одним из вариантов, доступных для выбора.

Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки.

Установите флажок Список допустимых значений

Не знаете, какой параметр выбрать в поле Вид?

Чтобы заблокировать пользователям ввод данных, которые не содержатся в раскрывающемся списке, выберите вариант Остановка.

Если список элементов для раскрывающегося списка находится на другом листе и вы хотите запретить пользователям его просмотр и изменение, скройте и защитите этот лист. Подробнее о защите листов см. в статье Блокировка ячеек.

Если вы решили изменить элементы раскрывающегося списка, см. статью Добавление и удаление элементов раскрывающегося списка.

Чтобы удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.

Ввод данных станет быстрее и точнее, если ограничить значения в ячейке вариантами из раскрывающегося списка.

Сначала создайте на листе список допустимых элементов, а затем выполните сортировку или расположите их в нужном порядке. В дальнейшем эти элементы могут служить источником для раскрывающегося списка данных. Если список небольшой, на него можно легко ссылаться и вводить элементы прямо в средстве проверки данных.

Создайте список допустимых элементов для раскрывающегося списка. Для этого введите элементы на листе в одном столбце или строке без пустых ячеек.

Выделите ячейки, для которых нужно ограничить ввод данных.

На вкладке Данные в группе Инструменты нажмите кнопку Проверка данных или Проверить.

Примечание: Если команда проверки недоступна, возможно, лист защищен или книга является общей. Если книга является общей или лист защищен, изменить параметры проверки данных невозможно. Дополнительные сведения о защите книги см. в статье Защита книги.

Откройте вкладку Параметры и во всплывающем меню Разрешить выберите пункт Список.

Щелкните поле Источник и выделите на листе список допустимых элементов.

Диалоговое окно свернется, чтобы было видно весь лист.

Нажмите клавишу ВВОД или кнопку Развернуть , чтобы развернуть диалоговое окно, а затем нажмите кнопку ОК.

Значения также можно ввести непосредственно в поле Источник через запятую.

Чтобы изменить список допустимых элементов, просто измените значения в списке-источнике или диапазон в поле Источник.

См. также

На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel.

Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.

На ленте откройте вкладку Данные и нажмите кнопку Проверка данных.

На вкладке Параметры в поле Разрешить выберите пункт Список.

Если вы уже создали таблицу с элементами раскрывающегося списка, щелкните поле Источник и выделите ячейки, содержащие эти элементы. Однако не включайте в него ячейку заголовка. Добавьте только ячейки, которые должны отображаться в раскрывающемся списке. Список элементов также можно ввести непосредственно в поле Источник через запятую. Например:

Фрукты;Овощи;Зерновые культуры;Молочные продукты;Перекусы

Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки.

Установите флажок Список допустимых значений

После создания раскрывающегося списка убедитесь, что он работает правильно. Например, рекомендуется проверить, изменяется ли ширина столбцов и высота строк при отображении всех ваших записей. Если вы решили изменить элементы раскрывающегося списка, см. статью Добавление и удаление элементов раскрывающегося списка. Чтобы удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Читайте также: