Однофакторный регрессионный Анализ

КОРРЕЛЯЦИОННО-РЕГРЕССИОННЫЙ АНАЛИЗ В 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

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