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

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-08-13