Содержание
При решении задач в Microsoft Office Excel с помощью макросов иногда необходимо сгенерировать произвольные числа для проверки правильности работы программы. Или нужно создать большой массив различных цифр без повторений. Для подобных заданий в редакторе заложены специализированные функции, и сегодня разберемся, как задать случайное число в excel.
Функции
Excel содержит две функции, которые позволяют генерировать произвольные цифры –СЛЧИС и СЛУЧМЕЖДУ. Рассмотрим каждую из них более подробно.
Функция СЛЧИС не имеет аргументов, поэтому просто вписываете ее в строку формул и нажимаете Enter.
При помощи маркера автозаполнения можно применить формулу к нужному количеству ячеек.
Важно! При любом действии с ячейкой число автоматически изменяется. Чтобы его зафиксировать, необходимо поставить курсор в нужную ячейку, выделить выражение в строке формул и нажать клавишу F9.
Особенностью этой функции является то, что она работает в пределах от нуля включительно до единицы с нормальным распределением.
Чтобы сгенерировать цифры в заданном диапазоне, например от 3 до 8, необходимо дополнять формулу.
Чтобы сгенерировать случайное целое число, отлично подойдет функция СЛУЧМЕЖДУ. Где в качестве аргументов выступают два числа – верхняя и нижняя границы. При этом существует несколько особенностей:
- Первая часть формулы должна быть меньше второй.
- Границы должны быть целыми числами.
- Цифры после запятой отбрасываются.
Если умножить формулу на определенный коэффициент, то можно создать случайное дробное число с десятыми, сотыми, тысячными и так далее.
В качестве бонуса рассмотрим, как использовать 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). Встроенный в табличном редакторе инструмент можно использовать как инструмент генерации, но нужно знать если вы хотите изменить набор случайных чисел, то вам нужно эту процедуру перезапустить.
Для получения доступа к этой, бесспорно, полезной надстройки, нужно, для начала, с помощью диалогового окна «Надстройки» установить этот пакет. Если у вас он уже установлен, то дело за малым, выбираете пункт меню «Данные» – «Анализ» – «Анализ данных», выбираете «Генерация случайных чисел» в предложенном программой списке и жмём «ОК».
В открывшемся окне мы выбираем тип в меню «Распределение», после указываем дополнительные параметры, которые изменяются, исходя с типа распределения. Ну и финальный шаг, это указание «Выходной интервал», именно тот интервал где будут храниться, ваши случайные числа.
А на этом у меня всё! Я очень надеюсь, что вопрос по созданию генератора случайных чисел я раскрыл полностью и вам всё понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!
Не забудьте поблагодарить автора!
Не додумывай слишком много. Так ты создаешь проблемы, которых изначально не было.