- Рефераты на русском
- Математика
- Математическое моделирование
Математическое моделирование
«Математическое моделирование и решение задачи оптимизации управления»
Содержание:
1. Исходные данные……………………………………………………………..3
2. Многомерный статистический анализ……………………………………....4
2.1. Очистка информации от засорения………………………………………..4
2.2. Проверка закона распределения всех параметров………………………..9
2.3. Оценка тесноты связей…………………………………………………….13
3. Регрессионный анализ двумерной модели………………………………....14
4. Регрессионный анализ трехмерной модели………………………………..19
5. Решение задачи нелинейной оптимизации управления деятельностью предпри-ятия…………………………………………………………………….23
Использованная литература…………………………………………………...25
1. Исходные данные (вариант № 4)
№ п\п Y1 X8 X9 X13 X14 X17
1 9,26 1,23 0,23 47750 6,4 17,72
2 9,38 1,04 0,39 50391 7,8 18,39
3 12,1 1,8 0,43 43149 9,76 26,46
4 10,8 0,43 0,18 41089 7,9 22,37
5 9,35 0,88 0,15 14257 5,35 28,13
6 9,87 0,57 0,34 22661 9,9 17,55
7 8,17 1,72 0,38 52509 4,5 21,92
8 9,12 1,7 0,09 14903 4,88 19,52
9 5,88 0,84 0,14 25587 3,46 23,99
10 6,3 0,6 0,21 16821 3,6 21,76
11 6,22 0,82 0,42 19459 3,56 25,68
12 5,49 0,84 0,05 12973 5,65 18,13
13 6,5 0,67 0,29 50907 4,28 25,74
14 6,61 1,04 0,48 6920 8,85 21,21
15 4,32 0,66 0,41 5736 8,52 22,97
16 7,37 0,86 0,62 26705 7,19 16,38
17 7,02 0,79 0,56 20068 4,82 13,21
18 8,25 0,34 1,76 11487 5,46 14,48
19 8,15 1,6 1,31 32029 6,2 13,38
20 8,72 1,46 0,45 18946 4,25 13,69
21 6,64 1,27 0,5 28025 5,38 16,66
22 8,1 1,58 0,77 20968 5,88 15,06
23 5,52 0,68 1,2 11049 9,27 20,09
24 9,37 0,86 0,21 45893 4,36 15,98
25 13,1 1,98 0,25 99400 10,3 18,27
26 6,67 0,33 0,15 20719 4,69 14,42
27 5,68 0,45 0,66 36813 4,16 22,76
28 5,22 0,74 0,74 33956 3,13 15,41
29 10 0,03 0,32 17016 4,02 19,35
30 8,16 0,99 0,89 34873 5,23 16,83
31 3,78 0,24 0,23 11237 2,74 30,53
32 6,48 0,57 0,32 17306 3,1 17,98
33 10,4 1,22 0,54 39250 10,4 22,09
34 7,65 0,68 0,75 19074 5,65 18,29
35 8,77 1 0,16 18452 6,67 26,05
36 7 0,81 0,24 17500 5,91 26,2
37 11 1,27 0,59 7888 12 17,26
38 9,02 1,14 0,56 58947 8,3 18,83
39 13,2 1,89 0,63 94697 1,63 19,7
40 9,27 0,67 1,1 29626 8,94 16,87
41 6,7 0,96 0,39 11688 5,82 14,63
42 6,69 0,67 0,73 21955 4,8 22,17
43 9,42 0,98 0,28 12243 5,01 22,62
44 7,24 1,16 0,1 20193 4,12 6,44
45 5,39 0,54 0,68 20122 5,1 22,26
46 5,61 1,23 0,87 7612 3,49 19,13
47 5,59 0,78 0,49 27404 4,19 18,28
48 6,57 1,16 0,16 39648 5,01 28,23
49 6,54 4,44 0,85 43799 11,4 12,39
50 4,23 1,06 0,13 6235 7,67 11,64
51 5,22 2,13 0,49 11524 4,66 8,62
52 18 1,21 0,09 17309 4,3 21
53 11 2,2 0,79 22225 6,62 19,41
Где:
Y1 – производительность труда (среднегодовая выработка продукции на одного ра-ботника), тыс. грн.;
Х8 – премии и вознаграждения на одного работника, %;
Х9 - удельный вес потерь от брака, %;
Х13 – среднегодовой фонд заработной платы ППП, тыс. грн.;
Х14 - фондовооруженность труда, грн.;
Х17 – непроизводственные расходы.
2. Многомерный статистический анализ.
2.1. Очистка информации от засорения.
При статистическом анализе экономической информации принято считать, что эко-номические показатели подчиняются нормальному закону распределения. Однако на практике это не всегда верно. Наблюдаются отклонения как односторонние, так и двусто-ронние. Во избежание искажения значений характеристик распределения при обработке информации необходимо отчистить её от засорения случайными отклонениями. Метод выявления аномальных наблюдений и их удаления из совокупности при обработке мно-гомерной статистической информации может привести к отбрасыванию слишком большо-го количества точек наблюдения. Известны более четко обоснованные методы обнаруже-ния засорения: метод Смирнова-Граббса проверки максимального наблюдения, критерий Граббса для обнаружения одного экстремального наблюдения, критерий исключения не-скольких грубых ошибок как обобщение критерия Граббса. Все они применяются к упо-рядоченной совокупности (вариационному ряду):
Χ(1) ≤ Χ(2) ≤ …≤ Χ(N) (N ≤ 25)
Для проверки максимального и минимального значений на наличие грубой ошибки используются критерии:
При N > 25экстремальные значения могут быть проверены по критерию S:
Sрасч = (Χ1 – Χ)/δ*
Где: δ* - стандартное отклонение, определенное для всей выборки;
Χ1 – предполагаемый выброс.
Χ – среднее значение, определенное для всей выборки
При Sрасч
30 2,929
50 3,082
100 3,283
1000 3,884
Из наших исходных данных видно что объем выборки составляет 53 строки, значит Sкр = 3,283, т.е Sрасч не должно превышать 3,283.
Красным цветом в таблице выделены строки, которые мы не должны учитывать в последующих расчетах, синим - значит, Sрасчетное не превышает Sкритическое. Другими словами мы очистили таблицу от засорения.
Y1 № п\п Y1 № п\п X8 № п\п X8 № п\п
3,78 31 3,78 31 0,03 29 0,03 29
4,23 50 4,23 50 0,24 31 0,24 31
4,32 15 4,32 15 0,33 26 0,33 26
5,22 28 5,22 28 0,34 18 0,34 18
5,22 51 5,22 51 0,43 4 0,43 4
5,39 45 5,39 45 0,45 27 0,45 27
5,49 12 5,49 12 0,54 45 0,54 45
5,52 23 5,52 23 0,57 6 0,57 6
5,59 47 5,59 47 0,57 32 0,57 32
5,61 46 5,61 46 0,6 10 0,6 10
5,68 27 5,68 27 0,66 15 0,66 15
5,88 9 5,88 9 0,67 13 0,67 13
6,22 11 6,22 11 0,67 40 0,67 40
6,3 10 6,3 10 0,67 42 0,67 42
6,48 32 6,48 32 0,68 23 0,68 23
6,5 13 6,5 13 0,68 34 0,68 34
6,54 49 6,54 49 0,74 28 0,74 28
6,57 48 6,57 48 0,78 47 0,78 47
6,61 14 6,61 14 0,79 17 0,79 17
6,64 21 6,64 21 0,81 36 0,81 36
6,67 26 6,67 26 0,82 11 0,82 11
6,69 42 6,69 42 0,84 9 0,84 9
6,7 41 6,7 41 0,84 12 0,84 12
7 36 7 36 0,86 16 0,86 16
7,02 17 7,02 17 0,86 24 0,86 24
7,24 44 7,24 44 0,88 5 0,88 5
7,37 16 7,37 16 0,96 41 0,96 41
7,65 34 7,65 34 0,98 43 0,98 43
8,1 22 8,1 22 0,99 30 0,99 30
8,15 19 8,15 19 1 35 1 35
8,16 30 8,16 30 1,04 2 1,04 2
8,17 7 8,17 7 1,04 14 1,04 14
8,25 18 8,25 18 1,06 50 1,06 50
8,72 20 8,72 20 1,14 38 1,14 38
8,77 35 8,77 35 1,16 44 1,16 44
9,02 38 9,02 38 1,16 48 1,16 48
9,12 8 9,12 8 1,21 52 1,21 52
9,26 1 9,26 1 1,22 33 1,22 33
9,27 40 9,27 40 1,23 1 1,23 1
9,35 5 9,35 5 1,23 46 1,23 46
9,37 24 9,37 24 1,27 21 1,27 21
9,38 2 9,38 2 1,27 37 1,27 37
9,42 43 9,42 43 1,46 20 1,46 20
9,87 6 9,87 6 1,58 22 1,58 22
10 29 10 29 1,6 19 1,6 19
10,4 33 10,4 33 1,7 8 1,7 8
10,8 4 10,8 4 1,72 7 1,72 7
11 37 11 37 1,8 3 1,8 3
11 53 11 53 1,89 39 1,89 39
12,1 3 12,1 3 1,98 25 1,98 25
13,1 25 13,1 25 2,13 51 2,13 51
13,2 39 13,2 39 2,2 53 2,2 53
18 52 7,7713462 Ср. значе-ние 4,44 49 1,007115385 Ср. значе-ние
7,9643396 Ср. значе-ние 2,2100661 Станд. от-клон. 1,0718868 Ср. значе-ние 0,48908212 Станд. от-клон.
2,6008701 Станд. от-клон. 2,456331 Sрасчетное 0,6759838 Станд. от-клон. 2,439027246 Sрасчетное
3,8585782 Sрасчетное -1,8059849 Sрасчетное 4,9825356 Sрасчетное -1,997855463 Sрасчетное
X9 № п\п X9 № п\п X14 № п\п X17 № п\п
0,05 12 0,05 12 1,63 39 6,44 44
0,09 8 0,09 8 2,74 31 8,62 51
0,09 52 0,09 52 3,1 32 11,64 50
0,1 44 0,1 44 3,13 28 12,39 49
0,13 50 0,13 50 3,46 9 13,21 17
0,14 9 0,14 9 3,49 46 13,38 19
0,15 5 0,15 5 3,56 11 13,69 20
0,15 26 0,15 26 3,6 10 14,42 26
0,16 35 0,16 35 4,02 29 14,48 18
0,16 48 0,16 48 4,12 44 14,63 41
0,18 4 0,18 4 4,16 27 15,06 22
0,21 10 0,21 10 4,19 47 15,41 28
0,21 24 0,21 24 4,25 20 15,98 24
0,23 1 0,23 1 4,28 13 16,38 16
0,23 31 0,23 31 4,3 52 16,66 21
0,24 36 0,24 36 4,36 24 16,83 30
0,25 25 0,25 25 4,5 7 16,87 40
0,28 43 0,28 43 4,66 51 17,26 37
0,29 13 0,29 13 4,69 26 17,55 6
0,32 29 0,32 29 4,8 42 17,72 1
0,32 32 0,32 32 4,82 17 17,98 32
0,34 6 0,34 6 4,88 8 18,13 12
0,38 7 0,38 7 5,01 43 18,27 25
0,39 2 0,39 2 5,01 48 18,28 47
0,39 41 0,39 41 5,1 45 18,29 34
0,41 15 0,41 15 5,23 30 18,39 2
0,42 11 0,42 11 5,35 5 18,83 38
0,43 3 0,43 3 5,38 21 19,13 46
0,45 20 0,45 20 5,46 18 19,35 29
0,48 14 0,48 14 5,65 12 19,41 53
0,49 47 0,49 47 5,65 34 19,52 8
0,49 51 0,49 51 5,82 41 19,7 39
0,5 21 0,5 21 5,88 22 20,09 23
0,54 33 0,54 33 5,91 36 21 52
0,56 17 0,56 17 6,2 19 21,21 14
0,56 38 0,56 38 6,4 1 21,76 10
0,59 37 0,59 37 6,62 53 21,92 7
0,62 16 0,62 16 6,67 35 22,09 33
0,63 39 0,63 39 7,19 16 22,17 42
0,66 27 0,66 27 7,67 50 22,26 45
0,68 45 0,68 45 7,8 2 22,37 4
0,73 42 0,73 42 7,9 4 22,62 43
0,74 28 0,74 28 8,3 38 22,76 27
0,75 34 0,75 34 8,52 15 22,97 15
0,77 22 0,77 22 8,85 14 23,99 9
0,79 53 0,79 53 8,94 40 25,68 11
0,85 49 0,85 49 9,27 23 25,74 13
0,87 46 0,87 46 9,76 3 26,05 35
0,89 30 0,89 30 9,9 6 26,2 36
1,1 40 1,1 40 10,3 25 26,46 3
1,2 23 1,2 23 10,4 33 28,13 5
1,31 19 1,31 19 11,4 49 28,23 48
1,76 18 0,4613462 Ср. значе-ние 12 37 30,53 31
0,485849057 Ср. значе-ние 0,2973084 Станд. от-клон. 5,9675472 Ср. значе-ние 19,21 Ср. значе-ние
0,344257473 Станд. от-клон. 2,8544566 Sрасчетное 2,3804217 Станд. от-клон. 4,9419208 Станд. от-клон.
3,701156966 Sрасчетное -1,3835673 Sрасчетное 2,5341951 Sрасчетное 2,2906073 Sрасчетное
-1,822176 Sрасчетное -2,5840155 Sрасчетное
X13 № п\п X13 № п\п X13 № п\п
5736 15 5736 15 5736 15
6235 50 6235 50 6235 50
6920 14 6920 14 6920 14
7612 46 7612 46 7612 46
7888 37 7888 37 7888 37
11049 23 11049 23 11049 23
11237 31 11237 31 11237 31
11487 18 11487 18 11487 18
11524 51 11524 51 11524 51
11688 41 11688 41 11688 41
12243 43 12243 43 12243 43
12973 12 12973 12 12973 12
14257 5 14257 5 14257 5
14903 8 14903 8 14903 8
16821 10 16821 10 16821 10
17016 29 17016 29 17016 29
17306 32 17306 32 17306 32
17309 52 17309 52 17309 52
17500 36 17500 36 17500 36
18452 35 18452 35 18452 35
18946 20 18946 20 18946 20
19074 34 19074 34 19074 34
19459 11 19459 11 19459 11
20068 17 20068 17 20068 17
20122 45 20122 45 20122 45
20193 44 20193 44 20193 44
20719 26 20719 26 20719 26
20968 22 20968 22 20968 22
21955 42 21955 42 21955 42
22225 53 22225 53 22225 53
22661 6 22661 6 22661 6
25587 9 25587 9 25587 9
26705 16 26705 16 26705 16
27404 47 27404 47 27404 47
28025 21 28025 21 28025 21
29626 40 29626 40 29626 40
32029 19 32029 19 32029 19
33956 28 33956 28 33956 28
34873 30 34873 30 34873 30
36813 27 36813 27 36813 27
39250 33 39250 33 39250 33
39648 48 39648 48 39648 48
41089 4 41089 4 41089 4
43149 3 43149 3 43149 3
43799 49 43799 49 43799 49
45893 24 45893 24 45893 24
47750 1 47750 1 47750 1
50391 2 50391 2 50391 2
50907 13 50907 13 50907 13
52509 7 52509 7 52509 7
58947 38 58947 38 58947 38
94697 39 94697 39 24801,88235 Ср. значе-ние
99400 25 26146,019 Ср. значе-ние 13868,61356 Станд. от-клон.
27528,17 Ср. значе-ние 16808,204 Станд. от-клон. 2,462042618 Sрасчетное
19450,726 Станд. от-клон. 4,078424 Sрасчетное -1,374750422 Sрасчетное
3,6950718 Sрасчетное
2.2. Проверка закона распределения всех параметров.
Предварительный анализ статистических данных заключается в проверке соответст-вия их предположению о нормальном распределении параметров, для чего строится гис-тограмма, и определяются выборочные числовые характеристики. Для построения гисто-граммы необходимо выполнить такую последовательность действий:
- разместить на рабочем листе Excel статистические данные наблюдений,
- Сервис – Анализ данных – Гистограмма,
- в появившемся диалоговом окне Гистограмма ввести в поле Входные данные ин-тервал (диапазон) ячеек, содержащий исходные данные, и отметить поле Метки, если таб-лица данных имеет заголовки,
- ввести в поле Параметры выхода адрес ячейки, с которой должны размещаться вы-ходные данные (выходной интервал) и щелкнуть пункт Вывод графика,
- ОК
В нашем случае строим гистограмму по диапазону ячеек Y1, X8, Х9, X13, Х14, X17.
Полученные данные (после очистки)
№ п\п Y1 X8 X9 X13 X14 X17
1 9,26 1,23 0,23 47750 6,4 17,72
2 9,38 1,04 0,39 50391 7,8 18,39
3 12,1 1,8 0,43 43149 9,76 26,46
4 10,8 0,43 0,18 41089 7,9 22,37
5 9,35 0,88 0,15 14257 5,35 28,13
6 9,87 0,57 0,34 22661 9,9 17,55
7 8,17 1,72 0,38 52509 4,5 21,92
8 9,12 1,7 0,09 14903 4,88 19,52
9 5,88 0,84 0,14 25587 3,46 23,99
10 6,3 0,6 0,21 16821 3,6 21,76
11 6,22 0,82 0,42 19459 3,56 25,68
12 5,49 0,84 0,05 12973 5,65 18,13
13 6,5 0,67 0,29 50907 4,28 25,74
14 6,61 1,04 0,48 6920 8,85 21,21
15 4,32 0,66 0,41 5736 8,52 22,97
16 7,37 0,86 0,62 26705 7,19 16,38
17 7,02 0,79 0,56 20068 4,82 13,21
18 8,15 1,6 1,31 32029 6,2 13,38
19 8,72 1,46 0,45 18946 4,25 13,69
20 6,64 1,27 0,5 28025 5,38 16,66
21 8,1 1,58 0,77 20968 5,88 15,06
22 5,52 0,68 1,2 11049 9,27 20,09
23 9,37 0,86 0,21 45893 4,36 15,98
24 6,67 0,33 0,15 20719 4,69 14,42
25 5,68 0,45 0,66 36813 4,16 22,76
26 5,22 0,74 0,74 33956 3,13 15,41
27 10 0,03 0,32 17016 4,02 19,35
28 8,16 0,99 0,89 34873 5,23 16,83
29 3,78 0,24 0,23 11237 2,74 30,53
30 6,48 0,57 0,32 17306 3,1 17,98
31 10,4 1,22 0,54 39250 10,4 22,09
32 7,65 0,68 0,75 19074 5,65 18,29
33 8,77 1 0,16 18452 6,67 26,05
34 7 0,81 0,24 17500 5,91 26,2
35 11 1,27 0,59 7888 12 17,26
36 9,02 1,14 0,56 58947 8,3 18,83
37 9,27 0,67 1,1 29626 8,94 16,87
38 6,7 0,96 0,39 11688 5,82 14,63
39 6,69 0,67 0,73 21955 4,8 22,17
40 9,42 0,98 0,28 12243 5,01 22,62
41 7,24 1,16 0,1 20193 4,12 6,44
42 5,39 0,54 0,68 20122 5,1 22,26
43 5,61 1,23 0,87 7612 3,49 19,13
44 5,59 0,78 0,49 27404 4,19 18,28
45 6,57 1,16 0,16 39648 5,01 28,23
46 4,23 1,06 0,13 6235 7,67 11,64
47 5,22 2,13 0,49 11524 4,66 8,62
48 11 2,2 0,79 22225 6,62 19,41
Карман Частота
3,78 1
5,1666667 2
6,5533333 13
7,94 11
9,3266667 10
10,713333 7
Еще 4
Карман Частота
0,03 1
0,3916667 2
0,7533333 13
1,115 16
1,4766667 9
1,8383333 5
Еще 2
Карман Частота
0,05 1
0,26 14
0,47 12
0,68 11
0,89 7
1,1 1
Еще 2
Карман Частота
5736 1
14604,5 11
23473 17
32341,5 6
41210 6
50078,5 3
Еще 4
Карман Частота
2,74 1
4,2833333 12
5,8266667 16
7,37 7
8,9133333 6
10,456667 5
Еще 1
Карман Частота
6,44 1
10,455 1
14,47 5
18,485 16
22,5 13
26,515 9
Еще 3
Рисунок 1. Гистограмма.
Числовые характеристики для всех признаков оцениваются по выборке с помощью инструмента анализа Описательная статистика, вызов которого осуществляется аналогич-но:
- Сервис – Анализ данных – Описательная статистика
- в появившемся диалоговом окне Описательная статистика необходимо ввести та-ким же образом Входные данные и Параметры выхода,
и отметить поле Метки, если таблица данных имеет заголовки,
- ввести в поле Параметры выхода адрес ячейки, с которой должны размещаться вы-ходные данные (выходной интервал) и щелкнуть пункт Итоговая статистика,
- ОК
Результаты применения инструмента Описательная статистика к данным наблюде-ний по результативному признаку Y1, X8, Х9, X13, Х14, X17 приведены ниже (рис.2).
Y1 X8 X9
Среднее 7,56 Среднее 0,978 Среднее 0,5
Стандартная ошибка 0,29 Стандартная ошибка 0,066 Стандартная ошибка 0
Медиана 7,13 Медиана 0,87 Медиана 0,4
Мода 5,22 Мода 0,67 Мода 0,2
Стандартное от-клонение 2 Стандартное от-клонение 0,46 Стандартное от-клонение 0,3
Дисперсия вы-борки 3,99 Дисперсия вы-борки 0,212 Дисперсия вы-борки 0,1
Эксцесс -0,71 Эксцесс 0,581 Эксцесс 0,6
Асимметричность 0,24 Асимметричность 0,694 Асимметричность 0,9
Интервал 8,32 Интервал 2,17 Интервал 1,3
Минимум 3,78 Минимум 0,03 Минимум 0,1
Максимум 12,1 Максимум 2,2 Максимум 1,3
Сумма 363 Сумма 46,95 Сумма 22
Счет 48 Счет 48 Счет 48
X13 X14 X17
Среднее 24839,604 Среднее 5,8997917 Среднее 19,422708
Стандартная ошибка 1999,8375 Стандартная ошибка 0,3153143 Стандартная ошибка 0,7276789
Медиана 20456 Медиана 5,29 Медиана 18,98
Мода #Н/Д Мода 5,65 Мода #Н/Д
Стандартное от-клонение 13855,281 Стандартное от-клонение 2,1845614 Стандартное от-клонение 5,0415072
Дисперсия вы-борки 191968807 Дисперсия вы-борки 4,7723085 Дисперсия вы-борки 25,416795
Эксцесс -0,3409897 Эксцесс 0,0969695 Эксцесс 0,1498246
Асимметричность 0,7463842 Асимметричность 0,8758745 Асимметричность -0,1062726
Интервал 53211 Интервал 9,26 Интервал 24,09
Минимум 5736 Минимум 2,74 Минимум 6,44
Максимум 58947 Максимум 12 Максимум 30,53
Сумма 1192301 Сумма 283,19 Сумма 932,29
Счет 48 Счет 48 Счет 48
Рисунок 2. Описательная статистика Y1, X8, Х9, X13, Х14, X17.
Как видно, результаты Описательной статистики дают возможность оценить спра-ведливость предположения о нормальном распределении признаков. Y1, X8, X14 подчи-няются нормальному закону распределения, т.к. имеют небольшие величины эксцесса и асимметричности, что подтверждается еще и тем, что назначение медианы и моды у них почти сходится.
2.3. Оценка тесноты связей.
Предварительный анализ тесноты взаимосвязи параметров многомерной модели осуществляется по оценке корреляционной матрицы генеральной совокупности X по на-блюдениям. Для этого используется инструмент Анализ данных в соответствии со сле-дующим алгоритмом:
- Сервис – Анализ данных – Корреляция,
- в появившемся диалоговом окне Корреляция в соответствующие поля ввести с по-мощью мыши входные данные (вводим диапазон ячеек $B$2;$G$50) и параметры ввода, в поле Метки в первой строке ставим метку, Выходной интервал (то место где хотим помес-тить таблицу),
- после щелчка мышью по кнопке ОК на рабочем листе появится матрица, содержа-щая оценки парных коэффициентов корреляции (рис.3)
Y1 X8 X9 X13 X14 X17
Y1 1
X8 0,31596 1
X9 0,0088 0,15681 1
X13 0,3801 0,1135 0,02534 1
X14 0,48235 0,1786 0,22263 0,05348 1
X17 0,03196 -0,2641 -0,2167 0,1102 -0,0108 1
Рисунок 3. Анализ парной корреляции.
Отобрать для дальнейшего анализа пары переменных, имеющие наибольшие значе-ния парных коэффициентов корреляции (|rij| ≥ 0,4), учитывая, что, чем меньше коэффици-ент rij, тем слабее их связь. Такими парами в нашем варианте являются Y1 - X13; Y1 - X14; X9 - X14; X17 - X9; X8 - X17, причем признак X8, Х17 связаны с другими компонен-тами (X17, Х9) отрицательным коэффициентом корреляции. Это говорит об обратной за-висимости, что вполне естественно для трудоемкости единицы продукции и других пока-зателей.
Y1 = F(X13; X14)
X9 = F(X14)
X17 = F(X9)
X8 = F(X17)
Проверить значимость коэффициентов корреляции на уровне α = 0,05. Поскольку объём выборки равен 53, критическое значение rкр для всех пар будет одинаково и в соот-ветствии с таблицей Фишера – Иейтса rкр = rтабл (0,05;53) rкр, коэффициенты корре-ляции всех отобранных пар признаков значимо отличаются от нуля, что подтверждает связь между ними.
3. Регрессионный анализ двухмерной модели.
В среде Excel для двумерного случая линейной регрессии предусмотрено несколько инструментов: статистические функции (КОРРЕЛ, ЛИНЕЙН, ТЕНДЕНЦИЯ и др.); инст-румент Регрессия надстройки Пакет анализа; графические средства при работе с диаграм-мой – построение линии тренда.
С помощью Пакета анализа можно получить искомую информацию, следуя такому алгоритму:
- разместить на рабочем листе Excel в двух смежных столбцах с соответствующими заголовками статистические данные по двум признакам, подлежащим исследованию (на-пример, X8 – X17);
- Сервис – Анализ данных – Регрессия;
- в появившемся диалоговом окне Регрессия ввести входные данные в поля Входной интервал Y(X8), Входной интервал X(X17) и щелкнуть по полю Метки, чтобы заголовки не вошли в интервал данных;
- ввести параметры вывода в поле выходной интервал или новый рабочий лист или адрес ячейки, где хотим поместить таблицу. В нашем случае это новый лист (номер 5).
- для наглядности можно вывести Остатки, График подбора, График остатков;
- в поле График нормальной вероятности поставить метку.
- ОК.
Результат работы инструмента Регрессия приведен на рисунке 4. Итак, выборочное уравнение линейной регрессии X8 на X17 имеет вид:
X8/Х17 = - 0,753+ 0,089x17
Выходная таблица содержит коэффициент детерминации R2 = 0,954, что означает, что полученная модель приблизительно на 90% отражает зависимость среднегодового фонда заработной платы от среднегодовой численности.
Стандартная ошибка (отклонение результата) σ = 0,09898 означает, что 68% реаль-ных значений результирующего признака x8 находится в диапазоне ± 0,09898 от линии регрессии. Это следует из того, что условные распределения нормально распределенной совокупности при фиксировании различных подмножеств компонент являются нормаль-ными.
В разделе Дисперсионный анализ приведены значения таких величин:
df – число степеней свободы
SS – сумма квадратов отклонений
MS – дисперсия
F – расчетное значение F-критерия. Поскольку критическое значение критерия Фи-шера Fкр = 4,03 (m1 = 1; m2 = 48; α = 0,05) Fрасч = 969,196 > Fкр, и, следовательно с веро-ятностью 1-α = 0,95 гипотеза об отсутствии связи между рассматриваемыми признаками отвергается. Это означает, что уравнение в целом статистически значимо, т.е. хорошо со-ответствует данным наблюдений.
ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R 0,977081653
R-квадрат 0,954688556
Нормированный R-квадрат 0,953703525
Стандартная ошибка 0,098984052
Наблюдения 48
Дисперсионный анализ
df SS MS F Значи-мость F
Регрес-сия 1 9,49603049 9,496030494 969,1960729 1,48123E-32
Оста-ток 46 0,45070076 0,009797843
Итого 47 9,94673125
Коэф-фици-енты Стан-дартная ошибка t-стати-стика P-Значение Нижние 95% Верхние 95% Ниж-ние 95,0% Верх-ние 95,0%
Y-пере-сече-ние -0,753 0,05742996 -13,1215 3,69945E-17 -0,86917087 -0,63797005 -0,86917 -0,6379
X17 0,0891582 0,00286389 31,131914 1,48123E-32 0,08339359 0,09492299 0,083393 0,09492
Рисунок 4. Результаты регрессионного анализа.
Нижняя часть таблицы содержит такие сведения:
Коэффициенты – оценки параметров βj уравнения регрессий;
Стандартная ошибка стандартные отклонения βj;
t – статистика – расчетное значение. Таким образом, можно оценить значимость ко-эффициентов уравнения регрессии, сравнив расчетное значение
t – статистики с критическим значением, найденным по распределению Стьюдента при уровне значимости α = 0,05 и m = 50:tкр = 2,009. Поскольку |tрасч| > tкр для коэффи-циентов, то они являются статистическими при уровне доверительной вероятности 0,95.
Нижние 95% и Верхние 95% определяют нижние и верхние границы доверительных интервалов для коэффициентов уравнения регрессии при α=0,05. Поскольку доверитель-ные интервалы не содержат 0, а это подтверждает значимость коэффициентов уравнения регрессии.
Для получения линии регрессии и её уравнения в случае двумерной модели удобным инструментом Excel является добавление линии тренда к точечной диаграмме, построен-ной на значениях компонент системы двух заданных случайных величин как результатов наблюдения (см. рис.5).
Алгоритм содержит такие действия:
- разместить на рабочем столе Excel в двух смежных столбцах исходные данные;
- Вставка – Диаграмма – Точечная (первый вариант)
- Далее;
- на закладке Диапазон данных ввести диапазон, занимаемый всей таблицей, для чего выделить мышью оба столбца;
- на закладке Ряд ввести в поле Значения X диапазон значений независимой величи-ны, а в поле Значения Y диапазон значений величины, регрессию которой следует оце-нить;
- Далее – на закладке Заголовки ввести заголовки осей и диаграммы – Далее – ука-зать, где поместить диаграмму, особенно формат осей и надписи, для чего щелкнуть пра-вой кнопкой мыши по оси или надписи, и в появившемся маленьком диалоговом окне щелкнуть по пункту Формат оси (или надписи);
- в появившемся диалоговом окне Формат оси (или надписи) выбрать нужную за-кладку и внести необходимые изменения – ОК;
- откорректировать полученное корреляционное поле, исключив резко выделяющие-ся из общего множества отдельные точки;
- щелкнуть правой кнопкой мыши по любой точке диаграммы и в появившемся диа-логовом окне выбрать пункт меню Добавить линию тренда;
- в появившемся диалоговом окне на закладке Тип выбрать тип зависимости: линей-ный или полиномиальный (указать порядок приближения);
- щелкнуть по закладке Параметры и в появившемся после этого диалоговом окне щелкнуть пункты показывать уравнение на диаграмме и поместить на диаграмму величи-ну достоверности аппроксимации (R^2);
- записать уравнение регрессии, заменив y и x на имена результативного и факторно-го признаков соответственно и оценить значимость полученного уравнения с помощью R^2.
На рисунке 5 приведены две линии тренда – линейная и нелинейная линия тренда. Они имеют уравнение, т.е. оценку линии регрессии, такого вида:
Х8/Х17 = -6Е-05х417 + 0,0044х317 - 0,1094x2 + 1,0563x - 1,9388 (полиномиальный)
Причем коэффициент детерминации равен R2 =0,1313(полиномиальный)
Х8/Х17 = -0,0241х17 + 1,4462(линейный)
Причем коэффициент детерминации равен R2 = 0,0697
Как видим, предпочтительнее использовать полиномиальную зависимость, т.к. R2 немного выше, а значит, лучше согласуется со статистическими данными. Результаты для Х8 и Х17 приведены на рисунке 5. Точно такие же операции были проведены для других пар признаков.
X17 X8 Линейный Полиномиальный
17,72 1,23
18,39 1,04
26,46 1,8
22,37 0,43
28,13 0,88
17,55 0,57
21,92 1,72
19,52 1,7
23,99 0,84
21,76 0,6
25,68 0,82
18,13 0,84
25,74 0,67
21,21 1,04
22,97 0,66
16,38 0,86
13,21 0,79
13,38 1,6
13,69 1,46
16,66 1,27
15,06 1,58
20,09 0,68
15,98 0,86
14,42 0,33
22,76 0,45
15,41 0,74
19,35 0,03
16,83 0,99
30,53 0,24
17,98 0,57
22,09 1,22
18,29 0,68
26,05 1
26,2 0,81
17,26 1,27
18,83 1,14
16,87 0,67
14,63 0,96
22,17 0,67
22,62 0,98
6,44 1,16
22,26 0,54
19,13 1,23
18,28 0,78
28,23 1,16
11,64 1,06
8,62 2,13
19,41 2,2
Рисунок 5. Линии тренда.
4. Регрессионный анализ трехмерной модели
Для исследования статистической зависимости одного результативного признака от двух и более факторных признаков в Excel есть две возможности:
- инструмент Регрессия для случая линейной статистической зависимости;
- применение метода наименьших квадратов в случае зависимости любого вида.
Алгоритм применения инструмента Регрессия отличается от описанного выше для случая двумерной модели только количеством исходных данных, размещаемых на рабо-чем листе и соответственно диапазоном входных параметров, вводимом в диалоговом ок-не Регрессия. Выходные данные также отличаются только количеством информации при сохранении их смысла. На рис. 6 приведены результаты применения инструмента Регрес-сия к статистическим данным по признакам Х14 Х13 Y1.
Оценка линейной функции регрессии Y1 на Х13, Х14 имеет вид:
ỹ1 = 3,792 + 0,0000512х13 – 0,4235x14
Значение F – критерия Fрасч = 12,577, что больше Fкр = 3,18. Это значит, что оценка нормально согласуется с данными наблюдений. Это подтверждается и нормальным значе-нием коэффициента детерминации R2 = 0,358. Расчетные значения t – статистики для сво-бодного члена и коэффициента при Х14 больше t кр = 2,009, что подтверждает их значи-мость. Для коэффициента при Х13 tрасч близко к критическому значению, что ставит под сомнение его значимость (см. рис 6).
Оценка квадратичной функции регрессии Y1 на X13, X14, X132, X13X14, X142, име-ет вид:
ỹ1 = 4,609 + 0,0001х13 – 0,101х14 – 2,28E-09х132 + 0,00001х13х14 + 0,022х142.
Значение F – критерия Fрасч = 6,67, что больше Fкр = 3,18. Это значит, что оценка нормально согласуется с данными наблюдений. Это подтверждается и нормальным значе-нием коэффициента детерминации R2 = 0,44 (см. рис 7).
Проверка значимости полученной квадратичной оценки уравнения регрессии выпол-ним так. Сравним значения коэффициента детерминации R2 квадратичной и линейной регрессии. Поскольку коэффициент детерминации для случая квадратичной регрессии, превосходит коэффициент детерминации для случая линейной регрессии и имеет нор-мальное значение (0,44), делаем вывод, что квадратичная регрессия достаточно хорошо согласуется со статистическими данными
X13 X14 Y1
47750 6,4 9,26
50391 7,8 9,38
43149 9,76 12,1
41089 7,9 10,8
14257 5,35 9,35
22661 9,9 9,87
52509 4,5 8,17
14903 4,88 9,12
25587 3,46 5,88
16821 3,6 6,3
19459 3,56 6,22
12973 5,65 5,49
50907 4,28 6,5
6920 8,85 6,61
5736 8,52 4,32
26705 7,19 7,37
20068 4,82 7,02
32029 6,2 8,15
18946 4,25 8,72
28025 5,38 6,64
20968 5,88 8,1
11049 9,27 5,52
45893 4,36 9,37
20719 4,69 6,67
36813 4,16 5,68
33956 3,13 5,22
17016 4,02 10
34873 5,23 8,16
11237 2,74 3,78
17306 3,1 6,48
39250 10,4 10,4
19074 5,65 7,65
18452 6,67 8,77
17500 5,91 7
7888 12 11
58947 8,3 9,02
29626 8,94 9,27
11688 5,82 6,7
21955 4,8 6,69
12243 5,01 9,42
20193 4,12 7,24
20122 5,1 5,39
7612 3,49 5,61
27404 4,19 5,59
39648 5,01 6,57
6235 7,67 4,23
11524 4,66 5,22
22225 6,62 11
ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R 0,598794493
R-квадрат 0,358554845
Нормированный R-квадрат 0,330046172
Стандартная ошибка 1,634437267
Наблюдения 48
Дисперсионный анализ
df SS MS F Значимость F
Регрессия 2 67,19626 33,59813 12,57704 4,58236E-05
Остаток 45 120,2123 2,671385
Итого 47 187,4086
Коэффи-циенты Стан-дартная ошибка t-стати-стика P-Значе-ние Нижние 95% Верх-ние 95% Нижние 95,0% Верх-ние 95,0%
Y-пер-ние 3,792115 0,790625 4,796351 1,81E-05 2,199715 5,384515 2,199715 5,384515
X13 5,12E-05 1,72E-05 2,971831 0,004739 1,65E-05 8,59E-05 1,65E-05 8,59E-05
X14 0,423537 0,109289 3,87538 0,000343 0,203417 0,643657 0,203417 0,643657
Рис.6. Линейная регрессия трехмерной модели.
X13 X14 X132 X13X14 X142 Y1 P(x) ε ε2
47750 6,4 2,28E+09 305600 40,96 9,26 8,9475425 0,3124575 0,0976297
50391 7,8 2,539E+09 393049,8 60,84 9,38 9,6757872 -0,295787 0,08749
43149 9,76 1,862E+09 421134,24 95,2576 12,1 10,135342 1,9646584 3,8598826
41089 7,9 1,688E+09 324603,1 62,41 10,8 9,2419784 1,5580216 2,4274313
14257 5,35 203262049 76274,95 28,6225 9,35 6,7882462 2,5617538 6,5625826
22661 9,9 513520921 224343,9 98,01 9,87 9,1458656 0,7241344 0,5243706
52509 4,5 2,757E+09 236290,5 20,25 8,17 8,3862997 -0,216299 0,0467856
14903 4,88 222099409 72726,64 23,8144 9,12 6,622219 2,497781 6,2389101
25587 3,46 654694569 88531,02 11,9716 5,88 6,5676091 -0,687609 0,4728063
16821 3,6 282946041 60555,6 12,96 6,3 6,1781826 0,1218174 0,0148395
19459 3,56 378652681 69274,04 12,6736 6,22 6,2962775 -0,076277 0,0058183
12973 5,65 168298729 73297,45 31,9225 5,49 6,8496007 -1,359600 1,8485142
50907 4,28 2,592E+09 217881,96 18,3184 6,5 8,2110994 -1,711099 2,9278613
6920 8,85 47886400 61242 78,3225 6,61 7,8952946 -1,285294 1,6519822
5736 8,52 32901696 48870,72 72,5904 4,32 7,6948948 -3,374894 11,389915
26705 7,19 713157025 192008,95 51,6961 7,37 8,2048991 -0,834899 0,6970566
20068 4,82 402724624 96727,76 23,2324 7,02 6,8611989 0,1588011 0,0252178
32029 6,2 1,026E+09 198579,8 38,44 8,15 8,0580624 0,0919376 0,0084525
18946 4,25 358950916 80520,5 18,0625 8,72 6,5623069 2,1576931 4,6556395
28025 5,38 785400625 150774,5 28,9444 6,64 7,5057387 -0,865738 0,7495034
20968 5,88 439657024 123291,84 34,5744 8,1 7,3562949 0,7437051 0,5530973
11049 9,27 122080401 102424,23 85,9329 5,52 8,2845738 -2,764573 7,642868
45893 4,36 2,106E+09 200093,48 19,0096 9,37 7,9883213 1,3816787 1,9090359
20719 4,69 429276961 97172,11 21,9961 6,67 6,8394545 -0,169454 0,0287148
36813 4,16 1,355E+09 153142,08 17,3056 5,68 7,4387942 -1,75879 3,0933572
33956 3,13 1,153E+09 106282,28 9,7969 5,22 6,8562276 -1,636227 2,6772409
17016 4,02 289544256 68404,32 16,1604 10 6,3660802 3,6339198 13,205373
34873 5,23 1,216E+09 182385,79 27,3529 8,16 7,7927467 0,3672533 0,134875
11237 2,74 126270169 30789,38 7,5076 3,78 5,5280342 -1,748034 3,0556237
17306 3,1 299497636 53648,6 9,61 6,48 5,9912055 0,4887945 0,23892
39250 10,4 1,541E+09 408200 108,16 10,4 10,206861 0,1931389 0,0373026
19074 5,65 363817476 107768,1 31,9225 7,65 7,1619111 0,4880889 0,2382308
18452 6,67 340476304 123074,84 44,4889 8,77 7,5621515 1,2078485 1,458898
17500 5,91 306250000 103425 34,9281 7 7,1914762 -0,191476 0,0366631
7888 12 62220544 94656 144 11 9,2792132 1,7207868 2,9611073
58947 8,3 3,475E+09 489260,1 68,89 9,02 10,325573 -1,305573 1,7045214
29626 8,94 877699876 264856,44 79,9236 9,27 9,09574 0,17426 0,0303665
11688 5,82 136609344 68024,16 33,8724 6,7 6,855835 -0,155835 0,0242845
21955 4,8 482022025 105384 23,04 6,69 6,9493223 -0,259322 0,0672481
12243 5,01 149891049 61337,43 25,1001 9,42 6,5411226 2,8788774 8,287935
20193 4,12 407757249 83195,16 16,9744 7,24 6,5710718 0,6689282 0,447465
20122 5,1 404894884 102622,2 26,01 5,39 6,9825735 -1,592573 2,5362905
7612 3,49 57942544 26565,88 12,1801 5,61 5,6601768 -0,050176 0,0025177
27404 4,19 750979216 114822,76 17,5561 5,59 6,9698556 -1,379855 1,9040014
39648 5,01 1,572E+09 198636,48 25,1001 6,57 7,9439852 -1,373985 1,8878352
6235 7,67 38875225 47822,45 58,8289 4,23 7,3603715 -3,130371 9,7992255
11524 4,66 132802576 53701,84 21,7156 5,22 6,356054 -1,136054 1,2906187
22225 6,62 493950625 147129,5 43,8244 11 7,734111 3,265889 10,666031
Q = 120,21234
σ = 1,5825371
β0 β1 β2
3,7921248 5,119E-05 0,4236085
ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R 0,665312096
R-квадрат 0,442640185
Нормированный R-квадрат 0,376287827
Стандартная ошибка 1,577022646
Наблюдения 48
Дисперсионный анализ
df SS MS F Значимость F
Регрессия 5 82,95457 16,59091 6,671054 0,000118005
Остаток 42 104,454 2,487
Итого 47 187,4086
Коэф-циенты Станд. ошибка t-стат-ка P-Значение Нижние 95% Верхние 95% Нижние 95,0% Верхние 95,0%
Y-пер-ние 4,609773644 2,522524 1,827445 0,074744 -0,48089 9,700435 -0,48089 9,700435
X13 0,000111017 9,21E-05 1,205353 0,234814 -7,5E-05 0,000297 -7,5E-05 0,000297
X14 -0,101424415 0,656635 -0,15446 0,877986 -1,42657 1,22372 -1,42657 1,22372
X132 -2,28E-09 1,23E-09 -1,8517 0,071107 -4,8E-09 2,04E-10 -4,8E-09 2,04E-10
X13X14 1,16594E-05 7,47E-06 1,560697 0,1261 -3,4E-06 2,67E-05 -3,4E-06 2,67E-05
X142 0,02276189 0,044468 0,511876 0,611419 -0,06698 0,112501 -0,06698 0,112501
Рис.7. Квадратичная регрессия трехмерной модели.
5. Решение задачи нелинейной оптимизации управления деятельностью предприятия
По результатам корреляционного и регрессионного многомерного анализа показате-лей производственно-хозяйственной деятельности предприятий составляется математиче-ская модель.
Для нашего примера:
Y1 = 4,6 + 0,0001*х13 – 0,1*х14 – 2,3*10-9*х132 + 0,00001х13х14 + 0,022х142
R2 = 0,44
Х9 = - 0,009*х143 + 0,0182*х142 - 0,0825*х14 + 0,497
R2 = 0,0518
Х17 = - 128,52*х94 + 347,93*х93 - 309,87*х92 - 98,9*х9 + 11,494
R2 = 0,1336
Х8 = - 6E-05*х174 + 0,0044*х173 - 0,1094*х172 + 1,0563*х17 - 1,9388
R2 = 0,1313
0,03 < Х8 < 2,2
0,05 < Х9 < 1,31
5736 < Х13 < 58947
2,74 < Х14 < 12
6,44 < Х17 < 30,53
Ограничения составляются по минимальному и максимальному значению выборки соответствующего Х.
Для решения задачи нелинейной оптимизации следует воспользоваться надстройкой Excel Поиск решения. Алгоритм необходимых действий для приведенной математической модели:
1. На рабочем листе Excel расположить исходные данные (см.рис.8.).
2. В ячейки А1-Е1 записать имена управляемых переменных, в ячейку G1 – имя це-левой функции.
3. В ячейки А2 и Е2 ввести значения 1, как значения переменных, вошедших в це-левую функцию (при решении нелинейных задач не рекомендуется задавать начальные нулевые значения), значения остальных переменных можно оставлять нулевыми. После окончания поиска решения в ячейках А1-Е1 появятся оптимальные значения управляемых переменных, а в ячейке G2 – оптимальное значение целевой функции.
4. В ячейке А3-Е3 ввести нижние допустимые значения управляемых переменных, в ячейки А4-Е4 – верхние.
5. В ячейки В5, С5, Е5 ввести формулы зависимостей, накладывающих ограничения на значения управляемых переменных в соответствии с математической моделью и адре-сами (№ ячеек) переменных (рис.9).
6. В ячейку G2 ввести формулу зависимости целевой функции от управляемых пе-ременных (рис.9).
7. Вызвать Сервис – Поиск решения.
8. В диалоговом окне ввести необходимые данные. Для ввода Ограничений щелк-нуть по кнопке Добавить и в появившемся диалоговом окне ввести необходимые ссылки и знаки неравенств.
9. Выполнить.
10. Проанализировать полученные результаты и выбрать рекомендации по обеспече-нию оптимального управления.
Как видно из рис.8, оптимальное решение при данных ограничениях и зависимостях свелось к таким результатам:
X8 X9 X13 X14 X17 Y1
1,060816 0,05 5736 3,48357 15,70701 Искомые 5,3275 цф
0,03 0,05 5736 2,74 6,44 Нижняя гр.
2,2 1,31 58947 12 30,53 Верхняя гр.
1,060816 0,05 15,70701 Зависимости
X9=F(X14) X17=F(X9) X8=F(X17)
Рис. 8. Нелинейная оптимизация.
Максимальное значение индекса производительности труда (Y1 = 5,3275) достигает-ся при таких значениях признаков:
Х8 – премии и вознаграждения на одного работника, % = 1,060816
Х9 – удельный вес потерь от брака, % = 0,05
Х13 – среднегодовой фонд заработной платы ППП, тыс. грн. = 5736
Х14 – фондовооруженность труда, грн. = 3,48357
Х17 – непроизводственные расходы, % = 15,70701
Использованная литература:
«Математичне моделювання і розв*язок задач оптимального управління» (Методичні вка-зівки до вивчення Математичного моделювання в управлінні і виконання курсового прое-кту) / Укладач Цибрій Л. В., Дніпропетровськ: ПДАБА, 2003