Расчет дисперсии в microsoft excel

Выбор линейной регрессионной модели

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

Определим “полную” модель: (10)

Определим “укороченную” модель: (11)

Вычисляем сумму квадратов ошибок для каждой модели:

(12)

(13)

Определяем количество степеней свобод

(14)

Нулевая гипотеза — “укороченная” модель мало отличается от “полной (удлиненной) модели”. Поэтому выбираем “укороченную” модель. Альтернативная гипотеза — “полная (удлиненная)” модель объясняет значимо большую долю дисперсии в данных по сравнению с “укороченной” моделью.

Коэффициент детерминации из формулы (6):

Из формулы (15) выразим SSE(F):

SSTO одинаково как для “укороченной”, так и для “длинной” модели. Тогда (14) примет вид:

Поделим числитель и знаменатель (14a) на SSTO, после чего прибавим и вычтем единицу в числителе.

Используя формулу (15) в конечном счете получим F-статистику, выраженную через коэффициенты детерминации.

-Критерий стьюдента для уравнения множественной регрессии.

Частный
-критерий
оценивает значимость коэффициентов
чистой регрессии. Зная величину,
можно определить и-критерий
для коэффициента регрессии при-м
факторе,,
а именно:

.
(2.24)

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

,
(2.25)

где
– коэффициент чистой регрессии при
факторе,– средняя квадратическая (стандартная)
ошибка коэффициента регрессии.

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

,
(2.26)

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

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

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

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

Вычисление дисперсии

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

Способ 1: расчет по генеральной совокупности

Для расчета данного показателя в Excel по генеральной совокупности применяется функция ДИСП.Г. Синтаксис этого выражения имеет следующий вид:

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

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

1. Производим выделение ячейки на листе, в которую будут выводиться итоги вычисления дисперсии. Щелкаем по кнопке «Вставить функцию», размещенную слева от строки формул.
2. Запускается Мастер функций. В категории «Статистические» или «Полный алфавитный перечень» выполняем поиск аргумента с наименованием «ДИСП.Г». После того, как нашли, выделяем его и щелкаем по кнопке «OK».
3. Выполняется запуск окна аргументов функции ДИСП.Г. Устанавливаем курсор в поле «Число1». Выделяем на листе диапазон ячеек, в котором содержится числовой ряд. Если таких диапазонов несколько, то можно также использовать для занесения их координат в окно аргументов поля «Число2», «Число3» и т.д. После того, как все данные внесены, жмем на кнопку «OK».
4. Как видим, после этих действий производится расчет. Итог вычисления величины дисперсии по генеральной совокупности выводится в предварительно указанную ячейку. Это именно та ячейка, в которой непосредственно находится формула ДИСП.Г.

Урок: Мастер функций в Эксель

Способ 2: расчет по выборке

В отличие от вычисления значения по генеральной совокупности, в расчете по выборке в знаменателе указывается не общее количество чисел, а на одно меньше. Это делается в целях коррекции погрешности. Эксель учитывает данный нюанс в специальной функции, которая предназначена для данного вида вычисления – ДИСП.В. Её синтаксис представлен следующей формулой:

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

1. Выделяем ячейку и таким же способом, как и в предыдущий раз, запускаем Мастер функций.
2. В категории «Полный алфавитный перечень» или «Статистические» ищем наименование «ДИСП.В». После того, как формула найдена, выделяем её и делаем клик по кнопке «OK».
3. Производится запуск окна аргументов функции. Далее поступаем полностью аналогичным образом, как и при использовании предыдущего оператора: устанавливаем курсор в поле аргумента «Число1» и выделяем область, содержащую числовой ряд, на листе. Затем щелкаем по кнопке «OK».
4. Результат вычисления будет выведен в отдельную ячейку.

Урок: Другие статистические функции в Эксель

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

Зачем использовать F-тест в регрессионном анализе

В линейной регрессии F-критерий можно использовать для ответа на следующие вопросы:

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

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

Говорят, что модель с ограничениями вложена в модель без ограничений.

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

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

Наш подход к тестированию будет следующим:

Начнем с двух гипотез:

• H_0: Нулевая гипотеза:Модель с лаговой переменной не объясняет разницу в цене закрытия DJIA лучше, чем модель с перехватом
• H_1: альтернативная гипотеза:Модель с переменной задержкой лучше справляется (статистически значимым образом) с объяснением разницы в цене закрытия DJIA, чем модель с перехватом.

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

• Нулевая гипотеза может быть отклонена (и принята альтернативная гипотеза) с некоторой погрешностью, ИЛИ
• Нулевая гипотеза должна быть принята.

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

history 12 декабря 2016 г.

Рассмотрим использование MS EXCEL при проверке статистических гипотез о равенстве дисперсий 2-х нормальных распределений. Вычислим значение тестовой статистики F , рассмотрим процедуру «двухвыборочный F -тест», вычислим Р-значение (Р- value ), построим доверительный интервал. С помощью надстройки Пакет анализа сделаем «двухвыборочный F -тест для дисперсии».

Имеется две независимых случайных нормально распределенных величины . Эти случайные величины имеют нормальные распределения с неизвестными дисперсиями σ ₁ 2 и σ ₂ 2 соответственно. Из этих распределений получены две выборки размером n ₁ и n ₂ .

Необходимо произвести проверку гипотезы о равенстве дисперсий этих распределений (англ. Hypothesis Tests for the Equality of Variances of Two Normal Distributions).

СОВЕТ : Для проверки гипотез потребуется знание следующих понятий:

Примечание : Провер ка гипотез о дисперсии нормального распределения ( одновыборочный тест ) изложена в статье Проверка статистических гипотез в MS EXCEL о дисперсии нормального распределения .

Нулевая гипотеза H звучит так: дисперсии нормальных распределений равны, т.е. σ ₁ 2 = σ ₂ 2 .

Альтернативная гипотеза H ₁ : σ ₁ 2 σ ₂ 2 . Т.е. нам требуется проверить двухстороннюю гипотезу .

В отличие от z-теста и t-теста , где мы рассматривали разность средних значений , в этом тесте будем рассматривать отношение дисперсий : σ ₁ 2 / σ ₂ 2 . Если дисперсии равны, то их отношение должно быть равно 1.

Как известно, точечной оценкой дисперсии распределения σ 2 может служить значение дисперсии выборки s 2 . Соответственно, оценкой отношения дисперсий σ ₂ 2 / σ ₂ 2 будет s ₁ 2 / s ₂ 2 .

Процедура проверки гипотезы о равенстве дисперсий 2-х распределений имеет специальное название: двухвыборочный F -тест для дисперсий (F-Test: Hypothesis Tests for the Variances of Two Normal Distributions).

Тестовой статистикой для проверки гипотез данного вида является случайная величина F= s ₁ 2 / s ₂ 2 .

Данная тестовая статистика , как и любая другая случайная величина, имеет свое распределение (в процедуре проверки гипотез это распределение называют « эталонным распределением », англ. Reference distribution). В нашем случае F -статистика имеет F-распределение (распределение Фишера) . Значение, которое приняла F -статистика обозначим F .

Примечание : В статье Статистики и их распределения показано , что выборочное распределение статистики

выборок₁₂степенями свободы

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

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

• больше верхнего α/2-квантиля F-распределения вероятности с n ₁ -1 и n ₂ -1 степенями свободы или
• меньше нижнего α/2-квантиля того же распределения.

Примечание : Верхний α/2-квантиль — это такое значение случайной величины F , что P ( F >= F _{α/2, n1-1, n2-1})=α/2. Верхний 1-α /2- квантиль равен нижнему α/2 квантилю . Подробнее о квантилях распределений см. статью Квантили распределений MS EXCEL .

Запишем критерий отклонения с помощью верхних квантилей:

Чтобы в MS EXCEL вычислить значение нижнего квантиля α/2-квантиля — используйте формулу =F.ОБР(α /2 ; n ₁ -1, n ₂ -1) или =F.ОБР.ПХ(1-α /2 ; n ₁ -1, n ₂ -1)

Проверка двухсторонней гипотезы приведена в файле примера .

F-тест обычно используется для того, чтобы ответить на следующие вопросы:

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

СОВЕТ : Перед проверкой гипотез о равенстве дисперсий полезно построить двумерную гистограмму , чтобы визуально определить разброс данных в обеих выборок .

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

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

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

Н0: R2 = 0;

Н1: R2 ≠ 0.

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

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

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

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

=FРАСПОБР (α;p;n-p-1)

где:

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

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

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

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

Определения, необходимые для F -теста

Согласно определению дисперсии , дисперсия выборки прогнозируемой переменной Y определяется формулой:

В формуле используется ряд сокращений:

• SST (Total Sum of Squares) – это просто компактное обозначение Суммы Квадратов отклонений от среднего (такое сокращение часто используется в зарубежной литературе).
• MST (Total Mean Square) – Среднее Суммы Квадратов отклонений (еще одно общеупотребительное сокращение).

Примечание : Необходимо иметь в виду, что с одной стороны величины MST и SST являются случайными величинами, вычисленными на основании выборки, т.е. статистиками . Однако с другой стороны, при проведении регрессионного анализа по данным имеющейся выборки вычисляются их конкретные значения. В этом случае величины MST и SST являются просто числами.

Значение n-1 в вышеуказанной формуле равно числу степеней свободы ( DF ) , которое относится к дисперсии выборки (одна степень свободы у n величин yi потеряна в результате наличия ограничения , связывающего все значения выборки). Число степеней свободы у величины SST также имеет специальное обозначение: DFT (DF Total).

Как видно из формулы, отношение величин SST и DFT обозначается как MST. Эти 3 величины обычно выдаются в таблице результатов дисперсионного анализа в различных прикладных статистических программах (в том числе и в надстройке Пакет анализа, инструмент Регрессия ).

Значение SST, характеризующую общую изменчивость переменной Y, можно разбить на 2 компоненты:

Изменчивость объясненную моделью (Explained variation), обозначается SSR

Необъясненную изменчивость (Unexplained variation), обозначается SSЕ

Известно , что справедливо равенство:

Величинам SSR и SSE также сопоставлены степени свободы . У SSR одна степень свободы , т.к. она однозначно определяется одним параметром – наклоном линии регрессии a (напомним, что мы рассматриваем простую линейную регрессию ). Это очевидно из формулы:

Примечание: Очевидность наличия только одной степени свободы проистекает из факта, что переменная Х – контролируемая (не является случайной величиной).

Число степеней свободы величины SSR имеет специальное обозначение: DFR (для простой регрессии DFR=1, т.к. число независимых переменных Х равно 1) . По аналогии с MST, отношение этих величин также часто обозначают MSR = SSR DFR .

У SSE число степеней свободы равно n -2 , которое обозначается как DFE (или DFRES — residual degrees of freedom). Двойка вычитается, т.к. изменчивость переменной yi имеет 2 ограничения, связанные с оценкой 2-х параметров линейной модели ( а и b ): ŷi=a*xi+b

Отношение этих величин также часто обозначают MSE = SSE DFE .

MSR и MSE имеют размерность дисперсий, хотя корректней их называть средними значениями квадратов отклонений. Тем не менее, ниже мы их будем «дисперсиями», т.к. они отображают меру разброса: MSE – меру разброса точек наблюдений относительно линии регрессии, MSR показывает насколько линия регрессии совпадает с горизонтальной линией среднего значения Y.

Примечание : Напомним, что MSE (Mean Square of Errors) является оценкой дисперсии s 2 ошибки, подробнее см. статью про линейную регрессию , раздел Стандартная ошибка регрессии .

Число степеней свободы обладает свойством аддитивности: DFT = DFR + DFE . В этом можно убедиться, составив соответствующее равенство n -1=1+( n -2)

Наконец, определившись с определениями, переходим к рассмотрению самой процедуры F -тест .

Вычисление Р-значения

При проверке гипотез, помимо F -теста, большое распространение получил еще один эквивалентный подход, основанный на вычислении p -значения (p-value).

Если p-значение меньше, чем заданный уровень значимости α , то нулевая гипотеза отвергается и принимается альтернативная гипотеза . И наоборот, если p-значение больше α, то нулевая гипотеза не отвергается.

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

• если F >1, то p-значение равно удвоенной вероятности, что F-статистика примет значение больше F ,
• если F =2*МИН(F.РАСП(F ; n ₁ -1; n ₂ -1; ИСТИНА); F.РАСП.ПХ(F ; n ₁ -1; n ₂ -1))

Почему вычисляется удвоенная вероятность? Представим, что установлен уровень доверия 0,05, а F Примечание : Про p -значение можно также прочитать в статье про двухвыборочный z-тест .

Анализ дисперсии, F-тест

При выполнении основных предположений линейной регрессии имеет место формула:

где ,

,

В случае, если имеем модель по формуле (1), то из предыдущего раздела знаем, что количество степеней свободы у SSTO равно n-1. Количество степеней свободы у SSE равно n-2. Таким образом количество степеней свободы у SSR равно 1. Только в таком случае получаем равенство .

Масштабируем SSE и SSR с учетом их степеней свободы:

Получены хи-квадрат распределения. F-статистика вычисляется по формуле:

Формула (9) используется при проверке нулевой гипотезы при альтернативной гипотезе в случае линейной регрессионной модели вида (1).

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

Пример
. По совокупности 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-критерий Фишера
: 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-статистику (критерий Фишера).

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

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

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

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

2. S 1 2

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

Пример

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

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

Таблица 5.1

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

Регрессионный анализ в Excel

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

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

• линейной (у = а + bx);
• параболической (y = a + bx + cx 2 );
• экспоненциальной (y = a * exp(bx));
• степенной (y = a*x^b);
• гиперболической (y = b/x + a);
• логарифмической (y = b * 1n(x) + a);
• показательной (y = a * b^x).

Рассмотрим на примере построение регрессионной модели в Excel и интерпретацию результатов. Возьмем линейный тип регрессии.

Задача. На 6 предприятиях была проанализирована среднемесячная заработная плата и количество уволившихся сотрудников. Необходимо определить зависимость числа уволившихся сотрудников от средней зарплаты.

Модель линейной регрессии имеет следующий вид:

Где а – коэффициенты регрессии, х – влияющие переменные, к – число факторов.

В нашем примере в качестве У выступает показатель уволившихся работников. Влияющий фактор – заработная плата (х).

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

Активируем мощный аналитический инструмент:

1. Нажимаем кнопку «Офис» и переходим на вкладку «Параметры Excel». «Надстройки».
2. Внизу, под выпадающим списком, в поле «Управление» будет надпись «Надстройки Excel» (если ее нет, нажмите на флажок справа и выберите). И кнопка «Перейти». Жмем.
3. Открывается список доступных надстроек. Выбираем «Пакет анализа» и нажимаем ОК.

После активации надстройка будет доступна на вкладке «Данные».

Теперь займемся непосредственно регрессионным анализом.

1. Открываем меню инструмента «Анализ данных». Выбираем «Регрессия».
2. Откроется меню для выбора входных значений и параметров вывода (где отобразить результат). В полях для исходных данных указываем диапазон описываемого параметра (У) и влияющего на него фактора (Х). Остальное можно и не заполнять.
3. После нажатия ОК, программа отобразит расчеты на новом листе (можно выбрать интервал для отображения на текущем листе или назначить вывод в новую книгу).

В первую очередь обращаем внимание на R-квадрат и коэффициенты. R-квадрат – коэффициент детерминации

В нашем примере – 0,755, или 75,5%. Это означает, что расчетные параметры модели на 75,5% объясняют зависимость между изучаемыми параметрами. Чем выше коэффициент детерминации, тем качественнее модель. Хорошо – выше 0,8. Плохо – меньше 0,5 (такой анализ вряд ли можно считать резонным). В нашем примере – «неплохо»

R-квадрат – коэффициент детерминации. В нашем примере – 0,755, или 75,5%. Это означает, что расчетные параметры модели на 75,5% объясняют зависимость между изучаемыми параметрами. Чем выше коэффициент детерминации, тем качественнее модель. Хорошо – выше 0,8. Плохо – меньше 0,5 (такой анализ вряд ли можно считать резонным). В нашем примере – «неплохо».

Коэффициент 64,1428 показывает, каким будет Y, если все переменные в рассматриваемой модели будут равны 0. То есть на значение анализируемого параметра влияют и другие факторы, не описанные в модели.

Коэффициент -0,16285 показывает весомость переменной Х на Y. То есть среднемесячная заработная плата в пределах данной модели влияет на количество уволившихся с весом -0,16285 (это небольшая степень влияния). Знак «-» указывает на отрицательное влияние: чем больше зарплата, тем меньше уволившихся. Что справедливо.

Роль степеней свободы (degree of freedom) в статистике

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

Сколько нужно наблюдений, чтобы построить линейную регрессионную модель? В случае двух наблюдений можем получить идеальную модель (рис.1), однако есть в этом недостаток. Причина в том, что сумма квадратов ошибки (MSE) равна нулю и не можем оценить оценить неопределенность коэффициентов . Например не можем построить доверительный интервал для коэффициента наклона по формуле:

А значит не можем сказать ничего о целесообразности использования коэффициента в данной регрессионной модели. Необходимо по крайней мере 3 точки. А что же, если все три точки могут поместиться на одну линию? Такое может быть. Но при большом количестве наблюдений маловероятна идеальная линейная зависимость между зависимой и независимыми переменными (рис. 1).

Рисунок 1 — простая линейная регрессия

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

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

Представим теперь что имеется 4 выборочных совокупностей (рис.3).

Рисунок 3

Каждая выборочная совокупность имеет свое среднее значение, определяемое по формуле . И каждое выборочное среднее может быть оценено . Для оценки мы используем 2 параметра , а значит теряем 2 степени свободы (нужно знать 2 точки). То есть количество степеней свобод Заметим, что при 2 наблюдениях получаем 0 степеней свободы, а значит не можем оценить коэффициенты модели и стандартные ошибки.

Таким образом сумма квадратов ошибок имеет (SSE, SSE — standard error of estimate) вид:

Стоит упомянуть, что в знаменателе стоит n-2, а не n-1 в связи с тем, что среднее значение оценивается по формуле . Квадратные корень формулы (4) — ошибка стандартного отклонения.

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

где n — число наблюдений, k — число независимых переменных.

Вывод

• F-критерий можно использовать в регрессионном анализе, чтобы определить, лучше ли сложная модель, чем более простая версия той же модели, для объяснения отклонения в зависимой переменной.
• Тестовая статистика F-теста является случайной величиной, чьяпrobabilityDensityFUnction является F-распределениемв предположении, что нулевая гипотеза верна.
• Процедура тестирования для F-теста на регрессию по своей структуре идентична процедуре других значимых параметрических тестов, таких как t-тест.

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

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