Лабораторная работа 6
6.1 Корреляционно-регрессионный анализ в ms excel 2007
Ход работы:
На примере таблицы 1создайте файл исходных данных в MS Excel.
Таблица 1 – Данные о работе 25 производственных организаций, осуществляющих один из видов экономической деятельности
Организация |
Среднегодовая стоимость основных производственных фондов, млн. руб. |
Объем продукции, млн. руб. |
Производительность труда, тыс. шт./чел. |
1 |
42 |
44 |
2,3 |
2 |
82 |
107 |
8,4 |
3 |
52 |
60 |
5,3 |
4 |
50 |
54 |
2,4 |
5 |
65 |
86 |
7,5 |
6 |
74 |
96 |
7,3 |
7 |
65 |
113 |
8,2 |
8 |
32 |
33 |
2,5 |
9 |
68 |
72 |
5,8 |
10 |
32 |
28 |
2,6 |
11 |
32 |
32 |
2,4 |
12 |
41 |
56 |
2,5 |
13 |
40 |
49 |
2,3 |
14 |
57 |
69 |
5,4 |
15 |
40 |
68 |
7,5 |
16 |
55 |
50 |
2,6 |
17 |
37 |
43 |
2,2 |
18 |
34 |
42 |
2 |
19 |
58 |
90 |
7,5 |
20 |
23 |
22 |
1,9 |
21 |
47 |
50 |
2,5 |
22 |
47 |
52 |
2,6 |
23 |
58 |
88 |
7,7 |
24 |
71 |
87 |
7,5 |
25 |
28 |
30 |
2,1 |
Построение корреляционного поля
Для построения корреляционного поля выделяем блок исходной информации, в командной строке выбираем меню Вставка/ Диаграмма. В появившемся диалоговом окне выберите тип диаграммы: Точечная; вид: Точечная диаграмма, позволяющая сравнить пары значений (рис. 1).
Рисунок 1 – Выбор типа диаграммы
Таким образом, получаем корреляционное поле зависимости y от x. Далее добавим на графике линию тренда, для чего выполним следующие действия:
В области диаграммы щелкнуть правой кнопкой мыши по любой точке графика. Появляется контекстное меню (см. рисунок 2), в котором выбираем команду Добавить линию тренда.
Рисунок 2 – Вид окна для выбора тренда
В появившемся диалоговом окне выбираем тип графика (в нашем примере линейная) и параметры уравнения, как показано на рисунке 3.
Рисунок 3 – Установка параметров линии тренда
Нажимаем Закрыть. Результат представлен на рисунке 4.
Рисунок 4 – Корреляционное поле зависимости объема продукции от среднегодовой стоимости основных производственных средств
Коэффициент достоверности аппроксимации R2 показывает степень соответствия трендовой модели исходным данным. Его значение может лежать в диапазоне от 0 до 1. Чем ближе R2 к 1, тем точнее модель описывает имеющиеся данные.
Аналогично определяются параметры других функций.
Построение корреляционной матрицы.
В блок исходных данных добавим еще один факторный признак (производительность труда). Для построения корреляционной матрицы в меню Данные выбираем Анализ данных.
В диалоговом окне Анализ данных выбираем Корреляция (рисунок 5).
Рисунок 5 – Диалоговое окно Анализ данных
После нажатия ОК в появившемся диалоговом окне указываем входной интервал (в нашем примере В2:D26), группирование (в нашем случае по столбцам) и параметры вывода, как показано на рисунке 6.
Рисунок 6 – Диалоговое окно Корреляция
Результат расчетов представлен в таблице: