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

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

Тема лабораторной работы: Матричная алгебра в MS Excel. Техника построения графиков и гистограмм

9из14

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

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

Годовой доход среднестатистического

гражданина

год

Доход (тыс. руб.)

1998

1999

2000

2001

2002

2003

Рис. 1.9. Гистограмма для исходных данных рис. 1.8

Замечание 1.4. Вызывая контекстное меню правой кнопкой мыши на ка-

ждом объекте построенной гистограммы, произвести его форматирование

(вариант оформления определяет преподаватель). При форматировании

точки данных (заливка столбца) произойдет автоматическое заполнение эле-

ментов легенды.

Замечание 1.5 (по оформлению). Область диаграммы – заливка гради-

ентная. Заголовок диаграммы – шрифт Arial, пункт 12, полужирный курсив.

Заголовок оси значений – шрифт Arial, пункт 9, жирный. Ось значений:

шрифт Arial, пункт 8, полужирный; вид – Основные – внутрь. Ось категорий:

шрифт Arial, пункт 8, полужирный; Выравнивание – Ориентация – (-30) гра-

дусов; вид – Основные – внутрь. Область построения: заливка градиентная;

выбрать необходимый цвет рамки. Подпись данных: шрифт Arial, пункт 8,

полужирный; Выравнивание - по центру и положение подписи – по центру.

Точка данных (столбец): заливка градиентная; граница с тенью. Легенда: за-

ливка градиентная; рамка с тенью. Элемент легенды – шрифт Arial, пункт 8,

полужирный.

1. Оттенок синего, градиентная заливка горизонтальная, шрифт Times

New Roman.

2. Оттенок зеленого, градиентная заливка вертикальная, шрифт Tahoma.

3. Оттенок красного, градиентная заливка диагональная 1, шрифт Century.

4. Оттенок бордового, градиентная заливка диагональная 2, шрифт

Courier.

5. Оттенок коричневого, градиентная заливка от центра, шрифт Verdana.

6. Оттенок фиолетового, градиентная заливка из угла, шрифт Andale

Mono.

7. Оттенок синего и желтого, градиентная заливка из угла, шрифт

Terminal.

Тема лабораторной работы: Матричная алгебра в MS Excel. Техника построения графиков и гистограмм

10из14

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

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

8. Оттенок синего и бордового, градиентная заливка диагональная 1,

шрифт Lucida Bright.

9. Оттенок красного и зеленого, градиентная заливка диагональная 2,

шрифт Lucida Calligraphy.

10. Оттенок красного и синего, градиентная заливка горизонтальная,

шрифт Impact.

11. Оттенок зеленого и синего, градиентная заливка вертикальная, шрифт

Lucida Console.

12. Оттенок серого, градиентная заливка от центра, шрифт Lucida Fax.

13. Сильно темные цвета с белым шрифтом, градиентная заливка из угла,

шрифт Lucida Sans.

14. Сильно темные цвета оттенка синего и коричневого с белым шриф-

том, градиентная заливка диагональная 2, шрифт Magneto.

15. Оттенок серого и красного, градиентная заливка горизонтальная,

шрифт Times New Roman.

16. Оттенок серого и зеленого, градиентная заливка вертикальная, шрифт

Tahoma.

17. Оттенок серого и синего, градиентная заливка диагональная 1, шрифт

Century.

18. Оттенок серого и бордового, градиентная заливка диагональная 2,

шрифт Courier.

19. Оттенок серого и коричневого, градиентная заливка от центра, шрифт

Verdana.

20. Оттенок серого и фиолетового, градиентная заливка из угла, шрифт

Andale Mono.

21. Оттенок чёрного и жёлтого, градиентная заливка из угла, шрифт

Terminal.

22. Оттенок черного и красного, градиентная заливка диагональная 1,

шрифт Lucida Bright.

23. Оттенок чёрного и оранжевого, градиентная заливка диагональная 2,

шрифт Lucida Calligraphy.

24. Оттенок красного и фиолетового, градиентная заливка горизонталь-

ная, шрифт Impact.

25. Оттенок зеленого и жёлтого, градиентная заливка вертикальная,

шрифт Lucida Console.

Задача 1.9. Построить поверхность для нелинейной регрессии (производ-

ственная функция Кобба-Дугласа): , при , ,

и условии, что , (рис. 1.10).

xxay = 2

=a 11

≤≤− x 11

≤≤− x

– объем производства, – капитальные затраты,

x – затраты труда, , – параметры модели.

Шаг по , считать 0,2.

Тема лабораторной работы: Матричная алгебра в MS Excel. Техника построения графиков и гистограмм

11из14

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

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

-1,00 -0,80 -0,60 -0,40 -0,20 0,00 0,20 0,40 0,60 0,80 1,00

-1,00 -2,00 -1,28 -0,72 -0,32 -0,08 0,00 -0,08 -0,32 -0,72 -1,28 -2,00

-0,80 -1,60 -1,02 -0,58 -0,26 -0,06 0,00 -0,06 -0,26 -0,58 -1,02 -1,60

-0,60 -1,20 -0,77 -0,43 -0,19 -0,05 0,00 -0,05 -0,19 -0,43 -0,77 -1,20

-0,40 -0,80 -0,51 -0,29 -0,13 -0,03 0,00 -0,03 -0,13 -0,29 -0,51 -0,80

-0,20 -0,40 -0,26 -0,14 -0,06 -0,02 0,00 -0,02 -0,06 -0,14 -0,26 -0,40

0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00 0,00

0,20 0,40 0,26 0,14 0,06 0,02 0,00 0,02 0,06 0,14 0,26 0,40

0,40 0,80 0,51 0,29 0,13 0,03 0,00 0,03 0,13 0,29 0,51 0,80

0,60 1,20 0,77 0,43 0,19 0,05 0,00 0,05 0,19 0,43 0,77 1,20

0,80 1,60 1,02 0,58 0,26 0,06 0,00 0,06 0,26 0,58 1,02 1,60

1,00 2,00 1,28 0,72 0,32 0,08 0,00 0,08 0,32 0,72 1,28 2,00

Рис. 1.10

1. Заполнить

столбец данными -1, -0,8,…, 1, используя быстрое запол-

нение.

2. Заполнить строку данными -1, -0,8,…, 1, используя транспонирова-

ние столбца .

3. В клетке, стоящей на пересечении (-1,00)

(1,00) записать формулу, в

которой использовать абсолютный адрес строки и столбца соответственно,

например, =2*C$4^2*$B5.

4. Распространить эту формулу на всю таблицу.

5. Выделить всю таблицу и вызвать мастера диаграмм: MS Excel –

Вставка – Диаграмма…

6. Выбрать тип диаграммы Поверхность.

Замечание 1.6 (по оформлению). Вариант оформления аналогично зада-

че 1.8. Линии сетки – пунктир. Цвет основания должен быть отличен от цвета

стенок (более светлый или более темный). Ось ряда данных и ось категорий –

с метками делений внизу, без основных и промежуточных. Заливка самой

поверхности градиентная, с плавным перетеканием цветов. Заголовок диа-

граммы – рамка с тенью. Легенда – рамка с тенью.

Если палитра цветов вам не подходит, то ее можно изменить добавлением

новых цветов и их оттенков: MS Excel – Сервис – Параметры… – вкладка

Цвет

Тема лабораторной работы: Матричная алгебра в MS Excel. Техника построения графиков и гистограмм

12из14

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

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

-1

-2,0

-1,5

-1,0

-0,5

0,0

0,5

1,0

1,5

2,0

Величина функции

Поверхность множественной нелинейной регрессии

1,50-2,00

1,00-1,50

0,50-1,00

0,00-0,50

-0,50-0,00

-1,00--0,50

-1,50--1,00

-2,00--1,50

Рис. 1.11. Поверхность для исходных данных рис. 1.10

Задача 1.10. Построить графики функций:

−−=

, 12 +−=

на отрезке

[

с шагом 0,5.

]

;43−

1. Заполнить столбец значений аргумента

