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

Министерство транспорта

Российской Федерации

Федеральное агентство железнодорожного транспорта

ГОУ ВПО «Дальневосточный государственный

университет путей сообщения»

Кафедра «Прикладная математика»

Хабаровск

Издательство ДВГУПС

2007

А.Н. Иванов

Сборник лабораторных работ

УДК 330.43(075.8)

ББК У.в631я73

И 200

Рецензенты:

Кафедра «Программного обеспечения вычислительной техники

и автоматизированных систем»

Тихоокеанского государственного университета

(заведующий кафедрой доктор физико-математических наук, профессор

Р. В. Намм)

Доцент кафедры «Высшей математики»

Тихоокеанского государственного университета,

кандидат физико-математических наук

В. Я. Прудников

Иванов, А. Н.

И 200 Построение эконометрических моделей и прогнозирование в MS

Excel: сборник лабораторных работ / А. Н. Иванов. — Хабаровск:

Изд-во ДВГУПС, 2007. — 131 с.: ил.

Сборник лабораторных работ соответствует Государственному

образовательному стандарту ВПО специальности 08010265 «Мировая

экономика».

Сборник содержит 8 лабораторных работ и методические указания по их

выполнению, контрольные вопросы и упражнения, варианты индивидуальных

заданий, список рекомендуемой литературы.

Рассмотрены модели парной линейной, множественной линейной и

нелинейной регрессий, регрессионная модель с переменной структурой, модель

временных рядов, системы одновременных уравнений и приведены подробные

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

MS Excel.

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

формы обучения, изучающих дисциплину «Эконометрика», может быть полезен

студентам заочной формы обучения и всем, кто интересуется этими вопросами.

путей сообщения» (ДВГУПС), 2007

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

1из14

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

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

В

Е

Д

Е

Н

И

Е

Эконометрика – научная дисциплина, занимающаяся моделированием

экономических систем со случайными факторами и последующим их анали-

зом с целью осуществления прогноза поведения этой экономической систе-

мы в будущем.

Настоящий сборник лабораторных работ подготовлен с целью закрепле-

ния студентом теоретического

материала по дисциплине «Эконометрика»

путём решения конкретных практических задач экономики посредством мо-

делирования экономических систем, с последующим их анализом и прогно-

зом, выработки навыков быстрой работы в табличном редакторе MS Excel

при решении практических задач эконометрики и изучения возможностей

редактора MS Excel применительно к статистическому анализу имеющихся

экономических данных.

Структура сборника такова, что каждой лабораторной работе предшест-

вует постановка эконометрической задачи, затем даётся подробное алгорит-

мическое решение и выводы по ходу решения, далее контрольные вопросы

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

ем теоретических аспектов эконометрики, и уже как завершающий этап – ва-

рианты индивидуальных задач, которые следует решать аналогично разо-

бранным.

При выполнении лабораторных работ важно понять идею решения задач

эконометрики и усвоить те методы, которые применяются при этом решении.

Только при таком условии студент сможет четко представлять себе назначе-

ние дисциплины «Эконометрика», способы и цель её практического приме-

нения.

Предлагаемый сборник лабораторных работ составляет единое целое с

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

О

с

н

о

в

н

ы

е

т

р

е

б

о

в

а

н

и

я

и

м

е

т

о

д

и

ч

е

с

к

и

е

р

е

к

о

м

е

н

д

а

ц

и

п

о

в

ы

п

о

л

н

е

н

и

ю

л

а

б

о

р

а

т

о

р

н

ы

х

р

а

б

о

т

1. Перед тем как выполнять лабораторные работы необходимо изучить

теоретический материал, соответствующий практике, который подробно из-

ложен в учебном пособии [3].

2. Перед началом выполнения

лабораторных работ необходимо создать

электронную книгу MS Excel.

2.1. Запустить табличный редактор MS Excel.

2.2. Нажать Фаил – Сохранить.

2.3. В появившемся окне «Сохранение документа» в строке «Папка:» вы-

брать папку на жестком диске, в которой вы будете сохранять книгу MS

Excel; в строке «Имя файла:» написать название книги MS Excel – название

дисциплины, фамилия, имя, отчество, номер группы (например, Эконометри-

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

2из14

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

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

ка_Иванов_Андрей_Николаевич_330); в строке «Тип файла:» выбрать «Кни-

га Microsoft Office Excel (*.xls)»; нажать «Сохранить» (рис. 1).

3. Каждую задачу необходимо выполнять на отдельном листе, название

