Построение линейного уравнения регрессии

Методические указания к контрольной работе

по курсу:

«Эконометрика»

 

Порядок выполнения работы

 

Общие вопросы

 

При написании контрольной работы все вычисления следует производить в табличном процессоре Excel, а оформление – в текстовом редакторе Word.

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

1. Линейная

2. Гипербола

3. Степенная

4. Показательная

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

6. Параболическая

Исходные данные необходимо разместить в столбцах А и B. При этом первая строка отводится для названий столбцов. Таким образом, значения будут находится в строках с 2 по 31. Строка 32 предназначена для вычисления автосумм значений соответствующих столбцов.

Для получения качественных графиков исходные данные необходимо отсортировать. Для чего выделяются значения фактора х (без названия!!!), находящиеся в колонке А На панели инструментов нажимается кнопка сортировки по возрастанию (). В появившемся диалоговом окне (Рис. 1) необходимо нажать кнопку [Сортировка].

 

Рис. 1.

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

 

Построение линейного уравнения регрессии

 

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

Для нахождения параметров a и b необходимо составить и решить следующую систему:

.

где n – число пар значений в исходных данных (n =30).

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

Суммы , вычисляются соответственно в ячейках А32 и В32 с использованием кнопки [ Автосумма] ([Σ]) на панели инструментов.

Квадраты значений фактора х ² вычисляются в столбце С. Для этого в ячейку С2 заносится формула: = А2^2. Данная формула распространяется на ниже лежащие ячейки путем протягивания с помощью мыши. В ячейке С32 вычисляется сумма значений этого столбца, т.е. .

Произведения х*у вычисляются в столбце D. Для этого в ячейку D2 вводится формула: =А2*В2. Данная формула распространяется на ниже лежащие ячейки путем протягивания с помощью мыши. В ячейке D32 вычисляется сумма значений этого столбца, т.е. .

На основании проведенных вычислений составляется система нормальных уравнений:

 

Эту систему необходимо решить с целью определения параметров a и b.

Для решения системы воспользуемся возможностями Excel.

Из математики известно, что решение системы линейных уравнений может быть получено, если матрицу, обратную матрице коэффициентов левых частей уравнений системы, умножить на вектор-столбец правых частей уравнений системы.

Реализуем данный подход в Excel. При этом решение будет искать в два этапа:

1. Определяем матрицу, обратную матрице коэффициентов левых частей уравнений системы, с помощью функции МОБР.

2. Определяем решение системы умножением обратной матрицы на матрицу правых частей уравнений системы с помощью МУМНОЖ.

Занесем параметры системы в ячейки диапазона А35:D36 как это показано на рис. 2.

При этом в ячейках А35:В36 будет находится матрица левых частей уравнений, а в ячейках D35:D36 – матрица-столбец правых частей.

 

Рис 2. Лист «Линейная»

 

В ячейку А38 введем функцию МОБР (Категория: Математические). Данная функция вычисляет матрицу обратную заданной. В качестве аргумента функции зададим массив ячеек А35:В36 (Рис. 3) и нажмем ОК.

 

Рис. 3.

В результате в ячейке А38 будет получено значение 0,331537. Поскольку результатом должна быть матрица, то необходимо осуществить следующие дополнительные действия. Выделяем диапазон ячеек А38:В39, на клавиатуре нажимаем клавишу F2 (при этом в ячейке А38 появится введенная ранее формула). Далее нажимается комбинация трех клавиш: Ctrl+Shift+Enter (Удерживая клавиши Ctrl и Shift, последней нажимается клавиша Enter). В результате в ячейках А38:В39 получается матрица обратная заданной.

Для получения окончательного решения необходимо полученную матрицу умножить на вектор-столбец правых частей уравнений системы. Для этого воспользуемся функцией МУМНОЖ (Категория: Математические). Введем ее в ячейку D38, зададим аргументы (при этом первым массивом должна быть обязательно обратная матрица) и нажмем ОК (рис. 4).

 

Рис. 4.

 

В результате в ячейке D38 будет получено значение 0,515082.

Поскольку решение системы должно состоят из двух значений (значения а и значения b), то необходимо осуществить следующие дополнительные действия. Выделяем диапазон ячеек D38:D39, нажимаем клавишу F2. Далее нажимается комбинация трех клавиш: Ctrl+Shift+Enter. В результате в ячейках D38:D39 получается окончательное решение: а= 0,515 и b= 3,155.

Таким образом, линейное уравнение регрессии запишется в виде:

Для построения графика и оценки качества построенного уравнения регрессии необходимо по построенному уравнению рассчитать теоретические значения результативной переменной y^ (Столбец F). В ячейку F2 вводим формулу: = $D$38+$D$39*A2. (Знаки $ отражают, то что данная ячейка является зафиксированной. Фиксирование осуществляется с помощью клавиши F4).

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

Для построения графика необходимо вызвать Мастер диаграмм, например через главное меню: Вставка ® Диаграмма … Тип и вид диаграммы выбирается в соответствии с Рис. 5.

 

Рис. 5

 

Нажимается кнопка Далее >. В появившемся окне выбирается вкладка Ряд. Нажимается кнопка Добавить. В появившиеся поля вводятся ссылки на значения фактора х и результативного признака y (Рис. 6).

 

Рис. 6.

 

В окне просмотра появляется точечный график, отражающий исходные данные - Поле корреляции. Чтобы получить на одном графике и поле корреляции и линию регрессии необходимо нажать кнопку Добавить. В появившиеся поля занести ссылки на ячейки с данными для построения линии регрессии. Значения фактора берутся те же самые, что и при построении поля корреляции, а значения результативного признака берутся не исходные, а расчетные (рис. 7).

Далее жмется кнопка Готово.

Рис. 7.

 

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

 

Пример оформления контрольной работы:

 

Исходные данные:

Вариант № ХХХ

x	y
8,82	28,72
6,01	19,31
10,96	39,72
13,99	42,35
13,85	45,85
14,58	45,77
5,14	17,42
9,07	26,74
13,63	42,08
6,39	20,61
7,45	25,08
5,45	16,74
5,32	18,01
6,64	20,85
7,20	22,53
5,17	17,01
7,85	26,39
8,43	30,97
10,54	35,88
8,57	31,19
8,72	24,29
8,56	25,46
14,10	44,21
9,66	28,68
9,26	30,27
8,04	23,86
14,76	47,28
13,07	47,45
14,91	44,37
7,56	21,48