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

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

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

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

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

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

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

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

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

Можно создать простую функцию на VBA, которая будет выдавать заданное количество случайных чисел из нужного интервала. Откроем редактор Visual Basic (Сервис - Макрос - Редактор 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:

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

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

к библиотеке 3GL к оглавлению к экономической информатике 4GL - визуальным средам

Знаете ли Вы, что только в 1990-х доплеровские измерения радиотелескопами показали скорость Маринова для CMB (космического микроволнового излучения), которую он открыл в 1974. Естественно, о Маринове никто не хотел вспоминать. Подробнее читайте в FAQ по эфирной физике.

НОВОСТИ ФОРУМА

Рыцари теории эфира