Случайная выборка

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

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

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

random_select1.gif

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

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

random_select2.gif

 

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

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

Этот способ заключается в использовании функции НАИМЕНЬШИЙ (SMALL) для выбора из списка N позиций с наименьшим случайным числом в столбце А:

random_select3.gif

После выбора пяти (в нашем примере) наименьших случайных чисел из столбца А, мы вытаскиваем имена, которые соответствуют этим числам с помощью функции ВПР (VLOOKUP).

Способ 3. Случайная выборка без повторов - функция Lotto на VBA

Можно создать простую функцию на VBA, которая будет выдавать заданное количество случайных чисел из нужного интервала. Откроем редактор Visual Basic (ALT+F11 или в старых версиях Excel через меню Сервис - Макрос - Редактор Visual Basic), вставим новый модуль через меню Insert - Module и скопируем туда текст вот такой функции:

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
    
    Application.Volatile
    
    ReDim iArr(Bottom To Top)
    For i = Bottom To Top
        iArr(i) = i
    Next i
    
    For i = Top To Bottom + 1 Step -1
        r = Int(Rnd() * (i - Bottom + 1)) + Bottom
        temp = iArr(r)
        iArr(r) = iArr(i)
        iArr(i) = temp
    Next i
    j = 0
    For i = Bottom To Bottom + Amount - 1
        Out(j) = iArr(i)
        j = j + 1
    Next i
    
    Lotto = Application.Transpose(Out)
    
End Function

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

  • Bottom - нижняя граница интервала случайных чисел
  • Top - верхняя граница интервала случайных чисел
  • Amount - количество случайных чисел, которое мы хотим отобрать из интервала

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

Теперь эту функцию легко использовать для отбора случайных значений. Добавим к нашему списку столбец с нумерацией и будем отбирать людей по случайным номерам, которые генерирует функция Lotto:

random_select4.gif

 

Обратите внимание, что наша функция Lotto должна быть введена как формула массива, т.е. сначала необходимо выделить диапазон ячеек результатов (D2:D6) затем ввести нашу функцио Lotto и, после ввода аргументов функции, нажать Ctrl+Shift+Enter, чтобы ввести эту функцию именно как функцию массива во все выделенные ячейки.

Ну, а дальше останется при помощи уже знакомой функции ВПР (VLOOKUP) вытащить имена из списка, соответствующие случайным номерам.

Ссылки по теме

 



12.02.2013 20:53:21
В Excel 2010 можно воспользоваться встроенной функцией СЛУЧМЕЖДУ, один в один как 3 способ, только VBA мучить не надо :).
13.02.2013 16:09:51
Не один в один. СЛУЧМЕЖДУ может запросто выдать вам одинаковые случайные числа, т.е. может выбрать одного человека два раза в одной выборке. А в третьем способе фишка именно в неповторяемости участников выборки.
MCH
10.06.2014 22:49:34
Вариант третьего способа (немного короче чем у Николая):
По моему мнению Integer лучше заменить на Long, нет необходимости выделять лишнюю память для выходного массива, да и один цикл - лишний.

Function Lotto2(ByVal Bottom&, Top&, Amount&)
    Dim i&, j&
    Randomize
    Application.Volatile
    ReDim rndArr&(Bottom To Top), outArr&(1 To Amount, 1 To 1)
    For i = Bottom To Top
        rndArr(i) = i
    Next i
    For i = 1 To Amount
        j = Int(Rnd * (Top - Bottom + 1)) + Bottom
        outArr(i, 1) = rndArr(j)
        rndArr(j) = rndArr(Bottom)
        Bottom = Bottom + 1
    Next i
    Lotto2 = outArr
End Function
26.09.2015 16:46:53
Ну или так...
Function Lotto(Bottom As Integer, Top As Integer, Amount As Integer)
  Dim iNumber As Integer, i As Integer
  With New Dictionary
    For i = 1 To Amount
      Do
        iNumber = WorksheetFunction.RandBetween(Bottom, Top)
      Loop Until Not .Exists(iNumber)
      .Item(iNumber) = iNumber
    Next
    Lotto = Application.Transpose(.Items)
  End With
End Function
 
22.05.2015 12:49:03
Добрый день.

Господа, а возможно силами эксель сделать автоматическую подготовку расписания.
Например.
Елена занимается в понедельник скакалкой.
Потом она занимается во вторник бегом и т.д.
И все это эксель делает случайным образом
см. пример файла
https://cloud.mail.ru/public/K3yH/qWKPVAy5E
16.12.2018 12:26:06
Расписание обычно делается при соблюдении большого количества сопутствующих условий и ограничений (два дня подряд не должно быть бега и т.п.). Копайте в сторону встроенной в Excel надстройки Поиск решения (Solver).
День добрый!
Помогите пожалуйста, написать формулу или макрос по набору случайных слов для сеошников.
Например у меня есть 3-5 колонок и 100 строк с повторяющимися словами.
Вот нужно в крайней правой колонке сделать выборку из масива но что бы были как в шахматном порядке  
16.12.2018 12:24:22
Дмитрий, это больше похоже на проект - вам, скорее, на Форум в раздел Работа :)
30.11.2017 17:19:49
Работает только до 10000, что править чтобы отбирать значения от 1 до 500 тыс., хелп плз.?
16.12.2018 12:22:46
На таком объеме лучше использовать Способ 1 со случайной сортировкой. Все остальные методы будут тормозить конкретно.
Наверх