Прогнозування і регресійний аналіз

ЛІНІЯ ТРЕНДА

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

Мал. 23. Графік залежності Y(x)

Лініями тренда можна доповнити ряди даних, представлені на ненормованих плоских діаграмах з областями, лінійчатих діаграмах, гістограмах, графіках, біржових, точкових і бульбашкових діаграмах. Не можна доповнити лініями тренда ряди даних на об'ємних діаграмах, нормованих діаграмах, пелюсткових діаграмах, кругових і кільцевих діаграмах. При заміні типу діаграми на один з вищеперелічених відповідні даним лінії тренда будуть втрачені.

Для точнішого визначення лінії тренда рекомендують вибрати тип діаграми «Точкова». Для створення тренда на робочому листі використовується пункт контекстного меню точкової діаграми «Додати лінію тренда».

Microsoft Excel дозволяє заповнити комірки поряд значень, відповідних простій лінійній або експоненціальній залежності. Прогнозовані значення визначаються на основі початкових даних, вказаних на листі. Щоб екстраполювати дані відповідно до лінійної залежності Microsoft Excel додає або віднімає постійну величину, рівну різниці вказаних початкових значень. У разі експоненціальної залежності Microsoft Excel умножає початкові значення на вказану постійну величину.

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

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

По величині достовірності апроксимації R2 (прагне до 1) вибрати модель, що найбільш точно відображає залежність даних.

Розрахувати Yрасчетное для певної функції, використовуючи одержані коефіцієнти з рівняння і дані (Х) з таблиці. Порівняти значення Y і Yрасчетное. Зробити висновок, чи існує залежність даних (різниця Y і Yрасч прагнути до нуля). Одержати бракуючі дані, розповсюдивши формулу у відповідні комірки.

Мал. 24. Відображення лінії тренда для поліноміальної моделі

Y=(-1*10^-13)*B5^4+(1*10^-8)*B5^3-0,0003*B5^2+3,4957*B5-5970,9

ЗАСТОСУВАННЯ ПУНКТУ МЕНЮ «ПОШУК РІШЕННЯ»

Для розрахунку моделей довільного вигляду при рішенні задачі регресійного аналізу засобами Excel, можна скористатися вбудованим інструментом «Пошук рішення» (пункт меню «Сервіс»).

В цьому випадку потрібно наперед визначитися з видом моделі. У нашому випадку

Y=x^a+x^b

№ п/п	X	Y	Ynom=x^a+x^b	(Y-Ynom)^2
	4876,45	5395,7	5149,515121	60606,99484
	7154,35	8584,45	7344,230633	1538144,078
	7332,87	6498,3	7513,819716	1031280,294
	7432,23	8845,67	7608,077298	1531635,695
	8134,87	9543,56	8272,069591	1616687,861
		11563,4	13394,77009	3353916,418
	15887,5		15378,74411	8024439,186
		16321,8	17581,55981	1586994,786
			23103,37843	411366,2928
	25332,3	21675,6	23695,35994	4079430,204
		28345,8	25223,73239	9747306,142
	28873,3		26749,68106	2227015,821
	32567,5			35208823,77
	37113,1			Сума:
	44234,1
	45223,1
	0,9413	0,90559
	а	b

Пошук (откл|)	У(х) лин|	Лінейн. (откл|)	У(х) балка	Логар. (откл|)	Y поліном.	Поліном.(откл|)
246,1848794	7414,416067	-2018,716067	7731,060438	-2335,360438	5370,213381	25,48661851
1240,219367	7567,775421	1016,674579	7812,782825	771,6671754	7191,392963	1393,057037
-1015,519716	7653,012522	-1154,712522	7858,577026	-1360,277026	7281,282552	-782,9825518
1237,592702	8253,460538	592,2094616	8188,867718	656,8022824	7328,532663	1517,137337
1271,490409	12885,91696	-3342,356955	11250,39064	-1706,830636	7609,746041	1933,813959
-1831,370093	14680,02402	-3116,624019	12723,07027	-1159,670269	7840,967766	3722,432234
-2832,744109	16672,02955	-4126,029552	14585,09305	-2039,093047	7614,963957	4931,036043
-1259,759813	21665,40835	-5343,608353	20539,99708	-4218,197078	7547,187303	8774,612697
-641,3784318	22200,73704	261,262962	21307,92782	1154,072176	9489,127942	12972,87206
-2019,759937	23582,84376	-1907,243758	23425,94565	-1750,345649	9937,436607	11738,16339
3122,067607	24962,75865	3383,041354	25750,62521	2595,174787	11335,62431	17010,17569
1492,318941	773,0306683		4903,13784	23338,86216	13079,92401	15162,07599

Найбільш прийнятні відхилення Yрасч. Y-x^a+X^b і лінійного рівнянь.

Мал. 25. Графіки залежності у(x) для різних видів моделей

Дата добавления: 2015-05-24