 |
Тема: Линейная парная регрессия. Решение задач с помощью инструментов MS Excel
|
|
|
|
Лабораторная работа 1
Пример. По территориям региона приводятся данные за 20ХX г.
Требуется:
1. Построить линейное уравнение парной регрессии У по Х.
2. Рассчитать линейный коэффициент парной корреляции, коэффициент детерминации.
3. Оценить статистическую значимость уравнения регрессии в целом и отдельных параметров регрессии и корреляции с помощью F - критерия Фишера и t -критерия Стьюдента.
4. Выполнить прогноз заработной платы y при прогнозном значении среднедушевого прожиточного минимума x, составляющем 107% от среднего уровня.
Решение типовой задачи в MS Excel C помощью инструмента анализа данных Регрессия можно получить результаты регрессионной статистики, дисперсионного анализа, доверительных интервалов, остатки и графики подбора линии регрессии. Если в меню сервис еще нет команды Анализ данных, то необходимо сделать следующее. В главном меню последовательно выбираем Данные → Контекстное меню на ленте → Настройка лент ы → Надстройки → Перейти и устанавливаем «флажок» в строке Пакет анализа (рис. 1):
Рис. 1
Далее следуем по следующему плану.
1.После ввода исходных данных выбираем Данные → Анализ данных → Регрессия.
2. Заполняем диалоговое окно ввода данных и параметров вывода (рис. 2):
Рис. 2
Здесь: Входной интервал Y – диапазон, содержащий данные объясняемой переменной;
Входной интервал X – диапазон, содержащий данные объясняющей переменной;
Метки – «флажок», который указывает, содержит ли первая строка названия столбцов;
Константа – ноль – «флажок», указывающий на наличие или отсутствие свободного члена в уравнении;
Выходной интервал – достаточно указать левую верхнюю ячейку будущего диапазона;
|
|
|
Новый рабочий лист – можно указать произвольное имя нового листа (или не указывать, тогда результаты выводятся на вновь созданный лист).
Получаем следующие результаты для рассмотренного выше примера:
Рис. 3
Описание первой таблицы:
Множественный R – коэффициент множественной корреляции.
R-квадрат – коэффициент детерминации.
Нормированный R-квадрат – скорректированный коэффициент детерминации.
Стандартная ошибка – среднее квадратическое отклонение.
Описание второй таблицы
Описание третьей таблицы
Данные первой строки относятся к свободному члену уравнения регрессии b0. Данные второй строки относятся к коэффициенту уравнения регрессии b1. Данные третьей строки относятся к коэффициенту уравнения регрессии b2 . И так далее в зависимости от количества объясняющих пременных.
Столбцы таблицы:
Первый столбец – названия коэффициентов.
Второй столбец значения коэффициентов уравнения регрессии.
Третий столбец – стандартная ошибка коэффициента уравнения регрессии (среднее квадратическое отклонение).
Четвертый столбец - t-статистика для соответствующего коэффициента уравнения регрессии.
Из таблиц выписываем, округляя до 4 знаков после запятой и переходя к нашим обозначениям:
Уравнение регрессии: 
Коэффициент корреляции 0.7210
Коэффициент детерминации: 0.5199.
Фактическое значение F -критерия Фишера: F =10,8280
Фактические значения t -критерия Стьюдента:
tb1=3,1793, tb2 =3,2906.
Решить задачи в Excel. Оформить лабораторную работу.
1. Имеются следующие данные об уровне механизации работ Х (%) и производительности труда У (т/ч) для четырнадцати однотипных предприятий:
| хi | ||||||||||||||
| yi |
а) Оценить тесноту и направление связи между переменными с помощью коэффициента корреляции.
|
|
|
б) Найти уравнение регрессии У по Х.
2. По территориям Калининградской области за 2015 год известны значения двух признаков:
| Район | Расходы на покупку продовольственных товаров в общих расходах, % (у) | Среднедневная заработная плата одного работающего, руб. (х) |
| Калининград | 68,8 | 45,1 |
| Гурьевский | 61,2 | 59,0 |
| Зеленоградский | 59,9 | 57,2 |
| Советский | 56,7 | 61,8 |
| Светлогорский | 55,0 | 58,8 |
| Нестеровский | 54,3 | 47,2 |
| Балтийский | 49,3 | 55,2 |
Найдите уравнение регрессии У по Х и оцените тесноту и направление связи между переменными с помощью коэффициента корреляции.
3. Имеются данные за 10 лет по прибылям Х и У (в % к предыдущему году) двух компаний:
| Х | 19,2 | 15,8 | 12,5 | 10,3 | 5,7 | -5,8 | -3,5 | 5,2 | 7,3 | 6,7 |
| У | 20,1 | 18,0 | 10,3 | 12,5 | 6,0 | -6,8 | -2,8 | 3,0 | 8,5 | 8,0 |
1) Постройте регрессионную модель У по Х.
2) Оцените тесноту и направление связи.
4. Решить свой вариант контрольной работы.
Домашнее задание:
Имеется информация за 7 лет относительно среднего дохода и среднего потребления (млн. руб.):
| Доход (t) | 14,56 | 15,70 | 16,30 | 18,50 | 20,34 | 21,70 | 23,50 |
| Потребление (С) | 12,00 | 12,70 | 13,00 | 15,50 | 16,70 | 17,30 | 20,00 |
Оценить тесноту и направление связи между переменными С и t с помощью коэффициента корреляции и построить уравнение регрессии С по t.
|
|
|
