Как сгенерировать выборку в excel - Word и 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)))

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

выборка

.

Т.к. значений в

выборке

значительно меньше, то и ее

гистограмма

визуально отличается от

гистограммы

генеральной совокупности

.

Источник

Загрузить PDF

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

1

Запустите программу Microsoft Excel. Также можно открыть существующую таблицу Excel, если в ней содержится нужная совокупность данных.
2

Выберите Новый документ. Если вы открываете существующую таблицу, пропустите этот шаг.
3
Введите данные. Для этого щелкайте по нужным ячейкам и вводите данные.
- Этот процесс зависит от типа данных, но рекомендуем начать вводить данные в столбце «A».
- Например, введите имена пользователей в столбце «А», а их ответы (например, «да» или «нет») в столбце «В».
4

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

Реклама

1

Щелкните правой кнопкой мыши по букве самого левого столбца. Например, если данные начинаются в столбце «A», щелкните правой кнопкой мыши по «A» в верхней части страницы.
2
Нажмите Вставить. Слева от выделенного столбца появится пустой (новый) столбец.
- Теперь столбец, который был столбцом «А», станет столбцом «B» и так далее.
3

Щелкните по ячейке A1. Она находится в новом (пустом) столбце.
4
Введите «=СЛЧИС()» в выбранной ячейке (кавычки не вводите). Функция «СЛЧИС» добавит в ячейку случайное число от 0 до 1. ^[1]
- Если Excel автоматически изменит формат функции «СЛЧИС», удалите ее, а затем повторно введите.
5

Нажмите ↵ Enter. В выбранной ячейке отобразится случайная десятичная дробь (например, 0,5647).
6

Выберите ячейку со случайным числом.
7
Удерживайте Control и нажмите C. Функция «СЛЧИС» будет скопирована в буфер обмена.
- На компьютере под управлением Mac удерживайте ⌘ Command вместо Control.
- Также можно щелкнуть правой кнопкой мыши по ячейке с функцией «СЛЧИС», а затем в меню выбрать Копировать.
8
Выберите ячейку под ячейкой со случайным числом. Вероятно, это будет ячейка A2.
- Щелчок по ячейке A1 и ее выделение могут привести к ошибке сортировки.
9
Выделите остальные ячейки случайной выборки. Для этого удерживаете ⇧ Shift и нажмите на последнюю ячейку того же столбца.
- Например, если данные расположены в столбцах «B» и «C» и в строках с 1 по 100, удерживайте ⇧ Shift и щелкните по A100, чтобы выделить ячейки с A2 до A100 столбца «А».
10
Удерживайте Control и коснитесь V. В выделенные ячейки (в нашем примере от A2 до A100) будет вставлена функция «СЛЧИС». Теперь отсортируйте данные, используя случайные числа, чтобы изменить порядок результатов.
- На компьютере Mac удерживайте ⌘ Command вместо Control.
Реклама

1
Выберите верхнюю левую ячейку. В большинстве случаев это будет ячейка A1. Чтобы отсортировать выборку, необходимо выделить все данные.
- Также нужно выделить случайные числа, которые находятся в столбце слева от столбцов с данными.
2
Удерживайте ⇧ Shift и щелкните по нижней правой ячейке. Будут выбраны все данные.
- Например, если данные расположены в столбцах «B» и «C» и в строках с 1 по 50, щелкните по ячейке C50, удерживая нажатой ⇧ Shift.
- Также можно удерживать клавишу мыши и перетащить курсор из левого верхнего угла в правый нижний угол данных (или наоборот), чтобы выделить их.
3
Щелкните правой кнопкой мыши по данным. Откроется контекстное меню с опциями, с помощью которых данные можно отсортировать.
- На компьютере Mac щелкните двумя пальцами (или удерживайте Ctrl и нажмите кнопку мыши), чтобы открыть контекстное меню.
4

Наведите курсор на Сортировка.
5

Нажмите Сортировка от минимального к максимальному. Также можно выбрать опцию Сортировка от максимального к минимальному — важно только то, что данные будут упорядочены случайным образом в соответствии со значениями функции «=СЛЧИС()» в столбце «A».
6
Просмотрите результаты сортировки. Этот процесс зависит от того, какое количество результатов вам нужно. Тем не менее, с отсортированными данными можно сделать следующее:
- Выберите первую, последнюю или центральную половину данных. Если половина данных включает чрезмерное количество значений, выберите меньшую долю данных (например, одну восьмую данных).
- Выберите все нечетные или четные данные. Например, если данные состоят из последовательного набора чисел от 1 до 10, выберите числа 1, 3, 5, 7, 9 или числа 2, 4, 6, 8, 10.
- Выберите несколько случайных данных. Этот метод подойдет для больших наборов данных, когда даже их половина включает чрезмерное количество значений.
7

Выберите данные, которые войдут в случайную выборку. Так вы получите объективную выборку для анализа опроса, раздачи продукта или чего-то подобного.

Реклама

Советы

Если на компьютере нет Microsoft Excel, в интернете есть другие аналогичные бесплатные программы (например, Google Таблицы или Outlook Excel), в которых можно создать случайную выборку.
Существуют мобильные версии Excel для iPhone и Android-устройств, поэтому создавать электронные таблицы можно «на ходу».

Предупреждения

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

Об этой статье

Эту страницу просматривали 23 353 раза.

Была ли эта статья полезной?

Источник

Содержание

Выполнение выборки
- Способ 1: применение расширенного автофильтра
- Способ 2: применение формулы массива
- Способ 3: выборка по нескольким условиям с помощью формулы
- Способ 4: случайная выборка
Вопросы и ответы

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

Выполнение выборки

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

Способ 1: применение расширенного автофильтра

Наиболее простым способом произвести отбор является применение расширенного автофильтра. Рассмотрим, как это сделать на конкретном примере.

Выделяем область на листе, среди данных которой нужно произвести выборку. Во вкладке «Главная» щелкаем по кнопке «Сортировка и фильтр». Она размещается в блоке настроек «Редактирование». В открывшемся после этого списка выполняем щелчок по кнопке «Фильтр».

Есть возможность поступить и по-другому. Для этого после выделения области на листе перемещаемся во вкладку «Данные». Щелкаем по кнопке «Фильтр», которая размещена на ленте в группе «Сортировка и фильтр».

После этого действия в шапке таблицы появляются пиктограммы для запуска фильтрования в виде перевернутых острием вниз небольших треугольников на правом краю ячеек. Кликаем по данному значку в заглавии того столбца, по которому желаем произвести выборку. В запустившемся меню переходим по пункту «Текстовые фильтры». Далее выбираем позицию «Настраиваемый фильтр…».

Активируется окно пользовательской фильтрации. В нем можно задать ограничение, по которому будет производиться отбор. В выпадающем списке для столбца содержащего ячейки числового формата, который мы используем для примера, можно выбрать одно из пяти видов условий:
- равно;
- не равно;
- больше;
- больше или равно;
- меньше.
Давайте в качестве примера зададим условие так, чтобы отобрать только значения, по которым сумма выручки превышает 10000 рублей. Устанавливаем переключатель в позицию «Больше». В правое поле вписываем значение «10000». Чтобы произвести выполнение действия, щелкаем по кнопке «OK».

Как видим, после фильтрации остались только строчки, в которых сумма выручки превышает 10000 рублей.

Но в этом же столбце мы можем добавить и второе условие. Для этого опять возвращаемся в окно пользовательской фильтрации. Как видим, в его нижней части есть ещё один переключатель условия и соответствующее ему поле для ввода. Давайте установим теперь верхнюю границу отбора в 15000 рублей. Для этого выставляем переключатель в позицию «Меньше», а в поле справа вписываем значение «15000».
Кроме того, существует ещё переключатель условий. У него два положения «И» и «ИЛИ». По умолчанию он установлен в первом положении. Это означает, что в выборке останутся только строчки, которые удовлетворяют обоим ограничениям. Если он будет выставлен в положение «ИЛИ», то тогда останутся значения, которые подходят под любое из двух условий. В нашем случае нужно выставить переключатель в положение «И», то есть, оставить данную настройку по умолчанию. После того, как все значения введены, щелкаем по кнопке «OK».

Теперь в таблице остались только строчки, в которых сумма выручки не меньше 10000 рублей, но не превышает 15000 рублей.

Аналогично можно настраивать фильтры и в других столбцах. При этом имеется возможность сохранять также фильтрацию и по предыдущим условиям, которые были заданы в колонках. Итак, посмотрим, как производится отбор с помощью фильтра для ячеек в формате даты. Кликаем по значку фильтрации в соответствующем столбце. Последовательно кликаем по пунктам списка «Фильтр по дате» и «Настраиваемый фильтр».

Снова запускается окно пользовательского автофильтра. Выполним отбор результатов в таблице с 4 по 6 мая 2016 года включительно. В переключателе выбора условий, как видим, ещё больше вариантов, чем для числового формата. Выбираем позицию «После или равно». В поле справа устанавливаем значение «04.05.2016». В нижнем блоке устанавливаем переключатель в позицию «До или равно». В правом поле вписываем значение «06.05.2016». Переключатель совместимости условий оставляем в положении по умолчанию – «И». Для того, чтобы применить фильтрацию в действии, жмем на кнопку «OK».

