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

Моделирование случайных величин в табличном процессоре 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 + C) mod M,  (1)

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

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

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

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

В хорошем ГСЧ в  интервал (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). Тогда

χ²_реал. = (n₁ – p₁ · N)² + (n₂ – p₂ · N)² + … + (n_k – p_k · N)².

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

Если χ²_реал. много больше χ²_теор., то генератор не удовлетворяет требованию равномерного распределения, так как наблюдается слишком большой разброс значений n_i. Если χ²_реал. мал, то такую последовательность нельзя назвать случайной. Например, для последовательности 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 χ² = 0,  т.е. последовательность идеально равномерна, но далеко не случайна. Таким образом, если χ²_реал. много больше или χ²_реал. много меньше любого χ²_теор. в строке (см. таблицу), то гипотеза о случайности равномерного генератора не выполняется. Если χ²_реал. лежит между значениями χ²_теор. двух рядом стоящих столбцов, то гипотеза о случайности равномерного генератора выполняется с вероятностью p. Чем ближе p к значению 50%, тем лучше.

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

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

В ячейки В9:В58 введена  формула (1) (см. строку формул на рис. 1.1), которая позволяет получить числа из интервала [0; М–1]. В ячейки С9:С58 введена формула ={B9:B58/($E$5–1)}, которая дает последовательность ПСЧ из диапазона [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].

2. Моделирование в табличном процессоре 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, т.е. перейдем от нее к величине

  (2)

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

.  (3)

На рис. 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)).

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

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

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

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

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

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

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

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

Пусть

.

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

,

.  (4)

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

.

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