(рис. 1.12).

2. Ввести формулу в ячейку для функции

3. Заполнить столбец значений функции .

4. Ввести формулу в ячейку для функции

5. Записать столбец значений функции .

6. Выделить диапазон значений функций и вызвать

мастер диаграмм.

7. Выбрать тип изображения График.

8. Заполнить значениями оси и

9. Озаглавить диаграмму, оси.

10. Провести дополнительные исследования:

– изменить линейную функцию, переместив маркер

на графике. Изменится ли при этом табличное значе-

ние? (ответ обосновать) (рис. 1.13);

– изменить линейную функцию, например:

23 −=

n . Изменится ли график? Если нет, то покажи-

те, как это сделать?

– изменить шрифты в значениях осей (Arial, полу-

жирный) и размер (8 пунктов);

– изменить тип и цвет параболы и прямой на графи-

ке, поработать с легендой.

xmn

-3,00 12,00 7,00

-2,50 8,25 6,00

-2,00 5,00 5,00

-1,50 2,25 4,00

-1,00 0,00 3,00

-0,50 -1,75 2,00

0,00 -3,00 1,00

0,50 -3,75 0,00

1,00 -4,00 -1,00

1,50 -3,75 -2,00

2,00 -3,00 -3,00

2,50 -1,75 -4,00

3,00 0,00 -5,00

3,50 2,25 -6,00

4,00 5,00 -7,00

Рис. 1.12

Тема лабораторной работы: Матричная алгебра в MS Excel. Техника построения графиков и гистограмм

13из14

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

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

Замечание 1.7 (по оформлению). Вариант оформления – аналогично за-

даче 1.8. Ось категорий – пересечение с осью (значений) в категории но-

мер: 7. Ось значений и ось категорий: метки делений внизу, основные метки

делений пересекают ось, без промежуточных меток делений. Маркер должен

иметь цвет границы одинаковый с линией, а заливку – отличную от нее.

Парабола и прямая

-9,00

-6,00

-3,00

0,00

3,00

6,00

9,00

12,00

15,00

-3,0

-2,5

-2,0

-1,5

-1,0

-0,5

0,0

0,5

1,0

1,5

2,0

2,5

3,0

3,5

4,0

Значение аргумента (x)

Значение функции

m n

Рис. 1.13. Графики для исходных данных рис. 1.12

1. Что такое формула массива?

2. Чем отличается абсолютный адрес от относительного адреса ячейки?

3. В каком случае можно применить относительный адрес ячейки?

4. Каково назначение функций MS Excel МОПРЕД, МУМНОЖ, МОБР,

ТРАНСП?

5. Перечислите все типы диаграмм, предусмотренные мастером диа-

грамм.

6. Что такое легенда?

7. Какие матрицы можно перемножать?

8. Можно ли найти определитель матрицы размерности 3

9. Что такое производственная функция Кобба – Дугласа?

10. Какими способами можно ввести формулу в ячейку?

11. Как удалить (восстановить) линии сетки?

12. Что значит объединить ячейки, каким образом это можно использо-

вать?

13. Как изменить общий формат ячейки на числовой?

Тема лабораторной работы: Матричная алгебра в MS Excel. Техника построения графиков и гистограмм

14из14

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

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

14. Что такое «транспонированная матрица»? Какое свойство транспони-

рованных матриц можете указать?

Написать 10 экономических показателей и

определить, от каких сущест-

венных (сильно влияющих) факторов они зависят. Например, экономический

показатель «спрос» зависит от факторов «цена», «маркетинговые вложения»,

«уровень инфляции», «качество продукции» и т.д. Однако к существенным

фактором можно отнести лишь фактор «цена». Показатели представить таб-

лицей следующего вида:

№

п/п

Экономиче-

ский

показатель

Влияющие

факторы

Сущест-

венные

факторы

Несущественные

факторы

1 спрос

цена, маркетинговые

вложения, уровень

инфляции, качество

продукции.

цена

маркетинговые вложе-

ния, уровень инфля-

ции, качество продук-

ции.

… … … … …

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

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

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

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

1из25

№

Метод наименьших квадратов. Аппроксимация линейной, квадра-

тичной, показательной

функций. Средняя ошибка аппроксимации.

1. Научиться аппроксимировать класс функций, линейных по па-

раметрам. 2. Научиться подбирать вид модели, которая будет иметь наи-

меньшую общую ошибку и наименьшую среднюю ошибку аппроксимации.

Представить преподавателю подробное решение за-

дачи с выводами в MS Excel. Оформление должно быть аналогично разо-

бранному примеру. Письменно ответить на контрольные вопросы на рабочем

листе MS Excel после решения задачи. Вариант работы определяет препо-

даватель.

Замечание 2.1. Ячейка, содержащая формулу, будет отмечена

серым цве-

том.

Задача 2.1.

Данные о росте безработицы

, % и росте преступности y, % приведены

в таблице (рис. 2.1).

1991 1992 1993 1994 1995 1996 1997

0,51,223,145,25,9

4,25 4,32 4,4 4,51 4,6 4,72 4,79

Уровень

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

Уровень

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

Год

Рис. 2.1

1. Методом наименьших квадратов по табличным данным найти аппрок-

симирующие (приближаемые) функции, то есть регрессии: линейную, квад-

ратичную, показательную, гиперболическую.

2. В каждом случае найти общую ошибку и среднюю ошибку аппрокси-

мации. Указать функцию лучшей аппроксимации.

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

данными.

Таблицу (рис. 2.1) можно

считать функцией, заданной таблично.

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

раметров линейной

регрессии: xaay

⎪

⎩

⎪

⎨

⎧

∑∑∑

∑∑

===

xyxaxa

yxana

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

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

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

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

2из25

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

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

столбцами:

Год

Уровень

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

Уровень

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

12345

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

2 1992 1,2 4,32 1,44 5,184

3 1993 2 4,4 4 8,8

4 1994 3,1 4,51 9,61 13,98

5 1995 4 4,6 16 18,4

6 1996 5,2 4,72 27,04 24,54

7 1997 5,9 4,79 34,81 28,26

№

п/п

Рис. 2.2

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

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

полнения).

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

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

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

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

на область за-

полнения).

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

1.4. Просуммируем значения столбцов:

, y,

y с помощью функ-

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

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

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

⎩

⎨

⎧

.295,10115,939,21

,59,319,217

Замечание 2.2. Данную систему нормальных уравнений можно решать и

методом Крамера, и матричным методом. Однако мы будем использовать для

ее решения надстройку MS Excel Поиск решения….

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

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

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

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

3из25

Год

Уровень

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

Уровень

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

12345

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

2 1992 1,2 4,32 1,44 5,184

3 1993 2 4,4 4 8,8

4 1994 3,1 4,51 9,61 13,98

5 1995 4 4,6 16 18,4

6 1996 5,2 4,72 27,04 24,54

7 1997 5,9 4,79 34,81 28,26

21,9 31,59 93,15 101,3

№

п/п

Сумма

Рис. 2.3

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

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

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

(рис. 2.4).

7 21,9 21,9 = 31,59

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

Переменные

Матрица

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

исходной

системы

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

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

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

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

Рис. 2.4

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

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

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

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

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

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

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

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

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

a .

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

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

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

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

4из25

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

дим формулу:

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

менных

a ,

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

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

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

уравнений».

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

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

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

Вызов Поиск решения...: MS Excel – Сервис – Поиск решения…

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

;

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

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

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

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

Замечание 2.3. Если в меню Сервис нет команды Поиск решения…, зна-

чит, надстройка не подключена. Подключение выполняется в окне Надстрой-

ки установкой флажка перед опцией Поиск решения, вызвать которое

можно

командой MS Excel – Сервис – Надстройки.

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

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

нажать ОК.

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

(рис. 2.5)

4,200 0,100

7 21,9 31,59 = 31,59

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

Переменные

Матрица

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

в исходной

системы

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

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

Свободные

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

системы

Рис. 2.5

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

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