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

Как выбрать случайную выборку в 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.

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

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

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

Инструмент Пакета анализа 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)))

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

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

покупка

Сделайте случайный выборочный выбор в Excel (полное руководство)

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

• Выберите случайную выборку с помощью формул
  • Выберите случайные значения/строки
  • Выбор случайных значений из списка
  • Выберите случайные значения из списка без дубликатов
  • Выбор случайных значений из списка в Excel 365/2021

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

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

  Выберите случайные значения/строки с помощью функции СЛЧИС.

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

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

  

  Шаг 1. Добавление вспомогательного столбца

  1. Во-первых, вам нужно добавить вспомогательный столбец в диапазон данных. В этом случае я выбираю ячейку E1 (ячейку, соседнюю с ячейкой заголовка в последнем столбце диапазона данных), ввожу заголовок столбца, а затем вводю приведенную ниже формулу в ячейку E2 и нажимаю Enter чтобы получить результат.

  Функции: функция СЛЧИС генерирует случайное число от 0 до 1.

  =RAND()

  

  Шаг 2. Сортировка вспомогательного столбца

  1. Выберите диапазон данных и вспомогательный столбец, перейдите к Данные , нажмите Сортировать.

  

  1. Сортировать по: ваш вспомогательный столбец («Вспомогательный столбец» в нашем примере).
  2. Сортировать по значения ячеек.
  3. Выберите сортировку заказ тебе нужно.
  4. Нажмите OK кнопка. Смотрите скриншот.

  

  Теперь весь диапазон данных отсортирован по вспомогательному столбцу.

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

  После сортировки строки в исходном диапазоне данных будут располагаться в случайном порядке. Теперь вы можете просто выбрать первые n строк, где n — количество случайных строк, которые вы хотите выбрать. Затем нажмите Ctrl + C чтобы скопировать выбранные строки и вставить их в любое место.

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

  

  Заметки:

  • Чтобы обновить случайные значения, нажмите кнопку F9 .
  • Каждый раз, когда вы обновляете лист, например добавляете новые данные, изменяете ячейки, удаляете данные и т. д., результаты формулы автоматически изменяются.
  • Если вспомогательный столбец вам больше не нужен, его можно удалить.
  • Если вы ищете еще более простой подход, подумайте о том, чтобы попробовать «Выбрать диапазон случайным образом» особенность Kutools for Excel. Всего несколькими щелчками мыши вы можете легко выбирать случайные ячейки, строки или даже столбцы из указанного диапазона. Нажмите здесь, чтобы начать 30-дневную бесплатную пробную версию Kutools for Excel.

  

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

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

  В данном случае мне нужно сгенерировать 7 случайных значений из диапазона B2:B53. Я выбираю пустую ячейку D2, ввожу следующую формулу и нажимаю Enter чтобы получить первое случайное значение из столбца B.

  =INDEX($B2:$B53,RANDBETWEEN(1,COUNTA($B2:$B53)),1)

  

  

  Заметки:

  • В формуле $B2:$B53 — это диапазон, из которого вы хотите выбрать случайную выборку.
  • Чтобы обновить случайные значения, нажмите кнопку F9 .
  • Если в списке есть дубликаты, в результатах могут появиться повторяющиеся значения.
  • Каждый раз, когда вы обновляете лист, например добавляете новые данные, изменяете ячейки, удаляете данные и т. д., случайные результаты будут автоматически меняться.

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

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

  Шаг 1. Добавление вспомогательного столбца

  1. Во-первых, вам нужно создать вспомогательный столбец рядом со столбцом, из которого вы хотите выбрать случайную выборку. В данном случае я выбираю ячейку C2 (ячейку, примыкающую ко второй ячейке столбца B), ввожу приведенную ниже формулу и нажимаю Enter.

  Функции: функция СЛЧИС генерирует случайное число от 0 до 1.

  =RAND()

  

  Шаг 2. Получите случайные значения из списка без дубликатов.

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

  =INDEX($B$2:$B$53, RANK.EQ(C2, $C$2:$C$53) + COUNTIF($C$2:C53, C2) - 1, 1)

  

  

  Заметки:

  • В формуле $B2:$B53 — это список столбцов, из которого вы хотите выбрать случайную выборку. И $C2:$C53 — это диапазон вспомогательного столбца.
  • Чтобы обновить случайные значения, нажмите кнопку F9 .
  • Результат не будет содержать повторяющихся значений.
  • Каждый раз, когда вы обновляете лист, например добавляете новые данные, изменяете ячейки, удаляете данные и т. д., случайные результаты будут автоматически меняться.

  Выбор случайных значений из списка в Excel 365/2021

  Если вы используете Excel 365 или 2021, вы можете применить новые функции «СОРТИРОВАТЬ ПО(Основной ключ) и РАНДАРРАЙ», чтобы легко генерировать случайную выборку в Excel.

  Шаг 1. Добавление вспомогательного столбца

  1. Во-первых, вам нужно добавить вспомогательный столбец в диапазон данных. В данном случае я выбираю ячейку C2 (ячейку, соседнюю со второй ячейкой столбца, из которого вы хотите выбрать случайные значения), ввожу приведенную ниже формулу и нажимаю Enter чтобы получить результаты.

  =SORTBY(B2:B53,RANDARRAY(COUNTA(B2:B53)))

  

  Заметки

  • В формуле B2: B53 — это список, из которого вы хотите выбрать случайную выборку.
  • Если вы используете Excel 365, список случайных значений будет сгенерирован автоматически после нажатия кнопки Enter .
  • Если вы используете Excel 2021, после получения первого случайного значения выберите ячейку с формулой и перетащите маркер заполнения вниз, чтобы получить желаемое количество случайных значений.
  • Чтобы обновить случайные значения, нажмите кнопку F9 .
  • Каждый раз, когда вы обновляете лист, например добавляете новые данные, изменяете ячейки, удаляете данные и т. д., случайные результаты будут автоматически меняться.

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

  Во вспомогательном столбце теперь вы можете просто выбрать n верхних ячеек, где n — количество случайных значений, которые вы хотите выбрать. Затем нажмите Ctrl + C чтобы скопировать выбранные значения, щелкните правой кнопкой мыши пустую ячейку и выберите Наши ценности из Параметры вставки раздел в контекстном меню.

  

  Заметки:

  • Чтобы автоматически генерировать указанное количество случайных значений или строк из указанного диапазона, введите число, представляющее количество случайных значений или строк, которые будут созданы в ячейке (в этом примере C2), а затем примените одну из следующих формул.

  Генерация случайных значений из списка:

  =INDEX(SORTBY(B2:B53, RANDARRAY(ROWS(B2:B53))), SEQUENCE(C2))

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

  Генерация случайных строк из диапазона:

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

  =INDEX(SORTBY(A2:B53, RANDARRAY(ROWS(A2:B53))), SEQUENCE(C2), )

  Функции: Массив в конце формулы должен соответствовать числу, указанному вами в C2. Если вы хотите сгенерировать 3 случайных выборки, вам нужно не только ввести число 3 в ячейку C2, но также указать массив как . Чтобы сгенерировать 4 случайных выборки, введите число 4 в ячейку и укажите массив как .

  Несколько кликов, чтобы выбрать случайную выборку с помощью удобного инструмента

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

  После установка Kutools для Excel, нажмите Кутулс > Выберите > Выбрать диапазон случайным образом, то вам нужно настроить следующим образом.

  • Выберите столбец или диапазон, из которого вы хотите выбрать случайные значения, строки или столбцы.
  • В Сортировка / выбор диапазона случайным образом диалоговом окне укажите количество случайных значений для выбора.
  • Выберите вариант в Выберите тип .
  • Нажмите OK.

  

  Результат

  я указал номер 5 в «Количество ячеек для выбора» раздел и выбрал «Выбрать случайные строки«вариант в»Тип выбора«. В результате в указанном диапазоне случайным образом будут выбраны 5 строк данных. Затем вы можете скопировать и вставить эти выбранные строки куда захотите.

  

  Заметки:

  • Чтобы использовать эту функцию, вы должны установить Kutools for Excel во-первых, пожалуйста нажмите, чтобы загрузить и получить 30-дневную бесплатную пробную версию сейчас.
  • Чтобы узнать больше об этой функции, посетите эту страницу: Случайным образом выберите ячейки, строки или столбцы в диапазоне ячеек

  Статьи по теме

  Рандомизировать список в Excel
  В этом уроке мы поговорим о том, как легко рандомизировать или перетасовать список в Excel за несколько шагов.

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

  Быстро генерировать случайное время
  В этом уроке мы поговорим о том, как генерировать случайное время в Excel.

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

  Похожие публикации:

  1. Speed perfect на стиральной машине что это за режим
  2. Из чего состоит слайд в информатике
  3. Как добавить человека в ватсап в группу с айфона
  4. Как списывать с apple watch