Лабораторная работа №2. Тема: моделирование случайных величин в табличном процессоре Excel

Тема: моделирование случайных величин в табличном процессоре Excel.

Цель работы. Научиться получать конечный набор значений случайной величины для разных законов распределения.

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

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

В Excel есть довольно много средств генерирования значений случайных ве­личин, имеющих различные распределения.

Функция СЛЧИС, выдает случайные числа, которые равномерно распределены на интервале [0, 1]. Ее синтаксис — СЛЧИС(), т.е. она не имеет аргументов.

Функцию СЛЧИС можно использовать в формулах мас­сивов для генерирования диапазонов случайных чисел. Сначала выделяется Диапазон ячеек, затем, не сни­мая выделения, вводится формула =СЛЧИС() и после этого нажимается комбинация клавиш <Ctrl+Shift+Enter>.

Необходимо отметить, что значения формул, содержащих функ­цию СЛЧИС, перевычисляются при каждом пересчете рабо­чего листа, например при вводе любого значения в ячейку или при удалении чего-либо. Это свойство данной функции полезно, например, в имитационном моделировании. Одна­ко в других случаях оно может замедлять работу в Excel или быть просто излишним. Чтобы зафиксировать значения, вычисляемые с помощью функции СЛЧИС, на­до выделить диапазон ячеек, содержащий эти значения, и скопировать его (Правка Копировать). Затем, не снимая выделения диапазона, следует выполнить ко­манду Правка Специальная вставка, в открывшемся диа­логовом окне Специальная вставка установить переключа­тель З начения. В ячейки выделенного диапазона вместо фор­мул будут записаны числовые значения.

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

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

Аргумент Нижняя_граница задает нижнюю границу интервала изменения слу­чайной величины, аргумент Верхняя_граница — верхнюю границу этого интер­вала. Если значения аргументов дробные, они округляются до ближайших це­лых. Если значение аргумента Нижняя_граница больше значения аргумента Верхняя_граница, функция возвращает значение ошибки #ЧИСЛО!.

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

Средство Генерация случайных чисел из надстройки Пакет анализа (команда Данные/анализ данных), предоставляет возможность генерировать случайные числа, которые имеют следующие распределения.

- Равномерное. Генерируется последовательность равномерно распределенных случайных чисел в заданном интервале.

- Нормальное. Генерируется последовательность случайных чисел, под­чиняющихся нормальному распределению. Задается математическое ожидание и среднеквадратическое отклонение.

- Бернулли. Генерируется последовательность случайных чисел, принимающих только значение 0 или 1, в зависимости от заданной вероятности успеха (исхода "1").

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

Между способами вычисления случайных чисел, полученных с помощью функции СЛЧИС (соответствующие формулы приведены в следующих разделах) и с помощью средства Генерация случайных чисел, в частности равномерно рас­пределенных на интервале [0, 1], имеются существенные различия.

Первое раз­личие заключается в том, что функцию СЛЧИС можно непосредственно исполь­зовать в формулах (в том числе в формулах массивов) как аргумент формулы или другой функции, тогда как для того, чтобы использовать в формулах слу­чайные числа, полученные с помощью средства Генерация случайных чисел, сна­чала необходимо их получить, т.е. записать в отдельном диапазоне ячеек, и только затем использовать в формулах.

Второе отличие состоит в том, что формулы, содержащие функцию СЛЧИС, пересчитываются при каждом пересчете рабочего листа (например, при любом вводе значения в ячейку или при удалении чего-либо, или при нажатии клави­ши < F9 >), а значения, полученные с помощью средства Генерация случайных чисел, фиксированы — при необходимости получения новой выборки на месте старой, следует еще раз вызвать и применить это средство.

Метод обратных функций моделирования случайных величин. Функция распределения случайной величины X в точке U имеет вид:

U

Y = F(u) = ∫ f(x)dx,,

-∞

где f(x) плотность вероятности случайной величины X. Это выражение позволяет для случайной величины Х определить её вероятность. Для получения случайных величин имеющих заданный закон распределения можно использовать метод обратных функций, суть которого в обратном преобразовании x = F^-1(y), где F^-1 - функция, обратная функции F. Это преобразование сводится к решению интегрального уравнения относительно х_i.

Т.е. равномерное распределение преобразуется в требуемое. В Excel есть несколько функций, возвращающих значения обратных функций для различных распределений. Например, функции:

· НОРМОБР. Вычисляет значение функции, обратной к функции нормального распределения.

· НОРМСТОБР. Вычисляет значение функции, обратной к функции стандартного нормального распределения.

· СТЬЮДРАСПОБР. Вычисляет значение функции» обратной к функции распределения Стьюдента.

Таким образом, формула =ФУНКЦИЯ(СЛЧИС();...), где ФУНКЦИЯ обозначает одну из вышеперечисленных будет генерировать последовательность случайных чисел, которые имеют распределение, определяемое данной функцией.

Для получения непрерывных случайных величин, принимающих любые значения на интервале между дву­мя точками а и b (a<b) с равной вероятностью, можно использовать выражение:

a + (b -а)*СЛЧИС()

Поиск по сайту: