Как рассчитать хи-квадрат в Excel

Обновлено: 21.11.2024

CHISQ.TEST – это статистическая функция в Excel, которая вычисляет статистику хи-квадрат двух переменных в наборе данных.

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

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

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

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

По теме:

Когда использовать тест независимости хи-квадрат?
Формула распределения критерия хи-квадрат
Функция Excel для теста хи-квадрат — синтаксис
Как использовать функцию CHISQ.TEST в Excel?
Что нужно помнить о формуле CHISQ.TEST

Вы можете загрузить практический лист Excel и следовать этому руководству.

Когда использовать тест независимости хи-квадрат?

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

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

В этом примере я проверю взаимосвязь между полом (мужской и женский) и предпочтениями при голосовании (слева, справа и по центру).

Формула распределения критерия хи-квадрат

Простейшая версия формулы распределения хи-квадрат приведена ниже:

"r" – это количество строк.
c — количество столбцов.
Oij – наблюдаемая частота в строке "i" и столбце "j".
Eij – ожидаемая частота в строке "i" и столбце "j".

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

Обратите внимание, что функция Excel для теста хи-квадрат (CHISQ.TEST) недоступна в версиях, предшествующих Excel 2010.

Это обновленная версия функции CHITEST.

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

Функция Excel для теста хи-квадрат — синтаксис

В версиях Excel 2010 и более поздних есть встроенная функция CHISQ.TEST для быстрого вычисления статистического значения хи-квадрат.

Он имеет следующий синтаксис:

= CHISQ.TEST(фактический_диапазон,ожидаемый_диапазон)

Где фактический_диапазон — это диапазон данных ячеек, содержащих наблюдаемые частоты.

Expected_range – это диапазон данных ячеек, содержащих ожидаемые частоты.

Также читайте:

Как использовать функцию CHISQ.TEST в Excel?

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

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

Для этого примера

Hₒ (нулевая гипотеза): предпочтения пола и голосования не зависят друг от друга.

H₁ (альтернативная гипотеза): предпочтения пола и голосования не являются независимыми.

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

Прежде чем мы начнем, упорядочите свой набор данных следующим образом:

Обратите внимание, что строки содержат частоты одной категориальной переменной (пол), а столбцы содержат частоты другой категориальной переменной (предпочтения при голосовании). Кроме того, подсчитайте итоги строк и столбцов, как показано выше.

Затем в другом месте рассчитайте ожидаемую частоту появления того же набора данных. Это очень просто и определяется по формуле = (сумма строк x сумма столбцов) / общая сумма.

Использовать функцию CHISQ.TEST очень просто. Все, что вам нужно сделать, это просто ввести диапазоны наблюдаемых и ожидаемых частот внутри формулы.

В этом примере я использую формулу =CHISQ.TEST(B3:D4,B10:D11)

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

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

Обычно значение p менее 0,05 (альфа — доверительный интервал) считается значимым. То есть, если p-значение равно 0,0125, то есть 1.25% вероятность того, что различия вызваны простой случайностью. Чем меньше это значение, тем выше значимость.

Более высокое значение значимости означает, что нулевая гипотеза (Hₒ) отвергается.

Следовательно, в данном случае Hₒ отклоняется. Это означает, что пол и предпочтения при голосовании в значительной степени зависят друг от друга.

Вы можете использовать формулу ЕСЛИ, чтобы сравнить p-значение и альфа-канал, чтобы проверить значимость и вернуть принятую гипотезу.

