Додаток 3_МУ_Excel_стат_аналіз_прогнозування

y aebt

y atb

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

Лінійна форма: y a bt , де: у – значення показника; t – номер тимчасового періоду; а – початковий рівень тренда в період часу t=0; b – середня зміна за одиницю, тобто константа тренда або швидкість зміни. Наприклад, це може бути, середньодобовий, середньомісячний, середньорічний приріст якогось показника.

Поліноміальна форма: y a bt bt2 ...

Степенева форма: . Степеневий тренд добре підходить для відображення процесів з різною мірою пропорційності змін у часі. Лінія степеневого тренду обов’язково повинна проходити через початок координат.

Експоненціальна форма: , де e – основа натурального логарифма. Дана форма тренду найкраща для показників, які різко збільшують своє значення з часом (наприклад збільшення населення в епоху „демографічного вибуху” в ХХ столітті).

Логарифмічна форма: y alnt b, де ln – функція натурального логарифма. Дана форма тренду використовується для відображення тенденції зростання з уповільненням. Чим більше значення t, тим більше ця залежність наближується до лінійної. Тому, при достатньо великому t логарифмічна крива не дуже відрізняється від прямої горизонтальної лінії. Така форма характерна для розвитку показників, які все важко покращити (спортивні рекорди, збільшення продуктивності процесу).

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

Приклади побудови трендів за допомогою MS Excel

Приклад 3. Продемонструємо можливість прогнозування на прикладі

визначення залежності висоти від часу вільного падіння тіла.
Порядок виконання розрахунку:
	Заповнимо даними Лист 3					таким чином:
	A	B	C	D	E		F	G	H	I	J	K
1	Час	1	2	3	4		5	6	7	8	9	10
2	Висота	5,1	19,4	45,2	78,4		122,8	176,3	238,6	312,9	396,9	499,6

За даними, що знаходяться в рядку висоти, побудуємо діаграму (графік) залежності висоти від часу вільного падіння тіла (Рис. 6).

11

					Висота
600
500
400
300									Висота
200
100
0
1	2	3	4	5	6	7	8	9	10

Рис.6. Графік залежності висоти від часу вільного падіння тіла

Потім на основі базової лінії побудуємо лінію тренда.

Апроксимуємо отриману криву за допомогою степеневої залежності. Для виконання апроксимації в MS Excel необхідно:

встановити курсор миші в межах діаграми;

вибрати в меню команду Диаграмма - Добавить линию тренда;

на екрані з’явиться вікно Линия тренда для вибору лінії тренда (Рис. 7). У цьому вікні на вкладці Тип задають тип лінії тренда, тобто функцію на основі якої будується тренд, і вказують на основі якого ряду будується тренд. При виборі поліноміального типу необхідно вказати ступінь поліному (ступінь змінюється від 2 до 6).

Рис.7. Вікно Лінії тренда

12

На вкладці Параметры діалогового вікна Линия тренда можна задати підпис (назву лінії тренда), що буде відображений в легенді діаграми, число періодів вперед чи назад, на які буде зроблено прогнозування. Для відображення рівняння та величини достовірності апроксимації R2 на діаграмі встановлюють прапорці напроти відповідних пунктів. Для того щоб задати точку перетину лінії тренда з віссю Y, вводять значення в полі Пересечение кривой с осью Y в точке. Цей параметр застосовують до лінійних, поліноміальних та експоненціальних ліній тренда, він не використовується в логарифмічних, степеневих та лініях з ковзним середнім.

Для настроювання лінії тренда для нашого прикладу у вікні Линия тренда необхідно:

на вкладці Тип вибрати степеневу апроксимацію;

на вкладці Параметры вибрати прапорець Показывать уравнение на диаграмме (Рис. 8);

крім того, для визначення адекватності апроксимації можна помістити на діаграму величину достовірності R2, активізувавши прапорець Поместить на диаграмму величину достоверности аппроксимации (R^2).

клацнути на кнопку OK.

Рис.8. Вікно настроювання параметрів лінії тренда

Результат апроксимації показано на Рис. 9.

Як бачимо, отримано таку апроксимуючу функцію: y=5,0118t1,9895 – степенева функція.

Якби ми не знали зі шкільного курсу фізики, що точна залежність y=gt2/2, то за отриманою за допомогою Excel залежністю можна було б передбачити, наприклад, що за час t=20 секунд тіло пролетить 1962,6 м.

13

y a bt

	600
	500					y = 5,0118x1,9895
	400						R2 = 0,9999
Висота
	300									Висота
										Ступеневий (Висота)
	200
	100
	0
	1	2	3	4	5	6	7	8	9	10
						Час
				Рис.9. Ступенева лінія тренду

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

Аналіз отриманих трендів та прогнозування

