Прогнозування і регресійний аналіз
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) для різних видів моделей
Зауваження
Чим більше графік даних нагадує експоненціальну криву, тим краще обчислена|обчисляти,вичислена| крива апроксимуватиме дані. Так само, як функція ЛИНЕЙН|, функція ЛГРФПРИБЛ| повертає масив, який описує залежність між значеннями, але|та| ЛИНЕЙН| підганяє|припасовує| пряму лінію до наявних даних, а ЛГРФПРИБЛ| підганяє|припасовує| експоненціальну криву.
Будь-яку пряму можна задати її нахилом (m) і Y-перетином.
Нахилом (m). Для того, щоб визначити нахил (m) для прямої, потрібно узяти дві точки цієї прямої
(x1,y1) (x2,y2),
тоді нахил рівний (y2-y1)/(x2-x1).
у|в,біля| - перетин (b.) Перетин (b) – є|з'являється,являється| значення Yкрапки|точки|, в якій пряма перетинає вісь Y.
Рівняння прямої має вигляд|вид| Y=mx+b. Якщо відомі значення mі b, то можна обчислити|обчисляти,вичислити| будь-яку крапку|точку| на прямій, підставляючи значення Yабо Xв рівняння. Можна також використовувати функцію ТЕНДЕНЦІЯ.
Якщо є|наявний| тільки|лише| одна незалежна змінна x, то значення нахилу (m) і перетину з|із| віссю у|в,біля| (b) можна одержати|отримати| безпосередньо, використовуючи наступні|слідуючі| формули:
Нахил (m):
ІНДЕКС(ЛИНЕЙН|(известные_значения_y;известные_значения_x);1)
Перетин з|із| віссю у|в,біля| (b):
ІНДЕКС(ЛИНЕЙН|(известные_значения_y;известные_значения_x);2)
Точність апроксимації за допомогою прямої, обчисленою|обчисляти,вичисленою| функцією ЛИНЕЙН| залежить від ступеня|міри| розкиду даних. Чим ближче дані до прямої, тим більше точної є|з'являється,являється| модель, використовувана функцією ЛИНЕЙН|. Функція ЛИНЕЙН| використовує метод найменших квадратів для визначення якнайкращої|щонайкращої,найкращої| апроксимації даних.
Можна використовувати рівняння = b*m^x для прогнозу|передбачення| майбутніх значень у|в,біля|, але|та| в Microsoft Excel для цієї мети|цілі| передбачена функція ЗРОСТАННЯ|зріст|.
Можна також обчислити|обчисляти,вичислити| функцію ТЕНДЕНЦІЯ (відомі значення Y, відомі значення X) для прямої або функцію ЗРОСТАННЯ|зріст| (відомі значення Y, відомі значення X) для експоненціальної кривої. Ці функції, якщо не задавати нові значення Х, повертають масив обчислених|обчисляти,вичислених| значень Yдля фактичних значень Ху відповідність з|із| прямою або кривою. Можна побудувати|спорудити| діаграми для візуального порівняння.
Формули, які повертають масиви, повинні бути введені|запроваджені| як формули для масивів. При введенні масиву констант, такого як Известные_значения_x, як аргумент, слідує|прямує| використовувати крапки з комою для розділення|поділу| значень в одному рядку і двокрапки для розділення|поділу| рядків. Символи-роздільники можуть бути різними, залежно від національних установок.
Слід пам'ятати, що значення у|в,біля|, передбачене|пророчене| за допомогою рівняння регресії, може бути недостовірним, якщо воно знаходиться|перебуває| поза|зовні| діапазоном значень у|в,біля|, які використовувалися для визначення рівняння.