П р а к т и ч н а  р о б о т а  №18

Тема: " Табличний процесор MS Excel. Побудова лінії тренда. Засоби прогнозування даних"

Мета: навчитись використовувати засоби MS Excel для побудови кількісних прогнозів на наступний часовий період на основі даних за  минулий часовий період. 

Хід роботи

Завдання 1. Лінія тренда,  її побудова та прогнозування даних. Уважно прочитайте теоретичні відомості і виконайте запропоновані практичні завдання.

Теоретичні відомості

Ms Excel має засоби кількісного прогнозування на основі часових рядів. Ці засоби дозволяють зробити прогноз шляхом поширення (екстраполяції) даних на наступний часовий період на основі даних за  минулий часовий період. 

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

Кожний елемент часового  ряду називають рівнем ряду і він відповідає певному моменту часу.

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

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

У загальному вигляді часовий ряд можна представити, з урахуванням викладеного вище, у вигляді суми:

G = f(t)+ε,                   (1)

де f(t) —детермінована компонента (деяка  функція часу); ε — випадкова компонента. Функцію f(t) називають трендом. Тренд відображає вплив чинників, що визначають тривалу зміну показника в часі.

При використовуванні методу екстраполяції на практиці перш за все необхідно оцінити детерміновану компоненту,   яка   характеризує   тренд.  

Статистичний метод, який дозволяє знайти аналітичну функціональну залежніть, яка найкраще описує дані спостережень (часові ряди), називають регресією. Ця аналітична залежність використовується для прогнозування шляхом екстраполяції даних (поширення цієї залежності на наступні проміжки часу).  Цей метод потребує великого об’єму обчислень. Але сьогодні існують потужні обчислювальні  комп’ютерні системи – електронні таблиці, як наприклад MS Excel, які дозволяють дуже швидко виконувати ці обчислення.

В MS Excel лінію рівняння регресії називають лінією тренда. Вона вказує тенденцію зміни даних, нею послуговуються для складання прогнозів.

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

- лінійна; де m – тангенс кута нахилу прямої, b – ордината точки перетину прямої з віссю ординат;

- логарифмічна; де с, b – сталі;

- поліноміальна; де с₆, ..., с₁ = сталі;

- степенева; де с, b – сталі;

- експоненціальна; де с, b – сталі;

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

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

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

Крім того,  в MS Excel, побудувавши гістограму часового ряду, можна з контекстного меню рядів даних побудувати лінію тренда прямо на гістограмі і відобразити на ній  рівняння регресії з вказанням достовірності апроксимації, а далі виконати за допомогою лінії тренда графічне зображення прогнозу.  

Практичне завдання

Задача 1

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

Табл. 1

Алгоритм розв’язання задачі

1. У своїй папці Excel створіть документ –   лист MS Excel, дайте йому ім’я Практ_№43.

2. Дайте одному з робочих аркушів книги ім’я Прогнозування.

3. В комірках A1: G3 цього робочого аркуша створіть зміст наведеної вище таблиці і у рядку Рік додайте ще значення  2006 і 2007.

4. Виділіть дані у комірках В2: І3 і побудуйте діаграму - гістограму.

5. Активізуйте діаграму,  відкрийте контекстне меню ряду даних і клацніть на команді Добавить линию тренда (або меню ДиаграммаàДобавить линию тренда). На екрані з’явиться діалогове вікно Линия тренда.
   

6. На вкладці Тип цього діалогового вікна потрібно обрати тип лінії тренда. За замовчуванням активним є тип – Линейная.

7. На вкладці Параметры діалогового вікна Линия тренда можна встановити параметри лінії тренда.   В групі Прогноз  можна вказати число періодів, на які лінія тренду або складає прогноз, або визначає історію процесу.

Якщо встановлений прапорець у віконці показывать уравнение на диаграмме, то рівяння лінії тренда буде відображатись на діаграмі.

При встановленому прапорці у віконці поместить на диаграмму величину достоверности аппроксимации (R^2) на діаграмі буде відображатись величина достовірності апроксимації статистичних даних рівнянням регресії: чим ближче R^2 до 1, тим краще апроксимуються дані. Якщо ця величина лежить в межах від 0,9 до 1, то лінію тренда можна використовувати для прогнозування.

Прапорець пересечение кривой с осью  Y в точке встановлюється лише в тому випадку, коли ця точка відома.параметри, як на наведеному у цьому пункті малюнку. Значення  вперед на 2  периодов, означає створення прогнозних даних наперед на 2006 і 2007 роки. Якби ми задали назад на 2  периодов, це б означало визначення історії процесу продажу у 1998 і 1999 роках, що у даній задачі не потрібно.

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

У лінійній фільтрації задайте: точки 3.

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

Визначте прогнозні дані на 2006 і 2007 роки для лінії регресії, що відповідає лінійній фільтрації. Збережіть зміни у файлі.

Виділіть дані у комірках В2: І3 і побудуйте діаграму – графік. 
Побудуйте лінію тренда типу лінійна фільтрація. 
Збережіть зміни у файлі.

Завдання 2. Прогнозування даних з застосуванням вбудованих функцій прогнозування. Уважно прочитайте теоретичні відомості і виконайте запропоновані практичні завдання.

Для лінійної апроксимації статистичних даних; де m – тангенс кута нахилу прямої до вісі абсцис, b – ордината точки перетину прямої з віссю ординат,

MS Excel  має такі функції прогнозування, що належать до категорії статистичних функцій:

• ПРЕДСКАЗ,

• ТЕНДЕНЦИЯ.

Функція ПРЕДСКАЗ

Має такий синтаксис:

ПРЕДСКАЗ(х; відомі_знач_у; відомі_знач_х),

де х –значення незалежної величини, що спостерігається (наприклад, певне значення часу), для якого віщується значення залежної величини, що спостерігається; відомі_знач_у – масив відомих значень залежної величини, значення якої спостерігаються;

відомі_знач_х – масив відомих значень незалежної величини (наприклад, значення часу), для яких відомі значення залежної величини, яка спостерігається.

Розмір масивів відомі_знач_у та відомі_знач_х  повинен бути однаковим.

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

Замість значень у ролі аргументів можуть виступати адреси комірок та їх діапазони. Наприклад, в деяку комірку B2 введена формула:

= ПРЕДСКАЗ(A2; $B$3: ; $B$7; $C$3: ; $C$7.

Функція ТЕНДЕНЦИЯ

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

Має такий синтаксис:

ТЕНДЕНЦИЯ (відомі_знач_у; відомі_знач_х; нові_знач_ х; стала),

де нові_знач_ х – масивзначень незалежної величини, що спостерігається (наприклад, певне значення часу), для якого віщується значення залежної величини, що спостерігається; відомі_знач_у – масив відомих значень залежної величини, значення якої спостерігаються;

відомі_знач_х – масив відомих значень незалежної величини (наприклад, значення часу), для яких відомі значення залежної величини, яка спостерігається; стала – логічне значення, яке вказує, чи потрібно, щоб стала b у формулі (1) дорівнювала нулю: істина або відсутність цього аргументу – bобчислюється, хибність  – bвважається рівним 0.

Розмір масивів відомі_знач_у та відомі_знач_х повин бути однаковим.

Для багатовимірного рівняння регресії потрібно задавати масиви відомі_знач_х та нові_знач_ х для кожної незалежної змінної. Якщо аргумент нові_знач_ х відсутній, то вважається, що масив нові_знач_ х співпадає з масивом відомі_знач_х.

Для експоненціальної апроксимації статистичних даних, де с, b – сталі, MS Excel  має функцію прогнозування РОСТ.

Функція РОСТ

Має такий синтаксис:

РОСТ (відомі_знач_у; відомі_знач_х; нові_знач_ х; стала),

де нові_знач_ х –масивзначень незалежної величини, що спостерігається (наприклад, певне значення часу), для якого віщується значення залежної величини, що спостерігається; відомі_знач_у – масив відомих значень залежної величини, значення якої спостерігаються;

відомі_знач_х – масив відомих значень незалежної величини (наприклад, значення часу), для яких відомі значення залежної величини, яка спостерігається; стала – логічне значення, яке вказує, чи потрібно, щоб стала b у формулі (1) дорівнювала нулю: істина або відсутність цього аргументу – bобчислюється, хибність  – bвважається рівним 0.

Розмір масивів відомі_знач_у та відомі_знач_х повин бути однаковим.

Для багатовимірного рівняння регресії потрібно задавати масиви відомі_знач_х та нові_знач_ х для кожної незалежної змінної. Якщо аргумент нові_знач_ х відсутній, то вважається, що масив нові_знач_ х співпадає з масивом відомі_знач_х.

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

Застосуємо функції прогнозування для розв’язання наведеної вище задачі 1:

1) В комірку H4 введіть формулу = ПРЕДСКАЗ(H2; В3:G3; В2:G2) з використанням майстра функцій, натисніть кнопку ОК діалогового вікна Аргументы функции.Це буде прогноз на основі лінійної апроксимації статистичних даних. В комірці H4 з’явиться число 385,4.

2) В комірки H5:I5 введіть з використанням майстра функцій формулу =ТЕНДЕНЦИЯ(H5:I5; В3:G3; В2:G2) і натисніть клавіші Ctrl + Shift+Enter.Це також буде прогноз на основі лінійної апроксимації 385,4 та 407,9429.

3) В комірки H6:I6 введіть з використанням майстра функцій формулу =РОСТ(H6:I6; В3:G3; В2:G2) і натисніть клавіші Ctrl + Shift+Enter.Це буде прогноз на основі експоненціальної апроксимації статистичних даних. В комірках H5:I5 з’являться числа 397,3978 та    429,1666. 

Оскільки лінійна апроксимація забезпечує більшу достовірність апроксимації (R²=0,912) ніж експоненціальна (R²=0,817), то більшу достовірність мають  прогнозовані дані у комірках  H5:I5.

Завдання 3. Складіть звіт і захистіть виконану роботу

Перелік питань до захисту практичної роботи

1. Що таке часовий ряд? Наведіть приклади часових рядів.

2. Що таке регресія?

3. Що таке тренд?

4. Що таке лінія тренда?

5. Які типи апроксимації застосовуються в MS Excel для побудови лінії тренда?

6. Яка найвища степінь полінома при поліноміальній апроксимації в MS Excel?

7. Для яких типів діаграм може бути побудована лінія тренда на діаграмі?

8. Який алгоритм побудови лінії тренда на діаграмі?

9. Як змінити вигляд лінії тренда?

10. Як відобразити на діаграмі рівняння лінії тренда та достовірність апроксимації?

11. При якій достовірності апроксимації за допомогою даної лінії тренду можна прогнозувати дані на майбутнє?

12. Як оцінити, яка лінія тренда  краще прогнозуватиме дані?

13. Як за допомогою лінії тренда побудувати прогнозні дані?

14. Які вбудовані функції має MS Excel для прогнозування на основі лінійної апроксимації?

15. Які вбудовані функції має MS Excel для прогнозування на основі експоненціальної  апроксимації?

16. Які відмінності існують між функціями ПРЕДСКАЗ та ТЕНДЕНЦИЯ?

17. Яку спільну властивість мають функції ТЕНДЕНЦИЯ та РОСТ?

18. Яку відмінність має лінійна фільтрація порівняно з іншими типами апроксимації часового ряду?

З повагою ІЦ "KURSOVIKS"!