Хи квадрат как считать эксель

Функция Excel для теста хи-квадрат — Руководство, примеры, как использовать

Функция CHISQ.DIST относится к категории функций статистических функций Excel. Список наиболее важных функций Excel для финансовых аналитиков. Эта шпаргалка охватывает 100 функций, которые критически важно знать аналитику Excel. Он вернет распределение хи-квадрат. Распределение обычно используется для изучения процентных вариаций между выборками. В этом руководстве подробно рассматривается функция Excel для проверки хи-квадрат.

В финансовом анализе Описание работы финансового аналитика В описании должности финансового аналитика ниже приводится типичный пример всех навыков, образования и опыта, необходимых для работы аналитиком в банке, учреждении или корпорации. Выполняйте финансовое прогнозирование, отчетность и отслеживание операционных показателей, анализируйте финансовые данные, создавайте финансовые модели, эта функция может быть полезна при обнаружении вариаций в индивидуальных предположениях. Например, предположим, что у нас есть компания по производству джинсов. Мы производим джинсы только двух цветов — черного и синего. Если мы хотим узнать долю проданных синих джинсов по отношению к общему количеству произведенных джинсов, мы можем использовать Chi Square в Excel.

Функция CHISQ.DIST была введена в MS Excel 2010 и поэтому недоступна в более ранних версиях.

Формула Хи-квадрат в Excel

= ХИСК.РАСП (x; степень_свободы; накопительное)

CHISQ.DIST использует следующие аргументы:

1. X (обязательный аргумент) — это значение, при котором должно оцениваться распределение хи-квадрат. Он должен быть больше или равен нулю.
2. Deg_freedom (обязательный аргумент) — это количество степеней свободы. Это должно быть целое число от 1 до 1010.
3. Накопительное (обязательный аргумент) — это логическое значение, определяющее форму функции. Это может быть ИСТИНА (используется кумулятивная функция распределения) или ЛОЖЬ (будет использоваться функция плотности вероятности).

Как использовать функцию хи-квадрат

Чтобы понять использование функции хи-квадрат в Excel, давайте рассмотрим пример:

пример

Допустим, нам даны следующие данные:

Формула для расчета распределения CHISQ с использованием кумулятивной функции распределения показана ниже:

Получаем результат ниже:

Формула для расчета распределения CHISQ с использованием функции плотности вероятности показана ниже:

Получаем результат ниже:

Примечания о функции Excel с хи-квадрат

1. Если аргумент deg_freedom не является целым числом, он усекается MS Excel.
2. #ЦЕННОСТЬ! ошибка — возникает, когда любой из предоставленных аргументов не является числовым.
3. # ЧИСЛО! ошибка — возникает, когда:
   1. Данное значение x отрицательно.
   2. Аргумент deg_freedom меньше 1 или больше 1010.

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

   Спасибо, что прочитали финансовое руководство по функции Excel для теста хи-квадрат! Потратив время на изучение и освоение этих функций, вы значительно ускорите свой финансовый анализ. Чтобы узнать больше, ознакомьтесь с этими дополнительными финансовыми ресурсами:

   Поиск функций хи-квадрат в Excel

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

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

   Детали хи-квадрат

   Прежде чем увидеть, что умеет Excel, давайте вспомним некоторые детали, касающиеся распределения хи-квадрат. Это распределение вероятностей, которое асимметрично и сильно смещено вправо. Значения распределения всегда неотрицательны. На самом деле существует бесконечное количество распределений хи-квадрат. То, что нас интересует, определяется количеством степеней свободы, которые у нас есть в нашем приложении. Чем больше число степеней свободы, тем меньше будет искажение нашего распределения хи-квадрат.

   Использование хи-квадрат

   Распределение хи-квадрат используется для нескольких приложений. Это включает:

   • Тест хи-квадрат — чтобы определить, независимы ли уровни двух категориальных переменных друг от друга.
   • Тест согласия — чтобы определить, насколько хорошо наблюдаемые значения одной категориальной переменной соответствуют значениям, ожидаемым теоретической моделью.
   • Мультиномиальный эксперимент — это конкретное использование теста хи-квадрат.

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

   CHISQ.DIST и CHISQ.DIST.RT в Excel

   В Excel есть несколько функций, которые мы можем использовать при работе с распределениями хи-квадрат. Первый из них — ХИСК.РАСП (). Эта функция возвращает левостороннюю вероятность указанного распределения хи-квадрат. Первый аргумент функции — это наблюдаемое значение статистики хи-квадрат. Второй аргумент — это количество степеней свободы. Третий аргумент используется для получения кумулятивного распределения.

   CHISQ.DIST.RT () тесно связан с CHISQ.DIST. Эта функция возвращает правостороннюю вероятность выбранного распределения хи-квадрат. Первый аргумент — это наблюдаемое значение статистики хи-квадрат, а второй аргумент — это количество степеней свободы.

   Например, если ввести в ячейку = CHISQ.DIST (3, 4, true), будет выведено 0,442175. Это означает, что для распределения хи-квадрат с четырьмя степенями свободы 44,2175% площади под кривой лежит слева от 3. Если ввести = ХИКО.РАСП.RT (3, 4) в ячейку, получится 0,557825. Это означает, что для распределения хи-квадрат с четырьмя степенями свободы 55,7825% площади под кривой лежит справа от 3.

   Для любых значений аргументов CHISQ.DIST.RT (x, r) = 1 — CHISQ.DIST (x, r, true). Это потому, что часть распределения, которая не лежит слева от значения Икс должно лежать вправо.

   CHISQ.INV

   Иногда мы начинаем с области для определенного распределения хи-квадрат. Мы хотим знать, какое значение статистики нам потребуется, чтобы эта область располагалась слева или справа от статистики. Это обратная задача хи-квадрат, которая полезна, когда мы хотим узнать критическое значение для определенного уровня значимости. Excel решает подобные проблемы с помощью функции обратного хи-квадрат.

    

   Хи квадрат как считать эксель

   7.3.3.2 Проверка соответствия теоретическому распределению с использованием критерия согласия хи-квадрат
   В MS Excel критерий хи-квадрат реализован функцией ХИ2ТЕСТ . Эта функция вычисляет вероятность совпадения наблюдаемых (фактических) значений и теоретических (гипотетических) значений. Функция имеет параметры:
   ХИ2ТЕСТ(фактический_интервал; ожидае-мый_интервал), где
   — фактический_интервал – диапазон данных, который содержит результаты наблюдения, подлежащие сравнению с ожидаемыми значениями;
   — ожидаемый_интервал – диапазон данных, который содержит теоретические (ожидаемые) значения для соответствующих наблюдаемых.
   Для получения правильных результатов необходимо, чтобы объем выборки был не менее 40, выборочные данные сгруппированы в интервальный ряд с количеством интервалов не менее 7, а количество наблюдений в каждом интервале (частот) не менее 5.
   Пример 17. Проверить соответствие выборочных данных результатов сдачи экзаменов, оцененных в баллах: 48, 51, 67, 70, 64, 71, 85, 79, 80, 83, 86, 91, 99, 56, 66, 65, 84, 84, 84, 75, 76, 77, 78, 80, 86, 88, 58, 69, 65, 81, 75, 78, 85, 80, 80, 83, 86, 80, 89, 60, 68, 55, 82, 64, 71, 72, 72, 73, 74, 74, 79 нормальному закону распределения.
   Решение
   1. На рабочем листе подготовьте исходные данные в виде таблицы, содержащей баллы из приведенной выборки (См.рис.).

   2. Выберите ширину интервала равную 5 баллам начиная от 50 до 95 и введите в диапазон F2:F11 граничные значения интервалов (см. рис.).

   3. 3. Подготовьте заголовки создаваемой таблицы (ячейки G1, H1, I1)
   4. Применяя функцию ЧАСТОТА заполните столбец абсолютных частот.
   5. В ячейке H15 вычислите общее количество наблюдений, используя формулу =СУММ(G2:G11).
   6.В ячейке Н16 вычислим среднее значение выборки, а в ячейке Н17 – стандартное отклонение.
   7. Вычислите теоретические частости распределения. Поскольку мы проверяем соответствие заданной совокупности случайных величин нормальному закону распределения, то для расчета применим функцию НОРМРАСП. Установим курсор в ячейку H2 и вызовем из мастера функций функцию НОРМРАСП. Заполним поля аргу-ментов: x – F2, среднее – $Н$16, стандартное_откл. –$Н$17, ин-тегральный – 1, щелкнем на ОК.
   8.В ячейку H3 введем формулу =НОРМРАСП(F3;$H$16;$H$17;1)-СУММ($H$2:H2) .
   9. Скопируем введенную формулу в ячейки диапазона H4:H12.
   10. Для вычисления теоретических частот установим курсор в ячейку I2 и введем формулу = $H$16* H2. Скопируем содержимое этой ячейки в ячейки диапазона I3:I12.
   11.Используя функцию ХИ2ТЕСТ, определим соответствие данных выборки нормальному закону распределения. Для этого:
   • установим курсор в свободную ячейку I15, включим Мастер функций, выберем категорию Статистические, а в списке функций – функцию ХИ2ТЕСТ;
   • заполним поля аргументов функции: фактический – введем адрес диа-пазона абсолютных частот G2:G12, ожидаемый – адрес диапазона тео-ретических частот I2: I12. После щелчка на кнопке ОК — в ячейке I15 будет вычислено значение вероятности того, что выборочные данные соответствуют нормальному закону распределения – 0,917143314. Поскольку полученная вероятность соответствия экспериментальных данных p = 0,917143314 намного больше уровня значимости α = 0,05, то можно утверждать, нулевая гипотеза не может быть отвергнута и эксперимен-тальные данные не противоречат нормальному закону распределения. Но, так как полученное значение вероятности очень мало отличается от 1, то можно говорить о высокой степени вероятности того, что эксперименталь-ные данные соответствуют нормальному закону.
   Предыдущая Следующая Открыть содержание

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

   П риняв уровень значимости 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+1) — 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. Критерий Колмогорова-Смирнова

   

   Литература

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

   © В.Н. Кравченко
   Последнее обновление: 2018.11.03

    
   Похожие публикации:

   1. Как обойти защиту cloudflare
   2. Как узнать скорость зарядки
   3. Колодки ate как отличить подделку
   4. Кто поставляет экраны для iphone