Хи2обр excel что это

74. Функция хи2обр

Описание:Возвращает значение, обратное односторонней вероятности распределения хи-квадрат. Если вероятность = ХИ2РАСП(x;. ), то ХИ2ОБР(вероятность;. ) = x. Данная функция позволяет сравнить наблюдаемые результаты с ожидаемыми, чтобы определить, была ли верна исходная гипотеза.

ХИ2ОБР(вероятность;степени_свободы)

Вероятность— вероятность, связанная с распределением c2 (хи-квадрат).

Степени_свободы— число степеней свободы.

— Если какой-либо из аргументов не является числом, функция ХИ2ОБР возвращает значение ошибки #ЗНАЧ!.

— Если вероятность < 0 или вероятность > 1, функция ХИ2ОБР возвращает значение ошибки #ЧИСЛО!.

— Если значение аргумента «степени_свободы» не является целым числом, оно усекается.

— Если аргумент «Степени_свободы» < 1 или ≥ 10^10, функция ХИ2ОБР возвращает значение ошибки #ЧИСЛО!.

Если задано значение вероятности, то функция ХИ2ОБР ищет значение x, для которого функция ХИ2РАСП(x; степень_свободы) = вероятность. Однако точность функции ХИ2ОБР зависит от точности ХИ2РАСП. В функции ХИ2ОБР для поиска применяется метод итераций. Если поиск не закончился после 100 итераций, функция возвращает сообщение об ошибке #Н/Д.

75. Функция хи2расп

Описание:Возвращает одностороннюю вероятность распределения хи-квадрат. Распределение χ 2 связано с критерием χ 2 . Критерий χ 2 используется для сравнения предполагаемых и наблюдаемых значений. Например, в генетическом эксперименте выдвигается гипотеза, что следующее поколение растений будет обладать определенной окраской. Сравнивая наблюдаемые результаты с предполагаемыми, можно определить, была ли верна исходная гипотеза.

ХИ2РАСП(x;степени_свободы)

x— значение, для которого требуется вычислить распределение.

Степени_свободы— число степеней свободы.

— Если какой-либо из аргументов не является числом, функция ХИ2РАСП возвращает значение ошибки #ЗНАЧ!.

— Если x отрицательное значение, функция ХИ2РАСП возвращает значение ошибки #ЧИСЛО!.

— Если значение аргумента «степени_свободы» не является целым числом, оно усекается.

— Если степени_свободы < 1 или степени_свободы > 10^10, функция ХИ2РАСП возвращает значение ошибки #ЧИСЛО!.

— ХИ2РАСП вычисляется как ХИ2РАСП = P(X> x), где x — χ 2 случайная величина.

76. Функция хи2тест

Описание:Возвращает критерий независимости. Функция ХИ2ТЕСТ возвращает значение статистики для распределения хи-квадрат (χ 2 ) и соответствующее число степеней свободы. Критерий χ 2 используется, чтобы определить, подтверждается ли гипотеза экспериментом.

ХИ2ТЕСТ(фактический_интервал;ожидаемый_интервал)

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

Ожидаемый_интервал— интервал данных, который содержит отношение произведений итогов по строкам и столбцам к общему итогу.

— Если фактический_интервал и ожидаемый_интервал имеют различное количество точек данных, то функция ХИ2ТЕСТ возвращает значение ошибки #Н/Д.

— Критерий χ 2 сначала вычисляет статистику χ 2 по формуле:

A_ij— фактическая частота в i-й строке, j-м столбце;

E_ij— ожидаемая частота в i-й строке, j-м столбце;

c — число столбцов.

— Нижнее значение критерия χ 2 является признаком независимости. Как видно из формулы, критерий χ 2 всегда положительный или равен 0, а последнее возможно только в том случае, если A_ij = E_ij при любых значениях i, j.

— Функция ХИ2ТЕСТ возвращает вероятность того, что при условии независимости может быть получено такое значение статистики χ 2 , которое будет по крайней мере не ниже значения, рассчитанного по приведенной выше формуле. Для вычисления этой вероятности, функцией ХИ2ТЕСТ используется распределение χ 2 с соответствующим числом степеней свободы (df). Если r > 1, а c > 1, то df = (r — 1)(c — 1). Если r = 1, а c > 1, то df = c — 1; если же r > 1, а c = 1, то df = r — 1. Равенство, где r = c= 1, недопустимо, поэтому появляется сообщение об ошибке #Н/Д.

— Использовать функцию ХИ2ТЕСТ лучше всего при не слишком малых значениях E_ij. Некоторые специалисты по статистике полагают, что значение E_ij всегда должно быть больше или равно 5.

Excel функция ХИ2ОБР (CHIINV)

Microsoft Excel функция ХИ2ОБР возвращает значение, обратное односторонней вероятности распределения хи-квадрат.
Функция ХИ2ОБР — это встроенная функция Excel, относящаяся к категории статистических функций.
Её можно использовать как функцию рабочего листа (WS) в Excel.
Как функцию рабочего листа, функцию ХИ2ОБР можно ввести как часть формулы в ячейку рабочего листа.

Синтаксис

Синтаксис функции ХИ2ОБР в Microsoft Excel:

Аргументы или параметры

Возвращаемое значение

Функция ХИ2ОБР возвращает числовое значение.
Если вероятность вероятность > 1, функция ХИ2ОБР вернет ошибку #ЧИСЛО!.
Если вероятность не является числом, функция ХИ2ОБР вернет ошибку #ЗНАЧ!.
Если степени_свободы степени_свободы > 10 10 , функция ХИ2ОБР вернет ошибку #ЧИСЛО!.
Если степени_свободы не является числом, функция ХИ2ОБР вернет ошибку #ЗНАЧ!.

Применение

• Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 для Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Тип функции

• Функция рабочего листа (WS)

Пример (как функция рабочего листа)

Рассмотрим несколько примеров функции Excel ХИ2ОБР, чтобы понять, как использовать Excel функцию ХИ2ОБР в качестве функции рабочего листа в Microsoft Excel:

Критерий согласия Пирсона χ2 (Хи-квадрат)

До конца XIX века нормальное распределение считалась всеобщим законом вариации данных. Однако К. Пирсон заметил, что эмпирические частоты могут сильно отличаться от нормального распределения. Встал вопрос, как это доказать. Требовалось не только графическое сопоставление, которое имеет субъективный характер, но и строгое количественное обоснование.

Так был изобретен критерий χ 2 (хи квадрат), который проверяет значимость расхождения эмпирических (наблюдаемых) и теоретических (ожидаемых) частот. Это произошло в далеком 1900 году, однако критерий и сегодня на ходу. Более того, его приспособили для решения широкого круга задач. Прежде всего, это анализ категориальных данных, т.е. таких, которые выражаются не количеством, а принадлежностью к какой-то категории. Например, класс автомобиля, пол участника эксперимента, вид растения и т.д. К таким данным нельзя применять математические операции вроде сложения и умножения, для них можно только подсчитать частоты.

Наблюдаемые частоты обозначим О (Observed), ожидаемые – E (Expected). В качестве примера возьмем результат 60-кратного бросания игральной кости. Если она симметрична и однородна, вероятность выпадения любой стороны равна 1/6 и, следовательно, ожидаемое количество выпадения каждой из сторон равна 10 (1/6∙60). Наблюдаемые и ожидаемые частоты запишем в таблицу и нарисуем гистограмму.

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

1. Обобщающая мера расхождения между наблюдаемыми и ожидаемыми частотами.
2. Распределение этой меры при справедливости гипотезы о том, что различий нет.

Начнем с расстояния между частотами. Если взять просто разницу О — E, то такая мера будет зависеть от масштаба данных (частот). Например, 20 — 5 =15 и 1020 – 1005 = 15. В обоих случаях разница составляет 15. Но в первом случае ожидаемые частоты в 3 раза меньше наблюдаемых, а во втором случае – лишь на 1,5%. Нужна относительная мера, не зависящая от масштаба.

Обратим внимание на следующие факты. В общем случае количество категорий, по которым измеряются частоты, может быть гораздо больше, поэтому вероятность того, что отдельно взятое наблюдение попадет в ту или иную категорию, довольно мала. Раз так, то, распределение такой случайной величины будет подчинятся закону редких событий, известному под названием закон Пуассона. В законе Пуассона, как известно, значение математического ожидания и дисперсии совпадают (параметр λ). Значит, ожидаемая частота для некоторой категории номинальной переменной E_i будет являться одновременное и ее дисперсией. Далее, закон Пуассона при большом количестве наблюдений стремится к нормальному. Соединяя эти два факта, получаем, что, если гипотеза о согласии наблюдаемых и ожидаемых частот верна, то, при большом количестве наблюдений, выражение

Важно помнить, что нормальность будет проявляться только при достаточно больших частотах. В статистике принято считать, что общее количество наблюдений (сумма частот) должна быть не менее 50 и ожидаемая частота в каждой группе должна быть не менее 5. Только в этом случае величина, показанная выше, имеет стандартное нормальное распределение. Предположим, что это условие выполнено.

У стандартного нормального распределения почти все значение находятся в пределах ±3 (правило трех сигм). Таким образом, мы получили относительную разность в частотах для одной группы. Нам нужна обобщающая мера. Просто сложить все отклонения нельзя – получим 0 (догадайтесь почему). Пирсон предложил сложить квадраты этих отклонений.

Это и есть статистика для критерия Хи-квадрат Пирсона. Если частоты действительно соответствуют ожидаемым, то значение статистики Хи-квадрат будет относительно не большим (отклонения находятся близко к нулю). Большое значение статистики свидетельствует в пользу существенных различий между частотами.

«Большой» статистика Хи-квадрат становится тогда, когда появление наблюдаемого или еще большего значения становится маловероятным. И чтобы рассчитать такую вероятность, необходимо знать распределение статистики Хи-квадрат при многократном повторении эксперимента, когда гипотеза о согласии частот верна.

Как нетрудно заметить, величина хи-квадрат также зависит от количества слагаемых. Чем больше слагаемых, тем больше ожидается значение статистики, ведь каждое слагаемое вносит свой вклад в общую сумму. Следовательно, для каждого количества независимых слагаемых, будет собственное распределение. Получается, что χ 2 – это целое семейство распределений.

И здесь мы подошли к одному щекотливому моменту. Что такое число независимых слагаемых? Вроде как любое слагаемое (т.е. отклонение) независимо. К. Пирсон тоже так думал, но оказался неправ. На самом деле число независимых слагаемых будет на один меньше, чем количество групп номинальной переменной n. Почему? Потому что, если мы имеем выборку, по которой уже посчитана сумма частот, то одну из частот всегда можно определить, как разность общего количества и суммой всех остальных. Отсюда и вариация будет несколько меньше. Данный факт Рональд Фишер заметил лет через 20 после разработки Пирсоном своего критерия. Даже таблицы пришлось переделывать.

По этому поводу Фишер ввел в статистику новое понятие – степень свободы (degrees of freedom), которое и представляет собой количество независимых слагаемых в сумме. Понятие степеней свободы имеет математическое объяснение и проявляется только в распределениях, связанных с нормальным (Стьюдента, Фишера-Снедекора и сам Хи-квадрат).

Чтобы лучше уловить смысл степеней свободы, обратимся к физическому аналогу. Представим точку, свободно движущуюся в пространстве. Она имеет 3 степени свободы, т.к. может перемещаться в любом направлении трехмерного пространства. Если точка движется по какой-либо поверхности, то у нее уже две степени свободы (вперед-назад, вправо-влево), хотя и продолжает находиться в трехмерном пространстве. Точка, перемещающаяся по пружине, снова находится в трехмерном пространстве, но имеет лишь одну степень свободы, т.к. может двигаться либо вперед, либо назад. Как видно, пространство, где находится объект, не всегда соответствует реальной свободе перемещения.

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

Таким образом, распределение хи квадрат (χ 2 ) – это семейство распределений, каждое из которых зависит от параметра степеней свободы. Формальное определение следующее. Распределение χ 2 (хи-квадрат) с k степенями свободы — это распределение суммы квадратов k независимых стандартных нормальных случайных величин.

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

Интересно посмотреть, как меняется форма распределения хи-квадрат в зависимости от количества степеней свободы.

С увеличением степеней свободы распределение хи-квадрат стремится к нормальному. Это объясняется действием центральной предельной теоремы, согласно которой сумма большого количества независимых случайных величин имеет нормальное распределение. Про квадраты там ничего не сказано )).

Проверка гипотезы по критерию Хи квадрат Пирсона

Вот мы и подошли к проверке гипотез по методу хи-квадрат. В целом техника остается прежней. Выдвигается нулевая гипотеза о том, что наблюдаемые частоты соответствуют ожидаемым (т.е. между ними нет разницы, т.к. они взяты из той же генеральной совокупности). Если этот так, то разброс будет относительно небольшим, в пределах случайных колебаний. Меру разброса определяют по статистике Хи-квадрат. Далее либо полученную статистику сравнивают с критическим значением (для соответствующего уровня значимости и степеней свободы), либо, что более правильно, рассчитывают наблюдаемый p-value, т.е. вероятность получить такое или еще больше значение статистики при справедливости нулевой гипотезы.

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

Вернемся к задаче с игральной костью. Рассчитаем по имеющимся данным значение статистики критерия хи-квадрат.

Теперь найдем критическое значение при 5-ти степенях свободы (k) и уровне значимости 0,05 (α) по таблице критических значений распределения хи квадрат.

То есть квантиль 0,05 хи квадрат распределения (правый хвост) с 5-ю степенями свободы χ 2 _{0,05; 5} = 11,1.

Сравним фактическое и табличное значение. 3,4 (χ 2 ) < 11,1 (χ 2 _{0,05; 5}). Расчетный значение оказалось меньшим, значит гипотеза о равенстве (согласии) частот не отклоняется. На рисунке ситуация выглядит вот так.

Если бы расчетное значение попало в критическую область, то нулевая гипотеза была бы отклонена.

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

Ниже их краткое описание.

ХИ2.ОБР – критическое значение Хи-квадрат при заданной вероятности слева (как в статистических таблицах)

ХИ2.ОБР.ПХ – критическое значение при заданной вероятности справа. Функция по сути дублирует предыдущую. Но здесь можно сразу указывать уровень α, а не вычитать его из 1. Это более удобно, т.к. в большинстве случаев нужен именно правый хвост распределения.

ХИ2.РАСП – p-value слева (можно рассчитать плотность).

ХИ2.РАСП.ПХ – p-value справа.

ХИ2.ТЕСТ – по двум диапазонам частот сразу проводит тест хи-квадрат. Количество степеней свободы берется на одну меньше, чем количество частот в столбце (так и должно быть), возвращая значение p-value.

Давайте пока рассчитаем для нашего эксперимента критическое (табличное) значение для 5-ти степеней свободы и альфа 0,05. Формула Excel будет выглядеть так:

Результат будет одинаковым – 11,0705. Именно это значение мы видим в таблице (округленное до 1 знака после запятой).

Рассчитаем, наконец, p-value для 5-ти степеней свободы критерия χ 2 = 3,4. Нужна вероятность справа, поэтому берем функцию с добавкой ПХ (правый хвост)

Значит, при 5-ти степенях свободы вероятность получить значение критерия χ 2 = 3,4 и больше равна почти 64%. Естественно, гипотеза не отклоняется (p-value больше 5%), частоты очень хорошо согласуются.

А теперь проверим гипотезу о согласии частот с помощью теста хи квадрат и функции Excel ХИ2.ТЕСТ.

Никаких таблиц, никаких громоздких расчетов. Указав в качестве аргументов функции столбцы с наблюдаемыми и ожидаемыми частотами, сразу получаем p-value. Красота.

Представим теперь, что вы играете в кости с подозрительным типом. Распределение очков от 1 до 5 остается прежним, но он выкидывает 26 шестерок (количество всех бросков становится 78).

p-value в этом случае оказывается 0,003, что гораздо меньше чем, 0,05. Есть серьезные основания сомневаться в правильности игральной кости. Вот, как выглядит эта вероятность на диаграмме распределения хи-квадрат.

Статистика критерия хи-квадрат здесь получается 17,8, что, естественно, больше табличного (11,1).

Надеюсь, мне удалось объяснить, что такое критерий согласия χ 2 (хи-квадрат) Пирсона и как с его помощью проверяются статистические гипотезы.

Напоследок еще раз о важном условии! Критерий хи-квадрат исправно работает только в случае, когда количество всех частот превышает 50, а минимальное ожидаемое значение для каждой группы не меньше 5. Если в какой-либо категории ожидаемая частота менее 5, но при этом сумма всех частот превышает 50, то такую категорию объединяют с ближайшей, чтобы их общая частота превысила 5. Если это сделать невозможно, или сумма частот меньше 50, то следует использовать более точные методы проверки гипотез. О них поговорим в другой раз.

Ниже находится видео ролик о том, как в Excel проверить гипотезу с помощью критерия хи-квадрат.

Таблица формул Excel для теории вероятностей и статистики

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

Выдает случайное число в интервале от 0 до 1 (равномерно распределенное).

Выдает случайное число в заданном интервале.

Вычисляет отдельное значение биномиального распределения.

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

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

Выдает обратное нормальное распределение.

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

Выдает обратное значение стандартного нормального распределения.

Определяет вероятность того, что значение из диапазона находится внутри заданных пределов.

Математическая статистика

При решении задач по математической статистике можно использовать те формулы, что перечислены выше, а также следующие (сгруппированы для удобства: обработка выборки, разные распределения, остальные формулы):

Обработка выборки: формулы Excel

Вычисляет среднее абсолютных значений отклонений точек данных от среднего.

Вычисляет среднее арифметическое аргументов.

Вычисляет среднее геометрическое.

Вычисляет среднее гармоническое.

Определяет эксцесс множества данных.

Находит медиану заданных чисел.

Определяет значение моды множества данных.

Определяет квартиль множества данных.

Определяет асимметрию распределения.

Оценивает стандартное отклонение по выборке.

Оценивает дисперсию по выборке.

Законы распределений: формулы Excel

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

Определяет обратную функцию к интегральной функции плотности бета-вероятности.

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

Вычисляет обратное значение односторонней вероятности распределения хи-квадрат.

Находит экспоненциальное распределение.

Находит F-распределение вероятности.

Определяет обратное значение для F-распределения вероятности.

Находит преобразование Фишера.

Находит обратное преобразование Фишера.

Находит обратное гамма-распределение.

Выдает распределение Пуассона.

Выдает t-распределение Стьюдента.

Выдает обратное t-распределение Стьюдента.

Выдает распределение Вейбулла.

Другое (корреляция, регрессия и т.п.)

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

Находит коэффициент корреляции между двумя множествами данных.

Подсчитывает количество чисел в списке аргументов.

Подсчитывает количество непустых ячеек, удовлетворяющих заданному условию внутри диапазона.

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

Вычисляет значение линейного тренда.

Находит параметры линейного тренда.

Определяет коэффициент корреляции Пирсона.

Справочный файл по формулам Excel

Нужна шпаргалка по функциям Excel под рукой? Скачивайте файл: Математические и статистические формулы Excel

Полезные ссылки

А если у вас есть задачи, которые надо срочно сделать, а времени нет? Можете поискать готовые решения в решебнике: