КОРРЕЛЯЦИОННО-РЕГРЕССИОННЫЙ АНАЛИЗ В MS EXCEL. Табл. 1-ДП
Исходные данные демонстрационного примера
| A | B | C | |||
| Номер единицы наблюдения | Среднегодовая стоимость основных производственных фондов, млн.руб. | Выпуск продукции, млн. руб. | |||
| 94,00 | 110,00 | ||||
| 107,00 | 101,00 | ||||
| 134,00 | 120,00 | ||||
| 157,00 | 81,00 | ||||
| 163,00 | 80,00 | ||||
| 167,00 | 114,00 | ||||
| 173,00 | 161,00 | ||||
| 173,00 | 90,00 | ||||
| 177,00 | 178,00 | ||||
| 179,00 | 107,00 | ||||
| 200,00 | 125,00 | ||||
| 201,00 | 108,00 | ||||
| 205,00 | 133,00 | ||||
| 208,00 | 124,00 | ||||
| 212,00 | 201,00 | ||||
| 213,00 | 161,00 | ||||
| 214,00 | 151,00 | ||||
| 216,00 | 169,00 | ||||
| 218,00 | 149,00 | ||||
| 230,00 | 180,00 | ||||
| 234,00 | 148,00 | ||||
| 237,00 | 162,00 | ||||
| 241,00 | 166,00 | ||||
| 248,00 | 168,00 | ||||
| 45,00 | 224,00 | ||||
| 276,00 | 171,00 | ||||
| 290,00 | 191,00 | ||||
| 298,00 | 220,00 | ||||
| 167,00 | 114,00 | ||||
| 205,00 | 133,00 | ||||
| 330,00 | 53,00 | ||||
| 260,00 | 224,00 | ||||
| Среднегодовая стоимость основных производственных фондов, млн.руб. | |||||
| 18440,00 | |||||
| 19875,00 | |||||
| 21515,00 | |||||
| 22130,00 | |||||
| 22950,00 | |||||
| 23360,00 | |||||
| 25000,00 | |||||
| 25615,00 | |||||
| 26025,00 | |||||
| 26845,00 | |||||
| 27050,00 | |||||
| 27665,00 | |||||
| 28075,00 | |||||
| 28280,00 | |||||
| 28895,00 | |||||
| 29100,00 | |||||
| 29715,00 | |||||
| 29715,00 | |||||
| 29715,00 | |||||
| 30535,00 | |||||
| 31765,00 | |||||
| 32175,00 | |||||
| 32380,00 | |||||
| 32585,00 | |||||
Таблица 2
1. Создайте файл исходных данных в MS Excel (табл. 1ДП), и/илиТаблица 2.
2. Построение корреляционного поля
Для построения корреляционного поля в командной строке выбираем меню Вставка/ Диаграмма. В появившемся диалоговом окне выберите тип диаграммы: Точечная; вид: Точечная диаграмма, позволяющая сравнить пары значений (Рис. 22).
Рисунок 22 – Выбор типа диаграммы
 |
Нажимаем кнопку Далее>. В появившемся диалоговом окне (Рис. 23) указываем диапазон значений, в нашем примере = Лист1!A2:B26 и указываем расположение данных: в столбцах.
 |
Рисунок 23– Вид окна при выборе диапазона и рядов
Нажимаем кнопку Далее>. В следующем диалоговом окне (рис. 24) указываем название диаграммы, наименование осей. Нажимаем кнопку Далее>, и Готово.
 |
Рисунок 24 – Вид окна, шаг 3.
Таким образом, получаем корреляционное поле зависимости y от x. Далее добавим на графике линию тренда, для чего выполним следующие действия:
1. В области диаграммы щелкнуть левой кнопкой мыши по любой точке графика, затем щелкнуть правой кнопкой мыши по этой же точке. Появляется контекстное меню (рис. 25).
 |
Рисунок 25 – Вид окна, шаг 4
2. В контекстном меню выбираем команду Добавить линию тренда.
3. В появившемся диалоговом окне выбираем тип графика (в нашем примере линейная) и параметры уравнения, как показано на рисунке 26.
 |
Рисунок 26 – Установка параметров линии тренда
 |
Нажимаем ОК. Результат представлен на рисунке 27.
Рисунок 27 – Корреляционное поле зависимости производительности труда от фондовооруженности
Аналогично строим корреляционное поле зависимости производительности труда от коэффициента сменности оборудования. (рисунок 28).
 |
Рисунок 28 – Корреляционное поле зависимости производительности труда
от коэффициента сменности оборудования
3. Построение корреляционной матрицы.
Для построения корреляционной матрицы в меню Сервис выбираем Анализ данных.
С помощью инструмента анализа данных Регрессия, помимо результатов регрессионной статистики, дисперсионного анализа и доверительных интервалов, можно получить остатки и графики подбора линии регрессии, остатков и нормальной вероятности. Для этого необходимо проверить доступ к пакету анализа. В главном меню последовательно выберите Сервис/ Надстройки. Установите флажок Пакет анализа (Рисунок 29)
 |
Рисунок 29 – Подключение надстройки Пакет анализа
В диалоговом окне Анализ данных выбираем Корреляция (Рисунок 30).
 |
Рисунок 3.0 – Диалоговое окно Анализ данных
После нажатия ОК в появившемся диалоговом окне указываем входной интервал (в нашем примере А2:D26), группирование (в нашем случае по столбцам) и параметры вывода, как показано на рисунке 31.
 |
Рисунок 31 – Диалоговое окно Корреляция
Результат расчетов представлен в таблице 4.
Таблица 4 – Корреляционная матрица
| Столбец 1 | Столбец 2 | Столбец 3 | |
| Столбец 1 | |||
| Столбец 2 | 0,3395753 | ||
| Столбец 3 | -0,1020202 | -0,161494 |
ОДНОФАКТОРНЫЙ РЕГРЕССИОННЫЙ АНАЛИЗ
