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

Обновлено: 06.01.2025

Есть такой критерий и в Excel он работает, судя по многим примерам.
Кто-нибудь возьмется рассчитать конкретный пример проверки на нормальность с предоставлением алгоритма в Excel 2007?
Спасибо, о цене договоримся.
Столбец C.

Перенести в платный раздел? Автор - Pelena
Дата добавления - 27.04.2020 в 10:46

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

прохожий2019, Жаль. Но возможно я выбрал слишком широкие карманы, поэтому по гистограмме "видно", что нет нормальности. А можно как-то смягчить критерии, чтобы не было возможности так категорично отказать в нормальности. И номер Яндекс кошелька напишите (можно в привате) для хотя бы символической благодарности. Если у вас получится, то что я выше написал, я оплачу больше. Спасибо вам за то, что снова откликнулись. Сергей

stormbringernew, ваши данные НЕ распределены нормально. Если хотите - поступайте как обычно делают в медицинских диссертациях : "распределение нормально (p=0,002652)". Но это подтасовка, подгон критериев под желаемое. Я в этом не участвую

stormbringernew, данные по стомату можно описать нормальным распределением (см. файл), но логнормальное описывает лучше. если вы хотите навести красоту - описывайте данные логнормальным распределением (КС пройдет для обоих факультетов), это и вполне объяснимо - у ИМТ есть точная нижняя граница - 0 (ниже не бывает), а вверх - границы нет, отсюда асимметрия. Не нужно искать нормальность там, где её нет. См. файл - оценено распределение логарифмов ИМТ - оно нормально

СПАСИБО! А параметры логнормального можно в экселе рассчитать? Есть функция?
И можно достоверность логнормального указать для обеих факультетов?

СПАСИБО! А параметры логнормального можно в экселе рассчитать? Есть функция?
И можно достоверность логнормального указать для обеих факультетов? stormbringernew

Вроде нашел функцию, пробую стоить график. НЕТ, только интегральная находится почему-то. Для плотности вероятности в 2007 нет что-ли?
И как искать a и сигма для логнормального, по специальным формулам? Или Excel может сам?

Что-то я не поняла. Тема в платном разделе, вовсю идёт обсуждение и даже с ответами.
прохожий2019 вообще ещё не вправе здесь писать.
Переносить тему обратно в бесплатную ветку ВОПРОСЫ ПО EXCEL?

Я не знал, что нельзя задавать вопросы здесь. Как решит прохожий2019. Спасибо ему за оказываемую на форуме помощь. Кстати, а где задавать вопросы? Почты или телефона я не знаю. С уважением Сергей

Первое знакомство с процедурой проверки гипотез (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 <χ 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 -распределение .

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

Рассмотрим Нормальное распределение. С помощью функции 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% значений. Убедиться в этом для стандартного нормального распределения можно записав формулу:

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

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

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

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

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

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

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

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

СОВЕТ : О надстройке Пакет анализа можно прочитать в статье Надстройка Пакет анализа 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 10, то Биномиальное распределение можно аппроксимировать Нормальным распределением .

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

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

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

П риняв уровень значимости alpha=0.05, проверить согласие этих данных обычного месяца с распределением Пуассона, пользуясь критерием Хи-квадрат. Перепроверить данные с помощью критерия Колмогорова-Смирнова, по прежнему принимая alpha =0.05.

Методические указания

критерий согласия Хи-квадрат
критерий Крамера-фон Мизеса
критерий Колмогорова-Смирнова

Критерий Хи-квадрат предпочтителен, когда исследуются большие объемы выборок. При малых объемах выборок этот критерий практически не пригоден.

Нулевая гипотеза при применении общих критериев согласия записывается в форме

где F_n(x) – эмпирическая функция распределения вероятностей; F(x) – гипотетическая функция распределения вероятностей.

Критерий Пирсона X 2 основан на сравнении эмпирической гистограммы распределения случайной величины с ее теоретической плотностью. Диапазон изменения экспериментальных данных разбивается на k интервалов, и подсчитывается статистика:

где n_i – количество значений случайной величины, попавших в i-й интервал; n – объем выборки; F(x) – гипотетический теоретический закон распределения вероятностей случайной величины; p_i = F(x_i₊₁) — F(x_i) – теоретическая вероятность попадания случайной величины в i-й интервал.

Статистика X 2 имеет распределение Хи-квадрат с f = n — 1 степенями свободы в том случае, когда проверяется простая нулевая гипотеза H0, т.е., когда гипотетическое распределение, на соответствие которому проверяется эмпирический ряд данных, известно с точностью до значения своих параметров.

Правило проверки гипотезы:

то на уровне значимости alpha, т. е. с достоверностью (1 — alpha) гипотеза

На мощность статистического критерия X 2 сильное влияние оказывает чиcло интервалов разбиения гистограммы (k) и порядок ее разбиения (т. е. выбор длин интервалов внутри диапазона изменения значений случайной величины). На практике принято считать, что статистику X 2 можно использовать, когда np_i >= 5.

Такое приближение допустимо и тогда, когда не более, чем в 20% интервалов имеет место 1 <= np_i <= 5.

Одна из рекомендаций по расчету k сводится к вычислению:

При n >= 200 можно выбирать k из условия

Еще одно простое правило: выбрать как можно большее k, но не превышающее n/5:

Критерий Крамера-фон Мизеса дает хорошие результаты при малых объемах выборок (менее 10). Однако вопрос о доверительной вероятности остается нерешенным (эта вероятность мала при значительных размерах доверительных интервалов.
Исходя из этого, полагают, что реальные объемы выборок, которые можно получить, находятся в диапазоне от 10 до 100.

Критерий Колмогорова-Смирнова также целесообразно использовать для выборки указанных объемов в тех случаях, когда проверяемое распределение непрерывно и известны среднее значение и дисперсия проверяемой совокупности.
Алгоритм реализации критерия Колмогорова-Смирнова предполагает использование критического значения D extr для проверки принятой гипотезы. Для этого используется приведенная ниже табл. 1.

Решение

1. Критерий Хи-квадрат

1.1. Реализация в MathCad

1.2. Реализация в Excel

Формулы ячеек на листе Excel представлены в табл. 2.

Ячейка	Характеристика	Формула
В15	– число случаев исхода	=СЧЁТЕСЛИ($B$3:$H$7;A15)
С15	– вероятность наступления	=ПУАССОН.РАСП(A15;$E$11;ЛОЖЬ)
D15	– ожидаемое число случаев исхода	=ОКРУГЛ(C15*$H$9;0)
H19	– статистика Хи-квадрат	=СУММ(H15:H18)
H23	– критическое значение Хи-квадрата (максимальное значение для заданного уровня значимости)	=ХИ2.ОБР(1-H22;H21)
J19	– p-value (вероятность получить расчетное значение Хи-квадрата)	=ХИ2.РАСП.ПХ(H19;H21)
J20	– Хи-квадрат тест	=ХИ2.ТЕСТ(F15:F18;G15:G18)

2. Критерий Колмогорова-Смирнова

Литература

Емельянов А.А., Власова Е.А., Дума Р.В. Имитационное моделирование экономических процессов: уч. пособ. - М.: Финансы и статистика, 2002. - 368с.
Кобзарь А. И. Прикладная математическая статистика. Для инженеров и научных работников. - М.: ФИЗМАТЛИТ, 2006. - 816 с.

Читайте также: