Синтаксис: лгрфприбл (відомі_значення_у; [відомі_значення_х]; [конст]; [статистика]).

Аргументи функції означають таке:

відомі_значення_у – це посилання на діапазон або масив відомих значень у ;

відомі_значення_х – це посилання на діапазон або масив значень х, які відповідають відомим значенням у. Якщо цей аргумент не заданий, то як значення х беруть послідовність {1, 2, 3, …, n}, де n – кількість відомих значень у;

конст – це логічне значення, яке визначає застосування константи. Якщо цей аргумент має значення ХИБА, то беруть b = 0, а якщо ІСТИНА або опущений, b обчислюють звичним способом;

статистика – це логічне значення, яке означає, чи потрібно вивести додаткову статистику. З регресії, наприклад, коефіцієнт кореляції. Якщо аргумент має значення ІСТИНА, то функція ЛИНЕЙН повертає додаткову регресійну статистику. Якщо аргумент має значення ХИБА або не заданий, то функція повертає лише значення коефіцієнтів.

Крім того, одновимірну експоненціальну модель можна побудувати графічно.

У діапазоні комірок В8:В10 уведена формула побудови лінійного тренда

{=ТЕНДЕНЦИЯ (В2:В7; А2:А7; А8:А10)}

У діапазоні комірок С2:С10 уведена формула побудови експоненціального тренда

{=РОСТ (В2:В7; А2:А7; А2:А10)}

Лінійний та експоненціальний тренди тісно пов’язані між собою. У діапазон комірок D2:D10 уведена формула

{=EXP(ТЕНДЕНЦИЯ (LN (В2:В7); А2:А7; А2:А10)}.

Як видно з рис. 6, значення в діапазоні С2:С10 і D2:D10 збігаються. У діапазон комірок F2:G2 I F3:G3 уведені формули

{=ЛИНЕЙН (В2:В7); А2:А7)};

{=ЛГРФПРИБЛ (В2:В7; А2:А7)}

для визначення параметрів лінійної та експоненціальної моделей.

Квадрат коефіцієнта кореляції експоненціальної моделі дорівнює 0,947 (див. рис. 6) і менший від квадрата коефіцієнта кореляції лінійної моделі ( = 09923). Отже, у цьому прикладі лінійна модель достовірніше описує залежність між спостережуваними величинами.

Виконати лінійну регресію можна також за допомогою пакета регресійного аналізу. Цей пакет в Excel досить простий у використанні, однак він дещо складніший, ніж звичайна побудова лінії тренда на діаграмі. Такий с4ладніший підхід може знадобитися з двох причин.

1. Іноді потрібно використовувати модель регресії , лінії тренда якої нема. Пакет регресійного аналізу дає змогу застосувати довільну модель лінійної регресії.

2. Може виявитись потрібною детальніша інформація про процес регресії, яку не можна одержати за допомогою лінії тренда.

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

Крок 1. Відкривають вікно Аналіз даних. Щоб викликати на екран діалогове вікно Аналіз даних, вибирають команду Сервіс → Аналіз даних… У цьому вікні відображений список інструментів аналізу, один із яких – Регресія. (Якщо пакет не активований, тобто в меню Сервіс не відображена команда Аналіз даних, то треба вибрати команду Сервіс → Надбудова й активувати команду Пакет аналізу).

Крок 2. Вибирають у спису вікно Аналіз даних команди Регресія. Для цього вибирають команду Регресія і клацають на кнопці ОК. Унаслідок цього на екрані повинно з’явитися діалогове вікно Регресія.

Крок 3а. Вибирають значення залежної змінної: використовують кнопку, яка дає змогу перейти в електронну таблицю.

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

Крок 3б. Вибирають комірки, які містять значення у.

Після переходу до електронної таблиці зазначають за допомогою мишки ті комірки, у яких містяться значення у. Коли вибрано значення у, клацають на кнопці, яка дає змогу повернутися до діалогового вікна (вона обведена кружечком), або натискають на клавішу <Enter>. У результаті повертаються до діалогового вікна Регресія.

Крок 4а. Вибирають значення залежної змінної: використовують кнопку, яка дає змогу перейти в електронну таблицю. Аналогічно треба задати значення х.

Крок 4б. Вибирають комірки, які містять значення х.

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

Крок 5. Розміщують результати регресійного аналізу.

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

Крок 6. Відображають або відміняють відображення результатів у графічному вигляді.

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

Крок 7. Виконання регресії.

Щоб виконати регресію, клацають на кнопці ОК. Результати набудуть вигляду таблиць і графіків (якщо графіки задано).

Залишок (residual) – це різниця між значеннями у, що відповідають даним, і значеннями у, через які проходить лінія регресії при кожному значенні х.

Графік залишків (residual plot) дає змогу підкреслити погані збіги і полегшує їх виявлення.

Інший клас методів кількісного прогнозування становлять методи прогнозування часового ряду. Ці методи забезпечують прогноз шляхом екстраполяції значень окремої змінної на підставі статистичних даних за минулий період. Модель часового ряду, тобто екстраполяційна функція, екстраполює (продовжує) “минулі” значення змінної в майбутнє. Дані часового ряду – це статистичні дані за минулий часовий період, упорядковані в хронологічному порядку, причому кожному відстежуваному моменту часу відповідає лише одне значення спостережуваної змінної.

Розглянемо декілька прикладів використання методу екстраполяції. Припустимо, що в журналі Wall Street Journal опубліковане зведення за останні 12 днів (включаючи сьогоднішній) цін на какао, що склалися на момент закриття біржі. З використанням цих даних потрібно передбачити завтрашню ціну на какао (також на момент закриття біржі). Розглянемо декілька способів, які дають змогу зробити це.

1. Якщо всі наявні значення цін суттєві і мають однакову цінність для прогнозу, то можна знайти середнє всіх 12 значень, і це буде найліпший прогноз на завтра.

2. Якщо останнє (сьогоднішнє) значення найбільш значиме порівняно з рештою, то воно є найкращим прогнозом на завтра.

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

4. Якщо всі значення суттєві, але сьогоднішнє 12-те значення найзначиміше, а попередні 11-, 10-, 9-те і так далі мають щораз меншу значимість, то треба знайти зважене середнє всіх 12 значень. Причому вагові коефіцієнти для останніх значень повинні бути більшими, ніж для попередніх, і сума всіх вагових коефіцієнтів повинна дорівнювати 1.

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

Тепер припустимо, що ми знаємо завтрашню ціну на какао і потрібно побудувати прогноз на післязавтра, використовуючи вже 13 значень. У цій ситуації можна застосувати перший і другий описані способи побудови прогнозу. Розглянемо третій спосіб. Потрібно взяти завтрашнє відоме значення, сьогоднішнє і значення за останні чотири дні, щоб одержати середнє значення за останні 6 днів. Цей спосіб називають побудовою плинного середнього.

Плинне середнє – це метод, що дає змогу спростити аналіз тенденції за допомогою згладжування або усереднення коливань вимірювання за деякий період. Коливання вимірювань та пов’язане з ним зміщення даних може бути результатом різних часових умов. Місячні обсяги продажу, наприклад, можуть змінюватися залежно від кількості робочих днів у місяці та від кількості продавців, що перебувають у відпустці.

Експоненціальне згладжування теж застосовують для прогнозування значень часових рядів.

Найліпшим цей метод виявився для випадків, коли дані мають дуже пологий або навіть горизонтальний тренд. Нову послідовність будують за таким правилом:

0 < а <1.

Початкове значення послідовності можна вибрати як

Під час аналізування використовують константу згладжування а, яка визначає міру впливу на прогнози похибок у попередньому періоді. Якщо обирають значення константи, близьке до 1, то вагомішими під час прогнозування будуть останні дані часового ряду, у разі вибору константи, близької до 0, вагомішими будуть віддаленіші значення.

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

У п’ятому способі прогнозування використовують метод підбору кривої. Коли існують значення змінної, що залежить від часу, то ці значення, як звичайно, називають часовим рядом, методи аналізу й екстраполяції таких даних називають аналізом часових рядів. Головна відмінність у застосуванні цього методу в моделях часових рядів від уже розглянутого методу підбору кривої для моделей прогнозування полягає в тому, що тут незалежною змінною є час. Статистичні дані, тобто значення залежної змінної, наносять на графік відповідно до часу, а потім за цими даними підбирають криву. Для одержання прогнозу криву продовжують далі в “майбутнє”. У цьому випадку продовження кривої означають обчислення значень одержаної функції (кривої) для значень часу t, які виходять за інтервал значень, за якими будували криву. Ця процедура для лінійної функції показана на рис. 6.

Рис. 6. Підбір лінійної функції

У моделях часових рядів, як і в кількісних моделях, для екстраполяції даних можна використовувати не лише лінійні функції. Наприклад, можна припустити, що у – це багаточлен від t порядку k виду

y_t = b₀ + b₁t + b₂r² + … + b_kt^k.

Як і раніше, значення коефіцієнтів b₀ + b₁ + b₂ + … + b_k можна обчислити на підставі наявних даних. Однак використання багаточленів високого порядку може дати ідеальне наближення, яке даватиме неточні прогнози.

У разі застосування методу плинного середнього обирають крок n-інтервал усереднення. Результатом використання пакета аналізу є таблиця, перші (n – 1) рядків якої не містять даних, а інші відображають усереднені та прогнозовані значення. У випадку реалізації експоненціального згладжування результатом також є таблиця зі згладженими та прогнозованими даними. Результати аналізу тенденції можуть супроводжуватися графіком.

Розглянуті методи аналізу можна реалізувати через пряме уведення вбудованих функцій у відповідні комірки, отримуючи в такий спосіб результати згладжування та прогнозів. Для плинного середнього такою функцією є СРЗНАЧ, наприклад, уводять СРЗНАЧ (В1:В3), якщо n = 3, а потім копіюють цю формулу в інші комірки діапазону. Аргументом функції СРЗНАЧ є діапазон комірок електронної таблиці, у яких записане фіксоване число n останніх спостережень, використовуваних для оцінки наступного значення у. Наприклад, якщо n дорівнює 4 й існує 15 фактичних значень у в комірках В1:В15, то значення у₁₆ обчислюють за формулою

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

Мірою порівняння є середнє абсолютних відхилень (САО) і середнє відносних помилок у відсотках (СООП), де

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

14