Как генерировать случайные числа в 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. Однако это не означает, что решения вообще нет, вам просто нужно выполнить еще несколько шагов:
- Создайте список случайных чисел. В зависимости от ваших потребностей используйте:
- Функция RAND для генерации случайных десятичных знаков от 0 до 1 или
- Функция СЛУЧМЕЖДУ для создания случайных целых чисел в указанном диапазоне.
Обязательно сгенерируйте больше значений, чем вам действительно нужно, потому что некоторые из них будут повторяться, и вы удалите их позже.
В этом примере мы создаем список из 10 случайных целых чисел от 1 до 20, используя следующую формулу:
=СЛУЧМЕЖДУ(1,20)
Чтобы ввести формулу сразу в несколько ячеек, выделите все ячейки (в нашем примере A2:A15), введите формулу в строке формул и нажмите Ctrl + Enter. Или вы можете ввести формулу в первую ячейку, как обычно, а затем перетащить ее на столько ячеек, сколько необходимо.
В любом случае результат будет выглядеть примерно так:
Как вы могли заметить, мы ввели формулу в 14 ячеек, хотя в конечном итоге нам нужно всего 10 уникальных случайных чисел.
- Превратите формулы в значения. Поскольку и СЛУЧАЙ, и СЛУЧМЕЖДУ пересчитываются при каждом изменении на листе, ваш список случайных чисел будет постоянно меняться. Чтобы этого не произошло, используйте Специальная вставка > Значения для преобразования формул в значения, как описано в Как остановить пересчет случайных чисел.
Чтобы убедиться, что вы все сделали правильно, выберите любое число и посмотрите на строку формул. Теперь должно отображаться значение, а не формула:
- Удалить дубликаты. Для этого выберите все числа, перейдите к Данные вкладка > Инструменты данных группу и нажмите Удалить дубликаты. в Удалить дубликаты диалоговом окне, просто нажмите OK, ничего не меняя. Подробные инструкции см. в разделе Как удалить дубликаты в Excel.
Сделанный! Все дубликаты исчезли, и теперь вы можете удалить лишние номера.
Кончик. Вместо встроенного инструмента Excel вы можете использовать наше расширенное средство для удаления дубликатов для Excel.
Как остановить изменение случайных чисел
Все функции рандомизации в Excel, включая СЛУЧАЙ, СЛУЧМЕЖДУ и СЛУЧАЙ, являются изменчивыми, то есть они пересчитываются каждый раз при изменении электронной таблицы. В результате при каждом изменении создаются новые случайные значения. Чтобы предотвратить автоматическое создание новых чисел, используйте функцию «Специальная вставка» > «Значения», чтобы заменить формулы статическими значениями. Вот как:
- Выделите все ячейки со своей случайной формулой и нажмите Ctrl + C, чтобы скопировать их.
- Щелкните правой кнопкой мыши выбранный диапазон и нажмите Специальная вставка > Ценности. Кроме того, вы можете нажать Shift + F10, а затем V, что является ярлыком для этой опции.
Подробные инструкции см. в разделе Как преобразовать формулы в значения в Excel.
Генератор случайных чисел для Excel без повторов
Пользователям нашего Ultimate Suite на самом деле не нужно ни одно из вышеперечисленных решений, потому что у них уже есть универсальный генератор случайных чисел в их Excel. Этот инструмент может легко создать список неповторяющихся целых чисел, десятичных чисел, дат и уникальных паролей. Вот как:
- На инструменты вкладка, нажмите Рандомизировать > Генератор случайных чисел.
- Выберите диапазон для заполнения случайными числами.
- На Генератор случайных чисел панели выполните следующие действия:
- Выберите нужный тип значения: целое число, действительное число, дата, логическое значение, настраиваемый список или строка (идеально подходит для создания надежных уникальных паролей!).
- Настройте Из а также К ценности.
- Установите флажок Уникальные значения.
- Нажмите Создать.
Вот и все! Выбранный диапазон сразу заполняется неповторяющимися случайными числами:
Если вам интересно попробовать этот инструмент и изучить другие интересные функции, включенные в наш Ultimate Suite, вы можете загрузить пробную версию.
Вот как рандомизировать числа в Excel без дубликатов. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь для скачивания
Генерация уникальных случайных чисел в Excel (файл .xlsx)
