Построение коэффициентов корреляции, линейного уравнения регрессии и прямолинейного тренда средствами табличного редактора MS Excel XP - файл n1.docx

Построение коэффициентов корреляции, линейного уравнения регрессии и прямолинейного тренда средствами табличного редактора MS Excel XP

Доступные файлы (1):

n1.docx

217kb.

01.02.2014 14:04

скачать

n1.docx

Всемирный технологический университет

Лабораторная работа по курсу эконометрика

«Построение коэффициентов корреляции, линейного уравнения регрессии и прямолинейного тренда средствами табличного редактора MS Excel XP»


Оренбург 2007г.
Содержание

	стр.
Часть 1. Анализ корреляции с использованием возможностей табличного редактора MS Excel
Цель работы и задачи работы
Исходные данные для проведения лабораторной работы
Построение корреляционного поля
Построение парного линейного коэффициента корреляции модуля Анализ данных
Построение парного линейного коэффициента корреляции с помощью встроенных функций
Часть 2. Построение парного линейного уравнения регрессии
Цель работы и задачи работы
Исходные данные для проведения лабораторной работы
Построение парной линейной регрессии с помощью модуля Анализ данных
Оценка параметров парного линейного уравнения регрессии с помощью встроенных функций
Часть 3. Прогнозирование развития явления на основе прямолинейного тренда
Цель работы и задачи работы
Исходные данные для проведения лабораторной работы
Построение тренда графическим способом
Построение тренда ручным способом на основе результатов макроса Анализ данных
Построение тренда с помощью встроенных функций
Приложение 1 Исходные данные для анализа корреляции
Приложение 2 Исходные данные для построения регрессионной модели
Приложение 3 Исходные данные для построения прямолинейного тренда

Часть 1. АНАЛИЗ КОРРЕЛЯЦИИ С ИСПОЛЬЗОВАНИЕМ ВОЗМОЖНОСТЕЙ ТАБЛИЧНОГО РЕДАКТОРА MS EXCEL
Цель работы: Изучение способов построения корреляционного поля и показателей корреляции.

Задание: Используя соответствующие данные вариантов (приложение 1) оценить взаимосвязь между экономическими показателями средствами табличного редактора MS Excel XP.

Исходные данные для проведения первой части лабораторной работы:

В качестве исходных данных для проведения иллюстрации применения корреляционно-регрессионных методов воспользуемся информацией по 15 промышленным предприятиям:

№	Произведено продукции в месяц, млн. руб.	Среднедневная заработная плата одного работающего, руб.
	Y	X
1	14,6	245,1
2	15,8	259,0
3	15,0	257,2
4	15,8	261,8
5	15,1	258,8
6	14,5	247,2
7	14,6	255,2
8	12,5	234,0
9	14,7	256,0
10	12,2	231,0
11	16,8	276,0
12	13,7	234,0
13	13,2	245,0
14	16,2	265,0
15	12,4	212,0

Построение корреляционного поля

Для того чтобы построить поле корреляции необходимо воспользоваться мастером постройки диаграмм:

Шаг 1. Для запуска Мастера диаграмм необходимо для этого в главном меню выберем: Вставка -> Диаграмма…

Шаг 2. Далее с помощью мастера создания диаграмм проходим 4 шага. Вначале выбираем вкладку Стандартные и тип диаграмм Точечные и нажимаем кнопку Далее > затем следуем указаниям мастера. В результате построения получаем следующий график (рисунок 1), отражающий взаимосвязь между явлениями.
Рисунок 1 - Поле корреляции между стоимостью произведенной продукции и средней заработной платой

Анализируя полученный график можно предположить наличие прямой корреляционной взаимосвязи между исследуемыми показателями.
Построение парного линейного коэффициента корреляции модуля Анализ данных

Для построения парного линейного коэффициента корреляции с помощью встроенного макроса «Анализ данных» необходимо в главном меню набрать Сервис -> Анализ данных… и в появившемся окне (рисунок 2) выберем Корреляция.



Рисунок 2 – Окно выбора метода обработки данных



Рисунок 3 – Окно установки диапазона анализируемых показателей

Далее переходим к окну (рисунок 3) в котором необходимо указать по каким показателям необходимо строить коэффициент, для этого нажмем кнопку и выделим диапазон данных.

После нажатия кнопки ОК получим данные приведенные в таблице 1.

Таблица 1- Результаты оценки парного линейного коэффициента корреляции

	Столбец 1	Столбец 2
Столбец 1	1
Столбец 2	0,921	1

Согласно приведенным данным между показателем Y и X наблюдается сильная прямая корреляционная взаимосвязь (т.к. значение попадает в диапазон - больше 0,7).
Построение парного линейного коэффициента корреляции с помощью встроенных функций
Для построения коэффициента корреляции в табличном редакторе Excel можно воспользоваться встроенными функциями. Для этого в главном меню выберем Вставка -> fx Функция…



Рисунок 4 - Окно выбора функция

В появившемся окне (рисунок 4) в поле Категория выберем Статистические, а в поле Выберите функцию укажем КОРРЕЛ (данная функция вычисляет значение парного линейного коэффициента корреляции).

В появившемся окне (рисунок 5) необходимо в поле Массив 1 указать значения показателя Y, а в поле Массив 2 значения X.



Рисунок 5 – Окно установок функции КОРРЕЛ

После нажатия кнопки ОК получим значение искомого коэффициента равное 0,921.
Часть 2. ПОСТРОЕНИЕ ПАРНОГО ЛИНЕЙНОГО УРАВНЕНИЯ РЕГРЕССИИ
Цель работы: Изучение способов определения параметров парного линейного уравнения регрессии.

Задание: Используя соответствующие данные вариантов оценить парную линейную регрессию средствами табличного редактора MS Excel XP.

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

№	Прибыль от продаж, тыс. руб.	Стоимость основных фондов предприятия, тыс. руб.
	Y	X
1	9400	32320
2	15000	34680
3	8200	12790
4	8200	30560
5	13700	36910
6	9200	16550
7	12400	47110
8	14000	44720
9	8600	30690
10	14700	39520
11	6300	11180
12	8500	23760
13	8800	16310
14	9200	19480
15	13400	35350
16	10900	29830

Построение парной линейной регрессии с помощью модуля Анализ данных
Для построения регрессионного с помощью встроенного макроса Анализ данных необходимо в главном меню необходимо набрать Сервис -> Анализ данных… и в появившемся окне (рисунок 6) выберем Регрессия.



Рисунок 6 - Окно выбора метода обработки данных

В окне (рисунок 7) установок регрессионного уравнения необходимо указать зависимую переменную Входной интервал Y: (Input Y Range:) и независимую переменную Входной интервал X: (Input X Range:). После чего получаем результаты регрессионного анализа (рисунок 8).



Рисунок 7 – Установки для построения парного линейного уравнения регрессии



Рисунок 8 - Результаты построения парного линейного уравнения регрессии
Первая таблица Регрессионная статистика (Regression Statistic) содержит следующие данные:

Множественный R (Multiple R) – множественный коэффициент корреляции (в данном случае, так как рассматривается регрессия одной независимой переменной на X на Y, это парный коэффициент корреляции);

R-квадрат (R Square) - Множественный коэффициент детерминации.

Нормированный R-квадрат (Adjusted R Square) - Скорректированный коэффициент детерминации.

Стандартная ошибка (Standard Error) - Стандартная ошибка или среднеквадратическая ошибка.

Наблюдения (Observations) - Объем совокупности или число уровней ряда.

Во второй таблице представлены результаты дисперсионного анализа. Результатом данного анализ является расчет фактического значения F-статистики Фишера.

В третьей таблице находятся параметры уравнения и показатели, характеризующие их качество. Так во втором столбце находятся параметры искомого уравнения:

= 4654,947+ 0,198X_i

Получаем, что при увеличении стоимости основных фондов на 1 тыс. руб., прибыль от продаж по данной группе предприятий в среднем увеличится на 0,198 тыс. руб.


Оценка параметров парного линейного уравнения регрессии с помощью встроенных функций