Как видим, наш список ещё больше сократился. Теперь в нем оставлены только строчки, в которых сумма выручки варьируется от 10000 до 15000 рублей за период с 04.05 по 06.05.2016 включительно.

Мы можем сбросить фильтрацию в одном из столбцов. Сделаем это для значений выручки. Кликаем по значку автофильтра в соответствующем столбце. В выпадающем списке щелкаем по пункту «Удалить фильтр».

Как видим, после этих действий, выборка по сумме выручки будет отключена, а останется только отбор по датам (с 04.05.2016 по 06.05.2016).

В данной таблице имеется ещё одна колонка – «Наименование». В ней содержатся данные в текстовом формате. Посмотрим, как сформировать выборку с помощью фильтрации по этим значениям.
Кликаем по значку фильтра в наименовании столбца. Последовательно переходим по наименованиям списка «Текстовые фильтры» и «Настраиваемый фильтр…».

Опять открывается окно пользовательского автофильтра. Давайте сделаем выборку по наименованиям «Картофель» и «Мясо». В первом блоке переключатель условий устанавливаем в позицию «Равно». В поле справа от него вписываем слово «Картофель». Переключатель нижнего блока так же ставим в позицию «Равно». В поле напротив него делаем запись – «Мясо». И вот далее мы выполняем то, чего ранее не делали: устанавливаем переключатель совместимости условий в позицию «ИЛИ». Теперь строчка, содержащая любое из указанных условий, будет выводиться на экран. Щелкаем по кнопке «OK».

Как видим, в новой выборке существуют ограничения по дате (с 04.05.2016 по 06.05.2016) и по наименованию (картофель и мясо). По сумме выручки ограничений нет.

Полностью удалить фильтр можно теми же способами, которые использовались для его установки. Причем неважно, какой именно способ применялся. Для сброса фильтрации, находясь во вкладке «Данные» щелкаем по кнопке «Фильтр», которая размещена в группе «Сортировка и фильтр».

Второй вариант предполагает переход во вкладку «Главная». Там выполняем щелчок на ленте по кнопке «Сортировка и фильтр» в блоке «Редактирование». В активировавшемся списке нажимаем на кнопку «Фильтр».

При использовании любого из двух вышеуказанных методов фильтрация будет удалена, а результаты выборки – очищены. То есть, в таблице будет показан весь массив данных, которыми она располагает.

Урок: Функция автофильтр в Excel

Способ 2: применение формулы массива

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

На том же листе создаем пустую таблицу с такими же наименованиями столбцов в шапке, что и у исходника.

Выделяем все пустые ячейки первой колонки новой таблицы. Устанавливаем курсор в строку формул. Как раз сюда будет заноситься формула, производящая выборку по указанным критериям. Отберем строчки, сумма выручки в которых превышает 15000 рублей. В нашем конкретном примере, вводимая формула будет выглядеть следующим образом:
=ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

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

Так как это формула массива, то для того, чтобы применить её в действии, нужно нажимать не кнопку Enter, а сочетание клавиш Ctrl+Shift+Enter. Делаем это.

