Случайная выборка из генеральной совокупности в MS EXCEL

Инструмент Пакета анализа MS EXCEL «Выборка» извлекает случайную выборку из входного диапазона, рассматривая его как генеральную совокупность. Также случайную выборку можно извлечь с помощью формул.

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

Данную выборку можно получить с помощью инструмента «Выборка» надстройки Пакет анализа (Analysis ToolPak).

Надстройка доступна из вкладки Данные, группа Анализ.

СОВЕТ: Подробнее о других инструментах надстройки Пакет анализа и ее подключении – читайте в статье Надстройка Пакет анализа MS EXCEL.

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

Разместим исходную генеральную совокупность в диапазоне A7:A32 (см. файл примера лист Пример).

Для наглядности все значения совокупности сделаны последовательными числами.

Вызовем надстройку Пакет анализа, выберем инструмент Выборка.

Нажмем ОК.

В появившемся диалоговом окне в поле Входной интервал введите ссылку на A7:A32.

Если диапазон включает и заголовок, то нужно установить галочку в поле Метки. В нашем случае устанавливать галочку не требуется, т.к. заголовок столбца не входит в диапазон A7:A32.

Метод выборки установите Случайный, в поле Число выборок введите 6. Таким образом, из совокупности будет выбрано 6 чисел в случайном порядке (возможны повторы).

В поле Выходной интервал достаточно ввести ссылку на верхнюю ячейку диапазона с результатами (укажем ячейку С7). Нажмем ОК.

В результате работы надстройки, MS EXCEL разместил в столбце D 6 значений выбранных случайных образом из диапазона A7:A32.

В принципе, количество случайных значений можно установить даже больше чем значений в исходной совокупности (безусловно, при этом будут повторы).

Аналогичную случайную выборку можно осуществить с помощью формулы
=ИНДЕКС($A$7:$A$32;СЛУЧМЕЖДУ(1;СЧЁТ($A$7:$A$32)))

Функция СЛУЧМЕЖДУ() случайным образом выбирает позицию совокупности, из которой нужно взять 1 значение (для этой функции вероятность выбрать любую строку одинакова).

Выборка с определенной периодичностью

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

Установив в поле Период, например, значение 3, выведем каждое третье значение из Входного интервала.

Этот же результат можно получить формулой (см. файл примера лист Пример):
=ИНДЕКС($A$7:$A$32;$H$6*(СТРОКА()-СТРОКА($G$6)))

В ячейке H6 содержится период выборки.

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

Для выборки из всех кварталов кроме 4-го придется включить в исходную совокупность пустые ячейки. Например, для 3-го квартала нужно ввести в поле Входной интервал ссылку на A6:A32, причем А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)))

При каждый раз пересчете листа будет извлекаться новая выборка.

Т.к. значений в выборке значительно меньше, то и ее гистограмма визуально отличается от гистограммы генеральной совокупности.

 

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (1 оценка)
Яндекс.Метрика