Построение степенной регрессии

Цель данной работы аналогичная, что для парной линейной регрессии: оценить качество модели (см. стр.1).

1) Запишите тему, цель работы и вид степенной регрессии .

2) Для построения степенной регрессии используются те же исходные данные, что и при построении линейной регрессии. Скопируйте их и вставьте на новый рабочий лист.

Цель данный работы аналогична предыдущей, значит и последовательность выполнения расчетов аналогична.

Отличительной особенностью является расчет регрессионного анализа, в который берутся не исходные данные У и Х, а логарифмированные значения этих переменных (LnY и LnX, см. таблицу 4), т.к. регрессионный анализ выполняется только для линейных функций. То есть для расчета коэффициентов а и b степенная функция приводится к линейному виду путем логарифмирования.

Для этого к таблице исходных данных (таблица 1), которую скопировали на новый рабочий лист, необходимо добавить два столбца (табл. 4 графы 4,5).

Для заполнения граф 4 и 5 (. LN(Y) и LN(X)) необходимо поставить курсор в первую ячейку столбца LN Y (синяя заливка)и на вкладке «Формулы» вызвать “ Вставить функцию ®Категория:Математические ® функция: LN” и выделить значение Y (продано) по Свердловской области и нажать «ОК». После ввода формулы захватив мышью правый нижний угол ячейки, протянуть ее вниз до конца таблицы, а затем на одну колонку вправо.

Таблица 4

Территории потребителей	Продано, т.р. Y	Заказано, т.р. X	LN (Y)	LN (X)	y^=a*x^b	(y-y^)/y	|(y-y^)/y|
Свердловская обл.	475,5		=LN(y)	=LN(x)	=а(F4)*x^b(F4)	=(гр.2-гр.6)/гр.2	=ABS(гр.7)
Республика Бурятия	2100,3	2022,2	7,649	7,611	2124,595	-0,0115	0,0115
Красноярский край	3188,4	3012,2	8,067	8,010	3219,812	-0,0098	0,0098
Омская область		1053,3	6,945	6,959	1075,812	-0,0364	0,0364
Алтайский край	2199,8	2100,1	7,696	7,649	2210,055	-0,0046	0,0046
Кемеровская обл.		19034,5	9,951	9,85	22037,49	-0,0503	0,0503
Новосибирская обл.	46104,3	38321,1	10,73	10,55	45731,91	0,0080	0,0080
Томская область	2503,8	2202,2	7,825	7,697	2322,269	0,0725	0,0725
Якутия			7,484	7,342	1603,337	0,0992	0,0992
Читинская область			6,746	6,767	880,2109	-0,0343	0,0343
							0,3713
						Аср.=	3,7126

Теперь рассчитываем параметры а и b. Для этого выполняем следующее: Данные ®а нализ данных →Регрессия.

В раскрывшемся диалоговом окне ввести толькоданные:

a) «Входной интервал Y» - выделить весь диапазон числовых значений содержащихся в колонке LN Y;

b) «Входной интервал Х» - выделить весь диапазон числовых значений содержащихся в колонке LN X;

c) «Выходной интервал» - выделить одну пустую ячейку слева под таблицей исходных данных см. рис. 1стр.4

После нажатия кнопки «ОК» в диалоговом окне Регрессии под таблицей исходных данных появятся результаты регрессионного анализа, которые называются «Вывод итогов».

В появившихся таблицах также как и при построении линейной функции необходимо выделить заливкой и подписать: коэффициенты корреляции и детерминации, критерий Фишера (табличное значение Fтабл такое же как и в парной линейной регрессии Fтабл.=2,15), коэффициенты А и в.

Обратите внимание, что с помощью Регрессии вы нашли коэффициент «А», а не «а». Следовательно, для того чтобы определить «а» необходимо поставить курсор в пустую ячейку рядом с «Выводом итогов» и на вкладке «Формулы» вызвать “ Вставить функцию ® Категория: Математические ® функция: EXP” и выделить значение «А».

После того как найдены коэффициенты а и b, рассчитываем y^ - функцию степенной регрессии

Для этого к таблице 4 необходимо добавить столбец y^ (графа 6).

Для расчета графы 6 необходимо выполнить следующие команды (как и в линейной регрессии):

1. поставить курсор в ячейку, выделенную желтым цветом;

2. ввести формулу: = абсолютный адрес коэффициента (а) * относительный адрес значения (х) заказано по Свердловской области ^ абсолютный адрес коэффициента (в).

Запись формулы представлена в таблице 4 гр.6.

Для того чтобы возвести число в степень, необходимо переключить клавиатуру на английский язык и нажать сочетание клавиш «Shift – 6» и тогда появится значок (^), означающий возведение в степень.

3. после ввода формулы нажать клавишу Enter и захватив мышью правый нижний угол ячейки, протянуть ее на 9 строчек вниз.

Далее все выполняется аналогично предыдущей работе (линейной регрессии), придерживаясь указаний изложенных выше и в лекционного материала:

· рассчитать среднюю ошибку аппроксимации (графы 7 и 8 таблица 4);

· оценить качество построенной степенной модели;

· сделать выводы по показателям;

· сравнить качество линейной и степенной моделей и сделать вывод о том, какая модель подходит для ваших данных.

Построение множественной регрессии