Давайте рассмотрим несколько быстрых способов случайного выбора имен, чисел или других данных. Вы также узнаете, как получить случайную выборку без дубликатов и как случайным образом выбрать указанное число или указанный процент ячеек, строк или столбцов.
Независимо от того, проводите ли вы исследование рынка для запуска нового продукта или оцениваете результаты своей маркетинговой кампании, важно использовать объективную выборку данных для анализа. И самый простой способ добиться этого — получить случайный выбор в Excel.
Что такое случайная выборка?
Прежде чем обсуждать методы выборки, давайте предоставим некоторую основную информацию о случайном выборе и о том, когда его использовать.
В теории вероятностей и статистике случайная выборка — это подмножество данных, взятых из более крупного набора данных, также известного как совокупность. Каждый элемент случайной выборки выбирается совершенно случайным образом и имеет одинаковую вероятность быть выбранным. Зачем тебе это? В основном, чтобы иметь объективный взгляд на все население.
Например, предположим, вы хотите провести небольшой опрос своих клиентов. Очевидно, было бы неразумно рассылать опрос каждому человеку в вашей большой базе данных. Итак, кого вы будете беспокоить по поводу своих вопросов? Будет ли это 100 новых клиентов, или первые 100 клиентов, перечисленные в алфавитном порядке, или 100 человек с самыми короткими именами? Ни один из этих подходов не отвечает вашим потребностям, поскольку они по своей сути субъективны. Чтобы получить объективную выборку, в которой все клиенты имеют равные возможности для выбора, сделайте случайный выбор, используя один из методов, описанных ниже.
Случайный выбор значения из списка
В Excel нет встроенной функции для случайного выбора ячеек, но вы можете использовать одну из функций для генерации случайных чисел в качестве временного решения. Их, наверное, нельзя назвать простыми и интуитивно понятными формулами, но они работают.
Предположим, у нас есть список имен в ячейках A2: A15, и мы хотим выбрать одно из них. Это можно сделать по одной из следующих формул:
= ИНДЕКС ($ A $ 2: $ A $ 15, СЛУЧАЙНОЕ МЕЖДУ (1, СЧЁТ ($ A $ 2: $ A $ 15)))
или
= ИНДЕКС ($ A $ 2: $ A $ 15; СЛУЧАЙНОЕ МЕЖДУ (1; СТРОКА ($ A $ 2: $ A $ 15)))
это все! Средство выбора случайных имен для Excel настроено и готово к использованию:
Примечание. Обратите внимание, что RANDBETWEEN — непостоянная функция, а это означает, что она будет пересчитываться каждый раз, когда вы вносите изменения в свой рабочий лист. В результате ваш случайный выбор из списка также будет постоянно меняться. Чтобы этого не произошло, вы можете скопировать извлеченное имя и вставить его как значение в другую ячейку (Специальная вставка> Значения).
Конечно, эти формулы могут не только выбирать случайные имена, но и случайные числа, даты или любые другие данные из ячеек.
Как работают эти формулы
Мы используем функцию ИНДЕКС для извлечения значения из списка на основе случайного порядкового номера, возвращаемого RANDBETWEEN.
То есть функция RANDBETWEEN генерирует случайное целое число между двумя указанными значениями. Для нижнего порога укажите число 1. Для верхнего порога используйте COUNT () или ROWS (), чтобы получить общее количество ячеек данных. В результате RANDBETWEEN () возвращает случайный номер ячейки в наборе данных. Этот номер передается в функцию ИНДЕКС, указывая, какую ячейку выбрать. Второй аргумент (номер столбца) можно не указывать, так как у нас он только один.
Примечание. Этот метод полезен для выбора случайного значения из списка. Если ваш выбор должен включать несколько результатов, приведенная выше формула может возвращать несколько вхождений одного и того же значения, поскольку функция СЛУЧМЕЖДУ не является дублирующим доказательством. Это особенно верно при выборе относительно большой выборки из относительно небольшого списка.
Эту проблему также можно решить с помощью формулы
= ИНДЕКС ($ A $ 2: $ A $ 15, СЛУЧАЙНОЕ МЕЖДУ (1, СЧЁТ ($ A $ 2: $ A $ 15)))
Функция RANDBETWEEN () случайным образом выбирает позицию в списке, из которой необходимо взять значение (для этой функции вероятность выбора любой строки одинакова).
Если вы скопируете эту формулу в один столбец, из списка будет выбрано несколько значений. Однако есть подводный камень: есть возможность выбрать несколько одинаковых значений, то есть получить дубликаты. Особенно, если ваш список относительно невелик.
В следующем примере показано, как сделать случайный выбор в Excel без дубликатов.
Случайный выбор без дубликатов.
В Excel есть несколько способов выбрать случайные данные без дубликатов. Например, вы должны использовать функцию RANDOM, чтобы присвоить случайное число каждой ячейке, а затем выбрать несколько ячеек, используя формулу индекса ранга.
Со списком имен в ячейках A2: A16 выполните следующие действия, чтобы извлечь несколько имен:
- Введите формулу случайного числа в B2 и скопируйте ее в столбец:
= СЛУЧАЙНЫЙ() - Введите следующую формулу в C2, чтобы извлечь случайное значение из столбца A:
= ИНДЕКС ($ A $ 2: $ A $ 16, РАНГ (B2; $ B $ 2: $ B $ 16))
- Скопируйте приведенную выше формулу в столько ячеек, сколько есть случайных значений, которые вы хотите выбрать. В нашем примере мы копируем формулу в четыре другие ячейки (C2: C6).
это все! Рисуются пять имен без повтора:
Как работает эта формула
Как и в предыдущем примере, мы используем функцию ИНДЕКС для извлечения значения из столбца A на основе номера позиции. Для этого требуются две разные функции:
- Формула RAND () заполняет столбец B случайными числами.
- Функция RANK () возвращает ранг случайного числа из столбца B и самой строки. Например, RANK (B2; $ B $ 2: $ B $ 16) получает ранг числа из B2 (0,188906401). B2 сравнивается со всеми числами в диапазоне $ B $ 2: $ B $ 16. Он занимает 13-е место по размеру. При копировании в C3 его ссылка B2 изменяется на B3 и возвращает ранг числа из B3 и так далее.
- Число, возвращаемое функцией RANK (), передается в функцию INDEX, а затем выбирает значение из соответствующей позиции. Это означает, что тринадцатое значение в порядке из диапазона $ A $ 2: $ A $ 16 должно быть введено в C3.
Предупреждение! Как показано на скриншоте выше, наша случайная выборка Excel содержит только уникальные значения. Теоретически вероятность появления дубликатов здесь очень мала, но все же существует. Вот почему: в очень большом наборе данных RAND () может генерировать повторяющиеся числа, а RANK () вернет то же пространство для этих чисел. У меня лично ни разу не было дубликатов при тестировании, но теоретически такая возможность есть.
Если вы ищете конкретную надежную формулу для случайного выбора только с уникальными значениями, используйте комбинацию RANK + COUNTIF вместо просто RANK.
Полная формула немного громоздка, но на 100% не содержит дубликатов:
= ИНДЕКС ($ A $ 2: $ A $ 16, RANK.RV (B2; $ B $ 2: $ B $ 16) + COUNTIF ($ B $ 2: B2; B2) -1,1)
Примечания:
- Как и RANDBETWEEN (), функция RANDBET () в Excel также воссоздает новые числа каждый раз, когда рабочий лист пересчитывается, что приводит к изменению набора выбора. Чтобы сохранить результат без изменений, скопируйте и вставьте его в другое место как значение (Специальная вставка> Значения).
- Если одно и то же имя (число, дата или любое другое значение) встречается в исходном наборе данных более одного раза, результат также может содержать несколько вхождений одного и того же значения.
А вот еще одно решение, аналогичное предыдущему, в котором используется функция НАИМЕНЬШИЙ().
Рядом с столбцом значений для выбора добавьте столбец случайных чисел. Как обычно, мы используем функцию RAND () для this().
Допустим, нам нужно выбрать 5 имен. Для этого в столбце C напишите цифры от 1 до 5.
Далее воспользуемся формулой
= ИНДЕКС ($ A $ 2: $ A $ 15; ПОИСК (МАЛЕНЬКИЙ ($ B $ 2: $ B $ 15; C2); $ B $ 2: $ B $ 15,0))
Объясняем, как это работает. Используя функцию НАИМЕНЬШИЙ (), выберите наименьшее значение из сгенерированных чисел. Функция ПОИСКПОЗ помогает нам определить его позицию в списке. А затем, используя INDEX (), получите имя в этом месте.
При копировании столбца следующая формула будет принимать второе наименьшее значение из списка случайных чисел и так далее.
Но можно сэкономить время и не создавать столбец с порядковыми номерами. Обычно в качестве счетчика () используется функция СТРОКА().
= ИНДЕКС ($ A $ 2: $ A $ 15, ПОИСК (МАЛЕНЬКИЙ ($ B $ 2: $ B $ 15; СТРОКА (A1)); $ B $ 2: $ B $ 15,0))
При копировании СТРОКА (A1) изменится на СТРОКА (A2) и соответственно вернет число 2. И так далее. В остальном все работает точно так же.
Думаю, вы понимаете, что вместо SMALL () вполне можно использовать LARGE (). Дело вкуса .
Как выбрать случайные строки в Excel
Если ваша электронная таблица содержит более одного столбца данных, вы можете получить набор таких значений: присвоить номер каждой строке, отсортировать эти числа и выбрать столько строк, сколько хотите. Подробные инструкции приведены ниже.
- Вставьте новый столбец справа или слева от таблицы (столбец D в этом примере).
- В первой ячейке вставленного столбца, исключая заголовки столбцов, введите формулу = СЛУЧАЙ()
- Дважды щелкните маркер заполнения, чтобы скопировать формулу в столбец. В результате в каждой строке будет записано случайное число.
- Отсортируйте эти числа от наибольшего к наименьшему (сортировка в порядке возрастания перемещает заголовки столбцов в нижнюю часть таблицы, поэтому обязательно отсортируйте их в порядке убывания). Для этого перейдите на вкладку «Данные» в группе «Сортировка и фильтр» и нажмите кнопку «Сортировка». Excel автоматически расширит выбор и попросит вас выбрать столбец и отсортировать.
Для экономии времени можно использовать кнопки сортировки AY или YA, но курсор необходимо поместить на столбец с формулой RAND (). Заголовок столбца рекомендуется писать на русском языке, иначе есть возможность перейти в конец таблицы.
Теперь остается нажать ОК, и строки таблицы будут переупорядочены и перемешаны в случайном порядке.
Пусть вас не смущает то, что после сортировки по столбцу D вы видите в нем номера совершенно не по порядку. Дело в том, что сортировка изменяет порядок строк, и поэтому все формулы RAND () пересчитываются заново. Но наша работа не в том, чтобы сортировать, а в случайном перемешивании строк, не так ли?
Если вас недостаточно устраивает рандомизация вашей таблицы, нажмите кнопку сортировки еще раз, чтобы перемешать ее снова.
- Наконец, выберите количество строк, необходимое для вашего выбора, скопируйте их в буфер обмена и вставьте в любое место.
Как случайно выбрать в Excel с помощью инструмента Randomize.
Теперь, когда вы знаете некоторые формулы для получения случайной выборки в Excel, давайте посмотрим, как можно получить тот же результат всего одним щелчком мыши.
Если в вашем Excel установлена надстройка Ultimate Suite, вы можете использовать инструмент случайной сортировки. Для этого нужно сделать следующее:
- Выберите любую ячейку в вашей таблице.
- Перейдите на вкладку AblebitsTools> Утилиты и нажмите кнопки «Случайно»> «Выбрать случайным образом):
Слева появится панель настроек, о которой стоит поговорить более подробно.
Объясняем, что скрывается за каждым из чисел.
- По умолчанию будет выбран весь диапазон данных. Выберите другой диапазон, выделив его мышью или введя адрес в это поле.
- Если вы изменили диапазон, снова выберите всю таблицу, щелкнув значок «Развернуть выделение» .
- Вы можете исключить строки заголовков из своего выбора, если они есть, установив флажок Моя таблица имеет 1 строку заголовка .
Комментарий. Если ваша таблица имеет более широкий заголовок, щелкните эту метку, введите фактическое количество строк, и они не будут выбраны и использованы.
- Откройте нужный вам раздел, чтобы случайным образом выбрать:
- определенное количество или процент строк.
- аналогично — столбцы на листе.
- некоторые или некоторые из ячеек в вашем диапазоне.
- Укажите количество или процент строк, столбцов или ячеек, которые вы хотите выбрать. Вы можете ввести необходимое значение в процентном или числовом поле соответственно. Или установите эти значения, щелкнув стрелки вверх и вниз рядом с полями.
- Пройдя через панель надстройки, выберите то, что вы хотите получить: случайные строки, столбцы или ячейки.
- Укажите число или процент для желаемого размера выборки.
- Нажмите кнопку Выбрать.
Например, вот как мы можем выбрать 5 случайных строк из нашего набора данных:
А через секунду перед вами случайный выбор:
Теперь вы можете нажать Ctrl + C, чтобы скопировать выделение, а затем использовать сочетание клавиш Ctrl + V, чтобы вставить его в любое место на том же или другом листе.
Точно так же вы можете случайным образом выбирать столбцы или отдельные ячейки. И скопируйте их в нужное место таким же образом. Никаких дополнительных формул или столбцов не требуется. Всего за пару кликов.
