Засоби прогнозування і перспективних оцінок ПЕТ MS Excel

Размещено на http://www.allbest.ru/

Размещено на http://www.allbest.ru/

МІНІСТЕРСТВО НАУКИ І ОСВІТИ УКРАЇНИ

Вінницький національний медичний університет

Кафедра медичної та біологічної фізики

ПРАКТИЧНЕ ЗАНЯТТЯ

з теми: «Засоби прогнозування і перспективних оцінок ПЕТ MS Excel»

Укладач А.Т.Теренчук

Вінниця -2011

Мета заняття

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

Конкретна.

1. Навчитись створювати прогноз за допомогою наявної регресійної моделі.

2. Навчитись використовувати функції передбачення ПЕТ MS Excel.

3. Навчитись будувати, аналізувати і застосовувати тренди.

прогноз функція регресивна модель excel

Завдання по самопідготовці

1. Підготувати протокол практичного заняття за встановленою на кафедрі формою.

2. Повторити матеріал математичної статистики по регресійному аналізу, часових рядах та трендах і занести до протоколу основні означення.

3. Ознайомитись з порядком виконання роботи і вивчити теоретичний матеріал в обсязі контрольних питань.

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

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

Засоби розрахунку прогнозів є важливим компонентом систем підтримки прийняття рішень. Досить ефективні і прості засоби розробки прогнозів надаються процесором електронних таблиць (ПЕТ) MS Excel.

В MS Excel пропонується три основних підходи до створення прогнозу:

1) з застосуванням ковзного середнього;

2) на основі моделі регресії;

3) на основі згладжування.

Метод ковзного середнього полягає у тому, що прогноз будь-якого періоду являє собою середній показник кількох попередніх елементів часового ряду. Цей метод дуже простий, але не забезпечує точності прогнозування. Вибір числа попередніх елементів часового ряду суттєво впливає на якість прогнозу: збільшення числа елементів веде до погіршення чутливості і відставання прогнозу, а зменшення - до надто високої чутливості до шумів і втрати ефекту узагальнення. Загального правила для вибору числа попередніх елементів ряду не існує, слід у кожному окремому випадку керуватися специфікою задачі і особливостями початкових даних. Звичайно число елементів, що використовуються в усередненні, доцільно брати не менше 3.

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

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

(1)

де у - результативний фактор (показник), t - час, F(t) - функція регресії. При прогнозуванні на основі моделі регресії час відіграє роль єдиного фактора, який узагальнює дію усіх факторів. Визначальним моментом прогнозування за регресійною моделлю є вибір адекватної моделі регресії. Для підбору найкращої моделі залежності результативного показника від часу зручно використовувати графічне подання моделі і даних часового ряду, що забезпечується наявною в MS Excel функцію побудови лінії тренду.

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

- ПРЕДСКАЗ - розраховує майбутнє значення на основі лінійної регресії;

- ТЕНДЕНЦИЯ - розраховує значення у відповідності з лінійним трендом;

- РОСТ - розраховує майбутнє значення на основі моделі експоненційної залежності;

- ЛИНЕЙН - розраховує за допомогою методу найменших квадратів пряму, яка найкраще апроксимує наявні дані, і повертає відповідний масив значень (коефіцієнти рівняння прямої та статистика);

- ЛГРФПРИБЛ - розраховує експоненційну криву, яка апроксимує дані, і повертає відповідний масив значень (коефіцієнти рівняння кривої та статистика).

Функції ПРЕДСКАЗ дає значення прогнозу в одній точці, а функції ТЕНДЕНЦИЯ та РОСТ є функціями масиву і розраховують прогноз відразу для ряду точок. Функції ЛИНЕЙН і ЛГРФПРИБЛ не розраховують прогноз безпосередньо, а повертають параметри (коефіцієнти) рівняння, за яким може бути розрахований прогноз на будь-який момент.

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

При прогнозуванні звичайно не стоїть питання про абсолютно точне майбутнє значення показника, достатньо отримати приблизну його оцінку, а в багатьох випадках, таких як прогнозування попиту на лікарські препарати і потреб у них, плануванні виробництва, прогнозуванні поширення захворювань тощо, важливо знати тенденцію даних і прогнозувати характер їх зміни у майбутньому. Це дозволяє досить ефективно застосовувати «ручні» методи, які базуються на наочному поданні часового ряду у вигляді графіка, за яким з врахуванням специфічних закономірностей і факторів предметної області визначається тенденція часового ряду і виконується продовження графіка наперед, у майбутнє, на потрібну глибину. Надалі за продовженим графіком наближено можуть бути знайдені прогнозні значення показника.. Такий підхід є зручним і гнучким, дозволяє повною мірою реалізувати суб'єктивний досвід, інтуїцію, неформалізовані знання про предметну область, але є досить трудомістким і повільним. Засоби графічного відображення даних передбачені в багатьох сучасних програмних засобах як спеціального, так і загального призначення.

Для наочного подання часових рядів графіками табличні процесори надають спеціальні засоби, такі як майстер діаграм в ПЕТ MS Excel.

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

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

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

ПЕТ MS Excel підтримує такі форми тренду:

- лінійна: застосовується для апроксимації даних за методом найменших квадратів у відповідності до рівняння

де а - початковий рівень тренду на момент чи період, прийнятий за початок відліку;

b - середня зміна за одиницю часу (константа тренду, або коефіцієнт нахилу лінії тренду).

- експоненційна: застосовується для апроксимації даних за методом найменших квадратів у відповідності до рівняння

де с - початковий рівень тренду;

k - константа тренду, яка виражає темп зміни показника;

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

- поліноміальна: застосовується для апроксимації даних за методом найменших квадратів у відповідності до рівняння

де b, с₁…c₆ - константи;

- степенева: застосовується для апроксимації даних за методом найменших квадратів у відповідності до рівняння

де с, b - константи. Майстер трендів MS Excel підтримує поліноміальні тренди до 6-го степеня.

- ковзне середнє (Лінійна фільтрація): являє собою послідовність середніх значень, розрахованих за частинами рядів даних

Лінія, побудована по точках ковзного середнього, більш явно показує закономірність розвитку даних, але рівняння тренду не формується і розрахунок прогнозних значень не передбачено.

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

Робота з трендами в MS Excel організована таким чином, що в якості проміжного продукту використовуються ХY-графіки (графіки у прямокутних, або декартових координатах) залежності показника від часу (точкова діаграма), які створюються майстром діаграм. Після створення цих графіків надається доступ до інструментів розрахунку і моделювання трендів. Таким чином, технологія побудови трендів в MS Excel складається з наступних кроків:

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

2) На XY-діаграмі відлиті (за допомога ЛК) точки № сертифіката, для Якого потрібно побудувати тренд, и відкликати команду Додати лінію тренда з пункту Діаграма головного меню Програми (цей пункт з'являється у меню Замість пункту Дані, коли обирається діаграма) або для виділених точок викликати контекстне меню и у ньому зверни опцію Додати лінію тренда. 3) У діалоговому вікні, Що відкривається при виконанні п.3), зверни один тип тренду (лінійній, логарифмічній ТОЩО). 4) У тому ж діалоговому вікні відкрити вкладку Параметри и Виконати Потрібні налаштування, а саме: - Задати відображення рівняння побудованого тренду на діаграмі (прапорець Показувати рівняння на діаграмі); - Задати відображення на діаграмі рівня достовірності апроксимації - коефіцієнта детермінації R2 (прапорець Помістити на діаграму величину вірогідності апроксимації);

- задати назву апроксимуючої кривої, яка буде відображена на діаграмі;

- задати точку перетину лінії тренду з віссю Y,тобто положення початкової точки тренду, або вільний член рівняння тренду (прапорець

Перетин кривої з віссю Y у точці и відповідне числове поле вводу).

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

2) Задати назву модельованого тренду, яка буде відображатися на діаграмі.

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

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

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

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

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

де t - часовий період,

F(t) - прогноз, зроблений в момент часу t,

a - константа згладжування,

e(t) - похибка (відмінність між прогнозом на момент часу t і дійсним значенням у момент часу t).

ПЕТ MS Excel безпосередньо підтримує один з методів прогнозування на основі згладжування, який називається експоненційним згладжуванням і знаходиться у надбудові Пакет аналізу.

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

Порядок виконання роботи

Задача 1. Розробити прогноз обсягів продажу лікарського препарату на основі даних по місяцях за попередній період, які наведені у таблиці (обсяг продажу - тис. одиниць):

	Місяць
	1	2	3	4	5	6	7	8	9	10	11	12
Рік	2010	140	156	123	109	103	94	102	168	180	172	160	120
	2011	90	101	82	80	71	66

1. Запустити ПЕТ MS Excel і підготувати робочий аркуш з даними за зразком рис. 1. Присвоїти робочому аркушу ім'я «Прогноз продажу».

Рис. 1. Зразок робочого аркуша для розрахунку прогнозу

Зауваження. Для використання інструментів прогнозування необхідно, щоб значення часової змінної були подані у вигляді послідовності номерів періодів (або часових від ліків), а не у їх натуральному вигляді (роки, місяці, дні тижня тощо). Числові значення часової змінної у вигляді номерів періодів міститься у стовпчику «Номер періоду» робочого аркуша рис.1..

1. Розрахувати прогноз обсягу продаж на наступний місяць за допомогою функцій MS Excel.

1.1. Розрахувати прогноз на 7-й місяць 2012 р. за допомогою функції ПРЕДСКАЗ. Для цього:

- обрати комірку Е20 і викликати майстра функцій;

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

- в якості аргументу х задати комірку D20, яка містить номер періоду, що відповідає наступному місяцю;

- в якості аргументу відомі значення y задати діапазон С2:С19, який містить усі вхідні дані, а в якості аргументу. Відомі значення x - діапазон номерів періодів D2:D19.

В результаті у комірці Е20 має бути формула =ПРЕДСКАЗ(D20;C2:C19;D2:D19).

- дати ЛК на командній кнопці ОК, в результаті у комірці Е20 з'явиться результат. Записати результат до протоколу.

1.2. У комірці F20 виконати розрахунок прогнозу на 7-й місяць за допомогою функції ПРЕДСКАЗ, задавши в якості відомих значень факторів частину вхідного часового ряду, наприклад, діапазони C10:C19 та D10:D19 (або інший на власний розсуд).

Занести отриманий результат до протоколу, порівняти з попереднім і пояснити відмінність.

2.3. Розрахувати прогноз обсягу продажу препарату до кінця року за допомогою функції ТЕНДЕЦИЯ, яка використовує лінійну модель тренду. Для цього:

- виділити діапазон G20:G25 і викликати майстра функцій;

- у категорії Статистично обрати функцію ТЕНДЕНЦИЯ і ознайомитись з довідкою по ній;

Зауваження. Функції ТЕНДЕНЦІЯ, РОСТ, ЛИНЕЙН, ЛГРФПРИБЛ є функціями масиву, вони повертають не одне значення в одній комірці, а масив - впорядковану множину значень, яка міститься в діапазоні комірок. Тому формула з функцією масиву вводиться в діапазон комірок, який має містити результат, повернений функцією. Для того, щоб відобразити результат у діапазоні комірок, потрібно вводити функцію не в одну комірку, а у відповідний діапазон, і для відображення результату виділити цей діапазон, натиснути клавішу F2, а потім - комбінацію клавіш Ctrl+Shift+Enter

- в якості аргументу Відомі значення y задати діапазон вхідних даних С2:С19 і встановити для нього абсолютну адресу, в якості аргументу Відомі значення х - діапазон номерів періодів D2:D19 і також встановити для нього абсолютну адресу, в якості аргументу Нові значення х - діапазон D20:D25, який містить номери решти місяців до кінця року (19...24), в якості аргументу Конст - значення ИСТИНА;

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

- дати ЛК на командній кнопці ОК, в результаті у комірці G20 з'явиться результат, який являє собою масив значень (по одному для кожного місяця). У комірці G20 відображається тільки перше значення масиву, яке відповідає 7-му місяцю. Для відображення усього масиву необхідно:

- виділити діапазон, який містить масив (в даному випадку G20:G25) і натиснути клавішу F2, після чого натиснути комбінацію клавіш Ctrl+Shift+Enter.

В результаті у комірках G20:G25 з'являються значення прогнозу на місяці з 7-го до кінця року.

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

2.4 Виконати розрахунок прогнозу до кінця року у діапазоні Н20:Н25 за допомогою функції ТЕНДЕНЦИЯ, використавши в якості відомих значень факторів тільки частину вхідного часового ряду, наприклад, діапазони C10:C19 та D10:D19 (або інший на власний розсуд). Значення аргументу Конст задати рівним ИСТИНА. Записати результат до протоколу і пояснити відмінність від попередніх результатів.

2.5 Розрахувати прогноз продажу препарату до кінця року за допомогою функції РОСТ, яка використовує еспоненційну модель тренду, в діапазоні І1:І25. Для цього:

- виділити діапазон І20:І25 і викликати майстра функцій;

- у категорії статистично обрати функцію РОСТ ознайомитись з довідкою по ній;

- в якості аргументу Відомі значення y задати діапазон вхідних даних С2:С19 і встановити для нього абсолютну адресу, в якості аргументу Відомі значеннях - діапазон номерів періодів D2:D19 і також встановити для нього абсолютну адресу, в якості аргументу нові значення х - діапазон І20:І25, який містить номери решти місяців до кінця року (19...24), в якості аргументу Конст - значення ИСТИНА;

Зауваження. Аргумент Конст визначає спосіб знаходження коефіцієнта b рівняння

- дати ЛК на командній кнопці ОК, в результаті у комірці І20 з'явиться результат (перший елемент масиву значень). Натиснути клавішу F2, а потім комбінацію клавіш Ctrl+Shift+Enter. В результаті у комірках І20:І25 відображається розрахований прогноз. Записати результат до протоколу і порівняти його з результатом прогнозування за допомогою функції ПРЕДСКАЗ.

2.6 Виконати у діапазоні комірок J20:J25 розрахунок прогнозу обсягів продажу за допомогою функції РОСТ, задавши в якості відомих значень факторів частину вхідних даних, наприклад, діапазони C10:C19 та D10:D19 (або інші).

Занести отриманий результат до протоколу, порівняти з попередніми і пояснити відмінність.

3. Знайти рівняння лінійної та експоненційної регресії для обсягу продаж за допомогою функцій ЛИНЕЙН і ЛГРФПРИБЛ і на їх основі розрахувати прогноз обсягу продаж до кінця року.

Перейти на робочий аркуш 2 і скопіювати на нього дані задачі з робочого аркуша „Прогноз продажу” п.2. (стовпчики A,B,C,D). Доповнити робочий аркуш за зразком рис. 2

Рис.2. Зразок робочого аркуша для розрахунку лінійної та експоненційної моделей і прогнозу на їх основі

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

Для знаходження рівняння лінійної регресії:

- виділити діапазон E4:F4 з двох комірок, в яких будуть знаходитись коефіцієнти рівняння лінійної регресії ;

- викликати майстра функцій і у категорії Статистично обрати функцію ЛИНЕЙН; ознайомитись з довідковою інформацією по функції ЛИНЕЙН;

- для аргументу Відомі значення y задати діапазон вхідних даних С2:С19, для аргументу Відомі значеннях - діапазон номерів періодів D2:D19, для аргументу Конст - значення ИСТИНА (при цьому значення вільного члена b розраховується звичайним чином за способом найменших квадратів); для аргументу Статистика - значення ЛОЖЬ або ніякого значення (при цьому не видається додаткова статистика);

- дати команду ОК, в результаті чого у комірці Е4 з'явиться результат;

- виділити діапазон Е4:F4 (якщо він не виділений), натиснути клавішу F2, а потім - Ctrl+Shift+Enter, в результаті чого у комірці Е4 відобразиться значення коефіцієнта b рівняння, а у комірці F4 - значення вільного члена а;

- занести до протоколу значення коефіцієнтів і записати рівняння регресії.

3.2 Розрахувати прогноз до кінця року на основі результатів функції ЛИНЕЙН. Для цього:

- обрати комірку F20 і за допомогою майстра функцій ввести до неї отриману у п. 3.2 формулу лінійної регресії, вказавши в якості аргументу х комірку D20 (7-й місяць) і встановивши для комірок E4 і F4, в яких містяться значення коефіцієнтів, абсолютні адреси ;

- обчислити значення функції у комірці F20;

- скопіювати формулу з комірки F20 на діапазон F21:F25 (місяці з 8-го по 12-й);

- занести результат до протоколу і порівняти його з відповідними результатами п. 2.3.

3.3 Знайти рівняння експоненційної регресії і за допомогою нього розрахувати прогноз обсягу продаж до кінця року.

Для знаходження рівняння експоненційної регресії:

- виділити діапазон , в комірках якого будуть знаходитись параметри рівняння експоненційної регресії ;

- за допомогою майстра функцій в діапазоні G4:Н4 створити функцію ЛГРФПРИБЛ (категорія Статистично) і задати для неї такі аргументи:

Відомі значення y - діапазон вхідних даних С2:С19;

Відомі значення х - діапазон номерів періодів D2:D19;

Конст - значення ИСТИНА (при цьому значення коефіцієнта b розраховується звичайним чином за способом найменших квадратів);

Статистика - значення ЛОЖЬ або ніякого значення (при цьому не видається додаткова статистика);

- обчислити результат функції ЛГРФПРИБЛ;

- виділити діапазон G4:Н4 (якщо він не виділений), натиснути клавішу F2, а потім - Ctrl+Shift+Enter, в результаті чого у комірці G4 відобразиться значення параметра m рівняння, а у комірці H4 - значення коефіцієнта b;

- занести до протоколу значення параметрів і записати рівняння регресії.

3.4 Розрахувати прогноз до кінця року на основі результатів функції ЛГРФПРИБЛ. Для цього:

- обрати комірку H20 і за допомогою майстра функцій ввести до неї отриману у п. 3.4 формулу експоненційної регресії, вказавши в якості аргументу х комірку D20 (7-й місяць) і встановивши для комірок G4 і H4, в яких містяться значення параметрів, абсолютні адреси (слід застосувати функцію СТЕПЕНЬ, яка належить до категорії математичних);

- обчислити значення функції у комірці Н20;

- скопіювати формулу з комірки Н20 на діапазон Н21:Н25 (місяці з 8-го по 12-й);

- занести результат до протоколу і порівняти його з відповідними результатами п. 2.5.

4. Побудувати часовий тренд і на основі нього розрахувати прогноз обсягів продажу препарату до кінця року.

4.1 Відкрити новий робочий аркуш і присвоїти йому ім'я „Тренд”.

4.2 Скопіювати на робочий аркуш „Тренд” стовпчики A-D робочого аркуша „Прогноз продажу”. Поміняти місцями вміст стовпчиків С і D, щоб у стовпчику С знаходились номери періодів, а у стовпчику D - значення обсягів продажу.

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

- викликати програму-майстра діаграм;

- у першому вікні майстра обрати тип діаграми точкова і вид графіка - Проста точкова діаграма;

- перейти до другого кроку майстра (ЛК на кнопці далі);

- у вікні другого кроку задати у полі Діапазон С2:D19, який містить номери періодів і відповідні значення показника за попередні місяці (ввести діапазон з клавіатури або виділити його протягуванням на робочому аркуші), і перейти до третього кроку майстра;

- у вікні третього кроку майстра задати назву діаграми та її осей, а також встановити інші параметри діаграми (на власний розсуд), після чого перейти до четвертого кроку майстра;

- у вікні четвертого кроку майстра задати створення діаграми на тому ж робочому аркуші. В результаті на робочому аркуші буде побудовано графік залежності обсягу продаж від часу.

Зауваження. 1. Переміщення вмісту стовпчиків С і D необхідне для того, щоб надалі отримати правильне відображення часового ряду і правильний тренд. Точкова діаграма відображає пари значень як точки на площині у прямокутній системі координат, причому значення першого діапазону (першого зліва, якщо ряди даних містяться у стовпчиках, або зверху, коли ряди даних містяться у рядках) відображаються по осі абсцис, а другого - по осі ординат. Тому необхідно забезпечити саме таке розміщення даних в електронній таблиці.

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

4.6 Побудувати на XY-діаграмі лінію поліноміального тренду 6-степеня, для чого:

- дати ПК на лінії діаграми, внаслідок чого з'явиться контекстне меню, або відкрити команду Диаграмма головного меню програми;

- клацнути у меню, що з'являється (контекстному або ви падаючому вертикальному), опцію Добавить лінію тренда, в результаті чого з'являється вікно програми-майстра трендів (рис.3);

- На сторінці Тип діалогового вікна Зверни тип Лінії Поліноміальна та Встановити степінь полінома 6, після Чого перейти до сторінки Параметри (дати ЛК на закладці Параметри) i Встановити на ній прапорці Показувати рівняння на діаграмі і Помістіть на діаграму величину вірогідності апроксимації;

- На сторінці Тип діалогового вікна зверни тип Лінії Поліноміальна и Встановити степінь полінома 6, після Чого перейти до сторінки Параметри (дати ЛК на закладці Параметри) i Встановити на ній прапорці Показувати рівняння на діаграмі и Помістіть на діаграму величину вірогідності апроксимації; - У вікні майстра на закладці Параметри у полі Назва апроксимуючої кривої зверни інше и задати назв апроксимуючої кривої "Поліном-6", у полі Прогноз задати побудову Лінії тренду вперед на 6 періодів (до кінця року);

- дати ЛК на командній кнопці ОК вікна Линия тренда, після чого на робочому аркуші з'явиться графік тренду. А також рівняння тренду і значення коефіцієнта детермінації,який характеризує достовірність апроксимації;

- занести до протоколу графік лінії тренду, рівняння тренду і коефіцієнт детермінації R².

Рис.3. Вікно майстра трендів

4.7 Аналогічно п.4.4 побудувати лінію поліноміального тренду 5-степеня та занести до протоколу його графік, рівняння і коефіцієнт детермінації R².

4.8 Порівняти отримані у п.4.4 і п.4.5 результати і зробити висновок.

Зауваження. Порівняння поліноміальних трендів 5-го і 6-го степенів показує, що поліном вищого степеня дає кращу апроксимацію наявних даних за попередні періоди. Але екстраполяція даних на їх основі дає протилежні результати: поліном 5-го степеня показує необмежене зростання, а поліном 6-го степеня - необмежене спадання показника.

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

4.9 Побудувати лінію експоненційного тренду і розрахувати прогноз об'ємів продажу препарату до кінця року за допомогою рівняння тренду. Для цього:

- Аналогічно побудові поліноміального тренду (п.п. 4.3-4.5) побудуваті лінію експоненційного тренду и занести до протоколу йо графік та рівняння (рівняння має буті таке:);- На робочому аркуші у комірку Е19 ввести слово "Прогноз";- У комірку Е20 (7-й місяць) ввести Отримання формулу тренду за правилами MS Excel (формула буде такою: "= -155,46 * EXP (-0,0342 * D20");- Скопіювати формулу з комірки Е20 на діапазон Е21: Е25;- Занести до протоколу результати та порівняти їх з попередніми. Зауваження.

1. Вибір найкращої Лінії тренду Зручний виконувати шляхом їх безпосередню візуального порівняння, для чого потрібно Швидко переході ті від однієї Лінії до іншої. Такий Перехід Може здійснюватись у майстри трендів таким чином:- Дати ПК На лінії тренду, щоб відкликати контекстне меню, або зверни лінію тренду и викликати команду Виділена лінія тренда з пункту Формат головного меню;- У контекстного меню зверни команду Формат лінії тренда, Яка відкликає діалогове Вікно Формат лінії тренда;- У діалоговому вікні Формат лінії тренда Встановити потрібній тип тренду (закладка Тип) та йо параметри (закладка Параметри).В результаті Замість обраної буде побудовано нову лінію тренду.

2. Можлива побудова кількох ліній тренду на одній діаграмі. Для цього слід:- На діаграмі Вже побудованої Лінії тренду дати ПК На лінії Графіка Даних, внаслідок Чого з'являється контекстне меню, або с користати відповіднім командами пункту Діаграма головного меню;- У контекстного меню зверни опцію Додати лінію тренда, Що відкликає майстра трендів;- Виконати звичайна процедуру побудова Лінії тренду. В результаті на діаграмі відображається Ще одна лінія тренду.

3. Побудова на одній діаграмі кількох ліній тренду (Більше 3) виробляти до захаращення діаграмі, тому будувати разом Більше трьох ліній тренд не слід.

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

4.1.За допомога програми-майстра діаграм полудуваті графік Зміни об'єму продажів у часі (XY-діаграму) (п.4.3).

4.2. Викликати програму-майстра трендів (п.4.4) i у йо діалоговому вікні зверни тип тренду Лінійна фільтрація, число точок, Що враховуються при згладжуванні, Встановити рівнім

4.3. Змінити лінію тренду, збільшивши число точок до 6. Для цього:- Дати ПК на наявній Лінії тренду и у контекстного меню, Що з'являється при цьому, зверни опцію Формат лінії тренда;- У діалоговому вікні Формат лінії тренда Збільшити число точок, Що враховуються при згладжуванні, до 6, и клацнути кнопку ОК діалогового вікна;- Проглянути Отримання лінію тренда и занести її до протоколу.

4.4. Змінити лінію тренду, збільшивши число точок до 10, и занести графік до протоколу.

4.5. Порівняти Отримані Лінії тренду и Зробити Висновок про Вплив числа точок, Що враховуються при згладжуванні, на лінію тренда и прогноз за нею.

5. Завдання для самостійного розв'язання. На Основі даних про витрати виробництва фармацевтичного підприємства Скласти прогноз на наступні 5 РОКІВ и побудувати відповідній часовий тренд. Отримати рівняння тренду и Виконати за ні м розрахунок прогнозу.

	1997	1998	1999	2000	2001	2002	2003	2004
Витрати виробництва млн. грн. уі	8,9	4,6	4,1	3,8	3,7	3,0	2,6	2,0

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

1. Навести приклади застосування прогнозування у медицині та фармації.

2. Принципи розрахунку прогнозу, реалізовані в MS Excel.

3. Як розраховується прогноз на основі регресійної моделі?

4. Назвати і охарактеризувати функції для розрахунку прогнозу, наявні в MS Excel.

5. Що таке майстер функцій?

6. Які функції називаються функціями масивів?

7. Що таке часовий ряд?

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

9. Які форми тренду підтримує MS Excel?

10. Записати рівняння лінійного тренду.

11. Записати рівняння експоненційного тренду.

12. Що таке згладжування, як і для чого воно використовується?

13. Що таке майстер трендів?

14. Які можливості надає майстер трендів MS Excel?

15. Описати процедуру побудови лінії тренду та розрахунку прогнозу?

16. Що таке коефіцієнт детермінації, що він характеризує?

17. Як отримати формулу, що описує побудовану лінію тренда?

18. Описати функцію ПРЕДСКАЗ.

19. Функція ТЕНДЕНЦИЯ, її можливості та створення.

20. Функція РОСТ, її можливості та формат виклику.

21. Функції ЛИНЕЙН та ЛГРФПРИБЛ.

22. Як відобразити на робочому аркуші масив значень, повернутий функцією масиву?

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

Размещено на Allbest.ru