Прогнозування з використанням вбудованих функцій
Мета роботи – прогнозування значень за табличними даними з використанням вбудованих функцій GROWTH, FORECAST і TREND.
Завдання для підготовки до виконання роботи
1 З використанням вбудованих функцій GROWTH, FORECAST і TREND зробити прогноз нових значень функції відповідно таблиці 4.1.
2 Зробити аналіз одержаних результатів.
Таблиця 4.1 – Варіанти до виконання лабораторної роботи
| Номер варіанта | Дані для побудови графіка функції | |||||||||
| Роки | ||||||||||
| Середня літня температура | 30,6 | 28,7 | 29,4 | 31,8 | 23,3 | 32,9 | 26,8 | |||
| Роки | ||||||||||
| Кількість газу (м3), добутих на одній скважині | 135,2 | 138,7 | 139,9 | 141,6 | 143,1 | 142,5 | 141,8 | |||
| Роки | ||||||||||
| Процент інф-ляції в країні | 9,7 | 10,3 | 10,8 | 10,7 | 11,6 | 11,4 | 11,4 | |||
| Роки | ||||||||||
| Врожай соняшнику | 14,5 | 16,2 | 16,5 | 17,2 | 17,8 | 17,7 | 17,5 | |||
| Точки дослід-ження | 0,5 | 0,75 | 1,0 | 1,25 | 1,5 | 1,75 | 2,0 | |||
| Температура | 32,8 | 30,2 | 21,7 | 27,8 | 27,5 | 27,2 | 27,9 | |||
| Кількість експериментів | ||||||||||
| Процент суміші в речовині | 36,3 | 38,5 | 39,7 | 39,1 | 39,0 | 38,7 | 40,0 | |||
| Роки | ||||||||||
| Кількість спа-лахів Сонця | 52,7 | 56,5 | 60,7 | 54,8 | 70,4 | 68,1 | 67,8 | |||
| Роки | ||||||||||
| Кількість ком-п’ютерів (тис. шт) | 1,84 | 2,12 | 2,6 | 3,31 | 4,62 | 5,1 | 5,73 | |||
| Роки | ||||||||||
| Процент азоту в атмосфері | 2,8 | 2,9 | 3,0 | 3,6 | 3,8 | 3,9 | 4,1 | |||
| Роки | ||||||||||
| Продаж труб | 9,8 | 10,1 | 10,3 | 11,9 | 11,9 | 11,8 | 12,1 | |||
| Роки | ||||||||||
| Продаж олії | 4,7 | 4,6 | 4,6 | 5,3 | 5,3 | 5,5 | 5,6 | |||
| Роки | ||||||||||
| Продаж оселедця | 1,97 | 2,12 | 1,28 | 1,71 | 1,6 | 1,11 | 1,18 | |||
Продовження табл. 4.1
| Роки | ||||||||
| Продаж конд. виробів | 50,1 | 51,4 | 54,9 | 57,4 | 57,7 | 58,9 | 64,3 | |
| Роки | ||||||||
| Продаж консервів | 2,46 | 2,38 | 2,79 | 2,63 | 2,86 | 3,46 | 4,32 | |
| Роки | ||||||||
| Продаж цукерок | 15,1 | 16,1 | 17,7 | 19,5 | 19,4 | 20,6 | 20,8 | |
| Роки | ||||||||
| Продаж сиру | 55,4 | 56,6 | 62,0 | 66,1 | 67,6 | 66,3 | 71,2 | |
| Точки дослідження | 0,5 | 0,75 | 1,0 | 1,25 | 1,5 | 1,75 | 2,0 | |
| Температура | 32,8 | 30,2 | 21,7 | 27,8 | 27,5 | 27,2 | 27,9 | |
| Роки | ||||||||
| Продаж тортів | 2,9 | 3,0 | 3,1 | 3,5 | 3,7 | 3,8 | 4,1 | |
| Роки | ||||||||
| Продаж труб | 9,8 | 10,1 | 10,3 | 11,9 | 11,9 | 11,8 | 12,1 | |
| Роки | ||||||||
| Продаж олії | 4,7 | 4,6 | 4,6 | 5,3 | 5,3 | 5,5 | 5,6 | |
| Роки | ||||||||
| Продаж оселедця | 1,97 | 2,12 | 1,28 | 1,71 | 1,6 | 1,11 | 1,18 | |
| Роки | ||||||||
| Продаж конд. виробів | 50,1 | 51,4 | 54,9 | 57,4 | 57,7 | 58,9 | 64,3 | |
| Роки | ||||||||
| Продаж консервів | 2,46 | 2,38 | 2,79 | 2,63 | 2,86 | 3,46 | 4,32 | |
| Роки | ||||||||
| Продаж цукерок | 15,1 | 16,1 | 17,7 | 19,5 | 19,4 | 20,6 | 20,8 | |
| Точки дослідження | 0,5 | 0,75 | 1,0 | 1,25 | 1,5 | 1,75 | 2,0 | |
| Температура | 32,8 | 30,2 | 21,7 | 27,8 | 27,5 | 27,2 | 27,9 | |
| Роки | ||||||||
| Процент азоту в атмосфері | 2,8 | 2,9 | 3,0 | 3,6 | 3,8 | 3,9 | 4,1 | |
| Роки | ||||||||
| Продаж труб | 9,8 | 10,1 | 10,3 | 11,9 | 11,9 | 11,8 | 12,1 | |
| Роки | ||||||||
| Продаж олії | 4,7 | 4,6 | 4,6 | 5,3 | 5,3 | 5,5 | 5,6 | |
| Роки | ||||||||
| Продаж тортів | 2,9 | 3,0 | 3,1 | 3,5 | 3,7 | 3,8 | 4,1 | |
| Роки | ||||||||
| Продаж консервів | 2,46 | 2,38 | 2,79 | 2,63 | 2,86 | 3,46 | 4,32 |
Загальні положення
Прогноз – це науково обґрунтований опис можливих станів об’єкту в майбутньому і часу досягнення цих станів, а процес розробки прогнозів носить назву прогнозування.
Прогнозування даних можна виконати з використанням вбудованих функцій.
Нехай функція У(Х) задана у вигляді таблиці (табл. 4.2).
Необхідно для заданих значень Х побудувати графік функції У(Х) і зробити прогноз для функції У(Х) в точках Х, які не входять в заданий діапазон їх визначення. Для цього необхідно використати функції GROWTH, FORECAST і TREND. Ці функції вибираються із МАСТЕРА ФУНКЦИЙ (категорія МАССИВ).
Функція GROWTH прогнозує експоненціальне зростання на основі одержаних даних (рис. 4.1). Вона має наступний синтаксис:
GROWTH (ВХІДНІ_У; ВХІДНІ_Х; НОВІ_ДАНІ_Х; ТИП_ФУНКЦІЇ),
де ВХІДНІ_У – це множина значень У, які відомі у співвідношенні 
; ВХІДНІ_Х– це множина значень Х, які відомі для співвідношення ; НОВІ_ДАНІ_Х – це нові значення Х, для яких функція GROWTH повертає відповідні значення у. Нові значення повинні розташовуватися у колонці або у рядку; ТИП_ФУНКЦІЇ може мати значення 0, якщо буде обчислюватися функція 
, в іншому випадку – .
Для прогнозування нового значення з використанням лінійної регресії (рис.4.2) призначається функція FORECAST. Синтаксис функції наступний:
FORECAST (ЗНАЧЕННЯ; ВХІДНІ_У; ВХІДНІ_Х),
де ЗНАЧЕННЯ – значення Х, для якого прогнозується нове значення з використанням лінійної регресії; ВХІДНІ_У – масив або діапазон даних У; ВХІДНІ_Х – масив або діапазон даних Х.
Для прогнозування нового значення з використанням лінійної регресії (рис. 4.3) призначається також функція TREND. Синтаксис функції наступний:
TREND (ВХІДНІ_У; ВХІДНІ_Х; НОВІ_ДАНІ_Х; Тип_ЛІНІІ),
де ВХІДНІ_У – масив даних У в точках Х, для яких відоме співвідношення у=mх+b; ВХІДНІ_Х – масив даних Х; НОВІ_ДАНІ_Х (необов’язково) – масив даних Х, які використовуються для повторного обчислення значень; Тип_ЛІНІІ (необов’язково) – якщо дорівнює 0, то лінії проходять через початок координат, в іншому випадку розраховуються зміщені лінії.
Рисунок 4.1 – Діалогове вікно функції GROWTH
Рисунок 4.2 – Діалогове вікно функції FORECAST
Рисунок 4.3 – Діалогове вікно функції TREND
Приклад
Дослідити характер зміни виробництва деякої продукції, що задана таблицею 4.2. Побудувати графік і зробити прогноз на 5 періодів для нових значень х, використовуючи вбудовану функцію GROWTH, FORECAST, TREND.
Таблиця 4.2 – Вихідні дані для прогнозування нових даних
| Рік | ||||||||||
| Продукція | 17,1 | 19,7 | 20,4 | 21,7 | 22,3 | 22,8 | 23,1 | 25,6 | 25,9 |
Порядок виконання роботи
1. На робочому аркуші 1 з назвою ВХІДНІ ДАНІ робочої книги, якій дати назву ПРОГНОЗУВАННЯ, ввести дані з таблиці 4.2.
1.1. В комірку А1 ввести слово «Рік», потім послідовно в клітини А2:А11 ввести 1997 і т.д. В комірку В1 занести слово «Продукція». В клітини В2:В11 послідовно значення 17,1 і т.д.
1.2. Вибрати діаграму ГРАФИК із МАСТЕРА ДИАГРАММ і побудувати графік експериментальних даних з таблиці 4.2 (рис. 4.4).
2. Дати назви робочим аркушам 2-4 відповідно: GROWTH, FORECAST, TREND.
2.1. Скопіювати дані з робочого аркуша ВХІДНІ ДАНІ на кожен робочий аркуш 2-4.
2.2. В комірці С1 на кожному з робочих аркушів розташувати текст «Х (нові значення)»; в комірку D1 ввести відповідно «У(Х) GROWTH», «У(Х) FORECAST», «У(Х) TREND».
2.3. На аркуші GROWTH додати наступне:
2.3.1. Ввести в діапазон С2:С17 наступні формули як масив для обчислення значення функції за межами заданого інтервалу:
{=GROWTH(B2:B1;A2:A11;С2:С17)},
2.3.2. Створити загальний графік функції У(Х) з урахуванням прогнозних значень Х (рис.4.5).
2.4. На кожному з аркушів FORECAST, TREND:
2.4.1. Ввести в діапазон С2:С17 наступні формули як масив для обчислення значення функції за межами заданого інтервалу:
{= FORECAST (C2:C17;$B$2:$B$11;$A$2:$A$11)},
{=TREND($B$2:$B$11;$A$2:$A$11;C2:C17)}.
2.3.2. Створити загальний графік функції У(Х) з урахуванням прогнозних (рис.4.6, 4.7).
Рисунок 4.4 – Вхідні дані для прикладу
Рисунок 4.5– Результат прогнозування з використанням
вбудованої функції GROWTH
Рисунок 4.6 – Результат прогнозування з використанням
вбудованої функції FORECAST
Рисунок 4.7 – Результат прогнозування з використанням
вбудованої функції TREND
Контрольні запитання
1. Які вбудовані функції використовуються для прогнозування даних?
2. Опишіть синтаксис функції GROWTH.
3. Опишіть синтаксис функції FORECAST.
4. Опишіть синтаксис функції TREND.
ЛАБОРАТОРНА РОБОТА № 5