Аппроксимація даних

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

Додавання лінії тренду або ковзаючищего середнього до рядів даних

У Excel використовуються шість різних видів ліній тренду (апроксимація і згладжування), які можуть бути додані в діаграму (мал. 18.11): 1) Лінійна апроксимація (Linear) — це пряма лінія, що щонайкраще описує набір даних. Рівняння прямої у=ах+Ь, де а — тангенс кута нахилу, b — точка пересічення прямої з віссю біля. Лінійна апроксимація застосовується для змінних, які збільшуються або убувають з постійною швидкістю.
2) Логарифмічна апроксимація (Logarithmic) добре описує позитивні, так і негативні величини, які спочатку швидко зростають або убувають, а потім поступово стабілізується. Логарифмічна апроксимація використовує рівняння у=с* lnx+Ь, де з і b константи, In — натуральний логарифм.
3) Поліноміальна апроксимація (Polynomial) використовується для опису величин, що поперемінно зростають і убувають. Її доцільно застосовувати для аналізу великого набору даних нестабільної величини. Міра полінома визначається кількістю екстремумів (максимумів і мінімумів) кривої. Поліном другої міри може описати лише один максимум або мінімум. Поліном третьої міри має один або два екстремуми. Поліном четвертої міри може мати не більше трьох екстремумів. Поліноміальна апроксимація описується рівнянням y=a+ciXi+C2X2++Cigx18, де а, Cj—Cjg — константи. Необхідна міра полінома задається в полі Міра (мал.). Максимальна величина міри — 18.
4) Статечна апроксимація (Power) дає добрі результати, якщо залежність, яка міститься в даних, характеризується постійною швидкістю росту. Прикладом такої залежності може служити графік прискорення автомобіля. Якщо в даних є нульові або негативні значення, використання статечного наближення неможливе. Статечна апроксимація описується рівнянням у=а * хn, де а і n — константи.
5) Експоненціальну апроксимацію (Exponential) слід використовувати в тому випадку, якщо швидкість зміни даних безперервно зростає. Проте для даних, які містять нульові або негативні значення, цей вигляд наближення непридатний. Експоненціальна апроксимація описується рівнянням у= а • ebx, де а і b — константи.
6) Лінійна фільтрація (Moving average) дозволяє згладити коливання даних і таким чином наочніше показати характер залежності. Така лінія тренду будується по певному числу крапок (вона задається параметром Тонкі (Period). Елементи даних усереднюються, і отриманий результат використовується як середнє значення для наближення. Так, якщо параметр Тонкі рівний 2, перша точка згладжуючої кривої визначається як середнє значення перших двох елементів даних, друга крапка — як середнє наступних двох елементів і так далі. Для розрахунку ковзаючого середнього використовується рівняння у= (Aj+Aj_i++Aj_n+i) /n.

Додавання лінії тренду до рядів даних

Для додавання лінії тренду до рядів даних виконаєте наступні дії:

•  виділите ряд даних, до якого потрібно додати лінію тренду або ковзаюче середнє;
•  виберіть команду Додати лінію тренду (Add Trendline ) у меню Діаграма (Chart). На вкладці Тип (Турі) виберіть потрібного типа регресійної лінії тренду або лінії ковзаючого середнього (мал. 18.11);

Мал. 18.11 Вибір лінії тренду

Мал. 18.12 Діаграма з нанесеною лінією тренду

•  при виборі типа Поліноміальна (Polynomial) введіть в поле Міра (Order) найбільшу міру для незалежної змінної;
•  при виборі типа Ковзаюче середнє (Moving Average) введіть в поле Крапки (Period) число крапок, використовуваних для розрахунку ковзаючого середнього.

Всі ряди даних діаграми, що підтримує лінії тренду перераховані в полі Побудований на ряду (Based On Series) . Для додавання лінії тренду до інших рядів виберіть потрібне ім'я в полі, а потім виберіть потрібні параметри. Якщо варіант Ковзаюче середнє (Moving Average) вибраний для точкової діаграми, результат залежатиме від порядку розташування значень X у вхідному діапазоні. Аби отримати правильний результат, необхідно відсортувати значення X перед побудовою лінії ковзаючого середнього.

Надїжакностюк лінії тренду

Оцінка надійності лінії тренду до фактичних даних виконується по показнику визначеності або величині R в квадраті. R може змінюватися від 0 до 1. Чим більше величина цього показника, тим достовірніше лінія тренду. Значення R2 автоматично розраховується Excel при підборі лінії тренду до даних. Це значення можна відображувати на діаграмі.
Для виведення значення R-квадрат для лінії тренду клацніть цю лінію, і виберіть команду Виділена лінія тренду (Selected Trendline) у меню Формат (Format) . На вкладці Параметри (Options) встановите прапорець помістити на діаграму величину достовірності апроксимації (R*2) (Display R-squared value on chart) (мал. 18.13). Відзначимо, що для ковзаючого середнього значення R-квадрат не може відображувати.

Мал. 18.13 виведення значення R-квадрат для лінії тренду