Как сделать случайную выборку

Как создать
Содержание
  1. Случайная выборка из генеральной совокупности в EXCEL
  2. Случайная выборка
  3. Выборка с определенной периодичностью
  4. Выборка из нормального распределения
  5. Простая случайная выборка
  6. Как сделать случайную выборку в Excel из списка?
  7. Создать список уникальных значений с помощью специальной функции
  8. Создать список уникальных значений с помощью расширенного фильтра
  9. Создать список уникальных значений с помощью формул
  10. Случайная выборка
  11. Способ 1. Случайная сортировка
  12. Способ 2. Функция НАИМЕНЬШИЙ
  13. Способ 3. Случайная выборка без повторов — функция Lotto на VBA
  14. Выборка и анализ данных в Excel
  15. Как сделать выборку в Excel из списка
  16. Как выбрать уникальные и повторяющиеся значения в Excel – пошаговая инструкция
  17. Подготовка содержания выпадающего списка
  18. Модификация исходной таблицы
  19. Создание выпадающего списка
  20. Выборка ячеек из таблицы по условию
  21. Скачать пример выборки из списка с условным форматированием
  22. 4 способа выборки данных в Excel
  23. Расширенный автофильтр
  24. Формула массива
  25. Выборка с несколькими условиями с помощью формулы
  26. Случайная выборка

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

Простая случайная выборка

Этот метод по­хож на розыгрыш лотереи, когда таблички с именами участников помещаются в барабан, кото­рый встряхивается, и из него произвольным образом извлекают отдельные таблички, в резуль­тате объективно определяются имена победителей.

При простой случайной выборке исследователь сначала формирует основу выборочного на­блюдения, в которой каждому элементу присваивается уникальный идентификационный но­мер. Затем генерируются случайные числа, чтобы определить номера элементов, которые будут включены в выборку. Эти случайные числа могут генерироваться компьютерной программой или выбираться из таблицы.

Предпо­ложим, что выборочную совокупность, объем которой равен 10, нужно сформировать из осно­вы выборочного наблюдения, содержащей 800 элементов. Поступают таким образом: выбирают по три правые цифры в каждом ряду, начиная с первой колонки и первого ряда табл. 1, и дви­гаются вниз, пока не будет отобрано 10 чисел из 800. Числа, которые находятся за пределами этого диапазона, не включаются. Выборочная совокупность создается из элементов, соответст­вующих случайно выбранным номерам. Так, в нашем примере будут выбраны элементы: 480, 368,130, 167, 570, 562, 301, 579,475 и 553. Обратите внимание, что последние три цифры ряда 6 (921) и ряда 11 (918) не включены в выборочную совокупность, поскольку они находятся за пределами установленного диапазона.

Простая случайная выборка имеет очевидные преимущества:

· Этот метод крайне прост для понимания.

· Результаты исследования можно распространять на изучаемую совокупность.

Однако метод простой случайной выборки имеет как минимум четыре существенных ограничения.

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

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

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

В-четвертых, в результа­те применения SRS может сформироваться нерепрезентативная выборка. Хотя выборки, полу­ченные простым случайным отбором, в среднем адекватно представляют генеральную сово­купность, некоторые из них крайне некорректно представляют изучаемую совокупность. Веро­ятность этого особенно велика при небольшом объеме выборки. Простая случайная выборка не часто используется в маркетинговых исследованиях. Более популярен метод систематической выборки.

Источник

Как сделать случайную выборку в Excel из списка?

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

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

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

Читайте также:  Как сделать большое искусственное озеро

Список уникальных значений возможно создать 6-ю способами:

Создать список уникальных значений с помощью специальной функции

Это очень простой способ для владельцев Excel выше 2007 версии как произвести отбор уникальных значений. Вам нужно на вкладке «Данные», в разделе «Работа с данными», использовать специальную команду «Удалить дубликаты».

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

В случае, когда в выделенном диапазоне размещается и заголовок таблицы, то поставьте галочку на пункте «Мои данные содержат заголовки», что бы вы случайно не удалили данные. Внимание! Когда вы будете производить отсев уникальных значений в таблице, где столбиков больше 2 и они взаимосвязаны информацией, Excel предложит вам расширить диапазон выбора, с чем вы должны, согласится, иначе будет нарушена логическая связь с другими столбиками.

Создать список уникальных значений с помощью расширенного фильтра

Это также не сложный способ произвести отбор уникальных значений в таблице.

Использовать этот инструмент возможно на вкладке «Данные», потом выбрать «Фильтр», и наконец «Расширенный фильтр», этот путь подходит для Excel 2003, а вот владельцы более юных версий, от 2007 и выше стоит пройти по пути: «Данные» — «Сортировка и фильтр» — «Дополнительно».

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

Внимание!Если программа запрещает вам переносить отфильтрованные данные на другой лист, вы просто запустите «Расширенный фильтр» на том листе, куда вам надо перенести отобранные уникальные значения.

Создать список уникальных значений с помощью формул

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

Пример 1. Вам нужно пронумеровать, уникальные, значение в списке значений, для этого нужно использовать функцию ЕСЛИ в формуле следующего вида:

ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;»«)

Суть формулы в том, что она проверяет сколько раз, текущее значение встречается в вашем диапазоне (начиная с начала), и если это значение равно 1, то есть это первое уникальное значение, формула ставит последовательно возвращающий номер по порядку.

Теперь можно произвести отбор уникальных значений, которые были ранее пронумерованы. Сделать это возможно в любом из соседних столбиков используя функцию ВПР и копируя ее вниз:

