Корреляционно-регрессионный анализ в MS Excel

КОРРЕЛЯЦИОННО-РЕГРЕССИОННЫЙ  АНАЛИЗ В MS EXCEL

 

1. Создайте файл исходных данных в MS Excel (например, таблица 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).

 

Рисунок 30 – Диалоговое окно Анализ данных

 

После нажатия ОК в  появившемся диалоговом окне указываем  входной интервал (в нашем примере  А2:D26), группирование (в нашем случае по столбцам) и параметры вывода, как показано на рисунке 31.

Рисунок 31 – Диалоговое окно Корреляция

 

Результат расчетов представлен  в таблице  4.

 

Таблица 4 – Корреляционная матрица

	Столбец 1	Столбец 2	Столбец 3
Столбец 1	1
Столбец 2	0,3395753	1
Столбец 3	-0,1020202	-0,161494	1

 

ОДНОФАКТОРНЫЙ РЕГРЕССИОННЫЙ АНАЛИЗ

 С ПРИМЕНЕНИЕМ  ИНСТРУМЕНТА РЕГРЕССИИ

 

Для проведения регрессионного анализа зависимости производительности труда от фондовооруженности в меню Сервис выбираем Анализ данных и указываем инструмент анализа Регрессия (Рисунок 32).

Рисунок 32 –  Диалоговое окно Анализ данных

 

После нажатия ОК в  диалоговом окне Регрессия указываем входной интервал Y (в нашем примере А2: А26)  и входной интервал X (в нашем примере B2: B26), а также параметры вывода, остатки, нормальную вероятность как указано на рисунке 33.

 

Рисунок 33 – Диалоговое окно Регрессия