Выполнение задания 3

В ППП «Excel» регрессионная модель нелинейной формы может быть выполнена только в форме экспоненциального приближения: Вставка – Функция – Статистические - ЛГРФПРИБЛ. Между тем нелинейную форму уравнения можно привести к линейной, осуществив процедуру линеаризации. Например, показательная функция

приводится к линейной форме логарифмированием обеих частей уравнения:

lnу =lna+x lnb

получаем Y=A + Bх,

где Y=lny; A=lna; B=lnb.

Теперь пересчитываем исходные данные х и у в lnx и lny: Вставка – Функция –Математические – LN.

Таблица 8. Пример расчета для показательной функции

x	y	Ln x	Ln y
		1,079181	1,361728
		1,653213	1,146128
		1,892095	1,39794
		1,94939	1,556303
		1,748188	1,672098
		1,361728	1,763428
		1,50515	1,838849
		1,812913	1,612784
		1,991226	1,716003
		1,939519	1,799341

Относительно новых переменных, lnx и lny выводим таблицу регрессии: Данные – Анализ данных – Регрессия. В окне Регрессия в качестве входного интервала х выделяем столбец lnx; в качестве входного интервала у выделяем столбец lny.

Таблица 9.Вывод итогов

Регрессионная статистика
Множественный R	0,850621
R-квадрат	0,352628
Нормированный R-квадрат	0,1905926
Стандартная ошибка	2,16016

Дисперсионный анализ
	df	SS	MS	F	Значимость F
Регрессия		1711,83	1711,83	29,704177	0,0138707
Остаток		5064,27	633,0337
Итого		6776,1

	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение
Y-пересечение	1,366991	9,30663	0,95163	0,369146
Переменная x1	0,129613	0,324557	1,644436	0,138707

Уравнение регрессии выглядит следующим образом: У=1,366991+0,129613Х. Вернемся к исходным переменным, т.е. перейдем от lnx и lny к х и у. Получим уравнение:

Проверка на значимость полученного уравнения и измерение тесноты связи проводится аналогично примерам, рассмотренным выше. Методом линеаризации можно достаточно быстро получить уравнения регрессии различной формы. Выбор лучшей формы уравнения регрессии зависит от величины остатков. Остатки – это расхождения эмпирических и теоретических (регрессионных) значений результативного признака. Чем больше сумма расхождений, тем хуже уравнение регрессии описывает связь фактора и результата.

Остатки рассчитываются как сумма квадратов отклонений исходных значений от регрессионных значений результативного признака (см. Таблица 6: Дисперсионный анализ – Остаток – SS или MS). SS – это сумма квадратов; MS – это сумма квадратов в расчете на 1 единицу числа степеней свободы (df). Сравнивать уравнения регрессии надо по MS. Чем меньше MS, тем лучше форма уравнения регрессии.