Построение контрольной карты по альтернативному признаку.

На листе Excel формируем таблицу с исходными данными (рис. 5.7).

В ячейку Е3 вводим формулу для расчета общего количества проконтролированных изделий: =СУММ(B3:B12).

В ячейку F3 вводим формулу для расчёта общего содержания вкусовых добавок: =СУММ(C3:C12).

В ячейку G3 вводим формулу для расчёта относительного содержания вкусовых добавок в продукции U_отн: =C3/B3 и протягиваем её до конца столбца.

В ячейку Н3 вводим формулу для расчёта U_ср: =$F$3/$E$3 и также протягиваем её до конца столбца.

Результаты расчётов представлены на рис. 5.8.

Для расчёта контрольных границ карты используются формулы (5.5) и (5.6), которые вводятся соответственно в ячейки I3 и J3 и протягиваются до конца столбцов:

(5.5)

(5.6)

Рис. 5.8. Результаты расчётов

Как и в Задаче 1, строим контрольную карту по альтернативному признаку, представленную на рис. 5.9.

Рис. 5.9. Контрольная карта по альтернативному признаку

Вывод: в позициях №3 (йогурт с вишней) и №5 (йогурт с тропическим вкусом) повышенное содержание вкусовых добавок (возможно из-за неполадок оборудования и т.п.). В позиции №4 (йогурт с клубникой) пониженное содержание вкусовых добавок. Эти позиции нельзя отправлять на реализацию.

Варианты заданий

1 вариант

Контрольный листок

Проба 1 (Х1)

Проба 2 (Х2)

Проба 3 (Х3)

Проба 4 (Х4)

0,51

0,56

0,52

0,58

0,53

0,49

0,51

0,49

0,54

0,58

0,59

0,56

0,46

0,56

0,51

0,58

0,51

0,55

0,5

0,55

0,57

0,48

0,51

0,52

0,51

0,53

0,52

0,51

0,56

0,59

0,57

0,54

0,51

0,57

0,52

0,57

0,53

0,58

0,51

0,54

0,48

0,52

0,46

0,49

0,51

0,58

0,53

0,54

0,48

0,51

0,5

0,59

0,54

0,49

0,58

объём	ср. содержание
выборки (n)	вкус. добавок
30	14
70	11
60	5
90	6
50	9
100	4
100	15
120	7
50	9
80	4

объём	ср. содержание
выборки (n)	вкус. добавок
40	11
30	3
70	13
20	9
50	10
40	17
60	4
40	14
50	9
30	4

2 вариант

Контрольный листок
Проба 1 (Х1)	Проба 2 (Х2)	Проба 3 (Х3)	Проба 4 (Х4)
2,05	2,18	2,62	0,82
1,87	0,28	4,42	4,08
2,36	3,82	0,48	0,38
1,99	2,28	3,22	1,58
2,11	1,88	2,02	3,98
2,35	0,82	0,38	2,08
2,16	3,44	1,52	2,02
1,75	1,16	2,56	4,08
2,36	2,58	0,48	3,68
2,32	2,8	2,78	2,58
0,82	0,62	0,48	1,52
2,62	0,34	0,38	2,58
2,28	4,16	2,45	0,48
1,42	1,88	2,56	2,59
0,22	2,62	0,98	0,58

3 вариант

Контрольный листок

Проба 1 (Х1)

Проба 2 (Х2)

Проба 3 (Х3)

Проба 4 (Х4)

0,151

0,156

0,152

0,158

0,153

0,149

0,151

0,149

0,154

0,158

0,159

0,156

0,146

0,156

0,151

0,158

0,151

0,155

0,15

0,155

0,157

0,148

0,151

0,152

0,151

0,153

0,152

0,151

0,156

0,159

0,157

0,154

0,151

0,157

0,152

0,157

0,153

0,158

0,151

0,154

0,148

0,152

0,146

0,149

0,151

0,158

0,153

0,154

0,148

0,151

0,15

0,159

0,154

0,149

0,158

объём	ср. содержание
выборки (n)	вкус. добавок
180	24
170	21
260	25
190	26
250	29
100	24
200	25
120	27
150	19
180	14

объём	ср. содержание
выборки (n)	вкус. добавок
140	21
130	23
170	23
120	29
150	20
140	27
160	24
140	24
150	29
130	24

4 вариант

Контрольный листок
Проба 1 (Х1)	Проба 2 (Х2)	Проба 3 (Х3)	Проба 4 (Х4)
2,105	2,118	2,162	0,182
1,187	0,118	4,142	4,108
2,136	3,182	0,418	0,138
1,199	2,128	3,212	1,158
2,111	1,188	2,102	3,198
2,135	0,182	0,138	2,108
2,116	3,414	1,152	2,102
1,175	1,116	2,156	4,108
2,136	2,158	0,148	3,618
2,132	2,18	2,178	2,158
0,182	0,612	0,148	1,152
2,162	0,134	0,318	2,158
2,128	4,116	2,145	0,148
1,142	1,188	2,516	2,159
0,212	2,162	0,198	0,158

5 вариант

Контрольный листок

Проба 1 (Х1)

Проба 2 (Х2)

Проба 3 (Х3)

Проба 4 (Х4)

0,251

0,256

0,252

0,258

0,253

0,249

0,251

0,249

0,254

0,258

0,259

0,256

0,246

0,256

0,251

0,258

0,251

0,255

0,25

0,255

0,257

0,248

0,251

0,252

0,251

0,253

0,252

0,251

0,256

0,259

0,257

0,254

0,251

0,257

0,252

0,257

0,253

0,258

0,251

0,254

0,248

0,252

0,246

0,249

0,251

0,258

0,253

0,254

0,248

0,251

0,25

0,259

0,254

0,249

0,258

объём	ср. содержание
выборки (n)	вкус. добавок
230	24
270	31
260	25
290	26
250	29
200	24
210	15
220	37
250	39
280	34

объём	ср. содержание
выборки (n)	вкус. добавок
240	31
230	33
270	13
220	39
250	30
240	17
260	34
240	14
250	29
230	34

6 вариант

Контрольный листок
Проба 1 (Х1)	Проба 2 (Х2)	Проба 3 (Х3)	Проба 4 (Х4)
2,305	2,318	2,362	0,832
1,387	0,328	4,342	4,308
2,336	3,382	0,348	0,338
1,399	2,328	3,322	1,358
2,311	1,388	2,302	3,398
2,335	0,382	0,338	2,308
2,316	3,344	1,352	2,302
1,375	1,316	2,356	4,308
2,336	2,358	0,348	3,368
2,332	2,38	2,378	2,358
0,382	0,362	0,348	1,352
2,362	0,334	0,338	2,358
2,328	4,316	2,345	0,348
1,342	1,388	2,356	2,359
0,322	2,362	0,298	0,358

7 вариант

Контрольный листок

Проба 1 (Х1)

Проба 2 (Х2)

Проба 3 (Х3)

Проба 4 (Х4)

0,451

0,456

0,452

0,458

0,453

0,449

0,451

0,449

0,454

0,458

0,459

0,456

0,446

0,456

0,451

0,458

0,451

0,455

0,45

0,455

0,457

0,448

0,451

0,452

0,451

0,453

0,452

0,451

0,456

0,459

0,457

0,454

0,451

0,457

0,452

0,457

0,453

0,458

0,451

0,454

0,448

0,452

0,446

0,449

0,451

0,458

0,453

0,454

0,448

0,451

0,45

0,459

0,454

0,449

0,458

объём	ср. содержание
выборки (n)	вкус. добавок
330	44
370	41
360	45
390	46
350	49
100	44
100	45
120	47
350	49
380	44

объём	ср. содержание
выборки (n)	вкус. добавок
340	41
330	43
370	13
320	49
350	40
340	18
360	44
340	14
350	49
330	44

8 вариант

Контрольный листок
Проба 1 (Х1)	Проба 2 (Х2)	Проба 3 (Х3)	Проба 4 (Х4)
2,045	2,148	2,642	0,842
1,847	0,248	4,442	4,048
2,346	3,842	0,448	0,348
1,949	2,248	3,242	1,548
2,141	1,88	2,042	3,948
2,345	0,842	0,348	2,048
2,146	3,444	1,542	2,042
1,745	1,146	2,546	4,048
2,346	2,548	0,448	3,648
2,342	2,48	2,748	2,548
0,842	0,642	0,448	1,542
2,642	0,344	0,348	2,548
2,248	4,146	2,445	0,448
1,442	1,848	2,546	2,549
0,242	2,642	0,948	0,548

9 вариант

Контрольный листок
Проба 1 (Х1)	Проба 2 (Х2)	Проба 3 (Х3)	Проба 4 (Х4)
0,551	0,556	0,556	0,552
0,558	0,553	0,459	0,551
0,459	0,554	0,558	0,559
0,556	0,456	0,556	0,551
0,558	0,551	0,555	0,55
0,55	0,555	0,557	0,458
0,551	0,552	0,551	0,553
0,552	0,551	0,556	0,559
0,557	0,554	0,551	0,557
0,552	0,557	0,553	0,558
0,551	0,554	0,458	0,552
0,456	0,459	0,551	0,558
0,553	0,554	0,554	0,458
0,551	0,551	0,55	0,559
0,554	0,554	0,459	0,558

объём	ср. содержание
выборки (n)	вкус. добавок
530	54
570	51
560	55
590	56
550	59
500	54
500	55
520	57
550	59
580	54

объём	ср. содержание
выборки (n)	вкус. добавок
540	51
530	53
570	53
520	59
550	50
540	57
560	54
540	54
550	59
530	54

10 вариант

Контрольный листок
Проба 1 (Х1)	Проба 2 (Х2)	Проба 3 (Х3)	Проба 4 (Х4)
2,055	2,158	2,652	0,852
1,857	0,258	4,452	4,058
2,356	3,852	0,458	0,358
1,959	2,258	3,252	1,558
2,151	1,858	2,052	3,958
2,355	0,852	0,358	2,058
2,156	3,454	1,552	2,052
1,755	1,156	2,556	4,058
2,356	2,558	0,458	3,658
2,352	2,58	2,758	2,558
0,852	0,652	0,458	1,552
2,652	0,354	0,358	2,558
2,258	4,156	2,455	0,458
1,452	1,858	2,556	2,559
0,252	2,652	0,958	0,558

Лабораторная работа № 4

Двухфакторный дисперсионный анализ качества

Цель работы: ознакомиться с методом факторного анализа и научиться оценивать существенность влияния различных факторов на изучаемый процесс с помощью Excel.

