Как сделать рандом в экселе без повторений

Как сделать рандом в экселе без повторений

EXCEL: генерируем случайные числа

Изучим все функции для генерации случайных чисел.Сделаем генератор случайных чисел без повторений. Рассмотрим новые функции и способы решения задач в Office 365.

Скачайте файл с примером

«Я покажу Вам 3 способа удаления пустых строк из таблицы без потери данных.

Первый способ — с помощью фильтра. Это базовый способ.

Второй способ — использование функции СЧЁТЗ. Подходит для таблиц с большим количеством столбцов.

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

Шлычков Константин
Эксперт и преподаватель Excel

Функция СЛЧИС() (английский вариант RAND())- возвращает случайное вещественное число из диапазона от 0 до 1. Возвращаемое число будет равномерно распределено в диапазоне от 0 до 1. Функция не требует аргументов.

Значения в ячейке будут меняться после пересчета книги (например, после редактирования любой ячейки или нажатия клавиши F9 ). Чтобы избежать изменения чисел, их нужно скопировать и вставить как значение. Для этого выделите ячейку, нажмите CTRL+C . Далее нажмите правой кнопкой мыши и во всплывающем меню выберите вариант «Вставить как значение» .

Функция СЛУЧМЕЖДУ

Чтобы генерировать целое случайное число используйте функцию СЛУЧМЕЖДУ(). Аргументы функции — минимальное и максимальное значения.

Синтаксис функции СЛУЧМЕЖДУ

=СЛУЧМЕЖДУ(мин, макс)

Мин — наименьшее целое число, которое возвращает функция (обязательный параметр).

Макс — наибольшее целое число, которое возвращает функция (обязательный параметр)

Особенность функции СЛУЧМЕЖДУ в том, что случайные числа могут повторяться. В примере Вы видите столбец случайных чисел от 1 до 10. столбце 10 строк, но уникальных значений всего 6.

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

Далее с помощью фильтра (данные-> фильтр) сортируем столбец со случайными числами, например по возрастанию. В первом столбце числа сортируются в случайном порядке без повторения. Так решается задача, когда, например, из диапазона от 1 до 10 нужно выбрать 3 уникальных случайных значений. Это первый способ реализовать генератор случайных чисел.

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

Генератор случайных чисел

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

Функция ИНДЕКС возвращает значение ячейки, координаты, которой Вы указали. Как в морском бое. Если я укажу =ИНДЕКС(A:A;5), то она вернет значение ячейки A5.

Шлычков Константин
Эксперт и преподаватель Excel

В первом столбце A:A представлен список фильмов. Нужно создать генератор выбора фильмов. Идея создать генератор, который будет случайным образом выбирать строку и соответственно фильм. Пока что я вручную задал номер строки в ячейке B3. Функция выглядит следующим образом: =ИНДЕКС(A:A;5).

Список фильмов содержит 250 записей. Нужно сгенерировать целое случайное число от 1 до 250. Используя функцию =СЧЕТМЕЖДУ(1;250). Если длина списка будет меняться, то лучше использовать функцию СЧЕТЗ, которая считает количество значений в диапазоне. Функция генерации случайного числа будет выглядеть следующим образом. =СЛУЧМЕЖДУ(1;СЧЁТЗ(B:B)).
Эта функция должна стоять на месте второго параметра функции ИНДЕКС. Итоговая функция в ячейке B3 будет такой: =ИНДЕКС(A2:A191;СЛУЧМЕЖДУ(1;СЧЁТЗ(A:A)))

Как сделать рандом в экселе без повторений

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

Рандомизировать список с функциями
Рандомизируйте список с помощью Kutools в 2 клика
Рандомизировать список с помощью VBA

Видео: Рандомизация списка в Excel

Рандомизировать список с функциями

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

Рандомизировать список с помощью функции RAND

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

Шаг 1. Введите формулу RAND.

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

=RAND()

Шаг 2: Заполните формулу RAND в другие ячейки

Дважды щелкните маркер заполнения (маленький зеленый квадрат в правом нижнем углу) ячейки формулы, чтобы применить формулу к ячейкам ниже.

Шаг 3. Отсортируйте результаты RAND, чтобы перетасовать список.

1. Выбрать B2: B8, список, содержащий формулу RAND.

2. Нажмите на Сортировать и фильтровать > Сортировать от наименьшего к наибольшему в Редактирование группы на Главная меню.

3. Во всплывающем окне Предупреждение о сортировке диалоговое окно, выберите Расширить выбор, а затем нажмите Сортировать.

Теперь все готово! Список рандомизирован, как показано ниже.

Примечание: Ассоциация Excel функция СЛУЧАЙ является изменчивым: он пересчитывает свой результат при каждом изменении рабочего листа. Итак, числа в столбце B изменились мгновенно после сортировки. Если вы хотите снова перетасовать список, повторите вышеописанное шаг 3. В противном случае вы можете просто удалить столбец с помощью функции RAND.

Рандомизация списка с помощью функций RANDARRAY, SORTBY и ROWS (Excel 365/2021)

Если вы являетесь пользователем более новых версий Excel, например Excel для Microsoft 365 или Excel 2021, или Excel для Интернета, РАНДАРРАЙ, СОРТИРОВАТЬ ПО и РЯДЫ формула поможет вам быстро получить список, перемешанный в указанной вами позиции.

Шаг 1. Введите формулы RANDARRAY, SORTBY и ROWS.

Выберите пустую ячейку, в которой вы хотите получить рандомизированный результат, введите приведенную ниже формулу (обратите внимание, что вы должны изменить A2: A8 в приведенной ниже формуле в исходный список, который вы хотите рандомизировать) и нажмите Enter .

=SORTBY(A2:A8,RANDARRAY(ROWS(A2:A8)))

• #ПРОЛИВАТЬ ошибки возвращаются, когда диапазон разлива ( B2: B8 в этом случае), поскольку формула не пуста.
• Формула изменчива: она пересчитывает результат при каждом изменении рабочего листа. Если вы хотите снова перетасовать список, нажмите F9 .
• Результат формулы (рандомизированный список) недоступен для редактирования. Если вам нужно отредактировать рандомизированный список, выполните следующий шаг.

(Необязательно) Шаг 2. Скопируйте и вставьте результат формулы, чтобы сделать его редактируемым.

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

1. Выберите результат формулы и нажмите Ctrl + C чтобы скопировать его.

2. Щелкните правой кнопкой мыши ячейку, в которую вы вставите скопированный результат. (Вы также можете вставить значение в его исходное местоположение.)

3. Выберите Вставить только значения вариант из контекстного меню.

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

Рандомизируйте список в Excel с помощью Kutools в 2 клика

Если вы устали от формул и собираетесь воспользоваться помощью надстройки, Kutools for ExcelАвтора Сортировка / выбор диапазона случайным образом Функция может помочь вам выполнить случайную сортировку намного проще с большим количеством опций. Пожалуйста, следующим образом:

Сначала выберите список, который вы будете перемешивать. А затем выберите Кутулс > Диапазон > Сортировка / выбор диапазона случайным образом . Во всплывающем окне Сортировка / выбор диапазона случайным образом диалоговое окно, выберите Целые строки, а затем нажмите Ok.

• Чтобы попробовать Сортировка / выбор диапазона случайным образом функция, на вашем компьютере должен быть установлен Kutools for Excel. Если у вас не установлен Kutools, нажмите здесь, чтобы скачать и установить. Профессиональная надстройка Excel предлагает 30-дневную бесплатную пробную версию без ограничений.
• Чтобы отменить действие, нажмите Ctrl + Z .
• Чтобы перетасовать данные другими способами, ознакомьтесь с этим руководством: Быстро сортируйте или выбирайте ячейки, строки и столбцы случайным образом в Excel.

Рандомизировать список с помощью VBA

Если вы предпочитаете рандомизировать список методом VBA, сделайте следующее.

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

Шаг 1: Выберите список, который вы будете перемешивать

Шаг 2: Скопируйте код VBA в окно модуля

1. Нажмите Ctrl + F11 чтобы открыть редактор VBA, а затем щелкните Вставить > Модули чтобы открыть окно кода модуля.

2. Скопируйте и вставьте приведенный ниже код VBA в открывшееся окно модуля.

Код VBA: случайная сортировка списка

Sub RandomSort() 'Update by ExtendOffice Dim xRg As Range Dim xNum, xF, xI As Integer Dim xWSh, xAWSh As Worksheet On Error Resume Next Application.ScreenUpdating = False Application.DisplayAlerts = False Set xAWSh = Application.ActiveSheet Set xRg = ActiveWindow.RangeSelection Set xWSh = Worksheets.Add xNum = xRg.Count For xF = xNum To 1 Step -1 xI = WorksheetFunction.RandBetween(1, xF) xWSh.Range("A1").Value = xRg.Item(xI) xRg.Item(xI) = xRg.Item(xF) xRg.Item(xF) = xWSh.Range("A1") Next xWSh.Delete Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub

Шаг 3: Запустите код VBA

В окне кода нажмите F5 или нажмите кнопку для запуска этого кода.

Примечание: Если вы хотите снова рандомизировать список, повторите вышеописанное шаг 3.

Статьи по теме

• Как произвольно выбирать ячейки в Excel?
• Предположим, у вас есть столбец значений (A1: A15) на листе, и теперь вам нужно выбрать 5 случайных ячеек из них, как вы можете с этим справиться? В этой статье я покажу вам некоторые приемы случайного выбора ячеек в Excel.
• Как выбрать случайные данные из списка без дубликатов?
• В этой статье я расскажу о том, как выбрать случайные ячейки из списка без повторяющихся значений. Следующие два метода могут помочь вам справиться с этой задачей как можно быстрее.
• Как случайным образом выбирать ячейки на основе критериев в Excel?
• Например, вам нужно случайным образом выбрать некоторые данные на основе одного или двух критериев из указанного диапазона, как вы могли бы решить это в Excel? В этой статье вы найдете несколько решений.
• Как случайным образом заполнить значения из списка данных в Excel?
• Например, у вас есть список имен, и теперь вам нужно случайным образом выбрать несколько имен и заполнить заданные ячейки, как с этим бороться? Здесь я расскажу о нескольких методах случайного заполнения значений из списка данных в Excel.
• Дополнительные советы и рекомендации по Excel.

