6 Лабораторна робота №6 Регресійний аналіз даніх

6.1 Постановка завдання

1. Побудувати лінійне рівняння регресії Y на X для запропонованих даних. Знайти точкові та інтервальні оцінки параметрів рівняння.

2. Перевірити значимість регресії та оцінити адекватність моделі.

3. Побудувати точковий та інтервальний прогноз для останнього значення x в таблиці.

4. Побудувати графік, відобразивши лінію регресії, вибіркові та прогнозні значення.

6.2 Теретичні відомості

Лінійний регресійний аналіз полягає у визначені параметрів емпіричної залежності у(х) = kх + b, яка описує зв’язок між деяким числом n пар значень x_i та y_i, забезпечивши при цьому найменшу середньоквадратичну похибку. Параметри k та b підбираються так, щоб мінімізувати суму квадратів відхилень теоретичних значень залежної змінної y від табличних значень, тобто мінімізувати функцію

де n - кількість табличних значень x_i та y_i.

Мінімум функції z знаходимо, розв’язуючи систему лінійних рівнянь:

Розв’язком системи є вирази:

Перший спосіб.

Занесемо табличні значення x_i та y_i в діапазон A2 : В22.

Для значень k та b відведемо клітини D2 та E2, а в клітині F2 розмістимо функцію z. В клітині F2 набираємо формулу {= СУММКВРАЗН(В2: В22; E2 + D2* А2 :А22)}.

В клітину С2 вводимо формулу {= $D$2 * А2 + $E$2} та копіюємо її в діапазон СЗ : С22.

Далі вибираємо команду Сервис/Поиск решения та заповнюємо діалогове вікно Поиск решения. Натискаємо кнопку Выполнить. В клітинах D2 : F2 з’являється результат.

Другий спосіб

Параметри k та b можна також визначити за допомогою функції НАКЛОН (SLOPE) та ОТРЕЗОК (INTERCEPT).

Функція НАКЛОН визначає коефіцієнт нахилу лінійного тренда.

Синтаксис:

НАКЛОН (відомі значення Y; відомі значення X) Функція ОТРЕЗОК визначає точку перетину лінії лінійного тренда з віссю ординат. Синтаксис:

ОТРЕЗОК (відомі значення X; відомі значення Y). В клітинах D3 та Е3 знайдені значення k та b відповідно по формулам

= НАКЛОН (В2:В22; А2:А22)

= ОТРЕЗОК (В2:В22; А2:А22)

6.3 Індивідуальні завдання

1. Лінійна регресія

Вариант 1		Вариант 2		Вариант 3		Вариант 4		Вариант 5		Вариант 6
24,00	53,59	38,45	148,00	60,25	3769,53	36,08	429,76	53,35	33,12	14,89	7,73
4,45	3,92	26,45	103,01	51,47	2792,37	18,54	55,85	31,22	16,52	38,05	21,63
34,89	81,41	31,79	124,09	13,03	196,97	0,34	8,56	85,04	56,88	3,59	0,95
55,53	132,62	28,22	109,75	4,39	21,49	47,10	810,41	27,47	13,70	38,59	21,95
53,97	128,72	-1,02	-1,37	23,38	603,58	32,70	318,07	39,15	22,46	33,17	18,70
64,66	155,46	16,53	65,92	57,06	3416,21	16,66	34,82	9,49	0,22	41,95	23,97
-13,67	-40,98	52,43	201,74	54,44	3126,52	23,90	145,42	-8,65	-13,39	2,68	0,41
25,32	57,09	34,37	133,72	39,20	1643,63	35,87	416,38	39,50	22,73	43,56	24,94
51,90	123,55	46,76	180,19	0,92	-7,37	39,45	528,27	48,21	29,25	8,89	4,13
8,27	13,46	20,07	78,77	22,15	547,96	45,63	721,86	45,57	27,27	24,39	13,43
16,20	34,29	4,60	20,37	25,71	727,40	15,57	24,28	15,19	4,49	2,39	0,23
-3,81	-12,72	61,83	237,47	14,20	233,10	8,61	-15,33	-9,98	-14,39	4,08	1,25
-6,94	-23,55	39,48	152,52	18,39	382,32	31,43	265,38	18,91	7,28	8,77	4,06
10,45	19,92	25,45	99,92	44,53	2115,48	50,14	938,74	53,56	33,27	5,50	2,10
14,53	30,52	40,14	155,04	31,31	1062,93	35,01	411,39	26,93	13,30	18,40	9,84
-12,36	-37,10	-0,61	0,17	19,35	428,47	24,02	137,53	43,72	25,89	55,42	32,05
18,64	40,40	58,96	225,56	15,60	279,08	59,22	1337,07	24,29	11,32	9,52	4,51
21,92	49,00	-8,02	-27,97	63,37	4164,85	63,11	1590,47	35,42	19,66	2,98	0,59
32,70	75,54	21,50	85,21	49,88	2626,49	22,63	143,97	31,10	16,42	36,39	20,64
22,69	51,53	98,55	376,97	38,39	1577,62	51,52	1000,24	43,77	25,92	9,79	4,67
100		-10		96		75		-21		67

Вариант 7		Вариант 8		Вариант 9		Вариант 10		Вариант 11		Вариант 12
22,71	122,52	58,12	124,00	15,55	441,25	-8,93	-337,26	13,87	9,24	35,43	217,31
47,57	234,18	38,61	91,56	7,21	38,04	22,54	421,15	-18,97	-20,16	45,44	233,43
-9,31	-72,77	5,14	-35,56	38,95	3224,62	62,60	1522,48	-20,45	-29,94	49,40	309,53
-3,76	-38,96	80,52	225,67	28,14	1695,58	29,61	684,80	24,16	31,59	50,61	367,53
22,17	159,23	28,26	78,43	-6,80	134,22	12,30	214,81	72,27	80,33	2,80	1,98
44,78	257,17	53,61	139,54	21,79	937,04	41,18	965,75	55,78	69,54	34,50	200,17
43,35	208,45	65,64	128,06	22,08	1064,96	-8,97	-308,32	44,02	45,42	42,89	266,56
54,55	316,74	45,73	114,32	4,07	-3,47	44,63	1055,36	35,38	45,05	54,48	343,05
19,86	125,18	28,20	58,24	23,80	1334,64	68,82	1584,37	31,77	44,73	25,18	139,72
48,28	278,51	28,02	47,66	58,09	7373,03	5,77	44,95	37,53	47,64	9,40	45,55
36,27	265,26	27,55	76,16	39,32	3388,01	44,21	1044,43	10,61	25,34	76,16	466,16
46,54	267,90	44,87	111,60	8,21	87,01	5,22	30,71	54,48	67,98	52,72	331,44
11,99	97,11	42,74	124,76	23,93	1108,07	15,20	230,31	50,26	69,91	37,40	270,37
49,36	285,12	8,83	-3,73	47,39	4846,42	44,78	1059,27	12,77	17,93	42,92	266,75
22,05	118,50	16,04	49,33	31,38	2009,17	44,10	1141,52	15,36	28,03	39,96	247,25
14,65	143,39	45,42	113,35	28,30	1645,48	28,55	637,24	10,12	14,75	11,94	42,29
28,73	159,24	48,22	122,31	32,64	2325,50	17,30	314,78	49,96	52,55	88,89	570,17
14,65	63,36	49,08	165,05	29,87	1845,52	41,07	962,92	8,54	12,84	14,29	70,84
30,63	170,85	38,90	92,48	48,45	5172,90	34,94	843,41	29,88	30,45	27,58	165,52
24,62	154,20	15,68	18,17	32,90	2263,18	38,05	884,20	19,25	25,69	61,51	349,45
93		3		2		78		62		99

2. Нелінійна регресія

X	1	2	3	4	5	6
1	-1,77683	9,223168	20,22317	31,22317	42,22317	53,22317
2	18,44257	34,44257	50,44257	66,44257	82,44257	98,44257
3	37,76474	62,76474	87,76474	112,7647	137,7647	162,7647
4	49,9835	87,9835	125,9835	163,9835	201,9835	239,9835
5	72,33133	127,3313	182,3313	237,3313	292,3313	347,3313
6	54,16412	130,1641	206,1641	282,1641	358,1641	434,1641
7	98,65819	199,6582	300,6582	401,6582	502,6582	603,6582
8	140,9502	270,9502	400,9502	530,9502	660,9502	790,9502
9	152,133	315,133	529,14	641,133	804,133	967,133
10	193,098	393,098	593,098	793,098	993,098	1193,098
11	224,0957	392,48	706,0957	947,0957	1000	1429,096
12	267,5309	553,5309	839,5309	1125,531	1411,531	1697,531
13	325,2237	660,2237	995,2237	1330,224	1665,224	2000,224
14	380,2649	768,2649	1156,265	1544,265	1932,265	2320,265
15	423,8207	868,8207	1313,821	1758,821	2203,821	2648,821
16	420	1006,321	1512,321	2018,321	2524,321	3030,321
17	566,9595	1137,96	1708,96	2279,96	2850,96	3421,96
18	641,3485	1281,349	1921,349	2561,349	3201,349	3841,349
19	709,3451	1422,345	2135,345	2848,345	3561,345	4274,345
20	786,7301	1576,73	2366,73	3156,73	3946,73	4736,73
21	867,2976	1738,298	2609,298	3480,298	4699,99	5222,298
22	969,4264	2060,38	2881,426	3837,426	4793,426	5749,426
23	1044,147	2089,147	3134,147	4179,147	5224,147	6269,147
24	1136,138	2274,138	3412,138	4550,138	5688,138	6826,138
25	1229,868	2464,868	3699,868	4934,868	6169,868	7404,868
26	1355,722	2691,722	4027,722	5363,722	6699,722	8035,722
27	1200,36	2890,657	4331,657	5772,657	7213,657	8654,657
28	1573,757	3123,757	5000,25	6223,757	7773,757	9323,757
29	1656,451	3319,451	4982,451	6645,451	8308,451	9971,451
30	1796,615	3890,36	5356,615	7136,615	8916,615	10696,61
31	1884,876	3785,876	5686,876	7587,876	9488,876	11389,88
32	2031,389	4057,389	6083,389	8109,389	10135,39	12161,39
33	2164,022	4319,022	6474,022	8629,022	10784,02	12939,02
34	2307,189	4595,189	6883,189	9171,189	11459,19	13747,19
35	2424,155	4849,155	7274,155	9699,155	12124,15	14549,15
36	2560,762	5126,762	7692,762	10258,76	12824,76	15390,76
37	2717,751	5428,751	8139,751	10850,75	13561,75	16272,75
38	3019,39	5716,187	8576,187	11436,19	14296,19	17156,19
39	3020,576	6033,576	9046,576	12059,58	15072,58	18085,58
40	3155,558	6325,558	9495,558	12665,56	15835,56	19005,56