В этой статье мы обсудим несколько различных формул, чтобы генерировать случайные числа в Excel без повторений. Узнайте, как использовать формулы и инструменты Excel для создания случайных чисел не допуская дубликатов. Предлагаем несколько простых советов для эффективной работы.
Как вы, наверное, знаете, в Microsoft Excel есть несколько функций для генерации чисел, таких как СЛЧИС (RAND в английской версии), СЛУЧМЕЖДУ (RANDBETWEEN) и СЛМАССИВ (RANDARRAY). Однако нет никакой гарантии, что среди результатов этих функций не будет одинаковых значений.
В этой статье предлагается несколько формул для создания списка уникальных случайных числовых значений. Обратите внимание, что некоторые формулы работают только в последних версиях Excel 365 и 2021, а другие можно использовать в любой версии Excel 2019, Excel 2016, Excel 2013 и более ранних версиях.
Случайные числа без повторов с заданным шагом
Работает только в Excel 365 и Excel 2021, поддерживающих динамические массивы.
Если у вас установлена последняя версия Excel, самый простой способ устроить розыгрыш случайных чисел без повторений — это объединить три функции динамического массива: СОРТПО, ПОСЛЕД и СЛМАССИВ:
=СОРТПО(ПОСЛЕД(n); СЛМАССИВ(n))
Где n — количество случайных значений, которые вы хотите получить.
Например, чтобы создать список из 10 чисел, используйте 10 вместо n :
=СОРТПО(ПОСЛЕД(10); СЛМАССИВ(10))
Введите формулу в первую ячейку диапазона, где вы хотите генерировать случайные числа, нажмите ENTER, и результаты будут автоматически записаны в столбце на n ячеек вниз.
Как вы можете видеть на скриншоте ниже, эта формула фактически выводит числа от 1 до 10 в произвольном порядке.
В приведенной выше формуле вы определяете только количество строк для заполнения. Для всех остальных аргументов оставлены значения по умолчанию, то есть список начнется с 1 и будет увеличиваться на 1. Если вам нужно другое начальное число и шаг между числами, установите свои собственные значения для аргументов функции ПОСЛЕД.
Функция ПОСЛЕД гарантирует вам, что будут получены числа без повторений, поскольку каждое следующее число на 1 больше, чем предыдущее.
Например, чтобы получить 10 случайных значений начиная со 100 с шагом 50, используйте следующую формулу:
=СОРТПО(ПОСЛЕД(10; ; 100; 50); СЛМАССИВ(10))
Как видите, эта простая формула создает список неповторяющихся случайных значений с заранее определенным шагом. Но на самом деле эти числа не совсем случайные, так как мы заранее указываем начальное значение и шаг его изменения. Просто записаны эти числа будут совершенно рандомно, своего рода розыгрыш без повторений.
Чтобы обойти это ограничение, используйте расширенную версию формулы, представленную ниже.
Алгоритм случайных чисел без повторения
Работает только в Excel 365 и Excel 2021, поддерживающих динамические массивы.
Чтобы генерировать случайные числа в Excel без дубликатов, используйте одну из приведенных ниже общих формул.
Случайные целые числа:
=ИНДЕКС(УНИК(СЛМАССИВ(n^2; 1; min; max; ИСТИНА)); ПОСЛЕД(n))
Случайные десятичные дроби:
=ИНДЕКС(УНИК(СЛМАССИВ(n^2; 1; min; max; ЛОЖЬ)); ПОСЛЕД(n))
Где:
- n — количество значений, которые нужно сгенерировать.
- min — минимальное значение.
- max — максимальное значение.
Например, чтобы создать список из 30 случайных целых чисел от 1 до 100 не повторяясь, используйте следующую формулу:
=ИНДЕКС(УНИК(СЛМАССИВ(30^2; 1; 1; 100; ИСТИНА)); ПОСЛЕД(30))
При этом все эти числа будут расположены в одном столбце, вертикально.
Чтобы записать эти 30 чисел в 3 столбца по 10 чисел в каждом, немного изменим формулу, добавив еще один аргумент в функцию ПОСЛЕД.
=ИНДЕКС(УНИК(СЛМАССИВ(n^2; 1; min; max; ИСТИНА)); ПОСЛЕД(a; b))
Где a – количество строк, b – количество столбцов в диапазоне, в котором нужно записать случайные неповторяющиеся числа. При этом a*b=n.
Ячейка с формулой – это верхний левый угол этого диапазона.
Вот как это может выглядеть с нашими 30 случайными целыми числами:
=ИНДЕКС(УНИК(СЛМАССИВ(30^2; 1; 1; 100; ИСТИНА)); ПОСЛЕД(10;3))
При этом обязательно обратите внимание, что функция ПОСЛЕД должна возвратить 10*3=30 значений.
Чтобы сгенерировать 30 уникальных случайных десятичных чисел, запишите ЛОЖЬ в последний аргумент СЛМАССИВ или просто опустите этот аргумент:
=ИНДЕКС(УНИК(СЛМАССИВ(30^2; 1; 1; 100; ЛОЖЬ)); ПОСЛЕД(10;3))
Если вам не нужно такое количество цифр после запятой, вы можете использовать любую удобную для вас функцию округления чисел, например:
=ОКРУГЛ(ИНДЕКС(УНИК(СЛМАССИВ(30^2; 1; 1; 100; ЛОЖЬ)); ПОСЛЕД(10;3));2)
Так может выглядеть простейший генератор случайных чисел без повторений цифр в Excel.
Примечание. На очень больших массивах эта формула может работать немного медленно. Например, чтобы получить в качестве конечного результата список из 1000 уникальных чисел, СЛМАССИВ должен будет сгенерировать массив из 1 000 000 случайных чисел (1000^2). В таких ситуациях вместо возведения в степень вы можете умножить n, скажем, на 10 или 20. Просто имейте в виду, что в функцию УНИК передается меньший массив (небольшой относительно желаемого количества уникальных случайных значений), поэтому будет выше вероятность того, что не все ячейки в диапазоне будут заполнены результатами.
Как генерировать случайные числа без повторений в Excel 2019, 2016 и более ранних версиях
Поскольку ни одна версия, кроме Excel 365 и 2021, не поддерживает динамические массивы, ни одно из приведенных выше решений не работает в более ранних версиях Excel. Однако это не значит, что выхода нет.
Вам просто придется выполнить еще несколько шагов:
- Создайте список случайных значений. В зависимости от ваших потребностей используйте либо:
- Функция СЛЧИС для генерации случайных десятичных чисел от 0 до 1 или
- Функция СЛУЧМЕЖДУ для создания случайных целых чисел в указанном вами диапазоне.
Обязательно сгенерируйте больше значений, чем вам действительно нужно, поскольку некоторые из них будут повторяться, и вы удалите их позже.
В этом примере мы создаем список из 10 случайных целых чисел от 1 до 30, используя следующую формулу:
=СЛУЧМЕЖДУ(1;30)
Чтобы ввести формулу в несколько ячеек за один раз, выделите все ячейки (в нашем примере A2:A15), введите формулу в строке формул и нажмите Ctrl + Enter. Или вы можете ввести формулу в первую ячейку, как обычно, а затем перетащить ее вниз на необходимое количество ячеек.
В любом случае результат будет выглядеть примерно так:
Как вы могли заметить, мы ввели формулу в 14 ячеек, хотя в итоге нам понадобится только 10 уникальных случайных значений. И сделали мы это не напрасно. Вы можете заметить, что среди сгенерированных чисел есть одинаковые значения, от которых нужно будет избавиться.
- Замените формулы значениями. Поскольку и СЛЧИС, и СЛУЧМЕЖДУ пересчитываются при каждом изменении на листе, ваш список случайных значений будет постоянно меняться. Чтобы этого не произошло, скопируйте весь этот диапазон в буфер обмена, а затем используйте «Специальная вставка» > «Значения», чтобы преобразовать формулы в значения, которые больше не будут пересчитываться и изменяться.
- Чтобы убедиться, что вы все сделали правильно, выберите любую ячейку и посмотрите на строку формул. Теперь там должно отображаться значение, а не формула.
- Удалите дубликаты. Для этого выделите весь диапазон, перейдите на вкладку «Данные» > группу «Работа с данными» и нажмите «Удалить дубликаты». В появившемся диалоговом окне просто нажмите «ОК», ничего не меняя. Подробные инструкции см. в статье «Как удалить дубликаты в Excel».
Готово! Все дубли исчезли, и теперь вы можете удалить лишние цифры, оставив только 10 из них.
Примечание. Вместо встроенного инструмента Excel вы можете использовать более функциональный мастер удаления дубликатов для Excel.
Генератор случайных чисел для Excel
Пользователям надстройки Ultimate Suite ни одно из вышеперечисленных решений на самом деле не нужно, поскольку в их Excel уже есть универсальный рандомайзер случайных чисел. Этот инструмент может легко создать список неповторяющихся целых чисел, десятичных чисел, дат и уникальных паролей. Его работа подробно описана в этой статье: Генератор случайных чисел в Excel.
Если вам интересно попробовать этот инструмент и изучить другие интересные функции, включенные в Ultimate Suite, вы можете загрузить пробную версию.
Вот как можно создать рандомайзер случайных чисел без повторений в Excel.
Благодарю вас за чтение.