Будь-який тренд – це спрощення реальності, необхідна „відмова” від безлічі факторів, які реально впливають. Тому кінцевий результат формального моделювання завжди необхідно оцінювати з погляду здорового глузду на основі комплексу знань про умови розвитку процесу і допустимі граничні значення показника. На одному наборі даних можна отримати 9 рівнянь трендів. Необхідно вибрати якнайкращий тренд, за яким можна було б побудувати надійний прогноз. У Excel для визначення оцінки істинності (адекватності апроксимації) вибраного тренда розраховується величина достовірності R2. Чим ближче величина цього коефіцієнта до 1, тим краще цей тренд описує процеси, що викликають зміну даного показника.

Прогнозування за допомогою вбудованих функцій – ТЕНДЕНЦИЯ(), РОСТ()

Функція ТЕНДЕНЦИЯ() повертає значення відповідно до лінійного

тренда. Апроксимує прямою лінією у вигляді: (за методом найменших квадратів). Повертає значення у, відповідно до цієї прямої для заданого масиву t.

Синтаксис функції: ТЕНДЕНЦИЯ (известные_значения_y; известные_ значения_x; новые_значения_x; конст).

14

Приклад 4. Для отримання прогнозних значень за допомогою стандартної функції ТЕНДЕНЦИЯ() скористайтеся даними з прикладу 1 (стор. 8). Скопіюйте ці дані на Лист 5 (Табл. 5). Значення показника записані в масиві В2:В11, а значення періодів у масиві А2:А11.

Таблиця 5. Розрахунок прогнозних значень показника (метод ковзного середнього)

	А	В	С
1	Номер періоду	Показник	Ковзне середнє
2	1	10
3	2	11	#Н/Д
4	3	10	#Н/Д
5	4	12	10,33
6	5	13	11,00
7	6	13	11,67
8	7	13	12,67
9	8	14	13,00
10	9	16	13,33
11	10	17	14,33
12	11		15,67

Порядок виконання розрахунку:

Виділіть масив комірок, в які буде записано результат D2:D11, викличте функцію ТЕНДЕНЦИЯ() командою Вставка – Функция – категорія

Статистические – ТЕНДЕНЦИЯ.

У діалоговому вікні (Рис. 10) функції вкажіть адреси діапазонів початкових даних:

известные_значения_y – діапазон В2:В11;

известные_значения_x – діапазон А2:А11.

Введення результатів у діапазон комірок закінчується натисненням клавіш

CTRL+SHIFT+ENTER.

Рис.10. Аргументи функції Тенденція

15

Для отримання прогнозу на період часу t=11 і t=12 виділіть діапазон комірок, в яких знаходитиметься результат прогнозування D12:D13 і зверніться до функції ТЕНДЕНЦИЯ().

Удіалоговому вікні додайте в полі аргумент Новые_значения_х - діапазон А12:А13. Функція виглядатиметаким чином:ТЕНДЕНЦИЯ (В2:В11;А2:А11;А12:А13). Введення результатів закінчіть натисненням клавіш CTRL+SHIFT+ENTER.

Урезультаті ви отримаєте дані, наведені в таблиці 6.

Таблиця 6. Розрахунок прогнозних значень показника (функція Тенденція)

	А	В	С	D
1	Номер періоду	Показник	Ковзне середнє	Тенденція
2	1	10		9,60
3	2	11	#Н/Д	10,33
4	3	10	#Н/Д	11,07
5	4	12	10,33	11,80
6	5	13	11,00	12,53
7	6	13	11,67	13,27
8	7	13	12,67	14,00
9	8	14	13,00	14,73
10	9	16	13,33	15,47
11	10	17	14,33	16,20
12	11		15,67	16,93
13	12			17,67

Приклад 5.

Функція РОСТ() використовується, коли залежність є нелінійною - експоненціальною. Функція РОСТ() розраховує прогнозоване експоненціальне

зростання на підставі наявних даних, тобто базується на формулі: y aebt . Функція РОСТ повертає значення у для послідовності нових значень t, що задаються за допомогою існуючих t та у значень.

Синтаксис функції: РОСТ (известные_значения_y; известные_значения_х; новые_значения_х; конст).

Для отримання прогнозних значень за допомогою стандартної функції РОСТ() скористайтеся даними з попереднього прикладу. Значення показника записані в масиві В2:В11, а значення періодів у масиві А2:А11.

Порядок виконання розрахунку:

Виділіть масив комірок, в які буде записано результат Е2:Е11, викличте функцію РОСТ() командою Вставка – Функция – категорія Статистические – РОСТ.

Заповніть поля діалогового вікна (Рис. 11).

Введення результатів закінчіть натисненням клавіш CTRL+SHIFT+ENTER.

16

Рис.11. Аргументи функції Зростання

Для отримання прогнозу виділіть діапазон комірок, в яких буде знаходиться результат прогнозування Е12:Е13 і зверніться до функції РОСТ().

У діалоговому вікні додайте в полі аргумент Новые_значения_х діапазон А12:А13. Функція виглядатиме таким чином: РОСТ (В2:В11;А2:А11;А12:А13). Введення результатів закінчіть натисненням клавіш CTRL+SHIFT+ENTER.

