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

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

		А	В	С	D
	1	Номер періоду	Показник	Лінійний	Параболічна
				тренд	залежність
	2	1	10	9,60	10,19
	3	2	11	10,33	10,53
	4	3	10	11,07	10,97
	5	4	12	11,80	11,50
	6	5	13	12,53	12,14
	7	6	13	13,27	12,87
	8	7	13	14,00	13,70
	9	8	14	14,73	14,63
	10	9	16	15,47	15,66
	11	10	17	16,20	16,79
	12	11		16,93	18,01
	13	12		17,67	19,34

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

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

Таблиця 10. Розрахунок контрольних сум

		А	В	С	D
	1	Номер періоду	Показник	Лінійний	Параболічна
				тренд	залежність
	2	1	10	9,60	10,19
	3	2	11	10,33	10,53
	4	3	10	11,07	10,97
	5	4	12	11,80	11,50
	6	5	13	12,53	12,14
	7	6	13	13,27	12,87
	8	7	13	14,00	13,70
	9	8	14	14,73	14,63
	10	9	16	15,47	15,66
	11	10	17	16,20	16,79
		Контрольна сума	129	129,00	128,99

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

21

Варіанти завдань для індивідуальної роботи № 1*

за темою „MS Excel. Статистична обробка даних, аналіз та прогнозування”

Порядок виконання індивідуального завдання №1

Згідно з отриманим у викладача варіантом вибрати економічний показник для аналізу та прогнозування. Початкова інформація повинна відображати динаміку показника не менше, ніж за 8-10 періодів та відповідати таким характеристикам:

базова лінія повинна містити результати спостережень, починаючи з більш ранніх та закінчуючи останніми спостереженнями;

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

спостереження повинні фіксуватися в один і той момент тимчасового періоду;

пропуск даних не допустимий.

У процесі виконання індивідуальної роботи студент повинен:

1.Виконати прогноз на майбутній період за допомогою відомих вам засобів в Excel (ковзного середнього, функцій ТЕНДЕНЦИЯ() і РОСТ(), трендового вирівнювання).

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

3.Порівняти та проаналізувати за допомогою засобів Excel отримані результати. Усі розрахунки представити у таблицях та діаграмах.

4.Роботу оформити у вигляді інформаційного файлу та звіту з детальним описом дій, що проводились при розв’язанні даної задачі.

______________________________________________

	Варіант №1				Варіант №2				Варіант №3
		Валовий				Експорт товарів				Імпорт товарів
Роки, t		внутрішній			Роки, t	та послуг,			Роки, t	та послуг,
		продукт, млнгрн				млн грн				млн грн
2000		170070			2000	106200			2000	–97643
2001		204190			2001	113245			2001	–109924
2002		225810			2002	124392			2002	–114501
2003		267344			2003	154394			2003	–147525
2004		345113			2004	219607			2004	–193120
2005		441452			2005	227252			2005	–223555
2006		544153			2006	253707			2006	–269200
2007		712945			2007	323205			2007	–362979

	Варіант №4					Варіант №5				Варіант №6
		Валовий					Чистий				Валовий
Роки, t		прибуток,				Роки, t	прибуток,			Роки, t	національний
		млн грн					млн грн				дохід, млн грн
2000		69617				2000	39394			2000	164942
2001		90486				2001	56183			2001	200610
2002		95054				2002	58894			2002	222585
2003		114909				2003	76024			2003	264247
2004		152500				2004	105924			2004	341686

* У індивідуальній роботі використовуються реальні дані

22

2005		168775		2005	118230			2005	436411
2006		202036		2006	143771			2006	535459
2007		...		2007	...			2007	...
	Варіант №7			Варіант №8				Варіант №9
		Чистий			Валовий				Обсяг
									реалізованої
		національний			регіональний
Роки, t				Роки, t				Роки, t	продукції
		дохід,			продукт,				промисловості,
		млн грн			млн грн
									млн грн
2000		134719		2000	137993			2000	182718,3
2001		166307		2001	183296			2001	210842,7
2002		186425		2002	204342			2002	229634,4
2003		225362		2003	244497			2003	289117,3
2004		295110		2004	345113			2004	400757,1
2005		385866		2005	441452			2005	468562,6
2006		477194		2006	544153			2006	551729,0
2007		...		2007	...			2007	717076,7
Варіант №10				Варіант №11				Варіант №12
		Виробництво			Виробництво				Виробництво
Роки, t		електроенергії,		Роки, t	електроенергії			Роки, t	електроенергії
		млрд кВт-год			ГЕС,				ТЕС,
					млрд кВт-год				млрд кВт-год
2000		171,4		2000	11,5			2000	82,6
2001		173,0		2001	12,2			2001	84,6
2002		173,7		2002	9,8			2002	85,9
2003		180,4		2003	9,4			2003	89,5
2004		182,2		2004	11,9			2004	83,2
2005		186,1		2005	12,5			2005	84,7
2006		193,4		2006	13,0			2006	90,1
2007		196,3		2007	10,3			2007	93,4
Варіант №13				Варіант №14				Варіант №15
		Виробництво			Виробництво				Виробництво
Роки, t		електроенергії		Роки, t	вугілля			Роки, t	газу
		АЕС,			готового,				натурального,
		млрд кВт-год			млн т				млрд м3
2000		77,3		2000	62,4			2000	17,9
2001		76,2		2001	61,7			2001	18,4
2002		78,0		2002	59,5			2002	18,7
2003		81,4		2003	59,8			2003	19,3
2004		87,0		2004	59,4			2004	20,5
2005		88,8		2005	60,4			2005	20,8
2006		90,2		2006	61,7			2006	21,1
2007		92,5		2007	58,9			2007	21,1
Варіант №16				Варіант №17				Варіант №18
		Виробництво			Виробництво				Виробництва
Роки, t		сирів жирних,		Роки, t	борошна,			Роки, t	цукру-піску,
		тис. т			тис. т				тис. т
2000		67,5		2000	3076			2000	1780

23

2001	105			2001	2984			2001		1947
2002	129			2002	2986			2002		1621
2003	173			2003	2827			2003		2486
2004	224			2004	2949			2004		2147
2005	274			2005	2944			2005		2139
2006	217			2006	2696			2006		2592
2007	246			2007	2908			2007		1867
Варіант №19				Варіант №20					Варіант №21
	Продукція				Продукція						Продукція
Роки, t	сільського			Роки, t					Роки, t
					рослинництва,						тваринництва,
	господарства,
					млн грн						млн грн
	млн грн
2000	77889			2000	43573			2000		34316
2001	85796			2001	49075			2001		36721
2002	86784			2002	48113			2002		38671
2003	77271			2003	41105			2003		36166
2004	92531			2004	55638			2004		36893
2005	92586			2005	53976			2005		38610
2006	94895			2006	54909			2006		39986
2007	88769			2007	49718			2007		39051
Варіант №22				Варіант №23					Варіант №24
	Поголів’я				Поголів’я						Поголів’я
Роки, t	великої рогатої			Роки, t					Роки, t
					свиней,						птиці,
	худоби,
					тис. голів						млн голів
	тис. голів
2000	9424			2000	7652				2000		123,7
2001	9421			2001	8370				2001		136,8
2002	9108			2002	9204				2002		147,4
2003	7712			2003	7322				2003		142,4
2004	6903			2004	6466				2004		152,8
2005	6514			2005	7053				2005		162,0
2006	6175			2006	8055				2006		166,5
2007	5491			2007	7020				2007		169,3
Варіант №25				Варіант №26					Варіант №27
	Виробництво				Виробництво						Виробництво
Роки, t	м’яса (у забійній			Роки, t					Роки, t
					молока, млн т						яєць, млн шт
	вазі), тис. т
2000	1663			2000	12,7			2000		8809
2001	1517			2001	13,4			2001		9668
2002	1648			2002	14,1			2002		11309
2003	1725			2003	13,7			2003		11477
2004	1600			2004	13,7			2004		11955
2005	1597			2005	13,7			2005		13046
2006	1723			2006	13,3			2006		14235
2007	1912			2007	12,3			2007		14063

24

Варіант №28				Варіант №29				Варіант №30
					Введення в				Введення в
									експлуатацію у
	Введення в				експлуатацію у
									сільській
	експлуатацію				міських
Роки, t				Роки, t				Роки, t	місцевості
	загальної площі				поселеннях
	житла, тис. м2				загальної площі				загальної
					2				площі житла,
					житла, тис. м				тис. м2
2000	5558			2000	4329			2000	1229
2001	5939			2001	4773			2001	1166
2002	6073			2002	4858			2002	1215
2003	6433			2003	5074			2003	1359
2004	7566			2004	5739			2004	1827
2005	7816			2005	6088			2005	1728
2006	8628			2006	6709			2006	1919
2007	10244			2007	7737			2007	2507

Варіант №31				Варіант №32				Варіант №33
	Перевезення				Перевезення				Перевезення
	вантажів				вантажів				вантажів
Роки, t	залізничним			Роки, t	автомобільним			Роки, t	морським
	транспортом,				транспортом,				транспортом,
	млн т				млн т				млн т
2000	357			2000	939			2000	6,3
2001	370			2001	977			2001	8,0
2002	393			2002	947			2002	8,8
2003	445			2003	973			2003	9,0
2004	462			2004	1027			2004	9,0
2005	450			2005	1121			2005	8,0
2006	479			2006	1167			2006	9,0
2007	514			2007	1255			2007	9,0

	Варіант №34					Варіант №35					Варіант №36
		Перевезення					Вантажооборот					Вантажооборот
Роки, t		вантажів річковим			Роки, t		залізничного			Роки, t		автомобільного
		транспортом,					транспорту,					транспорту,
		млн т					млн ткм					млн ткм
2000		8,3			2000		172,8			2000		19,3
2001		7			2001		177,5			2001		18,5
2002		7,6			2002		193,1			2002		20,6
2003		10			2003		225,3			2003		24,4
2004		12			2004		234			2004		28,8
2005		13			2005		224			2005		35,3
2006		14			2006		240,8			2006		40,6
2007		15			2007		262,5			2007		40,6

Варіант №37				Варіант №38				Варіант №39
	Вантажооборот				Вантажооборот				Вантажооборот
	вантажів
Роки, t				Роки, t	річкового			Роки, t	трубопровідного
	морського
	транспортом,				транспорту,				транспорту,
					млн ткм				млн ткм
	млн т
2000	8,6			2000	5,9			2000	187,5

25

2001	10,1			2001	3,7			2001	184,2
2002	8,8			2002	4,2			2002	184,2
2003	9,9			2003	4,7			2003	192,7
2004	9,3			2004	5,6			2004	202,1
2005	9,6			2005	6,3			2005	198,1
2006	12,1			2006	6,3			2006	194,5
2007	12,9			2007	5,7			2007	182,1
Варіант №40				Варіант №41				Варіант №42
	Оптовий				Оборот				Оборот
Роки, t	товарооборот			Роки, t	роздрібної			Роки, t	ресторанного
	підприємств,				торгівлі,				господарства,
	млрд грн				млрд грн				млрд грн
2000	167,3			2000	47,5			2000	2,1
2001	273,5			2001	59,0			2001	2,5
2002	290,1			2002	68,8			2002	2,8
2003	394,1			2003	87,6			2003	3,0
2004	547,1			2004	116,3			2004	3,5
2005	511,2			2005	174,4			2005	7,5
2006	667,2			2006	237,8			2006	9,1
2007	863,9			2007	325,1			2007	11,7
Варіант №43				Варіант №44				Варіант №45
	Роздрібний				Доходи				Весь житловий
	товарооборот
Роки, t				Роки, t	населення,			Роки, t	фонд, млн м2
	підприємств,				млн грн				загальної площі
	млрд грн
2000	28,8			2000	128736			2000	1015,0
2001	34,4			2001	157996			2001	1031,7
2002	39,7			2002	185073			2002	1035,7
2003	50,0			2003	215672			2003	1040,0
2004	67,6			2004	274241			2004	1046,4
2005	94,3			2005	381404			2005	1049,2
2006	130,0			2006	472061			2006	1057,6
2007	178,2			2007	614984			2007	...
Варіант №46				Варіант №47				Варіант №48
									Витрати на
	Міський				Сільський				охорону
Роки, t	житловий фонд,			Роки, t	житловий фонд,			Роки, t	навколишнього
	млн м2 загальної				млн м2 загальної				природного
	площі				площі				середовища,
									млн грн
2000	643,2			2000	371,8			2000	3224,3
2001	658,4			2001	373,3			2001	3675,8
2002	661,7			2002	374,0			2002	3827,3
2003	664,5			2003	375,5			2003	4508,9
2004	669,9			2004	376,5			2004	5987,7
2005	671,3			2005	377,9			2005	7089,2
2006	677,6			2006	380,0			2006	7366,6
2007	...			2007	...			2007	9691,0

26

Варіант №49				Варіант №50				Варіант №51
	Кількість				Кількість				Кількість
Роки, t	наявного			Роки, t	міського			Роки, t	сільського
	населення, млн				населення, млн				населення, млн
2001	48,9			2001	32,9			2001	16,0
2002	48,5			2002	32,6			2002	15,9
2003	48,0			2003	32,3			2003	15,7
2004	47,6			2004	32,1			2004	15,5
2005	47,3			2005	32,0			2005	15,3
2006	46,9			2006	31,9			2006	15,0
2007	46,6			2007	31,8			2007	14,8
2008	46,4			2008	31,7			2008	14,7

Варіант №52				Варіант №53				Варіант №54
	Кількість				Кількість				Кількість
Роки, t	постійного			Роки, t				Роки, t
					чоловіків, млн				жінок, млн
	населення, млн
2001	48,7			2001	22,5			2001	26,2
2002	48,2			2002	22,3			2002	25,9
2003	47,8			2003	22,1			2003	25,7
2004	47,4			2004	21,9			2004	25,5
2005	47,1			2005	21,8			2005	25,3
2006	46,7			2006	21,6			2006	25,1
2007	46,5			2007	21,5			2007	25,0
2008	46,2			2008	21,3			2008	24,9

Варіант №55				Варіант №56				Варіант №57
					Кількість				Кількість
Роки, t	Кількість			Роки, t	народжених у			Роки, t	народжених у
	народжених, тис.				міських				сільській
					поселеннях, тис.				місцевості, тис.
2000	385,1			2000	238,0			2000	147,1
2001	376,5			2001	237,3			2001	139,2
2002	390,7			2002	248,9			2002	141,8
2003	408,6			2003	266,4			2003	142,2
2004	427,3			2004	284,4			2004	142,9
2005	426,1			2005	284,3			2005	141,8
2006	460,4			2006	306,7			2006	153,7
2007	472,7			2007	314,1			2007	158,6

Варіант №58				Варіант №59				Варіант №60
	Кількість				Кількість				Кількість
Роки, t				Роки, t	зареєстрованих			Роки, t	зареєстрованих
	пенсіонерів, тис.
					шлюбів, тис.				розлучень, тис.
2001	14447			2000	274,5			2000	197,3
2002	14423			2001	309,6			2001	181,3
2003	14376			2002	317,2			2002	183,5
2004	14348			2003	371,0			2003	177,2
2005	14065			2004	278,2			2004	173,2
2006	14050			2005	332,1			2005	183,5
2007	13937			2006	355,0			2006	179,1
2008	13819			2007	416,4			2007	178,4

27

2. Кореляційно-регресійний аналіз у MS Excel Теоретичні поняття

Важливим розділом статистичного аналізу є кореляційно-регресійний аналіз, який виявляє зв’язки між вибірками.

Кореляційний аналіз досліджує характер та міру взаємозалежності факторів, які впливають на досліджувану змінну.

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

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

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

Задача. Лінійна кореляційна залежність. У результаті спостережень за товарообігом магазинів У (млн грн) та середньообліковою кількістю робітників Х (чол.) отримана така статистична залежність:

Х	У	9		15	21	27	33	39	m	x
	10		1	3						4
	17		1	10	11	1				23
	24			2	23	15	1			41
	31				3	17	6			26
	38					1	4	1		6
	my		2	15	37	34	11	1	100

Необхідно:

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

b)побудувати моделі регресійної залежності У на Х та Х на У. Оцінити щільність кореляційного зв’язку;

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

Розв’язання задачі.

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

У середовищі MS Excel у новій книзі на Листі 1 створимо таблицю з вхідними даними.

28

Розрахуємо mx та my як суми по рядках та стовпцях таблиці.

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

На Рис. 15 представлено загальний вид кореляційного поля при різних видах зв’язку:

a)пряма кореляційна залежність;

b)відсутність зв’язку;

c)зворотна кореляційна залежність.

а)

б)

в)

Рис.15. Загальний вид кореляційного поля при різних видах зв’язку

Побудуємо точкову діаграму статистичної залежності (кореляційне поле), використовуючи Майстер діаграм пакету MS Excel (Вставка – Диаграмма – тип діаграми - Точечная). У наступному діалоговому вікні Исходные данные на

29

вкладці Ряд (Рис. 16) поставимо значення Х та Y, якщо на перетині Х та Y є числове значення.

Ряд	Значення Х	Значення Y
Ряд 1	B3	C2
Ряд 2	B3	D2
Ряд 3	B4	C2
Ряд 4	B4	D2
Ряд 5	B4	E2
Ряд 6	B4	F2
Ряд 7	B5	D2
Ряд 8	B5	E2
Ряд 9	B5	F2
Ряд 10	B5	G2
Ряд 11	B6	E2
Ряд 12	B6	F2
Ряд 13	B6	G2
Ряд 14	B7	F2
Ряд 15	B7	G2
Ряд 16	B7	H2

Рис.16. Вікно Майстра діаграм, вкладка Ряд

30