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

 

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

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

 

 

Регрессионная статистика
Множественный R	0,762322
R-квадрат	0,581135
Нормированный R-квадрат	0,563682
Стандартная ошибка	50,23613
Наблюдения
Дисперсионный анализ
	df	SS	MS	F	Значимость F
Регрессия		168064,8	84032,39	33,2977	8,51E-10
Остаток		121136,1	2523,668
Итого		289200,9
	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение	Нижние 95%	Верхние 95%
Y-пересечение	225,7848	27,41026	8,237239	9,67E-11	170,6728	280,8968
X8	23,38168	10,96783	2,131842	0,038166	1,329382	45,43398
X4	-503,93	69,72031	-7,22788	3,29E-09	-644,112	-363,748

Рис.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
	X4	X8	Y2	P(x)	ε	ε2	P2 (x)	ε22
	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
	0,51	1,23		=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
	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
	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
	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
	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
	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
	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 приведено на рис.9.

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

 

 

	A	B	C	D	E	F	H	I
	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
	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
	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
					Q =	=СУММ(F2: F52)	Q2 =	=СУММ(I2: I52)
					σ =	=КОРЕНЬ(F53/51)	σ2 =	=КОРЕНЬ(I53/51)
	β0	β1	β2
	225,78481426	-503,	23,381653963
	β0	β1	β2	β3	β4	β5
	247,96413983	-930,	73,537978008	1009,39006400157	-4,446	-140,188

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

 

Значения β_j находятся с помощью надстройки Excel Поиск решения по такому алгоритму:

– установить курсор на ячейке, содержащей значение функции Q (Q₂);

– Сервис – Поиск решения;

– в появившемся диалоговом окне Поиск решения (рис.11) проверить, стоит ли в поле Установить целевую ячейку адрес функции Q (Q₂), и если нет, то ввести его;

– в поле Равной щелкнуть пункт минимальному значению;

– в поле Изменяя ячейки ввести диапазон ячеек, которые отведены для значений искомых параметров ;

– щелкнуть по кнопке Выполнить;

– если решение найдено, сообщение об этом появится в диалоговом окне, где нужно щелкнуть по пункту Сохранить найденное решение. Значения найдены и находятся в отведенных для них ячейках (рис.10).

Значение суммы квадратов отклонений найденной оценки функции регрессии от наблюденных значений результирующего признака, т.е. функции Q для линейной регрессии и функции Q₂ для квадратичной регрессии, находятся в ячейках F53 и I53, линейная величина отклонений – в ячейке F54 и в ячейке I54.

 

 

Рис.11. Ввод информации для Поиска решения.

 

Таким образом, коэффициенты линейной функции регрессии P(x) следует считывать из ячеек A56,B56 и С56; коэффициенты нелинейной функции регрессии P₂(x) – из ячеек A59 F59. Для рассматриваемого примера линейная функция регрессии совпадает с полученной с помощью инструмента Регрессия, а квадратичная

P₂(x) = 247,9641 – 930,3571x₄ + 73,538x₈ + 1009,39x₄² – 4,44689x₈² – 140,1884x₄x₈

Проверка значимости полученной квадратичной оценки уравнения регрессии выполним так. Определим коэффициент корреляции значений эмпирической функции регрессии и выборочного среднего RyP₂(x). Как видно из рис.12, коэффициент корреляции достаточно большой (0,80921). Выполним еще одну проверку значимости P₂(x) с помощью коэффициента детерминации, для чего необходимо вычислить значения S_ост, S_факт.

Размещение нужных формул приведено на рис.12, а промежуточные результаты и значения коэффициента детерминации ниже. Поскольку коэффициент детерминации для случая квадратичной регрессии значительно превосходит коэффициент детерминации для случая линейной регрессии и имеет достаточно большое значение (0,472867), делаем вывод, что квадратичная регрессия достаточно хорошо согласуется со статистическими данными.

Выполним оценку значимости полученного приближения функции в целом с помощью критерия Фишера. Для этого найдем значения критерия Фишера по выборке для рассматриваемых двух видов зависимости (см. рис.12 и 13).

 

 

	R	S
	RyP(x)	RyP2(x)
	=КОРРЕЛ(C2:C52;D2:D52)	=КОРРЕЛ(C2:C52;H2:H52)
	Sост	Sост
	=F53/48	=I53/45
	Sфакт	Sфакт
	=L53/48	=N53/45
	R2	R22
	=1-R5/ (R9 + R5)	=1-S5/ (S9 + S5)
	Fрасч	F2расч
	=R11*(51-2-1)/(1-R11)/2	=S11*(51-2-1)/(1-S11)/2
	Fкрит =	3,205

 

Рис.12.Расчетные формулы

Как видно, расчетное значение F-критерия для квадратичной зависимости значительно превосходит значение F_крит ,что подтверждает ее значимость. Для линейной зависимости превышение F_расч не столь велико, что делает снова-таки предпочтительнее квадратичную оценку регрессии y₂ на x₄ и x₈ .

 

	K	L	M	N	O	Q	R	S
1		^2		^2			RyP(x)	RyP2(x)
	66,0145	4357,91	52,4372	2749,66			0,762322	0,80921
	98,0407	9611,98	63,6085	4046,04			Sост	Sост
	36,9723	1366,95	39,0068	1521,53
	121,189	14686,7	59,1584	3499,72			2523,668	2218,362
	36,6828	1345,63	52,8333	2791,36			Sфакт	Sфакт
	66,8451	4468,27	52,8975	2798,14
	25,2325	636,678	31,6051	998,881
	-64,2814	4132,09	-63,8871	4081,57			3501,349	4208,353
	3,56772	12,7286	14,147	200,138			R2	R22
	43,0760	1855,54	43,5092	1893,05			0,581135	0,654822
	-12,1715	148,144	4,46566	19,9421			Fрасч	F2расч
	37,1816	1382,47	39,3711	1550,09			33,29771	45,5293
	68,8203	4736,24	53,556	2868,24
	37,88307	1435,127	39,90716	1592,582			Fкрит =	3,205

Рис.13.Проверка значимости.

 

Таким образом, выборочное уравнение регрессии имеет вид:

 

 

V. Содержание и объем курсовой работы

Цель задания – выполнить статистический анализ результатов измерений и определить на основании исследования математической модели оценки законов и параметров распределения, корреляционные связи и статистические зависимости

1. Выполнить многомерный экономико-статистический анализ показателей (в соответствии с вариантом):

– проверить данные на засорение;

– проверить закон распределения всех параметров;

– оценить тесноту связей параметров;

– выполнить регрессионный анализ двумерных и трехмерной моделей.

2. Составить математическую модель задачи на основании корреляционного и регрессионного анализа статистических данных.

3. Работу выполнить средствами Excel и его надстроек Пакет анализа и Поиск решения.

4. Отчет по курсовой работе создать в среде Word с необходимыми по тексту результатами в виде таблиц и диаграмм Excel.

5. Отчет должен содержать:

– титульный лист;

– задание в соответствии с вариантом;

– названия и краткое описание, а также результаты каждого этапа выполнения задания;

– приложение в виде рабочей книги Excel, содержащей все выполненные расчеты (на дискете);

Варианты заданий приведены в приложении на странице 48.

 

 

VI. Литература

1. Магнус Я.Р., Катышев П.К., Пересецкий А.А. Эконометрика. Начальный курс. – М.: Дело, 1997. – 248 с.

2. Дубров А.М., Мхитарян В.С., Трошин Л.И. Многомерные статистические методы: Учебник. – М.: Финансы и статистика, 2000. – 352 с.

3. Колемаев В.А. и др. Теория вероятностей и математическая статистика: Учеб. пособие для экон. спец. вузов / В.А.Колемаев, О.В.Староверов, В.Б.Турундаевский; Под ред. В.А.Колемаева. – М.: Высш. шк.,!991. – 400 с.

4. Сивец С.А. Статистические методы в оценке недвижимости и бизнесе. Учебно-практическое пособие по статистике для оценщиков. – Запорожье, 2001. – 320 с.

5. Тюрин Ю.Н., Макаров А.А. Статистический анализ данных на компьютере / Под ред. Фигурнова В.Э. – М.: ИНФРА, 1998. – 528 с.

6. Савицкая Г.В. Анализ хозяйственной деятельности предприятия. – Минск: ООО «Новое знание», 2000. – 668 с.

7. Ларсен, Рональд У. Инженерные расчеты в Excel.: Пер. с англ. – М.: Издательский дом «Вильямс», 2004. – 544 с.

8. Гурман В.Е. Теория вероятностей и математическая статистика. Учеб. Пособие для втузов. М., «Высш. школа», 1977. – 479 с.

9. Математическая статистика: Учебник / Иванова В.М., Калинина В.Н., Нешумова Л.А. и др. – М.: Высш. школа, 1981. –371 с.

 

Приложение

Статистические данные.

 

№ п/п
	9,26	204,2	13,26	0,23	0,78	0,40	1,37	1,23	0,23
	9,38	209,6	10,16	0,24	0,75	0,26	1,49	1,04	0,39
	12,1	222,6	13,72	0,19	0,68	0,40	1,44	1,80	0,43
	10,8	236,7	12,85	0,17	0,70	0,50	1,42	0,43	0,18
	9,35	62,0	10,63	0,23	0,62	0,40	1,35	0,88	0,15
	9,87	53,1	9,12	0,43	0,76	0,19	1,39	0,57	0,34
	8,17	172,1	25,83	0,31	0,73	0,25	1,16	1,72	0,38
	9,12	56,5	23,39	0,26	0,71	0,44	1,27	1,70	0,09
	5,88	52,6	14,68	0,49	0,69	0,17	1,16	0,84	0,14
	6,30	46,6	10,05	0,36	0,73	0,39	1,25	0,60	0,21
	6,22	53,2	13,99	0,37	0,68	0,33	1,13	0,82	0,42
	5,49	30,1	9,68	0,43	0,74	0,25	1,10	0,84	0,05
	6,50	146,4	10,03	0,35	0,66	0,32	1,15	0,67	0,29
	6,61	18,1	9,13	0,38	0,72	0,02	1,23	1,04	0,48
	4,32	13,6	5,37	0,42	0,68	0,06	1,39	0,66	0,41
	7,37	89,8	9,86	0,30	0,77	0,15	1,38	0,86	0,62
	7,02	62,5	12,62	0,32	0,78	0,08	1,35	0,79	0,56
	8,25	46,3	5,02	0,25	0,78	0,20	1,42	0,34	1,76
	8,15	103,5	21,18	0,31	0,81	0,20	1,37	1,60	1,31
	8,72	73,3	25,17	0,26	0,79	0,30	1,41	1,46	0,45
	6,64	76,6	19,40	0,37	0,77	0,24	1,35	1,27	0,50
	8,10	73,01	21,0	0,29	0,78	0,10	1,48	1,58	0,77
	5,52	32,3	6,57	0,34	0,72	0,11	1,24	0,68	1,20
	9,37	199,6	14,19	0,23	0,79	0,47	1,40	0,86	0,21
	13,1	598,1	15,81	0,17	0,77	0,53	1,45	1,98	0,25
	6,67	71,2	5,23	0,29	0,80	0,34	1,40	0,33	0,15
	5,68	90,8	7,99	0,41	0,71	0,20	1,28	0,45	0,66
	5,22	82,1	17,50	0,41	0,79	0,24	1,33	0,74	0,74
	10,0	76,2	17,16	0,22	0,76	0,54	1,22	0,03	0,32
	8,16	119,5	14,54	0,29	0,78	0,40	1,28	0,99	0,89
	3,78	21,9	6,24	0,51	0,62	0,20	1,47	0,24	0,23
	6,48	48,4	12,08	0,36	0,75	0,64	1,27	0,57	0,32
	10,4	173,5	9,49	0,23	0,71	0,42	1,51	1,22	0,54
	7,65	74,1	9,28	0,26	0,74	0,27	1,46	0,68	0,75
	8,77	68,6	11,42	0,27	0,65	0,37	1,27	1,00	0,16
	7,00	60,8	10,31	0,29	0,66	0,38	1,43	0,81	0,24
	11,0	355,6	8,65	0,01	0,84	0,35	1,50	1,27	0,59
	9,02	264,8	10,94	0,02	0,74	0,42	1,35	1,14	0,56
	13,2	526,6	9,87	0,18	0,75	0,32	1,41	1,89	0,63
	9,27	118,6	6,14	0,25	0,75	0,33	1,47	0,67	1,10
	6,70	37,1	12,93	0,31	0,79	0,29	1,35	0,96	0,39
	6,69	57,7	9,78	0,38	0,72	0,30	1,40	0,67	0,73
	9,42	51,6	13,72	0,24	0,70	0,56	1,20	0,98	0,28
	7,24	64,7	17,29	0,31	0,66	0,42	1,15	1,16	0,10
	5,39	48,3	7,11	0,42	0,69	0,26	1,09	0,54	0,68
	5,61	15,0	22,49	0,51	0,71	0,16	1,26	1,23	0,87
	5,59	87,5	12,14	0,31	0,73	0,45	1,36	0,78	0,49
	6,57	108,4	15,25	0,37	0,65	0,31	1,15	1,16	0,16
	6,54	267,3	31,34	0,16	0,82	0,08	1,87	4,44	0,85
	4,23	34,2	11,56	0,18	0,80	0,68	1,17	1,06	0,13
	5,22	26,8	30,14	0,43	0,83	0,03	1,61	2,13	0,49
	18,0	43,6	19,71	0,40	0,70	0,02	1,34	1,21	0,09
	11,0	72,0	23,56	0,31	0,74	0,22	1,22	2,20	0,79

 

Продолжение таблицы

 

