3.4.4. Розв’язок завдань із застосуванням комп’ютера

Застосування EXCEL

Завдання 1. Динаміка обсягів продажу компакт-дисків (Y_t) характеризується даними (тис.шт.), представленими в табл. 3.4.3.

Таблиця 3.4.3

Місяць	Січ.	Лют.	Берез.	Квіт.	Трав.	Черв.	Лип.	Серп.	Верес.	Жовт.	Лист.	Груд.
Рік	2003
Yt	130,5	131,3	141	143,5	166,1	155,9	160,3	160,2	165,2	161,7	144,7	138,8
Рік	2004
Yt	142,1	141,2	163,4	170	171,8	177,5	182,7	187	195	190,4	175,7	166,5
Рік	2005
Yt	170,2	167,1	188,9	197,5	207,2	205,7	210,4	210,6	-	-	-

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

Розв’язок в EXCEL

1. Статистична функція ЛИНЕЙН визначає параметри лінійної регресії за методом найменших квадратів. Порядок обчислювань наступний:

1. Введіть вхідну інформацію або відкрийте існуючий файл, який містить дані аналізу.

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

3. Активізуйте Майстер функцій будь-яким способом. У вікні Категорія (Рис. 3.4.1) виберіть Статистические, у вікні Функція – ЛИНЕЙН. Кліпніть ОК.

Рис. 3.4.1. Діалогове вікно «Майстер-функцій»

4. Заповніть аргументи функції (рис.3.4.2): Відомі значення – діапазон, який містить дані залежної змінної ; Відомі значення – діапазон, який містить дані незалежної змінної ; Константа – логічне значення, яке вказує на наявність вільного члена в рівнянні. Якщо Константа=1, то вільний член розраховується, якщо Константа=0, то вільний член дорівнює 0; Статистика – логічне значення, яке вказує, чи виводити додаткову інформацію з регресійного аналізу (Статистика=1) чи ні (Статистика=0). Кліпніть ОК.

Рис. 3.4.2. Діалогове вікно введення аргументів функції ЛИНЕЙН

5. Щоб у виділеному діапазоні розкрити усю таблицю регресійної статистики, натисніть на клавішу F2, а потім – на комбінацію клавіш CTRL + SHIFT + ENTER. Результати розрахунку будуть виведені за вказаною нижче схемою:

Значення коефіцієнта	Значення коефіцієнта
Середньоквадратичне відхилення	Середньоквадратичне відхилення
Коефіцієнт детермінації R^2	Середньоквадратичне відхилення
F-статистика	Число ступенів волі
Регресійна сума квадратів	Залишкова сума квадратів

Результати обчислення функції ЛИНЕЙН показаний на рис.3.4.3.

Рис. 3.4.3. Результати обчислення функції ЛИНЕЙН

2. Для розрахунку параметрів експоненціальної кривої у MS EXCEL застосовується функція ЛГРФПРИБЛ. Порядок обчислення такий самий, як для функції ЛИНЕЙН.

Результати обчислення функції ЛГРФПРИБЛ показаний на рис.3.4.4.

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

За даними рис. 3.4.3 та 3.4.4 запишемо рівняння лінійного й експоненціального трендів

,

.

Завдання 2. За даними Завдання 1 побудувати графіки ряду динаміки обсягу продажів компакт-дисків (Y_t) і трендів. За графічним зображенням та значенням коефіцієнтів детермінації вибрати найкращий вид тренду.

Розв’язок в EXCEL

Побудова графіків здійснюється за допомогою Майстра діаграм. Порядок побудови наступний:

1. Введіть початкові дані або відкрийте існуючий файл, який містить дані аналізу.

2. Активізуйте Майстер діаграм будь-яким способом. Наприклад, на панелі інструментів Стандартная кліпніть на кнопці Мастер диаграмм.

3. У вікні Тип виберіть График (рис. 3.4.5); вид графіка виберіть у полі поруч зі списком типів. Кліпніть на кнопці Далее.

Рис. 3.4.5. Діалогове вікно Майстра діаграм: тип діаграми

4. Заповніть діапазон даних, як показано на рис. 3.4.5. Встановіть фішку розміщення даних у стовпцях (рядках). Кліпніть на кнопці Далее.

Рис. 3.4.6. Діалогове вікно Майстра діаграм: джерело даних

5. Заповніть параметри діаграми на різних закладках (рис. 3.4.7): назви діаграми та висей, значення висей, лінії мережі, параметри легенди, таблиця й підписи даних. Кліпніть на кнопці Далее.

Рис. 3.4.7. Діалогове вікно Майстра діаграм: параметри діаграми

6. Вкажіть місце розміщення діаграми на окремому або відкритому листі. Кліпніть на кнопці Далее. Готова діаграма, яка відображає динаміку рівнів ряду, показана на рис. 3.4.8.

Рис. 3.4.8. Динаміка обсягу продажів компакт-дисків, Y(t)

У ППП MS Excel лінія тренду може бути додана до діаграми із областями гістограми або в графік. Для цього:

1. Виділіть область побудови діаграми; у головному меню виберіть Диаграмма/Добавить линию тренда.

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

Рис. 3.4.9. Діалогове вікно типів ліній тренду

На діаграмі додатково можна відобразити рівняння регресії та значення середньоквадратичного відхилення, встановивши відповідні фішки на закладці Параметри (рис. 3.4.10). Кліпніть на кнопці ОК.

Рис. 3.4.10. Діалогове вікно параметрів лінії тренду

На рис. 3.4.11 – 3.4.15 показані різні види трендів, які описують початкові дані завдання.

Рис. 3.4.11. Лінійний тренд

Рис. 3.4.12. Логарифмічний тренд

Рис. 3.4.13. Поліноміальний тренд

Рис. 3.4.14. Степеневий тренд

Рис. 3.4.15. Експоненціальний тренд

3. Порівняємо значення R² за різними рівняннями трендів:

поліноміальний 2-го ступеня R² = 0,7372

лінійний R² = 0,7259

експоненціальний R² = 0,7176

степеневий R² = 0,6293

логарифмічний R² = 0,6053

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