Моделирование случайных величин с заданным законом распределения в excel

Обновлено: 07.01.2025

Для формирования «истинно» случайных чисел используются аналого-цифровые преобразователи естественных источников случайных шумов (шумы электронных и полупроводниковых устройств, радиоактивный распад и т.п.) [1]. Случайные числа, генерируемые на ЭВМ (аппаратно или программно), называются псевдослучайными (ПСЧ). Последовательность псевдослучайных чисел всегда бывает периодической, т.е. в ней можно выделить повторяющиеся фрагменты.
К псевдослучайным числам предъявляются следующие требования:
Псевдослучайные числа должны быть равномерно распределены на интервале [0; 1].
Псевдослучайные числа должны быть независимыми.
Период последовательности псевдослучайных чисел должен быть большим.

Прикрепленные файлы: 1 файл

Случайные числа и метод Монте-Карло.doc

Моделирование случайных величин в табличном процессоре Microsoft Office Excel

При построении компьютерных моделей часто бывает необходимо учитывать влияние случайных факторов. Эти факторы могут фигурировать в модели как случайные события, случайные величины или случайные функции. В основе всех приемов моделирования случайных факторов лежит использование случайных величин, имеющих равномерное распределение на интервале [0; 1] (базовых случайных чисел).

Для формирования «истинно» случайных чисел используются аналого-цифровые преобразователи естественных источников случайных шумов (шумы электронных и полупроводниковых устройств, радиоактивный распад и т.п.) [1]. Случайные числа, генерируемые на ЭВМ (аппаратно или программно), называются псевдослучайными (ПСЧ). Последовательность псевдослучайных чисел всегда бывает периодической, т.е. в ней можно выделить повторяющиеся фрагменты.

К псевдослучайным числам предъявляются следующие требов ания:

1. Псевдослучайные числа должны быть равномерно распределены на интервале [0; 1].
2. Псевдослучайные числа должны быть независимыми.
3. Период последовательности псевдослучайных чисел должен быть большим.
4. Последовательность псевдослучайных чисел должна быть воспроизводимой.

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

Генерация произвольного случайного числа состоит из двух этапов:

1. генерация случайного числа r_i, равномерно распределенного в интервале от 0 до 1 (генерация базового СЧ);
2. преобразование базовых СЧ r_i в случайные числа x_i, которые распределены по заданному закону распределения или в заданном интервале.

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

Табличный процессор Excel содержит функцию СЛЧИС(), которая возвращает равномерно распределенное случайное число r_i (0 ≤ r_i ≤ 1). Случайные числа, полученные с помощью данной функции, имеют, по крайней мере, три недостатка:

1. новое случайное число возвращается при каждом вычислении рабочего листа;
2. нельзя задавать параметры последовательности СЧ;
3. последовательность случайных чисел нельзя повторить.

Первый недостаток можно устранить, если после ввода в формульной строке =СЛЧИС() нажать клавишу F9 (формула заменится на само случайное число). Второй и третий недостатки устранить нельзя.

От перечисленных недостатков свободна программная генерация псевдослучайных чисел, например, линейным конгруэнтным методом [2, 3]. В основе этого метода лежит рекуррентное соотношение:

где r_i, r_i-1 — очередное и предыдущее случайные числа, соответственно. В рекуррентном соотношении (1) начальное значение r₀ и константы А и С — целые числа из интервала [0; M), а М — большое целое положительное число. Последовательность чисел, генерируемая таким алгоритмом, периодична с периодом, не превышающим М.

Подбор параметров r₀, А, С и М — не простая задача и, обычно, занимает много времени. Поэтому студентам предлагается подобрать лишь один параметр, остальные задаются преподавателем. Например, при А = 16807, С = 0 и М = 2147483647 необходимо подобрать значение r₀, так чтобы последовательность ПСЧ была как можно более случайной, независимой и равномерно распределенной на интервале [0, 1]. Таким образом, при подборе параметра необходимо контролировать статистические характеристики, частотные характеристики, критерий «хи-квадрат» и коэффициент корреляции.

Генератор СЧ должен выдавать близкие к следующим значения статистических параметров, характерных для равномерного случайного закона:

1. математическое ожидание (N — общее число сгенерированных чисел);
2. дисперсия
3. среднеквадратичное отклонение

В хорошем ГСЧ в интервал (m_r – σ_r; m_r + σ_r) должно попадать около 57.7% всех выпавших случайных чисел, так как (0,5 + 0,2887) – (0,5 – 0, 2887) = 0,5774. Кроме этого, количество чисел, попавших в интервал (0; 0,5), должно быть примерно равно количеству чисел, попавших в интервал (0,5; 1).

Критерий «хи-квадрат» позволяет узнать, насколько созданный (реальный) ГСЧ близок к эталону ГСЧ. Пусть интервал [0; 1] разбит на k интервалов и в каждый интервал попадет по n_i чисел (n₁ + n₂ + … + n_k = N). Тогда

Теоретические значения «хи-квадрат» (χ 2 _теор.) для (N – 1) ≤ 30 приводятся в таблицах (см., например, [1]), а для (N – 1) > 30 вычисляются по формуле χ 2 _теор. @ (N – 1) + sqrt(2∙(N – 1)) · x_p + 2/3 · x 2 _p – 2/3. Для N = 50 вычисления по этой формуле дают следующий результат:

Если χ 2 _реал. много больше χ 2 _теор., то генератор не удовлетворяет требованию равномерного распределения, так как наблюдается слишком большой разброс значений n_i. Если χ 2 _реал. мал, то такую последовательность нельзя назвать случайной. Например, для последовательности 0,1; 0,2; 0,3; 0,4; 0,5; 0,6; 0,7; 0,8; 0,9; 0,1; 0,2; 0,3; 0,4; 0,5; 0,6; 0,7; 0,8; 0.9 χ 2 = 0, т.е. последовательность идеально равномерна, но далеко не случайна. Таким образом, если χ 2 _реал. много больше или χ 2 _реал. много меньше любого χ 2 _теор. в строке (см. таблицу), то гипотеза о случайности равномерного генератора не выполняется. Если χ 2 _реал. лежит между значениями χ 2 _теор. двух рядом стоящих столбцов, то гипотеза о случайности равномерного генератора выполняется с вероятностью p. Чем ближе p к значению 50%, тем лучше.

Ниже приведены фрагменты лабораторной работы «Моделирование последовательностей псевдослучайных чисел в Excel».

Рис. 1.1. Фрагменты последовательности ПСЧ, сгенерированной в Excel
линейным конгруэнтным методом.

В ячейки В9:В58 введена формула (1) (см. строку формул на рис. 1.1), которая позволяет получить числа из интервала [0; М–1]. В ячейки С9:С58 введена формула =, которая дает последовательность ПСЧ из диапазона [0; 1] (всего 50 чисел). В столбцах D–M с помощью функции ЕСЛИ() фиксируется попадание СЧ в заданный интервал. На рис.1.2 приведен фрагмент таблицы Excel с расчетами статистических и частотных характеристик последовательности ПСЧ, представленной на рис. 1.1. Ячейки таблиц взаимосвязаны. Изменение значения любого параметра (например, r₀) вызывает пересчет значений всех характеристик, поэтому подбор параметра не занимает много времени.

Рис. 1.2. Фрагмент таблицы Excel с расчетами характеристик
последовательности ПСЧ.

Для вычисления коэффициента корреляции в рассмотрение вводится дополнительная последовательность ПСЧ s_i = r_i + t, где t — величина сдвига последовательности S относительно исходной последовательности R (см. рис 1.3).

Рис. 1.3. Вычисление коэффициента корреляции с помощью функции КОРРЕЛ.

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

На рисунках 1.1, 1.2 и 1.3 показан конечный результат подбора параметра r₀ при фиксированных значениях А, С и М. Анализ статистических характеристик (рис. 1.2) показывает, что полученная последовательность ПСЧ близка к идеальной. На рис. 1.4 изображена гистограмма относительных частот сгенерированной последовательности ПСЧ.

Рис. 1.4. Частотная гистограмма последовательности ПСЧ,
равномерно распределенных на отрезке [0; 1].

Если требуется, чтобы случайное число x находилось в интервале [a; b], отличном от [0; 1], нужно воспользоваться формулой x = a + (b – a) · r, где r — случайное число из интервала [0; 1] (см. рис. 1.5).

Рис. 1.5. Преобразование равномерно распределенного на интервале [0, 1] случайного числа r в случайное число x, распределенное в интервале [a, b].

1. Моделирование в табличном процессоре Excel случайной величины с заданным законом распределения

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

1. Моделирование нормально распределенной случайной величины

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

Рассмотрим метод моделирования нормально распределенных СВ, основанный на центральной предельной теореме теории вероятности.

Согласно центральной предельной теореме, если случайные величины ξ₁, ξ₂, …, ξ_n независимы, одинаково распределены и их математическое ожидание и дисперсия конечны, то при увеличении n закон распределения суммы ξ₁ + ξ₂ + … + ξ_n приближается к нормальному. Опыт показывает, что при сложении всего 12 равномерно распределенных на интервале [0, 1] случайных чисел, получается случайная величина, которая с точностью, достаточной для большинства прикладных задач, может считаться нормальной.

Алгоритм моделирования этим методом нормально распределенной случайной величины состоит из трех пунктов:

1. Сложить 12 равномерно распределенных ПСЧ r_i.
2. Пронормировать полученную сумму, т.е. получить нормализованную нормально распределенную СВ X с математическим ожиданием М(Х) = 0 и средним квадратичным отклонением σ = 1.
3. Получить нормально распределенную случайную величину с требуемыми значениями математического ожидания и среднеквадратичного отклонения.

Рассмотрим этот алгоритм подробнее.

Пусть , где r_i — независимые равномерно распределенные на интервале [0, 1] случайные величины. В предыдущем разделе было показано, что математическое ожидание и дисперсия равномерно распределенной на интервале [0, 1] случайной величины R соответственно равны:

Тогда математическое ожидание суммы Z равно

а ее дисперсия D(Z) равна

Нормализуем сумму Z, т.е. перейдем от нее к величине

Из нормализованного распределения можно получить любое другое нормальное распределение с заданными параметрами. Пусть необходимо получить нормально распределенную случайную величину x_i с математическим ожиданием М(х) = m и среднеквадратичным отклонением σ = s. Тогда из формулы (2) получим

На рис. 2.1.1 показан фрагмент смоделированной последовательности случайных величин с нормальным законом распределения. В качестве равномерно распределенной на интервале [0, 1] последовательности ПСЧ взята последовательность, фрагменты которой показаны на рис. 1.1.

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

В ячейки D9:D86 (см. рис. 2.1.1) введена формула =$G$4+$H$4*(СУММ(C9:C20)-6), соответствующая расчетной формуле (3). При m = 0 и s = 1 она дает последовательность нормализованных СВ, подчиняющихся нормальному закону распределения. В столбцах E–X с помощью функции ЕСЛИ() фиксируется попадание СВ в заданный интервал (см. строку формул на рис. 2.1.1).

Рис. 2.1.2. Частотная гистограмма последовательности нормализованных (М(х) =0 и σ = 1) нормально распределенных случайных величин

Рис. 2.1.3. Частотная гистограмма последовательности СВ, распределенных по нормальному закону с параметрами М(х) =0 и σ = 3,5.

Рис. 2.1.4. Частотная гистограмма последовательности СВ, распределенных по нормальному закону с параметрами М(х) = 2 и σ = 3,5.

На рис. 2.1.2, 2.1.3 и 2.1.4 показаны частотные гистограммы случайных величин, распределенных по нормальному закону с различными параметрами (коэффициентами m = М(х) и s = σ в формуле (3)).

1. Моделирование экспоненциальной случайной величины

Случайная величина x, распределенная по экспоненциальныму (показательному) закону, описывается плотностью распределения:

Экспоненциальному распределению, как правило, подчиняется случайный интервал времени τ между поступлениями заявок в систему массового обслуживания. Поэтому важно уметь моделировать потоки заявок разной интенсивности λ. Математическое ожидание и дисперсия экспоненциально распределенной случайной величины τ равны:

М(τ) = 1/λ и D(τ) = 1/λ 2 .

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

Первый метод называется методом инверсии и базируется на следующей теореме.

Теорема. Пусть — функция распределения вероятностей случайной величины y, а — функция, обратная W(y). Тогда случайная величина имеет заданный закон распределения W(y), если случайная величина x равномерно распределена от 0 до 1.

Выведем расчетную формулу для моделирования СВ, распределенной по экспоненциальному закону.

Для получения обратной функции приравняем x к W(y) и из полученного уравнения выразим величину y:

Перейдем от обозначений y и x к обозначениям τ и R, соответственно. Тогда равенство (4) примет вид:

Случайная величина R распределена равномерно на отрезке [0; 1]. Величина (1–R) распределена так же, поэтому окончательно можно записать:

Распределение вероятностей – одно из центральных понятий теории вероятности и математической статистики. Определение распределения вероятности равносильно заданию вероятностей всех СВ, описывающих некоторое случайное событие. Распределение вероятностей некоторой СВ, возможные значения которой x ₁, x ₂, … x_n образуют выборку, задается указанием этих значений и соответствующих им вероятностей p ₁, p ₂,… p_n . ( p_n должны быть положительны и в сумме давать единицу).

В данной лабораторной работе будут рассмотрены и построены с помощью MS Excel наиболее распространенные распределения вероятности: биномиальное и нормальное.

1 Биномиальное распределение

Представляет собой распределение вероятностей числа наступлений некоторого события («удачи») в n повторных независимых испытаниях, если при каждом испытании вероятность наступления этого события равна p . При этом распределении разброс вариант (есть или нет события) является следствием влияния ряда независимых и случайных факторов.

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

В Excel функция БИНОМРАСП применяется для вычисления вероятности в задачах с фиксированным числом тестов или испытаний, когда результатом любого испытания может быть только успех или неудача.

Функция использует следующие параметры:

БИНОМРАСП (число_успехов; число_испытаний; вероятностъ_успеха; интегральная) , где

число_успехов — это количество успешных испытаний;

число_испытаний — это число независимых испытаний (число успехов и число испытаний должны быть целыми числами);

вероятность_ успеха — это вероятность успеха каждого испытания;

интегральный — это логическое значение, определяющее форму функции.

Если данный параметр имеет значение ИСТИНА (=1), то считается интегральная функция распределения (вероятность того, что число успешных испытаний не менее значения число_ успехов);

если этот параметр имеет значение ЛОЖЬ (=0), то вычисляется значение функ­ции плотности распределения (вероятность того, что число успешных испытаний в точности равно значению аргумента число_ успехов).

Пример 1. Какова вероятность того, что трое из четырех новорож­денных будут мальчиками?

1. Устанавливаем табличный курсор в свободную ячейку, например в А1. Здесь должно оказаться значение искомой вероятности.

2. Для получения значения вероятности воспользуемся специальной функцией: нажимаем на панели инструментов кнопку Вставка функции ( fx ) .

3. В появившемся диалоговом окне Мастер функций - шаг 1 из 2 слева в поле Катего­рия указаны виды функций. Выбираем Статистическая. Справа в поле Функция выбираем функцию БИНОМРАСП и нажимаем на кнопку ОК.

Появляется диалоговое окно функции. В поле Число_ s вводим с клавиатуры количество успешных испытаний (3). В поле Испытания вво­дим с клавиатуры общее количество испытаний (4). В рабочее поле Вероятность_ s вводим с клавиатуры вероятность успеха в отдельном испытании (0,5). В поле Интегральный вводим с клавиатуры вид функции распределения — интегральная или весовая (0). Нажимаем на кнопку ОК.

В ячейке А1 появляется искомое значение вероятности р = 0,25. Ровно 3 мальчика из 4 новорожденных могут появиться с вероят­ностью 0,25.

Если изменить формулировку условия задачи и выяснить вероятность того, что появится не более трех мальчиков, то в этом случае в рабочее поле Интегральный вводим 1 (вид функции распределения интегральный). Вероятность этого события будет равна 0,9375.

Задания для самостоятельной работы

1. Какова вероятность того, что восемь из десяти студентов, сдающих зачет, получат «незачет». (0,04)

2 . Нормальное распределение

Нормальное распределение - это совокупность объектов, в кото­ рой крайние значения некоторого признака — наименьшее и наибольшее — появ­ ляются редко; чем ближе значение признака к математическому ожиданию, тем чаще оно встречается. Например, распределение студентов по их весу приближа­ется к нормальному распределению. Это распределение имеет очень широкий круг приложений в статистике, включая проверку гипотез.

Диаграмма нормального распределения симметрична относительно точки а (математического ожидания). Ме­диана нормального распределения равна тоже а. При этом в точке а функция f(x) достигает своего максимума, который равен

В Excel для вычисления значений нормального распределения используются фун­кция НОРМРАСП, которая вычисляет значения вероятности нормальной функции распределения для указанного среднего и стандартного отклонения.

Функция имеет параметры:

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

х — значения выборки, для которых строится распределение;

среднее — среднее арифметическое выборки;

стандартное_откл — стандартное отклонение распределения;

интегральный — логическое значение, определяющее форму функции. Если интегральная имеет значение ИСТИНА(1), то функция НОРМРАСП возвращает интег­ральную функцию распределения; если это аргумент имеет значение ЛОЖЬ (0), то вычисляет значение функция плотности распределения.

Если среднее = 0 и стандартное_откл = 1, то функция НОРМРАСП возвращает стан­дартное нормальное распределение.

Пример 2 . Построить график нормальной функции распределения f ( x ) при x , меняющемся от 19,8 до 28,8 с шагом 0,5, a =24,3 и

1. В ячейку А1 вводим символ случайной величины х, а в ячейку B 1 — символ фун­кции плотности вероятности — f ( x ) .

2. Вводим в диапазон А2:А21 значе­ния х от 19,8 до 28,8 с шагом 0,5. Для этого воспользуемся маркером автозаполнения: в ячейку А2 вводим левую границу диапазона (19,8), в ячейку A3 левую границу плюс шаг (20,3). Выделяем блок А2:А3. Затем за правый нижний угол протягиваем мышью до ячейки А21 (при нажатой левой кнопке мыши).

3. Устанавливаем табличный курсор в ячейку В2 и для получения значения веро­ятности воспользуемся специальной функцией — нажимаем на панели инстру­ментов кнопку Вставка функции ( fx ) . В появившемся диалоговом окне Мастер функций - шаг 1 из 2 слева в поле Категория указаны виды функций. Выбираем Статистическая. Справа в поле Функция выбираем функцию НОРМРАСП. Нажимаем на кнопку ОК.

4. Появляется диалоговое окно НОРМРАСП. В рабочее поле X вводим адрес ячейки А2 щелчком мыши на этой ячейке. В рабочее поле Среднее вводим с клавиатуры значение математиче­ского ожидания (24,3). В рабочее поле Стандартное_откл вводим с клавиатуры значение среднеквадратического отклонения (1,5). В ра­бочее поле Интегральная вводим с клавиатуры вид функции распределения (0). Нажимаем на кнопку ОК.

5. В ячейке В2 появляется вероятность р = 0,002955. Указателем мыши за правый нижний угол табличного курсора протягиванием (при нажатой левой кнопке мыши) из ячейки В2 до В21 копируем функцию НОРМРАСП в диапазон В3:В21.

6. По полученным данным строим искомую диаграмму нормальной функции рас­пределения. Щелчком указателя мыши на кнопке на панели инструментов вызы­ваем Мастер диаграмм. В появившемся диалоговом окне выбираем тип диаграммы График, вид — левый верхний. После нажатия кнопки Далее указываем диапазон данных — В1:В21 (с помощью мыши). Проверяем, положение переключателя Ряды в: столбцах. Выбираем закладку Ряд и с помощью мыши вводим диапазон подписей оси X: А2:А21. Нажав на кнопку Далее, вводим названия осей Х и У и нажимаем на кнопку Готово.


Рис. 1 График нормальной функции распределения

Получен приближенный график нормальной функции плотности распределения (см. рис.1).

Задания для самостоятельной работы

1. Построить график нормальной функции плотности распределения f ( x ) при x , меняющемся от 20 до 40 с шагом 1 при

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

Еще одним аспектом использования законов распределения вероятностей являет­ ся генерация случайных величин. Бывают ситуации, когда необходимо получить пос­ледовательность случайных чисел. Это, в частности, требуется для моделирования объектов, имеющих случайную природу, по известному распределению вероятно­ стей.

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

В MS Excel для генерации СВ используются функции из категории Математические :

СЛЧИС () – выводит на экран равномерно распределенные случайные числа больше или равные 0 и меньшие 1;

СЛУЧМЕЖДУ (ниж_граница; верх_граница) – выводит на экран случайное число, лежащее между про­ извольными заданными значениями.

В случае использования процедуры Генерация случайных чисел из пакета Анализа необходимо запол­нить следующие поля:

- число переменных вводится число столбцов значений, которые необходимо разместить в выходном диапазоне. Если это число не введено, то все столбцы в выходном диапазоне будут заполнены;

- число случайных чисел вводится число случайных значений, которое необ­ ходимо вывести для каждой переменной, если число случайных чисел не будет введе­ но, то все строки выходного диапазона будут заполнены;

- в поле распределение необходимо выбрать тип распределения, которое следует использовать для генерации случайных переменных:

1. равномерное - характеризуется вер x ней и нижней границами. Переменные из­ влекаются с одной и той же вероятностью для всех значений интервала.

2. нормальное — характеризуется средним значением и стандартным отклонени­ ем. Обычно для этого распределения используют среднее значе­ ние 0 и стандартное отклонение 1.

3. биномиальное — характеризуется вероятностью успеха (величина р) для неко­ торого числа попыток. Например, можно сгенерировать случайные двухальтер нативные переменные по числу попыток, сумма которых будет биномиальной случайной переменной;

4. дискретное — характеризуется значением СВ и соответствующим ему интервалом вероятности, диапазон должен состоять из двух столбцов: левого, содержаще­ го значения, и правого, содержащего вероятности, связанные со значением в дан­ ной строке. Сумма вероятностей должна быть равна 1;

5. распределения Бернулли, Пуассона и Модельное.

- в поле случайное рассеивание вводится произвольное значение, для которого необ­ ходимо генерировать случайные числа. Впоследствии можно снова использовать это значение для получения тех же самых случайных чисел.

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

1. Пронумеруем первые блюда по порядку: 1 — уха, 2 — щи, 3 — борщ, 4 — грибной суп. Введем числа 1-4 в диапазон А2:А5 рабочей таблицы.

2. Укажем желаемую вероятность появления каждого первого блюда. Пусть все блюда будут равновероятны (р=1/4). Вводим число 0,25 в диапазон В2:В5.

4. Указываем выходной диапазон и нажимаем ОК. В столбце С появляются случайные числа: 1, 2, 3, 4.

Задание для самостоятельной работы

1. Сформировать выборку из 10 случайных чисел, лежащих в диапазоне от 0 до 1.

2. Сформировать выборку из 20 случайных чисел, лежащих в диапазоне от 5 до 20.

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

4. Составить расписание внеклассных мероприятий на неделю для случайного проведения: семинаров, интеллектуальных игр, КВН и спец. курса.

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

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

Нормальное распределение

В Excel достаточно удобно работать с нормальным распределением с помощью формул НОРМ.РАСП (NORM.DIST) и НОРМ.ОБР (NORM.INV). Первая функция позволяет считать доверительные интервалы, а вторая - генерировать нормальные распределения с произвольным мат. ожиданием и стандартным отклонением.

Треугольное распределение

Как сгенерировать в Excel

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

После добавления данного кода в Excel появится возможность написать формулу =TRDIST(random,min,max,mean)

Первый аргумент - random - случайная величина распределенная равномерно от 0 до 1. (функция СЛЧИС() либо СЛЧИСМЕЖДУ(0,1)).

Второй и третий аргументы - min. max - минимум и максимум функции распределения.

Третий аргумент - mean - мат. ожидание.

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

В каких случаях применяется

При моделировании случайных процессов чаще всего используется нормальное или log-нормальное распределения, однако в некоторых случаях оправдано использование треугольного распределения. Один из примеров - вариативность случайной величины строго ограничена определённым диапазоном. Когда такое бывает? Допустим, что мы строим модель DCF для оценки денежного потока компании и для симуляции монте-карло нам необходимо задать распределение EBIT margin. Очевидно, что в теории данная величина может принимать значения от -1 до 1, но на практике для большинства здоровых компаний она находится в диапазоне от 5% до 50% и здесь-то нам и может помочь треугольное распределение и пошльзовательская функция TRDIST.

Пусть имеется событие A, вероятность наступления которого равна P_A. Требуется разработать алгоритм, при многократном применении которого частота появления события стремилась бы к его вероятности.

Процедура моделирования простого события описывается алгоритмом, показанным на рис.1. Оператор 1 обращается к датчику случайных чисел (ДСЧ), генерирующему случайную величину z в интервале [0, 1]. Оператор 2 проверяет условие z < P_A. Если оно выполняется, считается, что произошло событие A (A=1), в противном случае считается, что произошло противоположное событие “не A” (A=0).

Рис.1. Блок-схема алгоритма моделирования простого события.

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

Моделирование полной группы несовместимых событий

Пусть имеется полная группа несовместимых событий A₁, A₂,…, A_k с вероятностями P₁, P₂, …, P_k. Для полной группы несовместимых событий выполняется условие нормировки:

Разделим интервал [0, 1] на k отрезков, длины которых составляют P₁, P₂, …, P_k соответственно. Если случайное число, генерируемое датчиком случайных чисел с равномерным распределением в интервале [0, 1], попало, например, на участок P_i, то это должно означать, что произошло событие i (i = 1..k).

Процедура моделирования полной группы несовместимых событий описывается алгоритмом, блок-схема которого показана на рис.2. Оператор 1 обращается к датчику случайных чисел (ДСЧ), генерирующему случайную величину z в интервале [0, 1]. Условный оператор 2 проверяет условие попадания случайной величины z в интервал [0, L₁]. Если условие выполняется, то считается, что произошло событие A₁. Если условие в операторе 2 не выполняется, то алгоритм последовательно осуществляет проверку условий попадания случайной величины в другие интервалы.

Рис.2. Блок-схема алгоритма моделирования полной группы
несовместимых событий: .

Дискретные случайные величины

Дискретной случайной величиной называют случайную величину, которая принимает счетное число возможных значений x_k (k = 1, 2, …) с заданной вероятностью p_k каждое:

Законом распределения дискретной случайной величины X называют соответствие между ее возможными значениями и их вероятностями. Закон распределения дискретной случайной величины можно задать табличным, аналитическим и графическим способами.

При табличном задании закона распределения дискретной случайной величины первая строка таблицы содержит список ее возможных значений x_k, а вторая строка таблицы – их вероятности p_k:

X	x1	x2	…	xn
p	p1	p2	…	pn

причем . Данная таблица называется рядом распределения.

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

где X принимает целочисленные значения k, P_n(k) – вероятность наступления ровно k событий в n независимых испытаниях, - число сочетаний из n по k.

Интегральная функция распределения дискретной случайной величины X в общем случае имеет вид:

Из этой формулы, в частности, следует, что

т.е. функция распределения дискретной случайной величины испытывает скачки в точках x_k, для которых существует положительная вероятность события k>.

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

X
p	0,3	0,5	0,2
F	0,3	0,8	1,0

Рис.3. Полигон распределения вероятностей дискретной случайной величины.




Рис.4. Интегральная функция распределения дискретной случайной величины.

Видим, что функция распределения имеет скачки в тех точках, в которых случайная величина принимает ненулевые значения согласно ряду распределения, а величина скачка равна вероятности этого значения. Сумма величин всех скачков функции распределения равна 1. Очевидно, что по значениям интегральной функции распределения дискретной случайной величины можно легко восстановить ее ряд распределения.

Скачкообразное поведение функции распределения в точках X=x_k с учетом условия нормировки позволяет разработать простой алгоритм моделирования произвольной дискретной случайной величины с помощью датчика случайных чисел, равномерно распределенных на отрезке [0, 1].

Моделирование дискретной случайной величины

Дискретная случайная величина может быть задана табличной зависимостью:

X	x1	x2	…	xn
P	p1	p2	…	pn

Здесь p_j – вероятность того, что дискретная величина X примет значение x_j. При этом выполняется условие нормировки p₁ + p₂ + … +p_n = 1. Разделим интервал [0, 1] на n отрезков, длины которых пропорциональны вероятностям p_i. Если случайное число z, вырабатываемое датчиком случайных чисел, равномерно распределенных в интервале [0, 1], попадет в интервал p_j, то случайная величина X примет значение x_j. Таким образом, при моделировании дискретных случайных величин фактически используется та же процедура, что и при моделировании полной группы несовместимых событий.

Непрерывные случайные величины

Непрерывной случайной величиной называют случайную величину, которая может принимать любые значения из некоторого конечного или бесконечного интервала.

Плотностью распределения вероятностей f(x) непрерывной случайной величины называют первую производную от интегральной функции распределения: f(x) = F’(x).

По определению функции f(x) вероятность того, что непрерывная случайная величина X получит значение, принадлежащее интервалу [a, b], равна:

Зная плотность вероятностей f(x), можно найти интегральную функцию распределения вероятностей:

Плотность распределения вероятностей непрерывной случайной величины обладает следующими свойствами:

1. Плотность распределения вероятностей неотрицательна: f(x) ³ 0.

2. Несобственный интеграл от плотности распределения вероятностей в пределах
[- ¥, ¥] равен 1: . В частности, если все возможные значения случайной величины принадлежат интервалу [a, b], то .

Моделирование непрерывной случайной величины

Пусть имеется некоторая непрерывная случайная величина x, заданная интегральной функцией распределения F(x). Можно доказать, что значения этой функции равномерно распределены и интервале [0, 1]. Поэтому между случайной величиной z, равномерно распределенной в этом интервале, и функцией распределения исходной случайной величины x существует взаимно однозначное соответствие, т.е.

Отсюда следует, что

где F -1 - обратная функция.

Следовательно, если уравнение (2) имеет аналитическое решение, то для моделирования случайной величины x можно использовать датчик случайных чисел, генерирующий случайную величину z, и затем найти соответствующее значение x по формуле (2).

Некоторые законы распределения могут быть смоделированы средствами EXCEL. Программа моделирования включена в состав встроенного в EXCEL специального пакета программ (”Пакет анализа”). Этот пакет устанавливается в меню ”Сервис” – ”Надстройки” (рис. 2.9).

После установки пакета в меню Сервис появляется строчка ”Анализ данных” (рис. 2.10). Выберите в меню строчку ”Анализ данных”. По этой команде раскрывается список программ, входящих в пакет (рис. 2.11).




Рис. 2.9. Установка программ ”Пакет анализа”




Рис. 2.10. Вызов пакета ”Анализ данных”




Рис. 2.11. Список программ пакета ”Анализ данных”




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




Рис. 2.12. Диалоговое окно моделирования нормального закона распределения

3. Предварительный анализ данных одномерных признаков

3.1. Расчет средних значений и дисперсии одномерного признака

Прежде всего, по данным одномерного признака, рассчитываются две статистические характеристики: среднее значение и дисперсия.

Среднее значение является оценкой математического ожидания случайной величины. Запишем формулу расчета среднего значения признака в принятых обозначениях. Форму для расчета среднего первого признака таблицы данных будет иметь вид:




(3.1)

Оценка дисперсии признака первого признака производится по формуле:




(3.2)

В пакете EXCEL расчет среднего значения производится с помощью функции СРЗНАЧ. При вызове функции указывается диапазон ячеек таблицы данных, для которого необходимо рассчитать среднее значение. В нашем случае один из столбцов таблицы данных (рис. 3.1).

В пакете EXCEL расчет оценки дисперсии признака производится с помощью функции ДИСП. При вызове функции указывается диапазон ячеек таблицы данных, для которого необходимо рассчитать оценка. В нашем случае один из столбцов таблицы данных (рис. 3.2). В EXCEL есть еще одна функция расчета дисперсии. Это функция ДИСПР. Отличие этой функции заключается в том, что в формуле 3.2 деление производится не на величину n-1 , а на величину n.




Рис. 3.1. Диалоговое окно функции СРЗНАЧ




Рис. 3.2. Диалоговое окно функции ДИСП

На ряду с дисперсией для характеристики случайных признаков используется такая характеристика как среднеквадратичное отклонение. Среднеквадратичное отклонение связано с дисперсией соотношением:




(3.3)

Для вычисления квадратного корня в EXCEL математическая функция КОРЕНЬ.

3.2. Диапазон значений признака

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

Существует три способа оценки интервала значений признака.

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

Второй способ. Оценка интервала значений признака исходя из содержательного смысла признака. Исследователи чаще всего имеют дело не с абстрактными признаками, а с признаками смысл которых им хорошо известен. Например, если мы исследуем рост студентов университета. Для этого совершенно не обязательно измерять рост всех студентов (генеральная совокупность), а достаточно судить о росте студентов на основе выборки. Как в этом случае выбрать интервал значений. Маловероятно, что в выборку нам сразу попадется и самый рослый и самый низкорослый студенты. Но мы, исходя из здравого смысла, можем сказать, что самый низкорослый студент будет не меньше 152 сантиметров, а самый рослый студент будет не больше 210 сантиметров. Но мы, из того же здравого смысла, можем точно утверждать, что не будет среди студентов университета студента с ростом 5 сантиметров или 25 метров.

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

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




Рис. 3.3. Расчет диапазона значений признака

Первый шаг. Рассчитаем минимальное и максимальное значение признака:




(3.4)




(3.5)

Для расчета минимального и максимального значения признака используются функции EXCEL. Разница между минимальным значением случайного признака и максимальным значением называется размахом вариации.

Второй шаг. Рассчитывается левая граница (нижняя граница) признака и правая граница (верхняя граница) интервала значений признака:




(3.6)




(3.7)




То есть в качестве границ диапазона значений признака мы выбираем такие границы, которые немного меньше минимального значения и максимального значения выборки. Расширение диапазона регулируется множителем . Множитель целесообразно выбирать равным 0,005.

Третий шаг. Рассчитывается ширина диапазона значений признака:




(3.8)

Диапазон значений признака используется для расчета частотного рада. Расчет частотного ряда рассмотрим в следующем параграфе.

Читайте также:

  
• Как начертить шкаф в автокаде
  
• Как создать макрос в 1с
  
• Не найдены приложение сервер исходный файл или элемент powerpoint
  
• Зайдите в настройки браузера и снимите галочку напротив пункта заранее
  
• Нет батарейки биос в ноутбуке