ЕСЛИ(МАКС(A1:A100)

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

Нечастая, но интересная задача: выбрать из массива данных (списка) случайным образом 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

ReDim iArr(Bottom To Top)
For i = Bottom To Top
iArr(i) = i
Next i

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

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

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

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

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

Читайте также:  Как сделать акварельный дизайн

Выборка и анализ данных в Excel

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

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

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

Статистический пакет анализа данных. Для его установки в меню Сервис выбирается команда Надстройки и далее в списке Пакет анализа.

Для использования инструментов анализа, анализируемые данные следует представить в виде строк или столбцов. Совокупность ячеек, содержащих эти данные, называется входным диапазоном.

В меню Сервис выбирается команда Анализ данных. В списке Инструменты анализа выбирается необходимая строка. Далее вводятся входной и выходной диапазоны.

Корреляционный анализ. Используется для количественной оценки взаимосвязи двух наборов данных, представленных в безразмерном виде.

Корреляционный анализ дает возможность установить: ассоциированы ли наборы данных по величине, то есть, большие значения из одного набора данных связаны с большими значениями другого набора (положительная корреляция), или, наоборот, малые значения одного набора связаны с большими значениями другого (отрицательная корреляция), или данные двух диапазонов никак не связаны (корреляция близка к нулю). Для вычисления коэффициента корреляции между двумя наборами данных используется статистическая функция КОРЕЛ.

Ковариационный анализ. Ковариация является мерой связи между двумя диапазонами данных. Используется для вычисления среднего произведения отклонений точек данных относительно средних.

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

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

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

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

Например, можно использовать нормальное распределение для моделирования совокупности данных по арифметическим ошибкам в бухгалтерском учете. Чтобы в результате выполнения вычислений вернуть равномерно распределенное случайное число, большее или равное 0 и меньшее 1, используется функция СЛЧИС().

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

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

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

Например, на объем реализации влияют несколько факторов, включая цену, выпуск и сезонность. Регрессия пропорционально распределяет меру реализации по этим трем факторам на основе данных функционирования организации.

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

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

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

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

Источник

Как сделать выборку в Excel из списка

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

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

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

Как выбрать уникальные и повторяющиеся значения в Excel – пошаговая инструкция

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

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

Читайте также:  Как сделать гель для ресниц

Подготовка содержания выпадающего списка

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

Здесь нам надо выделить все операции, совершенные по отношению к каждому конкретному контрагенту, с помощью цвета. Чтобы переключаться между ними, применим выпадающий список. Следовательно, изначально нужно его сделать, а для этого надо отобрать данные, которые будут его элементами. В нашем примере нам требуются все фамилии контрагентов, которые есть в колонке А и при этом не повторяются. Для подготовки содержания выпадающего списка нам нужно выполнить следующую инструкцию:

Теперь видим перечень ячеек с уникальными фамилиями, которые уже не повторяются. Это и будет наша выборка для списка.

Модификация исходной таблицы

После этого надо внести некоторые изменения в нашу таблицу. Для этого нужно выделить первые два ряда и нажать на комбинацию клавиш Ctrl+Shift+=. Таким образом у нас вставляются две дополнительные строчки. В новосозданную ячейку A1 вставляем слово «Клиент».

Создание выпадающего списка

После этого нам нужно создать выпадающий список. Чтобы это сделать, выполните следующие действия:

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

В случае же с нами в этом нет никакой необходимости, потому что у нас и без того вся информация располагается на одном листе.

Выборка ячеек из таблицы по условию

Теперь же давайте попробуем создать выборку ячеек по условию. Для этого надо выделить ту таблицу, в которой указана фамилия контрагента, его код, номер транзакции и сумма операции, после чего откроем окно «Условное форматирование». Чтобы его вызвать, нужно перейти на вкладку «Главная», там найти группу «Стили», и в ней будет кнопка «Условное форматирование».

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

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

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

Принцип действия следующий: проверяется значение в столбце А. Если оно равно тому, которое выбрано в списке, находящемся в ячейке B1, то эта формула возвращает значение ИСТИНА. После этого целая строка форматируется таким образом, каким вы захотите. В принципе, можно не только выделять эту строчку отдельным цветом, но и произвольно регулировать шрифт, границы и другие параметры. Но выделение цветом – самый быстрый метод.

Как мы добились того, чтобы цветом окрашивалась целая строка, а не отдельная ячейка? Для этого мы применили ссылку на ячейку, где адрес колонки является абсолютным, а номер ряда – относительным.

Скачать пример выборки из списка с условным форматированием

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

4 способа выборки данных в Excel

Но на этом мы не заканчиваем нашу инструкцию. На самом деле, у нас есть целых четыре доступных способа формирования выборки данных в Excel.

Расширенный автофильтр

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

Формула массива

Еще один вариант отбора – использование формулы массива. В этом случае результат выводится в отдельную таблицу, что может быть полезно, если первоначальные данные всегда нужно иметь перед глазами в неизменном виде. Чтобы это сделать, нам нужно следующее:

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

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

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

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

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

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

Далее выбираем «Настраиваемая сортировка» в меню фильтра.

Появляется меню настроек, где выставляем параметры так, как на скриншоте.

Потом нажимаем «ОК» и получаем результат.

Видим, что нет ничего сложного. Если немного потренироваться, то все будет получаться очень легко. Главное – понять принцип, а метод можно выбрать любой, какой душе угодно.

Источник

Оцените статью
Полезные советы и лайфхаки для жизни