Лучшие инструменты для офисной работы

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

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

• Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
• Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
• Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

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

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

Поэтому мы пойдем другим путем.

Во всех версиях Excel есть функция РАНГ (RANG) , предназначенная для ранжирования или, другими словами, определения топовой позиции числа в наборе. Для самого большого числа в списке ранг=1, второе в топе имеет ранг=2 и т.д.

Введем в ячейку А2 функцию СЛЧИС (RAND) без аргументов и скопируем формулу вниз на 10 ячеек. Эта функция сгенерирует нам набор из 10 случайных дробных чисел от 0 до 1:

В соседний столбец введем функцию РАНГ, чтобы определить позицию в рейтинге для каждого полученного случайного числа:

Получим в столбце В то, что хотели — любое нужное количество неповторяющихся случайных целых чисел от 1 до 10.

Чисто теоретически, может возникнуть ситуация, когда СЛЧИС выдаст нам два одинаковых случайных числа в столбце А, их ранги совпадут и мы получим повтор в столбце В. Однако, вероятность такого сценария крайне мала, учитывая тот факт, что точность составляет 15 знаков после запятой.

Способ 2. Сложный

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

Введите в ячейку А2 следующую формулу, нажмите в конце Ctrl+Shift+Enter (чтобы ввести ее как формулу массива!) и скопируйте формулу вниз на требуемое количество ячеек:

Способ 3. Макрос

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

Ссылки по теме

• Как подсчитать количество уникальных значений в диапазоне
• Случайная выборка элементов из списка

Как сделать рандом в экселе без повторений

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

Создайте уникальное случайное число с помощью Kutools for Excel’s Вставить случайные данные (легко!)

Easily Insert Random Data without duplicates in a range of cells

Создавайте уникальные случайные числа с помощью формул

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

1． Предположим, вам нужно сгенерировать случайные числа без дубликатов в столбце A и столбце B, теперь выберите ячейку E1 и введите эту формулу = СЛЧИС () , Затем нажмите Enter ключ, см. снимок экрана:

2. И выберите весь столбец E, нажав Ctrl + Space одновременно, а затем нажмите Ctrl + D ключи для применения формулы = СЛЧИС () ко всей колонке E. См. снимок экрана:

3. Затем в ячейке D1 введите максимальное количество необходимого случайного числа. В этом случае я хочу вставить случайные числа без повторения от 1 до 50, поэтому я введу 50 в D1.

4. Теперь перейдите к столбцу A, выберите ячейку A1, введите эту формулу. =IF(ROW()-ROW(A$1)+1>$D$1/2,»»,RANK(OFFSET($E$1,ROW()-ROW(A$1)+(COLUMN()-COLUMN($A1))*($D$1/2),),$E$1:INDEX($E$1:$E$1000,$D$1))) , затем перетащите маркер заполнения в следующий столбец B и перетащите маркер заполнения в нужный диапазон. Смотрите скриншот:

Теперь в этом диапазоне нужные вам случайные числа не повторяются.

Примечание:

1. В приведенной выше длинной формуле A1 указывает ячейку, в которой используется длинная формула, D1 указывает максимальное количество случайных чисел, E1 — первая ячейка столбца, в котором применяется формула = RAND (), а 2 означает, что вы хотите вставить случайное число в два столбца. Вы можете изменить их по своему усмотрению.

2. Когда все уникальные числа сгенерированы в диапазоне, избыточные ячейки будут показаны как пустые.

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

Генерация уникального случайного числа с помощью Kutools for Excel’s Вставить случайные данные

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

Меньше времени, но выше производительность

Включает более 300 профессиональных инструментов для Excel 2019-2003
Первая версия 1.0 была выпущена в 2011 году, сейчас это версия 18.0.
Решает большинство ежедневных сложных задач Excel за секунды, экономя ваше время
30-дневная бесплатная пробная версия без каких-либо ограничений
Бесплатная загрузка Детали функции

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

1. Выберите диапазон, необходимый для генерации случайных чисел, и нажмите Кутулс > Вставить > Вставить случайные данные. Смотрите скриншот:

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

3. Нажмите Ok для генерации случайных чисел и выхода из диалогового окна.

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

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

Наконечник.Если вы хотите выбрать или отсортировать данные случайным образом, попробуйте использовать Kutools for Excel’s Сортировка диапазона случайным образом как показано на следующем снимке экрана. Полная функция без ограничений в течение 30 дней, пожалуйста, скачайте и получите бесплатную пробную версию сейчас.