Двухвыборочный f тест для дисперсии excel где найти

Обновлено: 06.01.2025

Средство анализа «Двухвыборочный F-тест для дисперсии» надстройки «Пакет анализа» MS Excel служит для проверки гипотезы о равенстве дисперсий двух выборок. Для проверки необходимо заполнить диалоговое окно, приведенное на рис.4.6, назначение всех полей ввода очевидно.

Рис. 4.6 Диалоговое окно средства анализа «Двухвыборочный F-тест для дисперсии» надстройки «Пакет анализа» MS Excel

Результаты расчета представлены на рис.4.7.

Сравните полученные результаты с результатами, полученными вручную.

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

надстройки «Пакет анализа» MS Excel

4.3. Критерий Стьюдента ( t-критерий)

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

Пусть заданы две генеральные совокупности x и y, имеющие нормальное распределение, из них взяты выборки и , т.е. n₁ и n₂ - объемы первой и второй выборки соответственно. Выдвигается гипотеза H₀ , что средние значения выборок равны (альтернативная гипотеза H₁ - средние значения не равны).

Значение вычисляют по формуле:

(4.8)

где — средние арифметические выборок и ;

S - стандартная ошибка разности средних значений.

Число степеней свободы вычисляют по формуле:

(4.9)

Если , то гипотеза H₀ принимается, в противном случае принимается альтернативная гипотеза.

Стандартная ошибка разности средних значений S вычисляется различными способами в зависимости от поставленной задачи:

· сравнение двух выборок;

· сравнение двух зависимых выборок;

· сравнение более двух независимых выборок.

4.3.1. Случай двух независимых выборок.

Требуется сравнить средние значения двух независимых выборок. Здесь возможны два варианта:

1. Дисперсии выборок равны.

2. Дисперсии выборок не равны.

Рассмотрим первый вариант (дисперсии выборок равны). В этом случае значение S вычисляется по формуле

(4.10)

гдеn₁ и n₂ - объемы первой и второй выборки; и — средние арифметические выборок.

Пример 4.

В двух группах учащихся — экспериментальной и контрольной — применялись две различные методики обучения: экспериментальная и традиционная. После завершения обучения был проведен тест и получены следующие результаты по учебному предмету (тестовые баллы; см. табл. 4.4).

Результаты эксперимента

Первая группа (экспериментальная), N₁=11 человек

Вторая группа (контрольная), N₂=9 человек

Имеет ли экспериментальный метод обучения преимущество по сравнению с традиционным?

Решение

Общее количество членов выборки: n₁=11, n₂=9; средние значения: =13,636; =9,444. По формуле (4.10) находим стандартную ошибку разности средних значений:

Рис. 4.8. Проверка гипотезы о совпадении двух выборочных средних (фрагмент рабочего листа MS Excel в режиме отображения данных).

Вычисляем значение

Вычислим табличное значение с помощью встроенной функции СТЬЮДРАСПОБР(). Для этого определим число степеней свободы по формуле ,

Рис.4.9а. Проверка гипотезы о совпадении двух выборочных средних

(начало) (фрагмент рабочего листа MS Excel в режиме отображения формул)

Рис.4.9.б. Проверка гипотезы о совпадении двух выборочных средних (окончание) (фрагмент рабочего листа Excel в режиме отображения формул)

Для определения существования тенденции воспользуемся свойствами ППП «Excel». Откроем меню СЕРВИС-НАДСТРОЙКИ и активируем задачу ПАКЕТ АНАЛИЗА.

Рис. 31. Окно ППП «Excel», меню СЕРВИС-НАДСТРОЙКИ.

После этого, необходимо разделить исходный временной ряд на две равные половины. Далее откроем меню СЕРВИС – АНАЛИЗ ДАННЫХ, в этом подменю выберем функцию «Двухвыборочный F-тест для дисперсии», рис 32., нажмите ОК, появится диалоговое окно выполнения поставленной задачи, рис. 33.

Рис. 32. Диалоговое окно АНАЛИЗ ДАННЫХ.

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

В поле «Интервал переменной 1» вводим данные первой половины временного ряда, в поле «Интервал переменной 2» соответственно данные второй половины. Результаты выводим на новый рабочий лист. Получается таблица следующего вида, табл. 2.15., рис. 34.

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

Переменная 1

Переменная 2

F критическое одностороннее

Рис. 34. Результаты F-теста для дисперсии.

Из теории по методу разности средних уровней известно, что Fрасч должен быть больше единицы. По сделанным расчетам видно, Fрасч = 0,143, что значительно меньше единицы. Следовательно, нужно провести расчеты снова, только теперь поменять выборки местами, то есть Интервалом переменной 1 будут данные из второй половины временного ряда, а Интервалом переменной 2 соответственно данные из первой половины исходного временного ряда. В результате получим следующие результаты, рис. 35.

Рис. 35. Результаты расчета «Двухвыборочного F-теста для дисперсии».

По второму расчету «Двухвыборочного F-теста для дисперсии» Fрасч>Fтабл, следовательно, дисперсии неоднородны, поэтому для дальнейшего анализа выбираем функцию из подменю АНАЛИЗ ДАННЫХ «Двухвыборочный t-тест с разными дисперсиями», рис. 36., нажимаем ОК.

Рис.36. Диалоговое окно подменю АНАЛИЗ ДАННЫХ.

В результате высвечивается следующее окно, рис. 37.

Рис. 37. Диалоговое окно «Двухвыборочный t-тест

С различными дисперсиями»

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

По полученным результатам видно, что tрасч=9,67, tтабл=2,07, следовательно, tрасч>tтабл. Можно сделать вывод, что нулевая гипотеза не подтвердилась, и тенденция в исходных данных существует.

Если Fрасч>1 и выполняется условие Fрасч<Fтабл, то дисперсии однородны, и для дальнейшего анализа существования тенденции в исходном временном ряду выбирается «Двухвыборочный t-тест с одинаковыми дисперсиями».

Рис. 38. Результаты расчета «Двухвыборочного t-теста

Для различных дисперсий»

2. Для выявления тенденции изменения показателя

Постройте линейный график данных

Для построения графика используйте команду ВСТАВКА – ДИАГРАММА – ГРАФИК либо мастер диаграмм. В результате выполнения этой команды появится окно МАСТЕР ДИАГРАММ (шаг 2 из 4):

Рис. 39. Диалоговое окно «Мастер диаграмм (шаг 2 из 4)».

В окне Диапазон укажите область столбца электронной таблицы, где находится массив данных показателя. Щелкните мышкой по кнопке ДАЛЕЕ. В результате появится окно следующего 3 шага. В соответствующих окнах введите заголовок графика и названия осей; разместите график на рабочем листе. В результате будет получено следующее, рис. 40.

Рис. 40. Исходные данные и диаграмма на одном листе.

3. Определение характера тенденции с помощью метода скользящей средней и экспоненциальной средней

При определении характера тенденции с помощью метода скользящей средней воспользуемся функцией подменю АНАЛИЗ ДАННЫХ – Скользящее среднее, рис. 41., в заключении нажимаем ОК. В результате получим диалоговое окно Скользящее среднее, рис. 42.

Рис. 41. Диалоговое окно Анализ данных.

Рис. 42. Диалоговое окно Скользящее среднее.

Рис. 43. Результаты анализа тенденции с помощью

метода скользящей средней.

Определение тенденции с помощью экспоненциальной средней проводится по той же схеме, что и с помощью метода скользящей средней.

4. Выбор вида модели тренда

Определить вид модели тренда можно на основе построенного по исходным данным графиком. Для этого надо выделить саму линию данных на диаграмме, и после этого нажать правую кнопку мыши. Появится следующее подменю, рис. 44., в котором выбирается функция «Добавить линию тренда…».

Рис. 44. Рабочий лист «Excel» с диаграммой.

После выбора данной функции высветится следующее диалоговое окно, рис. 45.

Рис. 45. Диалоговое окно «Линия тренда».

В этом диалоговом окне выбирается линия тренда, которая по вашему мнению, должна описывать изменение исследуемого показателя во времени. После выбора функции необходимо в этом же диалоговом окне открыть закладку «Параметры», рис. 46.