Выделив второй столбец с датами и установив курсор в строку формул, вводим следующее выражение:
=ИНДЕКС(B2:B29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

Жмем сочетание клавиш Ctrl+Shift+Enter.

Аналогичным образом в столбец с выручкой вписываем формулу следующего содержания:
=ИНДЕКС(C2:C29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

Опять набираем сочетание клавиш Ctrl+Shift+Enter.

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

Как видим, таблица заполнена данными, но внешний вид её не совсем привлекателен, к тому же, значения даты заполнены в ней некорректно. Нужно исправить эти недостатки. Некорректность даты связана с тем, что формат ячеек соответствующего столбца общий, а нам нужно установить формат даты. Выделяем весь столбец, включая ячейки с ошибками, и кликаем по выделению правой кнопкой мыши. В появившемся списке переходим по пункту «Формат ячейки…».

В открывшемся окне форматирования открываем вкладку «Число». В блоке «Числовые форматы» выделяем значение «Дата». В правой части окна можно выбрать желаемый тип отображения даты. После того, как настройки выставлены, жмем на кнопку «OK».

Теперь дата отображается корректно. Но, как видим, вся нижняя часть таблицы заполнена ячейками, которые содержат ошибочное значение «#ЧИСЛО!». По сути, это те ячейки, данных из выборки для которых не хватило. Более привлекательно было бы, если бы они отображались вообще пустыми. Для этих целей воспользуемся условным форматированием. Выделяем все ячейки таблицы, кроме шапки. Находясь во вкладке «Главная» кликаем по кнопке «Условное форматирование», которая находится в блоке инструментов «Стили». В появившемся списке выбираем пункт «Создать правило…».

В открывшемся окне выбираем тип правила «Форматировать только ячейки, которые содержат». В первом поле под надписью «Форматировать только ячейки, для которых выполняется следующее условие» выбираем позицию «Ошибки». Далее жмем по кнопке «Формат…».

В запустившемся окне форматирования переходим во вкладку «Шрифт» и в соответствующем поле выбираем белый цвет. После этих действий щелкаем по кнопке «OK».

На кнопку с точно таким же названием жмем после возвращения в окно создания условий.

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

Урок: Условное форматирование в Excel

Способ 3: выборка по нескольким условиям с помощью формулы

Так же, как и при использовании фильтра, с помощью формулы можно осуществлять выборку по нескольким условиям. Для примера возьмем всю ту же исходную таблицу, а также пустую таблицу, где будут выводиться результаты, с уже выполненным числовым и условным форматированием. Установим первым ограничением нижнюю границу отбора по выручке в 15000 рублей, а вторым условием верхнюю границу в 20000 рублей.

Вписываем в отдельном столбце граничные условия для выборки.

Как и в предыдущем способе, поочередно выделяем пустые столбцы новой таблицы и вписываем в них соответствующие три формулы. В первый столбец вносим следующее выражение:
=ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(($D$2=C2:C29);СТРОКА(C2:C29);"");СТРОКА(C2:C29)-СТРОКА($C$1))-СТРОКА($C$1))

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

Каждый раз после ввода не забываем набирать сочетание клавиш Ctrl+Shift+Enter.

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

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

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

Слева от таблицы пропускаем один столбец. В ячейке следующего столбца, которая находится напротив первой ячейки с данными таблицы, вписываем формулу:
=СЛЧИС()

Эта функция выводит на экран случайное число. Для того, чтобы её активировать, жмем на кнопку ENTER.

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

Теперь у нас имеется диапазон ячеек, заполненный случайными числами. Но, он содержит в себе формулу СЛЧИС. Нам же нужно работать с чистыми значениями. Для этого следует выполнить копирование в пустой столбец справа. Выделяем диапазон ячеек со случайными числами. Расположившись во вкладке «Главная», щелкаем по иконке «Копировать» на ленте.

Выделяем пустой столбец и кликаем правой кнопкой мыши, вызывая контекстное меню. В группе инструментов «Параметры вставки» выбираем пункт «Значения», изображенный в виде пиктограммы с цифрами.

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

Активируется окно настройки сортировки. Обязательно устанавливаем галочку напротив параметра «Мои данные содержат заголовки», если шапка имеется, а галочки нет. В поле «Сортировать по» указываем наименование того столбца, в котором содержатся скопированные значения случайных чисел. В поле «Сортировка» оставляем настройки по умолчанию. В поле «Порядок» можно выбрать параметр как «По возрастанию», так и «По убыванию». Для случайной выборки это значения не имеет. После того, как настройки произведены, жмем на кнопку «OK».

После этого все значения таблицы выстраиваются в порядке возрастания или убывания случайных чисел. Можно взять любое количество первых строчек из таблицы (5, 10, 12, 15 и т.п.) и их можно будет считать результатом случайной выборки.

Урок: Сортировка и фильтрация данных в Excel

Как видим, выборку в таблице Excel можно произвести, как с помощью автофильтра, так и применив специальные формулы. В первом случае результат будет выводиться в исходную таблицу, а во втором – в отдельную область. Имеется возможность производить отбор, как по одному условию, так и по нескольким. Кроме того, можно осуществлять случайную выборку, использовав функцию СЛЧИС.

Еще статьи по данной теме:

Помогла ли Вам статья?

Источник

Download Article

Assembling the Data

Creating a Random Sample

Sorting the Sample

Tips

Warnings

This wikiHow teaches you how to generate a random selection from pre-existing data in Microsoft Excel. Random selections are useful for creating fair, non-biased samples of your data collection.

1

Open the Microsoft Excel program. You can also open an existing Microsoft Excel document if you have one that correlates to your random sample needs.
2

Select Blank workbook. If you aren’t opening a new document, skip this step.

Advertisement
3
Enter your data. To do this, click on a cell into which you wish to input data, then type in your data.
- Depending on the type of data you have, this process will vary. However, you should start all data in the «A» column.
- For example: you might place your users’ names in the «A» column and their responses to a survey (e.g., «yes» or «no») in the «B» column.
4

