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

96 Pages·2.205 MB·Russian

by  Иванов А.Н.

Министерство транспорта
Российской Федерации
Федеральное агентство железнодорожного транспорта
ГОУ ВПО «Дальневосточный государственный
университет путей сообщения»
Кафедра «Прикладная математика»
А.Н. Иванов
Сборник лабораторных работ
Хабаровск
Издательство ДВГУПС
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 – название
дисциплины, фамилия, имя, отчество, номер группы (например, Эконометри-
2из14 Тема лабораторной работы: Матричная алгебра в MS Excel. Техника построения графиков и гистограмм
Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич
Кафедра «Прикладная математика» ДВГУПС

ка_Иванов_Андрей_Николаевич_330); в строке «Тип файла:» выбрать «Кни-
га Microsoft Office Excel (*.xls)»; нажать «Сохранить» (рис. 1).
3. Каждую задачу необходимо выполнять на отдельном листе, название
которого будет соответствовать номеру задачи (например: Задача 1.2 или За-
дача 4.1). Первая цифра в номере задачи будет означать номер лабораторной
работы, вторая цифра – номер задачи в конкретной лабораторной работе (на-
пример: название листа «Задача 4.1» означает, что это первая задача в чет-
вертой лабораторной работе) (рис. 1).

Рис. 1

При возникновении трудностей в применении функций MS Excel необхо-
димо пользоваться методическими указаниями по использованию функций
табличного редактора MS Excel, которые представлены в ПРИЛОЖЕНИИ.

ЛЛЛАААБББОООРРРАААТТТОООРРРНННАААЯЯЯ РРРАААБББОООТТТААА №№№111
ТТТееемммааа::: Матричная алгебра в MS Excel. Техника построения графиков и
гистограмм.
ЦЦЦееелллььь::: 1. Научиться придавать полученным результатам наблюдений пре-
зентабельный вид. 2. Освоить матричную алгебру в целях дальнейшего при-
менения при построении модели множественной линейной регрессии.
ФФФооорррмммааа ооотттчччееетттннноооссстттиии... Представить преподавателю подробное решение за-
дач в MS Excel. Оформление должно быть аналогично разобранному приме-
ру. Вариант работы определяет преподаватель.
Замечание 1.1. Ячейка, содержащая формулу, будет отмечена серым цве-
том.
Задача 1.1. Даны матрицы A , B (рис. 1.1). Найти произведение
3x4 4x2
матриц, используя функцию МУМНОЖ.
Тема лабораторной работы: Матричная алгебра в 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-й пункт, полужирный. Название матрицы (например, A )
3x4
должно иметь шрифт Times New Roman, 14-й пункт, индекс полужирный и
выравнивание по правому краю (индекс можно поставить следующим обра-
зом: выделить текст, который должен располагаться в индексе – Формат –
Ячейки... – Видоизменение). При этом все столбцы листа должны быть рав-
ными, а ячейки, при необходимости, нужно объединить.

−1
Задача 1.2. Дана матрица A (рис. 1.2). Найти матрицу A , обратную
3x3
к данной, используя функцию МОБР.
4из14 Тема лабораторной работы: Матричная алгебра в MS Excel. Техника построения графиков и гистограмм
Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич
Кафедра «Прикладная математика» ДВГУПС

Рис. 1.2

АААлллгггооорррииитттммм нннааахххооожжждддееенннииияяя оообббрррааатттннноооййй мммааатттрррииицццыыы
1. Выделить область, где будет размещена обратная матрица.
2. Вызвать мастер функций, выбрать функцию МОБР.
3. Нажать одновременно комбинацию клавиш Ctrl+Shift+Enter, область
заполнится числами, являющимися элементами обратной матрицы.
4. Осуществить проверку: произведение матрицы на обратную к ней дает
единичную матрицу.

T
Задача 1.3. Дана матрица A (рис. 1.3). Найти матрицу A , транспони-
3x3
руемую к данной, используя функцию ТРАНСП.

Рис. 1.3
Тема лабораторной работы: Матричная алгебра в MS Excel. Техника построения графиков и гистограмм 5из14
Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич
Кафедра «Прикладная математика» ДВГУПС

АААлллгггооорррииитттммм нннааахххооожжждддееенннииияяя тттрррааанннссспппоооннниииррруууееемммоооййй мммааатттрррииицццыыы
1. Выделить область, где будет размещена транспонированная матрица.
2. Вызвать мастер функций, выбрать функцию ТРАНСП.
3. Нажать одновременно комбинацию клавиш Ctrl+Shift+Enter, область
заполнится числами, являющимися элементами транспонированной матри-
цы.
Задача 1.4. Дана матрица A (рис. 1.4). Найти определитель матрицы
3x3
A, используя функцию МОПРЕД.

АААлллгггооорррииитттммм нннааахххооожжждддееенннииияяя ооопппрррееедддееелллииитттеееллляяя мммааатттрррииицццыыы
1. Выделить ячейку, где будет размещено значение определителя.
2. Вызвать мастер функций, выбрать функцию МОПРЕД, нажать ОК.

Рис. 1.4

Задача 1.5. Даны матрицы F , K (рис. 1.5). Показать выполнимость
3x4 4x3
( )
⋅ T = T ⋅ T
свойства транспонированных матриц: F K K F .

АААлллгггооорррииитттммм рррееешшшееенннииияяя зззааадддааачччиии
⋅
1. Найти произведение матриц F K .
( )
⋅ T ⋅
2. Найти транспонированную матрицу F K к матрице F K .
T
3. Найти транспонированную матрицу K к матрице K.
T
4. Найти транспонированную матрицу F к матрице F.
T ⋅ T
5. Найти произведение матриц K F .
6. Показать равенство матриц: вычитание из одной матрицы другой дает
нулевую матрицу. Выделить область, где будет размещена матрица разности;
нажать клавишу «равно»; выделить уменьшаемую матрицу; нажать клавишу
«минус»; выделить вычитаемую матрицу; нажать одновременно комбинацию
клавиш Ctrl+Shift+Enter.
6из14 Тема лабораторной работы: Матричная алгебра в MS Excel. Техника построения графиков и гистограмм
Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич
Кафедра «Прикладная математика» ДВГУПС

Рис. 1.5

Задача 1.6. Дана матрица C (рис. 1.6). Найти новую матрицу 2C.

Рис. 1.6

АААлллгггооорррииитттммм рррееешшшееенннииияяя зззааадддааачччиии
1. Выделить область, где будет размещена новая матрица.
2. Записать формулу, начиная со знака равно: =диапазон С * 2.
Тема лабораторной работы: Матричная алгебра в MS Excel. Техника построения графиков и гистограмм 7из14
Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич
Кафедра «Прикладная математика» ДВГУПС

3. Нажать одновременно комбинацию клавиш Ctrl+Shift+Enter, область
заполнится числами, являющимися результатом умножения элементов мат-
рицы на число.

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

Рис. 1.7

АААлллгггооорррииитттммм рррееешшшееенннииияяя зззааадддааачччиии
1. Составляем матрицы, в которых, соответственно, по одному столбцу,
начиная с первого, заменены столбцом свободных членов.
2. Вычисляем определители основной матрицы и матриц, полученных в
первом пункте.
3. По формулам Крамера вычисляем неизвестные:

( ) ( ) ( )
det A x det A x det A x
x = 1 , x = 2 , x = 3 ,
1 2 3
det A det A det A
( ) ( )
det A x det A x
x = 4 , x = 5 .
4 5
det A det A
8из14 Тема лабораторной работы: Матричная алгебра в MS Excel. Техника построения графиков и гистограмм
Лектор: к.ф.-м.н., доцент Иванов Андрей Николаевич
Кафедра «Прикладная математика» ДВГУПС

Задача 1.8.
1. Построить гистограмму для следующих
Год Доход
табличных данных (рис. 1.8).
(тыс. руб)
2. Изменить на графике величину дохода
1998 50
в 2003 году. Какие изменения наблюдаются в
1999 48
таблице?
3. Внести изменения величины дохода в 2000 43
таблицу за 2002 год. Какие изменения на- 2001 52
блюдаются в гистограмме? 2002 49
Результат выполнений пунктов два и три
2003 55
описать словесно, желательно сопроводить
итоговыми гистограммами и таблицами.
Рис. 1.8
АААлллгггооорррииитттммм пппоооссстттррроооееенннииияяя гггиииссстттооогггррраааммммммыыы
1. Ввести таблицу исходных данных.
2. Вызвать мастер диаграмм: MS Excel – Вставка – Диаграмма…
3. Выбрать тип: Гистограмма, нажать Готово.
4. Выбрать закладку Ряд, нажать Добавить и заполнить форму, по окон-
чанию нажать Далее>:
Имя: ввести ссылку на ячейку с названием Доход.
Значения: ввести ссылку на диапазон данных по доходу.
Подписи по оси X: ввести ссылку на диапазон данных по годам.
5. Выбирая закладки, произвести их заполнение, по окончании нажать
Далее>:
закладка Заголовки:
Название диаграммы: Годовой доход среднестатистического
гражданина.
Ось X (категорий): год.
Ось Y (значений): Доход (тыс. руб.).
закладка Оси:
По основной оси поставить
ось X (категорий): категории;
ось Y (значений);
закладка Легенда:
Добавить легенду – справа;
закладка Подписи данных:
Включить в подписи – значения.
6. В окне Размещение диаграммы заполнить форму:
Поместить диаграмму на листе: имеющемся.
7. Нажать Готово (рис. 1.9).