Лінія тренда
Лінії тренда звичайно використовуються в завданнях прогнозування. Такі завдання вирішують за допомогою методів регресійного аналізу. За допомогою регресійного аналізу можна продовжити лінію тренда вперед або назад, екстраполювати її за межі, в яких дані вже відомі, і показати тенденцію їх зміни. Можна також побудувати лінію ковзаючого середнього, яка згладжує випадкові флуктуації, ясніше демонструє модель і простежує тенденцію зміни даних.
Мал. 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
1
4876,45
5395,7
5149,515121
60606,99484
3
7154,35
8584,45
7344,230633
1538144,078
2
7332,87
6498,3
7513,819716
1031280,294
4
7432,23
8845,67
7608,077298
1531635,695
5
8134,87
9543,56
8272,069591
1616687,861
6
13687
11563,4
13394,77009
3353916,418
7
15887,5
12546
15378,74411
8024439,186
8
18357
16321,8
17581,55981
1586994,786
10
24650
22462
23103,37843
411366,2928
9
25332,3
21675,6
23695,35994
4079430,204
12
27100
28345,8
25223,73239
9747306,142
11
28873,3
28242
26749,68106
2227015,821
13
32567,5
35208823,77
14
37113,1
Сума:
15
44234,1
16
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) для різних видів моделей