Сравнение двух выборок в excel
Рассмотрим использование MS EXCEL при проверке статистических гипотез о разнице средних значений 2-х распределений в случае неизвестных дисперсий (дисперсии этих 2-х распределений разные). Вычислим значение тестовой статистики t 0 *, рассмотрим соответствующую процедуру «двухвыборочный t -тест», вычислим Р-значение (Р- value ). С помощью надстройки Пакет анализа сделаем «Двухвыборочный t-тест с различными дисперсиями».
Имеется две независимых случайных величины. Эти случайные величины имеют распределения с неизвестными средними значениями μ 1 и μ 2 . Дисперсии этих распределений неизвестны и не равны между собой (обозначим их σ 1 2 и σ 2 2 ). Из этих распределений получены две выборки размером n 1 и n 2 .
Необходимо произвести проверку гипотезы о разнице средних значений этих распределений: μ 1 - μ 2 (англ. Hypothesis tests for a difference in means, populations with unknown and unequal variances).
Нулевая гипотеза H 0 звучит так: разница средних значений равна Δ 0 , т.е. Δ 0 = (μ 1 - μ 2 ). Часто предполагается, что Δ 0 =0, следовательно, μ 1 = μ 2 (значение Δ 0 задается исследователем исходя из условий решаемой задачи).
Альтернативная гипотеза H 1 : (μ 1 - μ 2 )<>Δ 0 . Т.е. нам требуется проверить двухстороннюю гипотезу .
СОВЕТ : При первом знакомстве с процедурой двухвыборочного t -теста может быть полезным освежить в памяти процедуру одновыброчного t-теста для среднего при неизвестной дисперсии .
СОВЕТ : Для проверки гипотез нам потребуется знание следующих понятий:
- дисперсия и стандартное отклонение ,
- выборочное распределение статистики ,
- уровень доверия/ уровень значимости ,
- нормальное распределение ,
- t-распределение Стьюдента и его квантили .
Примечание : Вышеуказанные распределения не обязательно должны быть нормальными . Однако, требуется чтобы выполнялись условия применимости Центральной предельной теоремы . Если размеры выборок меньше 30, то для справедливости сделанных здесь выводов, необходимо, чтобы выборки были сделаны из нормального распределения .
Точечной оценкой для Δ 0 или для μ 1 - μ 2 является разница между средними значениями, вычисленными на основании выборок из этих (независимых) распределений, т.е. Хср 1 - Хср 2 .
Когда дисперсии распределений, из которых сделаны выборки, не равны между собой, не существует точной t -статистики для проверки нулевой гипотезы , как для случая с одинаковыми дисперсиями (см. статью Двухвыборочный t-тест с одинаковыми дисперсиями ). Однако, при условии истинности нулевой гипотезы , статистика t* :
приблизительно имеет t -распределение с v (ню) степенями свободы:
Процедура t -теста в случае разных дисперсий аналогична процедуре t -теста в случае одинаковых дисперсий , за исключением того, что вместо t -статистики используется вышеуказанная статистики t*. Значение, которое приняла t *-статистика обозначим t 0 *.
Проверка двухсторонней гипотезы сводится к сравнению t 0 * с квантилями эталонного распределения , в данном случае распределения Стьюдента с v степенями свободы. Эта процедура носит название двухвыборочный t -тест в случае разных дисперсий (The two-sample t-Test with unequal variances).
Если вычисленное на основе выборок значение t 0 *, в случае двухсторонней гипотезы , не попадет в область значений ограниченной нижним и верхним α /2-квантилями t - распределения с v степенями свободы , то у нас будет основание отвергнуть нулевую гипотезу. Это утверждение эквивалентно случаю, когда Хср 1 - Хср 2 окажется вне пределов соответствующего доверительного интервала . В файле примера на листе Сигма неизвестн а показана эквивалентность доверительного интервала и соответствующего двухвыборочного t -теста.
Примечание : Про построение соответствующего двухстороннего доверительного интервала можно прочитать в этой статье Доверительный интервал для разницы средних значений 2-х распределений (дисперсии неизвестны и не равны) в MS EXCEL .
Чтобы в MS EXCEL вычислить значение t α /2, v для различных уровней значимости (10%; 5%; 1%) и степеней свобод можно использовать несколько формул: =СТЬЮДЕНТ.ОБР.2Х( α ; v) =СТЬЮДЕНТ.ОБР(1- α /2; v) =-СТЬЮДЕНТ.ОБР( α /2; v) =СТЬЮДРАСПОБР( α ; v)
Примечание : Подробнее про функции MS EXCEL, связанные с t - распределением см. статью t-распределение .
Примечание : Число степеней свободы v должно быть округлено до ближайшего целого .
Итак, если при проверке двухсторонней гипотезы формула =ABS(t 0 *) вернет значение больше, чем результат формулы =СТЬЮДЕНТ.ОБР.2Х( α ; v) , то это означает, что необходимо отвергнуть нулевую гипотезу (вычисления приведены файле примера на листе Сигма неизвестна ) .
Для односторонней альтернативной гипотезы (μ 1 - μ 2 )>Δ 0 , нулевая гипотеза будет отвергнута в случае t 0 *> t α /2, v .
Для односторонней альтернативной гипотезы (μ 1 - μ 2 ) =2*(1-СТЬЮДЕНТ.РАСП(ABS(t 0 *); v;ИСТИНА))
Примечание : Вычисления приведены файле примера на листе Сигма неизвестна .
Для односторонней гипотезы μ 1 - μ 2 > Δ 0 p -значение вычисляется по формуле: =1-СТЬЮДЕНТ.РАСП(t 0 *; v;ИСТИНА) В этом случае p-значение равно вероятности, что t -статистика примет значение больше t 0 *.
Для односторонней гипотезы μ 1 - μ 2 =СТЬЮДЕНТ.РАСП(t 0 *; v;ИСТИНА) В этом случае p-значение равно вероятности, что t -статистика примет значение меньше t 0 *.
В файле примера на листе Сигма неизвестна показана эквивалентность проверки гипотезы через доверительный интервал , статистику t * ( t -тест) и p -значение .
В MS EXCEL есть функция СТЬЮДЕНТ.TEСT() , которая вычисляет p-значение для 3-х различных двухвыборочных t -тестов (см. следующий раздел статьи) . К сожалению, эта функция может быть использована только для проверки гипотез с Δ 0 =0, то есть для проверки гипотез о равенстве средних μ 1 = μ 2 . Об этом легко догадаться, т.к. среди ее параметров отсутствует параметр Гипотетическая разность средних , т.е. Δ 0 .
Функция СТЬЮДЕНТ.ТЕСТ()
Функция СТЬЮДЕНТ.ТЕСТ() используется для оценки различия двух выборочных средних . До MS EXCEL 2010 имелась аналогичная функция ТТЕСТ() .
Примечание : В английской версии функция носит название T.TEST(), старая версия - TTEST().
Функция СТЬЮДЕНТ.ТЕСТ() имеет 4 параметра. Первые два – это ссылки на диапазоны ячеек, содержащие выборки из 2-х сравниваемых распределений.
Третий параметр имеет название «хвосты». Этот параметр задает тип проверяемой гипотезы: односторонняя (=1) или двухсторонняя (=2). Если мы проверяем двухстороннюю гипотезу , то смотрим, не попало ли значение тестовой статистики в один из 2-х хвостов соответствующего t-распределения . Если мы проверяем одностороннюю гипотезу (имеется ввиду гипотеза μ 1 файл примера ): =СТЬЮДЕНТ.ТЕСТ( выборка1 ; выборка2 ; 2; 3) или =2*(1-СТЬЮДЕНТ.РАСП(ABS(t 0 *); v;ИСТИНА))
Для односторонней гипотезы μ 1 =СТЬЮДЕНТ.ТЕСТ( выборка1 ; выборка2 ; 1; 3) или =СТЬЮДЕНТ.РАСП(t 0 *; v;ИСТИНА)
Для односторонней гипотезы μ 1 > μ 2 p -значение вычисляется по формуле: =1-СТЬЮДЕНТ.ТЕСТ( выборка1 ; выборка2 ; 1; 3) или =1-СТЬЮДЕНТ.РАСП(t 0 *; v;ИСТИНА)
К сожалению, результаты, возвращаемые функцией СТЬЮДЕНТ.ТЕСТ() и формулой на основе функции СТЬЮДЕНТ.РАСП() незначительно отличаются (в 4-м знаке после запятой). Причем различие проявляется только для случая с неравными дисперсиями.
Какой результат правильный? В поддержку формулы на основе функции СТЬЮДЕНТ.РАСП() выступает надстройка Пакет анализа , которая возвращает аналогичный ей результат (см. ниже).
Пакет анализа
В надстройке Пакет анализа для проведения двухвыборочного t -теста с различными дисперсиями имеется специальный инструмент: Двухвыборочный t -тест с различными дисперсиями (t-Test: Two-Sample Assuming Unequal Variances).
После выбора инструмента откроется окно, в котором требуется заполнить следующие поля (см. файл примера лист Пакет анализа ):
- интервал переменной 1 : ссылка на значения первой выборки . Ссылку указывать лучше с заголовком. В этом случае, при выводе результата надстройка выводит заголовки, которые делают результат нагляднее (в окне требуется установить галочку Метки );
- интервал переменной 2 : ссылка на значения второй выборки ;
- гипотетическая средняя разность : укажите значение Δ 0 , т.е. μ 1 - μ 2 . В нашем случае, введем 0;
- Метки: если в полях интервал переменной 1 и интервал переменной 2 указаны ссылки вместе с заголовками столбцов, то эту галочку нужно установить. В противном случае надстройка не позволит провести вычисления и пожалуется, что « входной интервал содержит нечисловые данные »;
- Альфа:уровень значимости ;
- Выходной интервал: диапазон ячеек, куда будут помещены результаты вычислений. Достаточно указать левую верхнюю ячейку этого диапазона.
В результате вычислений будет заполнен указанный Выходной интервал.
Тот же результат можно получить с помощью формул (см. файл примера лист Пакет анализа ):
Разберем результаты вычислений, выполненных надстройкой:
- Среднее : средние значения обеих выборок Хср 1 - Хср 2 . Вычисления можно сделать с помощью функции СРЗНАЧ() ;
- Дисперсия : дисперсии обеих выборок. Вычисления можно сделать с помощью функции ДИСП.В()
- Наблюдения : размер выборок. Вычисления можно сделать с помощью функции СЧЁТ()
- Df : число степеней свободы. Вычисление v приведено в ячейке Е10 ;
- t-статистика : значение тестовой статистикиt (в наших обозначениях – это t 0 *). Вычисление t 0 * приведено в ячейке Е16 ;
- P(T Δ 0 . Эквивалентная формула =1-СТЬЮДЕНТ.РАСП(t 0 *; v ; ИСТИНА) ;
- t критическое одностороннее : Верхний α -квантиль t-распределения. Эквивалентная формула =СТЬЮДЕНТ.ОБР(1- α ; v) ;
- P(T Δ 0 . Эквивалентная формула =2*(1-СТЬЮДЕНТ.РАСП(ABS(t 0 *); v; ИСТИНА)) ;
- t критическое двухстороннее: Верхний α /2-Квантиль t-распределения . Эквивалентная формула =СТЬЮДЕНТ.ОБР(1- α /2; v) .
Отметим, что значения P(T СТЬЮДЕНТ.ТЕСТ() . Например,
- 0,398457254347491 (результат, возвращаемый надстройкой)
- 0,398359475709341 (результат, возвращаемый функцией)
Это первый, замеченный мной случай в MS EXCEL, когда результат зависит от применяемого инструмента.
СОВЕТ : О проверке других видов гипотез см. статью Проверка статистических гипотез в MS EXCEL .
Рассмотрим использование MS EXCEL при проверке статистических гипотез о разнице средних значений 2-х распределений в случае неизвестных дисперсий (парный тест). Вычислим значение тестовой статистики t 0 , рассмотрим соответствующую процедуру «двухвыборочный t -тест», вычислим Р-значение (Р- value ). С помощью надстройки Пакет анализа сделаем «Парный двухвыборочный t-тест для средних».
Здесь рассмотрен специальный случай двухвыборочного t-теста , когда наблюдения случайных величин из двух распределений производятся не независимо, а парами.
Примечание : Процедура двухвыборочного t-теста также изложена в статьях Двухвыборочный t-тест с одинаковыми дисперсиями и Двухвыборочный t-тест с различными дисперсиями , где выборки из распределений считались независимыми.
СОВЕТ : При первом знакомстве с процедурой двухвыборочного t -теста может быть полезным освежить в памяти процедуру одновыброчного t-теста для среднего при неизвестной дисперсии .
СОВЕТ : Для проверки гипотез нам потребуется знание следующих понятий:
- дисперсия и стандартное отклонение ,
- выборочное распределение статистики ,
- уровень доверия/ уровень значимости ,
- нормальное распределение ,
- t-распределение Стьюдента и его квантили .
Приведем пример . Имеется 2 прибора измеряющих твердость металлических образцов. Необходимо проверить, что эти приборы показывают одинаковые результаты на одном и том же образце ( нулевая гипотеза ).
Если для испытания на приборах образцы отбирать случайным образом: половину для проверки на приборе №1, другую на приборе №2, и использовать для проверки нулевой гипотезы t-тест с одинаковыми (или различающимися) дисперсиями, то можно сделать ошибочное заключение. Дело в том, что металлические образцы могут быть изготовлены из различных заготовок, прошедших различную термообработку и, следовательно, они могут иметь различную твердость. Таким образом, наблюденная разность между средними значениями твердости, полученными на каждом из приборов (Х ср1 и Х ср2 ), будет также включать различие в твердости, обусловленную самими образцами. Другими словами, при таком методе исследования у нас имеется 2 источника неопределенности (случайности): несовершенство приборов и случайные колебания твердости самих образцов.
Чтобы исключить случайность, обусловленную различием образцов, и тем самым увеличить мощность t -теста , используют парные выборки . В нашем случае, измерения одного и того же образца будем проводить сначала на одном, затем на другом приборе (предполагается, что после измерения твердости на первом приборе, образец не портится).
Таким образом, процедура проверки гипотезы сводится к определению разности твердостей полученных приборами на одном и том же образце. Если приборы настроены одинаково, то среднее разностей должно быть около 0 (отклонение не должно быть статистически значимым).
Пусть имеется набор из n пар наблюдений (n образцов). Т.к. результат каждого наблюдения является случайной величиной (приборы не идеальны, присутствует случайная ошибка измерений), то эти случайные величины имеют распределения с неизвестными средними значениями μ 1 и μ 2 (измерения полученные на приборе №1 и №2, соответственно). Дисперсии этих распределений неизвестны (обозначим их σ 1 2 и σ 2 2 ).
Будем рассматривать не сами наблюдения, а их разницу. Обозначим D i – разницу измерений, полученную приборами №1 и №2 на i-м образце. Разницу между μ 1 и μ 2 , которую нам необходимо оценить, обозначим μ D .
Проведем проверку гипотезы о равенстве μ D заданному значению Δ 0 , т.е. парный t -тест (англ. The Paired t-Test). Если Δ 0 равно 0, то речь идет о проверке равенства средних двух распределений.
Т.е. нам требуется проверить двухстороннюю гипотезу .
Тестовой статистикой является случайная величина t:
где D ср – среднее значение разностей, S d – стандартное отклонение этих разностей.
Как известно из статьи про одновыборочный t-тест , данная тестовая статистика , имеет t-распределение c n-1 степенью свободы. Значение, которое приняла эта t -статистика, обозначим t 0 .
Установим требуемый уровень значимости α (альфа) = 0,05 (допустимую для данной задачи ошибку первого рода , т.е. вероятность отклонить нулевую гипотезу , когда она верна).
Если вычисленное на основе выборок значение t 0 , в случае двухсторонней гипотезы , не попадет в область значений ограниченной нижним и верхним α/2-квантилями t - распределения с n–1 степенями свободы, то у нас будет основание отвергнуть нулевую гипотезу. Это утверждение эквивалентно случаю, когда D ср окажется вне пределов соответствующего доверительного интервала. В файле примера на листе Парный тест показана эквивалентность доверительного интервала и соответствующего t -теста.
Примечание : Верхний α/2-квантиль - этотакое значение случайной величины t n–1 , что P ( t n-1 >=t α/2 , n-1 ) =α/2. Верхний α/2-квантиль t - распределения с n -1 степенью свободы обычно обозначают t α /2, n-1 . Подробнее о квантилях распределений см. статью Квантили распределений MS EXCEL .
В нашем случае, необходимо будет вычислить только верхний α/2-квантиль, т.к. он равен соответствующему нижнему квантилю со знаком минус. Следовательно, условие отклонения нулевой гипотезы можно записать как |t 0 |>t α/2 , n-1 .
Чтобы в MS EXCEL вычислить значение t α/2 , n-1 для различных уровней значимости (10%; 5%; 1%) и степеней свобод можно использовать несколько формул: =СТЬЮДЕНТ.ОБР.2Х(α; n-1) =СТЬЮДЕНТ.ОБР(1- α/2; n-1) =-СТЬЮДЕНТ.ОБР(α/2; n-1) =СТЬЮДРАСПОБР(α; n-1)
Примечание : Подробнее про функции MS EXCEL, связанные с t - распределением см. статью t-распределение .
Итак, если при проверке двухсторонней гипотезы формула =ABS(t 0 ) вернет значение больше, чем результат формулы =СТЬЮДЕНТ.ОБР.2Х(α; n-1) , то это означает, что необходимо отвергнуть нулевую гипотезу (вычисления приведены в файле примера на листе Парный тест ) .
Для односторонней альтернативной гипотезы μ D >Δ 0 , нулевая гипотеза будет отвергнута в случае t 0 > t α , n-1 .
Для односторонней альтернативной гипотезы μ D =2*(1-СТЬЮДЕНТ.РАСП(ABS(t 0 ); n-1;ИСТИНА))
Примечание : Вычисления приведены в файле примера на листе Парный тест .
Для односторонней гипотезы μ 1 -μ 2 >Δ 0 p -значение вычисляется по формуле: =1-СТЬЮДЕНТ.РАСП(t 0 ; n-1;ИСТИНА) В этом случае p-значение равно вероятности, что t -статистика примет значение больше t 0 .
Для односторонней гипотезы μ 1 -μ 2 = СТЬЮДЕНТ.РАСП(t 0 ; n-1;ИСТИНА) В этом случае p-значение равно вероятности, что t -статистика примет значение меньше t 0 .
В файле примера на листе Парный тест показана эквивалентность проверки гипотезы через доверительный интервал , статистику t 0 ( t -тест) и p -значение .
В MS EXCEL есть функция СТЬЮДЕНТ.TEСT() , которая вычисляет p-значение для 3-х различных двухвыборочных t -тестов (см. следующий раздел статьи) . К сожалению, эта функция может быть использована только для проверки гипотез с Δ 0 =0, то есть для проверки гипотез о равенстве средних μ 1 =μ 2 . Об этом легко догадаться, т.к. среди ее параметров отсутствует параметр Гипотетическая разность средних , т.е. Δ 0 .
Функция СТЬЮДЕНТ.ТЕСТ()
Функция СТЬЮДЕНТ.ТЕСТ() используется для оценки различия двух выборочных средних . До MS EXCEL 2010 имелась аналогичная функция ТТЕСТ() .
Примечание : В английской версии функция носит название T.TEST(), старая версия - TTEST().
Функция СТЬЮДЕНТ.ТЕСТ() имеет 4 параметра. Первые два – это ссылки на диапазоны ячеек, содержащие выборки из 2-х сравниваемых распределений.
Третий параметр имеет название «хвосты». Этот параметр задает тип проверяемой гипотезы: односторонняя (=1) или двухсторонняя (=2). Если мы проверяем двухстороннюю гипотезу , то смотрим, не попало ли значение тестовой статистики в один из 2-х хвостов соответствующего t-распределения . Если мы проверяем одностороннюю гипотезу (имеется ввиду гипотеза μ 1 файл примера ): =СТЬЮДЕНТ.ТЕСТ( выборка1 ; выборка2 ; 2; 1) или =2*(1-СТЬЮДЕНТ.РАСП(ABS(t 0 ); n-1;ИСТИНА))
Для односторонней гипотезы μ 1 =СТЬЮДЕНТ.ТЕСТ( выборка1 ; выборка2 ; 1; 1) или =СТЬЮДЕНТ.РАСП(t 0 ; n-1;ИСТИНА)
Для односторонней гипотезы μ 1 >μ 2 p -значение вычисляется по формуле: =1-СТЬЮДЕНТ.ТЕСТ( выборка1 ; выборка2 ; 1; 1) или =1-СТЬЮДЕНТ.РАСП(t 0 ; n-1;ИСТИНА)
Пакет анализа
В надстройке Пакет анализа для проведения Парного двухвыборочного t -теста имеется одноименный инструмент: Парный двухвыборочный t -тест для средних (t-Test: Paired Two-Sample for Means).
После выбора инструмента откроется окно, в котором требуется заполнить следующие поля (см. файл примера лист Пакет анализа ):
- интервал переменной 1 : ссылка на значения первой выборки . Ссылку указывать лучше с заголовком. В этом случае, при выводе результата надстройка выводит заголовки, которые делают результат нагляднее (в окне требуется установить галочку Метки );
- интервал переменной 2 : ссылка на значения второй выборки ;
- гипотетическая средняя разность : укажите значение Δ 0 , т.е. μ 1 -μ 2 . В нашем случае, введем 0;
- Метки: если в полях интервал переменной 1 и интервал переменной 2 указаны ссылки вместе с заголовками столбцов, то эту галочку нужно установить. В противном случае надстройка не позволит провести вычисления и пожалуется, что « входной интервал содержит нечисловые данные »;
- Альфа:уровень значимости ;
- Выходной интервал: диапазон ячеек, куда будут помещены результаты вычислений. Достаточно указать левую верхнюю ячейку этого диапазона.
В результате вычислений будет заполнен указанный Выходной интервал.
Тот же результат можно получить с помощью формул (см. файл примера лист Пакет анализа ):
Разберем результаты вычислений, выполненных надстройкой:
- Среднее : средние значения обеих выборок Хср 1 и Хср 2 . Вычисления можно сделать с помощью функции СРЗНАЧ() ;
- Дисперсия : дисперсии обеих выборок. Вычисления можно сделать с помощью функции ДИСП.В()
- Наблюдения : размер выборок. Вычисления можно сделать с помощью функции СЧЁТ()
- Корреляция Пирсона : коэффициент корреляции двух выборок . Вычисления можно сделать с помощью функции КОРРЕЛ() или PEARSON()
- Df : число степеней свободы : n-1, где n размер выборок ;
- t-статистика : значение тестовой статистикиt (в наших обозначениях – это t 0 ). Вычисление t 0 приведено в ячейке Е15 ;
- P(T Δ 0 . Эквивалентная формула =1-СТЬЮДЕНТ.РАСП(t 0 ; n-1;ИСТИНА) ;
- t критическое одностороннее : Верхний α-квантиль t-распределения. Эквивалентная формула =СТЬЮДЕНТ.ОБР(1- α; n-1) ;
- P(T Δ 0 . Эквивалентная формула =2*(1-СТЬЮДЕНТ.РАСП(ABS(t 0 ); n-1;ИСТИНА)) ;
- t критическое двухстороннее: Верхний α/2-Квантиль t-распределения . Эквивалентная формула =СТЬЮДЕНТ.ОБР(1- α/2; n-1) .
СОВЕТ : О проверке других видов гипотез см. статью Проверка статистических гипотез в MS EXCEL .
Рассмотрим использование MS EXCEL при проверке статистических гипотез о разнице средних значений 2-х распределений в случае неизвестных дисперсий (дисперсии этих 2-х распределений одинаковы). Вычислим значение тестовой статистики t 0 , рассмотрим соответствующую процедуру «двухвыборочный t -тест», вычислим Р-значение (Р- value ). С помощью надстройки Пакет анализа сделаем «Двухвыборочный t-тест с одинаковыми дисперсиями».
Имеется две независимых случайных величины. Эти случайные величины имеют распределения с неизвестными средними значениями μ 1 и μ 2 . Дисперсии этих распределений неизвестны, но равны между собой ( дисперсию обозначим σ 2 ). Из этих распределений получены две выборки размером n 1 и n 2 .
Необходимо произвести проверку гипотезы о разнице средних значений этих распределений: μ 1 - μ 2 (англ. Hypothesis tests for a difference in means, populations with unknown but equal variances).
Нулевая гипотеза H 0 звучит так: разница средних значений равна Δ 0 , т.е. Δ 0 = (μ 1 - μ 2 ). Часто предполагается, что Δ 0 =0, следовательно, μ 1 = μ 2 (значение Δ 0 задается исследователем исходя из условий решаемой задачи).
Альтернативная гипотеза H 1 : (μ 1 - μ 2 )<>Δ 0 . Т.е. нам требуется проверить двухстороннюю гипотезу .
Примечание : Про построение соответствующего двухстороннего доверительного интервала можно прочитать в этой статье Доверительный интервал для разницы средних значений 2-х распределений (дисперсии неизвестны, но равны) в MS EXCEL .
СОВЕТ : При первом знакомстве с процедурой двухвыборочного t -теста может быть полезным освежить в памяти процедуру одновыброчного t-теста для среднего при неизвестной дисперсии .
СОВЕТ : Для проверки гипотез нам потребуется знание следующих понятий:
- дисперсия и стандартное отклонение ,
- выборочное распределение статистики ,
- уровень доверия/ уровень значимости ,
- нормальное распределение ,
- t-распределение Стьюдента и его квантили .
Примечание : Вышеуказанные распределения не обязательно должны быть нормальными . Однако, требуется чтобы выполнялись условия применимости Центральной предельной теоремы . Если размеры выборок меньше 30, то для справедливости сделанных здесь выводов, необходимо, чтобы выборки были сделаны из нормального распределения .
Точечной оценкой для Δ 0 или для μ 1 - μ 2 является разница между средними значениями, вычисленными на основании выборок из этих (независимых) распределений, т.е. Хср 1 - Хср 2 . Это следует из свойства математического ожидания : Е(Хср 1 - Хср 2 )= Е(Хср 1 )-Е(Хср 2 )= μ 1 - μ 2
Хср 1 - Хср 2 является случайной величиной, и как любая другая случайная величина, она имеет свое распределение вероятности. В данном случае, эта случайная величина распределена по нормальному закону . Это следует из того, что Хср 1 и Хср 2 распределены по нормальному закону (см. статью про ЦПТ ), а их линейная комбинация Хср 1 - Хср 2 также имеет нормальное распределение (см. статью про нормальное распределение ).
Теперь вычислим дисперсию этого распределения. На основании свойств дисперсии имеем, что VAR(Хср 1 - Хср 2 )= VAR(Хср 1 )+ VAR(Хср 2 ) = σ 2 /n 1 + σ 2 /n 2 =σ 2 (1/n 1 + 1/n 2 ).
Т.к. дисперсия σ 2 нам неизвестна, то вместо нее используем ее оценку: так называемую объединенную оценку дисперсии s p 2 (pooled estimate of variance).
Из процедуры двухвыборочного z-теста известно, что тестовая статистика Z имеет стандартное нормальное распределение .
Заменив, неизвестное значение стандартного отклонения σ на ее оценку s p , получим величину t:
Эта величина является тестовой статистикой ( t -статистикой ) для нашего двухвыборочного t -теста с одинаковыми дисперсиями . Известно, что t -статистика имеет распределение Стьюдента с n 1 +n 2 –2 степенями свободы. Значение, которое приняла t -статистика обозначим t 0 .
Как и для z -теста , проверка двухсторонней гипотезы сводится к сравнению t 0 с квантилями эталонного распределения , в данном случае распределения Стьюдента с n 1 +n 2 –2 степенями свободы. Эта процедура носит название двухвыборочный t -тест в случае одинаковых дисперсий (The two-sample pooled t-Test).
Если вычисленное на основе выборок значение t 0 , в случае двухсторонней гипотезы , не попадет в область значений ограниченной нижним и верхним α /2-квантилями t - распределения с n 1 +n 2 –2 степенями свободы , то у нас будет основание отвергнуть нулевую гипотезу. Это утверждение эквивалентно случаю, когда Хср 1 -Хср 2 окажется вне пределов соответствующего доверительного интервала . В файле примера на листе Сигма неизвестна показана эквивалентность доверительного интервала и соответствующего двухвыборочного t -теста.
Примечание : Про построение соответствующего двухстороннего доверительного интервала можно прочитать в этой статье Доверительный интервал для разницы средних значений 2-х распределений (дисперсии неизвестны, но равны) в MS EXCEL .
В нашем случае, необходимо будет вычислить только верхний α /2-квантиль, т.к. он равен соответствующему нижнему квантилю со знаком минус. Следовательно, условие отклонения нулевой гипотезы можно записать как | t 0 |>t α/2 , n1+n2–2 .
Чтобы в MS EXCEL вычислить значение t α/2 , n1+n2–2 для различных уровней значимости (10%; 5%; 1%) и степеней свобод можно использовать несколько формул: =СТЬЮДЕНТ.ОБР.2Х( α ; n 1 +n 2 –2) =СТЬЮДЕНТ.ОБР(1- α /2; n 1 +n 2 –2) =-СТЬЮДЕНТ.ОБР( α /2; n 1 +n 2 –2) =СТЬЮДРАСПОБР( α ; n 1 +n 2 –2)
Примечание : Подробнее про функции MS EXCEL, связанные с t - распределением см. статью t-распределение .
Итак, если при проверке двухсторонней гипотезы формула =ABS( t 0 ) вернет значение больше, чем результат формулы =СТЬЮДЕНТ.ОБР.2Х( α ; n 1 +n 2 –2) , то это означает, что необходимо отвергнуть нулевую гипотезу (вычисления приведены в файле примера на листе Сигма неизвестна ) .
Для односторонней альтернативной гипотезы (μ 1 - μ 2 )>Δ 0 , нулевая гипотеза будет отвергнута в случае t 0 >t α , n1+n2–2 .
Примечание : Вычисления приведены в файле примера на листе Сигма неизвестна .
Для односторонней гипотезы μ 1 - μ 2 > Δ 0 p -значение вычисляется по формуле: =1-СТЬЮДЕНТ.РАСП( t 0 ; n 1 +n 2 –2;ИСТИНА) В этом случае p-значение равно вероятности, что t -статистика примет значение больше t 0 .
Для односторонней гипотезы μ 1 - μ 2 =СТЬЮДЕНТ.РАСП( t 0 ; n 1 +n 2 –2;ИСТИНА) В этом случае p-значение равно вероятности, что t -статистика примет значение меньше t 0 .
В файле примера на листе Сигма неизвестна показана эквивалентность проверки гипотезы через доверительный интервал , статистику t 0 ( t -тест) и p -значение .
В MS EXCEL есть функция СТЬЮДЕНТ.TEСT() , которая вычисляет p-значение для 3-х различных двухвыборочных t -тестов (см. следующий раздел статьи) . К сожалению, эта функция может быть использована только для проверки гипотез с Δ 0 =0, то есть для проверки гипотез о равенстве средних μ 1 = μ 2 . Об этом легко догадаться, т.к. среди ее параметров отсутствует параметр Гипотетическая разность средних , т.е. Δ 0 .
Функция СТЬЮДЕНТ.ТЕСТ()
Функция СТЬЮДЕНТ.ТЕСТ() используется для оценки различия двух выборочных средних . До MS EXCEL 2010 имелась аналогичная функция ТТЕСТ() .
Примечание : В английской версии функция носит название T.TEST() , старая версия - TTEST() .
Функция СТЬЮДЕНТ.ТЕСТ() имеет 4 параметра. Первые два – это ссылки на диапазоны ячеек, содержащие выборки из 2-х сравниваемых распределений.
Третий параметр имеет название «хвосты». Этот параметр задает тип проверяемой гипотезы: односторонняя (=1) или двухсторонняя (=2). Если мы проверяем двухстороннюю гипотезу , то смотрим, не попало ли значение тестовой статистики t 0 в один из 2-х хвостов соответствующего t-распределения . Если мы проверяем одностороннюю гипотезу (имеется ввиду гипотеза μ 1 файл примера ): =СТЬЮДЕНТ.ТЕСТ( выборка1 ; выборка2 ; 2; 2) или =2*(1-СТЬЮДЕНТ.РАСП(ABS( t 0 ); n 1 +n 2 –2;ИСТИНА))
Для односторонней гипотезы μ 1 =СТЬЮДЕНТ.ТЕСТ( выборка1 ; выборка2 ; 1; 2) или =СТЬЮДЕНТ.РАСП( t 0 ; n 1 +n 2 –2;ИСТИНА)
Для односторонней гипотезы μ 1 > μ 2 p -значение вычисляется по формуле: =1-СТЬЮДЕНТ.ТЕСТ( выборка1 ; выборка2 ; 1; 2) или =1-СТЬЮДЕНТ.РАСП( t 0 ; n 1 +n 2 –2;ИСТИНА)
Пакет анализа
В надстройке Пакет анализа для проведения двухвыборочного t -теста с одинаковыми дисперсиями имеется специальный инструмент: Двухвыборочный t -тест с одинаковыми дисперсиями (t-Test: Two-Sample Assuming Equal Variances).
После выбора инструмента откроется окно, в котором требуется заполнить следующие поля (см. файл примера лист Пакет анализа ):
- интервал переменной 1 : ссылка на значения первой выборки . Ссылку указывать лучше с заголовком. В этом случае, при выводе результата надстройка выводит заголовки, которые делают результат нагляднее (в окне требуется установить галочку Метки );
- интервал переменной 2 : ссылка на значения второй выборки ;
- гипотетическая средняя разность : укажите значение Δ 0 , т.е. μ 1 - μ 2 . В нашем случае, введем 0;
- Метки: если в полях интервал переменной 1 и интервал переменной 2 указаны ссылки вместе с заголовками столбцов, то эту галочку нужно установить. В противном случае надстройка не позволит провести вычисления и пожалуется, что « входной интервал содержит нечисловые данные »;
- Альфа:уровень значимости α;
- Выходной интервал: диапазон ячеек, куда будут помещены результаты вычислений. Достаточно указать левую верхнюю ячейку этого диапазона.
В результате вычислений будет заполнен указанный Выходной интервал.
Тот же результат можно получить с помощью формул (см. файл примера лист Пакет анализа ):
Разберем результаты вычислений, выполненных надстройкой:
- Среднее : средние значения обеих выборок Хср 1 и Хср 2 . Вычисления можно сделать с помощью функции СРЗНАЧ() ;
- Дисперсия : дисперсии обеих выборок. Вычисления можно сделать с помощью функции ДИСП.В()
- Наблюдения : размер выборок. Вычисления можно сделать с помощью функции СЧЁТ() ;
- Объединенная дисперсия : Объединенная оценка дисперсии, т.е.величина s p 2 ;
- Df : число степеней свободы, т.е. величина n 1 +n 2 –2;
- t-статистика : значение тестовой статистикиt (в наших обозначениях – это t0 ). Вычисление t0 приведено в ячейке Е15 ;
- P(T Δ 0 . Эквивалентная формула =1-СТЬЮДЕНТ.РАСП( t0 ; n 1 +n 2 –2;ИСТИНА) ;
- t критическое одностороннее : Верхний α-квантиль t-распределения . Эквивалентная формула =СТЬЮДЕНТ.ОБР(1- α; n 1 +n 2 –2) ;
- P(T Δ 0 . Эквивалентная формула =2*(1-СТЬЮДЕНТ.РАСП(ABS( t0 ); n 1 +n 2 –2;ИСТИНА)) ;
- t критическое двухстороннее: Верхний α/2-Квантиль t-распределения . Эквивалентная формула =СТЬЮДЕНТ.ОБР(1- α/2; n 1 +n 2 –2) .
СОВЕТ : О проверке других видов гипотез см. статью Проверка статистических гипотез в MS EXCEL .
Одним из наиболее известных статистических инструментов является критерий Стьюдента. Он используется для измерения статистической значимости различных парных величин. Microsoft Excel обладает специальной функцией для расчета данного показателя. Давайте узнаем, как рассчитать критерий Стьюдента в Экселе.
Определение термина
Но, для начала давайте все-таки выясним, что представляет собой критерий Стьюдента в общем. Данный показатель применяется для проверки равенства средних значений двух выборок. То есть, он определяет достоверность различий между двумя группами данных. При этом, для определения этого критерия используется целый набор методов. Показатель можно рассчитывать с учетом одностороннего или двухстороннего распределения.
Расчет показателя в Excel
Теперь перейдем непосредственно к вопросу, как рассчитать данный показатель в Экселе. Его можно произвести через функцию СТЬЮДЕНТ.ТЕСТ. В версиях Excel 2007 года и ранее она называлась ТТЕСТ. Впрочем, она была оставлена и в позднейших версиях в целях совместимости, но в них все-таки рекомендуется использовать более современную — СТЬЮДЕНТ.ТЕСТ. Данную функцию можно использовать тремя способами, о которых подробно пойдет речь ниже.
Способ 1: Мастер функций
Проще всего производить вычисления данного показателя через Мастер функций.
В поле «Хвосты» вписываем значение «1», если будет производиться расчет методом одностороннего распределения, и «2» в случае двухстороннего распределения.
В поле «Тип» вводятся следующие значения:
- 1 – выборка состоит из зависимых величин;
- 2 – выборка состоит из независимых величин;
- 3 – выборка состоит из независимых величин с неравным отклонением.
Выполняется расчет, а результат выводится на экран в заранее выделенную ячейку.
Способ 2: работа со вкладкой «Формулы»
Функцию СТЬЮДЕНТ.ТЕСТ можно вызвать также путем перехода во вкладку «Формулы» с помощью специальной кнопки на ленте.
-
Выделяем ячейку для вывода результата на лист. Выполняем переход во вкладку «Формулы».
Способ 3: ручной ввод
Формулу СТЬЮДЕНТ.ТЕСТ также можно ввести вручную в любую ячейку на листе или в строку функций. Её синтаксический вид выглядит следующим образом:
Что означает каждый из аргументов, было рассмотрено при разборе первого способа. Эти значения и следует подставлять в данную функцию.
После того, как данные введены, жмем кнопку Enter для вывода результата на экран.
Как видим, вычисляется критерий Стьюдента в Excel очень просто и быстро. Главное, пользователь, который проводит вычисления, должен понимать, что он собой представляет и какие вводимые данные за что отвечают. Непосредственный расчет программа выполняет сама.
Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Корреляционный анализ – популярный метод статистического исследования, который используется для выявления степени зависимости одного показателя от другого. В Microsoft Excel имеется специальный инструмент, предназначенный для выполнения этого типа анализа. Давайте выясним, как пользоваться данной функцией.
Суть корреляционного анализа
Предназначение корреляционного анализа сводится к выявлению наличия зависимости между различными факторами. То есть, определяется, влияет ли уменьшение или увеличение одного показателя на изменение другого.
Если зависимость установлена, то определяется коэффициент корреляции. В отличие от регрессионного анализа, это единственный показатель, который рассчитывает данный метод статистического исследования. Коэффициент корреляции варьируется в диапазоне от +1 до -1. При наличии положительной корреляции увеличение одного показателя способствует увеличению второго. При отрицательной корреляции увеличение одного показателя влечет за собой уменьшение другого. Чем больше модуль коэффициента корреляции, тем заметнее изменение одного показателя отражается на изменении второго. При коэффициенте равном 0 зависимость между ними отсутствует полностью.
Расчет коэффициента корреляции
Теперь давайте попробуем посчитать коэффициент корреляции на конкретном примере. Имеем таблицу, в которой помесячно расписана в отдельных колонках затрата на рекламу и величина продаж. Нам предстоит выяснить степень зависимости количества продаж от суммы денежных средств, которая была потрачена на рекламу.
Способ 1: определение корреляции через Мастер функций
Одним из способов, с помощью которого можно провести корреляционный анализ, является использование функции КОРРЕЛ. Сама функция имеет общий вид КОРРЕЛ(массив1;массив2).
-
Выделяем ячейку, в которой должен выводиться результат расчета. Кликаем по кнопке «Вставить функцию», которая размещается слева от строки формул.
В поле «Массив2» нужно внести координаты второго столбца. У нас это затраты на рекламу. Точно так же, как и в предыдущем случае, заносим данные в поле.
Как видим, коэффициент корреляции в виде числа появляется в заранее выбранной нами ячейке. В данном случае он равен 0,97, что является очень высоким признаком зависимости одной величины от другой.
Способ 2: вычисление корреляции с помощью пакета анализа
Кроме того, корреляцию можно вычислить с помощью одного из инструментов, который представлен в пакете анализа. Но прежде нам нужно этот инструмент активировать.
-
Переходим во вкладку «Файл».
Параметр «Группирование» оставляем без изменений – «По столбцам», так как у нас группы данных разбиты именно на два столбца. Если бы они были разбиты построчно, то тогда следовало бы переставить переключатель в позицию «По строкам».
В параметрах вывода по умолчанию установлен пункт «Новый рабочий лист», то есть, данные будут выводиться на другом листе. Можно изменить место, переставив переключатель. Это может быть текущий лист (тогда вы должны будете указать координаты ячеек вывода информации) или новая рабочая книга (файл).
Так как место вывода результатов анализа было оставлено по умолчанию, мы перемещаемся на новый лист. Как видим, тут указан коэффициент корреляции. Естественно, он тот же, что и при использовании первого способа – 0,97. Это объясняется тем, что оба варианта выполняют одни и те же вычисления, просто произвести их можно разными способами.
Как видим, приложение Эксель предлагает сразу два способа корреляционного анализа. Результат вычислений, если вы все сделаете правильно, будет полностью идентичным. Но, каждый пользователь может выбрать более удобный для него вариант осуществления расчета.
Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Читайте также: