Как сделать хи квадрат в эксель

Добавил пользователь Владимир З.
Обновлено: 09.10.2024

Содержание:

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

CHISQ.DIST.RT: распределение хи-квадрат

Функция CHISQ.DIST.RT, которая вычисляет правостороннюю вероятность распределения хи-квадрат, вычисляет уровень значимости, используя значение хи-квадрат и степени свободы. Значение хи-квадрат равно сумме квадратов стандартизированных баллов. Функция использует синтаксис

= CHISQ.DIST.RT (Икс,deg_freedom)

где Икс равно значению хи-квадрат и deg_freedom равняется степеням свободы.

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

Скажем, размер выборки 180, например. В этом случае вы могли бы ожидать, что каждая возможность игрового автомата появляется 30 раз, потому что 180/6 равно 30. Если вы построили фрагмент рабочего листа, как этот, вы можете проанализировать однорукий бандит.

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

Функция возвращает значение 0.010362338, которое является уровнем значимости, что значение хи-квадрат 15 связано с ошибкой выборки.

Ячейка D8 содержит значение хи-квадрат, которое является просто суммой квадратов разностей между наблюдаемыми и ожидаемыми значениями. Например, значение в ячейке D2 рассчитывается по формуле = + (B2 – C2) ^ 2 / C2 для возврата значения 3.333333333. Как и ожидалось, аналогичные формулы в диапазоне D3: D7 вычисляют квадратные различия для других символов игрового автомата. И, кстати, формула в ячейке D8 имеет вид = SUM (D2: D7).

Суть: это не выглядит хорошо, не так ли? Существует только 1-процентная вероятность того, что игровой автомат, о котором вы беспокоитесь, может действительно получить наблюдаемые значения из-за случайности. Очень подозрительно

CHISQ.DIST: распределение хи-квадрат

Функция CHISQ.DIST напоминает функцию CHISQ.DIST.RT, но вычисляет левый хвост вероятность распределения хи-квадрат. Функция использует синтаксис

= CHISQ.DIST (х, deg_freedom, кумулятивная)

где Икс равно значению хи-квадрат, deg_freedom равны степени свободы, и кумулятивный это переключатель, который вы установили на 0 или FALSE, если вы хотите рассчитать плотность вероятности, и на 1 или TRUE, если вы хотите рассчитать кумулятивную вероятность.

CHISQ.INV.RT: правосторонняя вероятность распределения хи-квадрат

Функция CHISQ.INV.RT возвращает значение, обратное к правосторонней вероятности распределения хи-квадрат. Функция использует синтаксис

= CHISQ.INV.RT (вероятность, deg_freedom)

где вероятность равняется уровню значимости и deg_freedom равняется степеням свободы.

Чтобы показать вам пример функции CHISQ.INV.RT, обратитесь к фрагменту листа. С шестью возможными результатами на игровом автомате у вас есть пять степеней свободы. Поэтому, если вы хотите рассчитать хи-квадрат, эквивалентный уровню значимости 0.010362338, вы можете ввести следующую формулу в ячейку D12:

Эта функция возвращает значение 14.99996888, которое чертовски близко к 15. Обратите внимание, что D10 используется в качестве первого вероятностного аргумента, потому что эта ячейка содержит уровень значимости, рассчитанный функцией CHISQ.DIST.

CHISQ.INV: Вероятность распределения хи-квадрат слева

Функция CHISQ.INV возвращает левостороннюю вероятность распределения хи-квадрат. Функция использует синтаксис

= CHISQ.INV (вероятность, deg_freedom)

где вероятность равняется уровню значимости и deg_freedom равняется степеням свободы.

Чтобы вычислить значение хи-квадрат, эквивалентное уровню значимости 0.010362338 с 5 степенями свободы, вы можете ввести в ячейку на листе следующую формулу:

Эта функция возвращает значение .562927.

CHISQ.TEST: Тест хи-квадрат

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

= CHISQ.TEST (actual_range, expected_range)

Снова обращаясь к примеру с подозрительным игровым автоматом, вы можете выполнить тест хи-квадрат, введя следующую формулу в ячейку D14, а затем сравнив то, что вы наблюдаете, с тем, что вы ожидаете:

= CHISQ.TEST (В2: В7, С2: С7)

Функция возвращает значение p или вероятность, показанную в ячейке D14, указывая, что существует только 1,0362-процентная вероятность того, что различия между наблюдаемыми и ожидаемыми результатами обусловлены ошибкой выборки.

Общей чертой теста хи-квадрат является сравнение значения p - опять же значения, которое возвращает функция CHISQ.TEST - с уровнем значимости. Например, в случае с подозрительным игровым автоматом вы можете сказать: «Поскольку невозможно быть уверенным на 100 процентов, мы скажем, что нам нужна 95-процентная вероятность, что соответствует 5-процентному уровню значимость."

Если значение p меньше уровня значимости, вы предполагаете, что что-то подозрительно. Статистики, не желая казаться такими приземленными, приводят еще одну фразу для этого довольно сомнительного вывода: отвергая нулевую гипотезу.

В этой статье описаны синтаксис формулы и использование функции ХИ2.ОБР в Microsoft Excel.

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

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

Синтаксис

Аргументы функции ХИ2.ОБР описаны ниже.

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

Степени_свободы — обязательный аргумент. Число степеней свободы.

Замечания

Если значение степени_свободы не целое, оно усекается.

Пример

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

Возвращает значение, обратное левосторонней вероятности распределения хи-квадрат, для 0,93 с 1 степенью свободы.

Возвращает значение, обратное левосторонней вероятности распределения хи-квадрат, для 0,6 с 2 степенями свободы.

Статистические функции хи-квадрат в Excel — 2021

Настя и сборник весёлых историй

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

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

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

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

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

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

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

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

CHISQ.DIST и CHISQ.DIST.RT в Excel

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

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

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

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

CHISQ.INV

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

Функция ПИРСОН (вводить следует PEARSON на английском) предназначена для вычисления коэффициента корреляции Пирсона r . Данную функцию используют в работе в том случае, когда необходимо отразить степень линейной зависимости между двумя массивами данных. В Excel имеется несколько функций с помощью которых можно получить такой же результат, однако универсальность и простота функции Пирсон делают выбор в ее пользу.

Как работает функция ПИРСОН в Excel?

Рассмотрим пример расчета корреляции Пирсона между двумя массивами данных при помощи функции PEARSON в MS EXCEL. Первый массив представляет собой значения температур, второй давление в определенный летний период. Пример заполненной таблицы изображен на рисунке:

Задача следующая: необходимо определить взаимосвязь между температурой и давлением за июнь месяц.

Пример решения с функцией ПИРСОН при анализе в Excel

Данный показатель -0,14 по Пирсону, который вернула функция, говорит об неблагоприятной зависимости температуры и давления в раннее время суток.

Функция ПИРСОН пошаговая инструкция

Коэффициент корреляции является самым удобным показателем сопряженности количественных признаков.

Задача: Определить линейный коэффициент корреляции Пирсона.

В таблице приведены данные для группы курящих людей. Первый массив х - представляет собой возраст курящего, второй массив y представляет собой количество сигарет, выкуренных в день.
Выберем ячейку В4 в которой должен будет посчитаться результат и нажмем кнопку мастер функций fx (SHIFT+F3).
В группе Статистические выберем функцию PEARSON.
Выделим Массив 1 – возраст курящего, затем Массив 2 – число сигарет, выкуренных в день.
Нажмем кнопку ОК и увидим критерий нормального распределения Пирсона в ячейке В4.

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

Корреляционный анализ по Пирсону в Excel

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

Пример решения: представим исходные данные в виде таблицы:

Интерпретация результата вычисления по Пирсону

Величина коэффициента линейной корреляции Пирсона не может превышать +1 и быть меньше чем -1. Эти два числа +1 и -1 – являются границами для коэффициента корреляции. Когда при расчете получается величина большая +1 или меньшая -1 – следовательно, произошла ошибка в вычислениях.

Если коэффициент корреляции по модулю оказывается близким к 1, то это соответствует высокому уровню связи между переменными.

Если же получен знак минус, то большей величине одного признака соответствует меньшая величина другого. Иначе говоря, при наличии знака минус, увеличению одной переменной (признака, значения) соответствует уменьшение другой переменной. Такая зависимость носит название обратно пропорциональной зависимости. Эти положения очень важно четко усвоить для правильной интерпретации полученной корреляционной зависимости.

В настоящей заметке ch 2 -распределение используется для проверки согласованности набора данных с фиксированным распределением вероятностей. В критерии согласия частоты, принадлежащие определенной категории, сравниваются с частотами, которые являются теоретически ожидаемыми, если бы данные действительно имели указанное распределение. [1]

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

где f₀ — наблюдаемая частота, f_е — теоретическая, или ожидаемая частота, k — количество категорий, оставшихся после объединения, р — количество оцениваемых параметров.

Использование ch 2 -критерия согласия для распределения Пуассона

Ранее распределение Пуассона использовалось для моделирования количества клиентов, прибывающих в отделение банка в течение минуты. Предположим, что в течение недели фактическое количество клиентов, приходящих в отделение банка в течение минуты, измерялось 200 раз (рис. 1).

Рис. 1. Распределение частоты прибытий в минуту во время ланча

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

Для расчета по этой формуле в Excel удобно воспользоваться функцией =СУММПРОИЗВ() (рис. 1).

Для оценки параметра l можно воспользоваться оценкой . Теоретическую частоту X успехов (Х = 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 и более), соответствующую параметру l = 2,9 можно определить с помощью функции =ПУАССОН.РАСП(Х; ;ЛОЖЬ). Умножив пуассоновскую вероятность на объем выборки n, получим теоретическую частоту f_e (рис. 2).

Рис. 2. Фактические и теоретические частоты прибытий в минуту

Для расчета ch 2 воспользуемся формулой (1) (рис. 3).

Рис. 3. Расчет ch 2 -критерия согласия для распределения Пуассона

Так как ch 2 = 2,277 2 -критерия согласия для нормального распределения

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

Рассмотрим в качестве примера данные о 5-летней доходности 158 инвестиционных фондов (рис. 4). Предположим, требуется поверить, имеют ли эти данные нормальное распределение. Нулевая и альтернативная гипотезы формулируются следующим образом: Н₀: 5-летняя доходность подчиняется нормальному распределению, Н₁: 5-летняя доходность не подчиняется нормальному распределению. Нормальное распределение имеет два параметра — математическое ожидание m и стандартное отклонение s, которые можно оценить на основе выборочных данных. В данном случае = 10,149 и S = 4,773.

Рис. 4. Упорядоченный массив, содержащий данные о пятилетней среднегодовой доходности 158 фондов

Данные о доходности фондов можно сгруппировать, разбив, например на классы (интервалы) шириной 5% (рис. 5).

Рис. 5. Распределение частот для пятилетней среднегодовой доходности 158 фондов

Поскольку нормальное распределение является непрерывным, необходимо определить площадь фигур, ограниченных кривой нормального распределения и границами каждого интервала. Кроме того, поскольку нормальное распределение теоретически изменяется от –? до +?, необходимо учитывать площадь фигур, выходящих за пределы классов. Итак, площадь, лежащая под нормальной кривой слева от точки –10, равна площади фигуры, лежащей под стандартизованной нормальной кривой слева от величины Z, равной

Z = (–10 – 10,149) / 4,773 = –4,22

Площадь фигуры, лежащей под стандартизованной нормальной кривой слева от величины Z = –4,22 определяется по формуле =НОРМ.РАСП(-10;10,149;4,773;ИСТИНА) и приближенно равна 0,00001. Для того чтобы вычислить площадь фигуры, лежащей под нормальной кривой между точками –10 и –5, сначала необходимо вычислить площадь фигуры, лежащей слева от точки –5: =НОРМ.РАСП(-5;10,149;4,773;ИСТИНА) = 0,00075. Итак, площадь фигуры, лежащей под нормальной кривой между точками –10 и –5, равна 0,00075 – 0,00001 = 0,00074. Аналогично можно вычислить площадь фигуры, ограниченной границами каждого класса (рис. 6).

Рис. 6. Площади и ожидаемые частоты для каждого класса 5-летней доходности

Видно, что теоретические частоты в четырех крайних классах (два минимальных и два максимальных) меньше 1, поэтому проведем объединение классов, как показано на рис 7.

Рис. 7. Вычисления, связанные с применением ch 2 -критерия согласия для нормального распределения

Используем ch 2 -критерий согласия данных с нормальным распределением с помощью формулы (1). В нашем примере после объединения остаются шесть классов. Поскольку математическое ожидание и стандартное отклонение оцениваются на основе выборочных данных, количество степеней свободы равно k – p – 1 = 6 – 2 – 1 = 3. Используя уровень значимости, равный 0,05, находим, что критическое значение ch 2 -статистики, имеющее три степени свободы =ХИ2.ОБР(1-0,05;F3) = 7,815. Вычисления, связанные с применением ch 2 -критерия согласия, приведены на рис. 7.

Видно, что ch 2 -статистика = 3,964 2 7,815, следовательно гипотезу Н₀ отклонять нельзя. Иначе говоря, у нас нет оснований утверждать, что 5-летняя доходность инвестиционных фондов, ориентированных на быстрый рост, не подчиняется нормальному распределению.

Рис. 8. Структурная схема методов проверки гипотез о категорийных данных

[1] Используются материалы книги Левин и др. Статистика для менеджеров. – М.: Вильямс, 2004. – с. 763–769

В Excel мы вычисляем p-значение хи-квадрат. Поскольку в Excel нет встроенной функции, для выполнения теста хи-квадрат используются математические формулы. Существует два типа тестов хи-квадрат, которые перечислены ниже: Тест согласия по критерию хи-квадрат.

Как найти значение хи-квадрат?

Критическое значение для статистики хи-квадрат определяется уровнем значимости (обычно 0,05) и степенями свободы. Степени свободы хи-квадрат вычисляются по следующей формуле: df = (r-1) (c-1) где r - количество строк, а c - количество столбцов.

Что вам говорит тест хи-квадрат?

Хи-квадрат (ch 2 ) статистика - это тест, который измеряет, насколько модель сравнивается с фактическими наблюдаемыми данными. . Статистика хи-квадрат сравнивает размер любых расхождений между ожидаемыми результатами и фактическими результатами, учитывая размер выборки и количество переменных во взаимосвязи.

Как рассчитать хи-квадрат вручную?

Шаг 1. Вычтите каждую ожидаемую частоту из соответствующей наблюдаемой частоты. .
Шаг 2: возведите в квадрат каждое значение, полученное на шаге 1, т. Е. (O-E) 2 . .
Шаг 3: разделите все значения, полученные на шаге 2, на соответствующие ожидаемые частоты, т. Е. (O-E) 2 / E.

Что такое p-значение для теста хи-квадрат?

Значение P равно вероятность того, что статистика хи-квадрат, имеющая 2 степени свободы, более экстремальна, чем 19,58. Мы используем калькулятор распределения хи-квадрат, чтобы найти P (CH 2 > 19,58) = 0,0001. Интерпретируйте результаты. Поскольку значение P (0,0001) меньше уровня значимости (0,05), мы не можем принять нулевую гипотезу.

Что такое критерий хи-квадрат на независимость?

Что такое критерий независимости хи-квадрат? Критерий независимости хи-квадрат проверка статистической гипотезы, используемая для определения того, связаны ли две категориальные или номинальные переменные между собой.

Что такое формула значения p?

Значение p вычисляется с использованием выборочного распределения статистики теста при нулевой гипотезе, выборочных данных и типа выполняемого теста (нижний хвостовой тест, верхний хвостовой тест или двусторонний тест). . тест с верхним хвостом определяется: p-значение = P (TS ts | H ₀ верно) = 1 - cdf (ts)

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