Виконання лінійної регресії за допомогою лінії тренда

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

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

Для виконання лінійної регресії, в процесі якої обчислюється кутовий коефіцієнт і точка перетину апроксимуючої прямої з віссю ординат, обчислюються різні суми, що включають у себе значення x (незалежної змінної) і y (залежної змінної). За допомогою цих сум і рівнянь (2.6) можна було б обчислити кутовий коефіцієнт a ₁, і знайти точку перетину прямої регресії, що найкраще описує дані, з віссю ординат (a ₀). Однак у цьому немає потреби, тому що формули (2.6) вбудовані в Excel. При побудові на діаграмі лінії тренда Excel автоматично обчислює всі необхідні суми, потім знаходить значення коефіцієнтів a ₁ і a ₀, а також квадрат коефіцієнта кореляції (вірогідність апроксимації) R ². Завдяки тому, що лінія тренда будується на діаграмі, можна переконатися, наскільки добре вона описує дані. Рівняння лінії тренда й значення R ² за замовчуванням на діаграмі не відображаються. Щоб відобразити цю інформацію, необхідно скористатися вкладкою Параметры діалогового вікна Линия тренда.

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

Крок 2. Побудова XY-діаграми вихідного динамічного ряду.

· Визначити блок вихідного динамічного ряду, тобто діапазон комірок А6:В15. Важливо, щоб у блок увійшов і спеціальний рядок міток даних. Натиснути лівої кнопкою миші по піктограмі Мастер диаграмм. Вибрати Точечная диаграмма (тобто тип XY) у стандартному списку типів (ліворуч). У правій частині вікна вибрати Вид точкової діаграми. У нашім прикладі обраний перший вид Точечная диаграмма с маркерами. Звичайно дані на точковій діаграмі позначаються маркерами, але не з'єднуються кривими, щоб краще було видно, наскільки близько від них проходить апроксимуюча лінія.

· Клацання по кнопці Далее> активізує другий крок Мастера диаграмм, де надається можливість уточнити або задати вихідний діапазон (якщо він не був заданий ще до натискання по піктограмі Мастера диаграмм). Тут же уточнюється, як необхідно сприймати дані (за стовпцями або за рядками). Після цього щигликом по кнопціДалее> активізується третій крок Мастера диаграмм.

· На третьому кроці Мастера диаграмм встановлюються всі спеціальні параметри діаграми (заголовки, вісі, лінії сітки, легенда, підписи даних). Вибрати вкладку Заголовки і прибрати встановлений у ній за замовчуванням заголовок «Уровни». У вікні Ось Х (категорий) ввести Время (минуты), а у вікні Ось Y (значений) – Температура (К). Клацнути по кнопці Далее> і активізувати четвертий крок Мастера диаграмм.

· На четвертому кроці Мастера диаграмм визначається, де розмістити діаграму: на окремому або на наявному аркуші електронної таблиці. Діаграма за замовчуванням розміщається на наявному аркуші, якщо клацнути по кнопці Готово (рис. 2.6).

Рис. 2.6

 

Крок 3. Створити копію діаграми вихідного ряду (не обов'язково).

Крок 4. Виділити клацанням лівої кнопки миші лінію показника, для якого слід побудувати тренд (лінія повинна промаркіруватися).

Крок 5. Клацнути правою кнопкою миші по маркірованої лінії для відкриття контекстно-залежного меню.

Крок 6. Вибрати опцію Добавить линию тренда…(рис. 2.7).

Опція недоступна, якщо не виділена лінія даних – доведеться повернутися до попереднього кроку. Якщо ж усе в порядку, то відкриється основне діалогове вікно Линия тренда, що надає можливість моделювати тренд, використовуючи п'ять основних типів регресії.

Рис. 2.7

Для цих типів регресії встановленням відповідних прапорців користувач може замовити вивід мітки тренда, куди автоматично відображується вид рівняння й значення коефіцієнта детермінації (R ²), що показує, яка частка варіації ознаки Y пояснена за рахунок фактора X за даної форми тренда.

З урахуванням п'яти форм поліноміальних рівнянь тренда, можна одержати дев'ять форм рівнянь регресії. Йдеться тільки про тренди, тобто про однофакторні моделі. (В Excel, як відомо, є також потужний інструментарій лінійного багатофакторного кореляційно-регресійного моделювання.)

Крок 7. У діалоговому вікні, що відкрилося, Линия тренда перейти на вкладку Тип і вибрати команду Линейная (рис. 2.8).

Рис. 2.8

Щоб закрити діалогове вікно Линия тренда й додати до діаграми лінію тренда, натисніть кнопку OK (рис. 2.9).

Рис. 2.9

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

Крок 8. Двічі клацніть на лінії тренда, що приведе до відображення на екрані діалогового вікна Формат линии тренда. У вікні вибрати вкладку Параметры. У діалоговому вікні параметрів тренда встановити прапорець показывать уравнение на диаграмме й прапорець поместить на диаграмму величину достоверности аппроксимации R^2, щоб включити ці елементи в діаграму (рис. 2.10).

Рис. 2.10

Встановити (за бажанням) необхідне число періодів для зображення можливого прогнозу вперед або назад.

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

Якщо необхідно побудувати пряму регресії, що перетинає вісь ординат в точці y = 0, у діалоговому вікні Формат линии тренда слід встановити прапорець пересечения кривой с осью Y в точке: і ввести в текстовому вікні, розташованому праворуч, значення 0 (відображається за умовчуванням). Оскільки в цьому випадку температура не наближається до нуля при наближенні часу до нуля, немає сенсу примусово встановлювати точку перетину лінії регресії з віссю ординат на початку координат.

Клацнути по кнопці OK, після чого Excel відобразить на діаграмі рівняння регресії та величину R ² (рис. 2.11).

Рис. 2.11

Як видно з діаграми, рівняння регресії має вигляд y = 2,7758x + 296,11 з вірогідністю апроксимації 0,9864.

Задачу розв'язано.

Для одержання кожного нового тренда необхідно повторити дії від Кроку 2 до Кроку 8.

Нагадаємо, що ті ж результати були отримані за допомогою функції НАКЛОН(), ОТРЕЗОК() і КВПИРСОН(). Оскільки «ідеальній» апроксимації відповідає величина R ² = 1, можна зробити висновок, що пряма лінія не описує залежності ідеально. Присутність лінії тренда на діаграмі дозволяє візуально оцінити якість апроксимації. З прикладу видно, що лінія тренда не точно лягає на дані. Потрібно спробувати підібрати іншу криву регресії, що має деяку кривизну.