Make sure you have all relevant data entered into your spreadsheet. Once you’re positive that you’ve added all necessary data, you’re ready to generate your random sample.

1

Right-click the far left column’s name. For example, if all of your data begins in column «A», you’d right-click the «A» at the top of the page.
2
Click Insert. This will add a column to the left of your current left column.
- After doing this, any data that was in the «A» column will be relisted as being in the «B» column and so on.
3

Select the new «A1» cell.
4
Type «= RAND()» into this cell. Exclude the quotation marks. The «RAND» command applies a number between 0 and 1 to your selected cell.
- If Excel attempts to automatically format your «RAND» command, delete the formatting and re-type the command.
5

Press ↵ Enter. You should see a decimal (e.g., 0.5647) appear in your selected cell.
6

Select the cell with the random sample number.
7
Hold down Control and tap C. Doing this will copy the «RAND» command.
- For a Mac, you’ll hold down ⌘ Command instead of Control.
- You can also right-click the «RAND» cell and then select Copy.
8
Select the cell below your random sample number. This will likely be the «A2» cell.
- Clicking the «A1» cell and highlighting from there can cause a sorting error.
9
Highlight the rest of the random sample cells. To do this, you’ll hold down ⇧ Shift while clicking the cell at the bottom of your data range.
- For example, if your data in columns «B» and «C» extends all the way down to cell 100, you would hold down ⇧ Shift and click «A100» to select all «A» cells from A2 to A100.
10
Hold down Control and tap V. Doing so will paste the random sample command into all selected cells (e.g., A2 through A100). Once this is done, you’ll need to sort your data using the random numbers to reorder your results.
- Again, Mac users will need to hold down ⌘ Command instead of Control.

1
Select the top left cell. In most cases, this will be the «A1» cell. Before you can sort your sample, you’ll need to highlight all of your data.
- This includes the random sample numbers to the left of your data as well.
2
Hold down ⇧ Shift and select the bottom right cell. Doing so will highlight all of your data, making it ready for sorting.
- For example, if your data takes up two columns of 50 cells each, you would select «C50» while holding down ⇧ Shift.
- You can also click and drag your cursor from the top left corner to the bottom right corner of your data (or vice versa) to highlight it.
3
Right-click your data. This will bring up a context menu with options that will allow you to sort your data.
- If you’re using a Mac, you can click using two fingers (or hold down Ctrl and click) to bring up the context menu.
4

Hover your cursor over Sort.
5

Click Sort Smallest to Largest. You can also click Sort Largest to Smallest here—the only thing that matters is that your data is reorganized randomly according to the «= RAND()» values in the «A» column.
6
Review the sorting results. Depending on how many results you need, your process from here will vary. However, you can do a couple of things with the sorted data:
- Select the first, last, or middle half of the data. If your number of data points is too large to warrant this, you can also settle on a lower fraction (for example, the first eighth of the data).
- Select all odd- or even-numbered data. For example, in a set of 10 data points, you would either pick numbers 1, 3, 5, 7, and 9, or 2, 4, 6, 8, and 10.
- Select a number of random data points. This method works best for large sets of data where picking half of the information is too ambitious.
7

Choose your random sample participants. Now you have a non-biased sample pool for a survey, product giveaway, or something similar.

Add New Question

Question

When I copy the random values from column A and paste them in column B following the paste special -> values instruction, I get a different set of numbers from what I have in column A. Is this correct?

The values in column B should match what was in column A before, but as you paste them, the values in column A will change. That’s expected since the RAND() formula recalculates a new random number each time you do anything else in the spreadsheet. Copying the values only (not the formula) over to column B will prevent them from changing in future.

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Submit

If you don’t have Microsoft Excel, there are other free programs online (such as Google Sheets or Outlook’s Excel app) that may allow you to create a random sample.
Microsoft makes an Excel app for iPhone and Android platforms so you can create spreadsheets on-the-go.

Thanks for submitting a tip for review!

Failing to use a random sample when looking for results (e.g., sending out a survey after updating a service) may cause your answers to be biased—and, therefore, inaccurate.

About This Article

Thanks to all authors for creating a page that has been read 442,538 times.

Is this article up to date?

Источник

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

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

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

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

Способ 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

Источник

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

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

Выборка из нормального распределения

Советы

Предупреждения

Об этой статье

Была ли эта статья полезной?

Выполнение выборки

Способ 1: применение расширенного автофильтра

Способ 2: применение формулы массива

Способ 3: выборка по нескольким условиям с помощью формулы

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

Еще статьи по данной теме:

Помогла ли Вам статья?

About This Article

Is this article up to date?

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

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

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

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

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

А вот еще интересные статьи: