Случайная выборка в Excel
Обновлено: 03.07.2024
Это руководство научит вас нескольким быстрым способам случайного выбора имен, чисел или любых других данных. Вы также узнаете, как получить случайную выборку без дубликатов и как случайным образом выбрать заданное количество или процент ячеек, строк или столбцов одним щелчком мыши.
Независимо от того, проводите ли вы исследование рынка для запуска нового продукта или оцениваете результаты своей маркетинговой кампании, важно использовать объективную выборку данных для анализа. И самый простой способ добиться этого — получить случайный выбор в Excel.
Что такое случайная выборка?
Прежде чем обсуждать методы выборки, давайте предоставим немного справочной информации о случайном отборе и о том, когда вы можете захотеть его использовать.
В теории вероятностей и статистике случайная выборка – это подмножество данных, выбранных из более крупного набора данных, также известного как совокупность. Каждый элемент случайной выборки выбирается совершенно случайно и имеет равную вероятность быть выбранным. Зачем он вам нужен? В основном, чтобы получить непредвзятое представление об общей численности населения.
Например, вы хотите провести небольшой опрос среди своих клиентов. Очевидно, что было бы неразумно рассылать анкету каждому отдельному человеку в вашей многотысячной базе данных. Итак, кого делать ваш опрос? Это будут 100 новых клиентов, или первые 100 клиентов, перечисленных в алфавитном порядке, или 100 человек с самыми короткими именами? Ни один из этих подходов не соответствует вашим потребностям, потому что они изначально предвзяты. Чтобы получить беспристрастную выборку, в которой у всех есть равные шансы быть выбранными, сделайте случайный выбор, используя один из методов, описанных ниже.
Случайный выбор Excel с формулами
В Excel нет встроенной функции для случайного выбора ячеек, но вы можете использовать одну из функций для создания случайных чисел в качестве временного решения. Наверное, их нельзя назвать простыми интуитивными формулами, но они работают.
Как выбрать случайное значение из списка
Предположим, у вас есть список имен в ячейках A2:A10, и вы хотите случайным образом выбрать одно имя из списка. Это можно сделать с помощью одной из следующих формул:
Вот оно! Средство выбора случайного имени для Excel настроено и готово к работе:
Примечание. Имейте в виду, что СЛУЧМЕЖДУ — это изменчивая функция, то есть она будет пересчитываться при каждом изменении, которое вы вносите в рабочий лист. В результате ваш случайный выбор также изменится. Чтобы этого не произошло, вы можете скопировать извлеченное имя и вставить его как значение в другую ячейку (Специальная вставка > Значения). Подробные инструкции см. в разделе Как заменить формулы значениями.
Естественно, эти формулы могут выбирать не только случайные имена, но и случайные числа, даты или любые другие случайные ячейки.
Как работают эти формулы
Короче говоря, вы используете функцию ИНДЕКС для извлечения значения из списка на основе случайного номера строки, возвращаемого функцией СЛУЧМЕЖДУ.
В частности, функция СЛУЧМЕЖДУ генерирует случайное целое число между двумя указанными вами значениями. Для нижнего значения вы указываете число 1. Для верхнего значения вы используете COUNTA или ROWS, чтобы получить общее количество строк. В результате СЛУЧМЕЖДУ возвращает случайное число от 1 до общего количества строк в вашем наборе данных. Это число передается в аргумент номер_строки функции ИНДЕКС, сообщая ей, какую строку выбрать. Для аргумента номер_столбца мы используем 1, так как хотим извлечь значение из первого столбца.
Примечание. Этот метод хорошо работает для выбора одной случайной ячейки из списка. Если ваш образец должен включать несколько ячеек, приведенная выше формула может возвращать несколько вхождений одного и того же значения, поскольку функция СЛУЧМЕЖДУ не свободна от дубликатов. Это особенно актуально, когда вы выбираете относительно большую выборку из относительно небольшого списка. В следующем примере показано, как сделать случайный выбор в Excel без дубликатов.
Как сделать случайный выбор в Excel без дубликатов
Есть несколько способов выбрать случайные данные без дубликатов в Excel. Как правило, вы используете функцию RAND для присвоения случайного числа каждой ячейке, а затем выбираете несколько ячеек, используя формулу Index Rank.
Со списком имен в ячейках A2:A16 выполните следующие действия, чтобы извлечь несколько случайных имен:
- Введите формулу Rand в ячейку B2 и скопируйте ее вниз по столбцу:
=RAND() - Поместите приведенную ниже формулу в C2, чтобы извлечь случайное значение из столбца A:
Вот оно!Извлекаются пять случайных имен без дубликатов:
Как работает эта формула
Как и в предыдущем примере, вы используете функцию ИНДЕКС для извлечения значения из столбца А на основе случайной координаты строки. В этом случае для его получения требуются две разные функции:
- Формула RAND заполняет столбец B случайными числами.
- Функция RANK возвращает ранг в виде случайного числа в той же строке. Например, RANK(B2,$B$2:$B$16) в ячейке C2 получает ранг числа в B2. При копировании в C3 относительная ссылка B2 изменяется на B3 и возвращает ранг числа в B3 и т. д.
- Число, возвращаемое функцией RANK, передается в аргумент номер_строки функции ИНДЕКС, поэтому она выбирает значение из этой строки. В аргументе номер_столбца вы указываете 1, потому что хотите извлечь значение из первого столбца.
Осторожно! Как показано на скриншоте выше, наша случайная выборка Excel содержит только уникальные значения. Но теоретически существует небольшая вероятность появления дубликатов в вашей выборке. И вот почему: в очень большом наборе данных RAND может сгенерировать повторяющиеся случайные числа, а RANK вернет тот же ранг для этих чисел. Лично у меня ни разу не было дубликатов во время тестов, но теоретически такая вероятность есть.
Если вам нужна надежная формула для получения случайного выбора только с уникальными значениями, используйте комбинацию РАНГ + СЧЁТЕСЛИ или РАНГ.EQ + СЧЁТЕСЛИ вместо просто РАНГ. Подробное объяснение логики см. в разделе Уникальное ранжирование в Excel.
Полная формула немного громоздка, но на 100% не повторяется:
=ИНДЕКС($A$2:$A$16, RANK.EQ(B2, $B$2:$B$16) + СЧЁТЕСЛИ($B$2:B2, B2) - 1, 1)
- Как и СЛУЧМЕЖДУ, функция СЛУЧАЙ в Excel также генерирует новые случайные числа при каждом пересчете вашего рабочего листа, вызывая изменение случайного выбора. Чтобы сохранить образец без изменений, скопируйте его и вставьте в другое место в качестве значений (ВставитьСпециальные >Значения).
- Если одно и то же имя (число, дата или любое другое значение) встречается в исходном наборе данных более одного раза, случайная выборка также может содержать несколько вхождений одного и того же значения.
Как выбрать случайные строки в Excel
Если ваш рабочий лист содержит более одного столбца данных, вы можете выбрать случайную выборку следующим образом: назначить случайное число каждой строке, отсортировать эти числа и выбрать необходимое количество строк. Подробные шаги приведены ниже.
- Вставьте новый столбец справа или слева от таблицы (столбец D в этом примере).
- В первой ячейке вставленного столбца, исключая заголовки столбцов, введите формулу СЛЧИС: =СЛУЧИС()
- Дважды щелкните маркер заполнения, чтобы скопировать формулу вниз по столбцу. В результате каждой строке будет присвоено случайное число.
- Сортировка случайных чисел от большего к меньшему (сортировка по возрастанию приведет к смещению заголовков столбцов в конец таблицы, поэтому обязательно сортируйте по убыванию). Для этого перейдите на вкладку Данные в группу Сортировка и фильтрация и нажмите кнопку ZA. Excel автоматически расширит выборку и отсортирует все строки в случайном порядке.
Чтобы поближе познакомиться с формулами, обсуждаемыми в этом руководстве, вы можете загрузить наш образец рабочей книги в Excel Random Selection.
Как сделать случайный выбор в Excel с помощью инструмента «Случайный порядок»
Теперь, когда вы знаете несколько формул для получения случайной выборки в Excel, давайте посмотрим, как можно добиться того же результата одним щелчком мыши.
С нашим Ultimate Suite, установленным в вашем Excel, вы можете делать следующее:
- Выберите любую ячейку в таблице.
- Перейдите на вкладку Инструменты Ablebits в группу Утилиты и нажмите Рандомизировать > Выбрать случайным образом:
- На панели надстройки выберите, что выбрать: случайные строки, случайные столбцы или случайные ячейки.
- Укажите число или процент для желаемого размера выборки.
- Нажмите кнопку "Выбрать". Готово!
- Диапазон ввода. Введите ячейки, содержащие интересующие вас данные из вашей совокупности. Обратите внимание, что введенные здесь ячейки могут содержать только числовые значения.
- Ярлыки. Если вы выделили заголовок в выбранном диапазоне ввода, убедитесь, что вы отметили параметр ярлыков.
- Выходной диапазон — выборка будет помещена на текущий лист. Вы можете использовать поле, чтобы указать конкретную ячейку
- Новый слой рабочего листа. Образец будет помещен на отдельный рабочий лист. Используйте поле, чтобы дать новому листу имя
- Новая рабочая книга. Последний вариант поместит образец в новый файл Excel.
Например, вот как мы можем выбрать 5 случайных строк из нашего выборочного набора данных:
И через секунду вы получите случайный выбор:
Теперь вы можете нажать Ctrl + C, чтобы скопировать случайный образец, а затем нажать Ctrl + V, чтобы вставить его в нужное место на том же или другом листе.
Если вы хотите протестировать инструмент рандомизации на своих листах, загрузите пробную версию Ultimate Suite для Excel:
В этом руководстве я покажу вам два совершенно разных способа выполнения случайной выборки в Microsoft Excel.
Что такое случайная выборка?
Случайная выборка – это метод отбора, при котором вы берете совокупность и случайным образом выбираете нужное число субъектов, чтобы создать меньшую группу, известную как выборка.
Во время случайной выборки каждый субъект имеет равные шансы попасть в выборку.
Случайная выборка в Excel: метод 1
Для первого метода я выполню случайную выборку с помощью надстройки Analysis ToolPak для Excel. Пакет инструментов анализа – это дополнительный набор параметров, которые помогут вам легко выполнять определенные статистические функции в Excel.
Шаг 1. Активируйте пакет инструментов анализа
Чтобы убедиться, что пакет инструментов анализа активирован правильно, перейдите в раздел Файл>Параметры.
Затем выберите Надстройки.
Внизу, где написано "Управление", убедитесь, что вы выбрали Надстройки Excel и нажмите Перейти.
Убедитесь, что установлен флажок Пакет инструментов анализа, и нажмите кнопку ОК.
Теперь, когда вы выбираете вкладку Данные вверху, вы должны увидеть кнопку Анализ данных.
Шаг 2. Используйте пакет инструментов анализа для выполнения случайной выборки
Теперь мы готовы выполнить выборку. Для этого нажмите кнопку Анализ данных.
Затем выберите из списка Выборка и нажмите ОК.
Для параметров Ввод выполните следующие действия.
Переходя к Метод выборки, вы хотите выбрать Случайный.
Количество выборок — это просто количество случайно выбранных значений из вашей совокупности. Например, если ввести 10, будут возвращены 10 случайных значений.
Наконец, есть три варианта, из которых можно выбрать, где вы хотите ввести случайную выборку.
Вот как выглядит окно выборки для моего примера.
Наконец, нажмите ОК, чтобы запустить выборку.
Теперь вы должны увидеть случайно выбранные значения из исходной совокупности.
Случайная выборка в Excel: метод 2
Недостаток метода 1 заключается в том, что вы можете выполнять случайную выборку только для ячеек, содержащих числовые значения. Для метода 2 я покажу вам, как использовать функцию RAND, которая позволит вам случайным образом выбирать разные строки. Поэтому вы можете иметь смесь ячеек, содержащих числа, а также символы в случайной выборке.
Шаг 1. Используйте функцию RAND для генерации случайных чисел
Функция RAND создает случайное число от 0 до 1, и теперь вы поймете, почему это полезно для случайной выборки.
В пустом столбце введите функцию RAND в каждую ячейку, расположенную рядом с вашими данными о населении.
Обратите внимание, что в скобках ничего не указано.
Теперь будет отображаться случайное число. Обратите внимание: каждый раз, когда вы что-то делаете в Excel, это число будет пересчитываться. Это не большая проблема, и вскоре я покажу вам, как с этим справиться.
Далее повторите этот процесс для всех ячеек, смежных с вашими данными о населении.
Чтобы случайные значения не обновлялись каждый раз, когда вы что-то делаете на листе, просто выделите все случайные числа, а затем скопируйте их (щелкните правой кнопкой мыши>Копировать).
Затем в те же ячейки вставьте значения (щелкните правой кнопкой мыши>Специальная вставка>Вставить значения).
Теперь случайная функция удалена из ячеек, остались только случайные числа. Это означает, что значения больше не будут обновляться при внесении изменений в наш лист.
Шаг 2. Отсортируйте ячейки по случайным числам
Далее выделите все столбцы, содержащие случайные числа и данные о населении.
Затем перейдите на Главную>Сортировка и фильтр>Пользовательская сортировка.
В новом окне выберите сортировку по столбцу, содержащему случайные числа, и упорядочите их от меньшего к большему, затем нажмите ОК.
Теперь данные отсортированы по случайным числам.
Шаг 3. Выберите случайную выборку
Решите, сколько значений вы хотите получить в своей случайной выборке. Затем возьмите это количество значений из верхней части листа.
Например, я хочу создать образец, содержащий 10 значений. Я могу просто выделить первые 10 строк в своей копии данных о населении и вставить их в новую часть этого листа, новый лист или новый файл Excel.
Случайная выборка в Excel: заключительные слова
Теперь вы должны знать два разных способа выполнения случайной выборки в Excel.
Первый метод использует пакет инструментов анализа для выбора нужного количества случайно выбранных ячеек, содержащих числа.
Во втором методе используется функция RAND для рандомизации субъектов в популяции, которую затем можно скопировать, например, на другой лист.
Используемая версия Microsoft Excel: 365 ProPlus
Стивен является основателем Top Tip Bio. В настоящее время он является медицинским писателем и бывшим научным сотрудником с докторской степенью. Понравилось обучение? Тогда дайте мне знать, оставив комментарий ниже, или подумайте о том, чтобы угостить меня кофе.
Часто может потребоваться выбрать случайную выборку из набора данных в Excel. К счастью, это легко сделать с помощью функции RAND(), которая генерирует случайное число от 0 до 1.
В этом руководстве представлен пошаговый пример использования этой функции для выбора случайной выборки в Excel.
Шаг 1. Создайте набор данных
Сначала введите значения вашего набора данных в один столбец. В этом примере мы введем 20 различных значений в столбец A:
Шаг 2. Создайте список случайных значений
Далее введите =СЛУЧИС() в ячейку B2. Это создает случайное значение от 0 до 1.
Затем наведите указатель мыши на правый нижний угол ячейки B2, пока не появится крошечный +, а затем дважды щелкните. Это скопирует формулу =СЛУЧИС() во все оставшиеся ячейки в столбце B:
Шаг 3. Скопируйте и вставьте случайные значения
Далее выделите значения в столбце B и нажмите Ctrl + C. Это скопирует все значения. Затем щелкните правой кнопкой мыши ячейку C2 и выберите "Вставить значения".
Обратите внимание, что после этого значения в столбце B могут измениться, но не беспокойтесь об этом.
Наконец, выделите значения в столбце C и перетащите их, чтобы заменить значения в столбце B.
Появится всплывающее окно с сообщением «Здесь уже есть данные. Вы хотите заменить это?" Нажмите "ОК".
Шаг 4. Сортировка по случайным значениям
Далее выделите ячейки A1:B21. Затем щелкните вкладку «Данные» на верхней ленте, затем нажмите «Сортировка» в разделе «Сортировка и фильтр». Отсортируйте значения по случайному числу от меньшего к большему.
Значения будут отсортированы на основе случайного числа, от меньшего к большему:
Шаг 5. Выберите случайную выборку
Наконец, выберите первые n строк для случайной выборки. Например, если вам нужна случайная выборка размером 5, выберите первые 5 необработанных значений данных, которые будут включены в вашу выборку.
В этом примере наша случайная выборка будет включать первые 5 значений: 8, 18, 26, 24, 24.
Введите «=Rand()» без кавычек в ячейке A1 или в любой другой ячейке, соответствующей первому значению данных.
Щелкните эту ячейку еще раз и дважды щелкните маленький черный маркер заполнения в правом нижнем углу ячейки. При этом формула копируется вниз по всему столбцу, пока не будет достигнута последняя запись данных. Все ячейки в столбце A остаются выделенными.
Нажмите «Ctrl-C», чтобы скопировать ячейки, щелкните правой кнопкой мыши выбранные данные и нажмите «V». Эта процедура удаляет исходную формулу Rand(), но оставляет случайные значения, что предотвращает их изменение при каждом обновлении формулы.
Нажмите любое значение в столбце A, выберите вкладку «Данные» и выберите параметр «AZ» в разделе «Сортировка и фильтрация». На этом шаге все столбцы сортируются в соответствии со значениями в столбце A.
Нажмите номер строки первой записи даты, удерживайте клавишу «Shift» и щелкните номер строки, соответствующий последнему нужному образцу. Вычислите этот номер строки, добавив размер выборки к номеру первой строки и вычтя 1. Например, чтобы выбрать 50 записей, нажмите вторую строку, удерживайте клавишу «Shift» и щелкните строку «51».
Нажмите «Ctrl-C», чтобы скопировать данные, откройте новую книгу или лист Excel и нажмите «Ctrl-V», чтобы скопировать образец.
Читайте также: