Как посчитать критерий фишера в excel

Задача №3. Расчёт параметров регрессии и корреляции с помощью Excel

1. Постройте поле корреляции и сформулируйте гипотезу о форме связи.

2. Рассчитайте параметры уравнения линейной регрессии

.

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

4. Дайте с помощью среднего (общего) коэффициента эластичности сравнительную оценку силы связи фактора с результатом.

5. Оцените с помощью средней ошибки аппроксимации качество уравнений.

6. Оцените с помощью F-критерия Фишера статистическую надёжность результатов регрессионного моделирования.

7. Рассчитайте прогнозное значение результата, если прогнозное значение фактора увеличится на 10% от его среднего уровня. Определите доверительный интервал прогноза для уровня значимости .

8. Оцените полученные результаты, выводы оформите в аналитической записке.

Решение:

Решим данную задачу с помощью Excel.

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

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

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

Затем выберете: Вставка / Точечная диаграмма / Точечная с маркерами как показано на рисунке 1.

Рисунок 1 Построение поля корреляции

Анализ поля корреляции показывает наличие близкой к прямолинейной зависимости, так как точки расположены практически по прямой линии.

2. Для расчёта параметров уравнения линейной регрессии
воспользуемся встроенной статистической функцией ЛИНЕЙН.

1) Откройте существующий файл, содержащий анализируемые данные;
2) Выделите область пустых ячеек 5×2 (5 строк, 2 столбца) для вывода результатов регрессионной статистики.
3) Активизируйте Мастер функций: в главном меню выберете Формулы / Вставить функцию.
4) В окне Категория выберете Статистические, в окне функция – ЛИНЕЙН. Щёлкните по кнопке ОК как показано на Рисунке 2;

Рисунок 2 Диалоговое окно «Мастер функций»

5) Заполните аргументы функции:

Известные значения у – диапазон, содержащий данные результативного признака;

Известные значения х – диапазон, содержащий данные факторного признака;

Константа – логическое значение, которое указывает на наличие или на отсутствие свободного члена в уравнении; если Константа = 1, то свободный член рассчитывается обычным образом, если Константа = 0, то свободный член равен 0;

Статистика – логическое значение, которое указывает, выводить дополнительную информацию по регрессионному анализу или нет. Если Статистика = 1, то дополнительная информация выводится, если Статистика = 0, то выводятся только оценки параметров уравнения.

Щёлкните по кнопке ОК;

Рисунок 3 Диалоговое окно аргументов функции ЛИНЕЙН

6) В левой верхней ячейке выделенной области появится первый элемент итоговой таблицы. Чтобы раскрыть всю таблицу, нажмите на клавишу <F2>, а затем на комбинацию клавиш <Ctrl>+<Shift>+<Enter>.

Дополнительная регрессионная статистика будет выводиться в порядке, указанном в следующей схеме:

Значение коэффициента b	Значение коэффициента a
Стандартная ошибка b	Стандартная ошибка a
Коэффициент детерминации R 2	Стандартная ошибка y
F-статистика	Число степеней свободы df
Регрессионная сумма квадратов

Рисунок 4 Результат вычисления функции ЛИНЕЙН

Получили уровнение регрессии:

Делаем вывод: С увеличением среднедушевого прожиточного минимума на 1 руб. среднедневная заработная плата возрастает в среднем на 0,92 руб.

3. Коэффициент детерминации означает, что 52% вариации заработной платы (у) объясняется вариацией фактора х – среднедушевого прожиточного минимума, а 48% — действием других факторов, не включённых в модель.

По вычисленному коэффициенту детерминации можно рассчитать коэффициент корреляции: .

Связь оценивается как тесная.

4. С помощью среднего (общего) коэффициента эластичности определим силу влияния фактора на результат.

Для уравнения прямой средний (общий) коэффициент эластичности определим по формуле:

Средние значения найдём, выделив область ячеек со значениями х, и выберем Формулы / Автосумма / Среднее, и то же самое произведём со значениями у.

Рисунок 5 Расчёт средних значений функции и аргумент

Таким образом, при изменении среднедушевого прожиточного минимума на 1% от своего среднего значения среднедневная заработная плата изменится в среднем на 0,51%.

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

Порядок действий следующий:

1) проверьте доступ к Пакету анализа. В главном меню последовательно выберите: Файл/Параметры/Надстройки.

2) В раскрывающемся списке Управление выберите пункт Надстройки Excel и нажмите кнопку Перейти.

3) В окне Надстройки установите флажок Пакет анализа, а затем нажмите кнопку ОК.

• Если Пакет анализа отсутствует в списке поля Доступные надстройки, нажмите кнопку Обзор, чтобы выполнить поиск.

• Если выводится сообщение о том, что пакет анализа не установлен на компьютере, нажмите кнопку Да, чтобы установить его.

4) В главном меню последовательно выберите: Данные / Анализ данных / Инструменты анализа / Регрессия, а затем нажмите кнопку ОК.

5) Заполните диалоговое окно ввода данных и параметров вывода:

Входной интервал Y – диапазон, содержащий данные результативного признака;

Входной интервал X – диапазон, содержащий данные факторного признака;

Метки – флажок, который указывает, содержит ли первая строка названия столбцов или нет;

Константа – ноль – флажок, указывающий на наличие или отсутствие свободного члена в уравнении;

Выходной интервал – достаточно указать левую верхнюю ячейку будущего диапазона;

6) Новый рабочий лист – можно задать произвольное имя нового листа.

Затем нажмите кнопку ОК.

Рисунок 6 Диалоговое окно ввода параметров инструмента Регрессия

Результаты регрессионного анализа для данных задачи представлены на рисунке 7.

Рисунок 7 Результат применения инструмента регрессия

5. Оценим с помощью средней ошибки аппроксимации качество уравнений. Воспользуемся результатами регрессионного анализа представленного на Рисунке 8.

Рисунок 8 Результат применения инструмента регрессия «Вывод остатка»

Составим новую таблицу как показано на рисунке 9. В графе С рассчитаем относительную ошибку аппроксимации по формуле:

Рисунок 9 Расчёт средней ошибки аппроксимации

Средняя ошибка аппроксимации рассчитывается по формуле:

Качество построенной модели оценивается как хорошее, так как не превышает 8 – 10%.

6. Из таблицы с регрессионной статистикой (Рисунок 4) выпишем фактическое значение F-критерия Фишера:

Поскольку при 5%-ном уровне значимости, то можно сделать вывод о значимости уравнения регрессии (связь доказана).

8. Оценку статистической значимости параметров регрессии проведём с помощью t-статистики Стьюдента и путём расчёта доверительного интервала каждого из показателей.

Выдвигаем гипотезу Н₀ о статистически незначимом отличии показателей от нуля:

.

для числа степеней свободы

На рисунке 7 имеются фактические значения t-статистики:

t-критерий для коэффициента корреляции можно рассчитать двумя способами:

I способ:

где – случайная ошибка коэффициента корреляции.

Данные для расчёта возьмём из таблицы на Рисунке 7.

II способ:

Фактические значения t-статистики превосходят табличные значения:

Поэтому гипотеза Н₀ отклоняется, то есть параметры регрессии и коэффициент корреляции не случайно отличаются от нуля, а статистически значимы.

Доверительный интервал для параметра a определяется как

Для параметра a 95%-ные границы как показано на рисунке 7 составили:

Доверительный интервал для коэффициента регрессии определяется как

Для коэффициента регрессии b 95%-ные границы как показано на рисунке 7 составили:

Анализ верхней и нижней границ доверительных интервалов приводит к выводу о том, что с вероятностью параметры a и b, находясь в указанных границах, не принимают нулевых значений, т.е. не являются статистически незначимыми и существенно отличны от нуля.

7. Полученные оценки уравнения регрессии позволяют использовать его для прогноза. Если прогнозное значение прожиточного минимума составит:

Тогда прогнозное значение прожиточного минимума составит:

Ошибку прогноза рассчитаем по формуле:

где

Дисперсию посчитаем также с помощью ППП Excel. Для этого:

1) Активизируйте Мастер функций: в главном меню выберете Формулы / Вставить функцию.

2) В окне Категория выберете Статистические, в окне функция – ДИСП.Г. Щёлкните по кнопке ОК.

3) Заполните диапазон, содержащий числовые данные факторного признака. Нажмите ОК.

Рисунок 10 Расчёт дисперсии

Получили значение дисперсии

Для подсчёта остаточной дисперсии на одну степень свободы воспользуемся результатами дисперсионного анализа как показано на Рисунке 7.

Доверительные интервалы прогноза индивидуальных значений у при с вероятностью 0,95 определяются выражением:

Интервал достаточно широк, прежде всего, за счёт малого объёма наблюдений. В целом выполненный прогноз среднемесячной заработной платы оказался надёжным.

Условие задачи взято из: Практикум по эконометрике: Учеб. пособие / И.И. Елисеева, С.В. Курышева, Н.М. Гордеенко и др.; Под ред. И.И. Елисеевой. – М.: Финансы и статистика, 2003. – 192 с.: ил.

Критерий фишера в excel что это

Точный критерий Фишера используется для определения того, существует ли значительная связь между двумя категориальными переменными. Обычно он используется в качестве альтернативы критерию независимости хи-квадрат, когда количество одной или нескольких ячеек в таблице 2 × 2 меньше 5.

В этом руководстве объясняется, как выполнить точный критерий Фишера в Excel.

Пример: точный критерий Фишера в Excel

Предположим, мы хотим знать, связан ли пол с предпочтениями политической партии в конкретном колледже. Чтобы изучить это, мы случайным образом опрашиваем 25 студентов в кампусе. Количество студентов, которые являются демократами или республиканцами, в зависимости от пола, показано в таблице ниже:

Чтобы определить, существует ли статистически значимая связь между полом и предпочтениями политической партии, мы можем выполнить точный тест Фишера.

Хотя в Excel нет встроенной функции для выполнения этого теста, мы можем использовать гипергеометрическую функцию для выполнения теста, которая использует следующий синтаксис:

=HYPGEOM.DIST(выборка_s, число_выборка, совокупность_s, число_население, кумулятивный)

• sample_s = количество «успехов» в образце
• number_sample = размер выборки
• населения_s = количество «успехов» в популяции
• number_pop = численность населения
• cumulative = если TRUE, возвращает кумулятивную функцию распределения; если FALSE, это возвращает функцию массы вероятности. Для наших целей мы всегда будем использовать TRUE.

Чтобы применить эту функцию к нашему примеру, мы выберем для использования одну из четырех ячеек в таблице 2×2. Подойдет любая ячейка, но в этом примере мы будем использовать верхнюю левую ячейку со значением «4».

Далее мы заполним следующие значения для функции:

= HYPGEOM.DIST (значение в отдельной ячейке, общее количество столбцов, общее количество строк, общий размер выборки, TRUE)

Это дает одностороннее p-значение 0,0812 .

Чтобы найти двустороннее p-значение для теста, мы сложим вместе следующие две вероятности:

• Вероятность получения x «успехов» в интересующей нас ячейке. В нашем случае это вероятность получения 4 успехов (мы уже нашли эту вероятность равной 0,0812).
• 1 — вероятность попадания (общее количество столбцов — х «успехов») в интересующую нас ячейку. В этом случае общее количество столбцов для демократа равно 12, поэтому мы найдем 1 — (вероятность 8 « успехов»)

Вот формула, которую мы будем использовать:

Это дает двустороннее p-значение 0,1152 .

В любом случае, проводим ли мы односторонний или двусторонний тест, p-значение не меньше 0,05, поэтому мы не можем отвергнуть нулевую гипотезу. Другими словами, у нас нет достаточных доказательств, чтобы сказать, что существует значительная связь между полом и предпочтениями политических партий.

FРАСПОБР для проверки значимости модели регрессии в Excel

Функция FПАСПОБР в Excel используется для проверки значимости модели регрессии с применением F-критерия (критерий Фишера), и возвращает числовое значение, соответствующее обратному значению для F-распределения вероятностей (верхнему квантилю). Например, если в качестве вероятности (первый аргумент функции) было введено значение уровня значимости, к примеру, 0,08, то FПАСПОБР вычислит значение случайной величины x, для которой выполняется следующее условие – P(X>x) = 0,08.

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

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

Для расчета F используется следующая формула:

Функция оперирует двумя дополнительными критериями:

1. Числитель степеней свободы: n1 = k.
2. Знаменатель степеней свободы: n2 = (n – k – 1).

Через переменную k обозначают число факторов, которые были включены в исследуемую модель регрессии.

В Excel предусмотрена функция для расчета вероятности для распределения Фишера – FРАСП. Между данной и рассматриваемой функциями существует следующая взаимосвязь: =FРАСПОБР(FРАСП(x;n1;n2);n1;n2)=x.

В MS Office 2007 и более поздних версиях была введена функция F.ОБР.ПХ, которая заменила рассматриваемую функцию. FПАСПОБР была оставлена для обеспечения совместимости с документами, созданными в более старых версиях Excel.

Определение верхнего квартиля F-распределения Фишера в Excel

Пример 1. В таблице указаны вероятность, связанная с распределением Фишера, а также числитель и знаменатель степеней свободы соответственно. Определить верхний квантиль данного F-распределения.

Вид таблицы данных:

Вычислим искомое значение с помощью функции:

Оценка в Excel эффективности использования технологий на производстве

Пример 2. На заводе есть несколько цехов по производству одного типа продукции. Существует 3 различные технологии изготовления данной продукции. Для оценки были записаны данные о количестве часов, необходимых для производства одной партии продукции каждым цехом с использованием каждой из трех технологий. Оценить эффективность использования технологий, проанализировать полученные значения.

Вид таблицы данных:

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

По условия поставленной задачи нас интересует выделенное значение. Поскольку оно

Здесь СЧЁТЗ(B3:D3) определяет число полей данных, а СЧЁТЗ(B3:D7) – количество исследуемых числовых значений.

Особенности использования функции FРАСПОБР в Excel

Функция имеет следующую синтаксическую запись:

• вероятность – обязательный, принимает числовое значение, характеризующее вероятность, которая связана с распределением Фишера;
• степени_свободы1 – обязательный, принимает числовое значение, соответствующее числителю степеней свободы (равно числу факторов исследуемой регрессии);
• степени_свободы2 – обязательный, принимает числовое значение, соответствующее знаменателю степеней свободы.

1. Рассматриваемая функция принимает в качестве любого из аргументов только числовые значения и данные, которые могут быть преобразованы к числам. Если любой из аргументов принимает данные недопустимого типа, будет сгенерирован код ошибки #ЗНАЧ!
2. Первый аргумент должен быть задан числом из диапазона от 0 до 1. В противном случае функция FПАСПОБР вернет код ошибки #ЧИСЛО!
3. Второй и третий аргумент функции должны быть заданы числами из диапазона от 1 до 10^10. При вводе значений, находящихся вне допустимого диапазона, будет сгенерирован код ошибки #ЧИСЛО!
4. Рассматриваемая функция использует итеративный подход к вычислениям (последовательный подбор приближенного значения в циклах). Если спустя 100 итераций решение не было найдено, результатом выполнения функции FПАСПОБР будет код ошибки #Н/Д.

4.2. Критерий Фишера

F — критерий Фишераиспользуют для сравнения дисперсий двух генеральных совокупностей, распределенных по нормальному закону.

По независимым выборкам объема из этих совокупностей найдены выборочные дисперсии и. Выдвигается гипотезаH₀ — дисперсии равны, альтернативная гипотезаH₁— дисперсии не равны. Вычисляетсяпо формуле:

,

где — большая дисперсия,— меньшая дисперсия. По заданному уровню значимости α и числам степеней свободыи(число степеней свободы числителя ичисло степеней свободы знаменателя) — определяемпо таблицам или используя встроенные функцииMSExcel.

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

,

где n₁— число вариант для большей дисперсии.

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

,

где n₂ — число вариант для меньшей дисперсии.

Если (вычисленное значение критерия не больше критического), то принимается гипотезаH₀(дисперсии равны), в противном случае () принимается гипотезаH₁ (дисперсии различны).

При проведении тестирования двух одинаковых приборов были проведены измерения эталона. При этом первым прибором было проведено n₁=11 измерений, а вторым — n₂=9.

Результаты были записаны в виде отклонений от значения эталона. Требуется выяснить: одинаковой ли точностью обладают приборы.

Величина отклонений от эталонного значения для первого прибора (n₁=11) внесена в столбец В,а для второго прибора (n₂=9) результаты — в столбец С (рис.4.4-4.5). Средние значения отклонений одинаковы и равны нулю. Следовательно, у приборов отсутствует систематическая ошибка.

Проверка точности приборов сводится к проверке совпадения дисперсий. Если дисперсии отклонений от эталонного значения статистически равны, то приборы обладают одинаковой точностью. Выдвигается гипотеза H₀ — дисперсии выборок равны, альтернативная гипотезаH₁— дисперсии не равны.

В результате расчета были получены соответственно следующие значения дисперсий: =7.35 и=2.188.

Значение критерия =7.35 /2.188 = 3.36.

Для уровня значимости α =0.05; числа степеней свободы числителяr₁ =11-1=10 и числа степеней свободы знаменателяr₂ = 9-1= 8 находим с помощью встроенной функции FРАСПОБР().F_крит= 3.347.

Поскольку то гипотезаH₀ отклоняется, и принимается альтернативная гипотезаH₁ (дисперсии различны). Следовательно, приборы имеют различную точность.

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

(фрагмент рабочего листа MSExcelв режиме отображения данных)

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

(фрагмент рабочего листа MSExcelв режиме отображений формул)

Средство анализа «Двухвыборочный f-тест для дисперсии» надстройки «Пакет анализа» ms Excel

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

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

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

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

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

17 авг. 2022 г.
читать 2 мин

Точный критерий Фишера используется для определения того, существует ли значительная связь между двумя категориальными переменными. Обычно он используется в качестве альтернативы критерию независимости хи-квадрат, когда количество одной или нескольких ячеек в таблице 2 × 2 меньше 5.

В этом руководстве объясняется, как выполнить точный критерий Фишера в Excel.

Пример: точный критерий Фишера в Excel

Предположим, мы хотим знать, связан ли пол с предпочтениями политической партии в конкретном колледже. Чтобы изучить это, мы случайным образом опрашиваем 25 студентов в кампусе. Количество студентов, которые являются демократами или республиканцами, в зависимости от пола, показано в таблице ниже:

Чтобы определить, существует ли статистически значимая связь между полом и предпочтениями политической партии, мы можем выполнить точный тест Фишера.

Хотя в Excel нет встроенной функции для выполнения этого теста, мы можем использовать гипергеометрическую функцию для выполнения теста, которая использует следующий синтаксис:

=HYPGEOM.DIST(выборка_s, число_выборка, совокупность_s, число_население, кумулятивный)

• sample_s = количество «успехов» в образце
• number_sample = размер выборки
• населения_s = количество «успехов» в популяции
• number_pop = численность населения
• cumulative = если TRUE, возвращает кумулятивную функцию распределения; если FALSE, это возвращает функцию массы вероятности. Для наших целей мы всегда будем использовать TRUE.

Чтобы применить эту функцию к нашему примеру, мы выберем для использования одну из четырех ячеек в таблице 2×2. Подойдет любая ячейка, но в этом примере мы будем использовать верхнюю левую ячейку со значением «4».

Далее мы заполним следующие значения для функции:

= HYPGEOM.DIST (значение в отдельной ячейке, общее количество столбцов, общее количество строк, общий размер выборки, TRUE)

Это дает одностороннее p-значение 0,0812 .

Чтобы найти двустороннее p-значение для теста, мы сложим вместе следующие две вероятности:

• Вероятность получения x «успехов» в интересующей нас ячейке. В нашем случае это вероятность получения 4 успехов (мы уже нашли эту вероятность равной 0,0812).
• 1 — вероятность попадания (общее количество столбцов — х «успехов») в интересующую нас ячейку. В этом случае общее количество столбцов для демократа равно 12, поэтому мы найдем 1 — (вероятность 8 « успехов»)

Вот формула, которую мы будем использовать:

Это дает двустороннее p-значение 0,1152 .

В любом случае, проводим ли мы односторонний или двусторонний тест, p-значение не меньше 0,05, поэтому мы не можем отвергнуть нулевую гипотезу. Другими словами, у нас нет достаточных доказательств, чтобы сказать, что существует значительная связь между полом и предпочтениями политических партий.

Дополнительные ресурсы

Как выполнить тест независимости хи-квадрат в Excel
Как выполнить критерий согласия хи-квадрат в Excel
Как рассчитать V Крамера в Excel

Распределение Фишера (F-распределение). Распределения математической статистики в MS EXCEL

​Смотрите также​ детерминации, равный 0,67.​крит​0​Левая интервальная оценка для​ значимости линейного коэффициента​2​ при которых данная​

​ выделите их и​ #ЧИСЛО!.​ эта функция все​ листа Excel. Чтобы​ MS EXCEL можно​ функции см. статью​ распределения (вероятность, что​

​>2, дисперсия равна 2*k​​ распределения и Плотности​_{​Рассмотрим распределение Фишера (F-распределение).​}​Таким образом, расчетное значение​_{​(см. рисунок 2).​}​: R2 = 0;​ z​ корреляции, а следовательно,​_{​1 068 000 000,00​}​ функция не будет​_{​ нажмите клавишу F2,​}​Если «степени_свободы2» < 1​ еще используется для​

​ отобразить результаты формул,​ прочитать в статье Распределения​_{​ про проверку гипотез​}​ случайная величина Х,​_​2​​ вероятности см. статью Функция​

​ С помощью функции​

​ F​

​Рисунок 2 – Пример​Н​

​=C12-C11*КОРЕНЬ(1/(6-3))​​ и о статистической​ ₽​ выдавать результатов. Это​ а затем —​ или «степени_свободы2» ≥​ обеспечения обратной совместимости,​

​ выделите их и​ случайной величины в​

​ о равенстве двух​ имеющая F-распределение, примет​_​2*(k​​ распределения и плотность​_{​ MS EXCEL F.РАСП()​}​расч​_{​ расчетов.​}​1​_​7​​ существенности зависимости между​76 000 000,00 ₽​_{​ возможно, если переменная:​}​ клавишу ВВОД. При​_{​ 10^10, функция FРАСПОБР​}​ она может стать​_{​ нажмите клавишу F2,​}​ MS EXCEL.​_{​ дисперсий.​}​ значение меньше или​1​

​ вероятности в MS​ построим графики функции​= 46.​Таким образом можно сказать,​: R2 ≠ 0.​

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

​Правая интервальная оценка для​ Х и Y;​

​3​_{​не является числом. В​}​ необходимости измените ширину​_{​ возвращает значение ошибки​}​ недоступной в последующих​_{​ а затем —​}​В этой статье описаны​_{​Обратная функция используется для​}​ равное х, P(X​_​+k​​ EXCEL.​_{​ распределения и плотности​}​Для определения F​_{​ что F​}​Проверим гипотезы с помощью​_{​ z​}​Определяется интервальная оценка для​_{​1 005 000 000,00​}​ такой ситуации функция​_{​ столбцов, чтобы видеть​}​ #ЧИСЛО!.​

​ версиях Excel, поэтому​ клавишу ВВОД. При​ синтаксис формулы и​ вычисления альфа-квантилей, т.е.​Примечание:​

​2​​Приведем пример случайной величины,​ вероятности, поясним применение​крит​расч​ F-критерия Фишера. Показатели​=C12+C11*КОРЕНЬ(1/(6-3))​ статистически значимого линейного​ ₽​ ФИШЕР осуществит возвращение​ все данные.​

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

​Функцию FРАСПОБР можно использовать​ мы рекомендуем использовать​ необходимости измените ширину​ использование функции​ для вычисления значений​Плотность вероятности можно​-2)/(k​ имеющей F-распределение.​ этого распределения для​используем распределение Фишера​> F​ приведены в таблице​8​ коэффициента корреляции.​

​78 000 000,00 ₽​​ значения ошибки #ЗНАЧ!;​Данные​ для определения критических​ новые функции.​

​ столбцов, чтобы видеть​ФИШЕР​ x при заданной​ также вычислить впрямую,​1​Пусть имеется 2 нормальных​ целей математической статистики.​ (см. рисунок 3).​крит​ 2.​Левая интервальная оценка для​Определяется интервальная оценка для​4​

​имеет значение либо меньше​Описание​ значений F-распределения. Например,​

​Чтобы узнать больше о​ все данные.​в Microsoft Excel.​ вероятности альфа, причем​ с помощью формул​*(k​ распределения N(μ​F-распределение (англ. F-distribution) применяется​Рисунок 3 – Пример​. В итоге принимается​Таблица 2 – Исходные​ rxy​ линейного коэффициента корреляции​

​610 000 000,00 ₽​ -1, либо больше​0,01​ результаты дисперсионного анализа​ новых функциях, см.​

Обратная функция F-распределения

​Формула​Возвращает преобразование Фишера для​ х должен удовлетворять​ (см. файл примера).​2​1​ для целей дисперсионного​

​ расчетов.​ гипотеза Н​ данные​

​=ФИШЕРОБР(C13)​ на основе обратного​89 000 000,00 ₽​ 1. В данном​Вероятность, связанная с интегральным​ обычно включают данные​ статьи Функция F.ОБР​Описание​ аргумента x. Это​ выражению P

​Таким образом, полученная оценка​1​Показатель​9​

​ z-преобразования Фишера;​5​ случае функция ФИШЕР​
​ F-распределением​
​ для F-статистики, F-вероятности​
​ и Функция F.ОБР.ПХ.​

​Результат​​ преобразование строит функцию,​В MS EXCEL обратная​ в EXCEL была​2​1​

ФИШЕР (функция ФИШЕР)

​ проверке гипотезы о​ уравнения регрессии надежна.​о статистической значимости​​SS​​Правая интервальная оценка для​

Описание

​Рассчитывается стандартная ошибка линейного​768 000 000,00 ₽​ возвратит значение ошибки​6​ и критическое значение​FРАСПОБР(вероятность;степени_свободы1;степени_свободы2)​=ФИШЕР(0,75)​ которая имеет нормальное,​ функция реализована с​

Синтаксис

​ функция FРАСП(), которая​

​ равенстве дисперсий двух​​sdr​ коэффициента детерминации.​MS​

Замечания

​ rxy​ коэффициента корреляции.​77 000 000,00 ₽​ #ЧИСЛО!.​

​Числитель степеней свободы​ F-распределения с уровнем​Аргументы функции FРАСПОБР описаны​Преобразование Фишера для аргумента​ а не асимметричное​

​ помощью функции F.ОБР().​ позволяет вычислить функцию​

Пример

​2​2​ нормальных распределений (F-тест)​: В Экселе есть​Пример 3. Используя данные​F​=ФИШЕРОБР(C14)​Результаты решения данной задачи​6​Уравнение, которое используется для​4​ значимости 0,05. Чтобы​ ниже.​

​ распределение. Данная функция​

​Функция F.ОБР.ПХ() используется для​

FРАСПОБР (функция FРАСПОБР)

​ раздел «Анализ данных»,​ 23 предприятий о:​расч​10​ с применяемыми функциями​

​799 000 000,00 ₽​ математического описания функции​Знаменатель степеней свободы​ определить критическое значение​Вероятность​0,9729551​ используется для проверки​ вычисления верхнего квантиля.​ правостороннюю вероятность, т.е.​В файле примера на​2​

​Определение​​ где можно произвести​ X — цена​Регрессия​Стандартное отклонение для rxy​ в пакете Excel​85 000 000,00 ₽​ ФИШЕР, имеет вид:​Формула​ F, нужно использовать​ — обязательный аргумент.​Возвращает значение, обратное (правостороннему)​ гипотез с помощью​ Т.е. если в​ P(X>x)). Функция FРАСП()​ листе График приведены​

​), из которых сделаны​: Если U​ математическую статистику. Мне​ на товар А,​

Синтаксис

​=КОРЕНЬ((1-C8^2)/4)​ приведены на рисунке​

​Схема решения таких задач​​Z’=1/2*ln(1+x)/(1-x)​Описание​ уровень значимости как​

​ Вероятность, связанная с​​ F-распределению вероятностей. Если​ коэффициента корреляции.​

​ качестве аргумента функции​​ оставлена в MS​ графики плотности распределения​

Замечания

​ выборки размером n​1​ нужно расчитать критерий​ тыс. руб.; Y​

​227,407​Таким образом, с вероятностью​ 1.​

​ выглядит следующим образом:​Рассмотрим применение данной функции​Результат​ аргумент «вероятность» функции​

​ интегральным F-распределением.​ p = FРАСП(x;…),​ФИШЕР(x)​ указан уровень значимости,​ EXCEL 2010 для​

​ вероятности и интегральной​1​и U​ Фишера. Его можно​ — прибыль торгового​

​7,075​ 0,95 линейный коэффициент​Рисунок 1 – Пример​Рассчитывается линейный коэффициент корреляции​ на 3-x конкретных​=FРАСПОБР(A2;A3;A4)​ FРАСПОБР.​Степени_свободы1​ то FРАСПОБР(p;…) =​Аргументы функции ФИШЕР описаны​ например 0,05, то​ совместимости. Аналогом FРАСП()​ функции распределения.​и n​

​2​ вычислить двумя способами.​ предприятия, млн. руб,​Остаток​ корреляции заключен в​ расчетов.​ r​ примерах.​Значение, обратное F-распределению вероятностей​По заданному значению вероятности​ — обязательный аргумент.​ x.​ ниже.​ функция вернет такое​

Пример

​ является функция F.РАСП.ПХ(),​Примечание​2​независимые случайные величины,​ Есть раздел «Регрессия»​ производится изучение их​1607,014​ интервале от (–0,386)​№ п/п​xy​​ для приведенных выше​ функция FРАСПОБР ищет​

​ Числитель степеней свободы.​

​F-распределение может использоваться в​

​ значение случайной величины​ появившаяся в MS​

​: Для построения функции​

​ имеющие ХИ2-распределение с​

​ и «Однофакторный дисперсионный​

​ зависимости. Оценка регрессионной​

​ до (–0,990) со​

​;​Пример 1. Используя данные​ данных​

Функция ФИШЕР в Excel и примеры ее работы

​Степени_свободы2​ F-тесте, который сравнивает​ — обязательный аргумент. Числовое​ х, для которого​ EXCEL 2010.​ распределения и плотности​1​ k​ анализ». Причем разница​ модели дала следующее:​Итого​

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

​ стандартной ошибкой 0,205.​Формула расчета​Проверяется значимость линейного коэффициента​ об активности коммерческих​15,206865​ которого FРАСП(x;степени_свободы1;степени_свободы2) =​ — обязательный аргумент.​ степени разброса двух​ значение, для которого​

• ​ P(X>x)=0,05. В качестве​Примеры расчетов приведены в​ вероятности можно использовать​2 и s​
• ​1​ в вычислениях кординальная.​ ∑(yi-yx)2 = 50000;​2061,828​Пример 2. Произвести проверку​1​

​ корреляции на основе​ организаций, требуется сделать​Функция ФИШЕР выполняет возвращение​

​ вероятность. Таким образом,​

​ Знаменатель степеней свободы.​ множеств данных. Например,​ необходимо получить преобразование.​

​ сравнения: функция F.ОБР()​

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

​ файле примера на​ диаграмму типа График​2​и k​ В разделе Регрессия​ ∑(yi-yср)2 = 130000.​-​ статистической значимости уравнения​Коэффициент корреляции​ t-критерия Стьюдента. При​

​ оценку связи прибыли​ преобразования Фишера для​

​ точность функции FРАСПОБР​	​Если какой-либо из аргументов​	​ можно проанализировать распределение​
​Если x не является​	​ вернет такое значение​	​ листе Функции.​
​ или Точечная (со​	​2 – дисперсии этих выборок,​2​	​ коэф-т вычисляется по​
​ Какой показатель корреляции​	​Для этого используем в​ множественной регрессии с​	​=КОРРЕЛ(B2:B7;C2:C7)​
​ этом выдвигается и​	​ Y (млн руб.)​	​ аргументов X. Это​
​ зависит от точности​	​ не является числом,​	​ доходов в США​
​ числом, функция ФИШЕР​	​ случайной величины х,​	​В MS EXCEL имеется​

​ сглаженными линиями и​ то отношение​

1. ​ степенями свободы соответственно, то​ формуле F=R/(1-R), какой​_{​ можно определить по​}​ пакете Excel функцию:​
2. ​ помощью F-критерия Фишера,​2​ проверяется гипотеза о​ и затрат X​ преобразование строит функцию,​ FРАСП. Для поиска​ функция FРАСПОБР возвращает​ и Канаде, чтобы​ возвращает значение ошибки​ для которого P(X​ еще одна функция,​ без точек). Подробнее​_{​имеет F-распределение. Это соотношение нам​}​ распределение случайной величины:​_{​ из критериев правильный?​}​ этим данным? Рассчитайте​=FРАСПОБР (α;p;n-p-1)​ сделать выводы.​Расчетное значение t-критерия tp​ равенстве коэффициента корреляции​ (млн руб.), используемых​ которая имеет нормальное,​
3. ​ функция FРАСПОБР использует​ значение ошибки #ЗНАЧ!.​ определить, похожи ли​
4. ​ #ЗНАЧ!.​В MS EXCEL 2007​ использующая для расчетов​ о построении диаграмм​
5. ​ потребуется при проверке​носит название F-распределения с​

​Serge​ величину показателя корреляции​где:​Для проверки значимости уравнения​=ABS(C8)/КОРЕНЬ(1-СТЕПЕНЬ(C8;2))*КОРЕНЬ(6-2)​

​ нулю. При проверке​ для разработки продукции​

​ а не асимметричное​	​ метод итераций. Если​	​Если «вероятность» 1, функция​
​ эти две страны​	​Если x ≤ -1​	​ и ранее вместо​
​ F-распределение – это​	​ читайте статью Основные​	​ гипотезы о равенстве​
​ параметрами k​	​: А так не​	​ и, используя критерий​
​α – вероятность, связанная​	​ в целом выдвинем​3​	​ этой гипотезы используется​
​ (приведены в таблице​	​ распределение. Используется функция​	​ поиск не закончился​
​ FРАСПОБР возвращает значение​	​ по степени плотности​ или x ≥​	​ F.ОБР.ПХ() использовалась функция​
​ F.ТЕСТ(массив1;массив2). Эта функция​	​ типы диаграмм.​ дисперсий двух нормальных​	​1​
​ пойдёт: =ФИШЕР(A1)?​	​ Фишера, сделайте вывод​ с данным распределением;​	​ гипотезу Н​
​Табличное значение t-критерия trh​	​ t-статистика. Если гипотеза​ 1).​	​ ФИШЕР для того​
​ после 100 итераций,​	​ ошибки #ЧИСЛО!.​	​ доходов.​

​ 1, функция ФИШЕР​ FРАСПОБР().​ возвращает результат F-теста:​В MS EXCEL, начиная​ распределений (F-тест).​ и k​

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

​Guest​ о качестве модели​p и n –​0​=СТЬЮДРАСПОБР(0,05;4)​

​ подтверждается, t-статистика имеет​Таблица 1 – Исходные​ чтобы проверить гипотезу​_{​ возвращается значение ошибки​}​Если значение аргумента «степени_свободы1″​Важно:​ возвращает значение ошибки​Вышеуказанные функции можно взаимозаменять,​_{​ двухстороннюю вероятность того,​}​ с версии 2010,​F-распределение при небольших параметрах​

​2​_{​: Нет, это фигня​}​ регрессии.​

​ числитель и знаменатель​_{​о статистической незначимости​}​4​

​ распределение Стьюдента. Если​ данные:​ с помощью коэффициента​ #Н/Д.​

​ или «степени_свободы2» не​ Эта функция была заменена​

​ #ЧИСЛО!.​	​ т.к. следующие формулы​	​ что разница между​	​ для F-распределения имеется​​ (​
​.​	​ какая то​	​Определим F​	​ степеней свободы, соответственно.​
​ коэффициента детерминации и​	​Табличное значение стандартного нормального​	​ расчетное значение t​
​№​	​ корреляции.​	​Скопируйте образец данных из​

​ является целым числом,​ одной или несколькими​

​Уравнение для преобразования Фишера​

​ возвращают одинаковый результат:​

• ​ дисперсиями выборок «массив1″​ специальная функция F.РАСП(),​
• ​Среднее значение равно k​Плотность F-распределения выражается формулой:​Софья​

​крит​Зная, что α =​ противоположную ей гипотезу​ распределения zy​р​X​_{​При работе с данной​}​ следующей таблицы и​

​ оно усекается.​ новыми функциями, которые​

​ имеет следующий вид:​=F.ОБР(0,05;k1;k2)​_{​ и «массив2» несущественна.​}​ английское название –​_​2​​где Г(…) – гамма-функция:​: =FРАСПОБР​_{​из выражения:​}​ 0,05, p =​ Н​

Расчет величины показателя корреляции в Excel

​=НОРМСТОБР((0,95+1)/2)​> t​Y​ функцией необходимо задать​ вставьте их в​Если «степени_свободы1» < 1​ обеспечивают более высокую​Скопируйте образец данных из​=F.ОБР.ПХ(1-0,05;k1;k2)​ Предполагается, что выборки​ F.DIST(), которая позволяет​/(k​если альфа – положительное​На сколько я​F​ 2 и n​1​5​кр​1​

​ значение переменной. Сразу​_{​ ячейку A1 нового​}​ или «степени_свободы1» ≥​

​ точность и имеют​_{​ следующей таблицы и​}​= FРАСПОБР (1-0,05;k1;k2)​

​ делаются из нормального​ вычислить плотность вероятности​

​2​ целое, то Г(альфа)=(альфа-1)!​_{​ знаю, критерий Фишера​}​расч​

​ = 53, получаем​_{​о статистической значимости​}​Значение преобразования Фишера z’​, то гипотеза отвергается,​

​210 000 000,00 ₽​ стоит отметить, что​

​ листа Excel. Чтобы​ 10^10, функция FРАСПОБР​

Расчет критерия Фишера

​ имена, лучше отражающие​​ вставьте их в​СОВЕТ​ распределения.​ (см. формулу выше)​-2) при k​СОВЕТ​ можно вычислить этой​= R2/23*(1-R2)​ следующее значение для​ коэффициента детерминации:​=ФИШЕР(C8)​ что свидетельствует о​95 000 000,00 ₽​ существуют некоторые ситуации,​ отобразить результаты формул,​

​ возвращает значение ошибки​​ их назначение. Хотя​ ячейку A1 нового​

​: О других распределениях​​Подробнее об использовании этой​ и интегральную функцию​

​2​​: Подробнее о Функции​
​ функцией​где R – коэффициент​ F​Н​

Функция ФИШЕР выполняет возвращение преобразования Фишера для аргументов X . Это преобразование строит функцию, которая имеет нормальное, а не асимметричное распределение. Используется функция ФИШЕР для того чтобы проверить гипотезу с помощью коэффициента корреляции.

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

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

• не является числом. В такой ситуации функция ФИШЕР осуществит возвращение значения ошибки #ЗНАЧ!;
• имеет значение либо меньше -1, либо больше 1. В данном случае функция ФИШЕР возвратит значение ошибки #ЧИСЛО!.

Уравнение, которое используется для математического описания функции ФИШЕР, имеет вид:

Рассмотрим применение данной функции на 3-x конкретных примерах.

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

Пример 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. Рассчитывается линейный коэффициент корреляции r_xy;
2. Проверяется значимость линейного коэффициента корреляции на основе t-критерия Стьюдента. При этом выдвигается и проверяется гипотеза о равенстве коэффициента корреляции нулю. При проверке этой гипотезы используется t-статистика. Если гипотеза подтверждается, t-статистика имеет распределение Стьюдента. Если расчетное значение t_р > t_кр, то гипотеза отвергается, что свидетельствует о значимости линейного коэффициента корреляции, а следовательно, и о статистической существенности зависимости между Х и Y;
3. Определяется интервальная оценка для статистически значимого линейного коэффициента корреляции.
4. Определяется интервальная оценка для линейного коэффициента корреляции на основе обратного z-преобразования Фишера;
5. Рассчитывается стандартная ошибка линейного коэффициента корреляции.

Результаты решения данной задачи с применяемыми функциями в пакете Excel приведены на рисунке 1.

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

№ п/п	Наименование показателя	Формула расчета
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 – Исходные данные

Показатель	SS	MS	Fрасч
Регрессия	454,814	227,407	7,075
Остаток	1607,014	32,14
Итого	2061,828	—

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

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

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

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

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

Расчет величины показателя корреляции в Excel

Пример 3. Используя данные 23 предприятий о: X — цена на товар А, тыс. руб.; Y — прибыль торгового предприятия, млн. руб, производится изучение их зависимости. Оценка регрессионной модели дала следующее: ∑(yi-yx) 2 = 50000; ∑(yi-yср) 2 = 130000. Какой показатель корреляции можно определить по этим данным? Рассчитайте величину показателя корреляции и, используя критерий Фишера, сделайте вывод о качестве модели регрессии.

Определим F_крит из выражения:

где R – коэффициент детерминации, равный 0,67.

Таким образом, расчетное значение F_расч = 46.

Для определения F_крит используем распределение Фишера (см. рисунок 3).

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

Скачать примеры работы функции ФИШЕР в Excel

Таким образом, полученная оценка уравнения регрессии надежна.

F — критерий Фишераиспользуют для
сравнения дисперсий двух генеральных
совокупностей, распределенных по
нормальному закону.

По независимым выборкам объема из этих
совокупностей найдены выборочные
дисперсии
и.
Выдвигается гипотезаH₀
— дисперсии равны, альтернативная
гипотезаH₁— дисперсии не равны. Вычисляетсяпо формуле:

,

где
— большая дисперсия,— меньшая дисперсия. По заданному уровню
значимости α и числам степеней свободыи(число степеней свободы числителя ичисло степеней свободы знаменателя) —
определяемпо таблицам или используя встроенные
функцииMSExcel.

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

,

где n₁— число
вариант для большей дисперсии.

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

,

где n₂ — число
вариант для меньшей дисперсии.

Если
(вычисленное
значение критерия
не больше
критического), то принимается гипотезаH₀(дисперсии
равны), в противном случае ()
принимается гипотезаH₁
(дисперсии различны).

При проведении тестирования двух
одинаковых приборов были проведены
измерения эталона. При этом первым
прибором было проведено n₁=11 измерений, а вторым — n₂=9.

Результаты были записаны в виде отклонений
от значения эталона. Требуется выяснить:
одинаковой ли точностью обладают
приборы.

Величина отклонений от эталонного
значения для первого прибора (n₁=11) внесена в столбец В,а для второго
прибора (n₂=9)
результаты — в столбец С (рис.4.4-4.5). Средние
значения отклонений одинаковы и равны
нулю. Следовательно, у приборов отсутствует
систематическая ошибка.

Проверка точности приборов сводится к
проверке совпадения дисперсий. Если
дисперсии отклонений от эталонного
значения статистически равны, то приборы
обладают одинаковой точностью. Выдвигается
гипотеза H₀
— дисперсии выборок равны, альтернативная
гипотезаH₁— дисперсии не равны.

В результате расчета были получены
соответственно следующие значения
дисперсий:
=7.35 и=2.188.

Значение критерия
=7.35 /2.188 = 3.36.

Для уровня значимости α =0.05; числа
степеней свободы числителяr₁ =11-1=10
и числа степеней свободы знаменателяr₂ = 9-1= 8
находим с помощью встроенной
функции FРАСПОБР().F_крит= 3.347.

Поскольку
то гипотезаH₀
отклоняется, и принимается альтернативная
гипотезаH₁
(дисперсии различны). Следовательно,
приборы имеют различную точность.

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

(фрагмент
рабочего листа MSExcelв режиме отображения данных)

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

(фрагмент
рабочего листа MSExcelв режиме отображений формул)

Средство анализа «Двухвыборочный f-тест для дисперсии» надстройки «Пакет анализа» ms Excel

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

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

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

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

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

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

Соседние файлы в папке Эконометрика 1 лекция

• #
• #
• #
• #
• #
• #

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше

В этой статье описаны синтаксис формулы и использование функции ФИШЕР в Microsoft Excel.

Описание

Возвращает преобразование Фишера для аргумента x. Это преобразование строит функцию, которая имеет нормальное, а не асимметричное распределение. Данная функция используется для проверки гипотез с помощью коэффициента корреляции.

Синтаксис

Аргументы функции ФИШЕР описаны ниже.

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

Замечания

Если x не является числом, фишер возвращает #VALUE! значение ошибки #ЗНАЧ!.

Если x ≤ -1 или x ≥ 1, фишер возвращает #NUM! значение ошибки #ЗНАЧ!.

Уравнение для преобразования Фишера имеет следующий вид:

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Преобразование Фишера для аргумента 0,75

Нужна дополнительная помощь?

Назначение.
Проверка гипотезы о принадлежности двух дисперсий одной генеральной совокупности и следовательно — их равенстве.

Нулевая гипотеза.
S 2 2 = S 1 2

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

1. S 1 2 > S 2 2 . Наиболее часто используемый вариант Н А. Критическая область — верхний хвост F-распределения.

2. S 1 2 < S 2 2 . Критическая область — нижний хвост F-распределения. Ввиду частого отсутствия нижнего хвоста, в таблицах критическую область обычно сводят к варианту 1, меняя местами дисперсии.

3. Двухсторонняя S 1 2 ≠S 2 2 .Комбинация первых двух.

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

F P = S 1 2 /S 2 2

Примечание. При описываемом способе проверки значение Fpaсч обязательно должно быть больше единицы. Критерий чувствителен к нарушению предположения о нормальности.

Для двухсторонней альтернативы S 1 2 ≠S 2 2 нулевая гипотеза принимается при выполнении условия:

F l — α /2 < Fрасч < F α /2

Комплексным теплометрическим методом определяли теплофизические. характеристики (ТФХ) зеленого солода. Для приготовления образцов брали воздушно-сухой (средняя влажность W=19%) и влажный солод четырехсуточного ращения (W=45%) в соответствии новой технологией приготовления карамельного солода. Опыты показали, что теплопроводность λ влажного солода примерно в 2,5 раза больше, чем сухого, а объемная теплоемкость не имеет четкой зависимости от влажности солода. Поэтому с помощью F-критерия проверили возможность обобщить данные по средним значениям без учета влажности

Расчетные данные сведены в таблицу 5.1

Данные к расчету F-критерия

Большее значение дисперсии получено для W=45%, т.е. S 2 45 = S 1 2 , S 2 19 = S 2 2 , и F P = S 1 2 /S 2 2 =1,35. Из таблицы 5.2 для степени свободы f 1 =N 1 -1=5 f 2 =N 2 -1=4 при γ=0,95 определяем F КР =6,2. Нуль гипотеза сформулированная как «В диапазоне влажности зеленого солода от 19 до 45% ее влиянием на объемную теплоемкость можно пренебречь» или «S 2 45 = S 2 19 » с доверительной вероятностью 95% подтвердилась, поскольку Fp

Пример проверки гипотезы о принадлежности двух дисперсий одной генеральной совокупности по критерию Фишера с помощью Excel

Приведены данные по двум независимым выборкам (табл. 5.2) степени водопоглощения зерна пшеницы Было проведено исследование воздействия магнитными полями низкой частоты.

Номер	Номер выборки
опыта	2 ,
0,027	0,075
0,036	0,4
0,1	0,08
0,12	0,105
0,32	0,075
0,45	0,12
0,049	0,06
0,105	0,075

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

На рис. 5.1 приведен пример проверки гипотезы о принадлежности двух дисперсий одной генеральной совокупности по критерию Фишера используя программный продукт Microsoft Excel.

Рисунок 5.1 Пример проверки принадлежности двух дисперсий одной генеральной совокупности по критерию Фишера

Исходные данные размещены в ячейках, находящихся на пересечении столбцов С и D со строками 3-10. Выполним следующие действия.

1. Определим, можно ли считать закон распределения первой и второй выборок нормальным (столбцы С и D соответственно). Если нет (хотя бы для одной выборки), то необходимо использовать непараметрический критерий, если да – продолжаем.

2. Рассчитаем дисперсии для первого и вто­рого столбца. Для этого в ячейках СП и D11 поместим функции =ДИСП(СЗ:С10) и =ДИСП(DЗ:D10) соответственно. Результатом работы этих функций является рассчитанное значение дисперсии для каждого столбца соответственно.

3. Находим расчетное значение для критерия Фишера. Для этого нужно большую дисперсию разделить на меньшую. В ячейку F13 помещаем формулу =C11/D11, которая и выполняет эту операцию.

4. Определяем, можно ли принять гипотезу о равенстве дисперсий. Существует два способа, которые представлены в примере. По пер­вому способу, задавшись уровнем значимос­ти, например 0,05, вычисляют критическое значение распределения Фишера для этого значения и соответствующего числа степеней свободы. В ячейку F14 вводится функция =FPACПOBP(0,05;7;7) (где 0,05 — заданный уровень значимости; 7 — число степеней свободы числителя, а 7 (второе) — число степеней свободы знаменателя). Число степеней свободы равно числу экспериментов минус единица. Результат — 3,787051. Поскольку это значение больше расчетного 1,81144, мы должны принять нулевую гипотезу о равенстве дисперсий.

По второму варианту рассчитывают для полученного расчетного значения критерия Фишера соответствующую вероятность. Для этого в ячейку F15 вводится функ­ция =FPACП(F13;7;7). Поскольку полученное значение 0,22566 больше, чем 0,05, то принимается гипотеза о равенстве дисперсий.

Это может быть выполнено специальной функцией. Выберите в меню последовательно пункты Сервис

, Анализ данных

. Появится окно следующего вида (рис. 5.2).

Рисунок 5.2 Окно выбора метода обработки

В этом окне выбираете «Двухвыборочный F-mecm для дисперсий

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

Задавайте все необходимые параметры и нажимайте ОК. Результат работы приведен на рис. 5.4

Следует отметить, что функция проверяет односторонний критерий и делает это правильно. Для случая, когда критериальное значение больше 1, вычисляется верхнее критическое значение.

Рисунок 5.3 Окно задания параметров

Когда критериальное значение меньше 1, то вычисляется нижнее критическое.

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

Рисунок 5.4 Проверка равенства дисперсий

На данном примере рассмотрим, как оценивается надежность полученного уравнение регрессии. Этот же тест используется для проверки гипотезы о том, что коэффициенты регрессии одновременно равны нулю, a=0 , b=0 . Другими словами, суть расчетов — ответить на вопрос: можно ли его использовать для дальнейшего анализа и прогнозов?

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

Итак, целью анализа является получение некоторой оценки, с помощью которой можно было бы утверждать, что при некотором уровне α полученное уравнение регрессии — статистически надежно. Для этого используется коэффициент детерминации R 2
.

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

Если расчетное значение с k 1 =(m) и k 2 =(n-m-1) степенями свободы больше табличного при заданном уровне значимости, то модель считается значимой.

где m – число факторов в модели.

Оценка статистической значимости парной линейной регрессии производится по следующему алгоритму:

1. Выдвигается нулевая гипотеза о том, что уравнение в целом статистически незначимо: H 0: R 2 =0 на уровне значимости α.

2. Далее определяют фактическое значение F-критерия:

где m=1 для парной регрессии.

3. Табличное значение определяется по таблицам распределения Фишера для заданного уровня значимости, принимая во внимание, что число степеней свободы для общей суммы квадратов (большей дисперсии) равно 1 и число степеней свободы остаточной суммы квадратов (меньшей дисперсии) при линейной регрессии равно n-2 (или через функцию Excel FРАСПОБР(вероятность;1;n-2)).

F табл — это максимально возможное значение критерия под влиянием случайных факторов при данных степенях свободы и уровне значимости α. Уровень значимости α — вероятность отвергнуть правильную гипотезу при условии, что она верна. Обычно α принимается равной 0,05 или 0,01.

4. Если фактическое значение F-критерия меньше табличного, то говорят, что нет основания отклонять нулевую гипотезу.

В противном случае, нулевая гипотеза отклоняется и с вероятностью (1-α) принимается альтернативная гипотеза о статистической значимости уравнения в целом.

Табличное значение критерия со степенями свободы k 1 =1 и k 2 =48, F табл = 4

Выводы
: Поскольку фактическое значение F > F табл, то коэффициент детерминации статистически значим (найденная оценка уравнения регрессии статистически надежна
)
.

Дисперсионный анализ

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

Пример
. По совокупности 25 предприятий торговли изучается зависимость между признаками: X — цена на товар А, тыс. руб.; Y — прибыль торгового предприятия, млн. руб. При оценке регрессионной модели были получены следующие промежуточные результаты: ∑(y i -y x) 2 = 46000; ∑(y i -y ср) 2 = 138000. Какой показатель корреляции можно определить по этим данным? Рассчитайте величину этого показателя, на основе этого результата и с помощью F-критерия Фишера
сделайте вывод о качестве модели регрессии.

Решение. По этим данным можно определить эмпирическое корреляционное отношение : , где ∑(y ср -y x) 2 = ∑(y i -y ср) 2 — ∑(y i -y x) 2 = 138000 — 46000 = 92 000.

η 2 = 92 000/138000 = 0.67, η = 0.816 (0.7 < η < 0.9 — связь между X и Y высокая).

F-критерий Фишера
: n = 25, m = 1.

R 2 = 1 — 46000/138000 = 0.67, F = 0.67/(1-0.67)x(25 — 1 — 1) = 46. F табл (1; 23) = 4.27

Поскольку фактическое значение F > Fтабл, то найденная оценка уравнения регрессии статистически надежна.

Вопрос: Какую статистику используют для проверки значимости модели регрессии?

Ответ: Для значимости всей модели в целом используют F-статистику (критерий Фишера).

Функция ФИШЕР выполняет возвращение преобразования Фишера для аргументов X
. Это преобразование строит функцию, которая имеет нормальное, а не асимметричное распределение. Используется функция ФИШЕР для того чтобы проверить гипотезу с помощью коэффициента корреляции.

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

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

• не является числом. В такой ситуации функция ФИШЕР осуществит возвращение значения ошибки #ЗНАЧ!;
• имеет значение либо меньше -1, либо больше 1. В данном случае функция ФИШЕР возвратит значение ошибки #ЧИСЛО!.

Уравнение, которое используется для математического описания функции ФИШЕР, имеет вид:

Рассмотрим применение данной функции на 3-x конкретных примерах.

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

Пример 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. Рассчитывается линейный коэффициент корреляции r xy ;
2. Проверяется значимость линейного коэффициента корреляции на основе t-критерия Стьюдента. При этом выдвигается и проверяется гипотеза о равенстве коэффициента корреляции нулю. При проверке этой гипотезы используется t-статистика. Если гипотеза подтверждается, t-статистика имеет распределение Стьюдента. Если расчетное значение t р > t кр, то гипотеза отвергается, что свидетельствует о значимости линейного коэффициента корреляции, а следовательно, и о статистической существенности зависимости между Х и Y;
3. Определяется интервальная оценка для статистически значимого линейного коэффициента корреляции.
4. Определяется интервальная оценка для линейного коэффициента корреляции на основе обратного z-преобразования Фишера;
5. Рассчитывается стандартная ошибка линейного коэффициента корреляции.

Результаты решения данной задачи с применяемыми функциями в пакете Excel приведены на рисунке 1.

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

№ п/п	Наименование показателя	Формула расчета
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-критерия Фишера, сделать выводы.

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

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

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

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

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

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

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

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

Расчет величины показателя корреляции в Excel

Пример 3.
Используя данные 23 предприятий о: X — цена на товар А, тыс. руб.; Y — прибыль торгового предприятия, млн. руб, производится изучение их зависимости. Оценка регрессионной модели дала следующее: ∑(yi-yx) 2 = 50000; ∑(yi-yср) 2 = 130000. Какой показатель корреляции можно определить по этим данным? Рассчитайте величину показателя корреляции и, используя критерий Фишера, сделайте вывод о качестве модели регрессии.

Определим F крит из выражения:

F расч = R 2 /23*(1-R 2)

где R – коэффициент детерминации, равный 0,67.

Таким образом, расчетное значение F расч = 46.

Для определения F крит используем распределение Фишера (см. рисунок 3).

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

Таким образом, полученная оценка уравнения регрессии надежна.

​ Точный критерий Фишера – это критерий, который используется для сравнения двух относительных показателей, характеризующих частоту определенного признака, имеющего два значения. Исходные данные для расчета точного критерия Фишера обычно группируются в виде четырехпольной таблицы.

1. История разработки критерия

Впервые критерий был предложен Рональдом Фишером
в его книге «Проектирование экспериментов». Это произошло в 1935 году. Сам Фишер утверждал, что на эту мысль его натолкнула Муриэль Бристоль. В начале 1920-х годов Рональд, Муриэль и Уильям Роуч находились в Англии на опытной сельскохозяйственной станции. Муриэль утверждала, что может определить, в какой последовательности наливали в ее чашку чай и молоко. На тот момент проверить правильность ее высказывания не представлялось возможным.

Это дало толчок идее Фишера о «нуль гипотезе». Целью стала не попытка доказать, что Муриэль может определить разницу между по-разному приготовленными чашками чая. Решено было опровергнуть гипотезу, что выбор женщина делает наугад. Было определено, что нуль-гипотезу нельзя ни доказать, ни обосновать. Зато ее можно опровергнуть во время экспериментов.

Было приготовлено 8 чашек. В первые четыре налито молоко сначала, в другие четыре – чай. Чашки были помешаны. Бристоль предложили опробовать чай на вкус и разделить чашки по методу приготовления чая. В результате должно было получиться две группы. История говорит, что эксперимент прошел удачно.

Благодаря тесту Фишера вероятность того, что Бристоль действует интуитивно, была уменьшена до 0.01428. То есть, верно определить чашку можно было в одном случае из 70. Но все же нет возможности свести к нулю шансы того, что мадам определяет случайно. Даже если увеличивать число чашек.

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

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

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

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

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

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

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

4. Как рассчитать точный критерий Фишера?

Допустим, изучается зависимость частоты рождения детей с врожденными пороками развития (ВПР) от курения матери во время беременности. Для этого выбраны две группы беременных женщин, одна из которых — экспериментальная, состоящая из 80 женщин, куривших в первом триместре беременности, а вторая — группа сравнения, включающая 90 женщин, ведущих здоровый образ жизни на протяжении всей беременности. Число случаев ВПР плода, установленных по данным УЗИ в экспериментальной группе, составило 10, в группе сравнения — 2.

Вначале составляем четырехпольную таблицу сопряженности
:

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

где N — общее число исследуемых в двух группах; ! — факториал, представляющий собой произведение числа на последовательность чисел, каждое из которых меньше предыдущего на 1 (например, 4! = 4 · 3 · 2 · 1)

В результате вычислений находим, что P = 0,0137.

5. Как интерпретировать значение точного критерия Фишера?

Достоинством метода является соответствие полученного критерия точному значению уровня значимости p
. То есть, полученное в нашем примере значение 0,0137 и есть уровень значимости различий сравниваемых групп по частоте развития ВПР плода. Необходимо лишь сопоставить данное число с критическим уровнем значимости, обычно принимаемым в медицинских исследованиях за 0,05.

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

В нашем примере P < 0,05, в связи с чем делаем вывод о наличии прямой взаимосвязи курения и вероятности развития ВПР плода. Частота возникновения врожденной патологии у детей курящих женщин статистически значимо выше
, чем у некурящих.

1. Таблица значений F-критерия Фишера для уровня значимости α = 0.05

1	2	3	4	5	6	8	12	24	∞
1	161,45	199,50	215,72	224,57	230,17	233,97	238,89	243,91	249,04	254,32
2	18,51	19,00	19,16	19,25	19,30	19,33	19,37	19,41	19,45	19,50
3	10,13	9,55	9,28	9,12	9,01	8,94	8,84	8,74	8,64	8,53
4	7,71	6,94	6,59	6,39	6,26	6,16	6,04	5,91	5,77	5,63
5	6,61	5,79	5,41	5, 19	5,05	4,95	4,82	4,68	4,53	4,36
6	5,99	5,14	4,76	4,53	4,39	4,28	4,15	4,00	3,84	3,67
7	5,59	4,74	4,35	4,12	3,97	3,87	3,73	3,57	3,41	3,23
8	5,32	4,46	4,07	3,84	3,69	3,58	3,44	3,28	3,12	2,93
9	5,12	4,26	3,86	3,63	3,48	3,37	3,23	3,07	2,90	2,71
10	4,96	4,10	3,71	3,48	3,33	3,22	3,07	2,91	2,74	2,54
11	4,84	3,98	3,59	3,36	3, 20	2,95	2,79	2,61	2,40

Когда m=1, выбираем 1 столбец.

k 2 =n-m=7-1=6 — т.е.6-я строка — берем табличное значение Фишера

F табл =5.99, у ср. = итого: 7

Влияние х на у — умеренное и отрицательное

ŷ — модельное значение.

F расч. =	28,648: 1	= 0,92
200,50: 5

А = 1/7 * 398,15 * 100% = 8,1% < 10% —

Модель достаточно точная.

F расч. = 1/0,92 =1,6

F расч. = 1,6 < F табл. = 5,99

Должно быть F расч. > F табл

Нарушается данная модель, поэтому данное уравнение статистически не значимо.

Так как расчетное значение меньше табличного — незначимая модель.

1	Σ	(y — ŷ)	*100%
N	y

A= 1/7*0,563494* 100% = 8,04991% 8,0%

Считаем, что модель точная, если средняя ошибка аппроксимации менее 10%.

Параметрическая идентификация парной нелинейной регрессии

Модель у = а * х b — степенная функция

Чтобы применить известную формулу, необходимо логарифмировать нелинейную модель.

log у = log a + b log x

Y=C+b*X -линейная модель.

С = 1,7605 — (- 0,298) * 1,7370 = 2,278

Возврат к исходной модели

Ŷ=10 с *x b =10 2.278 *x -0.298

№п/п	У	X	Y	X	Y*X	У	I (y-ŷ) /yI
1	68,80	45,10	1,8376	1,6542	3,039758	2,736378	60,9614643	0,113932
2	61, 20	59,00	1,7868	1,7709	3,164244	3,136087	56,2711901	0,080536
3	59,90	57, 20	1,7774	1,7574	3,123603	3,088455	56,7931534	0,051867
4	56,70	61,80	1,7536	1,7910	3,140698	3, 207681	55,4990353	0,021181
5	55,00	58,80	1,7404	1,7694	3,079464	3,130776	56,3281590	0,024148
6	54,30	47, 20	1,7348	1,6739	2,903882	2,801941	60,1402577	0,107555
7	49,30	55, 20	1,6928	1,7419	2,948688	3,034216	57,3987130	0,164274
Итого	405, 20	384,30	12,3234	12,1587	21,40034	21,13553	403,391973	0,563493
Средняя	57,88571	54,90	1,760486	1,736957	3,057191	3,019362	57,62742	0,080499

Входим в EXCEL через «Пуск»-программы. Заносим данные в таблицу. В «Сервис» — «Анализ данных» — «Регрессия» — ОК

Если в меню «Сервис» отсутствует строка «Анализ данных», то ее необходимо установить через «Сервис» — «Настройки» — «Пакет анализа данных»

Прогнозирование спроса на продукцию предприятия. Использование в MS Excel функции «Тенденция»

A — спрос на товар. B — время, дни

№ п/п	A
1	11	1
2	14	2
3	13	3
4	15	4
5	17	5
6	17,9
7	18,4	7

Шаг 1. Подготовка исходных данных

Шаг 2. Продлеваем временную ось, ставим на 6,7 вперед; имеем право прогнозировать на 1/3 от данных.

Шаг 3. Выделим диапазон A6: A7 под будущий прогноз.

Шаг 4. Вставка функция

Вставка диаграмма нестандартны
гладкие графики

диапазон у готово.

Если каждое последующее значение нашего временной оси будет отличаться не на несколько процентов, а в несколько раз, тогда нужно использовать не функцию «Тенденция», а функцию «Рост».

1. Елисеева «Эконометрика»

2. Елисеева «Практикум по эконометрике»

3. Карлсберг «Excel для цели анализа»

Несколько уравнений, а в каждом уравнении — несколько переменных. Задача оценивания параметров такой разветвленной модели решается с помощью сложных и причудливых методов. Однако все они имеют одну и ту же теоретическую основу. Поэтому для получения начального представления о содержании эконометрических методов мы ограничимся в последующих параграфах рассмотрением простой линейной регрессии. …

Что только что проведенное сравнение ранжировок (1) и (2) осуществлено не вполне строго. Ясно, что в эконометрическом инструментарии специалиста по проведению экспертных исследований должен быть алгоритм согласования ранжировок, полученных различными методами. Метод согласования кластеризованных ранжировок Рассматриваемая здесь проблема состоит в выделении общего нестрогого порядка из набора…

Осуществляется подстановкой в уравнение регрессии значений независимых переменных, которые определяют условия, для которых делается прогноз. 2.2 Методы планирования и прогнозирования доходов бюджетов органов местного самоуправления Методы прогнозирования и планирования выражаются в способах и приемах разработки прогнозных и плановых документов и показателей применительно к различным их видам…

1. Назначение и описание критерия Фишера
2. Гипотезы критерия Фишера
3. Графики функций
4. F-распределение в MS EXCEL
5. Оценка взаимосвязи прибыли и затрат по функции ФИШЕР
6. Проверка статистической значимости регрессии по функции FРАСПОБР
7. Таблицы по нахождению критерия Фишера и Стьюдента
8. Критерии Стьюдента
9. Порядок расчета критерия φ*
10. Расчет в программе Excel
11. Показатели качества уравнения регрессии
12. Для чего используется точный критерий Фишера?
13. В каких случаях можно использовать точный критерий Фишера?
14. Критические точки распределения Фишера

Назначение и описание критерия Фишера

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

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

Суть углового преобразования Фишера состоит в переводе процентных долей в величины центрального угла , который измеряется в радианах. Большей процентной доле будет соответствовать больший угол φ, а меньшей доле – меньший угол, но соотношения здесь не линейные: φ = 2*arcsin(), где P – процентная доля, выраженная в долях единицы.

При увеличении расхождения между углами φ1 и φ2 и увеличения численности выборок значение критерия возрастает. Чем больше величина φ*, тем более вероятно, что различия достоверны.

Гипотезы критерия Фишера

H₀: Доля лиц, у которых проявляется исследуемый эффект, в выборке 1 не больше, чем в выборке 2.

H₁: Доля лиц, у которых проявляется исследуемый эффект, в выборке 1 больше, чем в выборке 2.

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

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” несущественна. Предполагается, что выборки делаются из нормального распределения.

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

Пример 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. Рассчитывается линейный коэффициент корреляции r_xy
   
2. Проверяется значимость линейного коэффициента корреляции на основе t-критерия Стьюдента. При этом выдвигается и проверяется гипотеза о равенстве коэффициента корреляции нулю. При проверке этой гипотезы используется t-статистика. Если гипотеза подтверждается, t-статистика имеет распределение Стьюдента. Если расчетное значение t_р > t_кр, то гипотеза отвергается, что свидетельствует о значимости линейного коэффициента корреляции, а следовательно, и о статистической существенности зависимости между Х и Y;
3. Определяется интервальная оценка для статистически значимого линейного коэффициента корреляции.
4. Определяется интервальная оценка для линейного коэффициента корреляции на основе обратного z-преобразования Фишера;
5. Рассчитывается стандартная ошибка линейного коэффициента корреляции.

Результаты решения данной задачи с применяемыми функциями в пакете Excel приведены на рисунке 1.

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

№ п/п	Наименование показателя	Формула расчета
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.

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

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

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

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

Показатель	SS	MS	Fрасч
Регрессия	454,814	227,407	7,075
Остаток	1607,014	32,14
Итого	2061,828	–

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

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

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

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

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

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

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

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

1. Определяют k1, которое равно количеству факторов (Х). Например, в однофакторной модели (модели парной регрессии) k1=1, в двухфакторной k=2.
2. Определяют k2, которое определяется по формуле n — m — 1, где n — число наблюдений, m — количество факторов. Например, в однофакторной модели k2 = n — 2.
3. На пересечении столбца 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

Пример . По совокупности 25 предприятий торговли изучается зависимость между признаками: X — цена на товар А, тыс. руб.; Y — прибыль торгового предприятия, млн. руб. При оценке регрессионной модели были получены следующие промежуточные результаты: ∑(y_i-y_x) 2 = 46000; ∑(y_i-y_ср) 2 = 138000. Какой показатель корреляции можно определить по этим данным? Рассчитайте величину этого показателя, на основе этого результата и с помощью F-критерия Фишера сделайте вывод о качестве модели регрессии.
Решение. По этим данным можно определить эмпирическое корреляционное отношение : , где ∑(y_ср-y_x) 2 = ∑(y_i-y_ср) 2 – ∑(y_i-y_x) 2 = 138000 – 46000 = 92 000.
η 2 = 92 000/138000 = 0.67, η = 0.816 (0.7 < η < 0.9 – связь между X и Y высокая).

F-критерий Фишера: n = 25, m = 1.
R 2 = 1 – 46000/138000 = 0.67, F = 0.67/(1-0.67)x(25 – 1 – 1) = 46. F_{таблПоскольку фактическое значение F > Fтабл, то найденная оценка уравнения регрессии статистически надежна.}

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

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

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

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

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

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

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

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

Табличное значение критерия фишера в excel. Точный критерий фишера

Возвращает значение, обратное (правостороннему) F-распределению вероятностей. Если p = FРАСП(x;. ), то FРАСПОБР(p;. ) = x.

F-распределение может использоваться в F-тесте, который сравнивает степени разброса двух множеств данных. Например, можно проанализировать распределение доходов в США и Канаде, чтобы определить, похожи ли эти две страны по степени плотности доходов.

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

Чтобы узнать больше о новых функциях, см. статьи Функция F.ОБР и Функция F.ОБР.ПХ .

Синтаксис

Аргументы функции FРАСПОБР описаны ниже.

Вероятность — обязательный аргумент. Вероятность, связанная с интегральным F-распределением.

Степени_свободы1 — обязательный аргумент. Числитель степеней свободы.

Степени_свободы2 — обязательный аргумент. Знаменатель степеней свободы.

Замечания

Если какой-либо из аргументов не является числом, функция FРАСПОБР возвращает значение ошибки #ЗНАЧ!.

Если «вероятность» 1, функция FРАСПОБР возвращает значение ошибки #ЧИСЛО!.

Если значение аргумента «степени_свободы1» или «степени_свободы2» не является целым числом, оно усекается.

Если «степени_свободы1» S 2 2 . Наиболее часто используемый вариант Н А. Критическая область — верхний хвост F-распределения.

2. S 1 2 F табл, то коэффициент детерминации статистически значим (найденная оценка уравнения регрессии статистически надежна ) .

Дисперсионный анализ

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

Пример . По совокупности 25 предприятий торговли изучается зависимость между признаками: X — цена на товар А, тыс. руб.; Y — прибыль торгового предприятия, млн. руб. При оценке регрессионной модели были получены следующие промежуточные результаты: ∑(y i -y x) 2 = 46000; ∑(y i -y ср) 2 = 138000. Какой показатель корреляции можно определить по этим данным? Рассчитайте величину этого показателя, на основе этого результата и с помощью F-критерия Фишера сделайте вывод о качестве модели регрессии.
Решение. По этим данным можно определить эмпирическое корреляционное отношение : , где ∑(y ср -y x) 2 = ∑(y i -y ср) 2 — ∑(y i -y x) 2 = 138000 — 46000 = 92 000.
η 2 = 92 000/138000 = 0.67, η = 0.816 (0.7 Fтабл, то найденная оценка уравнения регрессии статистически надежна.

Вопрос: Какую статистику используют для проверки значимости модели регрессии?
Ответ: Для значимости всей модели в целом используют F-статистику (критерий Фишера).

Функция ФИШЕР выполняет возвращение преобразования Фишера для аргументов X . Это преобразование строит функцию, которая имеет нормальное, а не асимметричное распределение. Используется функция ФИШЕР для того чтобы проверить гипотезу с помощью коэффициента корреляции.

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

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

• не является числом. В такой ситуации функция ФИШЕР осуществит возвращение значения ошибки #ЗНАЧ!;
• имеет значение либо меньше -1, либо больше 1. В данном случае функция ФИШЕР возвратит значение ошибки #ЧИСЛО!.

Уравнение, которое используется для математического описания функции ФИШЕР, имеет вид:

Рассмотрим применение данной функции на 3-x конкретных примерах.

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

Пример 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. Рассчитывается линейный коэффициент корреляции r xy ;
2. Проверяется значимость линейного коэффициента корреляции на основе t-критерия Стьюдента. При этом выдвигается и проверяется гипотеза о равенстве коэффициента корреляции нулю. При проверке этой гипотезы используется t-статистика. Если гипотеза подтверждается, t-статистика имеет распределение Стьюдента. Если расчетное значение t р > t кр, то гипотеза отвергается, что свидетельствует о значимости линейного коэффициента корреляции, а следовательно, и о статистической существенности зависимости между Х и Y;
3. Определяется интервальная оценка для статистически значимого линейного коэффициента корреляции.
4. Определяется интервальная оценка для линейного коэффициента корреляции на основе обратного z-преобразования Фишера;
5. Рассчитывается стандартная ошибка линейного коэффициента корреляции.

Результаты решения данной задачи с применяемыми функциями в пакете Excel приведены на рисунке 1.

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

№ п/п	Наименование показателя	Формула расчета
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-критерия Фишера, сделать выводы.

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

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

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

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

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

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

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

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

Расчет величины показателя корреляции в Excel

Пример 3. Используя данные 23 предприятий о: X — цена на товар А, тыс. руб.; Y — прибыль торгового предприятия, млн. руб, производится изучение их зависимости. Оценка регрессионной модели дала следующее: ∑(yi-yx) 2 = 50000; ∑(yi-yср) 2 = 130000. Какой показатель корреляции можно определить по этим данным? Рассчитайте величину показателя корреляции и, используя критерий Фишера, сделайте вывод о качестве модели регрессии.

Функция фишер в excel и примеры ее работы.

Функция ФИШЕР выполняет возвращение преобразования Фишера для аргументов X . Это преобразование строит функцию, которая имеет нормальное, а не асимметричное распределение. Используется функция ФИШЕР для того чтобы проверить гипотезу с помощью коэффициента корреляции.

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

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

• не является числом. В такой ситуации функция ФИШЕР осуществит возвращение значения ошибки #ЗНАЧ!;
• имеет значение либо меньше -1, либо больше 1. В данном случае функция ФИШЕР возвратит значение ошибки #ЧИСЛО!.

Уравнение, которое используется для математического описания функции ФИШЕР, имеет вид:

Рассмотрим применение данной функции на 3-x конкретных примерах.

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

Пример 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. Рассчитывается линейный коэффициент корреляции r xy ;
2. Проверяется значимость линейного коэффициента корреляции на основе t-критерия Стьюдента. При этом выдвигается и проверяется гипотеза о равенстве коэффициента корреляции нулю. При проверке этой гипотезы используется t-статистика. Если гипотеза подтверждается, t-статистика имеет распределение Стьюдента. Если расчетное значение t р > t кр, то гипотеза отвергается, что свидетельствует о значимости линейного коэффициента корреляции, а следовательно, и о статистической существенности зависимости между Х и Y;
3. Определяется интервальная оценка для статистически значимого линейного коэффициента корреляции.
4. Определяется интервальная оценка для линейного коэффициента корреляции на основе обратного z-преобразования Фишера;
5. Рассчитывается стандартная ошибка линейного коэффициента корреляции.

Результаты решения данной задачи с применяемыми функциями в пакете Excel приведены на рисунке 1.

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

№ п/п	Наименование показателя	Формула расчета
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-критерия Фишера, сделать выводы.

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

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

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

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

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

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

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

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

Расчет величины показателя корреляции в Excel

Пример 3. Используя данные 23 предприятий о: X — цена на товар А, тыс. руб.; Y — прибыль торгового предприятия, млн. руб, производится изучение их зависимости. Оценка регрессионной модели дала следующее: ∑(yi-yx) 2 = 50000; ∑(yi-yср) 2 = 130000. Какой показатель корреляции можно определить по этим данным? Рассчитайте величину показателя корреляции и, используя критерий Фишера, сделайте вывод о качестве модели регрессии.

Определим F крит из выражения:

F расч = R 2 /23*(1-R 2)

где R – коэффициент детерминации, равный 0,67.

Таким образом, расчетное значение F расч = 46.

Для определения F крит используем распределение Фишера (см. рисунок 3).

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

Таким образом, полученная оценка уравнения регрессии надежна.

На данном примере рассмотрим, как оценивается надежность полученного уравнение регрессии. Этот же тест используется для проверки гипотезы о том, что коэффициенты регрессии одновременно равны нулю, a=0 , b=0 . Другими словами, суть расчетов — ответить на вопрос: можно ли его использовать для дальнейшего анализа и прогнозов?

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

Итак, целью анализа является получение некоторой оценки, с помощью которой можно было бы утверждать, что при некотором уровне α полученное уравнение регрессии — статистически надежно. Для этого используется коэффициент детерминации R 2 .
Проверка значимости модели регрессии проводится с использованием F-критерия Фишера, расчетное значение которого находится как отношение дисперсии исходного ряда наблюдений изучаемого показателя и несмещенной оценки дисперсии остаточной последовательности для данной модели.
Если расчетное значение с k 1 =(m) и k 2 =(n-m-1) степенями свободы больше табличного при заданном уровне значимости, то модель считается значимой.

где m – число факторов в модели.
Оценка статистической значимости парной линейной регрессии производится по следующему алгоритму:
1. Выдвигается нулевая гипотеза о том, что уравнение в целом статистически незначимо: H 0: R 2 =0 на уровне значимости α.
2. Далее определяют фактическое значение F-критерия:

где m=1 для парной регрессии.
3. Табличное значение определяется по таблицам распределения Фишера для заданного уровня значимости, принимая во внимание, что число степеней свободы для общей суммы квадратов (большей дисперсии) равно 1 и число степеней свободы остаточной суммы квадратов (меньшей дисперсии) при линейной регрессии равно n-2 (или через функцию Excel FРАСПОБР(вероятность;1;n-2)).
F табл — это максимально возможное значение критерия под влиянием случайных факторов при данных степенях свободы и уровне значимости α. Уровень значимости α — вероятность отвергнуть правильную гипотезу при условии, что она верна. Обычно α принимается равной 0,05 или 0,01.
4. Если фактическое значение F-критерия меньше табличного, то говорят, что нет основания отклонять нулевую гипотезу.
В противном случае, нулевая гипотеза отклоняется и с вероятностью (1-α) принимается альтернативная гипотеза о статистической значимости уравнения в целом.
Табличное значение критерия со степенями свободы k 1 =1 и k 2 =48, F табл = 4

Выводы : Поскольку фактическое значение F > F табл, то коэффициент детерминации статистически значим (найденная оценка уравнения регрессии статистически надежна ) .

Дисперсионный анализ

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

Пример . По совокупности 25 предприятий торговли изучается зависимость между признаками: X — цена на товар А, тыс. руб.; Y — прибыль торгового предприятия, млн. руб. При оценке регрессионной модели были получены следующие промежуточные результаты: ∑(y i -y x) 2 = 46000; ∑(y i -y ср) 2 = 138000. Какой показатель корреляции можно определить по этим данным? Рассчитайте величину этого показателя, на основе этого результата и с помощью F-критерия Фишера сделайте вывод о качестве модели регрессии.
Решение. По этим данным можно определить эмпирическое корреляционное отношение : , где ∑(y ср -y x) 2 = ∑(y i -y ср) 2 — ∑(y i -y x) 2 = 138000 — 46000 = 92 000.
η 2 = 92 000/138000 = 0.67, η = 0.816 (0.7 Fтабл, то найденная оценка уравнения регрессии статистически надежна.

Вопрос: Какую статистику используют для проверки значимости модели регрессии?
Ответ: Для значимости всей модели в целом используют F-статистику (критерий Фишера).

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

где в числителе стоит большее значение выборочной дисперсии, а в знаменателе — меньшее. Для вывода о достоверности различий между выборками используют ОСНОВНОЙ ПРИНЦИП проверки статистических гипотез. Критические точки для
содержатся в таблице. Нулевую гипотезу отвергают, если фактически установленная величина
превзойдет или окажется равной критическому (стандартному) значению
этой величины для принятого уровня значимости и числа степеней свободы k 1 = n большая -1 ; k 2 = n меньшая -1 .

П р и м е р: при изучении влияния некоторого препарата на скорость проростания семян было установлено, что в экспериментальной партии семян и контроле средняя скорость проростания одинакова, но есть разница в дисперсиях.
=1250,
=417. Объемы выборок одинаковы и равны 20.

=2,12. Следовательно, нулевая гипотеза отвергается.

Корреляционная зависимость. Коэффициент корреляции и его свойства. Уравнения регрессии.

ЗАДАЧА корреляционного анализа сводится к:

Установлению направления и формы связи между признаками;

Измерению ее тесноты.

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

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

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

По виду корреляционного поля можно судить о наличии или отсутствии связи и ее типе.

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

Связь называется отрицательной , если при увеличении одной переменной уменьшается другая переменная.

Связь называется линейной , если ее можно в аналитическом виде представить как
.

Показателем тесноты связи является коэффициент корреляции . Эмпирический коэффициент корреляции определяется выражением:

Коэффициент корреляции лежит в пределах от -1 до1 и характеризует степень близости между величинамиx иy . Если:

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

Уравнение прямой регрессии х нау в общем случае выглядит как

Наиболее вероятные значения коэффициентов а и в , с и d могут быть вычислены, например, при использовании метода наименьших квадратов.

Расчет критерия φ*

1. Определить те значения признака, которые будут критерием для разделения испытуемых на тех, у кого «есть эффект» и тех, у кого «нет эффекта». Если признак измерен количественно, использовать критерий λ для поиска опти­мальной точки разделения.

2. Начертить четырёхклеточную (синоним: четырёхпольная) таблицу из двух столбцов и двух строк. Пер­вый столбец — «есть эффект»; второй столбец — «нет эффекта»; первая стро­ка сверху — 1 группа (выборка); вторая строка — 2 группа (выборка).

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

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

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

8. Проверить, не равняется ли одна из сопоставляемых процентных долей ну­лю. Если это так, попробовать изменить это, сдвинув точку разделения групп в ту или иную сторону. Если это невозможно или нежелательно, от­казаться от критерия φ* и использовать критерий χ2.

9. Определить по Табл. XII Приложения 1 величины углов φ для каждой из сопоставляемых процентных долей.

где: φ1 — угол, соответствующий большей процентной доле;

φ2 — угол, соответствующий меньшей процентной доле;

N1 — количество наблюдений в выборке 1;

N2 — количество наблюдений в выборке 2.

11. Сопоставить полученное значение φ* с критическими значениями: φ* ≤1,64 (р φ*

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

Описание критерия

Критерий оценивает достоверность различий между теми процентными долями двух выборок, в которых зарегистрирован интересующий нас эффект (показатель). Образно говоря, мы сравниваем между собой 2 лучших куска, вырезанные из 2-х пирогов, и решаем, какой из них действительно больше.

Суть углового преобразования Фишера состоит в переводе процент­ных долей в величины центрального угла, который измеряется в радианах. Большей процентной доле будет соответствовать больший угол ф, а меньшей доле — меньший угол, но соотношения здесь не линейные:

где Р — процентная доля, выраженная в долях единицы (см. Рис. 5.1).

При увеличении расхождения между углами φ 1 и φ 2 и увеличе­ния численности выборок значение критерия возрастает. Чем больше величина φ* , тем более вероятно, что различия достоверны.

H 0 : Доля лиц , у которых проявляется исследуемый эффект, в выборке 1 не больше, чем в выборке 2.

H 1 : Доля лиц, у которых проявляется исследуемый эффект, в выборке 1 больше, чем в выборке 2.

Графическое представление критерия φ*

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

Формула, которой придерживается Е. В. Гублер при подсчете значений φ, предполагает, что 100% составляют угол φ=3,142, то есть округленную величину π=3,14159. Это позволяет нам представить со­поставляемые выборки в виде двух полукругов, каждый из которых символизирует 100% численности своей выборки. Процентные доли испытуемых с «эффектом» будут представлены как секторы, образован­ные центральными углами φ. На Рис. 5.2 представлены два полукруга, иллюстрирующие Пример 1. В первой выборке 60% испытуемых ре­шили задачу. Этой процентной доле соответствует угол φ=1,772. Во второй выборке 40% испытуемых решили задачу. Этой процентной доле соответствует угол φ =1,369.

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

Ограничения критерия φ*

1. Ни одна из сопоставляемых долей не должна быть равной нулю. Формально нет препятствий для применения метода φ в случаях, когда доля наблюдений в одной из выборок равна 0. Однако в этих случаях результат может оказаться неоправданно завышенным (Гублер Е.В., 1978, с. 86).

2. Верхний предел в критерии φ отсутствует — выборки могут быть сколь угодно большими.

Нижний предел — 2 наблюдения в одной из выборок. Однако должны соблюдаться следующие соотношения в численности двух выборок:

а) если в одной выборке всего 2 наблюдения, то во второй должно быть не менее 30:

б) если в одной из выборок всего 3 наблюдения, то во второй должно быть не менее 7:

в) если в одной из выборок всего 4 наблюдения, то во второй должно быть не менее 5:

г) при n 1 , n 2 ≥ 5 возможны любые сопоставления.

В принципе возможно и сопоставление выборок, не отвечающих этому условию, например, с соотношением n 1 =2, n 2 = 15, но в этих слу­чаях не удастся выявить достоверных различий.

Других ограничений у критерия φ* нет.

Рассмотрим несколько примеров, иллюстрирующих возможности критерия φ*.

Пример 1: сопоставление выборок по качественно определяемому при­знаку.

Пример 2: сопоставление выборок по количественно измеряемому при­знаку.

Пример 3: сопоставление выборок и по уровню, и по распределению признака.

Пример 4: использование критерия φ* в сочетании с критерием X Колмогорова-Смирнова в целях достижения максимально точного результата.

Пример 1 — сопоставление выборок по качественно определяемому признаку

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

Допустим, нас интересует, различаются ли две группы студентов по успешности решения новой экспериментальной задачи. В первой группе из 20 человек с нею справились 12 человек, а во второй выбор­ке из 25 человек — 10. В первом случае процентная доля решивших за­дачу составит 12/20·100%=60%, а во второй 10/25·100%=40%. Дос­товерно ли различаются эти процентные доли при данных n 1 и n 2 ?

Казалось бы, и «на глаз» можно определить, что 60% значи­тельно выше 40%. Однако на самом деле эти различия при данных n 1 , n 2 недостоверны.

Проверим это. Поскольку нас интересует факт решения задачи, будем считать «эффектом» успех в решении экспериментальной задачи, а отсутствием эффекта — неудачу в ее решении.

H 0 : Доля лиц, справившихся с задачей, в первой группе не больше, чем во второй группе.

H 1 : Доля лиц, справившихся с задачей, в первой группе больше, чем во второй группе.

Теперь построим так называемую четырехклеточную, или четы­рехпольную таблицу, которая фактически представляет собой таблицу эмпирических частот по двум значениям признака: «есть эффект» — «нет эффекта».

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