Рис. 46. Диалоговое окно «Линия тренда» закладка «Параметры».

В этом окне надо поставить галочки напротив следующих требований – «показывать уравнение на диаграмме» и «поместить на диаграмму величину достоверности аппроксимации (R2). В заключении нажать кнопку ОК. Результат появится сразу же на диаграмме, рис. 47.

Рис. 47. Рабочий лист Excel с линией тренда на диаграмме.

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

5. Расчет параметров модели тренда.

Проверка адекватности и точности

Расчет параметров модели тренда проводится с помощью функции регрессия. Применение в этом случае ППП «Excel» рассматривалось в разделах 1.2 и 2.2 – «Решение типовых задач с помощью ППП «Excel».

Особенность заключается в том, что при использовании регрессионного анализа при определении параметров модели зависимым показателем будет исследуемый показатель, а независимым – периоды времени t. Это для линейного тренда (прямая), а для полиномиального тренда, например, второй степени (парабола), независимыми показателями будут значения t и t2, а зависимым – исследуемый показатель у.

Проверка адекватности и точности модели тренда, построенного с помощью регрессии, проводится также как и в разделах 1.2 и 2.2.

6. Прогнозирование по модели тренда

Расчет прогноза можно провести тремя способами: по модели тренда, рассчитанной по регрессии, по исходным данным с помощью возможностей ППП «Excel», на основе диаграммы, то есть построение прогноза на графике с линией тренда.

В первом случае в уравнение регрессии подставляется значение периода прогноза и рассчитывается точечный прогноз. Затем по формулам рассчитывается верхняя и нижняя граница прогноза, в результате чего получается интервальный прогноз.

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

В третьем случае, когда строится прогноз на графике с линией тренда (рис. 47), необходимо указать следующие параметры при построении линии тренда в диалоговом окне Линия тренда закладка «Параметры» (рис. 46): количество точек для прогноза, уравнение тренда, достоверность аппроксимации.

F-тест - это статистический тест, который помогает нам определить, имеют ли два набора популяции, которые имеют нормальное распределение точек данных, одинаковое стандартное отклонение или дисперсию. Но первое и главное, что нужно выполнить F-тест, это то, что наборы данных должны иметь нормальное распределение. Это применяется к F-распределению при нулевой гипотезе. F-критерий является очень важной частью анализа отклонений (ANOVA) и рассчитывается путем взятия соотношений двух дисперсий двух разных наборов данных. Поскольку мы знаем, что отклонения дают нам информацию о разбросе точек данных. F-тест также используется в различных тестах, таких как регрессионный анализ, тест Чоу и т. Д.

Формула для F-теста:

Не существует простой формулы для F-Test, но это ряд шагов, которым мы должны следовать:

Шаг 1: Чтобы выполнить F-тест, сначала мы должны определить нулевую гипотезу и альтернативную гипотезу. Они даны:

H0 (нулевая гипотеза): дисперсия 1- го набора данных = дисперсия 2- го набора данных
Ha: дисперсия 1- го набора данных <дисперсия 2- го набора данных (для нижнего одностороннего теста)
Ha: Дисперсия 1- го набора данных> Дисперсия 2- го набора данных (для верхнего одностороннего теста)
Ha: дисперсия 1- го набора данных ≠ дисперсия 2- го набора данных (для двустороннего теста)

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

Шаг 3: Формула F-теста:

F Value = Variance of 1 st Data Set / Variance of 2 nd Data Set

Шаг 4: Найдите критическое значение F из таблицы F с учетом степени свободы и уровня значимости.

Шаг 5: Сравните эти два значения, и если критическое значение меньше значения F, вы можете отклонить нулевую гипотезу.

Примеры формулы F-теста (с шаблоном Excel)

Давайте рассмотрим пример, чтобы лучше понять расчет F-теста.

Вы можете скачать этот шаблон F-TEST Formula Excel здесь - Шаблон F-TEST Formula Excel

Формула F-теста - пример № 1

Допустим, у нас есть два набора данных A & B, которые содержат разные точки данных. Выполните F-тест, чтобы определить, можем ли мы отклонить нулевую гипотезу на уровне значимости 1%.

Наборы данных:

Решение:

Нулевая гипотеза: дисперсия A = дисперсия B

Степень свободы рассчитывается как

Вариация рассчитывается как:

Значение F рассчитывается по формуле, приведенной ниже

Значение F = дисперсия 1- го набора данных / дисперсия 2- го набора данных

Значение F = 1385, 61 / 521, 22
Значение F = 2.6584

Так что F критическое значение = 3.5225

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

Формула F-теста - пример № 2

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

Решение:

Степень свободы рассчитывается как

Вариация рассчитывается как:

Дисперсия XYZ = 1, 2 ^ 2 = 1, 44
Дисперсия ABC = 1, 1 ^ 2 = 1, 21

Значение F = 1, 44 / 1, 21
Значение F = 1, 19

F Критическое значение = 3, 137

Поскольку критическое значение F> F, нулевая гипотеза не может быть отклонена.

объяснение

В приведенных выше примерах мы видели применение F-Test и то, как оно выполняется. Но есть ряд предположений, которые мы должны позаботиться перед выполнением F-Test, иначе мы не получим требуемых результатов:

Во-первых, нам нужно всегда размещать числитель с более высоким значением дисперсии при расчете значения F. Так что, если F = V1 / V2, V1 должно быть> V2
Если мы хотим выполнить тест 2 хвоста, нам нужно разделить уровень значимости на 2, и это будет правильный уровень, чтобы найти критическое значение
Мы используем только дисперсию для расчета значения F, и если нам дают стандартные отклонения, как в примере 2, они должны быть возведены в квадрат, чтобы найти дисперсию.
Обе выборки должны быть независимы друг от друга, а размер выборки должен быть менее 30
Популяции, из которых отбираются пробы, должны быть нормально распределены

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

Актуальность и использование формулы F-Test

F-тест, как обсуждалось выше, помогает нам проверить равенство двух популяционных дисперсий. Поэтому, когда у нас есть две независимые выборки, взятые из нормальной популяции, и мы хотим проверить, имеют ли они одинаковую изменчивость, мы используем F-тест. F-критерий также имеет большое значение для регрессионного анализа, а также для проверки значимости R 2 . Таким образом, в двух словах, F-Test является очень важным инструментом в статистике, если мы хотим сравнить вариацию двух или более наборов данных. Но перед выполнением этого теста следует помнить все предположения.

Графики функций

F -распределение при небольших параметрах (

В файле примера на листе График приведены графики плотности распределения вероятности и интегральной функции распределения .

Примечание : Для построения функции распределения и плотности вероятности можно использовать диаграмму типа График или Точечная (со сглаженными линиями и без точек). Подробнее о построении диаграмм читайте статью Основные типы диаграмм .

F-распределение в MS EXCEL

Примечание : Плотность вероятности можно также вычислить впрямую, с помощью формул (см. файл примера ).

Примеры расчетов приведены в файле примера на листе Функции .

Оценка взаимосвязи прибыли и затрат по функции ФИШЕР

Пример 1. Используя данные об активности коммерческих организаций, требуется сделать оценку связи прибыли Y (млн руб.) и затрат X (млн руб.), используемых для разработки продукции (приведены в таблице 1).

Таблица 1 – Исходные данные:

№	X	Y
1	210 000 000,00 ₽	95 000 000,00 ₽
2	1 068 000 000,00 ₽	76 000 000,00 ₽
3	1 005 000 000,00 ₽	78 000 000,00 ₽
4	610 000 000,00 ₽	89 000 000,00 ₽
5	768 000 000,00 ₽	77 000 000,00 ₽
6	799 000 000,00 ₽	85 000 000,00 ₽

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

№ п/п	Наименование показателя	Формула расчета
1	Коэффициент корреляции	=КОРРЕЛ(B2:B7;C2:C7)
2	Расчетное значение t-критерия tp	=ABS(C8)/КОРЕНЬ(1-СТЕПЕНЬ(C8;2))*КОРЕНЬ(6-2)
3	Табличное значение t-критерия trh	=СТЬЮДРАСПОБР(0,05;4)
4	Табличное значение стандартного нормального распределения zy	=НОРМСТОБР((0,95+1)/2)
5	Значение преобразования Фишера z’	=ФИШЕР(C8)
6	Левая интервальная оценка для z	=C12-C11*КОРЕНЬ(1/(6-3))
7	Правая интервальная оценка для z	=C12+C11*КОРЕНЬ(1/(6-3))
8	Левая интервальная оценка для rxy	=ФИШЕРОБР(C13)
9	Правая интервальная оценка для rxy	=ФИШЕРОБР(C14)
10	Стандартное отклонение для rxy	=КОРЕНЬ((1-C8^2)/4)

Таким образом, с вероятностью 0,95 линейный коэффициент корреляции заключен в интервале от (–0,386) до (–0,990) со стандартной ошибкой 0,205.

Проверка статистической значимости регрессии по функции FРАСПОБР

Пример 2. Произвести проверку статистической значимости уравнения множественной регрессии с помощью F-критерия Фишера, сделать выводы.

Для проверки значимости уравнения в целом выдвинем гипотезу Н₀ о статистической незначимости коэффициента детерминации и противоположную ей гипотезу Н₁ о статистической значимости коэффициента детерминации:

Проверим гипотезы с помощью F-критерия Фишера. Показатели приведены в таблице 2.

Таблица 2 – Исходные данные

Для этого используем в пакете Excel функцию:

α – вероятность, связанная с данным распределением;
p и n – числитель и знаменатель степеней свободы, соответственно.

Зная, что α = 0,05, p = 2 и n = 53, получаем следующее значение для F_крит (см. рисунок 2).

Рисунок 2 – Пример расчетов.

Таким образом можно сказать, что F_расч > F_крит. В итоге принимается гипотеза Н₁ о статистической значимости коэффициента детерминации.

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

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

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

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

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

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

Для оценки статистической значимости модели по параметрам рассчитывают 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. Если фи*эмп больше чем фи*крит, различия между группами статистически достоверны.

Показатели качества уравнения регрессии

Показатель	Значение
Коэффициент детерминации	0.49
Средний коэффициент эластичности	0.51
Средняя ошибка аппроксимации	10.89

Для чего используется точный критерий Фишера?

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

Особое место отводится точному критерию Фишера в медицине. Это важный метод обработки медицинских данных, нашедший свое применение во многих научных исследованиях. Благодаря ему можно исследовать взаимосвязь определенных фактора и исхода, сравнивать частоту патологических состояний между разными группами пациентов и т.д.

В каких случаях можно использовать точный критерий Фишера?

Сравниваемые переменные должны быть измерены в номинальной шкале и иметь только два значения, например, артериальное давление в норме или повышено, исход благоприятный или неблагоприятный, послеоперационные осложнения есть или нет.
Критерий подходит для сравнения очень малых выборок: точный критерий Фишера может применяться для анализа четырехпольных таблиц в случае значений ожидаемого явления менее 10, что является ограничением для применения критерия хи-квадрат Пирсона.
Точный критерий Фишера бывает односторонним и двусторонним. При одностороннем варианте точно известно, куда отклонится один из показателей. Например, во время исследования сравнивают, сколько пациентов выздоровело по сравнению с группой контроля. Предполагают, что терапия не может ухудшить состояние пациентов, а только либо вылечить, либо нет.
Двусторонний тест является предпочтительным, так как оценивает различия частот по двум направлениям. То есть оценивается верятность как большей, так и меньшей частоты явления в экспериментальной группе по сравнению с контрольной группой.

Аналогом точного критерия Фишера является Критерий хи-квадрат Пирсона, при этом точный критерий Фишера обладает более высокой мощностью, особенно при сравнении малых выборок, в связи с чем в этом случае обладает преимуществом.

Критические точки распределения Фишера

(k₁— число степеней свободы большей дисперсии,
k₂—число степеней свободы меньшей дисперсии)
Уровень значимости a =0.01

Читайте также: