Нормальное распределение в Excel: кривая и проверка данных

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

Разберём обе: сначала как построить кривую функцией НОРМРАСП, потом как проверить свою выборку по асимметрии и эксцессу.

Зачем вообще проверять нормальность

Нормальность — не украшение работы, а развилка. От неё зависит, какой критерий вы имеете право применить в третьей главе.

Данные нормальные — можно брать параметрические методы: t-критерий Стьюдента, дисперсионный анализ, корреляцию Пирсона. Они считают средние и отклонения и молча предполагают колокол.

Данные ненормальные — переходите на ранговые методы: Манна-Уитни, Вилкоксона, Спирмена. Им форма распределения безразлична.

Поменять порядок нельзя: сначала проверка, потом критерий. Обратный ход — «посчитал Стьюдента, а нормальность подтвердил задним числом» — на защите разбирается за минуту.

Как построить кривую нормального распределения

Кривая-колокол — это теоретический график. Он показывает, как выглядит идеальное нормальное распределение с вашим средним и вашим отклонением. Строится одной функцией.

Пример. В дипломе по физической культуре у 60 первокурсников измерили прыжок в длину с места. Среднее — 210,5 см, стандартное отклонение — 14,2 см. Нужен рисунок теоретической кривой для теоретической главы.

Шаг 1. Подготовьте два числа и столбец x

Положите среднее в ячейку B1, отклонение — в B2:

B1: =СРЗНАЧ(A2:A61)
B2: =СТАНДОТКЛОН(A2:A61)

Разница между СТАНДОТКЛОН и СТАНДОТКЛОНП разобрана в статье «Стандартное отклонение в Excel» — в дипломе почти всегда нужен первый вариант.

В столбец D выпишите значения x, по которым будет идти кривая: от «среднее минус три отклонения» до «среднее плюс три отклонения». Для нашего примера это от 168 до 253 см. Шаг возьмите такой, чтобы получилось 25–40 точек — примерно 2–3 см.

Шаг 2. Посчитайте высоту кривой

В столбец E напротив каждого x:

=НОРМРАСП(D2;$B$1;$B$2;ЛОЖЬ)

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

Последний аргумент решает, что вы получите:

• ЛОЖЬ — плотность. Даёт классический колокол: высота показывает, насколько часто встречается значение. Это то, что нужно для рисунка «кривая нормального распределения».
• ИСТИНА — интегральная функция. Даёт S-образную кривую: значение показывает долю наблюдений ниже данного x. Например, =НОРМРАСП(225;210,5;14,2;ИСТИНА) вернёт 0,85 — прыгнуть меньше 225 см должны 85% студентов.

Шаг 3. Постройте график

Выделите оба столбца — D и E — и нажмите Вставка → Точечная → Точечная с гладкими кривыми. Получится ровный колокол.

Обычную «Гистограмму» или «График» здесь брать нельзя: они отложат по горизонтали номера строк, а не сами значения x, и кривая исказится.

Обратная задача: функция НОРМОБР

НОРМОБР отвечает на зеркальный вопрос: какое значение отсекает заданную долю выборки.

=НОРМОБР(0,95;210,5;14,2)

Результат — 233,9 см. Значит, 95% первокурсников по теоретической модели прыгают меньше этой отметки, а результат выше попадает в лучшие 5%. Так удобно считать нормативы и границы уровней «низкий — средний — высокий».

Четыре функции Excel для работы с нормальным распределением

Как проверить свои данные: асимметрия и эксцесс

Кривая по вашим M и σ нарисуется всегда — даже если данные распределены как угодно. Она ничего не проверяет. Проверка — это отдельный расчёт.

Критерия Шапиро-Уилка в Excel нет: ни функции, ни пункта в «Анализе данных». Поэтому в Excel нормальность оценивают по двум показателям формы.

Асимметрия (A) показывает перекос колокола: положительная — хвост тянется вправо, отрицательная — влево. У симметричного распределения она равна нулю.

Эксцесс (E) показывает остроту вершины: положительный — пик острее нормального, отрицательный — распределение приплюснуто. У нормального распределения ноль.

=СКОС(A2:A61)
=ЭКСЦЕСС(A2:A61)

В английском Excel — SKEW и KURT.

Правило трёх ошибок

Сами по себе эти числа ни о чём не говорят: на выборке из 20 человек асимметрия 0,5 — норма, а на выборке из 300 — уже перекос. Сравнивать их нужно с их собственными стандартными ошибками.

Положите объём выборки в ячейку H1 (=СЧЁТ(A2:A61)) и посчитайте ошибки:

Ошибка асимметрии:
=КОРЕНЬ(6*H1*(H1-1)/((H1-2)*(H1+1)*(H1+3)))

Ошибка эксцесса:
=2*КОРЕНЬ(6*H1*(H1-1)/((H1-2)*(H1+1)*(H1+3))*(H1^2-1)/((H1-3)*(H1+5)))

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

Проверка распределения результата прыжка в длину с места, n = 60

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

Строгий тест с p-значением всё равно надёжнее. Как его посчитать и как читать результат — в статье «Как проверить нормальность распределения»; сам расчёт делается в калькуляторе Шапиро-Уилка за минуту.

Правило трёх сигм

У нормального распределения доли значений в интервалах вокруг среднего фиксированы: 68,3% попадают в «M ± σ», 95,4% — в «M ± 2σ», 99,7% — в «M ± 3σ». Это и есть правило трёх сигм.

Это даёт быструю проверку руками. Посчитайте, какая доля ваших данных реально попала в «M ± σ»:

=СЧЁТЕСЛИМН(A2:A61;">="&$B$1-$B$2;A2:A61;"<="&$B$1+$B$2)/СЧЁТ(A2:A61)

Получилось около 0,68 — форма похожа на нормальную. Вышло 0,55 или 0,85 — распределение точно не колокол, и одними асимметрией с эксцессом дело не обойдётся.

Второе применение правила — поиск выбросов. Значение за пределами «M ± 3σ» при нормальном распределении встречается реже чем в одном случае из трёхсот. Такие числа проверяют: чаще всего это опечатка при вводе, а не рекорд.

Готовый файл всё проверяет сам

Асимметрию, эксцесс, обе стандартные ошибки и сравнение с тройным порогом можно не собирать вручную — в готовой книге Excel это уже настроено. Вы вставляете свои данные в жёлтые ячейки, а вывод появляется словами.

Проверка живёт на листе «8. Проверка нормальности»: там уже стоят СКОС, ЭКСЦЕСС, обе формулы ошибок, сравнение с тройным порогом и подсчёт долей по правилу трёх сигм. Вывод формулируется словами — его остаётся перенести в работу.

Три способа проверить — что выбрать

← листайте вправо, чтобы сравнить →

Асимметрия и эксцесс получатся везде одинаковые. Разница в том, чем вы подкрепите вывод: тройным порогом или полноценным p-значением, которое ждёт большинство научных руководителей.

Что писать в дипломе

Если проверяли по асимметрии и эксцессу:

Проверка распределения показала, что асимметрия составила 0,21 при стандартной ошибке 0,31, эксцесс −0,48 при стандартной ошибке 0,61. Ни один из показателей не превышает утроенной стандартной ошибки, что позволяет считать распределение близким к нормальному (n = 60). Для сравнения групп применялся параметрический t-критерий Стьюдента.

Если распределение отклонилось от нормального:

Асимметрия распределения составила 1,42 при стандартной ошибке 0,31, что превышает утроенную ошибку (0,93). Распределение признано отличным от нормального, поэтому для сравнения групп использовался U-критерий Манна-Уитни, а результаты представлены в виде медианы и квартилей.

Если считали строгим критерием, формулировка короче: «распределение соответствует нормальному (критерий Шапиро-Уилка: W = 0,972; p = 0,183)».

