Как выбрать случайную выборку в Excel (шаг за шагом)
Часто вам может понадобиться выбрать случайную выборку из набора данных в Excel. К счастью, это легко сделать с помощью функции RAND() , которая генерирует случайное число от 0 до 1.
В этом руководстве представлен пошаговый пример того, как использовать эту функцию для выбора случайной выборки в Excel.
Шаг 1: Создайте набор данных
Сначала введите значения вашего набора данных в один столбец. В этом примере мы введем 20 различных значений в столбец A:
Шаг 2: Создайте список случайных значений
Затем введите = RAND() в ячейку B2. Это создает случайное значение от 0 до 1.
Затем наведите указатель мыши на правый нижний угол ячейки B2, пока не появится крошечный + , а затем дважды щелкните. Это скопирует формулу =RAND() во все оставшиеся ячейки в столбце B:
Шаг 3: Скопируйте и вставьте случайные значения
Затем выделите значения в столбце B и нажмите Ctrl + C. Это скопирует все значения. Затем щелкните правой кнопкой мыши ячейку C2 и выберите «Вставить значения ».
Обратите внимание, что после этого значения в столбце B могут измениться, но не беспокойтесь об этом.
Наконец, выделите значения в столбце C и перетащите их, чтобы заменить значения в столбце B.
Появится всплывающее окно с надписью «Здесь уже есть данные. Вы хотите заменить это?» Нажмите ОК .
Шаг 4: Сортировка по случайным значениям
Затем выделите ячейки A1: B21. Затем щелкните вкладку « Данные » на верхней ленте, затем нажмите « Сортировка » в разделе « Сортировка и фильтр ». Отсортируйте значения по случайному числу от меньшего к большему.
Значения будут отсортированы на основе случайного числа, от меньшего к большему:
Шаг 5: Выберите случайную выборку
Наконец, выберите первые n строк для вашей случайной выборки. Например, если вам нужна случайная выборка размером 5, выберите первые 5 необработанных значений данных, которые будут включены в вашу выборку.
В этом примере наша случайная выборка будет включать первые 5 значений: 8, 18, 26, 24, 24.
Случайная выборка из генеральной совокупности в EXCEL
Инструмент Пакета анализа MS EXCEL «Выборка» извлекает случайную выборку из входного диапазона, рассматривая его как генеральную совокупность. Также случайную выборку можно извлечь с помощью формул.
Пусть имеется некая совокупность данных, которая слишком велика для обработки или построения диаграммы. Для этих целей можно использовать репрезентативную выборку из данной совокупности.
Данную выборку можно получить с помощью инструмента «Выборка» надстройки Пакет анализа ( Analysis ToolPak ).
Надстройка доступна из вкладки Данные , группа Анализ .
СОВЕТ : Подробнее о других инструментах надстройки Пакет анализа и ее подключении – читайте в статье Надстройка Пакет анализа MS EXCEL .
Случайная выборка
Разместим исходную генеральную совокупность в диапазоне A7:A32 (см. файл примера лист Пример ).
Для наглядности все значения совокупности сделаны последовательными числами .
Вызовем надстройку Пакет анализа , выберем инструмент Выборка. Нажмем ОК.
В появившемся диалоговом окне в поле Входной интервал введите ссылку на A 7: A 32 .
Если диапазон включает и заголовок, то нужно установить галочку в поле Метки . В нашем случае устанавливать галочку не требуется, т.к. заголовок столбца не входит в диапазон A 7: A 32 .
Метод выборки установите Случайный , в поле Число выборок введите 6. Таким образом, из совокупности будет выбрано 6 чисел в случайном порядке (возможны повторы).
В поле Выходной интервал достаточно ввести ссылку на верхнюю ячейку диапазона с результатами (укажем ячейку С7 ). Нажмем ОК.
В результате работы надстройки, MS EXCEL разместил в столбце D 6 значений выбранных случайных образом из диапазона A 7: A 32 .
В принципе, количество случайных значений можно установить даже больше чем значений в исходной совокупности (безусловно, при этом будут повторы).
Аналогичную случайную выборку можно осуществить с помощью формулы =ИНДЕКС($A$7:$A$32;СЛУЧМЕЖДУ(1;СЧЁТ($A$7:$A$32)))
Функция СЛУЧМЕЖДУ() случайным образом выбирает позицию совокупности, из которой нужно взять 1 значение ( для этой функции вероятность выбрать любую строку одинакова ).
Выборка с определенной периодичностью
С помощью инструмента «Выборка» можно осуществить выборку с определенной заданной периодичностью. В этом случае пользователь должен сам убедиться, что данная выборка будет репрезентативной (как минимум, исходная совокупность не должна быть отсортирована ).
Установив в поле Период , например, значение 3, выведем каждое третье значение из Входного интервала . 
Этот же результат можно получить формулой (см. файл примера лист Пример ): =ИНДЕКС($A$7:$A$32;$H$6*(СТРОКА()-СТРОКА($G$6)))
В ячейке H6 содержится период выборки .
Если исходная совокупность имеет цикличность и упорядочена, то можно создать выборку , содержащую значения только из отдельной части цикла. Например, если входной диапазон содержит данные для квартальных продаж за несколько лет, то создание выборки с периодом равным 4 разместит в выходном интервале значения продаж только из заданного квартала.
Для выборки из всех кварталов кроме 4-го придется включить в исходную совокупность пустые ячейки. Например, для 3-го квартала нужно ввести в поле Входной интервал ссылку на A 6: A 32 , причем А6 должна быть пустой ячейкой (или содержать фиктивное число).
Гораздо проще для этого использовать формулу (см. файл примера лист Квартал ): =ИНДЕКС($C$9:$C$32;4*(СТРОКА()-СТРОКА($F$8))-4+$F$6) или =СУММЕСЛИМН($C$9:$C$32;$A$9:$A$32;E9;$B$9:$B$32;$F$6&» кв.»)
Примечание : Для наглядности строки, относящиеся к нужному кварталу, выделены Условным форматированием .
Выборка из нормального распределения
Пусть имеется генеральная совокупность из 5000 значений случайной величины имеющей стандартное нормальное распределение (см. файл примера лист Выборка из НОРМ ).
Примечание : Случайные значения сгенерированы с помощью формулы =НОРМ.СТ.ОБР(СЛЧИС())
Построим гистограммы распределения для генеральной совокупности и выборки размером 200.
Это удобнее сделать с помощью формулы =ИНДЕКС($A$7:$A$5006; СЛУЧМЕЖДУ(1;СЧЁТ($A$12:$A$5006)))
При каждый раз пересчете листа будет извлекаться новая выборка .
Т.к. значений в выборке значительно меньше, то и ее гистограмма визуально отличается от гистограммы генеральной совокупности .
Случайная выборка
В любом случае перед нами стоит задача отобрать случайным образом заданное количество элементов из какого-либо набора (например, вот такого):
Способ 1. Случайная сортировка
Добавить к нашему списку еще один столбец и вставить в него функцию генерации случайных чисел СЛЧИС (RAND) . Затем отсортировать наш список по добавленному столбцу (Данные — Сортировка) и взять N первых элементов из получившейся таблицы:
Минусы такого способа очевидны — придется вручную каждый раз пересортировывать список, если нам необходимо будет сделать другую случайную выборку. В плюсах — простота и доступность.
Способ 2. Функция НАИМЕНЬШИЙ
Этот способ заключается в использовании функции НАИМЕНЬШИЙ (SMALL) для выбора из списка N позиций с наименьшим случайным числом в столбце А:
После выбора пяти (в нашем примере) наименьших случайных чисел из столбца А, мы вытаскиваем имена, которые соответствуют этим числам с помощью функции ВПР (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:
Обратите внимание, что наша функция Lotto должна быть введена как формула массива, т.е. сначала необходимо выделить диапазон ячеек результатов (D2:D6) затем ввести нашу функцио Lotto и, после ввода аргументов функции, нажать Ctrl+Shift+Enter, чтобы ввести эту функцию именно как функцию массива во все выделенные ячейки.
Ну, а дальше останется при помощи уже знакомой функции ВПР (VLOOKUP) вытащить имена из списка, соответствующие случайным номерам.
Ссылки по теме
- Использование функции ВПР (VLOOKUP) для подстановки значений
- Создание макросов и пользовательских функций на VBA
- Функция RandomSelect из надстройки PLEX
Как сделать выборку в Excel с помощью формул массива
С помощью средств Excel можно осуществлять выборку определенных данных из диапазона в случайном порядке, по одному условию или нескольким. Для решения подобных задач используются, как правило, формулы массива или макросы. Рассмотрим на примерах.
Как сделать выборку в Excel по условию
При использовании формул массива отобранные данные показываются в отдельной таблице. В чем и состоит преимущество данного способа в сравнении с обычным фильтром.
Сначала научимся делать выборку по одному числовому критерию. Задача – выбрать из таблицы товары с ценой выше 200 рублей. Один из способов решения – применение фильтрации. В результате в исходной таблице останутся только те товары, которые удовлетворяют запросу.
Другой способ решения – использование формулы массива. Соответствующие запросу строки поместятся в отдельный отчет-таблицу.
Сначала создаем пустую таблицу рядом с исходной: дублируем заголовки, количество строк и столбцов. Новая таблица занимает диапазон Е1:G10.Теперь выделяем Е2:Е10 (столбец «Дата») и вводим следующую формулу: < >.
Чтобы получилась формула массива, нажимаем сочетание клавиш Ctrl + Shift + Enter. В соседний столбец – «Товар» — вводим аналогичную формулу массива: < >. Изменился только первый аргумент функции ИНДЕКС.
В столбец «Цена» введем такую же формулу массива, изменив первый аргумент функции ИНДЕКС.
В результате получаем отчет по товарам с ценой больше 200 рублей.
Такая выборка является динамичной: при изменении запроса или появлении в исходной таблице новых товаров, автоматически поменяется отчет.
Задача №2 – выбрать из исходной таблицы товары, которые поступили в продажу 20.09.2015. То есть критерий отбора – дата. Для удобства искомую дату введем в отдельную ячейку, I2.
Для решения задачи используется аналогичная формула массива. Только вместо критерия >.
Подобные формулы вводятся и в другие столбцы (принцип см. выше).
Теперь используем текстовый критерий. Вместо даты в ячейку I2 введем текст «Товар 1». Немного изменим формулу массива: < >.
Такая большая функция выборки в Excel.
Выборка по нескольким условиям в Excel
Сначала возьмем два числовых критерия:
Задача – отобрать товары, которые стоят меньше 400 и больше 200 рублей. Объединим условия знаком «*». Формула массива выглядит следующим образом: < =C2:C10);СТРОКА(C2:C10);"");СТРОКА(C2:C10)-СТРОКА($C$1))-СТРОКА($C$1))' >>.
Это для первого столбца таблицы-отчета. Для второго и третьего – меняем первый аргумент функции ИНДЕКС. Результат:
Чтобы сделать выборку по нескольким датам или числовым критериям, используем аналогичные формулы массива.
Случайная выборка в Excel
Когда пользователь работает с большим количеством данных, для последующего их анализа может потребоваться случайная выборка. Каждому ряду можно присвоить случайный номер, а затем применить сортировку для выборки.
Исходный набор данных:
Сначала вставим слева два пустых столбца. В ячейку А2 впишем формулу СЛЧИС (). Размножим ее на весь столбец:
Теперь копируем столбец со случайными числами и вставляем его в столбец В. Это нужно для того, чтобы эти числа не менялись при внесении новых данных в документ.
Чтобы вставились значения, а не формула, щелкаем правой кнопкой мыши по столбцу В и выбираем инструмент «Специальная вставка». В открывшемся окне ставим галочку напротив пункта «Значения»:
Теперь можно отсортировать данные в столбце В по возрастанию или убыванию. Порядок представления исходных значений тоже изменится. Выбираем любое количество строк сверху или снизу – получим случайную выборку.
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры