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

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

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

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

Функции нормального распределения в Excel

Как известно, нормальное распределение N(μ, σ2) зависит от двух параметров: математического ожидания (μ) и дисперсии (σ2). У стандартного нормального распределения они равны соответственно 0 и 1, т.е. N(0, 1). Именно для нормированных данных созданы таблицы, упомянутые выше.

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

Функция НОРМ.СТ.РАСП

Формула НОРМ.СТ.РАСП предназначена для расчета плотности ϕ(z) или вероятности Φ(z) по нормированным данным (z). Синтаксис формулы следующий.

=НОРМ.СТ.РАСП(z;интегральная)

z – значение стандартизированной переменной

интегральная – если 0, то рассчитывается плотность ϕ(z), если 1 – значение функции Ф(z), т.е. вероятность P(Z<z).

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

Рассчитаем плотность и значение функции для различных z: -3, -2, -1, 0, 1, 2, 3 (их укажем в ячейке А2).

Для расчета плотности потребуется формула =НОРМ.СТ.РАСП(A2;0). На диаграмме ниже – это красная точка.

Для расчета значения функции =НОРМ.СТ.РАСП(A2;1). На диаграмме – закрашенная площадь под нормальной кривой.

Расчет плотности и функции нормального распределения в Excel

В реальности чаще приходится рассчитывать вероятность того, что случайная величина не выйдет за некоторые пределы от средней (в среднеквадратичных отклонениях, соответствующих переменной z), т.е. P(|Z|<z).

Вероятность отклонения при заданном z

Определим, чему равна вероятность попадания случайной величины в пределы ±1z, ±2z и ±3z от нуля. Для это нужно воспользоваться одним из свойств любого непрерывного распределения и взять разность: Ф(z)-Ф(-z), либо, что даст тот же результат, 2Ф(z)-1. В Excel это будет выглядеть следующим образом =2*НОРМ.СТ.РАСП(A2;1)-1.

Расчет вероятности отклонения от средней

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

Может стоять и обратная задача: по имеющейся вероятности P(Z<z) найти стандартизированную величину z,то есть значение обратной функции нормального распределения Ф-1(P)=z. Есть в Excel и такая функция.

Функция НОРМ.СТ.ОБР

Формула НОРМ.СТ.ОБР рассчитывает обратное значение стандартного нормального распределения. Ее синтаксис состоит из одного параметра:

=НОРМ.СТ.ОБР(вероятность)

вероятность – это вероятность.

Данная формула используется так же часто, как и предыдущая, ведь по тем же таблицам приходится искать не только вероятности, но и квантили (те самые z, соответствующие заданной вероятности).

Обратная функция стандартного нормального распределения

Реальная постановка задач обычно отличается и приходится использовать какую-либо конструкцию с использованием этой формулы. Здесь уместно вспомнить про доверительные интервалы, которые зависят от доверительной вероятности. Это то же отклонение от средней, только на этот раз задается вероятность, а рассчитывается величина z.

Расчет предельного отклонения при нормальном распределении

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

Формула расчета предельного отклонения с помощью обратной функции нормального стандартного распределения

А сейчас рассчитаем значения z (что соответствует отклонению от средней в сигмах) для нескольких вероятностей, включая те, которые наизусть знает любой статистик, т.е. доверительные интервалы для вероятностей 90%, 95% и 99%. В ячейке B2 укажем формулу: =НОРМ.СТ.ОБР((1+A2)/2). Меняя значение переменной (вероятности в ячейке А2) получим различные границы интервалов.

Расчет предельного отклонения при заданной вероятности

{module 111}

Как видно доверительный интервал для 95% равен 1,96, то есть почти 2 среднеквадратичных отклонения. Отсюда довольно легко даже в уме прикидывать возможный разброс нормальной случайной величины (например, средней арифметической, которая в силу действия Центральной предельно теоремы имеет нормальное распределение). В общем, доверительным вероятностям 90%, 95% и 99% соответствуют доверительные интервалы ±1,64, ±1,96 и ±2,58 σ.

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

Рассмотрим еще пару полезных формул с примерами.

Функция НОРМ.РАСП

Функция НОРМ.РАСП отличается от НОРМ.СТ.РАСП лишь тем, что ее используют для обработки данных любого масштаба, а не только нормированных. Параметры нормального распределения указываются в синтаксисе.

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

x – значение (или ссылка на ячейку), для которого рассчитывается плотность или значение функции нормального распределения

среднее – математическое ожидание, используемое в качестве первого параметра модели нормального распределения

стандартное_откл – среднеквадратичное отклонение – второй параметр модели

интегральная – если 0, то рассчитывается плотность, если 1 – то значение функции, т.е. P(X<x).

Например, чтобы рассчитать плотность для значения 15, которое извлекли из нормальной выборки с матожиданием 10, стандартным отклонением 3, нужно написать следующую формулу:

Расчет плотности для нормальных данных

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

Функция НОРМ.ОБР

Это обратная функция нормального распределения. Синтаксис следующий.

=НОРМ.ОБР(вероятность;среднее;стандартное_откл)

вероятность – вероятность

среднее – матожидание

стандартное_откл – среднеквадратичное отклонение

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

Пример показан в ролике в конце статьи.

Генератор нормальных случайных чисел

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

В Excel есть две функции, которые выдают случайные числа. Первая из них СЛУЧМЕЖДУ. Она выдает случайные равномерно распределенные целые числа в указанных пределах. Можно, например, случайным образом отобрать победителей какого-то конкурса или создать пароль.

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

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

=НОРМ.ОБР(СЛЧИС();10;3)

Протянем ее на необходимое количество ячеек и нормальная выборка готова. Для моделирования стандартизированных данных следует использовать НОРМ.СТ.ОБР.

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

Преобразование равномерной случайной величины в нормальную

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

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

Как обычно, прилагаю ролик, где все вышеописанное показывается в действии.

Всего доброго.

Поделиться в социальных сетях:
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  • Олег

    Подскажите пожалуйста, по поводу генератора нормальных случайных чисел: на последнем графике видно, что ни одно наблюдение не выходит за пределы +-2, а правилу трех сигм почти все наблюдения (99%) стандартного нормального распределения лежат в пределах +-3, не говоря уже про оставшийся 1%, который выходит за эти пределы. Есть ли более точные генераторы в экселе, которые учитывают этот момент?

    • Езепов Дмитрий

      +-2 объясняется только наглядностью картинки. Используйте мою формулу и получите любые нормальные значения (в том числе за 3 сигмы). Еще раз про смысл генератора. Генерируются случайные вероятности от 0 до 1, а они уже превращаются в нормальные данные.

      • Олег

        Разобрался, спасибо