Regression в ms Excel

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

1. Якщо в меню сервіс ще немає команди Анализ данных, то виконуємо Сервис→Надстройки і активовуємо Пакет анализа (рис. ):

2. Якщо вихідні дані вже внесені, то виконуємо Сервис → Анализ данных → Регрессия.

3. Заповнюємо діалогове вікно введення даних і параметрів виводу (рис. 2.3), після чого натискаємо ОК:

Входной интервал Y – діапазон, який містить дані результативної (залежної) ознаки. Діапазон має складатися з одного стовпця;

Входной интервал X – діапазон, який містить дані ознаки-фактора (незалежної змінної). Максимальна кількість вхідних діапазонів рівна 16;

Метки - «прапорець», який вказує, чи містить перший рядок назви стовпців;

Константа - ноль – «прапорець», який вказує на наявність абовідсутність вільного члена в рівнянні;

Выходной інтервал – досить вказати ліву верхню комірку майбутнього діапазону;

Новый рабочий лист – можна вказати довільне ім'я нового аркуша (або не вказувати, тоді результати виводяться на новостворений лист).

Отримаємо наступні результати для розглянутого вище прикладу:

Тема 2. Побудова однофакторної криволінійної регресійної моделі

Припустимо, що точки (x_i, y_i) i=1…n, групуються навколо деякої кривої лінії. Тоді по вигляду розташування точок на кореляційному полі будують математичну модель залежності M(Y/x) від спостережуваних значень випадкової величини X, M(Y/x)= (x), де (x) – функція певного виду – може бути поліномом k – тої степені, степеневою, логарифмічною, показниковою і т. п.

Припустимо, що точки на кореляційному полі групуються навколо параболи Тоді залежність між M(Y/x)і значеннями x випадкової величини X можна записати моделлю: M(Y/x)= ₀+₁x+₂x². Лінію , навколо якої групуються точки (x_i, y_i) i=1…n будемо розглядати як оціночну (наближену) криву модельної лінії регресії.

Точкові оцінки параметрів математичної моделі знайдемо за МНК при умові мінімуму

Щоб мінімізувати S, прирівняємо часткові похідні по b₀ і b₁, b₂ до нуля. Отримаємо систему нормальних рівнянь, яка в матричному вигляді записується т. ч.:

Розв'язок системи:

=

Рівняння регресії: , використовуючи яке можна робити прогнозування середніх значень випадкової величини Y за спостережуваними значеннями x випадкової величини X.

Приклад

Розрахунок параболічного рівняння регресії за допомогою МНК.

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

На основі отриманих результатів зробити висновки:

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

2. побудувати графік емпіричної і теоретичної лінії регресії;

3. оцінити параметри моделі, використовуючи метод найменших квадратів;

4. провести перевірку адекватності моделі за допомогою відповідних статистичних критеріїв.

Розв’язок

1.Оскільки точки (x_i,y_i), i=1...7 розташовуються у вигляді параболи, виберемо в якості математичної моделі залежності Y від X регресійну модель Y= ₀+₁x+₂x²

2-Побудуємо точкову діаграму, а також графіки емпіричної і теоретичної лінії регресії:

Таблиця. Початкові дані та проміжні розрахунки для визначення впливу X наY.

Система нормальних рівнянь:



=0,2989

Емпіричне рівняння регресії:

0,2989-0,008x+0,0007x²

Завдання.

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

2. Перевірити адекватність математичної моделі експериментальним даним (x_i,y_i).

	Варіант1				Варіант2
1	X	Y			X	Y
2	20	43,5		1	70	33
3	25	43,9		2	80	40
4	30	44,2		3	90	44
5	35	45		4	100	49
6	40	46		5	110	49
7	45	46,9		6	120	51
8	50	47,5		7	130	55
9	55	49		8	140	56
10	60	51		9	150	57
11	65	53		10	160	56

	Варіант3				Варіант4
	X	Y			X	Y
1	1	32,3		1	11	167
2	2	32,5		2	21	142,5
3	3	32,6		3	25	126,7
4	4	32,5		4	27	116
5	5	32,2		5	29	111,5
6	6	31,1		6	31	109
7	7	31,12		7	42	108,1
8	8	30		8	45	105

	Варіант5				Варіант6
	X	Y			X	Y
1	78	25		1	30	53
2	156	12		2	35	54,5
3	234	112		3	40	55
4	321	225		4	45	56,8
5	381	428		5	50	57,5
6	385	426		6	55	58
				7	60	57

	Варіант7				Варіант8
	X	Y			X	Y
1	10	16		1	20	10
2	12	31		2	30	55
3	13	59		3	45	145
4	15	82		4	55	200
5	17	105		5	65	318,5
6	19	128		6	70	397
7	22	149		7	75	475,5
8	25	161		8	85	554