Как генерировать случайные числа в Excel без повторений

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

Как вы, наверное, знаете, в Microsoft Excel есть несколько функций для генерации случайных чисел, таких как СЛУЧАЙ, СЛУЧМЕЖДУ и СЛУЧАЙ. Однако нет гарантии, что результат любой функции не будет повторяться.

В этом руководстве объясняется несколько формул для создания списка уникальных случайных чисел. Обратите внимание, что некоторые формулы работают только в последних версиях Excel 365 и 2021, а другие можно использовать в любой версии Excel 2019, Excel 2016, Excel 2013 и более ранних версиях.

Получить список уникальных случайных чисел с заданным шагом

Работает только в Excel 365 и Excel 2021, которые поддерживают динамические массивы.

Если у вас последняя версия Excel, самый простой способ получить список уникальных случайных чисел — объединить 3 новые функции динамического массива: СОРТИРОВКА, ПОСЛЕДОВАТЕЛЬНОСТЬ и СЛУЧАЙ:

СОРТИРОВАТЬ(ПОСЛЕДОВАТЕЛЬНОСТЬ(н), СЛУЧАЙ(н))

Где н это количество случайных значений, которые вы хотите получить.

Например, чтобы создать список из 5 случайных чисел, используйте 5 для н:

= СОРТИРОВАТЬ (ПОСЛЕДОВАТЕЛЬНОСТЬ (5), МАССИВ СЛУЧАЙНЫХ (5))

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

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

В приведенной выше формуле вы только определяете, сколько строк нужно заполнить. Все остальные аргументы оставлены со своими значениями по умолчанию, то есть список будет начинаться с 1 и будет увеличиваться на 1. Если вы хотите другое первое число и приращение, установите свои собственные значения для третьего (Начало) и 4-й (шаг) аргументы функции ПОСЛЕДОВАТЕЛЬНОСТЬ.

Например, чтобы начать со 100 и увеличить на 10, используйте следующую формулу:

=СОРТИРОВАТЬ(ПОСЛЕДОВАТЕЛЬНОСТЬ(5, , 100, 10), МАССИВ СЛУЧАЙНЫХ(5))

Как работает эта формула:

Работая изнутри наружу, вот что делает формула:

• Функция ПОСЛЕДОВАТЕЛЬНОСТЬ создает массив последовательных чисел на основе заданного или заданного по умолчанию начального значения и увеличивающегося размера шага. Эта последовательность переходит в множество аргумент SORTBY.
• Функция RANDARRAY создает массив случайных чисел того же размера, что и последовательность (5 строк, 1 столбец в нашем случае). Минимальное и максимальное значения на самом деле не имеют значения, поэтому мы можем оставить их по умолчанию. Этот массив идет в по_массиву аргумент SORTBY.
• Функция SORTBY сортирует последовательные числа, сгенерированные функцией SEQUENCE, используя массив случайных чисел, сгенерированный функцией RANDARRAY.

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

Создать список случайных чисел без дубликатов

Работает только в Excel 365 и Excel 2021, которые поддерживают динамические массивы.

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

Случайные числа:

ИНДЕКС (УНИКАЛЬНЫЙ (СЛУЧАЙНЫЙ МАССИВ (н^ 2, 1, мин, МаксимумИСТИНА)), ПОСЛЕДОВАТЕЛЬНОСТЬ(н))

Случайные десятичные дроби:

ИНДЕКС (УНИКАЛЬНЫЙ (СЛУЧАЙНЫЙ МАССИВ (н^ 2, 1, мин, МаксимумЛОЖЬ)), ПОСЛЕДОВАТЕЛЬНОСТЬ(н))

Где:

• Н количество значений для генерации.
• Мин. является минимальным значением.
• Максимум является максимальным значением.

Например, чтобы создать список из 5 случайных целых чисел от 1 до 100 без повторений, используйте следующую формулу:

= ИНДЕКС (УНИКАЛЬНЫЙ (СЛУЧАЙНЫЙ МАССИВ (5 ^ 2, 1, 1, 100, ИСТИНА)), ПОСЛЕДОВАТЕЛЬНОСТЬ (5))

Чтобы сгенерировать 5 уникальных случайных десятичных чисел, поместите FALSE в последний аргумент RANDARRAY или опустите этот аргумент:

= ИНДЕКС (УНИКАЛЬНЫЙ (СЛУЧАЙНЫЙ МАССИВ (5 ^ 2, 1, 1, 100)), ПОСЛЕДОВАТЕЛЬНОСТЬ (5))

Как работает эта формула:

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

• Функция RANDARRAY создает массив случайных чисел на основе заданных вами минимального и максимального значений. Чтобы определить, сколько значений генерировать, вы возводите желаемое количество уникальных значений в степень 2. Поскольку в результирующем массиве может быть неизвестно, сколько дубликатов, вам необходимо предоставить достаточный массив значений для выбора UNIQUE. В этом примере нам нужно только 5 уникальных случайных чисел, но мы указываем RANDARRAY произвести 25 (5 ^ 2).
• Функция UNIQUE удаляет все дубликаты и «скармливает» свободный от дубликатов массив в INDEX.
• Из массива, переданного функцией UNIQUE, функция ИНДЕКС извлекает первый н значения, указанные SEQUENCE (в нашем случае 5 чисел). Поскольку значения уже находятся в случайном порядке, не имеет большого значения, какие из них выживут.

Примечание. На очень больших массивах эта формула может быть немного медленной. Например, чтобы получить список из 1000 уникальных чисел в качестве конечного результата, RANDARRAY должен был бы внутренне сгенерировать массив из 1000000 случайных чисел (1000^2). В таких ситуациях вместо возведения в степень можно умножить н на, скажем, 10 или 20. Только имейте в виду, что чем меньший массив передается в функцию UNIQUE (маленький относительно желаемого количества уникальных случайных значений), тем больше шанс, что не все ячейки в диапазоне разлива будут наполнен результатами.

Создайте диапазон неповторяющихся случайных чисел в Excel

Работает только в Excel 365 и Excel 2021, которые поддерживают динамические массивы.

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

ИНДЕКС (УНИКАЛЬНЫЙ (СЛУЧАЙНЫЙ МАССИВ (н^ 2, 1, мин, Максимум)), ПОСЛЕДОВАТЕЛЬНОСТЬ(ряды, столбцы))

Где:

• н это количество ячеек для заполнения. Чтобы избежать ручных вычислений, вы можете указать его как (количество строк * количество столбцов). Например, чтобы заполнить 10 строк и 5 столбцов, используйте 50^2 или (10*5)^2.
• Ряды количество строк для заполнения.
• Столбцы количество столбцов для заполнения.
• Мин. является самым низким значением.
• Максимум является высшим значением.

Как вы могли заметить, формула в основном такая же, как и в предыдущем примере. Единственным отличием является функция ПОСЛЕДОВАТЕЛЬНОСТЬ, которая в данном случае определяет как количество строк, так и количество столбцов.

Например, чтобы заполнить диапазон из 10 строк и 3 столбцов уникальными случайными числами от 1 до 100, используйте следующую формулу:

= ИНДЕКС (УНИКАЛЬНЫЙ (СЛУЧАЙНЫЙ МАССИВ (30 ^ 2, 1, 1, 100)), ПОСЛЕДОВАТЕЛЬНОСТЬ (10, 3))

И он создаст массив случайных десятичных знаков без повторяющихся чисел:

Если вам нужны целые числа, установите последний аргумент RANDARRAY в TRUE:

= ИНДЕКС (УНИКАЛЬНЫЙ (СЛУЧАЙНЫЙ МАССИВ (30 ^ 2, 1, 1, 100, ИСТИНА)), ПОСЛЕДОВАТЕЛЬНОСТЬ (10,3))

Как генерировать уникальные случайные числа в Excel 2019, 2016 и более ранних версиях

Поскольку ни одна версия, кроме Excel 365 и 2021, не поддерживает динамические массивы, ни одно из вышеперечисленных решений не работает в более ранних версиях Excel. Однако это не означает, что решения вообще нет, вам просто нужно выполнить еще несколько шагов:

1. Создайте список случайных чисел. В зависимости от ваших потребностей используйте:
   • Функция RAND для генерации случайных десятичных знаков от 0 до 1 или
   • Функция СЛУЧМЕЖДУ для создания случайных целых чисел в указанном диапазоне.

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

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

   =СЛУЧМЕЖДУ(1,20)

   Чтобы ввести формулу сразу в несколько ячеек, выделите все ячейки (в нашем примере A2:A15), введите формулу в строке формул и нажмите Ctrl + Enter. Или вы можете ввести формулу в первую ячейку, как обычно, а затем перетащить ее на столько ячеек, сколько необходимо.

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

   Как вы могли заметить, мы ввели формулу в 14 ячеек, хотя в конечном итоге нам нужно всего 10 уникальных случайных чисел.

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

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

3. Удалить дубликаты. Для этого выберите все числа, перейдите к Данные вкладка > Инструменты данных группу и нажмите Удалить дубликаты. в Удалить дубликаты диалоговом окне, просто нажмите OK, ничего не меняя. Подробные инструкции см. в разделе Как удалить дубликаты в Excel.
   

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

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

Как остановить изменение случайных чисел

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

1. Выделите все ячейки со своей случайной формулой и нажмите Ctrl + C, чтобы скопировать их.
2. Щелкните правой кнопкой мыши выбранный диапазон и нажмите Специальная вставка > Ценности. Кроме того, вы можете нажать Shift + F10, а затем V, что является ярлыком для этой опции.

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

Генератор случайных чисел для Excel без повторов

Пользователям нашего Ultimate Suite на самом деле не нужно ни одно из вышеперечисленных решений, потому что у них уже есть универсальный генератор случайных чисел в их Excel. Этот инструмент может легко создать список неповторяющихся целых чисел, десятичных чисел, дат и уникальных паролей. Вот как:

1. На инструменты вкладка, нажмите Рандомизировать > Генератор случайных чисел.
2. Выберите диапазон для заполнения случайными числами.
3. На Генератор случайных чисел панели выполните следующие действия:
   • Выберите нужный тип значения: целое число, действительное число, дата, логическое значение, настраиваемый список или строка (идеально подходит для создания надежных уникальных паролей!).
   • Настройте Из а также К ценности.
   • Установите флажок Уникальные значения.
   • Нажмите Создать.

Вот и все! Выбранный диапазон сразу заполняется неповторяющимися случайными числами:

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

Вот как рандомизировать числа в Excel без дубликатов. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Практическая рабочая тетрадь для скачивания

Генерация уникальных случайных чисел в Excel (файл .xlsx)

Вас также могут заинтересовать