Лабораторна робота №2 на тему Табличний процесор MS Excel. Побудова лінії тренда. Засоби прогнозування даних
« Назад
ЛАБОРАТОРНА РОБОТА №2Тема: "Табличний процесор MS Excel. Побудова лінії тренда. Засоби прогнозування даних"Мета роботи: навчитись використовувати засоби MS Excel для побудови кількісних прогнозів на наступний часовий період на основі даних за минулий часовий період. Хід роботи Скопіюйте цю роботу у свою папку С. У цьому документі дайте відповіді на питання до захисту роботи. У папці ВС створіть документ програми Excel з ім’ям Практ_2. Лист 1 перейменуйте на Лінія Тренду, а Лист 2 – на Функції прогнозуванн.
Завдання 1. Прогнозування на основі лінії тренду.Деяка фірма накопила статистичні дані про обсяг продаж своєї продукції в деякому регіоні, які наведені у табл. 1. Для прийняття рішення стосовно плану випуску продукції на найближчі два роки фірмі потрібно мати кількістний прогноз. Побудувати кількісний прогноз на найближчі три роки.
МЕТОДИЧНІ РЕКОМЕНДАЦІЇMs Excel має засоби кількісного прогнозування. Ці засоби дозволяють зробити прогноз шляхом поширення (екстраполяції) даних на наступний часовий період на основі даних за минулий часовий період. Часовим рядом називають числову послідовність даних спостережень, які характеризують зміну певної величини, наприклад, певного економічного показника у часі. Кожний елемент часового ряду називають рівнем ряду і він відповідає певному моменту часу. Бізнесмени і підприємці завжди мають у своєму розпорядженні такі часові ряди даних, наприклад дані про продаж за попередні дні, місяці, роки, сезонні дані. В складніших ситуаціях використовується математичне моделювання, пов’язане з вивченням залежностей багаточинників. При прогнозуванні на основі часових рядів прийнято розглядати кожний рівень ряду як суму детермінованої і випадкової компонент, причому детерміновану компоненту апроксимують деякою функцією, яка відображає закономірності зміни даного показника. При прогнозуванні методом екстраполяції звичайно головна увага надається визначенню тенденцій розвитку детермінованої компоненти і її екстраполяції. У загальному вигляді часовий ряд можна представити, з урахуванням викладеного вище, у вигляді суми: G = f(t)+ε, (1) де f(t) —детермінована компонента (деяка функція часу); ε — випадкова компонента. Функцію f(t) називають трендом. Тренд відображає вплив чинників, що визначають тривалу зміну показника в часі. При використовуванні методу екстраполяції на практиці перш за все необхідно оцінити детерміновану компоненту, яка характеризує тренд. Статистичний метод, який дозволяє знайти аналітичну функціональну залежність, яка найкраще описує дані спостережень (часові ряди), називають регресією. Ця аналітична залежність використовується для прогнозування шляхом екстраполяції даних (поширення цієї залежності на наступні проміжки часу). Цей метод потребує великого об’єму обчислень. Але на сьогодні існують потужні обчислювальні комп’ютерні системи – електронні таблиці, як наприклад MS Excel, які дозволяють дуже швидко виконувати ці обчислення. В MS Excel лінію рівняння регресії називають лінією тренда. Вона вказує тенденцію зміни даних, нею послуговуються для складання прогнозів. Лінію тренда будують на основі пласкої діаграми. При цьому для побудови лінії тренду може бути використаний один із п’яти типів апроксимації: 1. лінійна ; де m – тангенс кута нахилу прямої, b – ордината точки перетину прямої з віссю ординат; 2. логарифмічна ; де с, b – сталі; 3. поліноміальна ; де с6, ..., с1 = сталі; 4. степенева ; де с, b – сталі; 5. експоненціальна; , де с, b – сталі; 6. лінійна фільтрація -- кожна точка даних на лінії тренда будується на основі середнього вказаної кількості точок даних (періодів). Чим більша кількість періодів встановлюється, тим більш гладкою, але менш точною, стає лінія тренду. На діаграмі можна виділити будь-який ряд даних і додати до нього лінію тренда. Коли лінія тренду додається до ряду даних, вона зв’язується з ним, і тому при зміні значень у ряді даних лінія тренду автоматично перераховується і оновлюється на діаграмі. Крім того, користувачу надається можливість обирати точку, в якій лінія тренда перетинає вісь ординат, відображення на діаграмі рівняння регресії і величини достовірності апроксимації. Крім того, в MS Excel, побудувавши гістограму часового ряду, можна з контекстного меню рядів даних побудувати лінію тренда прямо на гістограмі і відобразити на ній рівняння регресії з відображенням достовірності апроксимації, а далі виконати за допомогою лінії тренда графічне зображення прогнозованих даних.
Алгоритм розв’язання задачі
Як бачимо, обирати тип лінії треду потрібно такий, щоб значення величини R^2 знаходилось в межах від 0,9 до 1. Отже, обраний тип Логарифмическая линія тренду дуже добре апроксимує задані статистичні дані (R^2=0,9823). Значення вперед на 3 периодов, означає створення прогнозних даних наперед на 2011, 2012 і 2013 роки. Якби ми задали назад на 3 периодов, це б означало визначення історії процесу продаж у 1999, 2000 і 2001 роках, що у даній задачі не потрібно. Зауваження: незважаючи на високу точність апроксимації найкраща лінія тренду не завжди забезпечує високу точність прогнозування.
Завдання 2. Прогнозування із застосуванням вбудованих функцій прогнозування. Застосувати вбудовані функції прогнозування для складання кількістного прогнозу до навкденого у завданні 1 прикладу.
МЕТОДИЧНІ РЕКОМЕНДАЦІЇДля лінійної апроксимації статистичних даних ; де 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. У поточній книзі створіть робочий аркуш з ім’ям Функції прогнозування. 2. Перевіримо, чи маємо підстави застосовувати функції прогнозування лінійного тренда ПРЕДСКАЗ та ТЕНДЕНЦИЯ. Для цього скопіюємо на цей робочий аркуш вихідну таблицю 16.1 з робочого аркуша Лінія тренда, побудуємо гістограму і на ній – лінію тренду типу Линейная з відображенням величини достовірності апроксимації R^2 (рис.5). 3. Значення R^2=0,912 і це дає підстави вважати рівняння лінії тренда прийнятним для прогнозування. 4. В комірку, наприклад H4, введіть формулу =ПРЕДСКАЗ(H2;$B$3:$G$3;$B$2:$G$2)з використанням майстра функцій, задайте аргументи у діалоговому вікні Аргументы функции натисніть кнопку ОК. Виконайте копіювання формули у комірки І4 та J4. 5. Порівняйте обччислені значення з тими, які зняті з лінії тренда і зробіть висновки. 6. Ввиділіть діапазон комірок H5:I5 і введіть з використанням майстра функцій формулу =ТЕНДЕНЦИЯ(H5:I5; В3:G3; В2:G2) (рис.7), натисніть клавіші Ctrl + Shift+Enter. 7. Порівняйте отримані значення з тими, які обчислені з використанням функції ПРЕДСКАЗ. Зробіть висновки. 8. Перевіримо, чи маємо підстави застосовувати функцію прогнозування експоненціального тренда РОСТ. Для цього на гістограмі побудуємо лінію тренда типу Експоненциальная з відображенням величини достовірності апроксимації R^2 (рис.8). Робимо висновок: цей тип лінії тренда не підходить для прогнозування у даному випадку статистичних даних. 9. Переконаємось у тому, що прогнозні значення, обчислені з використанням функції РОСТ значно відрізняються від обчислених за допомогою ФУНКЦІЙ тенденция, предсказ ТА ОТРИМАНИХ НА ОСНОВІ ЛІНІЇ РЕГРЕСІЇ логарифмічного типу. В комірки H6:I6 введіть з використанням майстра функцій формулу =РОСТ(H6:I6; В3:G3; В2:G2) і натисніть клавіші Ctrl + Shift+Enter (рис.9). 10. Проаналізуйте отримані прогнозні дані і зробіть висновки. Завдання 3. Складіть звіт і захистіть виконану роботу
Перелік питань до захисту практичної роботи
З повагою ІЦ “KURSOVIKS”! |
||||||||||||||||||||||||||||