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

Построение коэффициентов корреляции, линейного уравнения регрессии и прямолинейного тренда средствами табличного редактора MS Excel XP
Скачать все файлы (216.9 kb.)

Доступные файлы (1):
n1.docx217kb.01.02.2014 14:04скачать

n1.docx

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

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

«Построение коэффициентов корреляции, линейного уравнения регрессии и прямолинейного тренда средствами табличного редактора 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) выберем Корреляция.

1.bmp

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

2.bmp

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

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

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

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

 

Столбец 1

Столбец 2

Столбец 1

1




Столбец 2

0,921

1

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

4.bmp

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

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

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

5.bmp

Рисунок 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) выберем Регрессия.

1

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

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

2

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

3

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

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

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

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

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

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

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

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

= 4654,947+ 0,198Xi

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


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

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

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

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

14

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

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

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

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

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

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

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

4

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

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

5

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

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

Согласно полученным результатам оцененное уравнение регрессии статистически значимо, о чем свидетельствует значение фактическое (расчетное) значение F-критерия Фишера. А также значения t-критерия Стьюдента которые равны: для а0 – 1508,707/86,549 = 17,43, а для параметра а1 - 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

  1   2   3
Учебный текст
© perviydoc.ru
При копировании укажите ссылку.
обратиться к администрации