Лабораторна робота №1

Тема: «Обчислення парних лінійних, степеневих та параболічних рівнянь регресії»

Мета: набуття навичок обчислення парних рівнянь регресії та їх аналізу.

1. Основні теоретичні положення

Нехай дані результати статистичних спостережень

x	x1	x2	...	xn
y	y1	y2	...	yn

Тут х – факторна ознака; у – результативна ознака.

Рівняння регресії відбиває залежність результативної ознаки (у) від факторної ознаки (х) у середньому.

Рівняння, у якому враховується одна факторна ознака, називається парним.

Обчислення параметрів парного (також і множинного) рівняння регресії можна робити за допомогою процесора електронних таблиць Excel двома способами:

• стандартним методом найменших квадратів;

• з використанням убудованої функції ЛИНЕЙН(...) процесора електронних таблиць MS Excel.

Рівняння парної регресії може бути задане у вигляді:

–лінійне рівняння;

–гіперболічне рівняння;

_{– параболічне рівняння;}

– показове рівняння;

–степеневе рівняння й ін.

При побудові рівняння регресії перевіряється його адекватність і вибирається та регресійна модель, що має менше значення критерію методу найменших квадратів (залишкового розсіювання Q_зал).

Обчислення рівняння регресії, тобто визначення параметрів цього рівняння, здійснюється методом найменших квадратів (методом Ейткена).

1.1. Нехай обчислюється лінійна регресійна модель. За вихідними даними потрібно визначити її параметриaіb, а також визначити її адекватність.

Порядок обчислень зводиться до наступного:

1. обчислюють середні значення ;;

2. обчислюють середні квадратичні відхилення

; ;

3. обчислюють коефіцієнт кореляції

;

4. обчислюють коефіцієнти регресії

; ;

5. обчислюють помилку коефіцієнта кореляції

;

6. обчислюють залишкову дисперсію

_;

7. обчислюють помилки коефіцієнтів регресії

; ;

8. обчислюють коефіцієнти надійності (значущості) коефіцієнтів кореляції і регресії (значення статистики Стюдента)

; ;;

9. обчислюють критерій адекватності Фішера

, де і– ступені волі; для парного рівняння регресії = 1, = n-2.

1.2. Якщо рівняння регресії задане степеневою моделлю , то шляхом логарифмування його зводять до форми, лінійної в логарифмах

.

Позначимо ln y = Y, ln a = A , lnx = X і отримаємо рівняння вигляду

.

Для розв’язання цього рівняння виконують розрахунки за формулами п. 1.1, але над змінними Y і X. Для переходу до вихідної форми запису обчислюють .

1.3. Якщо рівняння регресії задане параболічноюмоделлю, то для зведення її до лінійної форми виконують замінутаі отримують рівняння вигляду

_.

Для розв’язання цього рівняння виконують розрахунки за формулами п. 1.1, але над змінними х₁тах₂.

1.4. Обчислення рівнянь регресії може виконуватись із застосуванням процесора електронних таблиць MS Excel за допомогою убудованої функції ЛИНЕЙН(...).

Порядок обчислення зводиться до наступного:

1) викликають функцію ЛИНЕЙН(...) (категорія «Статистичні» майстра функцій);

2) у вікні цієї функції, виділяючи мишкою відповідні області таблиці вихідних даних, вказують область значень результуючої ознаки y і факторної ознаки х (якщо розраховують парне рівняння) або факторних ознак x₁, х₂, ..., х_m (якщо розраховують множинне рівняння). Параметри константа і статистика задають рівними 1, натискають «Готово».

Примітка: константа =1 означає, що рівняння регресії повинне мати вільний член а. Статистика =1 означає, що крім коефіцієнтів рівняння регресії повинні виводитись статистичні характеристики:

• помилки коефіцієнтів регресії (S_a, S_b1, ..., S_bm);

• коефіцієнт детермінації (r²);

• залишкова дисперсія (S_зал);

• статистика Фішера (F);

• число ступенів волі (ν);

• розсіювання регресії ;

• залишкове розсіювання ;

3) виділити область комірок (5 х (m+1)), включаючи комірку, у якій був отриманий результат обчислення функції ЛИНЕЙН(...);

4) щигликом мишки активізувати рядок формул;

5) утримуючи Ctrl+Shift, натиснути Enter.

У результаті одержати таблицю

bm	bm-1	...	b1	a
Sbm	Sb(m-1)	...	Sb1	Sa
r^2	Sзал	...	н/д	н/д
F		...	н/д	н/д
		...	н/д	н/д

2. Завдання

Обстежено 10 однотипних родин, для кожної з який установлений доход (x) і витрати на харчування (y) (табл. 1).

Таблиця 1

Доходи та витрати на харчування

x, грн	y, грн
100+10N	70+5N
130+10N	90+5N
150+10N	100+5N
160+10N	95+5N
175+10N	130+5N
180+10N	120+5N
185+10N	140+5N
200+10N	150+5N
215+10N	150+5N
220+10N	145+5N

Установити залежність витрат на харчування (y) від величини місячного доходу (x) у лінійній та степеневій формі із застосуванням стандартного метода найменших квадратів та убудованої функції ЛИНЕЙН(...) процесора електронних таблиць MS Excel, а також у параболічній формі із застосуванням убудованої функції ЛИНЕЙН(...) процесора електронних таблиць MS Excel.

Для кожної моделі оцінити значущість коефіцієнтів кореляції і регресії, адекватність рівняння регресії.

Побудувати графік кореляційного поля та лінії отриманих теоретичних кривих.

Проаналізувати, яке з отриманих рівнянь регресії більш точно описує залежність у від х.

Сформулювати висновки.

3. Порядок виконання роботи

3.1. Сформувати таблицю вихідних даних (табл. 2 стовпчики 1-2).

3.2. Обчислити лінійне рівняння регресії :

• скласти розрахункову таблицю для обчислення лінійної регресії (табл. 2 стовпчики 3-5);

Таблиця 2

	1	2	3	4	5	6	7
	…	…	…	…	…	…	…
	…	…	…	…	…	…	…
	…	…	…	…	…	…	…

• обчислити методом найменших квадратів лінійне рівняння залежності витрат на харчування від доходу родини, коефіцієнт кореляції, значущість коефіцієнтів кореляції і регресії, показник адекватності лінійної моделі (за формулами 1-9 п. 1.1);

• обчислити рівняння лінійної регресії та його статистичні характеристики за допомогою функції ЛИНЕЙН(...) (категорія «Статистичні» майстра функцій) (див. п.1.4);

• сформулювати висновки за лінійною регресією.

3.3. Обчислити степеневе рівняння регресії :

• скласти розрахункову таблицю для обчислення степеневої регресії (табл. 3 стовпчики 1-7);

Таблиця 3

	1	2	3	4	5	6	7	8	9
	…	…	…	…	…	…	…	…	…
	…	…	…	…	…	…	…	…	…
	…	…	…	…	…	…	…	…	…

• обчислити методом найменших квадратів степеневе рівняння залежності витрат на харчування від доходу родини, коефіцієнт кореляції, значущість коефіцієнтів кореляції і регресії, показник адекватності степеневої моделі (за формулами 1-9 п. 1.1);

• обчислити степеневе рівняння регресії та його статистичні характеристики за допомогою функції ЛИНЕЙН(...) (категорія «Статистичні» майстра функцій) (див. п.1.4);

• сформулювати висновки за степеневою регресією.

4. Обчислити параболічне рівняння регресії :

• скласти розрахункову таблицю для обчислення параболічної регресії (табл. 4 стовпчики 1-3);

Таблиця 4

	1	2	3	4	5
	…	…	…	…	…
	…	…	…	…	…
	…	…	…	…	…

• обчислити параболічне рівняння регресії та його статистичні характеристики за допомогою функції ЛИНЕЙН(...) (категорія «Статистичні» майстра функцій) (див. п.1.4);

• визначити значущість коефіцієнтів регресії (за формулою 8 п. 1.1)

• сформулювати висновки за параболічною регресією.

4. Побудувати графік кореляційного поля та лінії отриманих теоретичних кривих:

• побудувати графік кореляційного поля, використовуючи майстер діаграм (тип діаграми – точковий);

• використовуючи контекстне меню точок кореляційного поля додати лінію тренда (тип – лінійна та у вкладці «параметри» вказати на необхідність виводити рівняння регресії та величину достовірності апроксимації);

• використовуючи контекстне меню точок кореляційного поля додати лінію тренда (тип – степенева та у вкладці «параметри» вказати на необхідність виводити рівняння регресії та величину достовірності апроксимації);

• використовуючи контекстне меню точок кореляційного поля додати лінію тренда (тип – поліноміальна другого ступеня та у вкладці «параметри» вказати на необхідність виводити рівняння регресії та величину достовірності апроксимації).

4. Зіставити лінійне, степеневе та параболічне рівняння:

• за розрахованими рівняннями регресії визначити теоретичні значення результативної ознаки (табл. 2 стовпчик 6 , табл. 3 стовпчик 8, табл. 4 стовпчик 4);

• обчислити (табл. 2 стовпчик 7, табл. 3 стовпчик 9, табл. 4 стовпчик 4) та визначити, яке з розрахованих рівнянь більш точно описує залежність витрат родини на харчування від доходів.

4. Звіт з лабораторної роботи повинен містити

• вихідні дані;

• результати обчислень лінійного і степеневого рівнянь регресії двома способами;

• результати обчислень параболічного рівняння регресії та значущості його коефіцієнтів;

• графік кореляційного поля та ліній теоретичних кривих;

• висновки.

5. Приклади обчислення рівнянь регресії

Нехай дана наступна статистична вибірка про вік дітей (х) та кількість викликів дільничного лікаря (у):

	1	2	3	4	5	6	7	8	9	10
	8	11	10	16	16	20	19	25	24	23

Установити залежність кількість викликів дільничного лікаря (у) від віку дітей (х) у лінійній та степеневій формі із застосуванням стандартного метода найменших квадратів та убудованої функції ЛИНЕЙН(...) процесора електронних таблиць MS Excel, а також у параболічній формі із застосуванням убудованої функції ЛИНЕЙН(...) процесора електронних таблиць MS Excel.

Для кожної моделі оцінити значущість коефіцієнтів кореляції і регресії, адекватність рівняння регресії.

Побудувати графік кореляційного поля та лінії отриманих теоретичних кривих.

Проаналізувати, яке з отриманих рівнянь регресії більш точно описує залежність у від х.

Сформулювати висновки.

5.1. Обчислення лінійного рівняння методом найменших квадратів.

Складаємо розрахункову табл. 5 (стовпчики 1-5).

Таблиця 5

	1	2	3	4	5	6	7
	1	8	1	64	8	8,64	0,40
	2	11	4	121	22	10,54	0,21
	3	10	9	100	30	12,44	5,97
	4	16	16	256	64	14,35	2,74
	5	16	25	256	80	16,25	0,06
	6	20	36	400	120	18,15	3,42
	7	19	49	361	133	20,05	1,11
	8	25	64	625	200	21,96	9,26
	9	24	81	576	216	23,86	0,019
	10	23	100	529	230	25,76	7,64
	55	172	385	3288	1103	172	30,82
	5,5	17,2	38,5	328,8	110,3	17,2	3,082

1) 5,5;17,2;

2) 2,872281;5,741084;

3) 0,952092;

4) 1,90303;6,733333;

5) 0,10812;

6. 1,962914;

7. 1,340925; 0,21611;

8. 8,805854; 5,021408;8,805854;

9. F = 77,54306.

5.2. Обчислення рівняння регресії з використанням убудованої функції ЛИНЕЙН(…). Отримаємо

1,90303	6,733333
0,21611	1,340925
0,90648	1,962914
77,54306	8
298,7758	30,82424

Як видно, результати обчислень за допомогою убудованої функції ЛИНЕЙН(…) і методом найменших квадратів ідентичні.

Висновок: лінійне рівняння регресії має вигляд .

Коефіцієнт кореляції , тому взаємозв’язок між віком дитини (х) та кількістю викликів дільничного лікаря (у) можна вважати сильним. Крім того цей взаємозв’язок є прямим, тобто при збільшенні віку дитини (х) загальна кількість викликів лікаря (у) також збільшується (в середньому це збільшення складає 1,9 виклики). Коефіцієнт детермінації дорівнює 0,906, тобто 90,6% варіації кількості викликів (у) обумовлено варіацією віку дитини (х), а 9,4% - впливом інших факторів. Розрахункові значення ,ібільше табличного значення (2,31), тому коефіцієнт кореляції і коефіцієнти лінійного рівняння регресії значущі. Тому що розрахункове значення статистики Фішера для лінійного рівняння більше табличного (F_табл(1; 8; 0,05) = 5,32), то лінійне рівняння регресії адекватно описує залежність кількості викликів дільничного лікаря (y) від віку дитини (x).

2. Обчислення степеневого рівняння регресії методом найменших квадратів.

Складаємо розрахункову табл. 6 (стовпчики 1-7).

Таблиця 6

	1	2	3	4	5	6	7	8	9
	1	8	0	2,079442	0	4,324077	0	7,37	0,40
	2	11	0,693147	2,397895	0,480453	5,749902	1,662094	10,55	0,20
	3	10	1,098612	2,302585	1,206949	5,301898	2,529648	13,02	9,12
	4	16	1,386294	2,772589	1,921812	7,687248	3,843624	15,11	0,79
	5	16	1,609438	2,772589	2,59029	7,687248	4,462309	16,96	0,93
	6	20	1,791759	2,995732	3,210402	8,974412	5,367632	18,64	1,84
	7	19	1,94591	2,944439	3,786566	8,669721	5,729614	20,19	1,42
	8	25	2,079442	3,218876	4,324077	10,36116	6,693464	21,64	11,31
	9	24	2,197225	3,178054	4,827796	10,10003	6,982898	23,0	1,00
	10	23	2,302585	3,135494	5,301898	9,831324	7,219742	24,29	1,66
	55	172	15,10441	27,79769	27,65024	78,68702	44,49103	170,78	28,66
	5,5	17,2	1,510441	2,779769	2,765024	7,868702	4,449103	17,07	2,87

1) 1,510441;2,779769;

2) 0,695407;0,376276;

3) 0,95704;

4) 0,51784;1,9976;7,37134;

5) 0,10251;

6) 0,12198;

7) 0,09224;0,05547;

8) 9,335675; 21,6574;9,33567;

9) 87,15482.

5.4. Обчислення степеневої моделі за допомогою убудованої функції ЛИНЕЙН(…). Отримаємо

0,517842	1,997599
0,055469	0,092236
0,915926	0,121981
87,15482	8
1,296802	0,119034

_.

Висновок: степеневе рівняння регресії має вигляд .

Коефіцієнт кореляції , тому взаємозв’язок між віком дитини (х) та кількістю викликів дільничного лікаря (у) можна вважати сильним. Крім того цей взаємозв’язок є прямим, тобто при збільшенні віку дитини (х) загальна кількість викликів лікаря (у) також збільшується. Коефіцієнт детермінації дорівнює 0,916, тобто 91,6% варіації кількості викликів (у) обумовлено варіацією віку дитини (х), а 8,4% – впливом інших факторів. Розрахункові значення ,ібільше табличного значення (2,31), тому коефіцієнт кореляції і коефіцієнти степеневого рівняння регресії значущі. Тому що розрахункове значення статистики Фішера для степеневого рівняння більше табличного (F_табл(1; 8; 0,05) = 5,32), то степеневе рівняння регресії адекватно описує залежність кількості викликів дільничного лікаря (y) від віку дитини (x).

5.3. Обчислення параболічного рівняння регресії за допомогою убудованої функції ЛИНЕЙН(…).

Складаємо розрахункову табл. 7 (стовпчики 1-3).

Таблиця 7

	1	2	3	4	5
	1	1	8	7,36	0,40
	2	4	11	10,12	0,78
	3	9	10	12,65	7,05
	4	16	16	14,98	1,04
	5	25	16	17,10	1,20
	6	36	20	19,00	1,00
	7	49	19	20,69	2,86
	8	64	25	22,17	8,01
	9	81	24	23,44	0,32
	10	100	23	24,49	2,22
	55	385	172	172	24,88
	1	1	8	7,36	0,40

• розраховуємо параболічне рівняння регресії та його статистичні характеристики за допомогою функції ЛИНЕЙН(...) (категорія «Статистичні» майстра функцій)

  -0,10606	3,06970	4,40000
  0,08205	0,92616	2,21759
  0,92450	1,88547	#Н/Д
  42,85753	7	#Н/Д
  304,71515	24,88485	#Н/Д

• розраховуємо значущість коефіцієнтів регресії

t_a = 1,9841; t_b1 = 3,3144; t_b2 = 1,2926;

Висновок: параболічне рівняння регресії має вигляд

.

Коефіцієнт детермінації дорівнює 0,9245, тобто 92,45% варіації кількості викликів (у) обумовлено варіацією віку дитини (х), а 7,55% – впливом інших факторів. Розрахункові значення іменше табличного значення (2,37), тому коефіцієнтиа та b₂ параболічного рівняння регресії не значущі, а більше табличного значення, тому коефіцієнтb₁ – значущий. Тому що розрахункове значення статистики Фішера для параболічного рівняння більше табличного (F_табл(2; 7; 0,05) = 4,74), то параболічне рівняння регресії адекватно описує залежність кількості викликів дільничного лікаря (y) від віку дитини (x).

4. Будуємо графік кореляційного поля та лінії отриманих теоретичних кривих.

Рис. 1. Залежність у від х

5.5. Зіставимо розраховані рівняння.

Маючи a і b, розраховуємо і(табл. 2 стовпчик 6-7, табл. 3 стовпчик 8-9, табл. 7 стовпчики 4-5) та(підсумок стовпчиків _{відповідних таблиць).}

Висновок: тому що для параболічної моделі менше ніж для лінійної та степеневої, то можна сказати, що в даному випадку параболічна модель більш точно описує залежність кількості викликів дільничного лікаря (y) від віку дитини (x).