Расчет критерия стьюдента в excel

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Рассмотрим использование MS EXCEL при проверке статистических гипотез о равенстве дисперсий 2-х нормальных распределений. Вычислим значение тестовой статистики F , рассмотрим процедуру «двухвыборочный F -тест», вычислим Р-значение (Р- value ), построим доверительный интервал. С помощью надстройки Пакет анализа сделаем «двухвыборочный F -тест для дисперсии».

Имеется две независимых случайных нормально распределенных величины . Эти случайные величины имеют нормальные распределения с неизвестными дисперсиями σ ₁ 2 и σ ₂ 2 соответственно. Из этих распределений получены две выборки размером n ₁ и n ₂ .

Необходимо произвести проверку гипотезы о равенстве дисперсий этих распределений (англ. Hypothesis Tests for the Equality of Variances of Two Normal Distributions).

СОВЕТ : Для проверки гипотез потребуется знание следующих понятий:

Примечание : Провер ка гипотез о дисперсии нормального распределения ( одновыборочный тест ) изложена в статье Проверка статистических гипотез в MS EXCEL о дисперсии нормального распределения .

Нулевая гипотеза H звучит так: дисперсии нормальных распределений равны, т.е. σ ₁ 2 = σ ₂ 2 .

Альтернативная гипотеза H ₁ : σ ₁ 2 σ ₂ 2 . Т.е. нам требуется проверить двухстороннюю гипотезу .

В отличие от z-теста и t-теста , где мы рассматривали разность средних значений , в этом тесте будем рассматривать отношение дисперсий : σ ₁ 2 / σ ₂ 2 . Если дисперсии равны, то их отношение должно быть равно 1.

Как известно, точечной оценкой дисперсии распределения σ 2 может служить значение дисперсии выборки s 2 . Соответственно, оценкой отношения дисперсий σ ₂ 2 / σ ₂ 2 будет s ₁ 2 / s ₂ 2 .

Процедура проверки гипотезы о равенстве дисперсий 2-х распределений имеет специальное название: двухвыборочный F -тест для дисперсий (F-Test: Hypothesis Tests for the Variances of Two Normal Distributions).

Тестовой статистикой для проверки гипотез данного вида является случайная величина F= s ₁ 2 / s ₂ 2 .

Данная тестовая статистика , как и любая другая случайная величина, имеет свое распределение (в процедуре проверки гипотез это распределение называют « эталонным распределением », англ. Reference distribution). В нашем случае F -статистика имеет F-распределение (распределение Фишера) . Значение, которое приняла F -статистика обозначим F .

Примечание : В статье Статистики и их распределения показано , что выборочное распределение статистики при достаточно большом размере выборок стремится к F-распределению вероятности с n ₁ -1 и n ₂ -1 степенями свободы .

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

Мы будем отклонять нулевую двухстороннюю гипотезу, если F , вычисленное на основании выборок , примет значение:

• больше верхнего α/2-квантиля F-распределения вероятности с n ₁ -1 и n ₂ -1 степенями свободы или
• меньше нижнего α/2-квантиля того же распределения.

Примечание : Верхний α/2-квантиль — это такое значение случайной величины F , что P ( F >= F _{α/2, n1-1, n2-1})=α/2. Верхний 1-α /2- квантиль равен нижнему α/2 квантилю . Подробнее о квантилях распределений см. статью Квантили распределений MS EXCEL .

Запишем критерий отклонения с помощью верхних квантилей:

Чтобы в MS EXCEL вычислить значение нижнего квантиля α/2-квантиля — используйте формулу =F.ОБР(α /2 ; n ₁ -1, n ₂ -1) или =F.ОБР.ПХ(1-α /2 ; n ₁ -1, n ₂ -1)

Проверка двухсторонней гипотезы приведена в файле примера .

F-тест обычно используется для того, чтобы ответить на следующие вопросы:

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

СОВЕТ : Перед проверкой гипотез о равенстве дисперсий полезно построить двумерную гистограмму , чтобы визуально определить разброс данных в обеих выборок .

Инструменты Excel для построения интервальных оценок параметров распределений

Все, рассмотренные в этом разделе инструменты вычисляют значения квантилей как значения функций, обратных соответствующим функциям распределения. Все эти функции – библиотечные функции Excel из группы функций «Статистические»,.

Функция вычисления критических точек распределения Лапласа

Функция возвращает (вычисляет) значения квантили уровня, равного значению, введенному в поле «Вероятность» (понятно, что это число из промежутка (0б 1)) стандартного нормального распределения.

Функция вычисления критических точек распределения Стьюдента

Функция возвращает (вычисляет) значения квантили уровня, равного значению, введенному в поле «Вероятность» (понятно, что это число из промежутка (0б 1)) распределения Стьюдента с числом степеней свободы, равным значению, введенному в поле «Степени свободы» (понятно, что это натуральное число).

Важно знать, что функция Excel СТЬЮДРАСПОБР( p , k ) возвращает значение t , при котором P (| x | > t ) = p , x — значение случайной величины, имеющей распределение Стьюдента с k степенями свободы. Поэтому решение уравнения

Поэтому решение уравнения

n

Функция вычисления критических точек распределения

Функция возвращает (вычисляет) значения квантили уровня, равного значению, введенному в поле «Вероятность» (понятно, что это число из промежутка (0б 1)) распределения

В Excel функция распределения случайной величины определена нестандартно: F _x ( x ) = P ( x > x ). Поэтому для вычисления квантиля

Критерии Стьюдента

Для оценки статистической значимости модели по параметрам рассчитывают t-критерии Стьюдента.

Оценка значимости модели с помощью критерия Стьюдента проводится путем сравнения их значений с величиной случайной ошибки:

Случайные ошибки коэффициентов линейной регрессии и коэффициента корреляции определяются по формулам:

Сравнивая фактическое и табличное значения t-статистики и принимается или отвергается гипотеза о значимости модели по параметрам.

Зависимость между критерием Фишера и значением t-статистики Стьюдента определяется так

Как и в случае с оценкой значимости уравнения модели в целом, модель считается ненадежной если tтабл > tфакт

Порядок расчета критерия φ*

1. Формулируем статистические гипотезы:

Но: доля студентов, получивших оценки 4 и 5 до эксперимента такая же, как и после эксперимента;

Н1: доля студентов, получивших оценки 4 и 5 после эксперимента больше, чем до эксперимента.

2. Определяем значения углов φ1 и φ2, соответствующие долям p1 = 0,666; p2 = 0,888

φ1= 2arcsin (√p1)= 2 arcsin √0,6662 arcsin (0,816)= 2·0.954=1.908

φ2= 2arcsin (√p2)= 2 arcsin √0,888=2 arcsin (0,942)= 2·1.228=2.457

3. Вычисляем эмпирическое значение φ по формуле.

4. Сравниваем эмпирическое значение критерия с критическим (представлено в таблице 2)

Таблица 2. Критические значения критерия при различных значениях уровнях значимости α (Попов Г.И. с соавт., 2007).

α	критические значения критерия φ*
0,001	2,91
0,01	2,31
0,05	1,64
0,1	1,29

Расчет в программе Excel

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

Чтобы выполнить расчет, нужно заполнить клетки, выделенные желтым цветом в нижней части таблицы. После этого будет получено эмпирическое значение критерия (фи*эмп). Затем подученное значение эмпирического значения фи нужно сравнить с критическим значением (фи* крит) на заданном уровне значимости. Эти значения приведены в табл.1. Если фи*эмп больше чем фи*крит, различия между группами статистически достоверны.

Таблицы по нахождению критерия Фишера и Стьюдента

Таблицы значений F-критерия Фишера и t-критерия Стьюдента Вы можете посмотреть здесь.

Табличное значение критерия Фишера вычисляют следующим образом:

1. Определяют k1, которое равно количеству факторов (Х). Например, в однофакторной модели (модели парной регрессии) k1=1, в двухфакторной k=2.
2. Определяют k2, которое определяется по формуле n — m — 1, где n — число наблюдений, m — количество факторов. Например, в однофакторной модели k2 = n — 2.
3. На пересечении столбца k1 и строки k2 находят значение критерия Фишера

Для нахождения табличного значения критерия Стьюдента определяют число степеней свободы, которое определяется по формуле n — m — 1 и находят его значение при определенном уровне значимости (0,10, 0,05, 0,01).

Полное руководство: как интерпретировать результаты t-теста в Excel

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

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

Шаг 1: Создайте данные

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

Чтобы проверить это, она собирает простую случайную выборку из 20 растений каждого вида:

Шаг 2. Выполните t-тест для двух выборок.

Чтобы выполнить двухвыборочный t-критерий в Excel, щелкните вкладку « Данные » на верхней ленте, а затем щелкните « Анализ данных» :

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

В появившемся окне выберите параметр под названием t-Test: Two-Sample, предполагающий равные отклонения , а затем нажмите OK.Затем введите следующую информацию:

После того, как вы нажмете OK , отобразятся результаты t-теста:

Шаг 3: интерпретируйте результаты

Вот как интерпретировать каждую строку в результатах:

Среднее значение: среднее значение каждого образца.

• Образец 1 Среднее значение: 15,15
• Образец 2 Среднее значение: 15,8

Дисперсия: Дисперсия каждого образца.

• Образец 1 Дисперсия: 8,13
• Образец 2 Дисперсия: 12,9

Наблюдения: количество наблюдений в каждой выборке.

• Образец 1 Наблюдения: 20
• Образец 2 Наблюдения: 20

Объединенная дисперсия: средняя дисперсия выборок, рассчитанная путем «объединения» дисперсий каждой выборки вместе по следующей формуле:

• s 2 p = ((n 1 -1)s 2 1 + (n 2 -1)s 2 2 ) / (n 1 +n 2 -2)
• с 2 р = ((20-1)8,13 + (20-1)12,9) / (20+20-2)
• с 2 р = 10,51974

Гипотетическая средняя разница: число, которое мы «предполагаем», представляет собой разницу между двумя средними значениями совокупности. В этом случае мы выбрали , потому что хотим проверить, равна ли разница между двумя популяциями в среднем 0.

df: Степени свободы для t-критерия, рассчитанные как:

• df = n 1 + n 2 – 2
• df = 20 + 20 – 2
• дф = 38

t Stat: тестовая статистика t , рассчитанная как:

• т знак равно ( Икс 1 — Икс 2 ) / √ с 2 п (1 / п 1 + 1 / п 2 )
• t = (15,15–15,8) / √ 10,51974 (1/20+1/20)
• т = -0,63374

Как расчитать дисперсию в Excel с помощью функции ДИСП.В

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

s 2 – дисперсия выборки;

x_ср — среднее значение выборки;

n — размер выборки (количество значений данных),

(x_i – x_ср) — отклонение от средней величины для каждого значения набора данных.

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

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

Финальная фаза вычисления дисперсии выглядит так:

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

Использование метода «сырого счета» (пример с готовкой)

Существует более эффективный способ вычисления дисперсии, известный как метод «сырого счета». Хотя с первого взгляда уравнение может показаться весьма громоздким, на самом деле оно не такое уж страшное. Можете в этом удостовериться, а потом и решите, какой метод вам больше нравится.

— сумма каждого значения данных после возведения в квадрат,

— квадрат суммы всех значений данных.

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

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

Расчет дисперсии в Excel

Как вы уже, наверное, догадались, в Excel присутствует формула, позволяющая рассчитать дисперсию. Причем, начиная с Excel 2010 можно найти 4 разновидности формулы дисперсии:

1) ДИСП.В – Возвращает дисперсию по выборке. Логические значения и текст игнорируются.

2) ДИСП.Г — Возвращает дисперсию по генеральной совокупности. Логические значения и текст игнорируются.

3) ДИСПА — Возвращает дисперсию по выборке с учетом логических и текстовых значений.

4) ДИСПРА — Возвращает дисперсию по генеральной совокупности с учетом логических и текстовых значений.

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

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

Разница в формуле расчета дисперсии между выборкой и совокупностью заключается в знаменателе. Где для выборки он будет равняться (n-1), а для генеральной совокупности только n.

Теперь разберемся с функциями расчета дисперсии с окончаниями А, в описании которых сказано, что при расчете учитываются текстовые и логические значения. В данном случае при расчете дисперсии определенного массива данных, где встречаются не числовые значения, Excel будет интерпретировать текстовые и ложные логические значения как равными 0, а истинные логические значения как равными 1.

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

Как выполнить t-тест с двумя выборками в Excel?

Пошаговые инструкции по запуску двухвыборочного t-теста в Excel

1. В Excel щелкните Анализ данных на вкладке Данные.
2. Во всплывающем окне «Анализ данных» выберите t-тест: две выборки, предполагающие равные отклонения.
3. В разделе «Ввод» выберите диапазоны для переменной 1 и переменной 2.

Как вы интерпретируете результаты t-теста?

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

Какова формула проверки гипотезы с двумя выборками? Когда нас интересует сравнение двух выборочных дисперсий, мы используем соотношение F: F= F = . Если нулевая гипотеза σ21=σ22 σ 1 2 = σ 2 2 , то отношение F принимает вид: F==s21s22 F = [ s 1 2 σ 1 2 ] знак равно s 1 2 s 2 2 .

Как вы интерпретируете результаты t-теста в Excel?

Загрузка пакета анализа в Excel

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

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

Для удобства также приводим ссылку на оригинал (на английском языке)

Для удобства также приводим ссылку на оригинал (на английском языке).

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

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

Откройте вкладку Файл, нажмите кнопку Параметры и выберите категорию Надстройки.

Если вы используете Excel 2007, нажмите кнопку Microsoft Office , а затем – кнопку Параметры Excel.

В раскрывающемся списке Управление выберите пункт Надстройки Excel и нажмите кнопку Перейти.

Если вы используете Excel для Mac, в строке меню откройте вкладку Средства и в раскрывающемся списке выберите пункт Надстройки для Excel.

В диалоговом окне Надстройки установите флажок Пакет анализа, а затем нажмите кнопку ОК.

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

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

Примечание: Чтобы включить функцию Visual Basic для приложений (VBA) для пакета анализа, вы можете загрузить надстройку » Пакет анализа — VBA » таким же образом, как и при загрузке пакета анализа. В диалоговом окне Доступные надстройки установите флажок Пакет анализа — VBA .

Примечание: Пакет анализа недоступен для Excel для Mac 2011. Дополнительные сведения о том, как найти пакет анализа в Excel для Mac 2011, я не вижу.

Чтобы загрузить пакет анализа в Excel для Mac, выполните указанные ниже действия.

В меню Сервис выберите пункт надстройки Excel.

В окне Доступные надстройки установите флажок Пакет анализа, а затем нажмите кнопку ОК.

Если надстройка Пакет анализа отсутствует в списке поля Доступные надстройки, нажмите кнопку Обзор, чтобы найти ее.

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

Выйдите из приложения Excel и перезапустите его.

Теперь на вкладке Данные доступна команда Анализ данных.

Я не могу найти пакет анализа в Excel для Mac 2011

Существуют несколько сторонних надстроек, которые предоставляют функции пакета анализа для Excel 2011.

Вариант 1. Скачайте статистическое программное обеспечение надстройки КСЛСТАТ для Mac и используйте его в Excel 2011. КСЛСТАТ содержит более 200 основных и расширенных статистических средств, включающих все функции пакета анализа.

Выберите версию КСЛСТАТ, соответствующую операционной системе Mac OS, и загрузите ее.

Откройте файл Excel, содержащий данные, и щелкните значок КСЛСТАТ, чтобы открыть панель инструментов КСЛСТАТ.

В течение 30 дней вы получите доступ ко всем функциям КСЛСТАТ. По истечении 30 дней вы сможете использовать бесплатную версию, включающую функции пакета анализа, или заказать одно из более полных решений КСЛСТАТ.

Вариант 2. Скачайте Статплус: Mac LE бесплатно из Аналистсофт, а затем используйте Статплус: Mac LE с Excel 2011.

Вы можете использовать Статплус: Mac LE для выполнения многих функций, которые ранее были доступны в пакетах анализа, таких как регрессия, гистограммы, анализ вариации (Двухфакторный дисперсионный обработки) и t-тесты.

Перейдите на веб-сайт аналистсофти следуйте инструкциям на странице загрузки.

После загрузки и установки Статплус: Mac LE откройте книгу, содержащую данные, которые нужно проанализировать.

Откройте Статплус: Mac LE. Эти функции находятся в меню Статплус: Mac LE.

В Excel 2011 не входит Справка для Кслстат или Статплус: Mac LE. Справка по Кслстат предоставляется кслстат. Справка для Статплус: Mac LE предоставляется Аналистсофт.

Корпорация Майкрософт не предоставляет поддержку ни для каких продуктов.