Сравнение двух выборок в 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

А. Сравнение выборочных дисперсий.

Выбираем раздел меню Данные — «Анализ данных»-«Двухвыборочный тест для дисперсии» (Рис. 8).

В появившимся окне указываем диапазоны ячеек с вариантами обеих выборок («Интервал переменной 1» и «Интервал переменной 2), если диапазон ячеек выделяем вместе с названием столбцов ставим отметку в окошке «Метки», нажимаем «ОК» (как показано на Рисунке 8) и получаем таблицу с результатами.

Б. Сравнение средних.

Выбираем раздел меню «Данные»-«Анализ данных»-«Двухвыборочный t-тест с одинаковыми дисперсиями» (Рис. 9).

появившимся окнеуказываем диапазоны ячеек с вариантами обеих выборок («Интервал переменной 1» и «Интервал переменной 2). Если диапазон ячеек выделяем вместе с названием столбцов, ставим отметку в окошке «Метки», нажимаем «ОК» (как показано на Рисунке 9) и получаем таблицу с результатами.

3. Сравнение двух выборок с помощью приложения «Статистика».

Для начала подготовим наши данные: сгруппируем данные обеих выборок в единый ряд и введем еще одну переменную — код выборки: значениям признака из выборки 1 соответствует 1, значениям признака из выборки 2 – соответствует цифра 2 (Таблица 3).

Таблица 3. Исходные данные

Вставляем данные (оба столбца) в специальную таблицу Spreadsheet. Выбираем последовательно разделы Statistics-Basic Statistics/Tables-t—test, idependent by groups (как показано на Рисунке 10).

В открывшейся вкладке (Рис. 11) нажимаем кнопку Variables, чтобы указать, в каком столбце находятся значения изучаемого признака (Dependent variables), в каком — значения группирующей переменной (Grouping variables), в данном случае это коды выборки.

Нажимая кнопки Summary: T—tests и Box&whisker plot соответственно получим таблицу с итогами сравнения выборок и графическое изображение средних значений в выборках с различными видами интервалов.

Для более точного графического изображения доверительного интервала для средних обеих выборок следует воспользоваться разделом меню Graphs-Means w/Error Plots….(Рис. 12). В появившемся окне нажимаем кнопку Variables и указываем, в каком столбце находятся данные: Dependent variable (значения признака) и Grouping variable (коды выборки). Все остальные отметки оставляем неизменными (тип графика Graph type– Whiskers; группировка данных в б/интервальный вариационный ряд – Grouping intervals — Unique value, тип интервала – 95% доверительный интервал — Whiskers — Conf. Interval) и нажимаем ОК.

4. Непараметрическое сравнение выборочных статистик.

U-критерий Манна-Уитни Ограничения применимости критерия

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

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

Для применения U-критерия Манна-Уитни нужно произвести следующие операции.

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

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

Определить значение U-критерия Манна-Уитни по формуле: .

По таблице определить критические значения критерия для данных n₁ и n₂. Если полученное значение U меньше табличного или равно ему для избранного уровня статистической значимости, то признается наличие существенного различия между уровнем признака в рассматриваемых выборках (принимается альтернативная гипотеза). Если же полученное значение U больше табличного, принимается нулевая гипотеза. Достоверность различий тем выше, чем меньше значение U.

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

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

А. Ранжируем варианты обеих выборок в один общий ряд. Для этого:

Создадим еще одну таблицу (Табл. 4): 1 столбец – значения признака (в обеих выборках), 2 столбец – номер выборки.

ыделяем оба столбца (без названий) и сортируем (Данные-Сортировка) данные по столбцу со значениями признака (Рис. 13).

Вводим еще два столбца: 1- с порядковыми значениями вариант и 2 – где вычисляем для каждой варианты ранг (одинаковым значениям признака присваивается одинаковый ранг) (Табл. 4).

Табл. 4. Ранжирование данных обеих выборок в единый ряд и присвоение рангов отдельным значениям признака.

Для сравнения дисперсий двух выборок применяется критерий Фишера. Он определяется по следующей формуле

(55)

где — наибольшая дисперсия;

— наименьшая дисперсия.

Далее расчетный критерий сравнивается с табличным значением. Если F>F_расч, то гипотеза о равенстве дисперсий отвергается.

Значения критерия Фишера (F-критерия) для уровня значимости p = 0,05 приведены в таблице 16, где f₁ — число степеней свободы большей дисперсии, f₂ — число степеней свободы меньшей дисперсии.

В программе Excel проверка однородности дисперсий осуществляется с помощью функции ФТЕСТ (рис. 30). F-тест возвращает одностороннюю вероятность того, что дисперсии аргументов массив1 и массив2 различаются несущественно. Эта функция используется для того, чтобы определить, имеют ли две выборки различные дисперсии. Например, если даны результаты тестирования для частных и общественных школ, то можно определить, имеют ли эти школы различные уровни разнородности учащихся по результатам тестирования.

Рис. 30. Функция ФТЕСТ

Массив1— это первый массив или интервал данных.

Массив2— это второй массив или интервал данных.

Аргументы должны быть числами или именами, массивами или ссылками, содержащими числа.

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

Если количество точек данных в аргументе массив1 или массив2 меньше 2, или если дисперсия аргумента массив1 или массив2 равна нулю, то функция ФТЕСТ возвращает значение ошибки #ДЕЛ/0!.

В надстройке «Пакет анализа» используется двухвыборочный F-тест для дисперсии.

Элементы диалогового окна «Двухвыборочный F-тест для дисперсии» приведены на рис. 29. Элементы диалогового окна «Двухвыборочный F-тест для дисперсии» совпадают с элементами диалогового окна «Двухвыборочный t-тест с одинаковыми дисперсиями».

Рис. 29. Двухвыборочный F-тест для дисперсии

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

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