2.1. Использование средств ms Excel для моделирования стохастических процессов

Рассмотренные до сих пор модели были детерминированными. Иными словами, входные параметры в них имели вполне определённые значения. Существует другой класс моделей, в которых входными параметрами являются случайные величины. Необходимость учёта случайных факторов обусловлена двумя причинами, между которыми не всегда можно провести чёткую границу. 1) Параметры задачи могут иметь статистическую природу. 2) Часто мы не имеем полных данных об интересующей нас системе. Мы можем лишь предполагать, что тот или иной параметр может принимать с определённой вероятностью некоторые значения.

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

Отличие недетерминированных задач состоит в том, что вместо определённых чисел в них приходится оперировать статистическими величинами. В связи с этим возникает потребность в знании основ статистической теории. Данное пособие рассчитано на тех, кто обладает такими знаниями. При необходимости можно почерпнуть их из справочной и специальной литературы [7]. Основные сведения о функциях распределения случайных величин приведены в Приложении.

Можно выделить несколько типов задач, требующих статистического подхода. 1) Задачи связанные с учётом риска. В узком смысле слова риск можно понимать как возможность неблагоприятного исхода событий. В экономических моделях это, как правило, означает превышение расходов над доходами. В широком смысле слова риск можно понимать как возможность наступления события, отличного от ожидаемого. Далее будет использоваться в основном второе значение. 2) Задачи оптимизации систем обслуживания, в которых запросы на обслуживание возникают случайным образом и не могут быть моментально удовлетворены. Характерной чертой таких систем является образование в них очередей на обслуживание. 3) Задачи планирования материальных запасов. Наличие случайного спроса в соответствующих системах ведёт к невозможности точно определить объёмы и сроки заказов партий товаров.

Общая схема действий в случае стохастических моделей включает три этапа.

1. Генерирование случайных чисел, представляющих возможные значения входных параметров.

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

3. Анализ выходных данных.

На первом этапе основная проблема заключается в генерировании случайных чисел с нужной функцией распределения. В Excel есть функция СЛЧИС() (RAND()), выдающая случайные числа, распределённые равномерно в интервале [0; 1). При помощи математических преобразований из него можно получить другие виды распределений. Например, треугольное распределение случайной величины X можно получить, складывая две случайных величины U1 и U2 распределённые равномерно на единичном интервале по формуле

X = a+ (U1+U2), (2.1)

где a и b – минимальное и максимальное значения X соответственно. Наиболее вероятное значение X, в данном случае, будет находиться посередине между a и b (рис. 2.1). Чтобы получить треугольную функцию распределения с максимумом в одной из крайних точек (рис. 2.2), можно воспользоваться формулой

X = a+(c-a) . (2.2)

Д

Рис. 2.3. Треугольная функция распределения общего вида

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

X = – 6. (2.3)

Случайные величины с нормальной функцией распределения общего вида со средним значением  и стандартным отклонением  можно получить из (2.3) по формуле

Y = +X. (2.4)

Чтобы получить случайные величины, имеющие экспоненциальное распределение со средним значением , используют выражение

X = -ln(U). (2.5)

Для получения целочисленной случайной переменной J, принимающей значения k, k+1, …, k+m с равной вероятностью, можно воспользоваться формулой

J = k+ЦЕЛОЕ((m+1)U), (2.6)

где ЦЕЛОЕ(X) (в английском варианте INT(X)) – функция Excel, возвращающая наибольшее целое число меньшее X.

В общем случае, чтобы получить случайное число, принимающее ряд дискретных значений с заданными вероятностями w_i, поступают следующим образом. Вычисляют вероятности F_i = , с которыми случайное число должно принимать значения не бо́льшие, чем x_i. Такая зависимость F(w) называется кумулятивной функцией распределения. Далее, на листе Excel надо сформировать таблицу, в первом столбце которой расположены числа F_i, а во втором – x_i, причём таблица должна быть отсортирована по возрастанию значений F_i (и x_i). Сортировку значений можно произвести, воспользовавшись командой Excel Сортировка из меню Данные. Затем генерируют случайное число с равномерной функцией распределения из единичного интервала и находят индекс i, для которого выполняется неравенство: F_i-1U<F_i. По значению индекса определяют само число. Указанный алгоритм организуется посредством функции Excel ВПР (VLOOKUP). Эта функция имеет три аргумента. Первый из них указывает число, которое надо найти. Второй аргумент задаёт диапазон ячеек таблицы, в которых будет производиться поиск. Он состоит из адреса левой верхней ячейки, потом идёт двоеточие и затем адрес правой нижней ячейки таблицы. Третий аргумент указывает столбец таблицы, из которого надо взять искомое число. Действует функция ВПР следующим образом. В первом столбце указанной таблицы ищется заданное число. Если такого числа нет, то берётся ближайшее к нему меньшее число. Далее функция ВПР возвращает число, взятое из столбца таблицы, номер которого указывает третий аргумент. Это число находится в той же строке таблицы, что и число из первого столбца, задаваемое первым аргументом функции ВПР.

Описанный выше способ является реализацией метода обратного преобразования (Inverse Transform Method) для дискретной случайной величины. Этот метод также можно использовать для генерации непрерывных случайных величин. Чтобы воспользоваться им, надо знать функцию, обратную функции распределения. Наиболее употребительные из таких функций представлены в наборе функций, генерируемых Excel. Их отличительным признаком является добавка буквосочетания ОБР (INV) после названия прямой функции. Само значение случайной величины Х генерируется согласно формуле

X=F^-1(U), (2.7)

где F^-1 обозначает функцию, обратную к F. Имеется ряд программ, которые позволяют добавить в Excel функции, генерирующие случайные величины с нужной функцией распределения.

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