Отримані розрахунки представлені в таблиці 7.

Таблиця 7. Розрахунок прогнозних значень показника (функція Зростання)

	А	В	С	D	Е
1	Номер періоду	Показник	Ковзне	Тенденція	Зростання
			середнє
2	1	10		9,60	9,86
3	2	11	#Н/Д	10,33	10,43
4	3	10	#Н/Д	11,07	11,04
5	4	12	10,33	11,80	11,68
6	5	13	11,00	12,53	12,36
7	6	13	11,67	13,27	13,08
8	7	13	12,67	14,00	13,84
9	8	14	13,00	14,73	14,64
10	9	16	13,33	15,47	15,49
11	10	17	14,33	16,20	16,40
12	11		15,67	16,93	17,35
13	12			17,67	18,36

Приклад 6.

Прогноз на майбутні періоди t=11, t=12 за допомогою засобів Excel можна отримати, застосувавши метод трендового вирівнювання.

Скористайтеся значеннями показника з прикладу 1 (стор. 7). Розмістіть ці дані в Excel (Табл. 8) на Листі 4 (можна їх скопіювати з Листа 1).

17

Таблиця 8. Значення економічного показника

	А	В
1	Номер періоду	Показник
2	1	10
3	2	11
4	3	10
5	4	12
6	5	13
7	6	13
8	7	13
9	8	14
10	9	16
11	10	17

Порядок виконання розрахунку:

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

Виділіть діапазон фактичних значень параметра – В2:В11, виконайте команду Вставка - Диаграмма - Тип - График. Задайте необхідні параметри в діалогових вікнах Майстра діаграм.

Результат побудови діаграми буде виглядати таким чином (Рис. 12):

						Показник
	18
	16
показника	14
	12
	10									Показник
Значення	8
	6
	4
	2
	0
	1	2	3	4	5	6	7	8	9	10
					Номер періоду

Рис.12. Графік залежності показника від номера періоду

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

18

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

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

Результат побудови лінійної лінії тренда буде виглядати таким чином (Рис. 13):

	20
	18					y = 0,7333x + 8,8667
							R2 = 0,9073
	16
показника	14
	12
	10											Показник
												Лінійний (Показник)
Значення
	8
	6
	4
	2
	0
	1	2	3	4	5	6	7	8	9	10	11	12
					Номер періоду
		Рис.13. Лінійна лінія тренда на два періоди вперед

Побудоване за фактичними значеннями показника рівняння лінійної залежності має вигляд: у = 0,7333x + 8,8667.

Величина коефіцієнта достовірності R2=0,9073, тобто достовірність адекватності прогнозних значень достатньо велика (коефіцієнт прагне до 1, а це означає, що лінійний тренд достатньо достовірно описує процеси, що викликають зміну даного показника).

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

Враховуючи те, що тенденція зміни динамічного ряду не завжди може бути описана лінійним рівнянням, в Excel є можливість побудувати нелінійні залежності: ступеневу, експоненціальну, логарифмічну, а також поліноміальну (2-го, 4-го, 6-го ступеня).

Виконайте апроксимацію показника за поліноміальною залежністю. Для цього аналогічно попередньому побудуйте графік залежності, проте в діалоговому вікні Линия тренда на вкладці Тип виберіть Поліноміальну залежність (ступінь полінома 2).

19

Результат апроксимації буде виглядати таким чином (Рис. 14):
	25
	20
показника			y = 0,0492x2 + 0,1917x + 9,95
					R2 = 0,9335
	15
												Показник
												Поліноміальна (Показник)
Значення
	10
	5
	0
	1	2	3	4	5	6	7	8	9	10	11	12
					Номер періоду

Рис.14. Поліноміальна лінія тренду 2-го ступеня на два періоди вперед

Побудоване за фактичними значеннями показника рівняння поліноміальної залежності має вигляд: у = 0,0492x2 + 0,1917х + 9,95. Як бачимо, величина коефіцієнта достовірності R2=0,9335 значно вище, тобто достовірність адекватності прогнозних значень більша. Це означає, що поліном другого ступеня досить достовірно описує процеси, що викликають зміну даного показника.

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

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

Розглянемо випадок лінійного тренда. Формула, за якою будується лінійний тренд: y=0,7333x+8,8667. Для першого періоду, наприклад, у комірку С2 записуємо =0,7333*A2+8,8667. Далі визначаємо значення показника для інших періодів, скопіювавши формулу на діапазон С3:С13. У комірках С12 і С13 - майбутні значення показника у періоди часу t =11 і t =12.

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

Запишіть в комірку D2 отриману формулу параболічного рівняння в такому вигляді: =0,0492*A2^2+0,1917*А2+9,95, а потім скопіюйте її в комірки діапазону D3:D13. У комірках D2 і D13 - розрахункові значення показника, а також прогноз на періоди часу t =11 і t =12.

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

20