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

Постановка задачи

Предположим, что нам необходимо создать набор целых случайных чисел без повторов в заданном интервале значений. Примеры с ходу:

  • генерация уникальных случайных кодов для товаров или пользователей
  • назначение людей на задачи (каждому по случайной из списка)
  • перестановки слов в поисковом запросе (привет seo-шникам)
  • игра в лото и т.д.

Способ 1. Простой

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

random-unique3.png

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

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

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

random-unique1.png

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

random-unique2.png

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

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

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

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

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

random-unique4.png

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

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

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



MCH
23.09.2015 18:14:52
Допустим, нам нужно создать на листе список из 9 неповторяющихся случайных целых чисел в интервале от 1 до 50
Предложенная формула в "Сложном" способе возвращает значения от 0 до 49, т.к. используется СТРОКА($1:$51)-1
Не смотря на то, что обрабатывается массив из 51го значения (от 0 до 50), число 50 не возвращяется, т.к. СЛУЧМЕЖДУ(1;51-СТРОКА(B1)) не может вернуть значение последнего элемента (51)

Более правильно данную формулу записать как:
=НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(A$1:A1;СТРОКА($1:$50))=0;СТРОКА($1:$50));СЛУЧМЕЖДУ(1;51-СТРОКА(A1)))

или
=НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ(СТРОКА($1:$50);A$1:A1;0));СТРОКА($1:$50));СЛУЧМЕЖДУ(1;51-СТРОКА(A1)))
24.09.2015 10:03:21
Еще есть неспортивный вариант - задать случайные числа через СЛУЧМЕЖДУ(), а затем удалить дубликаты по столбцу. Все значения из диапазона можно выбрать, если присвоить их заранее бОльшему диапазону (например, в 5 раз большему количеству ячеек)

И смежная задача - когда необходимо диапазону присвоить произвольную сортировку. То есть, например, есть 10 товаров и нужно им присвоить произвольные числа от 1 до 10. Тогда можно заполнить ячейки этими числами, а в соседний столбец поместить случайное число через СЛЧИС(). После сортировки по столбцу со случайным числом получим произвольную сортировку.

ЧислоСЛЧИС
40,734615
30,645562
90,483153
10,089619
50,144564
100,488378
20,476774
80,900243
60,080447
70,522715
30.09.2015 04:46:34
Думаю, что создать случайные числа можно создать и функцией RANDBETWEEN(), а дубликаты удалять нет необходимости. Можно просто скрыть их с помощью Advanced Filter (там есть опция отображать уникальные значения). Мне кажется это самый простой способ. Правда нужно формулы в значения перевести так как после первого пересчета формул будут сгенерированы новые числа.

В случае удаления дубликатов, как было предложено Вами, массив получится максимально псеводослучайный так как Вы удаляете из выборки числа, которые дублируют друг друга, но являются уникальными для массива :)
29.03.2017 12:50:11
Но мне самое главное увязать генератор случайных чисел с таблицей жеребьевки дебютов и разместить  шифр жеребьевки в таблицу.
Добрый день. Прошу помочь. В 3х вариантах выше разобрался, но у меня задача сгенерить числа в диапазоне 0,8-1,4. Почему то не могу в примерах выше изменить диапазон.
Наверх