Генерация случайных величин. Построение простейшей имитационной модели в Exce

Основы имитационного  моделирования

Лабораторная работа № 1

 

Тема: Генерация случайных величин.

Построение простейшей имитационной модели в Excel

 

 

Для решения многих экономических задач с использованием имитационных моделей применяется генерация значений величин, которые имеют случайный характер. Создание ряда случайных величин бывает необходимо во многих задачах имитационного моделирования. Часто входные данные для таких задач неизвестны заранее, но могут быть описаны какими-либо вероятностными законами. Например, в задаче о банке: «поток клиентов, входящих в систему подчиняется экспоненциальному распределению с интервалом в 7 минут». То есть, мы не знаем моменты прихода каждого клиента, а знаем лишь общую закономерность, которой они подчиняются. В таких случаях применяется генерация случайных величин или метод Монте-Карло. Этот метод реализован во всех программах имитационного моделирования и в табличных процессорах.

В задачах обычно описывается  характер изменения случайной величины. То есть, определено является величина дискретной или непрерывной, а также указывается статистическое распределение, которому величина подчиняется. Например, условие задачи может звучать так: «Поток автомобилей, прибывающих на диагностику, распределен    по закону Пуассона и имеет интенсивность λ=0,85 (автомобиля в час). Время диагностики автомобиля распределено по показательному закону и в среднем равно 1,05 час».

Для генерации случайных чисел в Excel используется встроенная функция СЛЧИС(). Эта функция возвращает случайное число равномерно распределенное на отрезке [0, 1]. Кроме того, существует возможность воспользоваться опцией Сервис\ Анализ данных \ Генерация случайных чисел. В этом случае можно выбрать тип распределения, количество переменных и число случайных чисел, диапазон их изменения, вероятность случайной величины.

 

Задание 1. Сгенерировать четыре различных случайных величины с помощью Excel:

 

а) двадцать значений случайной величины Х, равномерно распределенной на отрезке [0,1], пользуясь функцией СЛЧИС ();

б) двадцать значений случайной величины Y, равномерно распределенной на отрезке [а, b] (а, b — заданные числа);

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

Z	2	7	15
Р	0,3	0,5	0,2

 

 

 

г) двадцать значений дискретной случайной величины W, имеющей распределение Пуассона.

Рекомендации к выполнению

1. Для генерации Х Достаточно ввести в ячейку A2 формулу =СЛЧИС() и скопировать ее в диапазон А3:А21 (табл.1).

 

Таблица 1

Образец заполнения таблицы  в  Excel

 

Случайная величина X	Случайная величина Y	Случайная величина E	Случайная величина N	Случайная величина D	Случайная величина В	Случайная величина Р
0,286	3,16	0,215807	-3,02301	2	7	0
0,867	2,699	0,521429	0,160065	15	5	4
…	…	…	…	…	…	1

 

2. Величина Y будет иметь равномерное распределение на отрезке [а, b]. Выберите самостоятельно значения а и b и введите их в ячейки таблицы. Не забудьте в формуле ссылку на ячейки а и b сделать абсолютными. Общая формула для Y будет выглядеть так:

=a+(b-a)*СЛЧИС().

 

3. Величина Е должна иметь экспоненциальное распределение (ЭКСПРАСП- статистическая функция) с параметром l=0,85. В качестве параметра Х используйте случайную величину Х, а для параметра «интегральная» выберите значение 1.

 

4. Величина N должна иметь нормальное распределение. Для ее генерации используйте команду Сервис \ Анализ данных \ Генерация случайных чисел, выберите нормальное распределение, укажите значение случайного рассеяния 1.

 

 5. Для дискретной случайной величины D определим ее функцию распределения следующим образом:

В итоге получена дискретная случайная величина с требуемым рядом распределения, поэтому запишем  формулу:

=ЕСЛИ (Х<0,3;2;ЕСЛИ (Х<0,8;7;15)).

6. Сгенерируйте биноминальную случайную величину В с вероятностью 0,2 и случайным рассеянием 1.

7.Чтобы сгенерировать случайную величину Р с распределением Пуассона надо воспользоваться командой генерация случайных чисел из меню Сервис\Анализ данных\ Генерация случайных чисел, выбрать распределение Пуассона, указать значение λ=0,85.

 

Задание 2. Покупатель ежедневно приобретает продукты в одном из трех магазинов. В магазине № 1 — с вероятностью 0,5 в магазине № 2 —с вероятностью 0,3, в магазине № 3 - с вероятностью 0,2. Количество приобретенного товара в обоих случаях является равномерно распределенной случайной величиной:

• магазин № 1 в промежутке [4; 13];
• магазин № 2 в промежутке [3; 12];
• магазин № 3 в промежутке [2; 11].

Цена товаров в каждом из трех случаев является случайной величиной с равномерным распределением на отрезке [25; 150].

 

Произвести 30 имитаций, (один месяц работы) для определения стоимости приобретенных продуктов. Получить оценки для математического ожидания стоимости приобретенного товара и ее среднего квадратичного отклонения. Определить, сколько раз стоимость товаров оказалась больше 1000 рублей.

Решение оформить в виде таблицы:

Результаты экспериментов
№ эксп.	№ магазина	Количество	Цена товара	Стоимость
1	1	8	48,77р.	390,19р.
2	2	7	135,83р.	950,80р.
3	3	5	30,94р.	154,72р.

 

Рассчитать общую стоимость продуктов, приобретенных покупателем в каждом из трех магазинов за месяц. Для этого нужно использовать функции Excel СУММЕСЛИ и СЧЕТЕСЛИ.

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

Примечание

• При заполнении столбца, содержащего № магазина можно воспользоваться формулой:          =ЕСЛИ (СЛЧИС()<0,2;3;ЕСЛИ (СЛЧИС()<=0,5;2;1)).
• При заполнении столбца Количество используйте формулу:

=ЕСЛИ(B2=1;ОКРВВЕРХ(4+9*СЛЧИС();1);ЕСЛИ(B2=2;

ОКРВВЕРХ(3+9*СЛЧИС();1);2+ОКРВВЕРХ(9*СЛЧИС();1)))

• При заполнении столбца Цена товара используйте анализ данных, тип распределения – равномерный.