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

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

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

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

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

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

15из25

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

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

12. Записываем уравнение гиперболической регрессии.

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

35207820,24668510- 4735234,66325435 y x= .

13. Для нахождения общей ошибки и средней ошибки аппроксимации по-

строим вспомогательную таблицу (рис. 2.18).

Год

Уровень

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

Уровень

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

y-линейное

y-квадратичное

y-показательное

y-гиперболическое

1234567

1 1991 0,5 4,25 4,2500 4,2500 4,2541 4,1699

2 1992 1,2 4,32 4,3200 4,3200 4,3205 4,4577

3 1993 2 4,4 4,4000 4,4000 4,3977 4,5399

4 1994 3,1 4,51 4,5100 4,5100 4,5061 4,5837

5 1995 4 4,6 4,6000 4,6000 4,5968 4,6016

6 1996 5,2 4,72 4,7200 4,7200 4,7206 4,6158

7 1997 5,9 4,79 4,7900 4,7900 4,7943 4,6214

31,5900 31,5900 31,5901 31,5900

Квадрат отклонения

y-линейного

Квадрат отклонения

y-квадратичного

Квадрат отклонения

y-показательного

Квадрат отклонения

y-гиперболического

Аппроксимация

y-линейного

Аппроксимация

y-квадратичного

Аппроксимация

y-показательного

Аппроксимация

y-гиперболического

8 9 10 11 12 13 14 15

1 0,0000 0,0000 0,0000 0,01 0,0000 0,0000 0,0010 0,02

2 0,0000 0,0000 0,0000 0,02 0,0000 0,0000 0,0001 0,03

3 0,0000 0,0000 0,0000 0,02 0,0000 0,0000 0,0005 0,03

4 0,0000 0,0000 0,0000 0,01 0,0000 0,0000 0,0009 0,02

5 0,0000 0,0000 0,0000 0,00 0,0000 0,0000 0,0007 0,00

6 0,0000 0,0000 0,0000 0,01 0,0000 0,0000 0,0001 0,02

7 0,0000 0,0000 0,0000 0,03 0,0000 0,0000 0,0009 0,04

Сумма 0,0000 0,0000 0,0001 0,09 0,00% 0,00% 0,06% 2,24%

№

п/п

№

п/п

Сумма

Рис. 2.18

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

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

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

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

16из25

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

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

=4,20000000000008+0,0999999999999777*«верхняя ячейка столбца x»

Замечание 2.4. Вместо значений оценок параметров вводим абсолютные

ссылки на ячейки, содержащие значения этих оценок параметров.

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

полним столбец (

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

=4,19998832974962+0,100011125811057*«верхняя ячейка столбца x» -

1,73873869144248E-06*(«верхняя ячейка столбца x»^2)

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

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

=4,20727*EXP(0,0221372486587452*«верхняя ячейка столбца x»)

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

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

с формулой на область заполнения):

=4,66325435473523-0,246685103520782/«верхняя ячейка столбца x»

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

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

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

4»);2)

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

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

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

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

5»);2)

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

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

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

6»);2)

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

полним столбец (протягиванием

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

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

7»);2)

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

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

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

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

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

за-

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

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

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

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

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

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

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

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

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

17из25

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

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

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

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

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

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

13.13. Просуммируем значения столбцов 4 – 11 с помощью функции

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

столбцом с соответствую-

щими данными.

В ячейках под столбцами 12 – 15 введем формулы и придадим этим ячей-

кам формат Процентный:

=1/7*СУММ(«диапазон значений ячеек соответствующего столбца 12

– 15»)*100%

Замечание 2.5. Суммы значений столбцов 8 – 11 – это общие ошибки ли-

нейной, квадратичной, показательной, гиперболической регрессий соответ-

ственно. Суммы значений столбцов 12 – 15 – это средние ошибки аппрокси-

мации линейной,

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

соответственно.

14. Укажем функцию наилучшей аппроксимации по общей ошибке и по

средней ошибке аппроксимации, используя функции ЕСЛИ и МИН.

14.1. Для определения регрессии с минимальной общей ошибкой введем

формулу:

=ЕСЛИ(«ячейка суммы столбца y-лиейное»=МИН(«диапазон ячеек

сумм регрессий»);"y-линейное";ЕСЛИ(«ячейка суммы столбца y-

квадратичное»=МИН(«диапазон

ячеек сумм регрессий»);"y-

квадратичное"; ЕСЛИ(«ячейка суммы столбца y-

показательное»=МИН(«диапазон ячеек сумм регрессий»);"y-

показательное";"y-гиперболическое")))

14.2. Для определения регрессии с минимальной средней ошибкой ап-

проксимации введем формулу:

=ЕСЛИ(«ячейка со значением средней ошибки аппроксимации y-ли-

нейное»=МИН(«диапазон ячеек со значениями всех средних ошибок ап-

проксимации»);"y-линейное";ЕСЛИ(«ячейка

со значением средней ошиб-

ки аппроксимации y-квадратичное»=МИН(«диапазон ячеек со значения-

ми всех средних ошибок аппроксимации»);"y-квадратичное";

ЕСЛИ(«ячейка со значением средней ошибки аппроксимации y-

показательное»=МИН(«диапазон ячеек со значениями всех средних оши-

бок аппроксимации»);"y-по-казательное";"y-гиперболическое")))

y-линейное

y-линейноеМинимальная средняя ошибка аппроксимации:

Минимальная общая ошибка:

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

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

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

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

18из25

Вывод. Линейная функция – функция наилучшей аппроксимации.

15. Построим линии регрессии в одной плоскости вместе с исходными

данными (рис. 2.19).

Замечание 2.6. Из-за того, что исходные данные выражают почти функ-

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

ской) на данном промежутке исходных данных почти совпадают.

Аппроксимирующие функции

4,15

4,25

4,35

4,45

4,55

4,65

4,75

0,3 1,3 2,3 3,3 4,3 5,3

Уровень безработицы

Уровень преступности

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

- Линейная регрессия

- Квадратичная регрессия

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

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

Рис. 2.19

Замечание 2.7. Если в таблице (рис. 2.18) значения аргумента функции не

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

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

ставить автофильтры и отфильтровать по возрастанию.

MS Excel – Данные – Фильтр – Автофильтр

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

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

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

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

19из25

Прежде чем поставить автофильтр, необходимо предварительно выделить

диапазон ячеек, включающий название столбцов и их значения (рис. 2.20).

Год

Уровень

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

Уровень

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

y-линейное

y-квадратичное

y-показательное

y-гиперболическое

Квадрат отклонения

y-линейного

Квадрат отклонения

y-квадратичного

Квадрат отклонения

y-показательного

Квадрат отклонения

y-гиперболического

Аппроксимация

y-линейного

Аппроксимация

y-квадратичного

Аппроксимация

y-показательного

Аппроксимация

y-гиперболического

123456789101112131415

1 1991 0,5 4,25 4,250 4,250 4,254 4,170 0,000 0,000 0,000 0,000 0,000 0,000 0,001 0,019

2 1992 1,2 4,32 4,320 4,320 4,321 4,458 0,000 0,000 0,000 0,000 0,000 0,000 0,000 0,032

3 1993 2 4,4 4,400 4,400 4,398 4,540 0,000 0,000 0,000 0,000 0,000 0,000 0,001 0,032

4 1994 3,1 4,51 4,510 4,510 4,506 4,584 0,000 0,000 0,000 0,000 0,000 0,000 0,001 0,016

5 1995 4 4,6 4,600 4,600 4,597 4,602 0,000 0,000 0,000 0,000 0,000 0,000 0,001 0,000

6 1996 5,2 4,72 4,720 4,720 4,721 4,616 0,000 0,000 0,000 0,000 0,000 0,000 0,000 0,022

7 1997 5,9 4,79 4,790 4,790 4,794 4,621 0,000 0,000 0,000 0,000 0,000 0,000 0,001 0,035

31,59 31,59 31,59 31,59 0,000 0,000 0,000 0,000 0,000 0,000 0,001 0,022

№

п/п

Сумма

Рис. 2.20

1. Что означает аппроксимация функции?

2. С помощью каких показателей можно судить о лучшей аппроксимации

функции?

3. Для чего применяют функцию MS Excel МИН?

4. Могут ли исходные данные лежать на линии регрессии? И какие вари-

анты могут быть?

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

с помощью надстройки Поиск решения...

6. Приведите

пять примеров возможных аппроксимирующих функции,

кроме тех, что рассмотрены в задаче.

7. Опишите синтаксис функции MS Excel ЕСЛИ.

8. Для чего применяется метод наименьших квадратов? В чем его суть?

9. Как вычисляется средняя ошибка аппроксимации? Что она характери-

зует?

10. Что произойдет с уровнем преступности при увеличении уровня без-

работицы, если исходить из графического изображения

линейной регрессии?

1. Исходные данные товарооборота от дохода населения.

Товарооборот,

млн руб.

Доходы населения,

тыс. руб.

23 26229 73 243 162

171 335 268 180 48 49 114 988

850

858

621 458196

118

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

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

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

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

20из25

2. Исходные данные уровня благосостояния населения от уровня занято-

сти.

326 310 295 219 193 182 177 165 169 150

Занятость, чел.

Благосостояние

(коэф.)

282 265 241 180 138 121159 146 145 135

3. Исходные данные товарооборота от дохода населения.

105 102

153 145

101 120 158 196 180 190 156 110

195 197 171 168307 309 253 224

Товарооборот,

млн руб.

Доходы населения,

тыс. руб.

4. Исходные данные уровня занятости от заработной платы населения.

2612 6975 2972 1517 1170 176 1088 2747 1063 1304

ЗП, тыс. руб.

685968 830 850 696

Уровень занятости,

чел.

1440 1438 1151 649 625

5. Исходные данные уровня рождаемости от уровня заработной платы на-

селения.

4316 444 318 70 51 1477 1072 852 219 405

402 507

Уровень

рождаемости, чел.

Уровень ЗП, тыс. руб.

4282 462 286 84 90 1842 1835 933

6. Исходные данные объема выпускаемой продукции от числа занятых

рабочих.

420 170 340 230 560 290 410 100 550 340

62 86 1927 53 57 115

Число рабочих, чел.

Объем выпуска готов.

продукции, шт.

99 830120

7. Исходные данные товарооборота между Россией и Японией за 5 лет

(млрд долларов) (http://www.polpred.ru).

1996 1997 1998 1999 2000

4973,4 5033,3 3846,3 4225,4 5124,2

3948,8 4018,4 2874,9 3747 4554,6

Товарооборот, млрд дол.

Экспорт, млрд дол.

Год

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

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

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

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

21из25

8. Исходные данные товарооборота между Россией и Японией за 5 лет

(млрд дол.) (http://www.polpred.ru).

1996 1997 1998 1999 2000

4973,4 5033,3 3846,3 4225,4 5124,2

1024,6 1014,9 971,4 478,4 569,6

Товарооборот, млрд дол.

Импорт, млрд дол.

Год

9. Некоторые исходные показатели экономического развития КНР (Ис-

точник: МЭ и международные отношения, – 2002. – № 8. – С. 65).

1985

1986

1988

1989

1990

1991

1992

1993

1994

Годы

Экспорт,

млрд дол.

27,42

Прирост ВВП,

млрд дол.

12,116

30,91

47,5

8,111

11,311

52,5

62,1

4,612

3,801

71,8

84,9

9,305

91,7

121

13,32

11,663

10. Некоторые исходные показатели экономического развития КНР (Ис-

точник: МЭ и международные отношения. – 2002. – № 8. – С. 65).

1985

1986

1988

1989

1990

1991

1992

1993

1994

Годы

Импорт,

млрд дол.

42,2

Прирост ВВП,

млрд дол.

12,116

42,9

55,3

8,111

11,311

59,1

53,4

4,612

3,801

63,8

80,6

9,305

104

115,7

13,32

11,663

11. Исходные данные заработной платы от производительности труда.

1200

567

500 900 1000

280

1,5 2 3

400 300 250

Заработная плата,

тыс. руб.

Производительность

труда, шт.

670

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

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

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

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

22из25

12. Исходные данные товарооборота России и Японии в 1991 – 1995 гг.

(Источник: Внешняя торговля. – 1995. – №2-3. – С. 21).

1991 1992 1993 1994 1995

1880,7 1568,7 2005,1 1514 578

Год

Экспорт, млн дол.

Товарооборот,

млн дол.

1100,64046,7 3248,8 3372 2243,4

13. Исходные данные товарооборота России и Японии в 1991 – 1995 гг.

(Источник: Внешняя торговля. – 1995. – №2-3. – С. 21).

1991 1992 1993 1994 1995

2166 1680,1 1366,9 729,4 522,6

Год

Импорт, млн дол.

Товарооборот,

млн дол.

4046,7 3248,8 3372 2243,4 1100,6

14. Исходные данные притока прямых иностранных инвестиций в КНР

(выбрать любую из пар зависимого и независимого показателей) (Источник:

МЭ и международные отношения. – 2002. – № 8. – С. 66).

1995 1996 1997 1998 1999 2000

Год

Число объектов (у),

млрд дол.

Согласование

инвестиций (х

), млрд дол.

Использование

инвестиций (х

), млрд дол.

21 19,8 17,1 15,7

91,9 73,2 51 52,1 41,2 39,2

37 24,6

40,4 28,137,8 42,4 45,2 45,6

15. Исходные данные макроэкономических итогов политики за 6 лет (вы-

брать любую из пар зависимого и независимого показателей) (Источник: МЭ

и международные отношения. – 2006. – № 2).

1998 1999 2000 2001 2002 2003

55,244,85,86

Экономический рост (рост

реального ВВП) (х

), %

Норма безработицы (у), %

Год

2,2

1,9 2,1

4,433,20,33,6

2,4 1,1 1,7

Инфляция (рост дефлятора

ВВП) (х

), %

1,5

16. Исходные данные распределения добавленной стоимости по отраслям

экономики в ЕС в 2002 г. (выбрать любую из пар зависимого и независимого

показателей).

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

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

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

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

23из25

Венгрия Кипр Латвия Литва Мальта Польша

65,6 75,6 70,6 62,4 69,1 66,9

Государство

Сфера услуг (х

), млрд дол.

4,7 7,1

Сельское хозяйство (у),

млрд дол.

Промышленность (х

мл

д дол.

2,8 3,1

30,7 20,3 24,7 30,5 28,1 30

3,7 4,1

17. Исходные данные основных экономических показателей Армении в

1996 – 2002 гг. (выбрать любую из пар зависимого и независимого показате-

лей) (Источник: Общество и экономика. – 2005. – № 4).

1996 1997 1998 1999 2000 2001 2002

106 109 117 121 128 141 159

Год

ВВП (у), млн руб.

Промышленная

продукция (х

), млн руб.

Валовая продукция с.-х. (х

млн

ру

б.

101 102 100 106 112 118 136

102 96 108 110 107 119 125

18. Исходные данные разнонаправленной динамики социально-

экономического развития по группам наиболее и наименее развитых регио-

нов России за 2004 г. (выбрать любую из пар зависимого и независимого по-

казателей) (Источник: Экономист. – 2005. – №3).

0,11

0,07

0,04

ВРП на душу

населения (у),

тыс. руб.

Инвестиции в

основной капитал на

душу населения (х

тыс.

ру

б.

Внешнеторговый

оборот на душу

населения (х

тыс.

ру

б.

40,73

41,21

23,43

36,49

1,24

218,12

78,68

88,13

14,16

14,00

3,21

1,90

2,67

1,58

0,93

ЕАО

Республика Тыва

Читинская область

Москва

Санкт - Петербург

Самарская область

Регион

19. Исходные данные некоторых экономических показателей хозяйствен-

но-финансовой деятельности с.-х. предприятий (выбрать любую из пар зави-

симого и независимого показателей) (Источник: Экономист. – 2005. – №3).

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

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

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

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

24из25

1998 1999 2000 2001 2002 2003 2004

322 354,2 406,2

Год

232 227 232,5 281

316,8 361,9 397,3

204 222 216,5 270 309,7 345,2 361,8

211 231,6

Выручка от продажи товаров,

продукции, работ, услуг (у),

млрд руб.

Себестоимость товаров,

продукции, работ, услуг (х

мл

рд

ру

б.

Кредиторская задолженность (х

мл

рд

ру

б.

206 298,5

20. Исходные данные основных экономических показателей Украины в

1996 – 2002 гг. (выбрать любую из пар зависимого и независимого показате-

лей) (Источник: Общество и экономика. – 2005. – № 4).

1996 1997 1998 1999 2000 2001 2002

90 87 86 86 91 99 104

Год

109 125 134

91 89 80 75 82 90 91

95 95 94 97

Промышленная

продукция (х

), млн дол.

Валовая продукция с.-х. (х

млн

ол.

ВВП (у), млн дол.

21. Исходные данные товарооборота России с Германией в 1992 – 1997 гг.

(выбрать любую из пар зависимого и независимого показателей) (Источник:

Внешняя торговля. – 1996. – №1-2. – С. 26).

1992 1993 1994 1995 1996 1997

Год

Импорт России (х

млн нем. ма

ок

10600

21800 22113

11200 10720

11393

Товарооборот (у),

млн нем. марок

Экспорт России (х

млн нем. ма

ок

23909 24000 25300 25950

13155 13500 13856 14500

10754 10500 11000 11325

22. Исходные данные динамики основных показателей Китая с 1991 по

1996 гг. (выбрать любую из пар зависимого и независимого показателей).

1991 1992 1993 1994 1995 1996

2,6 1,5 1,9 2,3 2,8 3,2

3,8 4,9 5,7 6,7 5,3 2,2

1 1,1 1,2 1,3 2,6 0,6

Инвестиции (х

), %

Год

ВВП (у), %

Импорт (х

), %

23. Исходные данные товарооборота России и Кореи в 1991 – 1995 гг.

(выбрать любую из пар зависимого и независимого показателей).