Как проверить нормальное распределение в excel

Как выполнить тест Колмогорова-Смирнова в Excel

Критерий Колмогорова-Смирнова используется для определения нормальности распределения выборки.

Этот тест широко используется, потому что многие статистические тесты и процедуры предполагают , что данные распределены нормально.

В следующем пошаговом примере показано, как выполнить тест Колмогорова-Смирнова для образца набора данных в Excel.

Шаг 1: введите данные

Во-первых, давайте введем значения для набора данных с размером выборки n = 20:

Шаг 2: Расчет фактических и ожидаемых значений из нормального распределения

Далее мы рассчитаем фактические значения по сравнению с ожидаемыми значениями из нормального распределения:

Вот формула, которую мы использовали в различных ячейках:

Шаг 3: интерпретируйте результаты

В тесте Колмогорова-Смирнова используются следующие нулевая и альтернативная гипотезы:

• H 0 : Данные нормально распределены.
• H A : Данные не распределены нормально.

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

Это представляет собой максимальную абсолютную разницу между фактическими значениями нашей выборки и ожидаемыми значениями нормального распределения.

Чтобы определить, является ли это максимальное значение статистически значимым, мы должны обратиться к таблице критических значений Колмогорова-Смирнова и найти число, равное n = 20 и α = 0,05.

Критическое значение оказывается равным 0,190 .

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

Это означает, что мы можем предположить, что наши выборочные данные нормально распределены.

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные статистические тесты в Excel:

Проверка статистических гипотез в EXCEL о дисперсии нормального распределения

Первое знакомство с процедурой проверки гипотез (Hypothesis testing) для дисперсии рекомендуется начать с изучения построения соответствующего доверительного интервала (см. статью Доверительный интервал для оценки дисперсии в MS EXCEL ).

Примечание : Перечень статей о проверке гипотез приведен в статье Проверка статистических гипотез в MS EXCEL .

СОВЕТ : Для проверки гипотез потребуется знание следующих понятий:

• дисперсия и стандартное отклонение ,
• доверительный интервал для оценки среднего ,
• выборочное распределение статистики ,
• уровень доверия/ уровень значимости ,
• нормальное распределение , распределение χ 2 и их квантили .

Формулировка задачи. Из генеральной совокупности имеющей нормальное распределение с неизвестным средним значением μ (мю) и неизвестной дисперсией σ 2 ( сигма 2 ) взята выборка размера n. Необходимо проверить двустороннюю статистическую гипотезу о равенстве неизвестной дисперсии σ 2 заданному исследователем значению σ ₀ 2 (англ. Inference on the variance of a normal population).

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

В качестве точечной оценкой дисперсии распределения, из которого взята выборка , используют Дисперсию выборки s 2 .

Перед процедурой проверки гипотезы , исследователь устанавливает требуемый уровень значимости – это допустимая для данной задачи ошибка первого рода , т.е. вероятность отклонить нулевую гипотезу , когда она верна ( уровень значимости обозначают буквой α (альфа) и чаще всего выбирают равным 0,1; 0,05 или 0,01).

Тестовой статистикой для проверки этой гипотезы является величина:

В статье про χ 2 -распределение показано , что выборочное распределение этой статистики, имеет χ 2 -распределение с n-1 степенью свободы, которое является « эталонным распределением » (англ. Reference distribution) для данного теста о равенстве дисперсии .

Значение, которое приняла χ 2 -статистика обозначим χ ₀ 2 .

Нулевая гипотеза Н ₀ о равенстве дисперсии значению σ ₀ 2 отвергается в том случае, если χ ₀ 2 >χ 2 _α/2,n-1 или χ ₀ 2 1-α/2,n-1

Примечание : Подробнее про квантили распределения можно прочитать в статье Квантили распределений MS EXCEL .

В MS EXCEL верхний α/2-квантиль распределения χ 2 вычисляется с помощью формулы =ХИ2.ОБР.ПХ(α/2; n-1)

Верхний (1-α /2)-квантиль вычисляется с помощью аналогичной формулы =ХИ2.ОБР.ПХ(1-α/2; n-1)

или через равный ему нижний квантиль

Вычисления приведены в файле примера .

В случае односторонней гипотезы речь идет об отклонении дисперсии только в одну сторону: либо больше либо меньше σ ₀ 2 . Если альтернативная гипотеза звучит как σ 2 > σ ₀ 2 , то гипотеза Н ₀ отвергается в случае χ ₀ 2 > χ 2 _{α ,n-1} . Если альтернативная гипотеза звучит как σ 2 < σ ₀ 2 , то гипотеза Н ₀ отвергается в случае χ ₀ 2 < χ 2 _{1-α ,n-1} .

СОВЕТ : О проверке гипотезы о равенстве дисперсий двух нормальных распределений ( F-test ) см. статью Двухвыборочный тест для дисперсии: F-тест в MS EXCEL .

Вычисление Р-значения

При проверке гипотез большое распространение также получил еще один эквивалентный подход, основанный на вычислении p -значения (p-value).

Если p-значение , вычисленное на основании выборки , меньше чем заданный уровень значимости α , то нулевая гипотеза отвергается и принимается альтернативная гипотеза . И наоборот, если p-значение больше α, то нулевая гипотеза не отвергается.

Формула для вычисления p-значения зависит от формулировки альтернативной гипотезы :

• Для односторонней гипотезы σ 2 < σ ₀ 2 p-значение вычисляется как =ХИ2.РАСП( χ ₀ 2 ; n-1;ИСТИНА)
• Для другой односторонней гипотезы σ 2 > σ ₀ 2 p-значение вычисляется как =ХИ2.РАСП.ПХ( χ ₀ 2 ; n-1)
• Для двусторонней гипотезыp-значение вычисляется как =2*МИН(ХИ2.РАСП( χ ₀ 2 ;n-1;ИСТИНА); ХИ2.РАСП.ПХ( χ ₀ 2 ;n-1))

Соответственно, χ ₀ 2 = (СЧЁТ( выборка )-1)* ДИСП.В( выборка )/ σ ₀ 2 , где выборка – ссылка на диапазон, содержащий значения выборки .

СОВЕТ : Подробнее про вышеуказанные функции MS EXCEL см. статью про χ 2 -распределение .

В файле примера на листе Дисперсия показано решение задач проверки двусторонней и односторонних гипотез .

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

Возвращает нормальную функцию распределения для указанного среднего и стандартного отклонения. Эта функция очень широко применяется в статистике, в том числе при проверке гипотез.

Синтаксис

Аргументы функции НОРМ.РАСП описаны ниже.

• X Обязательный. Значение, для которого строится распределение.
• Среднее Обязательный. Среднее арифметическое распределения.
• Стандартное_откл Обязательный. Стандартное отклонение распределения.
• Интегральная — обязательный аргумент. Логическое значение, определяющее форму функции. Если имеется истина, норм. Функция DIST возвращает накопительную функцию распределения; Если этот ложь, возвращается функция плотности вероятности.

Замечания

• Если число или standard_dev не является числом, то норм. DIST возвращает #VALUE! значение ошибки #ЗНАЧ!.
• Если standard_dev ≤ 0, норм. DIST возвращает #NUM! значение ошибки #ЗНАЧ!.
• Если значение «среднее» = 0, «стандартное_откл» = 1 и «интегральная» = ИСТИНА, то функция НОРМ.РАСП возвращает стандартное нормальное распределение, как функция НОРМ.СТ.РАСП.
• Уравнение для плотности нормального распределения (аргумент «интегральная» содержит значение ЛОЖЬ) имеет следующий вид:
• Если аргумент «интегральная» имеет значение ИСТИНА, формула описывает интеграл с пределами от минус бесконечности до x.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

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

Среднее арифметическое распределения

Стандартное отклонение распределения

Интегральная функция распределения для приведенных выше условий

Функция плотности распределения для приведенных выше условий

Нужна дополнительная помощь?

Нужны дополнительные параметры?

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

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

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

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

Чтобы вычислить вероятности, связанные с нормальным распределением в Excel, вы можете использовать функцию НОРМРАСП , которая использует следующий базовый синтаксис:

=NORMDIST(x, mean, standard_dev, cumulative) 

• x : интересующее значение в нормальном распределении
• mean : среднее значение нормального распределения
• standard_dev : стандартное отклонение нормального распределения.
• cumulative : Рассчитывать ли кумулятивные вероятности (обычно это TRUE)

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

Пример 1. Расчет вероятности меньше некоторого значения

Предположим, что баллы за экзамен нормально распределены со средним значением 90 и стандартным отклонением 10.

Найти вероятность того, что случайно выбранный студент наберет меньше 80 баллов.

На следующем снимке экрана показано, как использовать функцию НОРМРАСП() в Excel для расчета этой вероятности:

Вероятность того, что случайно выбранный студент получит меньше 80 баллов, равна 0,1587 .

Пример 2. Расчет вероятности больше некоторого значения

Предположим, что баллы за экзамен нормально распределены со средним значением 90 и стандартным отклонением 10.

Найти вероятность того, что случайно выбранный студент наберет больше 80 баллов.

Чтобы найти эту вероятность, мы можем просто выполнить 1 – НОРМРАСП() в Excel следующим образом:

Вероятность того, что случайно выбранный студент наберет больше 80 баллов, равна 0,1587 .

Пример 3: Расчет вероятности между двумя значениями

Предположим, что баллы за экзамен нормально распределены со средним значением 90 и стандартным отклонением 10.

Найдите вероятность того, что случайно выбранный студент получит от 87 до 93 баллов.

Чтобы найти эту вероятность, мы можем вычесть большее значение НОРМРАСП() из меньшего значения другого НОРМРАСП() в Excel следующим образом:

Вероятность того, что случайно выбранный студент получит от 87 до 93 баллов, равна 0,2358 .

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие задачи, связанные с нормальным распределением в Excel: