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

Случайное распределение чисел в excel

При решении задач в Microsoft Office Excel с помощью макросов иногда необходимо сгенерировать произвольные числа для проверки правильности работы программы. Или нужно создать большой массив различных цифр без повторений. Для подобных заданий в редакторе заложены специализированные функции, и сегодня разберемся, как задать случайное число в excel.

Функции

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

Функция СЛЧИС не имеет аргументов, поэтому просто вписываете ее в строку формул и нажимаете Enter.

При помощи маркера автозаполнения можно применить формулу к нужному количеству ячеек.

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

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

Чтобы сгенерировать цифры в заданном диапазоне, например от 3 до 8, необходимо дополнять формулу.

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

1. Первая часть формулы должна быть меньше второй.
2. Границы должны быть целыми числами.
3. Цифры после запятой отбрасываются.

Если умножить формулу на определенный коэффициент, то можно создать случайное дробное число с десятыми, сотыми, тысячными и так далее.

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

Где СЧЕТЗ возвращает количество непустых ячеек, а ИНДЕКС позволяет вывести значение ячейки, которая находится на пересечении конкретной строки и столбца.

Специальный инструмент

Генератор случайных чисел можно найти в пакете анализа данных, который активируется через надстройки Excel. Чтобы воспользоваться этой функцией, необходимо нажать отдельную кнопку во вкладке Данные на Панели инструментов и из списка выбрать нужную строку.

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

В результате получилось следующее:

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

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

Жми «Нравится» и получай только лучшие посты в Facebook ↓

Рассмотрим генерацию случайных чисел с помощью надстройки Пакет Анализа и формул MS EXCEL.

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

С помощью надстройки Пакет Анализа можно сгенерировать случайные числа следующих распределений:

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

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

Примечание: С помощью другого инструмента надстройки Пакет анализа, который называется «Выборка», можно извлечь случайную выборку из конечной генеральной совокупности. Подробнее см. статью Случайная выборка из генеральной совокупности в MS EXCEL.

Модельное распределение

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

Поле Число переменных задает количество столбцов генерируемых данных. Т.к. в них будут сгенерированы совершенно одинаковые данные, то нет особого смысла указывать в поле Число переменных значение отличное от 1.

Поле Число случайных чисел можно оставить незаполненным, т.к. оно рассчитывается автоматически в зависимости от значений, указанных в группе Параметры диалогового окна. Например, при параметрах, указанных на рисунке выше, в каждом столбце будет выведено по 24 «случайных» числа: четыре нечетных числа 1; 3; 5; 7 (от 1 и до 7; шаг равен 2) будут повторены по 2 раза, а каждая последовательность будет повторена по 3 раза (4*2*3=24).

Произвольное дискретное распределение

С помощью надстройки Пакет Анализа можно сгенерировать числа, имеющие произвольное дискретное распределение, т.е. распределение, где пользователь сам задает значения случайной величины и соответствующие вероятности.

В поле Входной интервал значений и вероятностей необходимо ввести ссылку на двухстолбцовый диапазон (см. файл примера ).

Необходимо следить, чтобы сумма вероятностей модельного распределения была равна 1. Для этого в MS EXCEL имеется специальная функция ВЕРОЯТНОСТЬ() .

СОВЕТ: О генерации чисел, имеющих произвольное дискретное распределение, см. статью Генерация дискретного случайного числа с произвольной функцией распределения в MS EXCEL. В этой статье также рассмотрена функция ВЕРОЯТНОСТЬ() .

Доброго времени суток, уважаемый, читатель!

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

Итак, для чего же собственно мы можем использовать этом механизм:

• во-первых: мы можем для тестировки формул, заполнить нужный нам диапазон случайными числами;
• во-вторых: для формирования вопросов различных тестов;
• в-третьих: для любого случайно распределения заранее пронумерованных задач между вашими сотрудниками;
• в-четвёртых: для симуляции разнообразнейших процессов;

…… да и во многих других ситуациях!

В этой статье я рассмотрю только 3 варианта создания генератора (возможности макроса, я не буду описывать), а именно:

Создаём генератор случайных чисел с помощью функции СЛЧИС

С помощью функции СЛЧИС, мы имеем возможность генерировать любое случайное число в диапазоне от 0 до 1 и эта функция будет выглядеть так:

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

=СЛЧИС()*100; А вот если вам не нравятся дробные числа или просто нужно использовать целые числа, тогда используйте такую комбинацию функций, это позволит вам отсечь значения после запятой или просто отбросить их:

=ОТБР((СЛЧИС()*100);0) Когда возникает необходимость использовать генератор случайных чисел в каком-то определённом, конкретном диапазоне, согласно нашим условиям, к примеру, от 1 до 6 надо использовать следующую конструкцию (обязательно закрепите ячейки с помощью абсолютных ссылок):

• a – представляет нижнюю границу,
• b – верхний предел

и полная формула будет выглядеть: =СЛЧИС()*(6-1)+1, а без дробных частей вам нужно написать: =ОТБР(СЛЧИС()*(6-1)+1;0)

Создаём генератор случайных чисел с помощью функции СЛУЧМЕЖДУ

Эта функция более проста и начала нас радовать в базовой комплектации Excel, после 2007 версии, что значительно облегчило работу с генератором, когда необходимо использовать диапазон. К примеру, для генерации случайного числа в диапазоне от 20 до 50 мы будем использовать конструкцию следующего вида:

Создаём генератор с помощью надстройки AnalysisToolPack

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

Для получения доступа к этой, бесспорно, полезной надстройки, нужно, для начала, с помощью диалогового окна «Надстройки» установить этот пакет. Если у вас он уже установлен, то дело за малым, выбираете пункт меню «Данные» – «Анализ» – «Анализ данных», выбираете «Генерация случайных чисел» в предложенном программой списке и жмём «ОК».

В открывшемся окне мы выбираем тип в меню «Распределение», после указываем дополнительные параметры, которые изменяются, исходя с типа распределения. Ну и финальный шаг, это указание «Выходной интервал», именно тот интервал где будут храниться, ваши случайные числа.

А на этом у меня всё! Я очень надеюсь, что вопрос по созданию генератора случайных чисел я раскрыл полностью и вам всё понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

Не забудьте поблагодарить автора!

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

Функция СЛУЧМЕЖДУ() — Случайное число из заданного интервала в EXCEL

Нижн_граница — наименьшее целое число, которое возвращает функция.

Верхн_граница — наибольшее целое число, которое возвращает функция.

Если значение нижняя_граница больше значения верхняя_граница , функция вернет ошибку #ЧИСЛО! Предполагается, что границы диапазона – целые числа. Если введено число с дробной частью, то дробная часть будет отброшена.

Если необходимо получить случайное число, например, в интервале от 0 до 0,1, то нужно написать следующую формулу: =СЛУЧМЕЖДУ(0;10)/100 (с точностью 0,01, т.е. случайные значения будут 0,02; 0,05 и т.д.) =СЛУЧМЕЖДУ(0;1000)/10000 (с точностью 0,0001, т.е. случайные значения будут 0,0689; 0,0254 и т.д.)

Если необходимо получить не целое, а вещественное число, например, в интервале от 3 до 10, то нужно использовать функцию СЛЧИС() : =СЛЧИС()*(10-3)+3 (точностью 15 знаков, т.е. случайные значения будут 7,68866700270417; 8,68428856478223 и т.д.)

Если требуется сгенерировать случайное число от -2,699 до 30,02, причем оно должно быть округлено до тысячных (количество знаков после запятой случайного числа = максимальному количеству знаков после запятой границ интервала), то сначала нужно определить количество знаков после запятой у обоих границ (см. файл примера ). Это можно сделать с помощью формулы = ДЛСТР(A15)-1-ДЛСТР(ЦЕЛОЕ(A15))

Затем воспользоваться функцией СЛЧИС() , округлив значение.

Примечание . Границы интервала должны быть заданы константой. В случае расчетных значений интервалов результат непредсказуем (см. Проблемы округления в MS EXCEL ). Совет: Если границы интервала рассчитываются формулами, что в них можно задать необходимую точность.

Функция пересчитывает свое значение после каждого ввода нового значения в любую ячейку листа (или изменения значения ячейки) или нажатии клавиши F9 .

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

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

Предыстория: подобные задачи я обычно решаю с помощью трех дополнительных столбцов, но PGC01 (это ник участника форума) предложил немаленькую формулу, чтобы справиться с этой проблемой одним махом. Чтобы понять формулу, надо для начала познакомиться с работой функции НАИМЕНЬШИЙ(массив;k). Как правило, она возвращает k-е наименьшее значение массива. Например, =НАИМЕНЬШИЙ(<69;9;12;27;42;55>;3) возвращает 27, так как, 1-е наименьшее число в списке – 9, 2-е – 12, а 3-е – 27. Нечисловые значения в массиве игнорируются. Например, =НАИМЕНЬШИЙ(<69;9;12;ЛОЖЬ;42;55>;3) вернет 42.

Решение: сейчас мы разработаем формулу, которая будет формировать массив из неиспользованных чисел диапазона, а затем выбирать из этих чисел (рис. 1).

Рис. 1. Формула генерит наборы чисел, от 1 до 10, упорядоченные случайным образом

Скачать заметку в формате Word или pdf, примеры в формате Excel

Формула массива в ячейке В9: <=НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(B$3:B8;СТРОКА($1:$10))<>1; СТРОКА($1:$10));1+ЦЕЛОЕ(СЛЧИС()*(10-СТРОКА()+СТРОКА(B$4))))>. Excel начинает расчет формулы с функции СЧЁТЕСЛИ (рис. 2 и 3). СТРОКА($1:$10) возвращает массив чисел <1, 2, 3,…10>(Е5:Е14 на рис. 2 и второй прямоугольник на рис. 3).

Рис. 2. Диапазон Е4:Н14 показывает, как вычисляется фрагмент формулы

Функция СЧЁТЕСЛИ просит Excel просматривать ранее сгенерированные числа (на рис. 1 и 2 они размещены в диапазоне B$3:B8), и подсчитывать, сколько из них равны последовательно 1, 2, 3, … 10. Ответы будут 1 или 0, в зависимости от того, было ли ранее сгенерировано соответствующее число, или нет. На рис. 2 результат работы функции СЧЁТЕСЛИ помещен в столбец F. На рис. 3 – это третий прямоугольник. В ячейках G5:G14 проверяется, равен ли единице результат в F (4-й прямоугольник). Функция ЕСЛИ в столбце Н (рис. 2) проверяет значение в столбце G, и возвращает значение из столбца Е, если в G = ИСТИНА, и ЛОЖЬ, если в G =ЛОЖЬ (см. также 6-й прямоугольник на рис. 3).

Рис. 3. Последовательность вычислений, аналогичная рис. 2

В этот момент массив для выбора следующего случайного числа (т.е., случайного числа, которое будет отражено в ячейке В9 на рис. 1) сформирован. Обратите внимание, что в столбце Н на рис. 2 и в 6-м прямоугольнике на рис. 3 представлены числа, ранее не выбиравшиеся среди случайных. Оставшаяся часть формулы призвана выбрать из них одно число случайным образом. В принципе, теперь осталась формула: =НАИМЕНЬШИЙ(Н5:Н14;СЛУЧМЕЖДУ(1;5))

На самом деле, установить, что осталось именно пять невыбранных чисел сложнее, чем в приведенной выше формуле. Чтобы сделать формулу совместимый с Excel 2003, PGC01 использует функции СЛЧИС() вместо СЛУЧМЕЖДУ(). Функция СЛЧИС возвращает случайное число между 0 и 0,99999… =ЦЕЛОЕ(СЛЧИС()*5) возвращает случайное целое число между 0 и 4. И, наконец, =1+ЦЕЛОЕ(СЛЧИС()*5) возвращает случайное целое число между 1 и 5.

Поскольку вместо 5 в этой части формулы в разных строках должны быть разные числа, вместо фиксированного значения используется фрагмент 10-СТРОКА()+СТРОКА(B$4). Для ячейки В9 он вернет 10-9+4=5.

Резюме: довольно сложная формула массива может генерировать случайные числа без повторений.