которого будет соответствовать номеру задачи (например: Задача 1.2 или За-

дача 4.1). Первая цифра в номере задачи будет означать номер лабораторной

работы, вторая цифра – номер задачи в конкретной лабораторной работе (на-

пример: название листа «Задача 4.1» означает, что это первая задача в чет-

вертой лабораторной работе) (рис. 1).

Рис. 1

При возникновении трудностей в применении функций MS Excel необхо-

димо пользоваться методическими указаниями по использованию функций

табличного редактора MS Excel, которые представлены в ПРИЛОЖЕНИИ.

Л

А

Б

О

Р

А

Т

О

Р

Н

А

Я

Р

А

Б

О

Т

А

№

1

Т

е

м

а

:

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

гистограмм.

Ц

е

л

ь

:

1. Научиться придавать полученным результатам наблюдений пре-

зентабельный вид. 2. Освоить матричную алгебру в целях дальнейшего при-

менения при построении модели множественной линейной

регрессии.

Ф

о

р

м

а

о

т

ч

е

т

н

о

с

т

и

.

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

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

ру. Вариант работы определяет преподаватель.

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

будет отмечена серым цве-

том.

Задача 1.1. Даны матрицы , (рис. 1.1). Найти произведение

матриц, используя функцию МУМНОЖ.

43x

A

24x

B

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

3из14

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

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

Рис. 1.1

А

л

г

о

р

и

т

м

у

м

н

о

ж

е

н

и

я

м

а

т

р

и

ц

1. Выделить область, где будет размещена матрица произведения двух

матриц.

2.

Вызвать мастер функций, выбрать функцию умножения матриц

МУМНОЖ.

3. Указать в полях диапазоны первой и второй матриц.

4. Не нажимая на кнопку ОК, вставить формулу массива, нажав одновре-

менно комбинацию клавиш Ctrl+Shift+Enter.

Замечание 1.2. В выделенной области появится результат от умножения

двух матриц, формула при этом будет заключена в фигурные скобки и пред-

ставлять собой формулу массива.

Замечание 1.3 (по оформлению). Ячейка матрицы должна быть квадрат-

ной, с шириной столбца равной 4 и высотой строки равной 18 и с границей

редкий пунктир (тип линии). Должны быть убраны линии сетки (MS Excel –

Сервис – Параметры… – сетка), либо сделана заливка белым цветом всего

рабочего листа. Элемент матрицы (число) должен иметь шрифт Times New

Roman, 12-й пункт, полужирный. Название матрицы (например, )

должно иметь шрифт Times New Roman, 14-й пункт, индекс полужирный и

выравнивание по правому краю (индекс можно поставить следующим обра-

зом: выделить текст, который должен располагаться в индексе – Формат –

Ячейки... – Видоизменение). При этом все столбцы листа должны быть рав-

ными, а ячейки, при необходимости, нужно объединить.

=

43x

A

Задача 1.2. Дана матрица (рис. 1.2). Найти матрицу

33x

A

1−

A

, обратную

к данной, используя функцию МОБР.

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

4из14

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

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

Рис. 1.2

А

л

г

о

р

и

т

м

н

а

х

о

ж

д

е

н

и

я

о

б

р

а

т

н

о

й

м

а

т

р

и

ц

ы

1. Выделить область, где будет размещена обратная матрица.

2. Вызвать мастер функций, выбрать функцию МОБР.

3. Нажать одновременно комбинацию клавиш Ctrl+Shift+Enter, область

заполнится числами, являющимися элементами обратной матрицы.

4. Осуществить проверку: произведение матрицы на обратную к ней дает

единичную матрицу.

Задача 1.3. Дана матрица (рис. 1.3). Найти матрицу

33x

A

T

A

, транспони-

руемую к данной, используя функцию ТРАНСП.

Рис. 1.3

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

5из14

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

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

А

л

г

о

р

и

т

м

н

а

х

о

ж

д

е

н

и

я

т

р

а

н

с

п

о

н

и

р

у

е

м

о

й

м

а

т

р

и

ц

ы

1. Выделить область, где будет размещена транспонированная матрица.

2. Вызвать мастер функций, выбрать функцию ТРАНСП.

3. Нажать одновременно комбинацию клавиш Ctrl+Shift+Enter, область

заполнится числами, являющимися элементами транспонированной матри-

цы.

Задача 1.4. Дана матрица

(рис. 1.4). Найти определитель матрицы

33x

A

, используя функцию МОПРЕД.

А

л

г

о

р

и

т