Задача: По данным табл. 8.1 оценить существенность влияния федерального округа РФ и рынка недвижимости на индекс цен на жильё при уровне значимости α=0,05.

Таблица 8.1

Решение: Определим исходные данные (табл. 8.2): Фактор А (рынок жилья) имеет 2 уровня (вводим в ячейку F2), Фактор В (федеральный округ) имеет 4 уровня (ячейка G2), количество наблюдений на каждом уровне = 4 (ячейка Н2), общее количество наблюдений вводим в ячейку I2: =F2*G2*H2.

Таблица 8.2

Вычислим выборочные средние по факторам (табл. 8.3). В ячейку F5 вводим формулу для вычисления выборочной средней по первому уровню фактора А: =СРЗНАЧ(B3:B18); в ячейку G5 вводим формулу соответственно для второго уровня фактора А: =СРЗНАЧ(C3:C18). В ячейку Н5 вводим формулу для вычисления выборочной средней по первому уровню фактора В: =СРЗНАЧ(B3:C6). В ячейки I5, J5, K5 вводим формулы для 2-го, 3-го и 4-го уровней фактора В соответственно. И, наконец, в ячейку L5 вводим формулу для вычисления общей средней: =СРЗНАЧ(B3:C18).

Таблица 8.3

Далее проведём вычисление сумм квадратов отклонений выборочных средних фактора А от общей средней (табл. 8.4). В ячейку G7 вводим формулу: =(F5-L5)^2; в ячейку G8 вводим: =(G5-L5)^2. В ячейке G9 вычисляем сумму квадратов отклонений фактора А: =G2*H2*СУММ(G7:G8).

Аналогично вычислим сумму квадратов отклонений выборочных средних фактора В от общей средней (табл. 8.4). В ячейку J7 вводим формулу: =(H5-L5)^2, в ячейку J8 вводим: =(I5-L5)^2 и т.д. В ячейке J11 вычисляем сумму квадратов отклонений фактора В: =F2*H2*СУММ(J7:J10).

Таблица 8.4

Для дальнейшего вычисления остаточной суммы квадратов отклонений нужно сначала вычислить выборочные средние для каждой группы наблюдений (табл. 8.5). В ячейку F13 вводим формулу: =СРЗНАЧ(B3:B6), в ячейку F14 вводим соответственно: =СРЗНАЧ(B7:B10) и т.д.

Таблица 8.5

Далее создаём табл. 8.6 для вычисления остаточной суммы квадратов отклонений. Заполнять её нужно следующим образом: из каждого наблюдения первой группы вычитаем выборочную среднюю первой группы, возводим результат в квадрат – и так для каждой группы, используя свои значения. Например, в ячейку В20 вводим формулу: =(B3-$F$13)^2 и протягиваем её до ячейки В23. В ячейку В24 вводим формулу: =(B7-$F$14)^2 и протягиваем её до ячейки В27 и т.д.

Таблица 8.6

В ячейке J13 суммируем все значения этой таблицы формулой: =СУММ(B20:C35) и получаем Sост.=331,325.

Для вычисления общей суммы квадратов отклонений создаём ещё одну таблицу (табл. 8.7). В ячейку Е20 вводим формулу: =(B3-$L$5)^2 и протягиваем её до ячейки F35.

Таблица 8.7

В ячейке J15 суммируем все значения этой таблицы формулой: =СУММ(E20:F35) и получаем Sобщ.=901,24.

В ячейке J17 вычисляем межфакторную сумму квадратов отклонений по формуле: =J15-G9-J11-J13 и получаем S_АВ=5,2175.

Составляем таблицу двухфакторного дисперсионного анализа (табл. 8.8). В ячейки I21:I25 копируем значения сумм квадратов отклонений, вычисленных ранее.

В следующий столбец вводим количества степеней свободы каждого фактора – в ячейку J21 вводим: =F2-1; в ячейку J22: =G2-1; в ячейку J23: =H2-1; в ячейку J24: =I2-F2*G2; в ячейку J25: =I2-1.

Далее вычисляем средние квадраты отклонений. В ячейку К21 вводим формулу: =I21/J21 и протягиваем её до ячейки К24.

Вычисляем наблюдаемые критерии Фишера. В ячейку L21 вводим формулу: =K21/$K$24 и протягиваем её до ячейки L23.

В последнем столбце вычисляем критические критерии Фишера. В ячейку М21 вводим формулу: =FРАСПОБР(0,05;J21;$J$24) и протягиваем её до ячейки М23

Таблица 8.8

Выводы:

Так как F_набл.А < F_крит.А и F_{набл.АВ} < F_{крит.АВ}, следовательно, влияние фактора А и взаимодействия факторов А и В на индекс цен на жильё в указанный период времени не является статистически значимым.

Так как F_набл.В < F_крит.В, следовательно, влияние фактора В на индекс цен на жильё в указанный период времени является статистически значимым.

Варианты заданий

Вариант 1			Вариант 2
	Рынок жилья				Рынок жилья
Округ	первичный	вторичный	Округ		первичный	вторичный
	132,5	188,1			120,9	104,6
Центральный	221,8	144,3	Центральный		243,8	140,1
	202,9	170,5			135	138,9
	355,7	108,8			393,1	192,7
	48,6	189,3			103,6	34,7
Северный	110,8	82,4	Северный		247,8	170,8
	140,5	316,9			118,7	107,5
	162,2	126,2			146,1	94,6
	76,9	93,1			49,6	41,9
Южный	192,7	70,4	Южный		212,1	149
	207,6	137,8			113,5	115,2
	255,4	130,1			159	109,6
	48,7	132,6			83,5	125
Восточный	191,4	119,5	Восточный		155	223,8
	217	145,8			154,8	169,6
	258,7	130,8			256,3	134,4

Вариант 3			Вариант 4
	Рынок жилья			Рынок жилья
Округ	первичный	вторичный	Округ	первичный		вторичный
	190	168,4		501,4		846,3
Центральный	166,9	126,4	Центральный	442,7		703,4
	170,8	131,8		846,2		391,4
	156,6	118,4		522,1		748,1
	157,7	139,7		780,4		450,6
Северный	141,4	116,5	Северный	504,6		891
	151,6	113,5		346		689,1
	179,5	123,4		462,3		526,5
	199	176,2		366,7		389,6
Южный	157,8	149,8	Южный	821,2		461,2
	163,6	157,9		651,7		705,7
	104,4	192,3		567,6		408,4
	150,9	130,6		686,5		531,2
Восточный	253,5	141	Восточный	810		841,3
	262,6	123,9		829,3		716,4
	151,3	134,8		529,4		821,3

Вариант 5			Вариант 6
	Рынок жилья				Рынок жилья
Округ	первичный	вторичный	Округ		первичный	вторичный
	1132,5	1188,1			1220,9	1204,6
Центральный	2121,8	1144,3	Центральный		2243,8	1240,1
	2102,9	1170,5			2135	1238,9
	3155,7	1108,8			3923,1	1292,7
	1148,6	1189,3			1023,6	2234,7
Северный	1110,8	1182,4	Северный		2427,8	1270,8
	1140,5	1116,9			1128,7	1207,5
	1162,2	1126,2			1426,1	2294,6
	1176,9	1193,1			4229,6	2241,9
Южный	1192,7	1170,4	Южный		2122,1	2149
	2107,6	1137,8			1123,5	1125,2
	2155,4	1130,1			1259	1029,6
	1148,7	1132,6			1322,5	1225
Восточный	1191,4	1119,5	Восточный		2155	2232,8
	1217	1145,8			1524,8	1692,6
	2518,7	1130,8			2526,3	1342,4

Вариант 7			Вариант 8
	Рынок жилья			Рынок жилья
Округ	первичный	вторичный	Округ	первичный		вторичный
	1190	1618,4		5201,4		8246,3
Центральный	1661,9	1216,4	Центральный	4242,7		7203,4
	1701,8	1311,8		8246,2		3291,4
	1561,6	1118,4		5222,1		7248,1
	1571,7	1319,7		7280,4		4250,6
Северный	1411,4	1116,5	Северный	5204,6		2891
	1511,6	1113,5		2346		6829,1
	1791,5	1213,4		4622,3		5226,5
	1919	1716,2		3626,7		3829,6
Южный	1517,8	1419,8	Южный	8221,2		4621,2
	1613,6	1517,9		6521,7		7025,7
	1014,4	1912,3		5627,6		4028,4
	1510,9	1310,6		6826,5		5321,2
Восточный	2513,5	1141	Восточный	8210		8421,3
	2612,6	1231,9		8292,3		7126,4
	1511,3	1341,8		5292,4		8221,3

Вариант 9			Вариант 10
	Рынок жилья			Рынок жилья
Округ	первичный	вторичный	Округ	первичный	вторичный
	1332,5	1388,1		1420,9	4104,6
Центральный	2321,8	1344,3	Центральный	2443,8	4140,1
	2302,9	1370,5		4135	4138,9
	3355,7	1308,8		3943,1	4192,7
	3348,6	1389,3		1043,6	4434,7
Северный	1310,8	3382,4	Северный	2447,8	1470,8
	1340,5	3316,9		1148,7	1407,5
	1362,2	1326,2		1446,1	4494,6
	3376,9	3393,1		4449,6	4441,9
Южный	1392,7	3370,4	Южный	2142,1	4149
	2307,6	1337,8		1143,5	1145,2
	2355,4	1330,1		1459	1049,6
	3348,7	1332,6		8344,5	1425
Восточный	1391,4	1319,5	Восточный	1545	2234,8
	3217	1345,8		1454,8	1694,6
	2538,7	1330,8		2456,3	1344,4

Лабораторная работа № 5

Корреляционный и регрессионный анализ качества

Цель работы: Научиться устанавливать наличие или отсутствие, а также характер линейной корреляционной взаимосвязи между случайными величинами с помощью методов корреляционного и регрессионного анализа.

Задача: На основе данных, представленных в табл. 9.1 провести корреляционный и регрессионный анализ и установить наличие или отсутствие корреляционных взаимосвязей.

Таблица 9.1

Страна	Y - индекс человеческого развития	X1 – продолжит. жизни	X2 - калорийность питания
Австрия	0,904	77	3343
Австралия	0,922	78,2	3001
Аргентина	0,827	72,9	3136
Белоруссия	0,763	68	3101
Бельгия	0,923	77,2	3543
Бразилия	0,739	66,8	2938
Великобритания	0,918	77,2	3237
Венгрия	0,795	70,9	3402
Германия	0,906	77,2	3330
Греция	0,867	78,1	3575

Решение: На листе Excel создадим таблицу исходных данных (табл. 9.1).

По этим данным построим матрицу корреляции: Данные → Анализ данных → Корреляция → ОК. Появится окошко, которое нужно заполнить, как показано на рис. 9.1.

Примечание: если во вкладке Данные отсутствует пакет Анализа данных, то необходимо настроить его следующим образом:

Нажать кнопку « Office » → кнопка Параметры Excel → Надстройки → выбрать Пакет Анализа → кнопка Перейти → поставить галочку возле Пакет анализа → ОК.

Рис. 9.1

Результаты вычислений представлены в табл. 9.2:

Таблица 9.2

Отсюда находим коэффициенты корреляции:

Проверим значимость найденных коэффициентов корреляции при уровне значимости α=0,05, используя критерий Стьюдента (табл. 9.3). В ячейке А20 вычислим количество исходных данных по формуле: =СЧЁТ(B2:B11). В ячейках В20:D20 вычислим квадраты коэффициентов корреляции (=B16^2, =С16^2, =D16^2) В ячейке Е20 вычислим по формуле: =B16*КОРЕНЬ(A20-2)/КОРЕНЬ(1-В20). Аналогично вычисляются остальные критерии и . В ячейке Н20 вычислим по формуле: =СТЬЮДРАСПОБР(0,05;А20-2).

Таблица 9.3

Сравнивая найденные значения критериев, можно сделать выводы о том, что:

- индекс человеческого развития и продолжительность жизни сильно взаимосвязаны, т.к. коэффициент корреляции между ними = 0,962, значимость этого коэффициента высока и квадрат коэффициента корреляции показывает, что 92,5% индексов развития определяются продолжительностью жизни;

- данная калорийность питания (в среднем 3200 ккал) хотя и влияет на индексы развития и продолжительность жизни (т.к. коэффициенты корреляции отличны от нуля), но не является статистически значимым фактором (т.к. наблюдаемые критерии меньше критического) и определяет соответственно 18,3% развития и 27,5% продолжительности жизни.

Далее построим корреляционные поля зависимостей случайных величин друг от друга (рис. 9.2 – 9.4).

Выбираем вкладку Вставка → Точечная диаграмма → 1 вид (отдельные точки) → на пустой диаграмме щёлкаем правой кнопкой мышки → Выбрать данные → Добавить → Значения Х: выбираем столбец Х1 → Значения Y: выбираем столбец Y → ОК → ОК → щёлкаем правой кнопкой мышки по любой точке данных построенной диаграммы → Добавить линию тренда → ставим галочку Линейная → ставим галочку Показывать уравнение на диаграмме → Закрыть.

Аналогично строим графики и для двух других полей зависимости.

Рис. 9.2

Рис. 9.3

Рис. 9.4

Далее составим прогноз значений результативного признака на основе соответствующих уравнений парной линейной регрессии и вычислим стандартную ошибку прогноза.

Выделяем мышкой ячейки Е2:Е11, вызываем функцию ТЕНДЕНЦИЯ и заполняем данными, как показано на рис. 9.5 (не забываем нажимать Ctrl+Shift+Enter для полного заполнения таблицы):

Рис. 9.5

В результате получаем следующие расчётные значения (табл. 9.4):

Таблица 9.4

По полученным данным вычисляем стандартную ошибку прогноза. В ячейку Е12 вводим формулу: =СТАНДОТКЛОН(E2:E11).

Полученное значение 0,0676626 говорит о том, что прогноз может быть ошибочным на 6,7%.

Варианты заданий

Вариант 1
Страна	Y – Индекс челов. развития	X₁ – Продолжительность жизни	X₂ – Калорийность питания
Дания	0,905	75,7	3446,24
Египет	0,616	66,3	2026,24
Израиль	0,883	77,8	2889,17
Индия	0,545	62,6	1316,17
Испания	0,894	78	2945,73
Италия	0,902	78,2	3153,62
Канада	0,932	79	2848,19
Казахстан	0,741	67,8	2225,18
Китай	0,701	69,8	1993,64
Латвия	0,744	68,4	2128,58

Вариант 2
Страна	Y – Индекс челов. развития	X₁ – Продолжительность жизни	X₂ – Калорийность питания
Нидерланды	0,921	77,9	3001,53
Норвегия	0,927	78,1	3105,45
Польша	0,802	72,5	2681,88
Россия	0,852	66,6	2019,88
Румыния	0,751	69,9	2213,13
США	0,907	76,6	3576,13
Турция	0,728	69	2597,54
Украина	0,721	68,8	1984,91
Финляндия	0,913	76,8	2662,38
Франция	0,918	78,1	3259,81

Вариант 3
Страна	Y – Индекс челов. развития	X₁ – Продолжительность жизни	X₂ – Калорийность питания
Англия	0,915	75,9	2851,53
Аргентина	0,827	68,1	2405,45
Беларусь	0,702	62,5	2381,88
Германия	0,952	76,6	2919,88
Мексика	0,751	67,9	2213,13
ОАЭ	0,961	76,2	2576,13
Сербия	0,728	68,2	2307,54
Швейцария	0,971	78,8	2784,91
Швеция	0,913	76,5	3162,38
Япония	0,934	78,1	2459,81
Вариант 4
Страна	Y – Индекс челов. развития	X₁ – Продолжительность жизни	X₂ – Калорийность питания
Боливия	0,705	68,7	2446,24
Египет	0,616	66,3	2026,24
Тайланд	0,683	67,8	2089,17
Индия	0,545	62,6	1316,17
Испания	0,894	78	2945,73
Италия	0,902	78,2	3153,62
Канада	0,932	79	2848,19
Казахстан	0,741	67,8	2225,18
Китай	0,701	69,8	1993,64
Литва	0,713	62,4	2028,58

