4 Нахождение коэффициента корреляции

Выбирается пункт меню Сервис – Анализ данных – Корреляция.

Задается входной интервал для X и Y – А1:В16 (группирование данных – по столбцам), устанавливается флажок в окошке «Метки» (это означает, что в первой строке – метки (имена данных) – x и y), «Выходной диапазон» - на новый лист или указывается выходной интервал на исходном листе.

Полученная матрица симметрична относительно главной диагонали. Для однофакторной регрессии получаем матрицу следующего вида:

	x	y
x	1
y	-0,86389	1

Коэффициент корреляции , что свидетельствует о наличии достаточной линейной зависимости между фактором x и откликом y. Знак «-» означает, что связь обратная – с ростом фактора x отклик y уменьшается.

5 Нахождение параметров линейной регрессии

Чтобы найти параметры регрессии, выбираем пункт меню Сервис – Анализ данных – Регрессия. Здесь задаем диапазоны отдельно для Y, отдельно – для X (для многофакторной регрессии в поле «Входной интервал Х» выделяем все значения Х), устанавливаем флажок в окошке «Метки», «Остатки», «График подбора», «Выходной диапазон» – на новый лист, Ок.

Результат получаем в виде нескольких таблиц (таблицы 1.15 – 1.18) и графика подбора (рисунок 1.4). В таблицах жирным шрифтом выделены величины, которые будут использоваться для дальнейших расчетов.

Таблица 1.15 – Регрессионная статистика

Множественный R	0,8639
R-квадрат	0,7463
Нормированный R-квадрат	0,7268
Стандартная ошибка	1,7980
Наблюдения	15

Здесь R-квадрат = 0,746 (74,6%) – значит, общее качество модели хорошее; стандартная ошибка = 1,798.

Таблица 1.16 – Дисперсионный анализ

	df	SS	MS	F	Значимость F
Регрессия	1	123,6341	123,6341	38,2423	0,000033
Остаток	13	42,0279	3,2329
Итого	14	165,6620

Значимость F = 0,000033, что означает, что полученная модель адекватна по критерию Фишера исходным данным с уровнем доверия . Все дальнейшие расчеты выполняются только при условии адекватности модели.

Таблица 1.17 – Коэффициенты модели

	Коэффициенты	Стандартная ошибка	t- статистика	P- Значение	Нижние 95%	Верхние 95%
Y-пересечение	48,2720	1,0157	47,5256	5,78202 E-16	46,077	50,4663
x	-0,0012	0,0002	-6,1840	3,30228 E-05	-0,002	-0,0008

Здесь коэффициенты линейной модели , .

Оба коэффициента статистически значимы по критерию Стьюдента, т. к. для P-Значение = . и для P-Значение = .

Полученная модель .

Таблица 1.18 – Вывод остатка

Наблюдение	Предсказанное y	Остатки
1	38,31	0,03
2	44,88	-0,19
3	40,54	-1,14
4	38,37	0,56
5	44,89	2,07
6	41,96	-2,48
7	46,03	0,02
8	45,28	-1,78
9	44,17	1,94
10	45,54	-2,75
11	40,20	-0,05
12	39,33	1,11
13	46,42	3,34
14	44,51	-1,52
15	39,85	0,84

Здесь «Предсказанное y» – рассчитанные по модели значения отклика.

Рисунок 1.4 – График подбора