Нормальное распределение. Непрерывные распределения в MS EXCEL

Рассмотрим Нормальное распределение. С помощью функции MS EXCEL НОРМ.РАСП() построим графики функции распределения и плотности вероятности. Сгенерируем массив случайных чисел, распределенных по нормальному закону, произведем оценку параметров распределения, среднего значения и стандартного отклонения.

Нормальное распределение (также называется распределением Гаусса) является самым важным как в теории, так в приложениях системы контроля качества. Важность значения Нормального распределения (англ. Normal distribution) во многих областях науки вытекает из Центральной предельной теоремы теории вероятностей. 

Определение: Случайная величина x распределена по нормальному закону, если она имеет плотность распределения:

СОВЕТ: Подробнее о Функции распределения и Плотности вероятности см. статью Функция распределения и плотность вероятности в MS EXCEL.

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

Примечание: О влиянии параметров μ и σ на форму распределения изложено в статье про Гауссову кривую, а в файле примера на листе Влияние параметров можно с помощью элементов управления Счетчик понаблюдать за изменением формы кривой.

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

В MS EXCEL, начиная с версии 2010, для Нормального распределения имеется функция НОРМ.РАСП(), английское название - NORM.DIST(), которая позволяет вычислить плотность вероятности (см. формулу выше) и интегральную функцию распределения (вероятность, что случайная величина X, распределенная по нормальному закону, примет значение меньше или равное x). Вычисления в последнем случае производятся по следующей формуле:

Вышеуказанное распределение имеет обозначение N(μ; σ). Так же часто используют обозначение через дисперсию  N(μ; σ2).

Примечание: До MS EXCEL 2010 в EXCEL была только функция НОРМРАСП(), которая также позволяет вычислить функцию распределения и плотность вероятности. НОРМРАСП() оставлена в MS EXCEL 2010 для совместимости.

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

Стандартным нормальным распределением называется нормальное распределение с математическим ожиданием μ=0 и дисперсией σ=1. Вышеуказанное распределение имеет обозначение N(0;1).

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

Любое нормальное распределение можно преобразовать в стандартное через замену переменной z=(x-μ)/σ. Этот процесс преобразования называется стандартизацией.

Примечание: В MS EXCEL имеется функция НОРМАЛИЗАЦИЯ(), которая выполняет вышеуказанное преобразование. Хотя в MS EXCEL это преобразование называется почему-то нормализацией. Формулы =(x-μ)/σ и =НОРМАЛИЗАЦИЯ(х;μ;σ) вернут одинаковый результат.

В MS EXCEL 2010 для стандартного нормального распределения имеется специальная функция НОРМ.СТ.РАСП() и ее устаревший вариант НОРМСТРАСП(), выполняющий аналогичные вычисления.

Продемонстрируем, как в MS EXCEL осуществляется процесс стандартизации нормального распределения N(1,5; 2).

Для этого вычислим вероятность, что случайная величина, распределенная по нормальному закону N(1,5; 2), меньше или равна 2,5. Формула выглядит так: =НОРМ.РАСП(2,5; 1,5; 2; ИСТИНА)=0,691462. Сделав замену переменной z=(2,5-1,5)/2=0,5, запишем формулу для вычисления Стандартного нормального распределения: =НОРМ.СТ.РАСП(0,5; ИСТИНА)=0,691462.

Естественно, обе формулы дают одинаковые результаты (см. файл примера лист Пример).

Обратите внимание, что стандартизация относится только к интегральной функции распределения (аргумент интегральная равен ИСТИНА), а не к плотности вероятности.

Примечание: В литературе для функции, вычисляющей вероятности случайной величины, распределенной по стандартному нормальному закону, закреплено специальное обозначение Ф(z). В MS EXCEL эта функция вычисляется по формуле
=НОРМ.СТ.РАСП(z;ИСТИНА). Вычисления производятся по формуле

В силу четности функции плотности стандартного нормального распределения f(x), а именно f(x)=f(-х), функция стандартного нормального распределения обладает свойством Ф(-x)=1-Ф(x). 

Обратные функции

Функция НОРМ.СТ.РАСП(x;ИСТИНА) вычисляет вероятность P, что случайная величина Х примет значение меньше или равное х. Но часто требуется провести обратное вычисление: зная вероятность P, требуется вычислить значение х. Вычисленное значение х называется квантилем стандартного нормального распределения.

В MS EXCEL для вычисления квантилей используют функцию НОРМ.СТ.ОБР() и НОРМ.ОБР().

Графики функций

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

Как известно, около 68% значений, выбранных из совокупности, имеющей нормальное распределение, находятся в пределах 1 стандартного отклонения (σ) от μ(среднего или математического ожидания); около 95% - в пределах 2-х σ, а в пределах 3-х σ находятся уже 99% значений. Убедиться в этом для стандартного нормального распределения можно записав формулу:

=НОРМ.СТ.РАСП(1;ИСТИНА)-НОРМ.СТ.РАСП(-1;ИСТИНА)

которая вернет значение 68,2689% - именно такой процент значений находятся в пределах +/-1 стандартного отклонения от среднего (см. лист График в файле примера).

В силу четности функции плотности стандартного нормального распределения: f(x)=f(-х), функция стандартного нормального распределения обладает свойством F(-x)=1-F(x). Поэтому, вышеуказанную формулу можно упростить:

=2*НОРМ.СТ.РАСП(1;ИСТИНА)-1

Для произвольной функции нормального распределения N(μ; σ) аналогичные вычисления нужно производить по формуле:

=2* НОРМ.РАСП(μ+1*σ;μ;σ;ИСТИНА)-1

Вышеуказанные расчеты вероятности требуются для построения доверительных интервалов.

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

Примечание: Для удобства написания формул в файле примера созданы Имена для параметров распределения: μ и σ.

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

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

СОВЕТ: О надстройке Пакет анализа можно прочитать в статье Надстройка Пакет анализа MS EXCEL.

Сгенерируем 3 массива по 100 чисел с различными μ и σ. Для этого в окне Генерация случайных чисел установим следующие значения для каждой пары параметров:

Примечание: Если установить опцию Случайное рассеивание (Random Seed), то можно выбрать определенный случайный набор сгенерированных чисел. Например, установив эту опцию равной 25, можно сгенерировать на разных компьютерах одни и те же наборы случайных чисел (если, конечно, другие параметры распределения совпадают). Значение опции может принимать целые значения от 1 до 32 767. Название опции Случайное рассеивание может запутать. Лучше было бы ее перевести как Номер набора со случайными числами.

В итоге будем иметь 3 столбца чисел, на основании которых можно, оценить параметры распределения, из которого была произведена выборка: μ и σ. Оценку для μ можно сделать с использованием функции СРЗНАЧ(), а для σ – с использованием функции СТАНДОТКЛОН.В(), см. файл примера лист Генерация.

Примечание: Для генерирования массива чисел, распределенных по нормальному закону, можно использовать формулу =НОРМ.ОБР(СЛЧИС();μ;σ). Функция СЛЧИС() генерирует непрерывное равномерное распределение от 0 до 1, что как раз соответствует диапазону изменения вероятности (см. файл примера лист Генерация).

Задачи

Задача1. Компания изготавливает нейлоновые нити со средней прочностью 41 МПа и стандартным отклонением 2 МПа. Потребитель хочет приобрести нити с прочностью не менее 36 МПа. Рассчитайте вероятность, что партии нити, изготовленные компанией для потребителя, будут соответствовать требованиям или превышать их.
Решение1: =1-НОРМ.РАСП(36;41;2;ИСТИНА)

Задача2. Предприятие изготавливает трубы, средний внешний диаметр которых равен 20,20 мм, а стандартное отклонение равно 0,25мм. Согласно техническим условиям, трубы признаются годными, если диаметр находится в пределах 20,00+/- 0,40 мм. Какая доля изготовленных труб соответствует ТУ?
Решение2: = НОРМ.РАСП(20,00+0,40;20,20;0,25;ИСТИНА)- НОРМ.РАСП(20,00-0,40;20,20;0,25)
На рисунке ниже, выделена область значений диаметров, которая удовлетворяет требованиям спецификации.

Решение приведено в файле примера лист Задачи.

Задача3. Предприятие изготавливает трубы, средний внешний диаметр которых равен 20,20 мм, а стандартное отклонение равно 0,25мм. Внешний диаметр не должен превышать определенное значение (предполагается, что нижняя граница не важна). Какую верхнюю границу в технических условиях необходимо установить, чтобы ей соответствовало 97,5% всех изготавливаемых изделий?
Решение3: =НОРМ.ОБР(0,975; 20,20; 0,25)=20,6899 или
=НОРМ.СТ.ОБР(0,975)*0,25+20,2 (произведена «дестандартизация», см. выше)

Задача 4. Нахождение параметров нормального распределения по значениям 2-х квантилей (или процентилей).
Предположим, известно, что случайная величина имеет нормальное распределение, но не известны его параметры, а только 2-я процентиля (например, 0,5-процентиль, т.е. медиана и 0,95-я процентиль). Т.к. известна медиана, то мы знаем среднее, т.е. μ. Чтобы найти стандартное отклонение нужно использовать Поиск решения.
Решение приведено в файле примера лист Задачи.

Примечание: До MS EXCEL 2010 в EXCEL были функции НОРМОБР() и НОРМСТОБР(), которые эквивалентны НОРМ.ОБР() и НОРМ.СТ.ОБР(). НОРМОБР() и НОРМСТОБР() оставлены в MS EXCEL 2010 и выше только для совместимости.

Линейные комбинации нормально распределенных случайных величин

Известно, что линейная комбинация нормально распределённых случайных величин x(i) с параметрами μ(i) и σ(i) также распределена нормально. Например, если случайная величина Y=x(1)+x(2), то Y будет иметь распределение с параметрами μ(1)+ μ(2) и КОРЕНЬ(σ(1)^2+ σ(2)^2). Убедимся в этом с помощью MS EXCEL.

С помощью надстройки Пакет анализа сгенерируем 2 массива по 100 чисел с различными μ и σ.

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

С помощью функций СРЗНАЧ() и СТАНДОТКЛОН.В() вычислим среднее и дисперсию получившейся выборки и сравним их с расчетными.

Кроме того, построим График проверки распределения на нормальность (Normal Probability Plot), чтобы убедиться, что наш массив соответствует выборке из нормального распределения.

Прямая линия, аппроксимирующая полученный график, имеет уравнение y=ax+b. Наклон кривой (параметр а) может служить оценкой стандартного отклонения, а пересечение с осью y (параметр b) – среднего значения.

Для сравнения сгенерируем массив напрямую из распределения N(μ(1)+ μ(2); КОРЕНЬ(σ(1)^2+ σ(2)^2)).

Как видно на рисунке ниже, обе аппроксимирующие кривые достаточно близки.

В качестве примера можно провести следующую задачу.

Задача. Завод изготавливает болты и гайки, которые упаковываются в ящики парами. Пусть известно, что вес каждого из изделий является нормальной случайной величиной. Для болтов средний вес составляет 50г, стандартное отклонение 1,5г, а для гаек 20г и 1,2г. В ящик фасуется 100 пар болтов и гаек. Вычислить какой процент ящиков будет тяжелее 7,2 кг.
Решение. Сначала переформулируем вопрос задачи: Вычислить какой процент пар болт-гайка будет тяжелее 7,2кг/100=72г. Учитывая, что вес пары представляет собой случайную величину = Вес(болта) + Вес(гайки) со средним весом (50+20)г, и стандартным отклонением =КОРЕНЬ(СУММКВ(1,5;1,2)), запишем решение
=1-НОРМ.РАСП(72; 50+20; КОРЕНЬ(СУММКВ(1,5;1,2));ИСТИНА)
Ответ: 15% (см. файл примера лист Линейн.комбинация)

Аппроксимация Биномиального распределения Нормальным распределением

Если параметры Биномиального распределения B(n;p) находятся в пределах 0,1<=p<=0,9 и n*p>10, то Биномиальное распределение можно аппроксимировать Нормальным распределением.

При значениях λ>15, Распределение Пуассона хорошо аппроксимируется Нормальным распределением с параметрами: μ, σ2=λ.

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

СОВЕТ: О других распределениях MS EXCEL можно прочитать в статье Распределения случайной величины в MS EXCEL.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 4.7 (3 оценок)