№ п/п
	1,45		167,69		6,40	166,32	10,08	17,72
	1,30		186,10		7,80	92,88	14,76	18,39
	1,37		220,45		9,76	158,04	6,48	26,46
	1,65		169,30		7,90	93,96	21,96	22,37
	1,91		39,53		5,35	173,88	11,88	28,13
	1,68		40,41		9,90	162,30	12,60	17,55
	1,94		102,96		4,50	88,56	11,52	21,92
	1,89		37,02		4,88	101,16	8,28	19,52
	1,94		45,74		3,46	166,32	11,52	23,99
	2,06		40,07		3,60	140,76	32,40	21,76
	1,96		45,44		3,56	128,52	11,52	25,68
	1,02		41,08		5,65	177,84	17,28	18,13
	1,85		136,14		4,28	114,48	16,20	25,74
	0,88		42,39		8,85	93,24	13,32	21,21
	0,62		37,39		8,52	126,72	17,28	22,97
	1,09		101,78		7,19	91,80	9,72	16,38
	1,60		47,55		4,82	69,12	16,20	13,21
	1,53		32,61		5,46	66,24	24,84	14,48
	1,40		103,25		6,20	67,68	14,76	13,38
	2,22		38,95		4,25	50,40	7,56	13,69
	1,32		81,32		5,38	70,56	8,64	16,66
	1,48		67,26		5,88	72,00	8,64	15,06
	0,68		59,92		9,27	97,20	9,00	20,09
	2,30		107,34		4,36	80,28	14,76	15,98
	1,37		512,60		10,3	51,48	10,08	18,27
	1,51		53,81		4,69	105,12	14,76	14,42
	1,43		80,83		4,16	128,52	10,44	22,76
	1,82		59,42		3,13	94,68	14,76	15,41
	2,62		36,96		4,02	85,32	20,52	19,35
	1,75		91,43		5,23	76,32	14,40	16,83
	1,54		17,16		2,74	153,00	24,84	30,53
	2,25		27,29		3,10	107,64	11,16	17,98
	1,07		184,33		10,4	90,72	6,48	22,09
	1,44		58,42		5,65	82,44	9,72	18,29
	1,40		59,40		6,67	79,92	3,24	26,05
	1,31		49,63		5,91	120,96	6,48	26,20
	1,12		391,27		12,0	84,60	5,40	17,26
	1,16		258,62		8,30	85,32	6,12	18,83
	0,88		75,66		1,63	101,52	8,64	19,70
	1,07		123,68		8,94	107,64	11,88	16,87
	1,24		37,21		5,82	85,32	7,92	14,63
	1,49		53,37		4,80	131,76	10,08	22,17
	2,03		32,87		5,01	116,64	18,72	22,62
	1,84		45,63		4,12	138,24	13,68	26,44
	1,22		48,41		5,10	156,96	16,56	22,26
	1,72		13,58		3,49	137,52	14,76	19,13
	1,75		63,99		4,19	135,72	7,92	18,28
	1,46		104,55		5,01	155,52	18,36	28,23
	1,60		222,11		11,4	48,60	8,28	12,39
	1,47		25,76		7,67	42,84	14,04	11,64
	1,38		29,52		4,66	142,20	16,92	8,62
	1,41		41,99		4,30	145,80	11,16	20,10
	1,39		78,11		6,62	120,52	14,76	19,41

№ вар.	Результативный признак	Факторные признаки, X	№ вар.	Результативный признак	Факторные признаки, X
	Y1 Y1 Y1 Y1 Y1 Y1 Y3 Y3 Y3 Y2 Y1 Y1 Y1 Y1 Y1 Y1 Y1 Y1	6,8,11,12,17 8,11,12,13,17 7,9,12,13,17 8,9,13,14,17 5,6,7,9,17 5,7,10,14,17 8,10,15,16,17 5,6,7,11,12 8,9,10,11,17 4,5,6,8,9 6,8,11,13,17 6,8,13,14,17 8,11,13,14,17 6,8,12,13,17 7,11,12,13,17 8,11,12,13,17 5,7,9,11,17 5,6,12,13,17		Y1 Y1 Y1 Y1 Y3 Y3 Y3 Y3 Y3 Y2 Y2 Y2 Y2 Y3 Y3 Y3 Y3	5,6,10,14,17 5,6,10,15,17 5,6,7,11,12 8,9,10,11,17 5,6,7,11,12 8,9,10,12,17 4,5,6,8,9 4,5,6,7,9 7,9,12,13,17 4,5,6,8,9 4,5,6,7,9 4,5,8,9,17 4,5,7,9,17 8,11,12,13,17 6,8,12,13,17 6,8,12,13,17 8,11,13,14,17

Варианты заданий к работе

«Статистический анализ»

Примечание. В таблице указаны индексы факторных признаков X. Так, для варианта 1 следует выполнить статистический анализ всех 53 значений показателей Y₁, X₆, X₈, X ₁₁, X₁₂, X₁₇.

 

⇐ Предыдущая3456789101112

Поделиться:

Воспользуйтесь поиском по сайту: