Как рандомизировать строки в Excel

Обновлено: 21.11.2024

Организация — это хорошо, но иногда случайность помогает. Это верно для жизни и Excel, и мы здесь для Excel. Excel довольно хорошо справляется со списками сортировки; в алфавитном порядке, в хронологическом порядке, в порядке возрастания и убывания, но прямая рандомизация (почти оксюморон) еще впереди. Хотя у нас есть функции, которые помогут нам в этом, и это то, что мы немного рассмотрим.

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

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

Для нашего примера, который будет использоваться в этом руководстве, мы рандомизируем следующий список имен, расположенных в алфавитном порядке:

Давайте рассмотрим варианты и посмотрим, что мы можем сделать для рандомизации списков.

Оглавление

Использование функции RAND и функции сортировки для рандомизации списка

Мы не можем сортировать имена случайным образом, но можем расположить случайные числа. Здесь мы будем использовать функцию RAND. Функция RAND возвращает случайное число больше 0 и меньше 1. Мы получим список случайных чисел, выстроенных в ряд с именами, а затем отсортируем их с помощью инструмента Сортировка и фильтр, который затем будет иметь имена перепутаны случайным образом. Позвольте нам показать вам шаги.

Введите функцию СЛЧИС против имени в новом столбце. Вот формула, которую вам нужно ввести:

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

Выберите любую ячейку, содержащую число. Перейдите на вкладку Главная в группу >Редактирование, выберите параметр Сортировка и фильтрация. В раскрывающемся списке выберите выберите любой из двух верхних вариантов. Мы выберем первый вариант — Сортировать от меньшего к большему.

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

Теперь, когда у нас есть готовый случайный список, мы можем выбрать столбец с помощью функции RAND и удалить его.

Примечания:

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

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

Преимущество этого метода заключается в том, что вы сохраняете исходный список и рандомизируете его вместо создания нового списка на основе исходного в качестве основы.

Использование функции RANDBETWEEN и функции сортировки для рандомизации списка

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

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

В столбце рядом с именами введите следующую формулу для функции СЛУЧМЕЖДУ:

В этой формуле мы используем функцию СЛУЧМЕЖДУ, чтобы получить случайное число от 1 до 10.
Выберите любую ячейку из диапазона и перейдите на вкладку Главная >Редактирование к группе >Сортировка и фильтр >Сортировка по наименьшему размеру к наибольшему (подойдет любой из двух вариантов).

Имена будут переставлены случайным образом. Числа будут пересчитаны (СЛУЧМЕЖДУ тоже является изменчивой функцией), что не является проблемой, так как вы скоро удалите числа.

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

Использование функций RANDARRAY, SORTBY и ROWS для рандомизации списка

Используя базовый список, можно создать рандомизированный список с помощью функций RANDARRAY, SORTBY и ROWS. Функция RANDARRAY возвращает массив случайных чисел. Функция SORTBY сортирует диапазон на основе значений в соответствующем диапазоне. Функция ROWS возвращает количество строк в ссылке.

Для формулы, которую мы будем использовать, функция СТРОКИ вернет количество строк для случайного рандомизации. Затем функция SORTBY отсортирует список в соответствии с рандомизированными строками.

Посмотрим, как все это выглядит на листе. Введите следующую формулу в отдельный столбец:

Функция ROW возвращает количество строк в B3:B12, равное 10. Функция RANDARRAY берет это число и возвращает массив из 10 случайных чисел для всех 10 строк. Затем функция SORTBY сортирует B3:B12 в соответствии со случайными числами, которые случайным образом корректируют B3:12 в новый список.

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

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

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

Использование функций INDEX и RANDBETWEEN для рандомизации списка

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

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

Давайте приступим к простыням. Введите следующую формулу в отдельный столбец:

Эту формулу можно использовать для одного выбора:

Или вы можете перетащить формулу рандомизированного списка:

Функция СЛУЧМЕЖДУ случайным образом возвращает число от 1 до 10. В первом случае генерируется случайное число 4. Этот результат передается функции ИНДЕКС вместо номера строки. Это означает, что функция ИНДЕКС настроена на возврат четвертого значения из диапазона B3:B12, которое в первом случае равно «Элтон Шон».

«1» в конце формулы здесь на самом деле не требуется, но полезно, если диапазон, введенный в формулу, относится к таблице (несколько столбцов), а не к списку (один столбец). Это число означает номер столбца, из которого должен быть выбран результат.

Формула была перетащена, чтобы заполнить оставшуюся часть столбца.

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

Примечания:

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

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

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

Использование функций CHOOSE и RANDBETWEEN для рандомизации списка

Подобно описанному выше методу, мы также можем создать рандомизированный список, используя функции ВЫБОР и СЛУЧМЕЖДУ. Наведение на этот метод; результаты (имена в данном случае), скорее всего, будут повторяться.

Если все в порядке, продолжайте читать.

Функция ВЫБОР выбирает значение из списка значений на основе порядкового номера. Функция СЛУЧМЕЖДУ возвращает случайное число из указанного диапазона. В нашей формуле функция ВЫБОР будет выбирать имя из списка имен в соответствии с числом, которое случайным образом указывает СЛУЧМЕЖДУ.

Пора работать с листами. Введите следующую формулу в отдельный столбец:

Эту формулу можно использовать для одного выбора:

Или вы можете перетащить формулу рандомизированного списка:

Функция СЛУЧМЕЖДУ случайным образом возвращает число от 1 до 10. В первом случае генерируется случайное число 3. Затем функция ВЫБОР выбирает третье значение из списка значений, переданных в функцию. Конечным результатом будет «Брюс Гримм Дин».

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

Примечания:

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

Здесь мы подходим к не совсем случайному завершению этого урока. Мы надеемся, что достаточно изучили способы рандомизации списка в Excel, чтобы дать вам хорошее представление о том, что работает в вашей ситуации с Excel. Мы вернемся с дополнительными инструкциями и всем обо всем в Excel. Не стесняйтесь возвращаться для решения других проблем!

Подпишитесь и станьте частью нашей семьи, насчитывающей более 15 000 человек!

Теперь подпишитесь на Excel Trick и получите бесплатную копию нашей электронной книги "200+ ярлыков Excel" (формат для печати), которая резко повысит вашу продуктивность.

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

Как рандомизировать список в Excel с помощью формулы

Хотя в Excel нет встроенной функции для выполнения случайной сортировки, есть функция для генерации случайных чисел (функция Excel RAND), и мы собираемся ее использовать.

Предполагая, что у вас есть список имен в столбце A, выполните следующие действия, чтобы рандомизировать ваш список:

В первой ячейке вставленного столбца введите формулу RAND: =RAND()
Скопируйте формулу вниз по столбцу. Самый быстрый способ сделать это — дважды щелкнуть маркер заполнения:
Отсортируйте столбец, заполненный случайными числами, в порядке возрастания (сортировка по убыванию приведет к перемещению заголовков столбцов в конец таблицы, чего вам точно не нужно). Итак, выберите любое число в столбце B, перейдите на вкладку Главная в группу >Редактирование и нажмите Сортировка и фильтр >Сортировать по наибольшему Самый маленький.

Или можно перейти на вкладку Данные в группу >Сортировка и фильтр и нажать кнопку ZA .

В любом случае Excel автоматически расширяет выборку и также сортирует имена в столбце A:

Excel RAND — это изменчивая функция, означающая, что новые случайные числа генерируются каждый раз, когда рабочий лист пересчитывается. Итак, если вы недовольны рандомизацией списка, продолжайте нажимать кнопку сортировки, пока не получите желаемый результат.
Чтобы случайные числа не пересчитывались при каждом изменении, которое вы вносите в рабочий лист, скопируйте случайные числа, а затем вставьте их как значения с помощью функции "Специальная вставка". Или просто удалите столбец с формулой RAND, если он вам больше не нужен.
Тот же подход можно использовать для рандомизации нескольких столбцов. Для этого разместите два или более столбца рядом, чтобы столбцы были смежными, а затем выполните описанные выше действия.

Как перемешать данные в Excel с помощью Ultimate Suite

Если у вас нет времени возиться с формулами, воспользуйтесь инструментом Перемешать ячейки, входящим в состав Ultimate Suite for Excel, чтобы ускорить случайную сортировку.

Перейдите на вкладку Инструменты Ablebits в группу Утилиты, нажмите кнопку Случайный порядок, а затем нажмите Перемешать ячейки.
Панель Перемешать появится в левой части книги. Вы выбираете диапазон, в котором хотите перетасовать данные, а затем выбираете один из следующих вариантов:
- Ячейки в каждой строке — перемешивание ячеек в каждой строке по отдельности.
- Ячейки в каждом столбце — случайная сортировка ячеек в каждом столбце.
- Вся строка – перемешать строки в выбранном диапазоне.
- Вся колонка — случайный порядок столбцов в диапазоне.
- Все ячейки в диапазоне — рандомизация всех ячеек в выбранном диапазоне.
Нажмите кнопку "Перемешать".

В этом примере нам нужно перетасовать ячейки в столбце A, поэтому мы выбираем третий вариант:

И вуаля, наш список имен мгновенно рандомизируется:

Если вам интересно попробовать это и изучить множество других интересных функций, включенных в Ultimate Suite for Excel, вы можете загрузить 14-дневную пробную версию.

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

Предположим, у вас есть диапазон ячеек, и теперь вы хотите перетасовать строки в случайном порядке, как показано на снимке экрана ниже, как вы можете быстро и случайным образом перетасовать их в Excel?

Случайное перемешивание строк/столбцов/диапазона ячеек с помощью Kutools for Excel

Перемешать значения строк/столбцов с помощью формулы

<р>2. Затем нажмите Ctrl+Enter. Теперь вы можете видеть, что отображается список случайных данных.

<р>3. Теперь вы можете перейти на вкладку «Данные» и выбрать «Сортировать от наименьшего к наибольшему» или «Сортировать от наибольшего к наименьшему», как вам нужно. Смотрите скриншот:

<р>4. Затем появится всплывающее диалоговое окно, и установите флажок «Расширить выбор». И нажмите Сортировать.

До сих пор диапазон данных перемешивался по строкам случайным образом.

Затем вы можете удалить ячейки с формулами.

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

Случайное перемешивание строк/столбцов/диапазона ячеек с помощью Kutools for Excel

Если у вас установлен Kutools for Excel, есть утилита – Сортировка диапазона случайным образом может перетасовать ваши данные по всей строке, всему столбцу или всем ячейкам диапазона, а также выбрать случайные ячейки, случайные столбцы или случайные строки из диапазона .

После установки Kutools for Excel сделайте следующее: (Бесплатно загрузите Kutools for Excel сейчас!)

<р>2. Затем в диалоговом окне «Сортировка/выбор диапазона случайным образом» на вкладке «Сортировка» выберите нужный вариант.

<р>3. Затем нажмите "ОК" или "Применить".

Сортировать по целым строкам

Сортировать по столбцам целиком

Сортировка ячеек в диапазоне

Сортировка ячеек в каждой строке

Сортировка ячеек в каждом столбце

С помощью утилиты Kutools for Excel's Sort Range Randomly вы можете случайным образом выбирать ячейки в диапазоне.

В этой статье рассказывается, как рандомизировать (перетасовать) список в Excel. Например, мы хотим рандомизировать список в столбце А ниже.

<р>3. Щелкните любое число в списке в столбце B.

Результат. Случайный список в столбце A (отсортированный по указанным выше случайным числам).

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

Если у вас Excel 365 или Excel 2021, используйте RANDARRAY, SORTBY и ROWS, чтобы рандомизировать список в Excel. Следующая формула просто потрясающая.

Примечание: функция СЛУЧАЙ, введенная в ячейку B1, заполняет несколько ячеек. Ух ты! Такое поведение в Excel 365/2021 называется сбросом.

<р>7. Вложите функцию RANDARRAY внутрь функции SORTBY.

<р>8. Если у вас есть длинный список, скажем, 20 имен, измените значение 8 на 20 в формуле, показанной выше, или, что еще лучше, используйте функцию СТРОКИ.

Читайте также: