Сравнение двух выборок в excel
Обновлено: 06.07.2024
Microsoft Excel имеет мощные инструменты для анализа и визуализации статистики. В этой статье мы продемонстрируем их на примере классической статистической процедуры: теста Стьюдента или t-теста.
t-тест: что это такое, и зачем это нужно?
t-тест или тест Стьюдента был разработан английским математиком Уильямом Госсетом. В начале XX века он трудился на пивоваренном заводе «Гиннесс» в Ирландии, разрабатывая математические методы оценки качества сырья, из которого варят пиво.
По условиям контракта, Госсет не имел права публиковать свои разработки под собственным именем. Поэтому первая публикация методики теста появилась в журнале «Биометрика» под псевдонимом Student, что значит «студент». Так тест и остался в истории под названием теста Стьюдента.
Тест Стьюдента позволяет сравнивать случайные выборки данных — либо с некой нормой, либо между собой. Например, завод выпускает шурупы, и нужно оценить, соответствуют ли они в норме по длине. Или в больнице ведется клиническое исследование лекарства, и нужно оценить его эффект на пациентах до и после приема.
В обоих случаях должно выполняться требование нормальности распределения исследуемого признака в каждой из сравниваемых групп. Результатом выполнения теста является число, отражающее данный показатель — t-критерий, или критерий Стьюдента.
t-тест: как выполнить в MS Excel?
Вне зависимости от того, для чего вам может понадобиться вычислить критерий Стьюдента, в Microsoft Excel есть функция TTEST, которая позволяет это сделать. Она доступна в надстройке Пакет анализа. Рассмотрим, как использовать функцию на примере Microsoft Excel 2013 в Windows 7. Синтаксис функции следующий: ТТЕСТ(массив1;массив2;хвосты;тип). Скопируйте эту формулу в любое поле вашего документа Excel. Первый набор данных называется «массив1», а «массив2», соответственно, обозначает второй набор данных. В примере (см. изображение) «массив1» имеет значение «A2:A10».
Переменная «хвосты» определяет тип распределения. Если в ячейке имеется значение «1», используется одностороннее распределение; если имеется значение «2», то используется двустороннее распределение.
Одним из наиболее известных статистических инструментов является критерий Стьюдента. Он используется для измерения статистической значимости различных парных величин. Microsoft Excel обладает специальной функцией для расчета данного показателя. Давайте узнаем, как рассчитать критерий Стьюдента в Экселе.
Определение термина
Но, для начала давайте все-таки выясним, что представляет собой критерий Стьюдента в общем. Данный показатель применяется для проверки равенства средних значений двух выборок. То есть, он определяет достоверность различий между двумя группами данных. При этом, для определения этого критерия используется целый набор методов. Показатель можно рассчитывать с учетом одностороннего или двухстороннего распределения.
Расчет показателя в Excel
Способ 1: Мастер функций
Проще всего производить вычисления данного показателя через Мастер функций.
-
Строим таблицу с двумя рядами переменных.
В поле «Хвосты» вписываем значение «1», если будет производиться расчет методом одностороннего распределения, и «2» в случае двухстороннего распределения.
В поле «Тип» вводятся следующие значения:
- 1 – выборка состоит из зависимых величин;
- 2 – выборка состоит из независимых величин;
- 3 – выборка состоит из независимых величин с неравным отклонением.
Выполняется расчет, а результат выводится на экран в заранее выделенную ячейку.
Способ 2: работа со вкладкой «Формулы»
Функцию СТЬЮДЕНТ.ТЕСТ можно вызвать также путем перехода во вкладку «Формулы» с помощью специальной кнопки на ленте.
-
Выделяем ячейку для вывода результата на лист. Выполняем переход во вкладку «Формулы».
Способ 3: ручной ввод
Формулу СТЬЮДЕНТ.ТЕСТ также можно ввести вручную в любую ячейку на листе или в строку функций. Её синтаксический вид выглядит следующим образом:
Что означает каждый из аргументов, было рассмотрено при разборе первого способа. Эти значения и следует подставлять в данную функцию.
После того, как данные введены, жмем кнопку Enter для вывода результата на экран.
Как видим, вычисляется критерий Стьюдента в Excel очень просто и быстро. Главное, пользователь, который проводит вычисления, должен понимать, что он собой представляет и какие вводимые данные за что отвечают. Непосредственный расчет программа выполняет сама.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Небольшая памятка для студентов и аналитиков всех отраслей: как использовать Microsoft Excel для вычисления критерия Стьюдента.
Microsoft Excel имеет мощные инструменты для анализа и визуализации статистики. В этой статье мы продемонстрируем их на примере классической статистической процедуры: теста Стьюдента или t-теста.
t-тест: что это такое, и зачем это нужно?
t-тест или тест Стьюдента был разработан английским математиком Уильямом Госсетом. В начале XX века он трудился на пивоваренном заводе «Гиннесс» в Ирландии, разрабатывая математические методы оценки качества сырья, из которого варят пиво.
По условиям контракта, Госсет не имел права публиковать свои разработки под собственным именем. Поэтому первая публикация методики теста появилась в журнале «Биометрика» под псевдонимом Student, что значит «студент». Так тест и остался в истории под названием теста Стьюдента.
Тест Стьюдента позволяет сравнивать случайные выборки данных — либо с некой нормой, либо между собой. Например, завод выпускает шурупы, и нужно оценить, соответствуют ли они в норме по длине. Или в больнице ведется клиническое исследование лекарства, и нужно оценить его эффект на пациентах до и после приема.
В обоих случаях должно выполняться требование нормальности распределения исследуемого признака в каждой из сравниваемых групп. Результатом выполнения теста является число, отражающее данный показатель — t-критерий, или критерий Стьюдента.
t-тест: как выполнить в MS Excel?
Вне зависимости от того, для чего вам может понадобиться вычислить критерий Стьюдента, в Microsoft Excel есть функция TTEST, которая позволяет это сделать. Она доступна в надстройке Пакет анализа. Рассмотрим, как использовать функцию на примере Microsoft Excel 2013 в Windows 7.
Как выполнить t-тест в Excel 2013
Синтаксис функции следующий: ТТЕСТ(массив1;массив2;хвосты;тип). Скопируйте эту формулу в любое поле вашего документа Excel.
Для выявления различий между двумя выборками с известным законом распределения применяют t-критерий различия Стьюдента и критерий различия Фишера. При этом предполагается, что данные распределены по нормальному закону. Первый критерий сравнивает средние двух выборок и вычисляет вероятность того, что они относятся к одной и той же генеральной совокупности. Второй критерий проверяет принадлежность дисперсий двух выборок одной генеральной совокупности. В обоих случаях по вычисленной вероятности судят о принадлежности выборок к одной или разным совокупностям: если вероятность случайного появления значений в исследуемых выборках меньше уровня значимости α
Воспроизведите полученные результаты. В ячейку В14 введите функцию ТТЕСТ из группы Статистические, заполните параметры и нажмите ОК. Здесь выбран Тип=3, поскольку выборки не связаны, независимы и с разным числом значений.
Далее вызовите инструмент Двухвыборочный t-тест с различными дисперсиями через меню Сервис Анализ данных…. На рис. 2.93 показано заполнение параметров инструмента. Интервал переменной 1 $А$2:$A$10 и интервал переменной 2 $B$2:$B$12 это диапазоны анализируемых данных. Выходной интервал $D$1 – это ячейка, начиная с которой будет выведен результат. Поле Альфа позволяет установить требуемый уровень значимости α =0.05.
Отметим важность правильного подбора типа t-теста, поскольку для одних и тех же данных они могут давать разные результаты. Если выбор типа t-теста не очевиден, то правильным будет применение двухвыборочного t-теста с разными дисперсиями как общий случай анализа; если выборки зависимы и связаны, то применяют парный t-тест.
Дисперсионный анализ
Часто требуется оценить существенность влияния на выборки одного или нескольких факторов. При этом выборки должны стремиться к нормальному распределению и быть независимыми. В Excel включены следующие инструменты: Однофакторный дисперсионный анализ, Двухфакторный дисперсионный анализ с повторениями, Двухфакторный дисперсионный анализ без повторения.
Рассмотрим однофакторный дисперсионный анализ. Степень влияния фактора на выборку определяется сравнением дисперсий двух выборок: выборки с наличием исследуемого фактора и выборки без этого фактора (со случайными причинами). Инструмент Excel Однофакторный дисперсионный анализ вычисляет вероятность случайности различий (Р-значение), которая указывает на значимость различий: если уровень значимости меньше 0.05, то различия не случайны и говорят о статистическом влиянии фактора на выборку (переменную).
В качестве примера проведем анализ влияния фактора цены комплексного обеда на дневную посещаемость кафе. На рисунке приведен результат анализа: Р-значение=0.00068257
А. Сравнение выборочных дисперсий.
В появившимся окне указываем диапазоны ячеек с вариантами обеих выборок («Интервал переменной 1» и «Интервал переменной 2), если диапазон ячеек выделяем вместе с названием столбцов ставим отметку в окошке «Метки», нажимаем «ОК» (как показано на Рисунке 8) и получаем таблицу с результатами.
Б. Сравнение средних.
Выбираем раздел меню «Данные»-«Анализ данных»-«Двухвыборочный t-тест с одинаковыми дисперсиями» (Рис. 9).
появившимся окнеуказываем диапазоны ячеек с вариантами обеих выборок («Интервал переменной 1» и «Интервал переменной 2). Если диапазон ячеек выделяем вместе с названием столбцов, ставим отметку в окошке «Метки», нажимаем «ОК» (как показано на Рисунке 9) и получаем таблицу с результатами.
3. Сравнение двух выборок с помощью приложения «Статистика».
Таблица 3. Исходные данные
4. Непараметрическое сравнение выборочных статистик.
U-критерий Манна-Уитни Ограничения применимости критерия
В каждой из выборок должно быть не менее 3 значений признака. Допускается, чтобы в одной выборке было два значения, но во второй тогда не менее пяти.
В каждой выборке должно быть не более 60 значений параметра, но уже при выборках в 20 и более единиц ранжирование становится довольно трудоемким.
Для применения U-критерия Манна-Уитни нужно произвести следующие операции.
Составить единый ранжированный (в порядке возрастания) ряд из обоих сопоставляемых выборок, каждому значению признака присвоить ранг (ранги –числа натурального ранга; меньшему значению присваивается меньший ранг; одинаковым значениям признака присваивается одинаковый средний ранг).
Разделить единый ранжированный ряд на два, состоящие соответственно из единиц первой и второй выборок. Подсчитать отдельно сумму рангов, пришедшихся на долю элементов первой выборки, и отдельно — на долю элементов второй выборки. Определить большую из двух ранговых сумм (Tx), соответствующую выборке с nx единиц.
Определить значение U-критерия Манна-Уитни по формуле: .
По таблице определить критические значения критерия для данных n1 и n2. Если полученное значение U меньше табличного или равно ему для избранного уровня статистической значимости, то признается наличие существенного различия между уровнем признака в рассматриваемых выборках (принимается альтернативная гипотеза). Если же полученное значение U больше табличного, принимается нулевая гипотеза. Достоверность различий тем выше, чем меньше значение U.
Но: Наблюдаемые различия между значениями признака в рассматриваемых выборках случайны.
На: Наблюдаемые различия между значениями признака в рассматриваемых выборках не случайны.
А. Ранжируем варианты обеих выборок в один общий ряд. Для этого:
Создадим еще одну таблицу (Табл. 4): 1 столбец – значения признака (в обеих выборках), 2 столбец – номер выборки.
ыделяем оба столбца (без названий) и сортируем (Данные-Сортировка) данные по столбцу со значениями признака (Рис. 13).
Вводим еще два столбца: 1- с порядковыми значениями вариант и 2 – где вычисляем для каждой варианты ранг (одинаковым значениям признака присваивается одинаковый ранг) (Табл. 4).
Табл. 4. Ранжирование данных обеих выборок в единый ряд и присвоение рангов отдельным значениям признака.
Для сравнения дисперсий двух выборок применяется критерий Фишера. Он определяется по следующей формуле
(55)
Далее расчетный критерий сравнивается с табличным значением. Если F>Fрасч, то гипотеза о равенстве дисперсий отвергается.
В программе Excel проверка однородности дисперсий осуществляется с помощью функции ФТЕСТ (рис. 30). F-тест возвращает одностороннюю вероятность того, что дисперсии аргументов массив1 и массив2 различаются несущественно. Эта функция используется для того, чтобы определить, имеют ли две выборки различные дисперсии. Например, если даны результаты тестирования для частных и общественных школ, то можно определить, имеют ли эти школы различные уровни разнородности учащихся по результатам тестирования.
Рис. 30. Функция ФТЕСТ
Массив1— это первый массив или интервал данных.
Массив2— это второй массив или интервал данных.
Аргументы должны быть числами или именами, массивами или ссылками, содержащими числа.
Если аргумент, который является массивом или ссылкой, содержит текст, логические значения или пустые ячейки, эти значения игнорируются; ячейки, содержащие нулевые значения, учитываются.
В надстройке «Пакет анализа» используется двухвыборочный F-тест для дисперсии.
Элементы диалогового окна «Двухвыборочный F-тест для дисперсии» приведены на рис. 29. Элементы диалогового окна «Двухвыборочный F-тест для дисперсии» совпадают с элементами диалогового окна «Двухвыборочный t-тест с одинаковыми дисперсиями».
Рис. 29. Двухвыборочный F-тест для дисперсии
Двухвыборочный F-тест применяется для сравнения дисперсий двух генеральных совокупностей.
Например, можно использовать F-тест по выборкам результатов заплыва для каждой из двух команд. Это средство предоставляет результаты сравнения нулевой гипотезы о том, что эти две выборки взяты из распределения с равными дисперсиями, с гипотезой, предполагающей, что дисперсии различны в базовом распределении.
Читайте также: