На примере задачи

Лабораторная работа №6 - Линейная регрессия

Цель: Построение и анализ качества модели парной линейной регрессии. Точечный и интервальный прогнозы по модели парной линейной регрессии. Стандартная ошибка точечного прогноза.

Расчеты произвести средствами Microsoft Excel.

Лабораторная составлена по материалам http://edu.dvgups.ru/METDOC/ENF/PRMATEM/EKONOMETRIKA/METOD/LAB_EXCEL/Ivanov_5.htm

На примере задачи

Предпринимателем даны сведения о товарообороте (млн руб.) и числе работников, представленные в таблице.

Товарооборот, млн руб.	0.2	0.7	0.9	1.1	1.4	1.4	1.7	1.9
Число работников

Необходимо исследовать зависимость розничного товарооборота магазинов от числа занятых (числа работников) и дать рекомендации о целесообразности принятия на работу новых работников, то есть необходимо:

1) произвести идентификацию модели парной линейной регрессии;

2) рассчитать общую, факторную и остаточную дисперсии;

3) вычислить коэффициент детерминации;

4) вычислить среднюю ошибку аппроксимации;

5) вычислить стандартную ошибку регрессии;

6) вычислить стандартные ошибки параметров регрессии;

7) проверить гипотезу о наличии регрессионной зависимости при уровне значимости равном 0.05;

8) произвести интервальное оценивание параметров регрессионной модели;

9) осуществить точечный прогноз (только в случае качественной модели) при значении фактора, равного 120 % от среднего числа работников;

10) определить стандартную ошибку точечного прогноза;

11) осуществить интервальный прогноз, при значении фактора равного 120 % от среднего числа работников;

12) изобразить графически парную линейную регрессию и исходные данные.

1. Ознакомьтесь с теорией о регрессионном анализе.
2. Откройте документ Excel "Лабораторная работа 6.xls" и выберите вариант задания.

1. Создайте на втором листе таблицу данных из вашего варианта.

1. Будем строить модель парной линейной регрессии вида y = a₀ + a₁ * x. Где y – товарооборот, x – число работников, a₀ и a₁ – коэффициенты уравнения регрессии. Найдем оценки параметров модели a₀ и a₁

Штрих сверху означает среднее значение. Вычислите средние значений x и y с помощью функции СРЗНАЧ.

1. Вычислите значения z_i=xi-«x-среднее»

1. Добавьте вычисления коэффициентов a1 с помощью функции СУММПРОИЗВ

1. Добавьте вычисление коэффициентся a0 = «y-среднее» - «a1» * «x-среднее»

1. Создайте колонку «y-оценка» = a0 + a1 * x

1. Отобразите на точечном графике фактический и оценочный товаооборот в зависимости от числа работников

1. Для нахождения общей, факторной и остаточной дисперсии дополним расчетную таблицу еще тремя столбцами: общей, факторной и остаточной суммами квадратов.
   В столбец «общая сумма квадратов» вставляем формулу СТЕПЕНЬ(y – «y-среднее»;2).
   В столбец «факторная сумма квадратов» вставляем формулу СТЕПЕНЬ («y-оценка» – «y-среднее»;2). В столбец «остаточная сумма квадратов» вставляем формулу СТЕПЕНЬ(y – «y-оценка»;2).
   Вместо СТЕПЕНЬ можно использовать оператор возведения в степень ^.
   Суммированием по трем столбцам находим соответственно общую, факторную и остаточную суммы квадратов. Используем функцию СУММ.

1. Определяем число степеней свободы соответственно общей, факторной и остаточной сумм квадратов.
   Число степеней свободы общей суммы квадратов: n -1 = 8-1 = 7
   Число степеней свободы факторной суммы квадратов: k =1
   Число степеней свободы остаточной суммы квадратов: n -2 = 8-2 = 6
   Где n – количество исходных данных, объем выборки (для нашей задачи, так как имеем всего 8 пар результатов наблюдений за показателями); k – число факторов (для нашей задачи, так исследуем влияние только числа работников на товарооборот).
2. Дисперсии находим делением соответствующих сумм квадратов на соответствующие им числа степеней свободы.
3. Вычисляем коэффициент детерминации R² по формуле: 1 – «остаточная сумма квадратов»/«общая сумма квадратов»

1. Для нахождения средней ошибки аппроксимации припишем еще один столбец к расчетной таблице: Аппроксимация = ABS((y – «y-оценка»)/ y).
   Суммируем все значения столбца «Аппроксимация», используя функцию СУММ.

1. Вычисляем среднюю ошибку аппроксимации по формуле: 1/ n *«сумма модулей столбца аппроксимация»*100%.
   Вычисляем стандартную ошибку регрессии по формуле: КОРЕНЬ(1/(n -2)*«остаточная сумма квадратов»)

1. Вычислим стандартные ошибки параметров регрессии по формулам:

=КОРЕНЬ(1/(n *(n -2)) * «остаточная сумма квадратов» * СУММПРОИЗВ («диапазон значений x»; «диапазон значений x») / СУММПРОИЗВ ((«диапазон значений x» – «x-среднее в фиксированной ячейке»); («диапазон значений x» – «x-среднее в фиксированной ячейке»)))

=КОРЕНЬ(1/(n -2) *«остаточная сумма квадратов» / СУММПРОИЗВ ((«диапазон значений x» – «x-среднее в фиксированной ячейке»); («диапазон значений x» – «x-среднее в фиксированной ячейке»)))

1. Проверим гипотезу о наличии регрессионной зависимости, то есть проверим статистическую значимость параметра регрессии a1 (коэффициента регрессии).
   Строим статистику: t_ф = ABS(«параметр регрессии a1»/«стандартная ошибка a1»)
   Находим t_т квантиль распределения Стьюдента с n -2 = 8-2 = 6 степенями свободы при уровне значимости, равном 0.05. Используем функцию СТЬЮДРАСПОБР или СТЬЮДЕНТ.ОБР.2Х.
   Делаем вывод о принятии гипотезы: ЕСЛИ(t_ф >=«t_т»;"отвергается и принимается альтернативная гипотеза, следовательно, коэффициент уравнения парной линейной регрессии a1 статистически значим, то есть регрессионная зависимость существует.";"принимается, это означает, что фактор x не связан линейно с зависимой переменной y, то есть регрессионная зависимость отсутствует.")

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

= «оценка параметра a0 уравнения регрессии» – «t -квантиль» * «стандартная ошибка параметра уравнения регрессии a0»

= «оценка параметра a0 уравнения регрессии» + «t -квантиль» * «стандартная ошибка параметра уравнения регрессии a0»

= «оценка параметра a1 уравнения регрессии» – «t -квантиль» * «стандартная ошибка параметра уравнения регрессии a1»

= «оценка параметра a1 уравнения регрессии» + «t -квантиль» * «стандартная ошибка параметра уравнения регрессии a1»

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

20. Вычислим значение фактора, равное 120% от среднего числа работников по формуле: x120% = «x-среднее»*120%
Так как число работников не может быть дробным значением, то будем использовать число, округленное до целого с помощью ОКРУГЛ.

21. y-прогнозное вычисляем по формуле =«оценка параметра a0» + «оценка параметра a1» * «округленное значение фактора x120%»

22. Вычислим стандартную ошибку точечного прогноза y-прогнозное по формуле:

=«стандартная ошибка регрессии»* КОРЕНЬ(1/ n +СТЕПЕНЬ((«округленное значение фактора» – «x-среднее»);2)/СУММПРОИЗВ((«диапазон значений x» – «x-среднее в фиксированной ячейке»);(«диапазон значений x» – «x-среднее в фиксированной ячейке»)))

23. Осуществим интервальный прогноз при значении фактора, равного 120 % от среднего числа работников. Доверительный интервал для истинного точечного прогноза есть интервал вида

= «y-прогнозное» – «t -квантиль» * «стандартная ошибка точечного прогноза y-прогнозное»

= «y-прогнозное» + «t -квантиль» * «стандартная ошибка точечного прогноза y-прогнозное»

24. Поместите прогнозное значение на график

25. Добавьте на график линию регрессии для фактических данных, рассчитываемую автоматически средствами Excel.
Для этого, нужно выбрать ряд на графике (кликнуть левой кнопкой мышки на одну из точек фактических данных). Далее, правой кнопкой мышки вызвать контекстное меню и выбрать команду «Добавить линию тренда», по умолчанию добавиться линейный тренд.
Чтобы посмотреть коэффициент детерминации и уравнение регрессии нужно выбрать линию тренда на графике. Далее, правой кнопкой мышки вызвать контекстное меню и выбрать команду «Формат линии тренда». В отрывшемся окне выбрать закладку «Параметры линии тренда» и включить опции «показывать уравнение на диаграмме» и «поместить на диаграмму величину достоверности аппроксимации R^2».
Сравните ваше уравнение регрессии с рассчитанным автоматически.