183757 (584787), страница 3

Просмтор этого файла доступен только зарегистрированным пользователям. Но у нас супер быстрая регистрация: достаточно только электронной почты!

Текст из файла (страница 3)

- щелкнуть по закладке Параметры и в появившемся после этого диалоговом окне щелкнуть пункты показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R^2);

- записать уравнение регрессии, заменив y и x на имена результативного и факторного признаков соответственно и оценить значимость полученного уравнения с помощью R^2.

На рис.6 приведены: точечная диаграмма зависимости X6 от X4 и две линии тренда – линейная и нелинейная. Уравнение первой совпадает с уравнением линией регрессии, полученным с помощью инструмента Регрессия. Вторая имеет уравнение , т.е. оценку линии регрессии, такого вида:

.

Причем коэффициент детерминации в первом случае равен 0,3688 , а для кубической зависимости R² = 0,4762 , т.е. предпочтительнее использовать полиномиальную зависимость как лучше согласующуюся со статистическими данными.

Для остальных двух отобранных пар факторных признаков необходимо выполнить такие же действия и получить аналогичные оценки функций регрессии.

§1.5 Регрессионный анализ трехмерной модели

Для исследования статистической зависимости одного результирующего признака от двух и более факторных признаков в Excel есть две возможности: инструмент Регрессия для случая линейной статистической зависимости и непосредственное применение метода наименьших квадратов в случае зависимости любого вида.

Алгоритм применения инструмента Регрессия отличается от описанного выше для случая двумерной модели только количеством исходных данных, размещаемых на рабочем листе и соответственно диапазоном входных параметров , вводимом в диалоговом окне Регрессия . Выходные данные также отличаются только количеством информации при сохранении их смысла.

Рис.8. Регрессия Y2 на X4,X8.

На рис.8 приведены результаты применения инструмента Регрессия к статистическим данным по признакам X4–X8–Y2 .

Оценка линейной функции регрессии y₂ на x₄,x₈ имеет вид:

Значение F–критерия F_расч =33,2977 , что значительно больше F_кр = 3,18 Это означает, что оценка достаточно хорошо согласуется с данными наблюдений. Это подтверждается и достаточно высоким значением коэффициента детерминации R² = 0,5811351 . Расчетные значения t –статистики для свободного члена и коэффициента при x₄ больше t_кр = 2,009 , что подтверждает их значимость. Для коэффициента при x₈ t_расч близко к критическому значению, что ставит под сомнение его значимость.

	A	B	C	D	E	F	H	I
1	X4	X8	Y2	P(x)	ε	ε2	P2 (x)	ε22
2	0,42	0,66	13,6	=A$56+B$56*A2+C$56* B2	=C2-D2	=E2^2	=A$59+B$59*A2+C$59*B2+D$59*A2^2+E$59*B2^2+F$59*A2*B2	=(C2-H2)^2
3	0,51	1,23	15	=A$56+B$56*A3+C$56* B3	=C3-D3	=E3^2	=A$59+B$59*A3+C$59*B3+D$59*A3^2+E$59*B3^2+F$59*A3*B3	=(C3-H3)^2
4	0,38	1,04	18,1	=A$56+B$56*A4+C$56* B4	=C4-D4	=E4^2	=A$59+B$59*A4+C$59*B4+D$59*A4^2+E$59*B4^2+F$59*A4*B4	=(C4-H4)^2
5	0,51	0,24	21,9	=A$56+B$56*A5+C$56* B5	=C5-D5	=E5^2	=A$59+B$59*A5+C$59*B5+D$59*A5^2+E$59*B5^2+F$59*A5*B5	=(C5-H5)^2
6	0,43	2,13	26,8	=A$56+B$56*A6+C$56* B6	=C6-D6	=E6^2	=A$59+B$59*A6+C$59*B6+D$59*A6^2+E$59*B6^2+F$59*A6*B6	=(C6-H6)^2
7	0,43	0,84	30,1	=A$56+B$56*A7+C$56* B7	=C7-D7	=E7^2	=A$59+B$59*A7+C$59*B7+D$59*A7^2+E$59*B7^2+F$59*A7*B7	=(C7-H7)^2
8	0,34	0,68	32,3	=A$56+B$56*A8+C$56* B8	=C8-D8	=E8^2	=A$59+B$59*A8+C$59*B8+D$59*A8^2+E$59*B8^2+F$59*A8*B8	=(C8-H8)^2
9	0,18	1,06	34,2	=A$56+B$56*A9+C$56* B9	=C9-D9	=E9^2	=A$59+B$59*A9+C$59*B9+D$59*A9^2+E$59*B9^2+F$59*A9*B9	=(C9-H9)^2

Рис.9. Размещение информации для МНК.

В случае нелинейной регрессии специального инструмента в Excel нет, необходимо выполнять действия, предусмотренные методом наименьших квадратов(МНК), используя вычислительные возможности Excel. Расположение исходных данных и формул в таблице Excel приведено на рис.9.

Все формулы вводятся только в верхнюю строку, а затем копируются по всему столбцу. На рис.9 приведены расчеты поиска оценок линейной P(x) и квадратичной P₂ (x) функции регрессии. Параметры функции регрессии β_j расположены в ячейках A56 ÷ C56 для линейной зависимости и в ячейках A59 ÷ F59 для квадратичной зависимости (см. рис.10). Ячейки F53 и I53 содержат значения функций Q – суммы квадратов отклонений.

	A	B	C	D	E	F	H	I
50	0,02	1,14	264,8	=A$56+ B$56*A50+ C$56*B50	=C50-D50	=E50^2	=A$59+B$59*A50+ C$59*B50+D$59*A50^2+E$59*B50^2+F$59*A50*B50	=(C50-H50)^2
51	0,16	4,44	267,3	=A$56+ B$56*A51+ C$56*B51	=C51-D51	=E51^2	=A$59+B$59*A51+ C$59*B51+D$59*A51^2+E$59*B51^2+F$59*A51*B51	=(C51-H51)^2
52	0,01	1,27	355,6	=A$56+ B$56*A52+ C$56*B52	=C52-D52	=E52^2	=A$59+B$59*A52+ C$59*B52+D$59*A52^2+E$59*B52^2+F$59*A52*B52	=(C52-H52)^2
53					Q =	=СУММ(F2: F52)	Q2 =	=СУММ(I2: I52)
54					σ =	=КОРЕНЬ(F53/51)	σ2 =	=КОРЕНЬ(I53/51)
55	β0	β1	β2
56	225,78481426	-503, 9302	23,381653963
57
58	β0	β1	β2	β3	β4	β5
59	247,96413983	-930, 357130	73,537978008	1009,39006400157	-4,446 88827	-140,188 41146628

Рис.10. Размещение информации для Поиска решения.

Характеристики

Список файлов ответов (шпаргалок)