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

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

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

Что такое случайная выборка?

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

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

Например, вы хотите провести небольшой опрос среди своих клиентов. Очевидно, что было бы неразумно рассылать анкету каждому отдельному человеку в вашей многотысячной базе данных. Итак, кого делать ваш опрос? Это будут 100 новых клиентов, или первые 100 клиентов, перечисленных в алфавитном порядке, или 100 человек с самыми короткими именами? Ни один из этих подходов не соответствует вашим потребностям, потому что они изначально предвзяты. Чтобы получить беспристрастную выборку, в которой все имеют равные возможности быть выбранными, проведите случайный отбор, используя один из методов, описанных ниже.

Случайный выбор Excel с формулами

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

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

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

=ИНДЕКС($A$2:$A$10,СЛУЧМЕЖДУ(1,СЧЕТЧ($A$2:$A$10)),1)

или же

=ИНДЕКС($A$2:$A$10,СЛУЧМЕЖДУ(1,СТРОКИ($A$2:$A$10)),1)

Вот и все! Ваше средство выбора случайных имен для Excel настроено и готово к работе:
Выбор случайного имени в Excel

Примечание. Имейте в виду, что СЛУЧМЕЖДУ — это изменчивая функция, то есть она будет пересчитываться при каждом изменении, которое вы вносите в рабочий лист. В результате ваш случайный выбор также изменится. Чтобы этого не произошло, вы можете скопировать извлеченное имя и вставить его как значение в другую ячейку (Специальная вставка > Ценности). Подробные инструкции см. в разделе Как заменить формулы значениями.

Естественно, эти формулы могут выбирать не только случайные имена, но и случайные числа, даты или любые другие случайные ячейки.

Как работают эти формулы

Короче говоря, вы используете функцию ИНДЕКС для извлечения значения из списка на основе случайного номера строки, возвращаемого функцией СЛУЧМЕЖДУ.

В частности, функция СЛУЧМЕЖДУ генерирует случайное целое число между двумя указанными вами значениями. Для нижнего значения вы указываете число 1. Для верхнего значения вы используете COUNTA или ROWS, чтобы получить общее количество строк. В результате СЛУЧМЕЖДУ возвращает случайное число от 1 до общего количества строк в вашем наборе данных. Этот номер идет на row_num аргумент функции ИНДЕКС, указывающий, какую строку выбрать. Для номер_столбца аргумент, мы используем 1, так как мы хотим извлечь значение из первого столбца.

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

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

Есть несколько способов выбрать случайные данные без дубликатов в Excel. Как правило, вы используете функцию RAND для присвоения случайного числа каждой ячейке, а затем выбираете несколько ячеек, используя формулу Index Rank.

Имея список имен в ячейках A2:A16, выполните следующие действия, чтобы извлечь несколько случайных имен:

  1. Введите формулу Рэнда в ячейку B2 и скопируйте ее вниз по столбцу:
    =СЛУЧАЙ()
  2. Поместите приведенную ниже формулу в C2, чтобы извлечь случайное значение из столбца A:

    =ИНДЕКС($A$2:$A$16, РАНГ(B2,$B$2:$B$16), 1)

  3. Скопируйте приведенную выше формулу в столько ячеек, сколько случайных значений вы хотите выбрать. В нашем случае мы копируем формулу еще в четыре ячейки (C2:C6).

Вот и все! Пять случайных имен извлекаются без дубликатов:
Случайный выбор в Excel без дубликатов

Как работает эта формула

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

  • Формула RAND заполняет столбец B случайными числами.
  • Функция RANK возвращает ранг случайным числом в той же строке. Например, RANK(B2,$B$2:$B$16) в ячейке C2 получает ранг числа в B2. При копировании в C3 относительная ссылка B2 изменяется на B3 и возвращает ранг числа в B3 и так далее.
  • Число, возвращенное RANK, передается в row_num аргумент функции ИНДЕКС, поэтому она выбирает значение из этой строки. в номер_столбца аргумент, вы указываете 1, потому что хотите извлечь значение из первого столбца.

Слово предостережения! Как показано на скриншоте выше, наша случайная выборка Excel содержит только уникальные значения. Но теоретически существует небольшая вероятность появления дубликатов в вашей выборке. И вот почему: в очень большом наборе данных RAND может сгенерировать повторяющиеся случайные числа, а RANK вернет тот же ранг для этих чисел. Лично у меня за время тестов ни разу не было дубликатов, но теоретически такая вероятность существует.

Если вы ищете надежную формулу для получения случайного выбора только с уникальными значениями, используйте комбинацию РАНГ + СЧЁТЕСЛИ или РАНГ.EQ + СЧЁТЕСЛИ вместо просто РАНГ. Подробное объяснение логики см. в разделе Уникальное ранжирование в Excel.

Полная формула немного громоздка, но на 100% не содержит дубликатов:

=ИНДЕКС($A$2:$A$16, РАНГ.EQ(B2, $B$2:$B$16) + СЧЁТЕСЛИ($B$2:B2, B2) – 1, 1)
Формула для получения случайной выборки в Excel без дубликатов

Заметки:

  • Как и СЛУЧМЕЖДУ, функция СЛУЧАЙ в Excel также генерирует новые случайные числа при каждом пересчете вашего рабочего листа, вызывая изменение случайного выбора. Чтобы сохранить образец без изменений, скопируйте его и вставьте в другое место в качестве значений (Вставить Специальный > Ценности).
  • Если одно и то же имя (число, дата или любое другое значение) появляется более одного раза в исходном наборе данных, случайная выборка также может содержать несколько вхождений одного и того же значения.

Дополнительные способы получения случайного выбора без повторов в Excel 365–2010 описаны здесь: Как получить случайный выбор в Excel без повторов.

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

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

  1. Вставьте новый столбец справа или слева от таблицы (столбец D в этом примере).
  2. В первую ячейку вставленного столбца, исключая заголовки столбцов, введите формулу СЛЧИС: =СЛЧИС()
  3. Дважды щелкните маркер заполнения, чтобы скопировать формулу вниз по столбцу. В результате каждой строке будет присвоено случайное число.
  4. Отсортируйте случайные числа от большего к меньшему (сортировка по возрастанию приведет к перемещению заголовков столбцов в нижнюю часть таблицы, поэтому обязательно сортируйте по убыванию). Для этого отправляйтесь в Данные вкладка > Сортировать и фильтровать группу и нажмите кнопку ZA. Excel автоматически расширит выборку и отсортирует все строки в случайном порядке.

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

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

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

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

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

Генератор случайных чисел для Excel, входящий в состав Ultimate Suite, позволяет:

  1. Выберите любую ячейку в таблице.
  2. Перейти к инструменты вкладка > Утилиты группу и нажмите «Случайно» > «Выбрать случайным образом»:
    Выбрать случайным образом в Excel
  3. На панели надстройки выберите, что выбрать: случайные строки, случайные столбцы или случайные ячейки.
  4. Укажите число или процент для желаемого размера выборки.
  5. Нажмите кнопку Выбрать. Сделанный!

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

И через секунду вы получите случайный выбор:
Случайный выбор в Excel без дубликатов

Теперь вы можете нажать Ctrl + C, чтобы скопировать случайный образец, а затем нажать Ctrl + V, чтобы вставить его в нужное место на том же или другом листе.

Если вы хотите протестировать инструмент «Случайный порядок» на своих листах, просто скачайте пробную версию Ultimate Suite ниже. Если вы используете электронные таблицы Google, вы можете найти наш Генератор случайных чисел для Google Таблиц полезный.

Доступные загрузки

Выбор случайной выборки – примеры формул (файл .xlsx)
Ultimate Suite – пробная версия (файл .exe)

Вас также могут заинтересовать

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *