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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В частности, функция RANDBETWEEN генерирует случайное целое число между двумя указанными вами значениями. Для нижнего значения вы указываете число 1. Для верхнего значения вы используете COUNTA или ROWS, чтобы получить общее количество строк. В результате RANDBETWEEN возвращает случайное число от 1 до общего количества строк в вашем наборе данных. Это число передается аргументу row_num функции INDEX, сообщая ему, какую строку выбрать. Для аргумента column_num мы используем 1, поскольку мы хотим извлечь значение из первого столбца.

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

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

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

  1. Введите формулу Rand в B2 и скопируйте ее в столбец:

    = RAND ()
  2. Поместите приведенную ниже формулу в C2, чтобы извлечь случайное значение из столбца A:

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

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

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

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

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

Если вы ищете пуленепробиваемую формулу для случайного выбора только с уникальными значениями, используйте комбинацию RANK + COUNTIF или RANK.EQ + COUNTIF вместо просто RANK. Подробное объяснение логики см. В разделе Уникальное ранжирование в Excel.

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

= ИНДЕКС ($ A $ 2: $ A $ 16, RANK.EQ (B2; $ B $ 2: $ B $ 16) + СЧЁТЕСЛИ ($ B $ 2: B2; B2) - 1, 1)

  • Как и RANDBETWEEN, функция RAND в Excel также генерирует новые случайные числа при каждом пересчете вашего рабочего листа, что приводит к изменению случайного выбора. Чтобы образец оставался неизменным, скопируйте его и вставьте в другое место в качестве значений ( Специальная вставка >Значения ).
  • Если одно и то же имя (число, дата или любое другое значение) встречается в исходном наборе данных более одного раза, случайная выборка может также содержать несколько вхождений одного и того же значения.

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

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

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

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

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

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

Когда наш Ultimate Suite установлен в вашем Excel, вы делаете следующее:

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

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

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

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

Если вы хотите протестировать инструмент «Случайный выбор» на своих листах, не стесняйтесь загрузить пробную версию Ultimate Suite for Excel: