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

Виды регрессии

Само это понятие было введено в математику в 1886 году. Регрессия бывает:

• линейной;
• параболической;
• степенной;
• экспоненциальной;
• гиперболической;
• показательной;
• логарифмической.

Пример 1

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

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

		Количество уволившихся	Зарплата
			30000 рублей
			35000 рублей
			40000 рублей
			45000 рублей
			50000 рублей
			55000 рублей
			60000 рублей

Для задачи определения зависимости количества уволившихся работников от средней зарплаты на 6 предприятиях модель регрессии имеет вид уравнения Y = а 0 + а 1 x 1 +…+а k x k , где х i — влияющие переменные, a i — коэффициенты регрессии, a k — число факторов.

Для данной задачи Y — это показатель уволившихся сотрудников, а влияющий фактор — зарплата, которую обозначаем X.

Использование возможностей табличного процессора «Эксель»

Анализу регрессии в Excel должно предшествовать применение к имеющимся табличным данным встроенных функций. Однако для этих целей лучше воспользоваться очень полезной надстройкой «Пакет анализа». Для его активации нужно:

• с вкладки «Файл» перейти в раздел «Параметры»;
• в открывшемся окне выбрать строку «Надстройки»;
• щелкнуть по кнопке «Перейти», расположенной внизу, справа от строки «Управление»;
• поставить галочку рядом с названием «Пакет анализа» и подтвердить свои действия, нажав «Ок».

Если все сделано правильно, в правой части вкладки «Данные», расположенном над рабочим листом «Эксель», появится нужная кнопка.

в Excel

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

• щелкаем по кнопке «Анализ данных»;
• в открывшемся окне нажимаем на кнопку «Регрессия»;
• в появившуюся вкладку вводим диапазон значений для Y (количество уволившихся работников) и для X (их зарплаты);
• подтверждаем свои действия нажатием кнопки «Ok».

В результате программа автоматически заполнит новый лист табличного процессора данными анализа регрессии. Обратите внимание! В Excel есть возможность самостоятельно задать место, которое вы предпочитаете для этой цели. Например, это может быть тот же лист, где находятся значения Y и X, или даже новая книга, специально предназначенная для хранения подобных данных.

Анализ результатов регрессии для R-квадрата

В Excel данные полученные в ходе обработки данных рассматриваемого примера имеют вид:

Прежде всего, следует обратить внимание на значение R-квадрата. Он представляет собой коэффициент детерминации. В данном примере R-квадрат = 0,755 (75,5%), т. е. расчетные параметры модели объясняют зависимость между рассматриваемыми параметрами на 75,5 %. Чем выше значение коэффициента детерминации, тем выбранная модель считается более применимой для конкретной задачи. Считается, что она корректно описывает реальную ситуацию при значении R-квадрата выше 0,8. Если R-квадрата<0,5, то такой анализа регрессии в Excel нельзя считать резонным.

Анализ коэффициентов

Число 64,1428 показывает, каким будет значение Y, если все переменные xi в рассматриваемой нами модели обнулятся. Иными словами можно утверждать, что на значение анализируемого параметра оказывают влияние и другие факторы, не описанные в конкретной модели.

Следующий коэффициент -0,16285, расположенный в ячейке B18, показывает весомость влияния переменной Х на Y. Это значит, что среднемесячная зарплата сотрудников в пределах рассматриваемой модели влияет на число уволившихся с весом -0,16285, т. е. степень ее влияния совсем небольшая. Знак «-» указывает на то, что коэффициент имеет отрицательное значение. Это очевидно, так как всем известно, что чем больше зарплата на предприятии, тем меньше людей выражают желание расторгнуть трудовой договор или увольняется.

Множественная регрессия

Под таким термином понимается уравнение связи с несколькими независимыми переменными вида:

y=f(x 1 +x 2 +…x m) + ε, где y — это результативный признак (зависимая переменная), а x 1 , x 2 , …x m — это признаки-факторы (независимые переменные).

Оценка параметров

Для множественной регрессии (МР) ее осуществляют, используя метод наименьших квадратов (МНК). Для линейных уравнений вида Y = a + b 1 x 1 +…+b m x m + ε строим систему нормальных уравнений (см. ниже)

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

Отсюда получаем:

где σ — это дисперсия соответствующего признака, отраженного в индексе.

МНК применим к уравнению МР в стандартизируемом масштабе. В таком случае получаем уравнение:

в котором t y , t x 1, … t xm — стандартизируемые переменные, для которых средние значения равны 0; β i — стандартизированные коэффициенты регрессии, а среднеквадратическое отклонение — 1.

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

Задача с использованием уравнения линейной регрессии

Предположим, имеется таблица динамики цены конкретного товара N в течение последних 8 месяцев. Необходимо принять решение о целесообразности приобретения его партии по цене 1850 руб./т.

	номер месяца	название месяца	цена товара N
			1750 рублей за тонну
			1755 рублей за тонну
			1767 рублей за тонну
			1760 рублей за тонну
			1770 рублей за тонну
			1790 рублей за тонну
			1810 рублей за тонну
			1840 рублей за тонну

Для решения этой задачи в табличном процессоре «Эксель» требуется задействовать уже известный по представленному выше примеру инструмент «Анализ данных». Далее выбирают раздел «Регрессия» и задают параметры. Нужно помнить, что в поле «Входной интервал Y» должен вводиться диапазон значений для зависимой переменной (в данном случае цены на товар в конкретные месяцы года), а в «Входной интервал X» — для независимой (номер месяца). Подтверждаем действия нажатием «Ok». На новом листе (если так было указано) получаем данные для регрессии.

Строим по ним линейное уравнение вида y=ax+b, где в качестве параметров a и b выступают коэффициенты строки с наименованием номера месяца и коэффициенты и строки «Y-пересечение» из листа с результатами регрессионного анализа. Таким образом, линейное уравнение регрессии (УР) для задачи 3 записывается в виде:

Цена на товар N = 11,714* номер месяца + 1727,54.

или в алгебраических обозначениях

y = 11,714 x + 1727,54

Анализ результатов

Чтобы решить, адекватно ли полученное уравнения линейной регрессии, используются коэффициенты множественной корреляции (КМК) и детерминации, а также критерий Фишера и критерий Стьюдента. В таблице «Эксель» с результатами регрессии они выступают под названиями множественный R, R-квадрат, F-статистика и t-статистика соответственно.

КМК R дает возможность оценить тесноту вероятностной связи между независимой и зависимой переменными. Ее высокое значение свидетельствует о достаточно сильной связи между переменными «Номер месяца» и «Цена товара N в рублях за 1 тонну». Однако, характер этой связи остается неизвестным.

Квадрат коэффициента детерминации R 2 (RI) представляет собой числовую характеристику доли общего разброса и показывает, разброс какой части экспериментальных данных, т.е. значений зависимой переменной соответствует уравнению линейной регрессии. В рассматриваемой задаче эта величина равна 84,8%, т. е. статистические данные с высокой степенью точности описываются полученным УР.

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

(критерий Стьюдента) помогает оценивать значимость коэффициента при неизвестной либо свободного члена линейной зависимости. Если значение t-критерия > t кр, то гипотеза о незначимости свободного члена линейного уравнения отвергается.

В рассматриваемой задаче для свободного члена посредством инструментов «Эксель» было получено, что t=169,20903, а p=2,89Е-12, т. е. имеем нулевую вероятность того, что будет отвергнута верная гипотеза о незначимости свободного члена. Для коэффициента при неизвестной t=5,79405, а p=0,001158. Иными словами вероятность того, что будет отвергнута верная гипотеза о незначимости коэффициента при неизвестной, равна 0,12%.

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

Задача о целесообразности покупки пакета акций

Множественная регрессия в Excel выполняется с использованием все того же инструмента «Анализ данных». Рассмотрим конкретную прикладную задачу.

Руководство компания «NNN» должно принять решение о целесообразности покупки 20 % пакета акций АО «MMM». Стоимость пакета (СП) составляет 70 млн американских долларов. Специалистами «NNN» собраны данные об аналогичных сделках. Было принято решение оценивать стоимость пакета акций по таким параметрам, выраженным в миллионах американских долларов, как:

• кредиторская задолженность (VK);
• объем годового оборота (VO);
• дебиторская задолженность (VD);
• стоимость основных фондов (СОФ).

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

Решение средствами табличного процессора Excel

Прежде всего, необходимо составить таблицу исходных данных. Она имеет следующий вид:

• вызывают окно «Анализ данных»;
• выбирают раздел «Регрессия»;
• в окошко «Входной интервал Y» вводят диапазон значений зависимых переменных из столбца G;
• щелкают по иконке с красной стрелкой справа от окна «Входной интервал X» и выделяют на листе диапазон всех значений из столбцов B,C, D, F.

Отмечают пункт «Новый рабочий лист» и нажимают «Ok».

Получают анализ регрессии для данной задачи.

Изучение результатов и выводы

«Собираем» из округленных данных, представленных выше на листе табличного процессора Excel, уравнение регрессии:

СП = 0,103*СОФ + 0,541*VO - 0,031*VK +0,405*VD +0,691*VZP - 265,844.

В более привычном математическом виде его можно записать, как:

y = 0,103*x1 + 0,541*x2 - 0,031*x3 +0,405*x4 +0,691*x5 - 265,844

Данные для АО «MMM» представлены в таблице:

Подставив их в уравнение регрессии, получают цифру в 64,72 млн американских долларов. Это значит, что акции АО «MMM» не стоит приобретать, так как их стоимость в 70 млн американских долларов достаточно завышена.

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

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

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

1) выделить столбцы с данными X и Y (они должны располагаться именно в таком порядке!);

2) вызвать Мастер диаграмм и выбрать в группе Тип – Точечная и сразу нажать Готово ;

3) не сбрасывая выделения с диаграммы, выбрать появившейся пункт основного меню Диаграмма , в котором следует выбрать пункт Добавить линию тренда ;

4) в появившемся диалоговом окне Линия тренда во вкладке Тип выбрать Линейная ;

5) во вкладке Параметры можно активизировать переключатель Показывать уравнение на диаграмме , что позволит увидеть уравнение линейной регрессии (4.4), в котором будут вычислены коэффициенты (4.5).

6) В этой же вкладке можно активизировать переключатель Поместить на диаграмму величину достоверности аппроксимации (R^2) . Эта величина есть квадрат коэффициента корреляции (4.3) и она показывает, насколько хорошо рассчитанное уравнение описывает экспериментальную зависимость. Если R 2 близок к единице, то теоретическое уравнение регрессии хорошо описывает экспериментальную зависимость (теория хорошо согласуется с экспериментом), а если R 2 близок к нулю, то данное уравнение не пригодно для описания экспериментальной зависимости (теория не согласуется с экспериментом).

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

§4.3. Основные виды нелинейной регрессии

Параболическая и полиномиальная регрессии.

Параболической зависимостью величины Y от величины Х называется зависимость, выраженная квадратичной функцией (параболой 2-ого порядка):

Это уравнение называется уравнением параболической регрессии Y на Х . Параметры а , b , с называются коэффициентами параболической регрессии . Вычисление коэффициентов параболической регрессии всегда громоздко, поэтому для расчетов рекомендуется использовать компьютер.

Уравнение (4.8) параболической регрессии является частным случаем более общей регрессии, называемой полиномиальной. Полиномиальной зависимостью величины Y от величины Х называется зависимость, выраженная полиномом n -ого порядка:

где числа а i (i =0,1,…, n ) называются коэффициентами полиномиальной регрессии .

Степенная регрессия.

Степенной зависимостью величины Y от величины Х называется зависимость вида:

Это уравнение называется уравнением степенной регрессии Y на Х . Параметры а и b называются коэффициентами степенной регрессии .

ln =lna +b· lnx . (4.11)

Это уравнение описывает прямую на плоскости с логарифмическими координатными осями lnx и ln . Поэтому критерием применимости степенной регрессии служит требование того, чтобы точки логарифмов эмпирических данных lnx i и lnу i находились ближе всего к прямой (4.11).

Показательная регрессия.

Показательной (или экспоненциальной ) зависимостью величины Y от величины Х называется зависимость вида:

(или ). (4.12)

Это уравнение называется уравнением показательной (или экспоненциальной ) регрессии Y на Х . Параметры а (или k ) и b называются коэффициентами показательной (или экспоненциальной ) регрессии .

Если прологарифмировать обе части уравнения степенной регрессии, то получится уравнение

ln =x· lna +lnb (или ln =k·x +lnb ). (4.13)

Это уравнение описывает линейную зависимость логарифма одной величины ln от другой величины x . Поэтому критерием применимости степенной регрессии служит требование того, чтобы точки эмпирических данных одной величины x i и логарифмы другой величины lnу i находились ближе всего к прямой (4.13).

Логарифмическая регрессия.

Логарифмической зависимостью величины Y от величины Х называется зависимость вида:

=a +b· lnx . (4.14)

Это уравнение называется уравнением логарифмической регрессии Y на Х . Параметры а и b называются коэффициентами логарифмической регрессии .

Гиперболическая регрессия.

Гиперболической зависимостью величины Y от величины Х называется зависимость вида:

Это уравнение называется уравнением гиперболической регрессии Y на Х . Параметры а и b называются коэффициентами гиперболической регрессии и определяются методом наименьших квадратов. Применение этого метода приводит к формулам:

В формулах (4.16-4.17) суммирование проводится по индексу i от единицы до количества наблюдений n .

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

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

Подключение пакета анализа

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

1. Перемещаемся во вкладку «Файл».
2. Переходим в раздел «Параметры».
3. Открывается окно параметров Excel. Переходим в подраздел «Надстройки».
4. В самой нижней части открывшегося окна переставляем переключатель в блоке «Управление» в позицию «Надстройки Excel», если он находится в другом положении. Жмем на кнопку «Перейти».
5. Открывается окно доступных надстроек Эксель. Ставим галочку около пункта «Пакет анализа». Жмем на кнопку «OK».

Теперь, когда мы перейдем во вкладку «Данные», на ленте в блоке инструментов «Анализ» мы увидим новую кнопку – «Анализ данных».

Виды регрессионного анализа

Существует несколько видов регрессий:

• параболическая;
• степенная;
• логарифмическая;
• экспоненциальная;
• показательная;
• гиперболическая;
• линейная регрессия.

О выполнении последнего вида регрессионного анализа в Экселе мы подробнее поговорим далее.

Линейная регрессия в программе Excel

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

Общее уравнение регрессии линейного вида выглядит следующим образом: У = а0 + а1х1 +…+акхк. В этой формуле Y означает переменную, влияние факторов на которую мы пытаемся изучить. В нашем случае, это количество покупателей. Значение x – это различные факторы, влияющие на переменную. Параметры a являются коэффициентами регрессии. То есть, именно они определяют значимость того или иного фактора. Индекс k обозначает общее количество этих самых факторов.

Разбор результатов анализа

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

Одним из основных показателей является R-квадрат. В нем указывается качество модели. В нашем случае данный коэффициент равен 0,705 или около 70,5%. Это приемлемый уровень качества. Зависимость менее 0,5 является плохой.

Ещё один важный показатель расположен в ячейке на пересечении строки «Y-пересечение» и столбца «Коэффициенты». Тут указывается какое значение будет у Y, а в нашем случае, это количество покупателей, при всех остальных факторах равных нулю. В этой таблице данное значение равно 58,04.

Значение на пересечении граф «Переменная X1» и «Коэффициенты» показывает уровень зависимости Y от X. В нашем случае - это уровень зависимости количества клиентов магазина от температуры. Коэффициент 1,31 считается довольно высоким показателем влияния.

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

Мы рады, что смогли помочь Вам в решении проблемы.

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

Помогла ли вам эта статья?

Метод линейной регрессии позволяет нам описывать прямую линию, максимально соответствующую ряду упорядоченных пар (x, y). Уравнение для прямой линии, известное как линейное уравнение, представлено ниже:

ŷ - ожидаемое значение у при заданном значении х,

x - независимая переменная,

a - отрезок на оси y для прямой линии,

b - наклон прямой линии.

На рисунке ниже это понятие представлено графически:

На рисунке выше показана линия, описанная уравнением ŷ =2+0.5х. Отрезок на оси у - это точка пересечения линией оси у; в нашем случае а = 2. Наклон линии, b, отношение подъема линии к длине линии, имеет значение 0.5. Положительный наклон означает, что линия поднимается слева направо. Если b = 0, линия горизонтальна, а это значит, что между зависимой и независимой переменными нет никакой связи. Иными словами, изменение значения x не влияет на значение y.

Часто путают ŷ и у. На графике показаны 6 упорядоченных пар точек и линия, в соответствии с данным уравнением

На этом рисунке показана точка, соответствующая упорядоченной паре х = 2 и у = 4. Обратите внимание, что ожидаемое значение у в соответствии с линией при х = 2 является ŷ. Мы можем подтвердить это с помощью следу­ющего уравнения:

ŷ = 2 + 0.5х =2 +0.5(2) =3.

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

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

Использование Excel для определения линейной регрессии

Для того, чтобы воспользоваться инструментом регрессионного анализа встроенного в Excel, необходимо активировать надстройку Пакет анализа . Найти ее можно, перейдя по вкладке Файл –> Параметры (2007+), в появившемся диалоговом окне Параметры Excel переходим во вкладку Надстройки. В поле Управление выбираем Надстройки Excel и щелкаем Перейти. В появившемся окне ставим галочку напротив Пакет анализа, жмем ОК.

Во вкладке Данные в группе Анализ появится новая кнопка Анализ данных.

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

Перейдите во вкладку Данные, в группе Анализ щелкните Анализ данных. В появившемся окне Анализ данных выберите Регрессия , как показано на рисунке, и щелкните ОК.

Установите необходимыe параметры регрессии в окне Рег­рессия , как показано на рисунке:

Щелкните ОК. На рисунке ниже показаны полученные результаты:

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

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

Виды регрессии

Само это понятие было введено в математику Фрэнсисом Гальтоном в 1886 году. Регрессия бывает:

• линейной;
• параболической;
• степенной;
• экспоненциальной;
• гиперболической;
• показательной;
• логарифмической.

Пример 1

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

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

Для задачи определения зависимости количества уволившихся работников от средней зарплаты на 6 предприятиях модель регрессии имеет вид уравнения Y = а0 + а1×1 +…+аkxk, где хi - влияющие переменные, ai - коэффициенты регрессии, a k - число факторов.

Для данной задачи Y - это показатель уволившихся сотрудников, а влияющий фактор - зарплата, которую обозначаем X.

Использование возможностей табличного процессора «Эксель»

Анализу регрессии в Excel должно предшествовать применение к имеющимся табличным данным встроенных функций. Однако для этих целей лучше воспользоваться очень полезной надстройкой «Пакет анализа». Для его активации нужно:

• с вкладки «Файл» перейти в раздел «Параметры»;
• в открывшемся окне выбрать строку «Надстройки»;
• щелкнуть по кнопке «Перейти», расположенной внизу, справа от строки «Управление»;
• поставить галочку рядом с названием «Пакет анализа» и подтвердить свои действия, нажав «Ок».

Если все сделано правильно, в правой части вкладки «Данные», расположенном над рабочим листом «Эксель», появится нужная кнопка.

Линейная регрессия в Excel

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

• щелкаем по кнопке «Анализ данных»;
• в открывшемся окне нажимаем на кнопку «Регрессия»;
• в появившуюся вкладку вводим диапазон значений для Y (количество уволившихся работников) и для X (их зарплаты);
• подтверждаем свои действия нажатием кнопки «Ok».

В результате программа автоматически заполнит новый лист табличного процессора данными анализа регрессии. Обратите внимание! В Excel есть возможность самостоятельно задать место, которое вы предпочитаете для этой цели. Например, это может быть тот же лист, где находятся значения Y и X, или даже новая книга, специально предназначенная для хранения подобных данных.

Анализ результатов регрессии для R-квадрата

В Excel данные полученные в ходе обработки данных рассматриваемого примера имеют вид:

Прежде всего, следует обратить внимание на значение R-квадрата. Он представляет собой коэффициент детерминации. В данном примере R-квадрат = 0,755 (75,5%), т. е. расчетные параметры модели объясняют зависимость между рассматриваемыми параметрами на 75,5 %. Чем выше значение коэффициента детерминации, тем выбранная модель считается более применимой для конкретной задачи. Считается, что она корректно описывает реальную ситуацию при значении R-квадрата выше 0,8. Если R-квадрата tкр, то гипотеза о незначимости свободного члена линейного уравнения отвергается.

В рассматриваемой задаче для свободного члена посредством инструментов «Эксель» было получено, что t=169,20903, а p=2,89Е-12, т. е. имеем нулевую вероятность того, что будет отвергнута верная гипотеза о незначимости свободного члена. Для коэффициента при неизвестной t=5,79405, а p=0,001158. Иными словами вероятность того, что будет отвергнута верная гипотеза о незначимости коэффициента при неизвестной, равна 0,12%.

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

Задача о целесообразности покупки пакета акций

Множественная регрессия в Excel выполняется с использованием все того же инструмента «Анализ данных». Рассмотрим конкретную прикладную задачу.

Руководство компания «NNN» должно принять решение о целесообразности покупки 20 % пакета акций АО «MMM». Стоимость пакета (СП) составляет 70 млн американских долларов. Специалистами «NNN» собраны данные об аналогичных сделках. Было принято решение оценивать стоимость пакета акций по таким параметрам, выраженным в миллионах американских долларов, как:

• кредиторская задолженность (VK);
• объем годового оборота (VO);
• дебиторская задолженность (VD);
• стоимость основных фондов (СОФ).

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

Решение средствами табличного процессора Excel

Прежде всего, необходимо составить таблицу исходных данных. Она имеет следующий вид:

• вызывают окно «Анализ данных»;
• выбирают раздел «Регрессия»;
• в окошко «Входной интервал Y» вводят диапазон значений зависимых переменных из столбца G;
• щелкают по иконке с красной стрелкой справа от окна «Входной интервал X» и выделяют на листе диапазон всех значений из столбцов B,C, D, F.

Отмечают пункт «Новый рабочий лист» и нажимают «Ok».

Получают анализ регрессии для данной задачи.

Изучение результатов и выводы

«Собираем» из округленных данных, представленных выше на листе табличного процессора Excel, уравнение регрессии:

СП = 0,103*СОФ + 0,541*VO – 0,031*VK +0,405*VD +0,691*VZP – 265,844.

В более привычном математическом виде его можно записать, как:

y = 0,103*x1 + 0,541*x2 – 0,031*x3 +0,405*x4 +0,691*x5 – 265,844

Данные для АО «MMM» представлены в таблице:

Подставив их в уравнение регрессии, получают цифру в 64,72 млн американских долларов. Это значит, что акции АО «MMM» не стоит приобретать, так как их стоимость в 70 млн американских долларов достаточно завышена.

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

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

Статистическая обработка данных может также проводиться с помощью надстройки ПАКЕТ АНАЛИЗА (рис. 62).

Из предложенных пунктов выбирает пункт «РЕГРЕССИЯ » и щелкаем на нем левой кнопкой мыши. Далее нажимаем ОК.

Появится окно, показанное на рис. 63.

Инструмент анализа «РЕГРЕССИЯ » применяется для подбора графика для набора наблюдений с помощью метода наименьших квадратов. Регрессия используется для анализа воздействия на отдельную зависимую переменную значений одной или нескольких независимых переменных. Например, на спортивные качества атлета влияют несколько факторов, включая возраст, рост и вес. Можно вычислить степень влияния каждого из этих трех факторов по результатам выступления спортсмена, а затем использовать полученные данные для предсказания выступления другого спортсмена.

Инструмент «Регрессия» использует функцию ЛИНЕЙН .

Диалоговое окно «РЕГРЕССИЯ»

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

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

Константа - ноль Установите флажок, чтобы линия регрессии прошла через начало координат.

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

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

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

Остатки Установите флажок для включения остатков в выходную таблицу.

Стандартизированные остатки Установите флажок для включения стандартизированных остатков в выходную таблицу.

График остатков Установите флажок для построения графика остатков для каждой независимой переменной.

График подбора Установите флажок для построения графика зависимости предсказанных значений от наблюдаемых.

График нормальной вероятности Установите флажок, для построения графика нормальной вероятности.

Функция ЛИНЕЙН

Для проведения расчетов выделяем курсором ячейку, в которой хотим отобразить среднее значение и нажимаем на клавиатуре клавишу =. Далее в поле Имя указываем нужную функцию, например СРЗНАЧ (рис. 22).

Функция ЛИНЕЙН рассчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные и затем возвращает массив, который описывает полученную прямую. Можно также объединять функцию ЛИНЕЙН с другими функциями для вычисления других видов моделей, являющихся линейными в неизвестных параметрах (неизвестные параметры которых являются линейными), включая полиномиальные, логарифмические, экспоненциальные и степенные ряды. Поскольку возвращается массив значений, функция должна задаваться в виде формулы массива.

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

y=m 1 x 1 +m 2 x 2 +…+b (в случае нескольких диапазонов значений x),

где зависимое значение y - функция независимого значения x, значения m - коэффициенты, соответствующие каждой независимой переменной x, а b - постоянная. Обратите внимание, что y, x и m могут быть векторами. Функция ЛИНЕЙН возвращает массив{mn;mn-1;…;m 1 ;b}. ЛИНЕЙН может также возвращать дополнительную регрессионную статистику.

ЛИНЕЙН (известные_значения_y; известные_значения_x; конст; статистика)

Известные_значения_y - множество значений y, которые уже известны для соотношения y=mx+b.

Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная.

Если массив известные_значения_y имеет одну строку, то каждая строка массива известные_значения_x интерпретируется как отдельная переменная.

Известные_значения_x - необязательное множество значений x, которые уже известны для соотношения y=mx+b.

Массив известные_значения_x может содержать одно или несколько множеств переменных. Если используется только одна переменная, то массивы_известные_значения_y и известные_значения_x могут иметь любую форму - при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то известные_значения_y должны быть вектором (т. е. интервалом высотой в одну строку или шириной в один столбец).

Если массив_известные_значения_x опущен, то предполагается, что этот массив {1;2;3;...} имеет такой же размер, как и массив_известные_значения_y.

Конст - логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0.

Если аргумент «конст» имеет значение ИСТИНА или опущен, то константа b вычисляется обычным образом.

Если аргумент «конст» имеет значение ЛОЖЬ, то значение b полагается равным 0 и значения m подбираются таким образом, чтобы выполнялось соотношение y=mx.

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

Если аргумент «статистика» имеет значение ИСТИНА, функция ЛИНЕЙН возвращает дополнительную регрессионную статистику. Возвращаемый массив будет иметь следующий вид: {mn;mn-1;...;m1;b:sen;sen-1;...;se1;seb:r2;sey:F;df:ssreg;ssresid}.

Если аргумент «статистика» имеет значение ЛОЖЬ или опущен, функция ЛИНЕЙН возвращает только коэффициенты m и постоянную b.

Дополнительная регрессионная статистика.(табл.17)

Величина	Описание
se1,se2,...,sen	Стандартные значения ошибок для коэффициентов m1,m2,...,mn.
seb	Стандартное значение ошибки для постоянной b (seb = #Н/Д, если аргумент «конст» имеет значение ЛОЖЬ).
r2	Коэффициент детерминированности. Сравниваются фактические значения y и значения, получаемые из уравнения прямой; по результатам сравнения вычисляется коэффициент детерминированности, нормированный от 0 до 1. Если он равен 1, то имеет место полная корреляция с моделью, т. е. различия между фактическим и оценочным значениями y не существует. В противоположном случае, если коэффициент детерминированности равен 0, использовать уравнение регрессии для предсказания значений y не имеет смысла. Для получения дополнительных сведений о способах вычисления r2, см. «Замечания» в конце данного раздела.
sey	Стандартная ошибка для оценки y.
F	F-статистика или F-наблюдаемое значение. F-статистика используется для определения того, является ли случайной наблюдаемая взаимосвязь между зависимой и независимой переменными.
df	Степени свободы. Степени свободы полезны для нахождения F-критических значений в статистической таблице. Для определения уровня надежности модели необходимо сравнить значения в таблице с F-статистикой, возвращаемой функцией ЛИНЕЙН. Для получения дополнительных сведений о вычислении величины df см. «Замечания» в конце данного раздела. Далее в примере 4 показано использование величин F и df.
ssreg	Регрессионная сумма квадратов.
ssresid	Остаточная сумма квадратов. Для получения дополнительных сведений о расчете величин ssreg и ssresid см. «Замечания» в конце данного раздела.

На приведенном ниже рисунке показано, в каком порядке возвращается дополнительная регрессионная статистика (рис. 64).

Замечания:

Любую прямую можно описать ее наклоном и пересечением с осью y:

Наклон (m): чтобы определить наклон прямой, обычно обозначаемый через m, нужно взять две точки прямой (x 1 ,y 1) и(x 2 ,y 2); наклон будет равен (y 2 -y 1)/(x 2 -x 1).

Y-пересечение (b): Y-пересечением прямой, обычно обозначаемым через b, является значение y для точки, в которой прямая пересекает ось y.

Уравнение прямой имеет вид y=mx+b. Если известны значения m и b, то можно вычислить любую точку на прямой, подставляя значения y или x в уравнение. Можно также воспользоваться функцией ТЕНДЕНЦИЯ.

Если имеется только одна независимая переменная x, можно получить наклон и y-пересечение непосредственно, воспользовавшись следующими формулами:

Наклон: ИНДЕКС (ЛИНЕЙН(известные_значения_y; известные_значения_x); 1)

Y-пересечение: ИНДЕКС (ЛИНЕЙН (известные_значения_y; известные_значения_x); 2)

Точность аппроксимации с помощью прямой, вычисленной функцией ЛИНЕЙН, зависит от степени разброса данных. Чем ближе данные к прямой, тем более точной является модель, используемая функцией ЛИНЕЙН. Функция ЛИНЕЙН использует метод наименьших квадратов для определения наилучшей аппроксимации данных. Когда имеется только одна независимая переменная x, m и b вычисляются по следующим формулам:

где x и y – выборочные средние значения, например x = СРЗНАЧ (известные_значения_x), а y = СРЗНАЧ (известные_значения_y).

Функции аппроксимации ЛИНЕЙН и ЛГРФПРИБЛ могут вычислить прямую или экспоненциальную кривую, наилучшим образом описывающую данные. Однако они не дают ответа на вопрос, какой из двух результатов больше подходит для решения поставленной задачи. Можно также вычислить функцию ТЕНДЕНЦИЯ (известные_значения_y; известные_значения_x) для прямой или функцию РОСТ(известные_значения_y; известные_значения_x) для экспоненциальной кривой. Эти функции, если не задавать аргумент новые_значения_x, возвращают массив вычисленных значений y для фактических значений x в соответствии с прямой или кривой. После этого можно сравнить вычисленные значения с фактическими значениями. Можно также построить диаграммы для визуального сравнения.

Проводя регрессионный анализ, Microsoft Excel вычисляет для каждой точки квадрат разности между прогнозируемым значением y и фактическим значением y. Сумма этих квадратов разностей называется остаточной суммой квадратов (ssresid). Затем Microsoft Excel подсчитывает общую сумму квадратов (sstotal). Если конст = ИСТИНА или значение этого аргумента не указано, общая сумма квадратов будет равна сумме квадратов разностей действительных значений y и средних значений y. При конст = ЛОЖЬ общая сумма квадратов будет равна сумме квадратов действительных значений y (без вычитания среднего значения y из частного значения y). После этого регрессионную сумму квадратов можно вычислить следующим образом: ssreg = sstotal - ssresid. Чем меньше остаточная сумма квадратов, тем больше значение коэффициента детерминированности r2, который показывает, насколько хорошо уравнение, полученное с помощью регрессионного анализа, объясняет взаимосвязи между переменными. Коэффициент r2 равен ssreg/sstotal.

В некоторых случаях один или более столбцов X (пусть значения Y и X находятся в столбцах) не имеет дополнительного предикативного значения в других столбцах X. Другими словами, удаление одного или более столбцов X может привести к значениям Y, вычисленным с одинаковой точностью. В этом случае избыточные столбцы X будут исключены из модели регрессии. Этот феномен называется «коллинеарностью», поскольку избыточные столбцы X могут быть представлены в виде суммы нескольких неизбыточных столбцов. Функция ЛИНЕЙН проверяет на коллинеарность и удаляет из модели регрессии все избыточные столбцы X, если обнаруживает их. Удаленные столбцы X можно определить в выходных данных ЛИНЕЙН по коэффициенту, равному 0, и по значению se, равному 0. Удаление одного или более столбцов как избыточных изменяет величину df, поскольку она зависит от количества столбцов X, в действительности используемых для предикативных целей. Подробнее о вычислении величины df см. ниже в примере 4. При изменении df вследствие удаления избыточных столбцов значения sey и F также изменяются. Часто использовать коллинеарность не рекомендуется. Однако ее следует применять, если некоторые столбцы X содержат 0 или 1 в качестве индикатора указывающего, входит ли предмет эксперимента в отдельную группу. Если конст = ИСТИНА или значение этого аргумента не указано, функция ЛИНЕЙН вставляет дополнительный столбец X для моделирования точки пересечения. Если имеется столбец со значениями 1 для указания мужчин и 0 - для женщин, а также имеется столбец со значениями 1 для указания женщин и 0 - для мужчин, то последний столбец удаляется, поскольку его значения можно получить из столбца с «индикатором мужского пола».

Вычисление df для случаев, когда столбцы X не удаляются из модели вследствие коллинеарности происходит следующим образом: если существует k столбцов известных_значений_x и значение конст = ИСТИНА или не указано, то df = n – k – 1. Если конст = ЛОЖЬ, то df = n - k. В обоих случаях удаление столбцов X вследствие коллинеарности увеличивает значение df на 1.

Формулы, которые возвращают массивы, должны быть введены как формулы массива.

При вводе массива констант в качестве, например, аргумента известные_значения_x следует использовать точку с запятой для разделения значений в одной строке и двоеточие для разделения строк. Знаки-разделители могут быть различными в зависимости от параметров, заданных в окне «Язык и стандарты» на панели управления.

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

Основной алгоритм, используемый в функции ЛИНЕЙН , отличается от основного алгоритма функций НАКЛОН и ОТРЕЗОК . Разница между алгоритмами может привести к различным результатам при неопределенных и коллинеарных данных. Например, если точки данных аргумента известные_значения_y равны 0, а точки данных аргумента известные_значения_x равны 1, то:

Функция ЛИНЕЙН возвращает значение, равное 0. Алгоритм функции ЛИНЕЙН используется для возвращения подходящих значений для коллинеарных данных, и в данном случае может быть найден по меньшей мере один ответ.

Функции НАКЛОН и ОТРЕЗОК возвращают ошибку #ДЕЛ/0!. Алгоритм функций НАКЛОН и ОТРЕЗОК используется для поиска только одного ответа, а в данном случае их может быть несколько.

Помимо вычисления статистики для других типов регрессии функцию ЛИНЕЙН можно использовать при вычислении диапазонов для других типов регрессии, вводя функции переменных x и y как ряды переменных х и у для ЛИНЕЙН. Например, следующая формула:

ЛИНЕЙН(значения_y, значения_x^СТОЛБЕЦ($A:$C))

работает при наличии одного столбца значений Y и одного столбца значений Х для вычисления аппроксимации куба (многочлен 3-й степени) следующей формы:

y=m 1 x+m 2 x 2 +m 3 x 3 +b

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

По территориям региона приводятся данные за 200Х г.

Номер региона	Среднедушевой прожиточный минимум в день одного трудоспособного, руб., х	Среднедневная заработная плата, руб., у
1	78	133
2	82	148
3	87	134
4	79	154
5	89	162
6	106	195
7	67	139
8	88	158
9	73	152
10	87	162
11	76	159
12	115	173

Задание:

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

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

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

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

Решение:

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

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

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

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

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

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

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

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

Для этого:

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

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

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

Известные значения у

Известные значения х

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

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

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

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

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

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

Значение коэффициента b	Значение коэффициента a
Стандартная ошибка b	Стандартная ошибка a
Стандартная ошибка y
F-статистика
Регрессионная сумма квадратов

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

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

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

Означает, что 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-статистики Стьюдента и путём расчёта доверительного интервала каждого из показателей.

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

.

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

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

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

I способ:

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

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

II способ:

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

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

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

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

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

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

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

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

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

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

где

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

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

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

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

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

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

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

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

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