Иванов А.Н. Построение эконометрических моделей и прогнозирование в MS Excel: сборник лабораторных работ

Подождите немного. Документ загружается.

Тема лабораторной работы: Метод наименьших квадратов.

Аппроксимация линейной, квадратичной, показательной функций. Средняя ошибка аппроксимации.

Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич

Кафедра «Прикладная математика» ДВГУПС

5из25

3. Записываем уравнение линейной регрессии.

Уравнение линейной регрессии имеет вид:

y 999997770,099999990000084,20000000

= .

4. Определим систему нормальных уравнений для нахождения оценок па-

раметров квадратичной регрессии:

210

xaxaay ++= .

⎪

⎩

⎪

⎨

⎧

=++

∑∑∑∑

∑∑∑

====

===

xyxaxaxa

yxaxana

4.1. В целях удобства расчетов представим таблицу исходных данных

следующим образом (рис. 2.6), и дополним ее еще пятью расчетными столб-

цами:

y и yx

Год

Уровень

безработицы, x

Уровень

преступности, y

12345678

1 1991 0,5 4,25 0,25 0,125 0,063 2,125 1,063

2 1992 1,2 4,32 1,44 1,728 2,074 5,184 6,221

3 1993 2 4,4 4 8 16 8,8 17,6

4 1994 3,1 4,51 9,61 29,79 92,35 13,98 43,34

5 1995 4 4,6 16 64 256 18,4 73,6

6 1996 5,2 4,72 27,04 140,6 731,2 24,54 127,6

7 1997 5,9 4,79 34,81 205,4 1212 28,26 166,7

№

п/п

Рис. 2.6

4.2. В верхние ячейки столбцов 4, 5, 6 введем соответственно формулы и

автоматически заполним столбцы (протягиванием ячейки с формулой на об-

ласть заполнения):

=СТЕПЕНЬ(«верхняя ячейка столбца x»;2)

Тема лабораторной работы: Метод наименьших квадратов.

Аппроксимация линейной, квадратичной, показательной функций. Средняя ошибка аппроксимации.

Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич

Кафедра «Прикладная математика» ДВГУПС

6из25

=СТЕПЕНЬ(«верхняя ячейка столбца x»;3)

=СТЕПЕНЬ(«верхняя ячейка столбца x»;4)

Вызов функции: MS Excel - Вставка - Функция… - Математические

4.3. В верхнюю ячейку столбца 7 введем формулу и автоматически за-

полним весь столбец (протягиванием ячейки с формулой на область заполне-

ния):

=«верхняя ячейка столбца x»*«верхняя ячейка столбца y»

4.4. В верхнюю ячейку столбца 8 введем формулу и автоматически за

полним весь столбец (протягиванием ячейки с формулой на область заполне-

ния):

=«верхняя ячейка столбца 4»*«верхняя ячейка столбца y»

4.5. Просуммируем значения столбцов 2, 3, 4, 5, 6, 7, 8 с помощью функ-

ции СУММ, а результат суммирования запишем под столбцом с соответст-

вующими данными (рис. 2.7).

Год

Уровень

безработицы, x

Уровень

преступности, y

12345678

1 1991 0,5 4,25 0,25 0,125 0,063 2,125 1,063

2 1992 1,2 4,32 1,44 1,728 2,074 5,184 6,221

3 1993 2 4,4 4 8 16 8,8 17,6

4 1994 3,1 4,51 9,61 29,79 92,35 13,98 43,34

5 1995 4 4,6 16 64 256 18,4 73,6

6 1996 5,2 4,72 27,04 140,6 731,2 24,54 127,6

7 1997 5,9 4,79 34,81 205,4 1212 28,26 166,7

21,9 31,59 93,15 449,6 2309 101,3 436,2

№

п/п

Сумма

Рис. 2.7

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

⎪

⎩

⎪

⎨

⎧

=++

.193,43639,2309631,44915,93

,295,101631,44915,939,21

,59,3115,939,217

210

aaa

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

Тема лабораторной работы: Метод наименьших квадратов.

Аппроксимация линейной, квадратичной, показательной функций. Средняя ошибка аппроксимации.

Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич

Кафедра «Прикладная математика» ДВГУПС

7из25

5.1. Составим исходную табличную модель для решения системы линей-

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

(рис. 2.8).

012

7 21,9 93,15 208,2 = 31,59

21,9 93,15 449,631 992,412 = 101,295

93,15 449,631 2309,39 5068,411 = 436,193

Свободные члены

исходной системы

Переменные

Матрица коэффициентов

исходной системы

Значения левых

частей уравнений

Рис. 2.8

5.2. В блок «Переменные» в первую строку записываем переменные сис-

темы алгебраических уравнений.

5.3. В блок «Переменные» во вторую строку записываем произвольные

числовые значения (удобнее в качестве числовых значений поставить номера

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

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

5.4. В блок «Матрица коэффициентов исходной

системы» записываем со-

ответствующую матрицу коэффициентов при переменных

a ,

a .

5.5. В блок «Значения левых частей уравнений» в верхнюю ячейку вво-

дим формулу:

=СУММПРОИЗВ(«фиксированный диапазон строки значений пере-

менных

a ,

a »;«диапазон первой строки матрицы коэффициентов

исходной системы»)

5.6. Автоматически заполняем весь столбец «Значения левых частей

уравнений».

5.7. В блок «Свободные члены исходной системы» в столбец записываем

значения правой части исходной системы.

5.8. Вызываем Поиск решения и заполняем форму:

Установить целевую ячейку – ничего не ставить;

Равной – максимальному значению;

Изменяя ячейки – диапазон строки значений

переменных;

Ограничения – диапазон «Значения левых частей уравнений» = диа-

пазон «Свободные члены исходной системы».

5.8.1. Заполнить форму Результаты поиска решений:

поставить опцию Сохранить найденное решение;

нажать ОК.

Тема лабораторной работы: Метод наименьших квадратов.

Аппроксимация линейной, квадратичной, показательной функций. Средняя ошибка аппроксимации.

Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич

Кафедра «Прикладная математика» ДВГУПС

8из25

Результат выполнения команды Поиск решения… будет следующий

(рис. 2.9).

4,200 0,100 0,000

7 21,9 93,15 31,59 = 31,59

21,9 93,15 449,631 101,295 = 101,295

93,15 449,631 2309,39 436,193 = 436,193

Переменные

Матрица коэффициентов

исходной системы

Значения левых

частей уравнений

Свободные члены

исходной системы

Рис. 2.9

5.9. Изменить формат ячеек с полученным решением (строка значений

переменных) так, чтобы было три знака после запятой.

6. Записываем уравнение квадратичной регрессии.

Уравнение квадратичной регрессии имеет вид:

.38691442480000017387,0

110571000111258,049621999883297,4

−

7. Определим систему нормальных уравнений для нахождения оценок па-

раметров показательной регрессии:

eay

;

xaay

lnln +=

;

ln ;

ln ba =

;

ba = ;

⎪

⎩

⎪

⎨

⎧

∑∑∑

∑∑

===

xxbxb

xbnb

7.1. В целях удобства расчетов представим таблицу исходных данных

(рис. 2.10), которую дополним ещё тремя расчетными столбцами:

7.2. В верхнюю ячейку столбца 4 введем формулу и автоматически за-

полним столбец (протягиванием ячейки с формулой на область заполнения):

=СТЕПЕНЬ(«верхняя ячейка столбца x»;2)

7.3. В верхнюю ячейку столбца 5 введем формулу и автоматически за-

полним весь столбец (протягиванием ячейки с формулой на область заполне-

ния):

Тема лабораторной работы: Метод наименьших квадратов.

Аппроксимация линейной, квадратичной, показательной функций. Средняя ошибка аппроксимации.

Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич

Кафедра «Прикладная математика» ДВГУПС

9из25

=LN(«верхняя ячейка столбца y»)

Год

Уровень

безработицы, x

Уровень

преступности, y

φφx

123456

1 1991 0,5 4,25 0,25 1,447 0,723

2 1992 1,2 4,32 1,44 1,463 1,756

3 1993 2 4,4 4 1,482 2,963

4 1994 3,1 4,51 9,61 1,506 4,67

5 1995 4 4,6 16 1,526 6,104

6 1996 5,2 4,72 27,04 1,552 8,069

7 1997 5,9 4,79 34,81 1,567 9,243

№

п/п

Рис. 2.10

7.4. В верхнюю ячейку столбца 6 введем формулу и автоматически за-

полним весь столбец (протягиванием ячейки с формулой на область заполне-

ния):

=«верхняя ячейка столбца 5»*«верхняя ячейка столбца x»

7.5. Просуммируем значения столбцов 2, 4, 5, 6 с помощью функции

СУММ, а результат суммирования запишем под столбцом с соответствую-

щими данными (рис. 2.11).

Год

Уровень

безработицы, x

Уровень

преступности, y

φφx

123456

1 1991 0,5 4,25 0,25 1,447 0,723

2 1992 1,2 4,32 1,44 1,463 1,756

3 1993 2 4,4 4 1,482 2,963

4 1994 3,1 4,51 9,61 1,506 4,67

5 1995 4 4,6 16 1,526 6,104

6 1996 5,2 4,72 27,04 1,552 8,069

7 1997 5,9 4,79 34,81 1,567 9,243

21,9 93,15 10,54 33,53

Сумма

№

п/п

Рис. 2.11

Тема лабораторной работы: Метод наименьших квадратов.

Аппроксимация линейной, квадратичной, показательной функций. Средняя ошибка аппроксимации.

Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич

Кафедра «Прикладная математика» ДВГУПС

10из25

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

⎩

⎨

⎧

.5283,3315,939,21

,5425,109,217

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

8.1. Составим исходную табличную модель для решения системы линей-

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

(рис. 2.12).

7 21,9 21,9 = 10,5425

21,9 93,15 93,15 = 33,5283

Переменные

Матрица

коэффициентов

исходной

системы

Значения левых

частей уравнений

Свободные члены

исходной системы

Рис. 2.12

8.2. В блок «Переменные» в первую строку записываем переменные сис-

темы алгебраических уравнений.

8.3. В блок «Переменные» во вторую строку записываем произвольные

числовые значения (удобнее в качестве числовых значений поставить номера

переменных), в дальнейшем, после выполнения команды Поиск решения..., в

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

8.4. В блок «Матрица коэффициентов

исходной системы» записываем со-

ответствующую матрицу коэффициентов при переменных

b ,

b .

8.5. В блок «Значения левых частей уравнений» в верхнюю ячейку вво-

дим формулу:

=СУММПРОИЗВ(«фиксированный диапазон строки значений пере-

менных

b ,

b »;«диапазон первой строки матрицы коэффициентов ис-

ходной системы»)

8.6. Автоматически заполняем весь столбец «Значения левых частей

уравнений».

8.7. В блок «Свободные члены исходной системы» в столбец записываем

значения правой части исходной системы.

8.8. Вызываем Поиск решения и заполняем форму:

Установить целевую ячейку – ничего не ставить;

Тема лабораторной работы: Метод наименьших квадратов.

Аппроксимация линейной, квадратичной, показательной функций. Средняя ошибка аппроксимации.

Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич

Кафедра «Прикладная математика» ДВГУПС

11из25

Равной – максимальному значению;

Изменяя ячейки – диапазон строки значений переменных;

Ограничения – диапазон «Значения левых частей уравнений» = диа-

пазон «Свободные члены исходной системы»;

нажать Выполнить.

8.8.1. Заполнить форму Результаты поиска решений:

поставить опцию Сохранить найденное решение;

нажать ОК.

Результат выполнения команды Поиск решения… будет следующий

(рис. 2.13).

1,437 0,022

7 21,9 10,5425 = 10,5425

21,9 93,15 33,5283 = 33,5283

Значения левых

частей уравнений

Свободные члены

исходной системы

Переменные

Матрица

коэффициентов

исходной

системы

Рис. 2.13

8.9. Изменить формат ячеек с полученным решением (строка значений

переменных) так, чтобы было три знака после запятой.

8.10. Возвращаясь к показательной функции, найдем параметры

a ,

a из

условий, использовав функцию EXP:

ea = ,

4,207

0,022

9. Записываем уравнение показательной регрессии.

Уравнение показательной регрессии имеет вид:

4,20727 =

865874520,02213724 x

ey ⋅ .

10. Определим систему нормальных уравнений для нахождения оценок

параметров гиперболической регрессии:

Тема лабораторной работы: Метод наименьших квадратов.

Аппроксимация линейной, квадратичной, показательной функций. Средняя ошибка аппроксимации.

Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич

Кафедра «Прикладная математика» ДВГУПС

12из25

⎪

⎩

⎪

⎨

⎧

∑∑∑

∑∑

===

ana

10.1. Для удобства расчетов представим таблицу исходных данных сле-

дующим образом (рис. 2.14) и дополним ее еще тремя расчетными столбца-

ми:

x1 ,

1 x , xy .

Год

Уровень

безработицы, x

Уровень

преступности, y

1/x

y/x

123456

1 1991 0,5 4,25 2 4 8,5

2 1992 1,2 4,32 0,833 0,694 3,6

3 1993 2 4,4 0,5 0,25 2,2

4 1994 3,1 4,51 0,323 0,104 1,455

5 1995 4 4,6 0,25 0,063 1,15

6 1996 5,2 4,72 0,192 0,037 0,908

7 1997 5,9 4,79 0,169 0,029 0,812

№

п/п

Рис. 2.14

10.2. В верхнюю ячейку столбца 4 введем формулу и автоматически за-

полним столбец (протягиванием ячейки с формулой на область заполнения):

=1/«верхняя ячейка столбца x»

10.3. В верхнюю ячейку столбца 5 введем формулу и автоматически за-

полним весь столбец (протягиванием ячейки с формулой на область заполне-

ния):

=1/(«верхняя ячейка столбца x»^2)

10.4. В верхнюю

ячейку столбца 6 введем формулу и автоматически за-

полним весь столбец (протягиванием ячейки с формулой на область заполне-

ния):

=«верхняя ячейка столбца y»/«верхняя ячейка столбца x»

10.5. Просуммируем значения столбцов 3, 4, 5, 6 с помощью функции

СУММ, а результат суммирования запишем под столбцом с соответствую-

щими данными (рис. 2.15).

Тема лабораторной работы: Метод наименьших квадратов.

Аппроксимация линейной, квадратичной, показательной функций. Средняя ошибка аппроксимации.

Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич

Кафедра «Прикладная математика» ДВГУПС

13из25

Год

Уровень

безработицы, x

Уровень

преступности, y

1/x

y/x

123456

1 1991 0,5 4,25 2 4 8,5

2 1992 1,2 4,32 0,833 0,694 3,6

3 1993 2 4,4 0,5 0,25 2,2

4 1994 3,1 4,51 0,323 0,104 1,455

5 1995 4 4,6 0,25 0,063 1,15

6 1996 5,2 4,72 0,192 0,037 0,908

7 1997 5,9 4,79 0,169 0,029 0,812

31,59 4,268 5,177 18,62

№

п/п

Сумма

Рис. 2.15

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

⎩

⎨

⎧

.6244,1817671,526771,4

,59,3126771,47

11. Решаем систему нормальных уравнений для гиперболической регрес-

сии.

11.1. Составим исходную табличную модель для решения системы ли-

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

(рис. 2.16).

7 4,2677 4,26771 = 31,59

4,2677 5,1767 5,17671 = 18,6244

Переменные

Матрица

коэффициенто

в исходной

системы

Значения левых

частей уравнений

Свободные

члены исходной

системы

Рис. 2.16

Тема лабораторной работы: Метод наименьших квадратов.

Аппроксимация линейной, квадратичной, показательной функций. Средняя ошибка аппроксимации.

Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич

Кафедра «Прикладная математика» ДВГУПС

14из25

11.2. В блок «Переменные» в первую строку записываем переменные сис-

темы алгебраических уравнений.

11.3. В блок «Переменные» во вторую строку записываем произвольные

числовые значения (удобнее в качестве числовых значений поставить номера

переменных), в дальнейшем, после выполнения команды Поиск решения..., в

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

11.4. В блок «Матрица коэффициентов исходной

системы» записываем

соответствующую матрицу коэффициентов при переменных

a ,

a .

11.5. В блок «Значения левых частей уравнений» в верхнюю ячейку вво-

дим формулу:

=СУММПРОИЗВ(«фиксированный диапазон строки значений пере-

менных

a ,

a »;«диапазон первой строки матрицы коэффициентов ис-

ходной системы»)

11.6. Автоматически заполняем весь столбец «Значения левых частей

уравнений».

11.7. В блок «Свободные члены исходной системы» в столбец записыва-

ем значения правой части исходной системы.

11.8. Вызываем Поиск решения и заполняем форму:

Установить целевую ячейку – ничего не ставить;

Равной – максимальному значению;

Изменяя ячейки – диапазон

строки значений переменных;

Ограничения – диапазон «Значения левых частей уравнений» =

диапазон «Свободные члены исходной системы»;

нажать Выполнить.

11.8.1. Заполнить форму Результаты поиска решений:

поставить опцию Сохранить найденное решение;

нажать ОК.

Результат выполнения команды Поиск решения… будет следующий

(рис. 2.17).

4,663 -0,247

7 4,2677 31,59 = 31,59

4,2677 5,1767 18,6244 = 18,6244

Переменные

Матрица

коэффициенто

в исходной

системы

Значения левых

частей уравнений

Свободные

члены исходной

системы

Рис. 2.17