Нормальное распределение (Гаусса) в Excel

Нормальный закон – основа и следствие многих статистических методов. 

Закон нормального распределения

Нормальный закон, как и другие теоретические распределения, не является фиксированным уравнением зависимости одной переменной от другой. Фиксируется только характер этой зависимости. Конкретная форма распределения задается специальными параметрами. Например, у = аx + b – это уравнение прямой. Однако где конкретно она проходит и под каким наклоном, определяется параметрами а и b. Также и с нормальным распределением. Ясно, что это функция, которая описывает тенденцию высокой концентрации значений около центра, но ее точная форма задается специальными параметрами.

История нормального закона насчитывает уже почти 300 лет. Первым открывателем стал Абрахам де Муавр, который придумал теоретическое приближение биномиального распределения при большом количестве наблюдений еще в далеком 1733 году. Однако широкое признание нормальный закон получил намного позже благодаря анализу выборочных данных. 

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

Лаплас также обнаружил замечательную закономерность и сформулировал центральную предельную теорему (ЦПТ), согласно которой сумма большого количества малых и независимых величин имеет нормальное распределение. Центральная предельная теорема далее многократно уточнялась и видоизменялась, но суть ее осталась прежней. Таким образом, история открытия нормального закона насчитывает более 200 лет. Начиная от открытия Муавра, до окончательных формулировок ЦПТ в середине 20-го века.

Функция плотности нормального распределения, она же функция Гаусса, имеет следующий вид.

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

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

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

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

Формула нормального распределения следующая.

Функция Гаусса

Формула состоит из двух математических констант:

π – соотношение длины окружности и его диаметра, равно примерно 3,142;

е – основание натурального логарифма, равно примерно 2,718;

и двух параметров, которые задают форму конкретной кривой:

m — математическое ожидание (в различных источниках могут использоваться другие обозначения, например, µ или a);

σ2 — дисперсия;

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

Конкретная форма нормального распределения зависит от 2-х параметров: математического ожидания (m) и дисперсии (σ2). Кратко обозначается N(m, σ2) или N(m, σ). Параметр m (матожидание) определяет центр распределения, которому соответствует максимальная высота графика. Дисперсия σ2 характеризует размах вариации, то есть «размазанность» данных.

Параметр математического ожидания смещает центр распределения вправо или влево, не влияя на саму форму кривой плотности.

Влияние матожидания на нормальное распределение

А вот дисперсия определяет остроконечность кривой. Когда данные имеют малый разброс, то вся их масса концентрируется у центра. Если же у данных большой разброс, то они «размазываются» по широкому диапазону.

Влияние сигмы на нормальное распределение

Плотность нормального распределения не имеет прямого практического применения. Вероятность того, что случайная величина окажется меньше некоторого значения x, определяется функцией нормального распределения:

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

P(a ≤ X < b) = Ф(b) – Ф(a)

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

Нормальное распределение зависит от параметров средней и дисперсии, из-за чего плохо видны его свойства. Хорошо бы иметь некоторый эталон нормального распределения, не зависящий от масштаба данных. И он существует. Называется стандартным нормальным распределением. Это обычное нормальное нормальное распределение, только с параметрами математического ожидания 0, а дисперсией – 1, кратко записывается N(0, 1).

Любое нормальное распределение легко превращается в стандартное путем нормирования:

Нормирование

где z – новая переменная, которая используется вместо x;
m – математическое ожидание;
σ – стандартное отклонение.

Для выборочных данных берутся оценки:

Нормирование по оценкам параметров

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

В литературе встречается название z-оценка. Это оно самое – нормированные данные. Z-оценку можно напрямую сравнивать с теоретическими вероятностями, т.к. ее масштаб совпадает с эталоном.

Посмотрим теперь, как выглядит плотность стандартного нормального распределения (для z-оценок). Напомню, что функция Гаусса имеет вид:

Функция Гаусса

Подставим вместо (x-m)/σ букву z, а вместо σ – единицу, получим функцию плотности стандартного нормального распределения:

Плотность стандартного нормального распределения

График плотности:

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

Центр, как и ожидалось, находится в точке 0. В этой же точке функция Гаусса достигает своего максимума, что соответствует принятию случайной величиной своего среднего значения (т.е. x-m=0). Плотность в этой точке равна 0,3989, что можно посчитать даже в уме, т.к. e0=1 и остается рассчитать только соотношение 1 на корень из 2 пи.

Таким образом, по графику хорошо видно, что значения, имеющие маленькие отклонения от средней, выпадают чаще других, а те, которые сильно отдалены от центра, встречаются значительно реже. Шкала оси абсцисс измеряется в стандартных отклонениях, что позволяет отвязаться от единиц измерения и получить универсальную структуру нормального распределения. Кривая Гаусса для нормированных данных отлично демонстрирует и другие свойства нормального распределения. Например, то, что оно является симметричным относительно оси ординат. В пределах ±1σ от средней арифметической сконцентрирована большая часть всех значений (прикидываем пока на глазок). В пределах ±2σ находятся большинство данных. В пределах ±3σ находятся почти все данные. Последнее свойство широко известно под названием правило трех сигм для нормального распределения.

Для нормированной (стандартизованной) случайной величины можно задавать следующие вопросы. Какова вероятность, что случайная величина z отклонится от средней не более, чем на 2 стандартных отклонения. И если данные действительно имеют подобное распределение, то ответ всегда одинаков – 95,45%. То бишь в пределах ±2 сигмы от средней арифметической находится 95% всей совокупности нормально распределенных данных. Значит, вероятность отклонения за эти пределы относительно маленькая, всего 5%. Или наоборот – целых 5%. Все зависит от поставленной задачи.

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

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

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

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

Таблица нормального распределения

Таблицы нормального распределения встречаются двух типов:

— таблица плотности стандартного нормального распределения;

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

Таблица плотности используется редко. Тем не менее, посмотрим, как она выглядит. Допустим, нужно получить плотность для z = 1, т.е. плотность значения, отстоящего от матожидания на 1 сигму. Ниже показан кусок таблицы. 

Таблица плотности стандартного нормального распределения

В зависимости от организации данных ищем нужное значение по названию столбца и строки. В нашем примере берем строку 1,0 и столбец 0, т.к. сотых долей нет. Искомое значение равно 0,2420 (0 перед 2420 опущен). Не пугайтесь различных обозначений переменной, чаще всего в таблицах указывается именно x. Главное – это формула над таблицей.

Функция Гаусса симметрична относительно оси ординат. Поэтому φ(z)= φ(-z), т.е. плотность для 1 тождественна плотности для -1, что отчетливо видно на рисунке.

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

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

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

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

Функция Ф(z) симметрична относительно своего значения 0,5 (а не оси ординат, как плотность). Отсюда справедливо равенство:

Свойство 1

Это факт показан на картинке:

Свойство нормального распределения 1

Значения функции Ф(-z) и Ф(z) делят график на 3 части. Причем верхняя и нижняя части равны (обозначены галочками). Для того, чтобы дополнить вероятность Ф(z) до 1, достаточно добавить недостающую величину Ф(-z). Получится равенство, указанное чуть выше.

Если нужно отыскать вероятность попадания в интервал (0; z), то есть вероятность отклонения от нуля в положительную сторону до некоторого количества стандартных отклонений, достаточно от значения функции стандартного нормального распределения отнять 0,5:

Свойство 2

Для наглядности можно взглянуть на рисунок.

Свойство нормального распределения 2

На кривой Гаусса, эта же ситуация выглядит как площадь от центра вправо до z.

Свойство нормального распределения 2 на кривой Гаусса

Довольно часто аналитика интересует вероятность отклонения в обе стороны от нуля. А так как функция симметрична относительно центра, предыдущую формулу нужно умножить на 2:

Свойство 3

Рисунок ниже.

Свойство нормального распределения 3

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

Свойство нормального распределения 3 на кривой Гаусса

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

Для облегчения задачи в учебниках обычно публикуют таблицы для функции вида:

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

Если нужна вероятность отклонения в обе стороны от нуля, то, как мы только что убедились, табличное значение для данной функции просто умножается на 2.

Теперь посмотрим на конкретные примеры использования статистической таблицы функции стандартного нормального распределения. Найдем табличные значения для трех z: 1,64, 1,96 и 3.

Таблица функции Лапласа

Как понять смысл этих чисел? Начнем с z=1,64, для которого табличное значение составляет 0,4495. Проще всего пояснить смысл на рисунке.

Значение функции Лапласа для z=1,64 в правую сторону

То есть вероятность того, что стандартизованная нормально распределенная случайная величина попадет в интервал от 0 до 1,64, равна 0,4495. При решении задач обычно нужно рассчитать вероятность отклонения в обе стороны, поэтому умножим величину 0,4495 на 2 и получим примерно 0,9. Занимаемая площадь под кривой Гаусса показана ниже.

Значение функции Лапласа для z=1,64 под кривой Гаусса

Таким образом, 90% всех нормально распределенных значений попадает в интервал ±1,64σ от средней арифметической. Я не случайно выбрал значение z=1,64, т.к. окрестность вокруг средней арифметической, занимающая 90% всей площади, иногда используется для проверки статистических гипотез и расчета доверительных интервалов. Если проверяемое значение не попадает в обозначенную область, то его наступление маловероятно (всего 10%).

Для проверки гипотез, однако, чаще используется интервал, накрывающий 95% всех значений. Половина вероятности от 0,95 – это 0,4750 (см. второе выделенное в таблице значение).

Значение функции Лапласа для z=1,96 в правую сторону

Для этой вероятности z=1,96. Т.е. в пределах почти ±2σ от средней находится 95% значений. Только 5% выпадают за эти пределы.

Значение функции Лапласа для z=1,96 под кривой Гаусса

Еще одно интересное и часто используемое табличное значение соответствует z=3, оно равно по нашей таблице 0,4986. Умножим на 2 и получим 0,997. Значит, в рамках ±3σ от средней арифметической заключены почти все значения нормально распределенной совокупности данных.

Значение функции Лапласа для z=3 под кривой Гаусса

Так выглядит правило 3 сигм для нормального распределения геометрически.

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

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

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

Функции нормального распределения в 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 от нуля. Потребуется формула 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) получим различные границы интервалов.

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

Доверительный интервал для 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, а они уже превращаются в нормальные данные.

      • Олег

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