ФЕДЕРАЛЬНОЕ АГЕНСТВО ПО ОБРАЗОВАНИЮ

КОЛОМЕНСКИЙ ИНСТИТУТ (ФИЛИАЛ)

Государственного образовательного учреждения высшего профессионального образования

«МОСКОВСКИЙ ГОСУДАРСТВЕННЫЙ ОТКРЫТЫЙ УНИВЕРСИТЕТ»

²УТВЕРЖДЕНО² Учебно-методическим Советом КИ(a) МГОУ

Балабан Е.И.

И Н С Т Р У К Ц И Я

для выполнения лабораторной работы

Парный регрессионный анализ с использованием электронных таблиц Excel

г. Коломна

2012 г.

1. Введение

В эконометрике для проведения расчетов часто используют табличные процессоры офисных пакетов (Excel, OpenOffice.org Calc), математические пакеты (Mathcad, Mathematica и др.) и несколько специализированных пакетов прикладных программ (STATA, SPSS, Econometric Views, Statistica и др.). Наиболее доступными для использования являются электронные таблицы офисных пакетов.

Цель работы: Изучение стандартных функций Еxcel. Построение и анализ простейших эконометрических моделей. В качестве исходной информации взяты социально-экономические показатели Московской области за последние годы .

2. Методические указания

2.1. Парная линейная регрессия показывает зависимость в виде Y=α +β X. Электронные таблицы Excel позволяет находить уравнение парной линейной регрессии путем:

- подсчета показателей по соответствующим эмпирическим формулам;

- вставки тренда на график функции;

- - используя стандартную функцию ЛИНЕЙН;

- нахождения оценок параметров, используя операции над матрицами;

2.2. Подготовка данных для расчета.

1. Из всей системы показателей, представленной на листе «ИД» рабочей книги на листе «Начало» выбираются зависимая переменная Y и факторные переменные Х1, Х2, Х3 согласно номерам, получаемым при нажатии кнопок НАЧАЛО и ВАРИАНТ. Ввод численных значений показателей следует осуществлять не копированием, а ссылкой на лист «ИД»: в левую верхнюю ячейку (С4 листа «Начало») вводится, например, «=ИД.В7», а затем формула «растягивается» на весь диапазон.

2. Выбор одной из факторных переменных производится на основе корреляционного анализа на листе «Начало» в подготовленной области J5:M5. Коэффициенты парной корреляции находятся с помощью статистической функции «КОРРЕЛ», аргументами которой служат два множества данных. В ячейку J5 вводим, например, «=КОРРЕЛ(C4:C65;C4:C65)». Адреса ячеек первого множества следует сделать абсолютными: «=КОРРЕЛ($C$4:$C$65;C4:C65)», это позволит распространить введенную формулу на всю строку занчений парных коэффициентов корреляции: активизируйте ячейку J5, поставьте курсор мышки на маркер заполнения, нажмите левую кнопку мышки и перетащите курсор заполнения через заполняемые ячейки, отпустите левую кнопку мышки.

3. Значение коэффициента корреляции показывает степень линейной связи между переменными. Для проведения парного регрессионного анализа из переменных Х1, Х2, Х3 выбираем ту, для которой абсолютная величина парного коэффициента корреляции с Y максимальна.

4. На основе расчетов, проведенных на листе Начало, делается вывод о том, какая из рассмотренных факторных переменных оказывает наибольшее воздействие на переменную Y: в ячейку М14 слдует записать «Х1», «Х2» или «Х3». При этом нужно указать в ячейках J8:J11 конкретное экономическое содержание всех включенных в модель переменных и их размерности.

5. Выбранные для парного регрессионного анализа данные (Y и выбранная факторная переменные для всех рассматриваемых результатов наблюдений) переносятся на лист «ПарРег».

2.3. Нахождение оценок параметров уравнения парной регрессии осуществляется по формулам:

где , , , .

Выборочный коэффициент детерминации можно найти по одной из формул:

Порядок выполнения работы:

Расчет рекомендуется оформлять в виде таблиц, заголовки столбцов которых введены в строке 3 листа «ПарРег» рабочей книги.

6. Найти суммы и средние значения элементов столбцов D:M. Выборочные средние для Х и Y подсчитываются ниже таблицы с данными либо делением суммы значений результатов наблюдений, либо с помощью функции СРЗНАЧ.

7. Заполнить столбцы F:J.

8. Оценки параметров уравнения парной регрессии подсчитайте в ячейках R7, T7.

9. В столбце K подсчитайте оценки значений Y: ( ), в столбце L – их отклонения от результатов наблюдений, в столбце M – квадраты отклонений, в столбце N – отклонения оценок от средних.

10. В ячейках G70, L70, N70 найти суммы квадратов Q, Q₂, Q₁, используя функцию СУММКВ

11. В ячейке V7 найдите значение R².

12. Постройте график зависимости Y и от Х (по данным в столбцах D, E, K), используя мастер диаграмм (тип диаграммы – Точечная без соединяющих отрезков). Введите название диаграммы (зависимость каких показателей рассматривается), легенду (названия столбцов, использовавшихся при построении графика) и размерности показателей.

2.4. В случае, когда рассматривается зависимость между двумя переменными самый простой способ нахождения уравнения регрессии – вставка тренда на график функции.

13. Используя график, построенный в п. 2.3 (п.12), выполните следующие действия:

- Курсор на график.

- На экране: график выделен метками.

- Диаграмма, Вставка линии тренда—

На экране: диалоговое окно Линия тренда, ярлычок Тип

Характеристики каждого типа регрессии приведены в таблице

Тип парной регрессии	Уравнение	Определяемые параметры
Линейная	y=α+βx	α, β
Полиномиальная	Y=d+c1x+c2x2+…+cnxn	d,c1,c2, ...,c n
Логарифмическая	y=b+c ln x	b, с
Экспоненциальная	y=cebx	b,с
Степенная	y=cxb	B ,с

- Исходя из того, что рассматривается линейная регрессия, выбираем Линейная.

- Курсор на ярлычок Параметры.

- На экране: диалоговое окно Линия тренда, Параметры

- Назначаем: Показывать уравнение на диаграмме, Поместить на диаграмму R².

- ОК.

- Уравнение регрессии для наглядности можно выделить жирным шрифтом и переместить в середину графика.

2.6. Нахождение оценок параметров уравнения как парной, так и множественной регрессии можно производить, используя функцию ЛИНЕЙН мастера функций. Функция рассчитывает статистику для ряда с применением метода наименьших квадратов, вычисляя прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные. Функция возвращает массив, который описывает полученную прямую. Поскольку возвращается массив значений, функция должна задаваться в виде формулы массива.

Уравнение для прямой линии имеет следующий вид: y = bx + b₀ или y = b₁x₁ + b₂x₂ + ... + b₀ (в случае нескольких интервалов значений x), где зависимое значение y является функцией независимого значения x. Значения b - это коэффициенты, соответствующие каждой независимой переменной x, а b₀ - это постоянная. Заметим, что y, x и b могут быть векторами. Функция ЛИНЕЙН возвращает массив {b_n; b_n-1;...; b₁; b₀}. ЛИНЕЙН может также возвращать дополнительную регрессионную статистику.

Синтаксис: ЛИНЕЙН( Y ; M ; конст ; статистика)

Y - это множество значений Y, которые уже известны для соотношения

Y = b₀ + b₁X₁+…+b_nX_n

M - это множество значений X, которые известны

конст - это логическое значение, которое указывает, требуется ли, чтобы константа a₀ была равна 0. (конст=1),

статистика - это логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии (статистика=1).