Критерий фишера в excel примеры
Распределение Фишера (F-распределение). Распределения математической статистики в EXCEL
Рассмотрим распределение Фишера (F-распределение). С помощью функции MS EXCEL F .РАСП() построим графики функции распределения и плотности вероятности, поясним применение этого распределения для целей математической статистики.
F-распределение (англ. F-distribution) применяется для целей дисперсионного анализа (ANOVA), при проверке гипотезы о равенстве дисперсий двух нормальных распределений (F-тест) и др.
Определение : Если U 1 и U 2 независимые случайные величины, имеющие ХИ2-распределение с k 1 и k 2 степенями свободы соответственно, то распределение случайной величины:
носит название F -распределения с параметрами k 1 и k 2 .
Плотность F -распределения выражается формулой:
где Г(…) – гамма-функция:
если альфа – положительное целое, то Г( альфа )=( альфа -1)!
Приведем пример случайной величины, имеющей F -распределение.
Пусть имеется 2 нормальных распределения N(μ 1 ;σ 1 ) и N(μ 2 ; σ 2 ), из которых сделаны выборки размером n 1 и n 2 . Если s 1 2 и s 2 2 – дисперсии этих выборок , то отношение
имеет F -распределение. Это соотношение нам потребуется при проверке гипотезы о равенстве дисперсий двух нормальных распределений (F-тест) .
Графики функций
В файле примера на листе График приведены графики плотности распределения вероятности и интегральной функции распределения .
Примечание : Для построения функции распределения и плотности вероятности можно использовать диаграмму типа График или Точечная (со сглаженными линиями и без точек). Подробнее о построении диаграмм читайте статью Основные типы диаграмм .
F-распределение в MS EXCEL
В MS EXCEL, начиная с версии 2010, для F-распределения имеется специальная функция F.РАСП() , английское название – F.DIST(), которая позволяет вычислить плотность вероятности (см. формулу выше) и интегральную функцию распределения (вероятность, что случайная величина Х, имеющая F — распределение , примет значение меньше или равное х, P(X Примечание : Плотность вероятности можно также вычислить впрямую, с помощью формул (см. файл примера ).
До MS EXCEL 2010 в EXCEL была функция FРАСП() , которая позволяет вычислить функцию распределения (точнее — правостороннюю вероятность, т.е. P(X>x)). Функция FРАСП() оставлена в MS EXCEL 2010 для совместимости. Аналогом FРАСП() является функция F.РАСП.ПХ() , появившаяся в MS EXCEL 2010.
Примеры расчетов приведены в файле примера на листе Функции .
В MS EXCEL имеется еще одна функция, использующая для расчетов F-распределение – это F.ТЕСТ(массив1;массив2) . Эта функция возвращает результат F-теста : двухстороннюю вероятность того, что разница между дисперсиями выборок «массив1» и «массив2» несущественна. Предполагается, что выборки делаются из нормального распределения .
Обратная функция F-распределения
Обратная функция используется для вычисления альфа — квантилей , т.е. для вычисления значений x при заданной вероятности альфа , причем х должен удовлетворять выражению P Функция F.ОБР.ПХ() используется для вычисления верхнего квантиля . Т.е. если в качестве аргумента функции указан уровень значимости, например 0,05, то функция вернет такое значение случайной величины х, для которого P(X>x)=0,05. В качестве сравнения: функция F.ОБР() вернет такое значение случайной величины х, для которого P(X F.ОБР.ПХ() использовалась функция FРАСПОБР() . Вышеуказанные функции можно взаимозаменять, т.к. следующие формулы возвращают одинаковый результат: =F.ОБР(0,05;k1;k2) =F.ОБР.ПХ(1-0,05;k1;k2) = FРАСПОБР (1-0,05;k1;k2) СОВЕТ : О других распределениях MS EXCEL можно прочитать в статье Распределения случайной величины в MS EXCEL . Функция ФИШЕР выполняет возвращение преобразования Фишера для аргументов X . Это преобразование строит функцию, которая имеет нормальное, а не асимметричное распределение. Используется функция ФИШЕР для того чтобы проверить гипотезу с помощью коэффициента корреляции. При работе с данной функцией необходимо задать значение переменной. Сразу стоит отметить, что существуют некоторые ситуации, при которых данная функция не будет выдавать результатов. Это возможно, если переменная: Уравнение, которое используется для математического описания функции ФИШЕР, имеет вид: Рассмотрим применение данной функции на 3-x конкретных примерах. Пример 1. Используя данные об активности коммерческих организаций, требуется сделать оценку связи прибыли Y (млн руб.) и затрат X (млн руб.), используемых для разработки продукции (приведены в таблице 1). Таблица 1 – Исходные данные: Схема решения таких задач выглядит следующим образом: Результаты решения данной задачи с применяемыми функциями в пакете Excel приведены на рисунке 1. Рисунок 1 – Пример расчетов. Таким образом, с вероятностью 0,95 линейный коэффициент корреляции заключен в интервале от (–0,386) до (–0,990) со стандартной ошибкой 0,205. Пример 2. Произвести проверку статистической значимости уравнения множественной регрессии с помощью F-критерия Фишера, сделать выводы. Для проверки значимости уравнения в целом выдвинем гипотезу Н о статистической незначимости коэффициента детерминации и противоположную ей гипотезу Н1 о статистической значимости коэффициента детерминации: Проверим гипотезы с помощью F-критерия Фишера. Показатели приведены в таблице 2. Таблица 2 – Исходные данные Для этого используем в пакете Excel функцию: Зная, что α = 0,05, p = 2 и n = 53, получаем следующее значение для Fкрит (см. рисунок 2). Рисунок 2 – Пример расчетов. Таким образом можно сказать, что Fрасч > Fкрит. В итоге принимается гипотеза Н1 о статистической значимости коэффициента детерминации. Пример 3. Используя данные 23 предприятий о: X — цена на товар А, тыс. руб.; Y — прибыль торгового предприятия, млн. руб, производится изучение их зависимости. Оценка регрессионной модели дала следующее: ∑(yi-yx) 2 = 50000; ∑(yi-yср) 2 = 130000. Какой показатель корреляции можно определить по этим данным? Рассчитайте величину показателя корреляции и, используя критерий Фишера, сделайте вывод о качестве модели регрессии. Определим Fкрит из выражения: где R – коэффициент детерминации, равный 0,67. Таким образом, расчетное значение Fрасч = 46. Для определения Fкрит используем распределение Фишера (см. рисунок 3). Рисунок 3 – Пример расчетов. Таким образом, полученная оценка уравнения регрессии надежна. F — критерий Фишераиспользуют для сравнения дисперсий двух генеральных совокупностей, распределенных по нормальному закону. По независимым выборкам объема из этих совокупностей найдены выборочные дисперсии где Число степеней свободы числителя определяется по формуле: где n1— число вариант для большей дисперсии. Число степеней свободы знаменателя определяется по формуле: где n2 — число вариант для меньшей дисперсии. Если При проведении тестирования двух одинаковых приборов были проведены измерения эталона. При этом первым прибором было проведено n1=11 измерений, а вторым — n2=9. Результаты были записаны в виде отклонений от значения эталона. Требуется выяснить: одинаковой ли точностью обладают приборы. Величина отклонений от эталонного значения для первого прибора (n1=11) внесена в столбец В,а для второго прибора (n2=9) результаты — в столбец С (рис.4.4-4.5). Средние значения отклонений одинаковы и равны нулю. Следовательно, у приборов отсутствует систематическая ошибка. Проверка точности приборов сводится к проверке совпадения дисперсий. Если дисперсии отклонений от эталонного значения статистически равны, то приборы обладают одинаковой точностью. Выдвигается гипотеза H — дисперсии выборок равны, альтернативная гипотезаH1— дисперсии не равны. В результате расчета были получены соответственно следующие значения дисперсий: Значение критерия Для уровня значимости α =0.05; числа степеней свободы числителяr1 =11-1=10 и числа степеней свободы знаменателяr2 = 9-1= 8 находим с помощью встроенной функции FРАСПОБР().Fкрит= 3.347. Поскольку Рис. 4.4 Сравнение двух выборочных дисперсий (фрагмент рабочего листа MSExcelв режиме отображения данных) Рис. 4.5. Сравнение двух выборочных дисперсий (фрагмент рабочего листа MSExcelв режиме отображений формул) Средство анализа «Двухвыборочный F-тест для дисперсии» надстройки «Пакет анализа»MSExcelслужит для проверки гипотезы о равенстве дисперсий двух выборок. Для проверки необходимо заполнить диалоговое окно, приведенное на рис.4.6, назначение всех полей ввода очевидно. Рис. 4.6 Диалоговое окно средства анализа «Двухвыборочный F-тест для дисперсии» надстройки «Пакет анализа»MSExcel Результаты расчета представлены на рис.4.7. Сравните полученные результаты с результатами, полученными вручную. Рис. 4.7 «Двухвыборочный F-тест для дисперсии» Ниже приведено условие задачи и текстовая часть решения. Закачка полного решения, файлы word+Excel в архиве rar, начнется автоматически через 10 секунд. Если закачка не началась, кликните по этой ссылке. Видеоурок по решению этой задачи в Excel вы можете посмотреть здесь. По предложенным вам экспериментальным данным, представляющим собою макроэкономические показатели или показатели финансовой (денежно-кредитной) системы некоторой страны, т.е. случайной выборке объема n – построить математическую модель зависимости случайной величины Y от случайных величин X1 и X2. Построение и оценку качества экономико-математической (эконометрической) модели вести в следующей последовательности: В таблице 1.1. приведены е же квартальные данные о валовом внутреннем продукте (млн. евро) ; экспорта товаров и услуг (млн. евро ) ; эффективный обменный курс евро к национальной волюте для Испании на период с 2000 по 2007 годы. Еж еквартальные данные о валовом внутреннем продукте, экспорте товаров и услуг , эффективном обменном курсе евро к национальной валюте для И сландии на период с 2000 по 2007 годыФункция ФИШЕР в Excel и примеры ее работы
Описание работы функции ФИШЕР в Excel
Оценка взаимосвязи прибыли и затрат по функции ФИШЕР
№ 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) Проверка статистической значимости регрессии по функции FРАСПОБР
Показатель SS MS Fрасч Регрессия 454,814 227,407 7,075 Остаток 1607,014 32,14 Итого 2061,828 — Расчет величины показателя корреляции в Excel
4.2. Критерий Фишера
и
. Выдвигается гипотезаH — дисперсии равны, альтернативная гипотезаH1— дисперсии не равны. Вычисляется
по формуле:
,
— большая дисперсия,
— меньшая дисперсия. По заданному уровню значимости α и числам степеней свободы
и
(
число степеней свободы числителя и
число степеней свободы знаменателя) — определяем
по таблицам или используя встроенные функцииMSExcel.
,
,
(вычисленное значение критерия
не больше критического), то принимается гипотезаH(дисперсии равны), в противном случае (
) принимается гипотезаH1 (дисперсии различны).
=7.35 и
=2.188.
=7.35 /2.188 = 3.36.
то гипотезаH отклоняется, и принимается альтернативная гипотезаH1 (дисперсии различны). Следовательно, приборы имеют различную точность.
Средство анализа «Двухвыборочный f-тест для дисперсии» надстройки «Пакет анализа» ms Excel
Пример решения эконометрической задачи в Excel
•Построить корреляционную матрицу для случайных величин и оценить статистическую значимость корреляции между ними.
•Исходя из наличия между эндогенной переменной и экзогенными переменными, линейной зависимости, оценить параметры регрессионной модели по методу наименьших квадратов. Вычислите вектора регрессионных значений эндогенной переменной и случайных отклонений.
•Найдите средние квадратические ошибки коэффициентов регрессии. Используя критерий Стьюдента проверьте статистическую значимость параметров модели. Здесь и далее принять уровень значимости 0,05(т. е. надежность 95%).
•Вычислите эмпирический коэффициент детерминации и скорректированный коэффициент детерминации. Проверьте, используя критерий Фишера, адекватность линейной модели.
•Установите наличие (отсутствие) автокорреляции случайных отклонений модели. Используйте для этого метод графического анализа, статистику Дарбина-Уотсона и критерий Бреуша-Годфри.
•Установите наличие (отсутствие) гетероскедастичности случайных отклонений модели. Используйте для этого графический анализ, тест Вайта и тест Парка для вариантов с добавочным индексом А (графический метод, тест Глейзера и тест Бреуша-Пагана для вариантов с добавочным индексом В).
•Обобщите результаты оценивания параметров модели и результаты проверки модели на адекватность.