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

8 апреля 2022 г.

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

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

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

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

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

1. Настройте 4 столбца

Откройте электронную таблицу и создайте четыре столбца со следующими заголовками:

• Категория

• Предполагаемая пропорция

• Наблюдаемые данные

• Ожидаемые данные

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

ABC1Категория**Данные наблюдений**Ожидаемые данные2Январь-июнь260
3июль-декабрь240
4

2. Рассчитайте ожидаемые данные

Ожидаемые данные — это то, какими, как вы предполагаете, будут результаты до сбора фактических данных. Вы можете рассчитать это значение, просуммировав наблюдаемые данные и разделив их на количество категорий. В приведенном выше примере учитель может ввести формулу «=СУММ(B2:B3)» в ячейку B4, чтобы получить 500, то есть общее количество учеников, опрошенных учителем.

Затем учитель может ввести формулу «=B4/2» в C2. В этой формуле указано число 250 — количество учеников, которые, как ожидается, учитель назовет днем ​​рождения в январе. Наконец, они могут скопировать эту формулу во все остальные строки в столбце «Ожидаемые данные». В приведенном выше примере будут отображаться ожидаемые данные 250 для числа учащихся, дни рождения которых приходятся на январь-июнь и июль-декабрь.

3. Рассчитайте p-значение

Назначьте ячейку, содержащую p-значение. В этой ячейке введите «=CHISQ.TEST» и нажмите «Enter» на клавиатуре. Введите наблюдаемые данные в качестве первого аргумента, введите запятую и введите ожидаемые данные в качестве второго аргумента. Добавьте закрывающую скобку и нажмите «Enter». В приведенном выше примере формула будет выглядеть так: “=CHISQ.ТЕСТ(B2:B3, C2:C3)”. Нажатие «Enter» дает результат 0,3710933695.

4. Сравните p-значение с альфа-каналом

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

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

Вот как выполнить тест хи-квадрат в Excel при сравнении нескольких категорий:

1. Настройте таблицу «Наблюдаемые данные».

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

Учащийся, который хочет проанализировать гендерное соотношение учащихся средней школы, может создать следующую таблицу «Наблюдаемые данные» после сбора результатов опроса:

ABCD1
Мужской**Женский**Всего2Первокурсник101992003Второкурсник1271232504Младший1101152255Выпускной1141112256Всего452448900### 2. Создайте таблицу «Ожидаемые данные».

Выберите заголовки и данные, которые вы ввели для таблицы «Наблюдаемые данные». Щелкните правой кнопкой мыши и выберите «Копировать». Выберите пустую ячейку в электронной таблице, рядом с которой и под ней достаточно места. Щелкните правой кнопкой мыши, выберите «Специальная вставка» и выберите «Значения». Назовите эту таблицу «Ожидаемые данные». Выделите все значения, кроме итогов, и нажмите «Удалить» на клавиатуре.

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

В таблице «Ожидаемые данные» вычислите ожидаемое значение каждой пустой ячейки. Выберите первую пустую ячейку и введите формулу, которая делит произведение общих значений категорий на общий размер выборки. В приведенном выше примере формула в ячейке B2 будет выглядеть так: «=СУММ((B6*D2)/D6)». Повторите этот процесс для всех остальных пустых ячеек.

4. Рассчитайте p-значение

Выберите ячейку за пределами таблиц «Наблюдаемые данные» и «Ожидаемые данные». Введите «=CHISQ.TEST» и нажмите «Enter» на клавиатуре. Выберите наблюдаемые значения в таблице «Наблюдаемые данные», введите запятую и выберите ожидаемые значения в таблице «Ожидаемые данные». Обязательно исключите итоги в своем выборе. Добавьте закрывающую скобку и нажмите «Enter» на клавиатуре.

5. Сравните p-значение с альфа

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

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