Найти в Дзене
Piter Melnikov

Генерация случайных величин в Excel

Оглавление

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

Генерация случайной величины, распределенной по равномерному закону

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

Р(ч)=1/N,

где N – количество возможных значений случайной величины

Для получения случайной величины, распределенной по равномерному закону, в библиотеке Мастера функций табличного процессора в категории Математические есть специальная функция СЛЧИС(), которая генерирует случайные вещественные числа в диапазоне 0 -1. Функция не имеет параметров
Если необходимо сгенерировать случайные числа в другом диапазоне, то для этого нужно использовать формулу:

= СЛЧИС() * (b – a) +a, где

a - число, устанавливающее нижнюю границу диапазона;
b – число, устанавливающее верхнюю границу диапазона.
Например, для генерации чисел распределенных по равномерному закону в диапазоне 10 – 20, нужно в ячейку рабочего листа ввести формулу:

=СЛЧИС()*(20-10)+10.

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

СЛУЧМЕЖДУ(Нижн_гран; Верхн_гран), где

Нижн_гран – число, устанавливающее нижнюю границу диапазона;
Верхн_гран - число, устанавливающее верхнюю границу диапазона. Применение функций СЛЧИС и СЛУЧМЕЖДУ рассмотрим на примере.
Пример 1. Требуется создать массив из 10 чисел, распределенных равномерно в диапазоне 50 – 100.
Решение

1. Выделим диапазон, включающий десять ячеек рабочего листа, например B2:B11 (рис. 1).
2. На ленте
Формулы в группе Библиотека функций кликнем на пиктограмме Вставить функцию.
3. В открывшемся окне диалога
Мастер функций выберем категорию Математические, в списке функций – СЛЧИС, кликнем на ОК - появится окно диалога Аргументы функции.
4. Нажмем комбинацию клавиш <Ctrl> + <Shift> + <Enter> - в выделенном диапазоне будут помещены числа, распределенные по равномерному закону в диапазоне 0 – 1 (рис. 1).

Рис. 1.
Рис. 1.

5. Щелкнем указателем мыши в строке формул и изменим имеющуюся там формулу, приведя ее к виду: =СЛЧИС()*(100-50)+50.
6. Нажмем комбинацию клавиш <Ctrl> + <Shift> + <Enter> - в выделенном диапазоне будут размещены числа, распределенные по равномерному закону в диапазоне 50 – 100 (рис. 2).

Рис.2.
Рис.2.

Генерация случайных чисел, распределенных по другим законам

В табличном процессоре MS Excel для генерации случайных чисел есть специальный инструмент Генерация случайных чисел. Этот инструмент позволяет генерировать числа, распределенные по различным законам. В их число входят:
равномерное распределение, инструмент позволяет генерировать заданное количество случайных чисел (по умолчанию в интервале 0 – 1);
нормальное распределение – характеризуется средним значением и стандартным отклонением. Инструмент позволяет генерировать заданное количество случайных чисел, по умолчанию используется среднее значение 0 и стандартное отклонение 1;
биноминальное распределение - характеризуется вероятностью успеха для некоторого числа испытаний, например, можно сгенерировать случайные двухальтернативные переменные по числу испытаний, сумма которых будет биноминальной случайной величиной;
дискретное – характеризуется значением и соответствующим ему интервалом вероятности. Величины значений предварительно формируются в диапазоне ячеек в виде столбца, в смежном столбце правее первого указываются и соответствующие вероятности. Сумма вероятностей должна быть равна единице;
• распределения
Бернулли, Пуассона и Модельное.

  1. Для включения инструмента Генерация случайных чисел на ленте Данные в группе Анализ нужно выбрать пиктограмму Анализ данных (Data Analysis), в открывшемся окне диалога Анализ данных выбрать в списке Генерация случайных чисел (Random Number Generator) (рис. 3) – откроется диалоговое окно Генерация случайных чисел (Random Number Generator) (рис.4).
Рис. 3
Рис. 3
Рис. 4
Рис. 4

В рабочие поля группы Параметры Parameters)вводятся параметры выбранного распределения. В поле Случайное рассеивание (Random Seed) вводится произвольное значение, для которого нужно генерировать случайные числа. В поле Выходной диапазон (Output Range) вводится ссылка на верхнюю левую ячейку выходного диапазона.

Рассмотрим на примерах технологию применения инструмента Генерация случайных чисел для генерации случайных чисел с различными законами распределения.

Пример 2. Требуется создать массив из 20 чисел, распределенных по равномерному закону в диапазоне 10 – 50.

Решение

1. Включим инструмент Генерация случайных чисел.
2. В поле
Число случайных чисел (Number of Random Numbers) введем число 20 (рис. 5).
3. Раскроем список
Распределение (Distribution) и выберем из него Равномерное (Uniform).
4. В поля
Между (Between) группы Параметры введем значения 10 и 50.
5. Установим курсор в поле
Выходной интервал и кликнем на ячейке рабочего листа (на рисунке G1), которая будет являться самой верхней левой ячейкой диапазона для вывода результата генерации.
6. Кликнем на кнопке ОК, в диапазоне G1:G20 будут выведены числа, распределенные по равномерному закону на интервале 10 – 50.

Рис. 5.
Рис. 5.

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

Рекомендуем почитать
Документы, вакансии и контакты