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

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

45

40

35

30

25

20

15

10

5

0

0

5

10

15

20

25

30

35

40

Рис.17. Точкова діаграма статистичної залежності (кореляційне поле)

Вигляд кореляційної „хмари” дозволяє припустити наявність кореляційного зв’язку між факторами.

2. Знайдемо групові середні			ух	i за формулою:
				y j mij
	y	xi
				m xi

Кореляційна залежність У по Х (результат):

Кореляційна залежність У по Х (формули в Excel):

31

A	B	C	D	E	F	G
...
13	Кореляційна залежність Y по X:
14
15	х	=B3	=B4	=B5	=B6	=B7
16	y x
17	m x	=I3	=I4	=I5	=I6	=I7
18	Комірка С16		=СУММПРОИЗВ(C2:H2;C3:H3)/I3
	Комірка D16		=СУММПРОИЗВ(C2:H2;C4:H4)/I4
	Комірка E16		=СУММПРОИЗВ(C2:H2;C5:H5)/I5
	Комірка F16		=СУММПРОИЗВ(C2:H2;C6:H6)/I6
	Комірка G16		=СУММПРОИЗВ(C2:H2;C7:H7)/I7

Аналогічно знайдемо групові середні					xyj за формулою:
				xi mij
	x y j
				my j

Кореляційна залежність Х по У (результат):

Кореляційна залежність Х по У (формули в Excel):
J	K	L	M	N	O	P	Q
13	Кореляційна залежність X по Y:
14
15	y	=C2	=D2	=E2	=F2	=G2	=H2
16	x y
17	m y	=C8	=D8	=E8	=F8	=G8	=H8
18	Комірка L16		=СУММПРОИЗВ($B$3:$B$7;C3:C7)/C8
	Комірка M16		=СУММПРОИЗВ($B$3:$B$7;D3:D7)/D8

32

	Комірка N16			=СУММПРОИЗВ($B$3:$B$7;E3:E7)/E8
	Комірка O16			=СУММПРОИЗВ($B$3:$B$7;F3:F7)/F8
	Комірка P16			=СУММПРОИЗВ($B$3:$B$7;G3:G7)/G8
	Комірка Q16			=СУММПРОИЗВ($B$3:$B$7;H3:H7)/H8
У прямокутній системі координат побудуємо точки, що відповідають парам
чисел ( x i ,	y j	), та з’єднаємо точки відрізками прямих. Отримана ламана буде
емпіричною лінією регресії У по Х (Рис. 18).
Аналогічно будується емпірична лінія регресії Х по У. Будуємо ламані
регресії, використовуючи „Майстер діаграм”.
			Емпіричні лінії регресії
45,00
40,00
35,00
30,00
25,00								Ряд 1
20,00								Ряд 2
15,00
10,00
5,00
0,00
0	5	10	15	20	25	30	35	40
Рис.18. Графік емпіричних кореляційних залежностей У по Х і Х по У.

На діаграмі Ряд 1 відповідає емпіричній кореляційній залежності У по Х. Ряд 2 - кореляційна залежність Х по У. Вигляд ламаних дозволяє припустити наявність лінійної кореляційної залежності.

Теоретичні рівняння регресії У по Х та Х по У знайдемо у вигляді:

yx y yx (x x);

xy x x y	(y y);
	,

де середнє значення ознаки Х:

33

ximxi

x

n

,

середнє значення ознаки У:

yjmyj

y

n

,

коефіцієнт регресії У по Х:

)

y

x

(xy

x

y

2

x

,

коефіцієнт регресії Х по У:

)

x y

(xy

x

y

2

y

,

xi yjmij

)2; y2 y2 (

)

2 ;

x2

x2

(

y

x

xy

n

Результати розрахунків запишемо в таблиці:

34

Формули у Excel:

	A	B	C	D	E	F	G
37	1)					1а)

38

x

i

mx

xi mx

i

xi2mx

i

y x

(XY)срi

i

39

=B3

=I3

=A39*B39

=A39^2*B39

=C16 =F39*C39/$B$44

40

=B4

=I4

=A40*B40

=A40^2*B40

=D16 =F40*C40/$B$44

41

=B5

=I5

=A41*B41

=A41^2*B41

=E16 =F41*C41/$B$44

42

=B6

=I6

=A42*B42

=A42^2*B42

=F16

=F42*C42/$B$44

43

=B7

=I7

=A43*B43

=A43^2*B43

=G16

=F43*C43/$B$44

44

∑

=СУММ

=СУММ

=СУММ

=СУММ

(B39:B43)

(C39:C43)

(D39:D43)

(G39:G43)

Результати розрахунків запишемо у таблиці:

Формули у Excel:

	I	J	K	L
37	2)
38	y j	my	yjmyj	y2j myj
		i
39	=C2	=C8	=I39*J39	=I39^2*J39
40	=D2	=D8	=I40*J40	=I40^2*J40
41	=E2	=E8	=I41*J41	=I41^2*J41

35

42	=F2				=F8			=I42*J42		=I42^2*J42
43	=G2				=G8			=I43*J43		=I43^2*J43
44	=H2				=H8			=I44*J44		=I44^2*J44
45	∑	=СУММ(J39:J44)						=СУММ(K39:K44)		=СУММ(L39:L44)
	N						O	P		Q
37	3)
38		x	ср		=				=C44/B44
39		(x2)ср =							=D44/B44
40		sx2 =						=P39-P38^2
41		sx =						=КОРЕНЬ(P40)
42		y	ср		=				=K45/J45
43		2 ср				=			=L45/J45
		(y )
44		sy2 =						=P43-P42^2
45		sy =						=КОРЕНЬ(P44)
46		(xy)		ср			=		=G44
47		rx/y			=			=(P46-P38*P42)/P44
48		ry/x			=			=(P46-P38*P42)/P40
49		rx/y × ry/x =							=P47*P48
50		r =						=КОРЕНЬ(P49)

Розрахуємо коефіцієнти регресії:

Формули у Excel:

	A	B		C	D		E
47		4)
48			ax=ry/x=			=P48
49		bx=yср-ry/x × xср=			=P42-P48*P38
50			ay=rx/y=			=P47
51		by=xср-rx/y ×yср=			=P38-P47*P42

36

x =24,49; y =23,40;

y x 0,69 ;

x

y 0,88 ; .

Рівняння

кореляційної

залежності

У

по

Х:

yx

-23,4=0,69(х−24,49),

де

y x

=0,69х+6,5.

Рівняння

кореляційної

залежності

Х

по

У:

x y

-24,49=0,88(у-23,4),

де

x y

=0,88у+3,9.

З урахуванням отриманих коефіцієнтів розрахуємо Ухтеор та Хутеор.

Розрахунки наведені у формулах Excel:

Формули у Excel:

37

Визначимо щільність зв’язку між факторами. Розрахуємо лінійний
коефіцієнт кореляції за формулою:
			r		x	y y	x ,
де знак перед коренем збігається зі знаками y x та							x y (вони однакові).
Оскільки y		x >0, то r = +		0,69 0,88		0,78
Значення лінійного коефіцієнта кореляції свідчить про помірний зв’язок
між досліджуваними ознаками.
Побудуємо отримані теоретичні регресійні прямі за допомогою майстра
діаграм (Рис. 19).
			Теоретичнілініїрегресії
45,00
40,00
35,00
30,00
25,00									Ряд 1
20,00
									Ряд 2
15,00
10,00
5,00
0,00
0	5	10	15	20	25	30	35	40	45
Рис.19. Графік теоретичних емпіричних кореляційних залежностей У по Х та Х по У

На діаграмі Ряд 1 відповідає теоретичній кореляційній залежності У по Х. Ряд 2 - кореляційна залежність Х по У.

3. Коефіцієнт a1 = 0,69 у регресії yx =0,69х+6,5 показує, що збільшення середньооблікової кількості на одну особу призводить до збільшення товарообігу в середньому на 0,69 млн грн. Це своєрідний емпіричний норматив приросту ефективності використання робітників даною групою магазинів. Вільний член

а0 = 6,5 не інтерпретується, оскільки має розрахункове значення.

Знайдемо прогнозне значення для х = 39: ух 0,69 39 6,5 33,41.

38

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

за темою „MS Excel. Елементи кореляційно-регресійного аналізу”

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

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

основних виробничих фондів (ОВФ) Х (млн грн) для сукупності 50 однотипних підприємств наведена в таблиці (N – номер варіанта):

Х	У	7+N–	11+N–	15+N-	19+N-	23+N-	mx
		11+N	15+N	19+N	23+N	27+N
20+N-	25+N	2	1				3
25+N-	30+N	3	6	4			13
30+N-	35+N		3	11	7		21
35+N-	40+N		1	2	6	2	11
40+N-	45+N				1	1	2
my		5	11	17	14	3	50
Необхідно:

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

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

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

______________________________________________

39

Список літератури

1.Вітюк В.Ф., Мацкул В.М., Чернишев В.Г., Міцкевич Ю.О. Методичні вказівки з курсу „Теорія ймовірностей та математична статистика” для студентів ІІ курсу всіх форм навчання усіх спеціальностей. - Одеса: ОДЕУ, ротапринт, 2006. – 48 с.

2.Лук’янова В.В. Комп’ютерний аналіз даних. – К.: Академія, 2003. – 344 с.

3.Станчук К.І., Зоріна В.С. Методичні вказівки до лабораторних робіт з курсу „Інформатика та комп’ютерна техніка”. Розділ „Сучасні технології автоматизації офісу. Табличний процесор Microsoft Excel” для студентів 2 курсу денної форми навчання спеціальності „Економіка підприємства” та „Банківська справа”. –

Одеса: ОДЕУ, 2004. – 80 с.

4.Статистичний щорічник України за 2007 рік / За редакцією О.Г.Осауленка. – К.: ТОВ «Видавництво „Консультант”». - 572 с.

5.Табличний процессор Microsoft Excel: Методичні вказівки до виконання лабораторних робіт з дисципліни „Інформатика та комп’ютерна техніка”. Ч. 2 /Ю.В. Бондарчук, Є.С. Вакал, С.Г. Карпенко та ін. – К.: МАУП, 2002. – 104 с.

6.Тюрин Ю.Н., Макаров А.А. Статистический анализ данных на компьютере. -

М.: ИНФРА, 1998.

7.http://www.ukrstat.gov.ua – Офіційний сайт Державного комітету статистики України.

Зміст
Вступ .......................................................................................................................................	3
1. Статистична обробка даних, аналіз та прогнозування в MS Excel..............................	4
Теоретичні відомості.........................................................................................................	4
Прогнозування із застосуванням методу ковзного середнього....................................	5
Аналіз та прогнозування на основі трендів.....................................................................	10
Прогнозування за допомогою вбудованих функцій ТЕНДЕНЦИЯ(), РОСТ() .........	14
Варіанти завдань для індивідуальної роботи № 1 за темою „MS Excel.
Статистична обробка даних, аналіз та прогнозування”...............................................	22
2. Кореляційно-регресійний аналіз у MS Excel................................................................	28
Теоретичні поняття..........................................................................................................	28
Варіанти завдань для індивідуальної роботи № 2 за темою „MS Excel.
Елементи кореляційно-регресійного аналізу”..............................................................	39
Список літератури................................................................................................................	40

40