Как сделать рандомайзер в excel
Как перемешать строки и сделать случайный выбор из списка
Рассмотрим несколько быстрых способов случайного выбора имен, чисел или любых других данных. Вы также узнаете, как получить случайную выборку без дубликатов и как произвольным образом выбрать указанное количество или определённый процент ячеек, строк или столбцов.
Что такое случайная выборка?
Прежде чем обсуждать методы выборки, давайте предоставим небольшую справочную информацию о случайном выборе и о том, когда целесообразно его использовать.
Например, вы хотите провести небольшой опрос среди своих клиентов. Очевидно, было бы неразумно рассылать анкету каждому человеку в вашей большой базе данных. Итак, кого вы побеспокоите своими вопросами? Будет ли это 100 новых клиентов, или первые 100 клиентов, перечисленные в алфавитном порядке, или 100 человек с самыми короткими именами? Ни один из этих подходов не соответствует вашим потребностям, потому что они изначально субъективны. Чтобы получить беспристрастную выборку, в которой у всех клиентов есть равные возможности быть выбранными, произведите случайный выбор, используя один из методов, описанных ниже.
Случайный выбор значения из списка
В Excel нет встроенной функции для случайного выбора ячеек, но вы можете использовать одну из функций для генерации случайных чисел в качестве временного решения. Их, наверное, нельзя назвать простыми и интуитивно понятными формулами, но они работают.
Предположим, у вас есть список имен в ячейках A2:A15, и вы хотите выбрать одно из них. Это можно сделать с помощью одной из следующих формул:
Вот и все! Средство выбора случайных имен для Excel настроено и готово к работе:
Примечание. Имейте в виду, что СЛУЧМЕЖДУ – это непостоянная функция, то есть она будет пересчитываться при каждом изменении, которое вы вносите в рабочий лист. В результате ваш случайный выбор из списка также будет постоянно меняться. Чтобы этого не произошло, вы можете скопировать извлеченное имя и вставить его как значение в другую ячейку (Специальная вставка > Значения).
Естественно, эти формулы могут не только выбирать случайные имена, но также и случайные числа, даты или любые другие данные из ячеек.
Как работают эти формулы
Мы используем функцию ИНДЕКС для извлечения значения из списка на основе случайного порядкового номера, возвращаемого СЛУЧМЕЖДУ.
То есть, функция СЛУЧМЕЖДУ генерирует случайное целое число между двумя указанными вами значениями. Для нижнего порога вы указываете число 1. Для верхнего — используете СЧЁТЗ() или ЧСТРОК(), чтобы получить общее количество ячеек с данными. В результате СЛУЧМЕЖДУ() возвращает случайный номер ячейки в вашем наборе данных. Этот номер передается в функцию ИНДЕКС, сообщая ей, какую по счёту ячейку выбрать. Второй аргумент (номер столбца) можно не указывать, поскольку он у нас только один.
Примечание. Этот метод хорошо подходит для выбора одного случайного значения из списка. Если ваш выбор должен включать несколько результатов, приведенная выше формула может возвращать несколько вхождений одного и того же значения, поскольку функция СЛУЧМЕЖДУ не защищена от дубликатов. Это особенно актуально, когда вы выбираете относительно большую выборку из относительно небольшого списка.
Эту задачу можно также решить с помощью формулы
Функция СЛУЧМЕЖДУ() случайным образом выбирает позицию списка, из которой нужно взять одно значение ( для этой функции вероятность выбрать любую строку одинакова).
Если скопировать эту формулу вниз по столбцу, то будет выбрано несколько значений из списка. Однако, есть подводный камень: есть вероятность выбора несколько одинаковых значений, то есть получения дубликатов. Особенно, если ваш список относительно небольшой.
В следующем примере показано, как сделать случайный выбор в Excel без дубликатов.
Случайный выбор без дубликатов.
Есть несколько способов выбрать случайные данные без дубликатов в Excel. К примеру, вы должны использовать функцию СЛЧИС, чтобы присвоить случайное число каждой ячейке, а затем выбрать несколько ячеек с помощью формулы индекса ранга.
Со списком имен в ячейках A2: A16 выполните следующие действия, чтобы извлечь несколько имен:
Вот и все! Извлекаются пять имен без повторов:
Как работает эта формула
Как и в предыдущем примере, вы используете функцию ИНДЕКС для извлечения значения из столбца A на основе номера позиции. Для этого требуются две разные функции:
Предупреждение! Как показано на скриншоте выше, наша случайная выборка Excel содержит только уникальные значения. Теоретически вероятность появления дубликатов здесь очень мала, но все же существует. И вот почему: в очень большом наборе данных СЛЧИС() может сгенерировать повторяющиеся числа, а РАНГ() будет возвращать одинаковое место для этих чисел. Лично у меня во время тестов ни разу не было дубликатов, но теоретически такая вероятность есть.
Если вы ищете железобетонно надёжную формулу для случайного выбора только с уникальными значениями, используйте комбинацию РАНГ + СЧЁТЕСЛИ вместо просто РАНГ.
Полная формула немного громоздка, но на 100% не содержит дубликатов:
Примечания:
А вот еще одно похожее на предыдущее решение, в котором используется функция НАИМЕНЬШИЙ().
Рядом со столбцом значений для выборки добавляем столбец случайных чисел. Как обычно, используем для этого функцию СЛЧИС().
Предположим, нам нужно выбрать 5 имён. Для этого в колонке С записываем цифры от 1 до 5.
Далее используем формулу
Поясним, как это работает. При помощи функции НАИМЕНЬШИЙ() выбираем самое маленькое значение из сгенерированных чисел. Функция ПОИСКПОЗ помогает нам определить его позицию в списке. И затем при помощи ИНДЕКС() извлекаем имя, находящееся в этой позиции.
При копировании вниз по столбцу следующая формула будет брать второе наименьшее значение из списка случайных чисел, и так далее.
Но можно сэкономить себе время и не создавать колонку с порядковыми номерами. В качестве счётчика принято использовать функцию СТРОКА().
При копировании вниз СТРОКА(A1) изменится на СТРОКА(А2) и соответственно возвратит номер 2. И так далее. В остальном всё работает точно так же.
Думаю, вы понимаете, что вместо НАИМЕНЬШИЙ() можно вполне использовать НАИБОЛЬШИЙ(). Дело вкуса 😊.
Как выбрать случайные строки в Excel
Если ваш рабочий лист содержит более одного столбца данных, вы можете получить набор значений следующим образом: назначить какое-то число каждой строке, отсортировать эти числа и выбрать необходимое количество строк. Подробные инструкции приведены ниже.
Вы можете для экономии времени использовать кнопки сортировки АЯ или ЯА, но при этом курсор нужно обязательно установить на столбец с формулой СЛЧИС(). Заголовок колонки желательно написать по-русски, иначе он имеет шансы переместиться в конец таблицы.
Теперь осталось нажать ОК, и строки таблицы будут пересортированы и произвольно перемешаны.
Пусть вас не смущает тот факт, что после сортировки по столбцу D вы видите в нем совершенно не упорядоченные числа. Дело в том, что сортировка меняет порядок строк, и тут же вновь происходит пересчёт всех формул СЛЧИС(). Но ведь наша задача не отсортировать, а произвольно перемешать строки, не так ли?
Если вас не совсем устраивает рандомизация вашей таблицы, нажмите кнопку сортировки еще раз, чтобы вновь перемешать её.
Как случайно выбрать в Excel с помощью инструмента Randomize.
Теперь, когда вы знаете несколько формул для получения случайной выборки в Excel, давайте посмотрим, как можно добиться того же результата буквально одним щелчком мыши.
Слева появится панель настройки, о которой следует сказать чуть подробнее.
Поясним, что скрывается за каждой из цифр.
Замечание. Если в вашей таблице более широкий заголовок, кликните по этой надписи, введите реальное количество строк, и они не будут выделены и использованы.
Например, вот как мы можем выбрать 5 случайных строк из нашего набора данных:
И через секунду вы получите случайный выбор:
Совершенно аналогичным образом вы можете сделать случайную выборку столбцов или отдельных ячеек. И точно так же скопировать их в нужное место. Не нужно ни формул, ни дополнительных столбцов. Всего пара кликов.
Если вы хотите протестировать инструмент «Случайный выбор» на своих данных, загрузите полнофункциональную пробную версию Ultimate Suite for Excel.
Если у вас еще остались вопросы относительно случайного выбора из списка либо перемешивания ячеек и строк в произвольном порядке — пишите в комментариях.