Имитация с инструментом генератор случайных чисел

Этот инструмент предназначен для автоматической генерации множества данных (генеральной совокупности) заданного объема, элементы которого характеризуются определенным распределением вероятностей. При этом могут быть использованы семь типов распределений: равномерное, нормальное, Бернулли, Пуассона, биномиальное, модельное и дискретное. Применение инструмента Генератор случайных чисел, как и большинство используемых в этой работе функций, требует установки специального дополнения Пакет анализа.

Для демонстрации техники применения этого инструмента изменим условия нашего примера, определив вероятности для каждого сценария развития событий следующим образом (Таблица 20.).

Вероятностные сценарии реализации проекта

Таблица 20.

Мы также будем исходить из предположения о нормальном распределении ключевых переменных. Количество имитаций оставим прежним -500. Как и в предыдущем случае, выделим в рабочей книге два листа: Имитация2 и Результаты анализа2.

Решение задачи начнем с листа Результаты анализа2.

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

Функция НОРМРАСП()

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

Синтаксис

НОРМРАСП(x;среднее;стандартное_откл;интегральная)

X - это значение, для которого строится распределение.

Среднее - это среднее арифметическое распределения.

Стандартное_откл - это стандартное отклонение распределения.

Интегральная –0 или 1

В зависимости от заданного параметра интегральная –0 (ложь) или 1(истина) – она возвращает плотность распределения j(х) или значение функции распределения вероятностей F(x) для нормальной случайной величины.

Переходим к следующему листу и присваиваем ему имя Имитация2. Первая часть этого листа ( блок ячеек А1:Е10) предназначена для ввода исходных данных и расчета необходимых параметров их распределений. Нормальное распределение характеризуется двумя параметрами - математическим ожиданием (средним) и стандартным отклонением. Для расчета стандартных отклонений используются формулы - массивы.

Функция СУММПРОИЗВ()

Перемножает соответствующие элементы заданных массивов и возвращает сумму произведений.

Синтаксис

СУММПРОИЗВ(массив1;массив2;массив3; ...)

Массив1, массив2, массив3, ... - это от 2 до 30 массивов, чьи компоненты нужно перемножить, а затем сложить.

· Аргументы, которые являются массивами, должны иметь одинаковые размерности. Если это не так, то функция СУММПРОИЗВ возвращает значение ошибки #ЗНАЧ!.

· СУММПРОИЗВ трактует нечисловые элементы массивов как нулевые.

Пример

Следующая формула перемножает все компоненты двух массивов, а затем складывает полученные произведения, то есть выполняются следующие вычисления: 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3.

СУММПРОИЗВ({3;4:8;6:1;9}; {2;7:6;7:5;3}) равняется 156

Для формирования листа Имитация 2 необходимо определить необходимые имена (Вставка-имя-присвоить) для блоков ячеек и задать требуемые формулы. Затем ввести исходные значения постоянных переменных, ввести значения ключевых переменных и соответствующие вероятности.

Устанавливаем курсор в ячейку Переменные расходы и . приступаем к проведению имитационного эксперимента.

1. Выбрать в главном меню Сервис-Анализ данных. Результатом выполнения этих действий будет появление диалогового окна Анализ данных, содержащего список инструментов анализа.

2.Выбрать из списка Инструменты анализа пункт Генерация случайных чисел.

3. На экране появится диалоговое окно (Рисунок 12. Окно генерация случайных чисел). Указать в списке Распределениятребуемый тип - Нормальное. Заполнить остальные поля окна.

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

Рисунок 12. Окно генерация случайных чисел

Следующий обязательный аргумент для заполнения – содержимое поля Число случайных чисел(количество имитаций). Согласно условиям примера оно должно быть равно 500. При этом MS Excel автоматически подсчитывает необходимое количество ячеек для хранения генеральной совокупности.

Необходимый вид распределения задается путем соответствующего выбора из списка Распределения.Как уже отмечалось ранее, могут быть получены семь наиболее распространенных в практическом анализе типов распределений, каждое из которых характеризуется собственными параметрами. В рассматриваемом примере выбор типа распределения Нормальное повлек за собой появление дополнительных аргументов – параметров Среднее Стандартное отклонение, рассчитанных ранее для исследуемой переменной V листа Имитация. Эти аргументы могут быть заданы только в виде констант. Использование адресов ячеек и собственных имен здесь не допускается.

Указание аргумента Случайное рассеиваниепозволяет при повторных запусках генератора получать те же значения случайных величин, что и при первом. Таким образом, одну и ту же генеральную совокупность случайных чисел можно получить несколько раз, что значительно повышает эффективность анализа. В случае если этот аргумент не задан (равен 0), при каждом последующем запуске генератора будет формироваться новая генеральная совокупность. В нашем примере этот аргумент задан равным 1,что позволит нам оперировать с одной и той же генеральной совокупностью и избежать постоянных перерасчетов таблицы.

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

· Выходной блок ячеек на текущем листе (достаточно ввести ссылку на левую верхнюю ячейку выходного диапазона, при этом его размер будет определен автоматически и в случае возможного наложения генерируемых значений на уже имеющиеся данные на экран будет выведено предупреждающее сообщение;

· Новый рабочий лист – в рабочей книге будет открыт новый лист, содержащий результаты генерации случайных чисел, начиная с ячейки А1;

· Новая рабочая книга – будет открыта новая книга с результатами имитации на первом листе.

В рассматриваемом примере для проведения дальнейшего анализа необходимо, чтобы случайные величины размещались в специально отведенные для них блоки ячеек. В частности для хранения 500 значений первой переменной ранее был указан блок ячеек:Переменные расходы . Поскольку для этого блока определено собственное имя – Перем_расх, его необходимо указать в качестве выходного диапазона.

Генерация значений остальных переменных Q и Р осуществляется аналогичным образом.

По результатам проведенного имитационного эксперимента необходимо провести статистический анализ.