В табличном редакторе Excel находится огромное количество встроенных функция (математические, статистические, финансовые и др.) все они предназначены для того, чтобы облегчить исследователю расчет некоторых формул.

Рассмотрим некоторые важные статистические функции необходимые для построения регрессионных моделей.

Шаг 1: Для вставки необходимой функции в главном меню выберем Вставка -> fx Функция…(рисунок 9).



Рисунок 9 – Окно выбора необходимой функции

Шаг 2: Для определения параметров парного линейного уравнения регресии служит функция ЛИНЕЙН. Синтаксис данной функции следующий:

ЛИНЕЙН(известные_значения_y;известные_значения_x;конст;статистика)

Конст — логическое значение, которое указывает, требуется ли, чтобы параметр а₀ был равен нулю.

• 
  Если конст имеет значение ИСТИНА или опущено, то а₀ вычисляется обычным образом.
  
• 
  Если аргумент конст имеет значение ЛОЖЬ, то а₀ полагается равным 0 и значения a подбираются так, чтобы выполнялось соотношение = ax.
  

Статистика — логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии.

• 
  Если аргумент статистика имеет значение ИСТИНА, то функция ЛИНЕЙН возвращает дополнительную регрессионную статистику, так что возвращаемый массив будет иметь вид: {а_m;a_m-1;...;а₀:se_m;se_m-1;...;se₁;se₀:R²;se_y:F;df:ss_reg;ss_resid}.
  
• 
  Если аргумент статистика имеет значение ЛОЖЬ или опущен, то функция ЛИНЕЙН возвращает только коэффициенты a и постоянную а₀.
  

Более подробную информацию по данной функции можно получить в MS Excel, для этого в главном меню необходимо выбрать Справка->Справка: Microsoft Excel или нажав клавишу F1.

Шаг 3: Вводим в поле табличного редактора формулу, как показано на рисунке 10.



Рисунок 10 – Расчет параметров уравнения парной линейной регрессии с применением функции ЛИНЕЙН

Шаг 4: Выделяем диапазон ячеек E2:F6, далее нажимаем клавишу F2, затем сочетание славишь Ctrl+Shift+Enter (ввод массива данных) в итоге получаем результаты представленные на рисунке 11.



Рисунок 11 – Результаты построения уравнения парной линейной регрессии с помощью функции ЛИНЕЙН

Итоги, полученные в результате применения функции ЛИНЕЙН следующие:

• 
  E2=97,453 – параметр а₁
  
• 
  F2=1508,707 – параметр а₀
  
• 
  Е3=2,468 – стандартная ошибка параметр а₁
  
• 
  F3=86,549 – стандартная ошибка параметр а₀
  
• 
  Е4=0,964 – коэффициент детерминации R²
  
• 
  F4=331,023 – стандартная ошибка модели
  
• 
  Е5=1560 – фактическое значение F-статистики Фишера
  
• 
  F5=58 – число степеней свободы
  
• 
  Е6=170899259 – регрессионная сумма квадратов
  
• 
  F6=6355418 – остаточная сумма квадратов
  

Согласно полученным результатам оцененное уравнение регрессии статистически значимо, о чем свидетельствует значение фактическое (расчетное) значение F-критерия Фишера. А также значения t-критерия Стьюдента которые равны: для а₀ – 1508,707/86,549 = 17,43, а для параметра а₁ - 97,453/2,468=39,49. Данные значения намного превосходят табличное значение (t_табл = 2,1604), что указывает на статистическую значимость параметров.

ПРОГНОЗИРОВАНИЕ РАЗВИТИЯ ЯВЛЕНИЯ НА ОСНОВЕ ПРЯМОЛИНЕЙНОГО ТРЕНДА
Цель работы: Изучение способов прогнозирования состояния исследуемого показателя в будущем.

Задание. Используя трендовую модель прогнозирования динамики рентабельности промышленного предприятия средствами табличного редактора MS Excel.

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

Годы	Рентабельность производства, %
1990	22
1991	25
1992	26
1993	29
1994	31
1995	34
1996	43
1997	44
1998	43
1999	49
2000	52
2001	52
2002	51,4
2003	53
2004	53,6
2005	54
2006	55,6
2007	57