Частые ошибки

• Строят кривую по своим M и σ и считают, что проверили нормальность. НОРМРАСП рисует идеальный колокол при любых данных — хоть при двугорбых, хоть при равномерных.Кривая — иллюстрация. Проверка — это СКОС, ЭКСЦЕСС и сравнение с тремя ошибками.
• Сравнивают асимметрию с единицей. Порог зависит от объёма выборки и при n = 200 в три раза строже, чем при n = 20.Считайте стандартные ошибки по формулам выше и сравнивайте с утроенным значением.
• Оставляют последний аргумент ИСТИНА. Вместо колокола получается S-образная кривая, и рисунок в главе не соответствует подписи.Для колокола нужна ЛОЖЬ: она даёт плотность, а не накопленную вероятность.
• Берут тип диаграммы «График» вместо точечной. Excel отложит по горизонтали порядковые номера точек, и при неравномерном шаге x колокол перекосится.Только «Точечная с гладкими кривыми» — она читает x из первого столбца.
• Накладывают кривую на гистограмму без пересчёта. Плотность измеряется сотыми долями, частоты — десятками, кривая ложится на ось.Умножьте плотность на объём выборки и ширину интервала.
• Забывают закрепить ссылки на среднее и отклонение. При протягивании формула съезжает, и колокол превращается в ломаную.Пишите $B$1 и $B$2 со знаками доллара.

Частые вопросы

Чем НОРМРАСП отличается от НОРМ.РАСП

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

Есть ли в Excel критерий Шапиро-Уилка

Нет — ни функции, ни пункта в надстройке «Анализ данных». Его считают в SPSS, jamovi, R или онлайн: в калькуляторе Шапиро-Уилка достаточно вставить столбец значений.

Сколько человек нужно, чтобы проверка имела смысл

Формулы ошибок работают начиная с n = 4, но осмысленный вывод получается примерно от 20 наблюдений. На выборке из 8–10 человек любой критерий почти всегда «подтверждает» нормальность просто потому, что данных мало для обратного.

Почему кривая получилась ломаной

Точек слишком мало или шаг по x неравномерный. Возьмите 25–40 значений с одинаковым шагом от «M − 3σ» до «M + 3σ» и тип диаграммы «с гладкими кривыми».

Что делать, если распределение оказалось ненормальным

Перейти на ранговые критерии — Манна-Уитни для независимых групп, Вилкоксона для повторных замеров — и описывать данные медианой и квартилями вместо «M ± σ». Это не провал работы, а корректный выбор метода.

Короткий алгоритм

1. Определитесь, что вам нужно: теоретическая кривая для главы или проверка своих данных.
2. Для кривой посчитайте =СРЗНАЧ() и =СТАНДОТКЛОН(), выпишите x от «M − 3σ» до «M + 3σ».
3. Протяните =НОРМРАСП(x;$B$1;$B$2;ЛОЖЬ) и постройте точечную диаграмму с гладкими кривыми.
4. Для проверки посчитайте =СКОС() и =ЭКСЦЕСС(), затем обе стандартные ошибки по формулам выше.
5. Сравните каждый показатель с тремя его ошибками: уложились — распределение близко к нормальному.
6. Для диплома подкрепите вывод p-значением критерия Шапиро-Уилка и запишите готовую формулировку.

Или пропустите все шаги: скачайте файл выше либо вставьте данные в калькулятор Шапиро-Уилка.

Что ещё почитать

• Нормальное распределение — что это за колокол и откуда он берётся в природе и в психологии.
• Как проверить нормальность распределения — все способы проверки, включая строгие критерии с p-значением.
• Асимметрия и эксцесс — подробный разбор обоих показателей формы.
• Гистограмма в Excel — как увидеть форму распределения собственными глазами.
• Стандартное отклонение в Excel — откуда берётся σ для формулы кривой.
• Параметрические и непараметрические критерии — что выбрать после проверки.

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