Вариант 5
Страна	Y – Индекс челов. развития	X₁ – Продолжительность жизни	X₂ – Калорийность питания
Непал	0,721	67,9	2001,53
Норвегия	0,927	78,1	3105,45
Польша	0,802	72,5	2681,88
Россия	0,852	66,6	2019,88
Румыния	0,751	69,9	2213,13
Таджикистан	0,607	61,6	1876,13
Турция	0,728	69	2597,54
Украина	0,721	68,8	1984,91
Финляндия	0,913	76,8	2662,38
Франция	0,918	78,1	3259,81

Вариант 6
Страна	Y – Индекс челов. развития	X₁ – Продолжительность жизни	X₂ – Калорийность питания
Австралия	0,895	75,9	2961,53
Аргентина	0,827	68,1	2405,45
Беларусь	0,702	62,5	2381,88
Германия	0,952	76,6	2919,88
Мексика	0,751	67,9	2213,13
Перу	0,761	66,2	2176,13
Сербия	0,728	68,2	2307,54
Швейцария	0,971	78,8	2784,91
Швеция	0,913	76,5	3162,38
Япония	0,934	78,1	2459,81

Вариант 7
Страна	Y – Индекс челов. развития	X₁ – Продолжительность жизни	X₂ – Калорийность питания
Греция	0,845	78,4	2446,24
Египет	0,616	66,3	2026,24
Израиль	0,883	77,8	2889,17
Индия	0,545	62,6	1316,17
Испания	0,894	78	2945,73
Италия	0,902	78,2	3153,62
Канада	0,932	79	2848,19
Казахстан	0,741	67,8	2225,18
Китай	0,701	69,8	1993,64
Уругвай	0,644	58,4	2008,58

Вариант 8
Страна	Y – Индекс челов. развития	X₁ – Продолжительность жизни	X₂ – Калорийность питания
Нидерланды	0,921	77,9	3001,53
Пакистан	0,727	64,1	2105,45
Польша	0,802	72,5	2681,88
Россия	0,852	66,6	2019,88
Румыния	0,751	69,9	2213,13
США	0,907	76,6	3576,13
Турция	0,728	69	2597,54
Украина	0,721	68,8	1984,91
Финляндия	0,913	76,8	2662,38
Хорватия	0,718	68,1	2259,81

Вариант 9
Страна	Y – Индекс челов. развития	X₁ – Продолжительность жизни	X₂ – Калорийность питания
Армения	0,715	78,9	2651,53
Аргентина	0,827	68,1	2405,45
Беларусь	0,702	62,5	2381,88
Германия	0,952	76,6	2919,88
Грузия	0,851	77,9	2813,13
ОАЭ	0,961	76,2	2576,13
Сербия	0,728	68,2	2307,54
Швейцария	0,971	78,8	2784,91
Швеция	0,913	76,5	3162,38
Япония	0,934	78,1	2459,81

Вариант 10
Страна	Y – Индекс челов. развития	X₁ – Продолжительность жизни	X₂ – Калорийность питания
Дания	0,905	75,7	3446,24
Египет	0,616	66,3	2026,24
Израиль	0,883	77,8	2889,17
Ирак	0,545	57,6	1316,17
Испания	0,894	78	2945,73
Италия	0,902	78,2	3153,62
Канада	0,932	79	2848,19
Казахстан	0,741	67,8	2225,18
Китай	0,701	69,8	1993,64
Узбекистан	0,644	61,4	1928,58

Лабораторная работа № 6

Анализ временны́х рядов

Цель работы: ознакомиться с методами анализа временны́х рядов, получить основу для формирования навыков работы с экономическими временны́ми рядами.

Задача: Дан временно́й ряд – котировки акций в течение месяца (табл. 10.1). Необходимо исследовать первые 25 значений, сделать краткосрочный прогноз поведения цены акции в конце месяца и сравнить его с реальными данными.

Таблица 10.1

Решение: На первом этапе работы необходимо выяснить наличие (или отсутствие) неслучайной составляющей временно́го ряда. Для этого сначала нужно вычислить медиану временного ряда (табл. 10.3). В ячейку В34 вводим формулу: =МЕДИАНА(B2:B26) – работаем только с первыми 25-ю ячейками.

Далее разобьём данные временно́го ряда на серии (табл. 10.2). Сначала из данных вычтем значение медианы – для этого в ячейку С2 вводим формулу: =B2-$B$34 и протягиваем её до ячейки С26. В ячейку D2 вводим условие: =ЕСЛИ(C2<0;-1;1) и также протягиваем до ячейки D26. В следующем столбце Е2:Е26 вручную подсчитываем длину каждой серии.

Таблица 10.2

Далее вычисляем количество серий (табл. 10.3). В ячейку С34 вводим формулу: =СЧЁТ(E2:E26). Для нахождения самой длинной серии в ячейку D34 вводим условие: =ЕСЛИ(ABS(МАКС(E2:E26))>ABS(МИН(E2:E26));МАКС(E2:E26);МИН(E2:E26))

Таблица 10.3

Далее рассчитываем критерии выявления наличия неслучайной составляющей, основанные на методе медианы. В ячейку С36 вводим формулу: =0,5*(25+2-1,96*КОРЕНЬ(25-1)). Получаем значение 8,699. Это значение больше количества серий (8,699 > 4), а должно быть меньше, следовательно, во временно́м ряду скорее всего наличествует неслучайная составляющая.

Для подтверждения (или опровержения) нашей гипотезы вычислим дополнительный критерий. В ячейку С37 вводим формулу: =1,43*LN(25+1). Получаем значение 4,659. Это значение меньше максимальной длины серии (4,659 < 12), а должно быть больше, следовательно, гипотеза подтверждается, и во временно́м ряду точно наличествует неслучайная составляющая.

Далее рассмотрим еще один критерий выявления неслучайной составляющей (табл. 10.4). Для этого в ячейку F2 вводим формулу: =B3-B2 и протягиваем её до ячейки F26. Серии и их длины вычисляем также, как и в табл. 10.2. Количество серий и максимальную длину тоже вычисляем, как и для предыдущего критерия.

Таблица 10.4

Для выявления наличия неслучайной составляющей в ячейку G30 вводим формулу: =1/3*(2*25-1)-1,96*КОРЕНЬ((16*25-29)/90). Получаем значение 12,353. Это значение больше количества серий (12,353 > 12), а должно быть меньше, следовательно, во временно́м ряду скорее всего наличествует неслучайная составляющая.

Для подтверждения нашей гипотезы значение текущей максимальной длины серии должно быть меньше предыдущей максимальной длины серии (5 < 12), следовательно, гипотеза подтверждается, и во временно́м ряду точно наличествует неслучайная составляющая.

Для того, чтобы сделать краткосрочный прогноз поведения цены акции, сначала необходимо нанести на график исходные данные и построить линию тренда (рис. 10.1).

Заходим во вкладку Вставка → Точечная → 2-й вид → щёлкаем по окну диаграммы правой кнопкой мышки → Выбрать данные → Добавить → Имя ряда: Исходные данные → Значения Х: А2:А26 → Значения Y : B2:B26 → ОК → ОК→ щёлкаем правой кнопкой мышки по любой точке графика → Добавить линию тренда → ставим галочки возле Линейная и Показывать уравнение на диаграмме → Закрыть.

Рисунок 10.1

Для составления прогноза (табл. 10.5) в ячейку I2 вводим формулу, основанную на коэффициентах линии тренда: =2,133*A2+1052 и протягиваем до I31. В ячейку J2 вводим формулу для вычисления нижней границы доверительного интервала:=I2-СТЬЮДРАСПОБР(0,05;23)*СТОШYX($B$2:$B$26;$A$2:$A$26) и протягиваем её до ячейки J31. В ячейку К2 вводим формулу для расчёта верхней границы доверительного интервала:

=I2+СТЬЮДРАСПОБР(0,05;23)*СТОШYX($B$2:$B$26;$A$2:$A$26) и протягиваем её до ячейки К31.

По полученным данным строим графики краткосрочного прогноза (рис. 10.2), добавляя их на диаграмму с исходными данными.

Из графика видно, что остатки (5 последних значений котировок) попадают в границы прогноза, следовательно, прогноз можно считать достоверным.

Таблица 10.5

Рисунок 10.2

Варианты заданий

Вариант 1		Вариант 2		Вариант 3		Вариант 4		Вариант 5
1	908,3	1	858,6	1	945,8	1	847,1	1	1017,5
2	908,8	2	880,9	2	993,2	2	827,7	2	1019,9
3	908,2	3	876,2	3	985,7	3	818,3	3	1038,8
4	928,9	4	878,7	4	1004,1	4	800,2	4	1034,2
5	922,8	5	879,9	5	1006,8	5	785,5	5	1038,8
6	909,4	6	878,2	6	987,3	6	800,7	6	1033,6
7	927,7	7	865,9	7	996,4	7	803,9	7	1038,1
8	927,3	8	871,5	8	998,1	8	834,7	8	1038,4
9	926,7	9	868,3	9	1007,9	9	841,9	9	1038,6
10	931,5	10	885,2	10	1000,1	10	880,8	10	1045,1
11	918,1	11	890,8	11	994,2	11	860,7	11	1049,2
12	914,8	12	879,9	12	981,6	12	878,9	12	1046,4
13	901,7	13	893,5	13	992,5	13	884,1	13	1049,4
14	887,8	14	892,1	14	993,3	14	899,6	14	1038,8
15	878,5	15	911,3	15	978,6	15	889,3	15	1039,3
16	887,5	16	919,2	16	987,9	16	895,9	16	1044,2
17	861,1	17	911,4	17	988,4	17	882,1	17	1045,8
18	846,9	18	898,8	18	981,5	18	897,2	18	1030,5
19	858,4	19	914,8	19	998,4	19	890,2	19	1033,5
20	864,5	20	919,5	20	998,6	20	882,3	20	1028,3
21	844,5	21	916,9	21	996,7	21	890,3	21	1028,9
22	856,4	22	916,3	22	989,1	22	885,9	22	1023,6
23	859,7	23	930,8	23	987,4	23	900,9	23	1046,1
24	848,1	24	926,5	24	990,1	24	907,6	24	1048,3
25	843,3	25	934,3	25	980,2	25	915,1	25	1046,6
26	838,3	26	929,6	26	980,1	26	923,4	26	1050,1
27	829,5	27	920,2	27	982,7	27	901,8	27	1050,7
28	835,8	28	933,4	28	965,3	28	894,3	28	1058,8
29	828,8	29	945,4	29	967,2	29	876,2	29	1053,2
30	818,5	30	942,3	30	974,1	30	882,9	30	1051,8
31	911,4	31	911,4	31	981,5	31	981,5	31	1044,2
32	898,8	32	898,8	32	998,4	32	998,4	32	1045,8
33	914,8	33	914,8	33	998,6	33	998,6	33	1030,5
34	919,5	34	919,5	34	996,7	34	996,7	34	1033,5
35	916,9	35	916,9	35	989,1	35	989,1	35	1028,3
36	916,3	36	916,3	36	987,4	36	987,4	36	1028,9
37	930,8	37	930,8	37	990,1	37	990,1	37	1023,6
38	926,5	38	926,5	38	980,2	38	980,2	38	1046,1
39	934,3	39	934,3	39	980,1	39	980,1	39	1048,3
40	929,6	40	929,6	40	982,7	40	982,7	40	1046,6

Вариант 6		Вариант 7		Вариант 8		Вариант 9		Вариант 10
1	1908,3	1	2858,6	1	3945,8	1	4847,1	1	1117,5
2	1908,8	2	2880,9	2	3993,2	2	4827,7	2	1019,9
3	1908,2	3	2876,2	3	3985,7	3	4818,3	3	1038,8
4	1928,9	4	2878,7	4	3004,1	4	4800,2	4	1224,2
5	1922,8	5	2879,9	5	3006,8	5	4785,5	5	1038,8
6	1909,4	6	2878,2	6	3987,3	6	4800,7	6	1113,6
7	1927,7	7	2865,9	7	3996,4	7	4803,9	7	1038,1
8	1927,3	8	2871,5	8	3998,1	8	4834,7	8	1238,4
9	1926,7	9	2868,3	9	3007,9	9	4841,9	9	1038,6
10	1931,5	10	2885,2	10	3000,1	10	4880,8	10	1145,1
11	1918,1	11	2890,8	11	3994,2	11	4860,7	11	1049,2
12	1914,8	12	2879,9	12	3981,6	12	4878,9	12	1246,4
13	1901,7	13	2893,5	13	3992,5	13	4884,1	13	1149,4
14	1887,8	14	2892,1	14	3993,3	14	4899,6	14	1038,8
15	1878,5	15	2911,3	15	3978,6	15	4889,3	15	1239,3
16	1887,5	16	2919,2	16	3987,9	16	4895,9	16	1144,2
17	1861,1	17	2911,4	17	3988,4	17	4882,1	17	1045,8
18	1846,9	18	2898,8	18	3981,5	18	4897,2	18	1230,5
19	1858,4	19	2914,8	19	3998,4	19	4890,2	19	1033,5
20	1864,5	20	2919,5	20	3998,6	20	4882,3	20	1128,3
21	1844,5	21	2916,9	21	3996,7	21	4890,3	21	1228,9
22	1856,4	22	2916,3	22	3989,1	22	4885,9	22	1123,6
23	1859,7	23	2930,8	23	3987,4	23	4900,9	23	1246,1
24	1848,1	24	2926,5	24	3990,1	24	4907,6	24	1048,3
25	1843,3	25	2934,3	25	3980,2	25	4915,1	25	1046,6
26	1838,3	26	2929,6	26	3980,1	26	4923,4	26	1150,1
27	1829,5	27	2920,2	27	3982,7	27	4901,8	27	1050,7
28	1835,8	28	2933,4	28	3965,3	28	4894,3	28	1228,8
29	1828,8	29	2945,4	29	3967,2	29	4876,2	29	1153,2
30	1818,5	30	2942,3	30	3974,1	30	4882,9	30	1151,8
31	1911,4	31	2911,4	31	3981,5	31	4981,5	31	1224,2
32	1898,8	32	2898,8	32	3998,4	32	4998,4	32	1045,8
33	1914,8	33	2914,8	33	3998,6	33	4998,6	33	1130,5
34	1919,5	34	2919,5	34	3996,7	34	4996,7	34	1033,5
35	1916,9	35	2916,9	35	3989,1	35	4989,1	35	1128,3
36	1916,3	36	2916,3	36	3987,4	36	4987,4	36	1028,9
37	1930,8	37	2930,8	37	3990,1	37	4990,1	37	1123,6
38	1926,5	38	2926,5	38	3980,2	38	4980,2	38	1246,1
39	1934,3	39	2934,3	39	3980,1	39	4980,1	39	1148,3
40	1929,6	40	2929,6	40	3982,7	40	4982,7	40	1246,6

Приложение 1

Дата добавления: 2020-11-15; просмотров: 296; Мы поможем в написании вашей работы!

Поделиться с друзьями:

⇐ Предыдущая 123 Следующая ⇒

Мы поможем в написании ваших работ!