м

н

а

х

о

ж

д

е

н

и

я

о

п

р

е

д

е

л

и

т

е

л

я

м

а

т

р

и

ц

ы

1. Выделить ячейку, где будет размещено значение определителя.

2. Вызвать мастер функций, выбрать функцию МОПРЕД, нажать ОК.

Рис. 1.4

Задача 1.5. Даны матрицы , (рис. 1.5). Показать выполнимость

свойства транспонированных матриц: .

43x

F

34x

K

()

TT

T

FKKF ⋅=⋅

А

л

г

о

р

и

т

м

р

е

ш

е

н

и

я

з

а

д

а

ч

и

1. Найти произведение матриц

K

F ⋅ .

2. Найти транспонированную матрицу

(

к матрице

)

T

KF ⋅

K

F ⋅ .

3. Найти транспонированную матрицу

T

K

к матрице

K

.

4. Найти транспонированную матрицу к матрице .

T

F F

5. Найти произведение матриц

TT

F

K

⋅ .

6. Показать равенство матриц: вычитание из одной матрицы другой дает

нулевую матрицу. Выделить область, где будет размещена матрица разности;

нажать клавишу «равно»; выделить уменьшаемую матрицу; нажать клавишу

«минус»; выделить вычитаемую матрицу; нажать одновременно комбинацию

клавиш Ctrl+Shift+Enter.

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

6из14

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

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

Рис. 1.5

Задача 1.6. Дана матрица

C (рис. 1.6). Найти новую матрицу . C2

Рис. 1.6

А

л

г

о

р

и

т

м

р

е

ш

е

н

и

я

з

а

д

а

ч

и

1. Выделить область, где будет размещена новая матрица.

2. Записать формулу, начиная со знака

равно: =диапазон С * 2.

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

7из14

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

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

3. Нажать одновременно комбинацию клавиш Ctrl+Shift+Enter, область

заполнится числами, являющимися результатом умножения элементов мат-

рицы на число.

Задача 1.7. Решить систему линейных уравнений методом Крамера

(рис. 1.7).

Рис. 1.7

А

л

г

о

р

и

т

м

р

е

ш

е

н

и

я

з

а

д

а

ч

и

1. Составляем матрицы, в которых, соответственно, по одному столбцу,

начиная с первого, заменены столбцом

свободных членов.

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

первом пункте.

3. По формулам Крамера вычисляем неизвестные:

()

A

xA

x

det

1

=

,

()

A

xA

x

det

2

=

,

()

A

xA

x

det

3

=

,

()

A

xA

x

det

4

=

,

()

A

xA

x

det

5

=

.

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

8из14

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

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

Задача 1.8.

1. Построить гистограмму для следующих

табличных данных (рис. 1.8).

2. Изменить на графике величину дохода

в 2003 году. Какие изменения наблюдаются в

таблице?

3. Внести изменения величины дохода в

таблицу за 2002 год. Какие изменения на-

блюдаются в гистограмме?

Результат выполнений пунктов два и три

описать словесно, желательно сопроводить

итоговыми гистограммами и таблицами.

Год

1998 50

1999 48

2000 43

2001 52

2002 49

2003 55

Доход

(тыс. руб)

Рис. 1.8

А

л

г

о

р

и

т

м

п

о

с

т

р

о

е

н

и

я

г

и

с

т

о

г

р

а

м

ы

1. Ввести

таблицу исходных данных.

2. Вызвать мастер диаграмм: MS Excel – Вставка – Диаграмма…

3. Выбрать тип: Гистограмма, нажать Готово.

4. Выбрать закладку Ряд, нажать Добавить и заполнить форму, по окон-

чанию нажать Далее>:

Имя: ввести ссылку на ячейку с названием Доход.

Значения: ввести ссылку на диапазон данных по доходу.

Подписи по оси X: ввести ссылку на диапазон данных по годам.

5. Выбирая закладки, произвести их заполнение, по окончании нажать

Далее>:

закладка

Заголовки:

Название диаграммы: Годовой доход среднестатистического

гражданина.

Ось X (категорий): год.

Ось Y (значений): Доход (тыс. руб.).

закладка

Оси:

По основной оси поставить

ось X (категорий): категории;

ось Y (значений);

закладка

Легенда:

Добавить легенду – справа;

закладка Подписи данных:

Включить в подписи – значения.

6. В окне Размещение диаграммы заполнить форму:

Поместить диаграмму на листе: имеющемся.

7. Нажать Готово (рис. 1.9).

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

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