Степенева

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

Контрольні запитання

1. В яких випадках доцільно дані подати у графічному вигляді?

2. Яких правил слід дотримуватися при використанні діаграм?

3. Назвіть типи діаграм, які найчастіше використовують у процесі аналізу економічної інформації?

4. Чим відрізняється лінійна діаграма від гістограми?

5. Перелічіть кроки майстра діаграм.

6. Назвіть основні елементи діаграми

7. Коли доцільно представити дані із таблиці у вигляді графіку, а коли у вигляді кругової діаграми?

Тема: Технологія проведення статистичного аналізу засобами MS Excel. Методи прогнозування статистичних даних. Графічне прогнозування даних за допомогою лінії тренду.

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

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

Прогнозування на основі лінії тренда

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

y=f(x₁, x₂, …, x_n), де y – залежний показник, x₁, x₂, …, x_n – незалежні показники.

Функцію f, яка описує рівняння регресії називають – функцією апроксимації

Графік функції апроксимації називають лінією регресії(лінією тренду).

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

Залежно від форми кореляційних зв’язків розрізняють такі типи апроксимації:

1) лінійна

y=mx+b,

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

2) логарифмічна

y=сlnx+b,

де c, b – сталі;

3) поліноміальна

y=c₆ x⁶+…+c₁ x+b,

де c₆, …, c₁ – сталі;

y=c x^b

де c, b – сталі;

5) експоненціальна

y=c x^bx

де c, b – сталі;

До головних завдань регресійного аналізу належать:

Ø Знаходження форми залежності між змінними (інтерполяція);

Ø Прогнозування значення залежної змінної (екстраполяція).

Окремим випадком парної регресії є часовий ряд.

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

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

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

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

У загальному вигляді часовий пояс можна подати, з урахуванням викладеного вище, у вигляді суми: G=f(t)+z,, де f(t) – детермінована компонента (функція часу); z – випадкова компонента. Функцію f(t) називають трендом. Тренд відображає вплив чинників, що визначають тривалу зміну показника в часі.

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

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

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

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

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

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

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

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

Таблица 1

Обсяги продажу (тис.од.)
Рік
Обсяг

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

1. У своїй папці створіть документ – документ MS Excel, дайте йому ім'я

2. Назвіть один із робочих аркушів книги Лінія тренда.

3. У комірках А1:G3 цього робочого аркуша створіть табл.(див. мал.1)

4. Виділіть дані в комірках В2:G3 і побудуйте діаграму – гістограму (див. мал. 1)

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

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

7. На вкладці Параметры діалогового вікна Линия тренда встановіть параметри лінії тренда. В групі Прогноз можна вказати число періодів, на які лінія тренда або складає прогноз (уперед), або визначає історію процесу (назад). Задайте вперед на три періоди (роки у цьому прикладі). Натисніть на кнопку ОК. Якщо встановлено прапорець у віконці Показывать уравнение на диаграмме, то рівняння лінії тренда відображатиметься на діаграмі. При встановленому прапорці у віконці Поместить на диаграмму величину достоверности аппроксимации (R^2) на діаграмі відображатиметься величина достовірності апроксимації статистичних даних рівнянням регресії: чим ближче R^2 до 1, тим ліпше апроксимуються дані. Якщо ця величина перебуває в межах від 0,9 до 1, то лінію тренда можна використовувати для прогнозування.

8. На діаграмі буде відображена лінія тренда, що відповідає обраному типу лінії тренда (мал. 2). Щоб змінити вигляд лінії (колір, товщину, стиль), потрібно відкрити її контекстне меню і в діалоговому вікні Формат линии тренда – вкладка Вид обрати необхідні параметри.

Як бачимо, слід обирати такий тип лінії тренда, щоб значення величини R^2 перебувало в межах від 0,9 до 1. Отже, обраний тип Логарифмическая линия тренда дуже добре апроксимує задані статистичні дані (R^2=0,9893)

Значення Вперед на 3 периода полягає в створенні прогнозних даних наперед на 2008, 2009 і 2010рр. Якщо задати Назад на 3 периода, це означало б визначення історії процесу продажу у 1999, 2000 і 2001рр., що у цій задачі не потрібно.

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

9. Нанесіть на діаграмі проміжні лінії сітки і зніміть з діаграми значення обсягу продажу на наступні три роки. Внесіть дані в таблицю.

Рис. 1

Рис. 2

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

Для лінійної апроксимації статистичних даних y=mx+b,, де m ­ тангенс кута нахилу прямої до вісі абсцис, b – ордината точки перетину прямої з віссю ординат, Ms Excel має такі функції прогнозування, що належать до категорії статистичних функцій: ПРЕДСКАЗ, ТЕНДЕНЦИЯ.

За допомогою функції ПРЕДСКАЗ обчислюється одне значення рівняння лінійної регресії. Вона має такий синтаксис:

ПРЕДСКАЗ (x; відомі_знач_y; відомі_знач_x), де x – значення незалежної величини, що спостерігається (наприклад, певне значення часу), для якого передбачається значення залежної величини, що спостерігається; відомі_знач_y ­ масив відомих значень залежної величини, значення якої спостерігаються; відомі_знач_ x – масив відомих значень незалежної величини (наприклад значення часу), для яких відомі значення залежної величини, що спостерігається. Розмір масивів відомі_знач_y та відомі_знач_x має бути однаковим.

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

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

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

За допомогою функції тенденція обчислюється значення рівняння лінійної регресії для діапазону значень незалежної змінної як для випадку одновимірного, так і для багатовимірного рівняння регресії. Багатовимірна лінійна модель регресії має такий вигляд: y=m₁x₁+m₂x₂+…+m_nx_n+b.

Функція тенденція має такий синтаксис:

ТЕНДЕНЦИЯ (відомі_знач_y; відомі_знач_x; нові_знач_x;стала), де нові_знач_x – масив значень незалежної величини, що спостерігається (наприклад, певне значення часу), для якого передбачається значення залежної величини, що спостерігається; відомі_знач_ y – масив відомих значень залежної величини, значення якої спостерігаються; відомі_знач_ x – масив відомих значень незалежної величини (наприклад значення часу), для яких відомі значення залежної величини, що постерігається; стала – логічне значення, яке вказує, чи потрібно, щоб стала b у формулі дорівнювала 0: істина або відсутність цього аргументу – обчислюється, хибність – b вважається таким, що дорівнює 0.

Розмір масивів відомі_знач_y та відомі_знач_x має бути однаковим.

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

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

Функція РОСТ має такий синтаксис

РОСТ (відомі_знач_y; відомі_знач_x; нові_знач_x;стала),де нові_знач_x ­ масив значень незалежної величини, що спостерігається (наприклад, певне значення часу), для якого передбачається значення залежної величини, що спостерігається; відомі_знач_y – масив відомих значень залежної величини, значення якої спостерігаються; відомі_знач_ x – масив відомих значень незалежної величини (наприклад значення часу), для яких відомі значення залежної величини, що спостеріггається; стала – логічне значення, яке вказує, чи потрібно, щоб стала b у формулі дорівнювала 0; істина або відсутність цього аргументу – обчислюється, хибність – b вважається таким, що дорівнює 0.

Розмір масивів відомі_знач_y та відомі_знач_ x має бути однаковим.

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

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

Застосуємо вбудовані функції прогнозування з метою складання кількісного прогнозу до наведеної задачі1

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

1. У поточній книзі створіть робочий аркуш з ім’ям Функції прогнозування.

2. Перевіримо, чи є підстави застосовувати функції прогнозування лінійного тренда ПРЕДСКАЗ та ТЕНДЕНЦИЯ. Скопіюємо на цей робочий аркуш вихідну таблицю 1 із робочого аркуша Линия тренда, побудуємо гістограму і на ній лінію тренда на зразок Линейная з відображенням величини достовірності апроксимації R^2 (мал. 3).

3. Значення R^2=0,912, і це дає підстави вважати рівняння лінії тренда прийнятним для прогнозування.

4. У комірку, наприклад H4, введіть формулу:

5. = ПРЕДСКАЗ (H2;$B$3;$G$3;$B$2;$G$2), використовуючи Майстер функцій, задайте аргументи в діалоговому вікні Аргументы функции шляхом натискання на кнопку ОК. Скопіюйте формули у комірки I4 та J4.

6. Порівняйте обчисленні значення з тими, що зняті з лінії тренда, зробіть висновки.

Рис. 3

1 У комірку H5 введіть шляхом використання Майстра функцій формулу =ТЕНДЕНЦИЯ (H5;$B$3:$G$3;$B$2:$G$2),скопіюйте формули в комірки I5 та J5.(ал..4)

2 Порівняйте отримані значення з тими, що обчислені шляхом використання функції ПРЕДСКАЗ. Зробіть висновки.

3 Перевірте, чи є підстави застосовувати функцію прогнозування експоненціального тренда РОСТ. Для цього на гістограмі побудуйте лінію тренда на зразок Експоненциальная з відображенням величини достовірності апроксимації R^2. Зробимо висновок: цей тип лінії тренда не підходить для прогнозування у цьому разі статистичних даних.

4 Переконайтеся в тому, що прогнозні значення, обчислені за допомогою використання функції РОСТ, значно відрізняються від обчислених за допомогою функції ТЕНДЕНЦІЯ, ПРЕДСКАЗ та отриманих на основі лінії регресії логарифмічного типу. В комірку H6 уведіть шляхом використання Майстра функцій формулу =РОСТ(H6;$B$3:$G$3; $B$2:$G$2), скопіюйте формули в комірки I6 та J6.

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

Рис. 4

Контрольні запитання

1. У чому полягає сутність поняття часовий ряд? Наведіть приклади часових рядів.
2. Що розуміють під регресією?
3. Що таке тренд?
4. У чому полягає сутність лінії тренда?
5. Які типи апроксимації застосовуються в MS Excel для побудови лінії тренда?
6. Для яких типів діаграм можна побудувати лінію тренда на діаграмі?
7. Який алгоритм побудови лінії тренда на діаграмі?
8. За якої достовірності апроксимації за допомогою лінії тренда можна прогнозувати дані на майбутнє?
9. Як оцінити, яка лінія тренда краще прогнозуватиме дані?
10. Яким чином за допомогою лінії тренда побудувати прогнозні дані?
11. Які вбудовані функції має MS Excel для прогнозування на основі лінійної апроксимації?
12. Які вбудовані функції має MS Excel для прогнозування на основі екс поненціальної апроксимації?
13. Чим відрізняються функції ПРЕДСКАЗ і ТЕНДЕНЦИЯ?
14. Яка спільна властивість характерна для функцій ТЕНДЕНЦИЯ і РОСТ?

Дата добавления: 2015-05-23