В этом примере я использую =IF(H7

Что нужно помнить о формуле CHISQ.TEST

Предлагаемые чтения:

Что такое критерий согласия Хи-квадрат?

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

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

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

Заключительные мысли

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

Если вам нужны дополнительные высококачественные руководства по Excel, посетите наш бесплатный ресурсный центр Excel.

Готовы погрузиться в Excel? Саймон Сез ИТ преподает Excel более десяти лет. За небольшую ежемесячную плату вы можете получить доступ к более чем 100 обучающим курсам по ИТ.

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

Адам Лейси

Адам Лейси – энтузиаст Excel и эксперт по онлайн-обучению. Он сочетает эти две страсти в Simon Sez IT, где он носит несколько разных шляп. Когда Адама не беспокоит посещаемость сайта или сводные таблицы, вы найдете его на теннисном корте или на кухне, готовя бурю.

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

Функции Excel, формулы, диаграммы, форматирование, создание информационной панели Excel и др.

Тест хи-квадрат – это проверка правильности гипотезы. Значение хи-квадрат P говорит нам, являются ли наши наблюдаемые результаты статистически значимыми или нет. Статистически значимый результат означает, что мы отвергаем нулевую гипотезу (нулевая гипотеза в статистике — это утверждение или гипотеза, которые могут оказаться неверными). P-значение хи-квадрат — это число от 0 до 1. P-значение хи-квадрат меньше 0,05 обычно приводит к отклонению нулевой гипотезы.

Как выполнить тест хи-квадрат в Excel?

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

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

Предположим, ваша компания имеет 10 000 единиц мебели. Примерно каждый десятый из них распределен по четырем залам. Мы можем узнать, какая доля общей мебели находится в одном зале, как показано ниже:

Обратите внимание, что у нас около 250 предметов мебели в каждом зале. Если мы хотим получить ожидаемое количество мебели по типам, то рассчитаем его следующим образом:

Excel Advanced Training (16 курсов, более 23 проектов) 16 онлайн-курсов | 23 практических проекта | 140+ часов | Поддающийся проверке сертификат об окончании | Пожизненный доступ
4,8 (10 185 оценок)

Ожидаемое значение = Итого по столбцу категории X (Итого по строке категории/Общий размер выборки)

Используя эту формулу, мы получаем ожидаемое количество около 92 стульев в зале A следующим образом:

Ожидаемое количество стульев в зале A = 250 X (362/984)

Мы проверим разницу по следующей формуле:

((Наблюдаемое значение-ожидаемое значение)ⁿ)/ожидаемое значение

В случае теста хи-квадрат n = 2

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

(количество строк – 1)(количество столбцов – 1)

Что в данном случае равно 6.

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

P-значение хи-квадрат для стульев будет рассчитываться следующим образом:

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

Если тестовая статистика слишком велика в текущем наборе данных, мы отклоняем нулевую гипотезу.

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

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

Чтобы рассчитать p-значение в Excel, нам нужно выполнить следующие шаги:

Рассчитайте ожидаемое значение. Ожидаемое значение в хи-квадрате обычно представляет собой простое среднее или среднее значение для нормально распределенных наборов данных. Для получения более сложных данных см. приведенный выше пример.

Введите данные в столбцы и щелкните пустую ячейку, в которой вы хотите отобразить результаты на рабочем листе, а затем нажмите кнопку «Вставить функцию» на панели инструментов, появится всплывающее окно. Введите chi в поле «Поиск функции» и нажмите «Перейти». Затем выберите «CHITEST» из списка и нажмите «ОК».

Выберите наблюдаемый и ожидаемый диапазоны и нажмите "ОК".

Мы получим результат следующим образом.

Что нужно помнить

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

Характеристики критерия хи-квадрат

Функции теста хи-квадрат перечислены ниже:

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

Примечание. В простейшей форме распределение хи-квадрат представляет собой квадрат стандартного нормального распределения.

Как выполнить тест хи-квадрат в Excel? (с примером)

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

Она организует задачу следующим образом:

Рассматривается случайная выборка из 100 клиентов.
Каждого клиента просят оценить обслуживание в ресторане на "отлично", "хорошо" и "плохо".

Она выдвигает следующую гипотезу:

Нулевая гипотезаНулевая гипотезаНулевая гипотеза предполагает, что выборочные данные и данные совокупности не имеют различий, или, говоря простыми словами, она предполагает, что утверждение, сделанное человеком в отношении данных или совокупности, является абсолютной истиной и всегда верно. Таким образом, даже если выборка будет взята из населения, результат, полученный при изучении выборки, будет таким же, как и предположение. Подробнее (H₀)– Качество обслуживания не зависит на зарплату клиентов, ожидающих обслуживания.
Альтернативная гипотеза (H₁). Качество обслуживания зависит от заработной платы клиентов, ожидающих обслуживания.

Менеджер делит клиентов на три категории в зависимости от их зарплаты: "низкая", "средняя" и "высокая". Уровень значимости (α) равен 0,05.

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

Рассчитаем сумму всех строк и столбцов. Мы применяем следующую формулу СУММ, чтобы сложить числа четвертой строки.

Нажмите клавишу «Ввод», и сумма появится в ячейке E4. Выход равен 26.

Мы применяем формулу "(r-1)(c-1)" для вычисления степеней свободы (df).

Мы применяем следующую формулу для расчета ожидаемой частоты для столбца B и строки 4.

Расчет показан на следующем изображении.

Ожидаемое количество клиентов с «низкой» зарплатой, но оценивших обслуживание в ресторане на «отлично», составляет 8,32.

В следующих расчетах E₁₁ – это ожидаемая частота появления первой строки и первого столбца. E₁₂ – ожидаемая частота первой строки и второго столбца.

Аналогичным образом мы вычисляем ожидаемую частоту для всей таблицы, как показано на следующем рисунке.

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

Мы применяем формулу "=(B4-B14)^2/B14" для вычисления первой точки хи-квадрат.

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

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

Вычисленное значение хи-квадрат равно 18,65823.

Вероятность составляет 0,05, что является значительным значением. ДФ равен 4.

Критическое значение хи-квадрат равно 9,487729037.

Давайте найдем p-значение хи-квадрат с помощью следующей формулы.

Мы применяем формулу "=CHITEST(B4:D6,B14:D16)".

Значение хи-квадрат p= 0,00091723.

Вычисленное значение хи-квадрат является значимым, если оно равно или превышает критическое значение хи-квадрат (табличное значение). Нулевая гипотеза (H₀) отклоняется, если вычисленное значение хи-квадрат больше критического значения хи-квадрат.

Здесь x 2 (расчетное)>x 2 (табличное) или 18,65>9,48. Следовательно, мы отвергаем нулевую гипотезу и принимаем альтернативную гипотезу.

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

«x 2 » в тесте хи-квадрат на независимость определяет, насколько вероятно, что разница между фактической и ожидаемой частотами может быть объяснена случайностью. Эта разница могла быть вызвана или не быть вызвана ошибкой выборки.

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

Например, p-значение, равное 0,0254, означает вероятность 2,54 % того, что результаты могли быть получены случайно. Чем меньше p-значение, тем важнее (значительнее) результаты. Значимым считается результат, при котором нулевая гипотеза отвергается.

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

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

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

Ключевые выводы

Критерий хи-квадрат – это непараметрический тест, который сравнивает две или более переменных из случайно выбранных данных.
Критерий согласия хи-квадрат помогает определить, соответствуют ли данные выборки совокупности или нет.
Критерий независимости хи-квадрат помогает определить, независимы ли переменные друг от друга.
Две случайные величины называются независимыми, если распределение вероятностей одной переменной не зависит от другой.
Символ критерия хи-квадрат — «x 2» (т. е. «x», возведенный в степень 2).
Если вычисленное значение хи-квадрат больше критического значения хи-квадрат, нулевая гипотеза (H₀) отклоняется.
Если p-значение хи-квадрат меньше или равно уровню значимости (α), нулевая гипотеза отклоняется.

Пример данных

Допустим, у меня есть выборка из 200 человек, которые посетили мой местный паб. Из этих 200 участников половина – мужчины, а половина – женщины.

Я спросил каждого участника, курит он или не курит. Вот мои результаты:

< tbody>

Курильщики	Некурящие
Мужской	29	71
Женщина	16	84

Итак, курили 29 мужчин и не курили 71 человек. Среди женщин было 16 курящих и 84 некурящих. Поскольку это фактические значения из моего эксперимента, они известны как наблюдаемые значения.

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

Как выполнить критерий независимости хи-квадрат в Excel

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

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

1. Вычислить строку, столбец и общие итоги

Первый шаг к выполнению теста хи-квадрат – суммирование всех строк и столбцов таблицы непредвиденных обстоятельств с помощью функции СУММ.

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

Итак, всего было 45 курильщиков.

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

На изображении ниже показаны все формулы, использованные в моем примере.

2. Рассчитать ожидаемые значения

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

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

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

Итак, в моем примере ожидаемое количество курящих мужчин составило 22,5.

Опять же, этот процесс необходимо повторить для всех записей в таблице непредвиденных обстоятельств.

3. Вычислить разницу между наблюдаемыми и ожидаемыми значениями

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

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

Этот процесс необходимо повторить для остальных записей в таблице.

4. Рассчитать статистику хи-квадрат

Далее нам нужно рассчитать статистику хи-квадрат.

Для этого просто сложите все значения, недавно рассчитанные на шаге 3.

В моем примере я буду использовать следующую формулу.

Итак, статистика хи-квадрат для моего примера при округлении составила 4,85.

5. Вычислить степени свободы

Далее нам нужно вычислить степени свободы.

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

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

Что дает ответ 1. Итак, в этом примере степень свободы равна 1.

6. Рассчитать p-значение

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

Чтобы сделать это в Excel, вы можете использовать функцию ХИ.РАСП.ВУ.

x – ячейка, содержащая значение хи-квадрат.
deg_freedom — ячейка, содержащая значение степеней свободы.

В моем примере при округлении я получаю значение p, равное 0,028.

7. [Необязательно] Используйте функцию CHISQ.TEST для расчета p-значения

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

Для этого используйте функцию CHISQ.TEST.

Фактический диапазон – ячейки, содержащие наблюдаемые значения.
Ожидаемый диапазон – ячейки, содержащие ожидаемые значения.

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

Чтобы интерпретировать p-значение, вам нужно сформулировать две мои гипотезы (нулевую и альтернативную).

Вот мои гипотезы для моего примера.

Нулевая гипотеза — связи между полом и статусом курения нет.
Альтернативная гипотеза: существует связь между полом и курением.

Если бы мой альфа-уровень или порог значимости был установлен на уровне 0,05, это означало бы, что я не смогу отвергнуть нулевую гипотезу, если p>0,05. С другой стороны, если p

Стивен является основателем Top Tip Bio. В настоящее время он является медицинским писателем и бывшим научным сотрудником с докторской степенью.Понравилось обучение? Тогда дайте мне знать, оставив комментарий ниже, или подумайте о том, чтобы угостить меня кофе.

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