игральные кубики

Генераторы случайных чисел

надстройки "Моделирование Монте-Карло"

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

После старта надстройки "Моделирование Монте-Карло" в меню MS Excel «Вставка функций» появится новая группа функций с префиксом "fmc_..." — это генераторы случайных чисел разного вида.

Панель "Вставка функций" MS Excel
панель вставки функции с генераторами случайных чисел
Список генераторов надстройки

Возвращает случайную величину с бета-распределением, приведенным к интервалу от T_min до T_max и максимумом при T_most_probable.

Возвращает дискретную случайную величину с заданным частотным распределением: Values — Probabilities.

Возвращает случайную величину времени расходования запаса Inventory при среднем потреблении Mean и стандартном отклонении спроса StDev.

Возвращает экспоненциально распределенную случайную величину со средним значением Mean.

Возвращает нормально распределенную случайную величину со средним значением Mean и стандартным отклонением STDev.

Возвращает случайную величину с пуассоновским распределением со средним значением Mean.

Возвращает случайное число с равномерным распределением от 0 до 1.

Возвращает случайную величину с треугольным распределением с нижним пределом Pessimistic, ожидаемым значением Most probable и верхним пределом Optimistic.

Возвращает случайное число с равномерным распределением от Left до Right.

Помните, что MS Excel "узнает" эти функции только, когда подключена надстройка "Моделирование Монте-Карло".
Если надстройка не подключена, то в ячеках рабочей книги Excel вместо генераторов случайных чисел будет #ИМЯ?.
Не исправляйте ячейки с ошибками, просто подключите надстройку или запустите ее с панели инструментов Excel и ячейки с генераторами случайных чисел автоматически опознаются.
При каждом обновлении рабочего листа значение ячеек, содержащих генераторы, будет другим. Самостоятельное обновление ячеек происходит при нажатии клавиши F9.
Очевидно, что один и тот же файл, использующий генераторы случайных чисел, запущенный в разное время, и запущенный на разных компьютерах, выдает разные результаты.

Подробное описание Генераторов случайных чисел

= fmc_Rand (..)

Возвращает натуральное число с равномерным распределением от 0 до 1.
Генератор не имеет параметров и является основой других генераторов. С его помощью получают другие, более изощренные распределения. Этот генератор работает быстрее всех остальных.
Генератор fmc_Rand() полный аналог стандартного генератора случайных числе MS Excel = СЛЧИС (..).

Примеры гистограмм для генератора fmc_Rand
1 / 3
2 / 3
3 / 3

 Гистограмма построена на основании 10 тысяч испытаний
Гистограмма построена на основании 100 тысяч испытаний
Гистограмма построена на основании 1 миллиона испытаний

Обратите внимание на скорость работы генераторов и других функций при моделировании.
Надстройка "Моделирование Монте-Карло" всегда показывает время расчета. На это время следует ориентироваться при увеличении статистики.
Если статистика увеличивается в 100 раз, то время расчета увеличивается тоже в 100 раз.

= fmc_Uniform ( Left; Right )(..)

Возвращает случайное число с равномерным распределением от Left до Right.
Генератор fmc_Uniform легко получить из генератора fmc_Rand. Для этого нужно растянуть значения генератора fmc_Rand до интервала Right - Left и сместить до Left. При этом безразлично, в каком порядке показать концы интервала Left и Right - генератор будет работать корректно.
Генератор случайных чисел fmc_Uniform ( Left; Right ) аналогичен генератору
=СЛУЧМЕЖДУ (нижн_граница; верхн_граница) из библиотеки MS Excel, который становится доступен только при подключенной стандартной надстройки "Пакет анализа".
Быстродействие генератора fmc_Uniform ( ) очень близко к быстродействию fmc_Rand ().

= fmc_Normal ( Mean; STDev )

Возвращает нормально распределенную случайную величину со средним значением Mean и стандартным отклонением STDev.
Нормальное распределение - это второе фундаментальное распределение, которое очень часто встречается в нашей жизни.

Панель для ввода параметров генератора fmc_Normal

Параметры распределения вводят числами или ссылками на ячейки.

Панель для ввода параметров генератора fmc_Normal
Гистограммы генератора fmc_Normal
Гистограммы для генератора fmc_Normal
Как избавиться от отрицательной области гистограммы, если она возникнет
Однако при моделировании спроса может оказаться, что часть гистограммы расположена в отрицательной области оси, что соответствует отрицательному спросу.
Это получается при больших значениях параметра StDev (при StDev > ~1/3 Mean).
Например, отрицательный "хвост" этой гистограммы получен при значениях параметров Mean = 10 и StDev = 5 ( fmc_Normal (10; 5)).
Гистограммы для генератора fmc_Normal с отрицательной областью
Чтобы получить корректные значения спроса предлагается использовать функцию MS Excel =ЕСЛИ(..) и считать, что при отрицательных значениях генератора спрос был нулевой.
В этом случае в качестве целевой ячейки в надстройке "Моделирование Монте-Карло" должна быть указана ячейка A2.
A
1 = fmc_Normal (10; 5)
2 = ЕСЛИ ( A1 < 0; 0; A1)
Тогда вся отрицательная часть распределения (гистограммы) будет собрана в одном столбце - нулевом.
Такой столбец можно интерпретировать как нулевые продажи. Что соответствует реальному положению дел, так как при высокой вариативности спроса нулевые продажи случаются чаще, чем небольшие.

Теперь гистограмма будет выглядеть следующем образом:

Гистограммы для генератора fmc_Normal без отрицательной области

Быстродействие генератора случайных чисел fmc_Normal примерно в 1,5 раза ниже, чем у fmc_Rand. Но если использовать для генерации нормально распределенных чисел функцию Excel = НОРМОБР (fmc_Rand(); 10; 5) расчет получится примерно в три раза медленнее, чем при использовании fmc_Normal().

= fmc_Discrete ( Values; Probabilities )

Возвращает дискретную случайную величину с заданным частотным распределением: Values — Probabilities.
Во многих случаях невозможно (или нет смысла) установить конкретный вид функции распределения случайной величины. Чаще всего это связано с малым числом возможных исходов. Однако, если известны вероятности всех возможных исходов, можно моделировать случайную величину по таблице с помощью генератора fmc_Discrete().

Пример применения генератора fmc_Discrete
A B
1 Values Probabilities
2 -10 35%
3 10 60%
4 50 5%
Решение

Вызываем функцию fmc_Disсrete и показываем, что выигрыши Values содержатся в ячейках A2:A4, а вероятности Probabilities в ячейках B2:B4.

Аргументы функции fmc_Discrete
Гистограмма, иллюстрирующая результаты моделирования
Гистограмма fmc_Discrete
Замечание

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

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

Генератор fmc_Discrete работает примерно вдвое медленнее генератора fmc_Rand.
Если таблица Values — Probabilities содержит много разрядов, то генератор работает очень медленно.

= fmc_ExhaustTime ( Inventory; Mean; StDev )

Возвращает случайную величину времени расходования запаса Inventory при среднем потреблении Mean и стандартном отклонении спроса StDev.
Этот генератор разработан автором сайта для моделирования систем управления запасами и может быть использован для оценки риска дефицита при заданном уровне безопасного резерва (safety stock), для расчета потерь из-за дефицита и для определения оптимального риска дефицита. На сайте есть пример подобного использования генератора.
Предполагается, что спрос клиентов распределен нормально (как это и бывает в подавляющем большинстве случаев).

Пример применения генератора fmc_ExhaustTime
A B C
8 Invetory Mean StDev
9 500 50 10
Решение

Вызываем функцию fmc_ExhaustTime и показываем, что запас Inventory нужно взять из ячейки A9, средний спрос Mean из ячейки B9, а стандартное отклонение спроса StDev из ячейки C9.

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

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

Когда мы говорим о нормально-распределенном спросе, необходимо помнить, что спрос не бывает отрицательным (хм, не будем учитывать возвраты товара), но генератор нормально-распределенной случайной величины fmc_Normal, тем не менее, может выдать отрицательное число. Вероятность такого события особенно велика, если стандартное отклонение StDev больше 1/3 от среднего спроса Mean.
При расчетах внутри генератора fmc_ExhaustTime в случае получения отрицательного значения спроса на очередном отрезке времени, спрос, естественно, приравнивают к нулю. Это означает, что среднее значение спроса, рассчитанное по результатам моделирования, не будет равно Mean (будет больше) при больших StDev.
Я планирую добавить на сайт пример корректных расчетов в такой ситуации, а пока просто обращаю внимание пользователей на необходимость учета такого различия.
Коротко же говоря, параметры Mean и StDev (при StDev > ~1/3Mean) должны быть равны не среднему спросу и стандартному отклонению спроса, рассчитаннным по статистическим данным, а среднему значению и стандартному отклонению нормального распределения, соответствующего частотной диаграмме спроса. Sapienti sat.

Скорость расчета с генератором fmc_ExhaustTime примерно в 10 раз меньше, чем с fmc_Rand, что неудивительно, учитывая многократный вызов генератора fmc_Normal для моделирования спроса.

= fmc_Exponential ( Mean )

Возвращает экспоненциально распределенную случайную величину со средним значением Mean.
Формула плотности распределения для данного экспоненциального распределения классическая:
p(t) = 1/m * exp(-1/m * t)
Напомним, что при этом среднее значение < t > (Mean) для экспоненциально распределенной случайной величины равно как раз: Mean = < t > = m, как и стандартное отклонение: σ = m.

Проверка работы генератора fmc_Exponential:

Аргумент функции можно задать числом или ссылкой на ячейку.

Панель надстройки fmc_Exponential
Результат работы генератора fmc_Exponential
Результат работы надстройки fmc_Exponential
Гистограмма, полученная в результате моделирования
Гистограмма надстройки fmc_Exponential
При моделировании средний интервал между событиями составит m единиц времени, а частота событий n = 1/m штук в единицу времени.

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

= fmc_Poisson ( Mean )

Возвращает случайную величину с пуассоновским распределением со средним значением Mean.
Распределение Пуассона дает вероятность того, что за единицу времени произойдет ровно n событий.
Например, вероятность того, что за единицу времени, соответствующую единице измерения потока λ= Mean, в систему поступит заданное количество заявок.
Формула для плотности распределения:
p(n) = e λn /n!
Среднее значение < n > для пуассоновски распределенной случайной величины равно
Mean = < n > = λ, а стандартное отклонение: σ = λ1/2.

Проверка работы генератора fmc_Poisson:

Аргумент функции можно задать числом или ссылкой на ячейку.

Панель надстройки fmc_Poisson
Результат работы генератора fmc_Poisson
Результат работы надстройки fmc_Poisson
Гистограмма, полученная в результате моделирования
Гистограмма надстройки fmc_Poisson

Это распределение широко применяется в теории массового обслуживания. Таким распределением моделируют входной поток независимых клиентов.

= fmc_Beta_PERT (T_min; T_most_probable; T_max)

Возвращает случайную величину с бета-распределением, приведенным к интервалу от T_min до T_max и максимумом при T_most_probable.
Такое распределение используется при оценке рисков в управлении проектами в модели PERT ( Project Evaluation and Review Technique ).
Плотность вероятности для случайной величины Beta_PERT приблизительно соответствует формуле Формула плотности вероятности Beta_PERT Это распределение получено из стандартных формул для бета-распределением
Формула бета-распределения где
Формула бета-распределения путем линейного масштабирования.
При этом среднее значение случайной величины равно
< x > = ( T_min + 4 * T_most_probable + T_max ) / 6,
а стандартное отклонение
s = ( T_max - T_min ) / 6.

Проверка работы генератора fmc_Beta_PERT:

Аргумент функции можно задать числом или ссылкой на ячейку.

Панель надстройки fmc_Beta_PERT
Гистограмма, полученная в результате моделирования
Гистограмма надстройки fmc_Beta_PERT
Результат работы генератора fmc_Beta_PERT:

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

Результат работы надстройки fmc_Beta_PERT
Точного соответствия функции распределения времени выполнения работ в модели PERT бета-распределению нет. Это только приближение. Однако связь параметров бета-распределения и генерируемых случайных чисел можно проверить.
С учетом известных соотношений для бета-распределения:
Формула бета-распределения

= fmc_Triangular ( Pessimistic; Most probable; Optimistic )

Возвращает случайную величину с треугольным распределением с нижним пределом Pessimistic, ожидаемым (наиболее вероятным) значением Most probable и верхним пределом Optimistic.
Это простое и ясное распределение, которое неплохо, пусть и грубо, имитирует, например, нормальное распределение для целей моделирования поведения систем или же распределение Пуассона.

Проверка работы генератора fmc_Triangular:

Аргумент функции можно задать числом или ссылкой на ячейку.

Панель надстройки fmc_Triangular
Гистограмма, полученная в результате моделирования
Гистограмма надстройки fmc_Triangular