Тема: Группировка данных посредством создания сводных таблиц
На основе базы данных муниципальных образований осуществить группировку данных по критерию «Инвестиции в основной капитал, млн. руб», создав сводную таблицу. Группировку данных по критерию «Инвестиции в основной капитал, млн. руб» осуществить с шагом 300,0 млн. руб.
Осуществить вторичную группировку по критерию «Численность населения, всего» с шагом 10000 чел.
Сформировать аналитическую группировку (табл. 2).
Таблица 1 – Основные показатели социально-экономического развития муниципальных образований Алтайского края
Инвестиции в основной капитал, млн. руб.
|
№ п/п
|
Ввод в действие жилых домов, общей площади, м2
|
Численность населения, всего
|
Численность населения трудоспособного возраста на 1 января, чел.
|
Естественный прирост (убыль)
|
Миграционный прирост населения, чел
|
Объем производства потребительских товаров, тыс. руб.
|
Продукция сельского хозяйства, млн руб.
|
Оборот розничной торговли, общественного питания, платных услуг, млн руб
|
108,6
|
1
|
1242
|
16678
|
9482
|
-35
|
-803
|
274628
|
2022,229
|
420,5
|
2243,2
|
1
|
10246
|
25690
|
14835
|
-32
|
108
|
300778
|
1298,163
|
2412,5
|
129,3
|
1
|
1049
|
10912
|
6139
|
-46
|
-331
|
81022
|
876,554
|
547,2
|
864,8
|
1
|
13139
|
34054
|
19638
|
-107
|
-235
|
1884454
|
2588,726
|
1765,5
|
432,2
|
1
|
2079
|
30720
|
17922
|
-31
|
-344
|
4389108
|
1277,069
|
1889,8
|
186,3
|
1
|
645
|
11996
|
6934
|
-50
|
-425
|
70810
|
713,255
|
423,8
|
68
|
1
|
1232
|
10096
|
5420
|
-67
|
-196
|
84133
|
925,002
|
435,4
|
119
|
1
|
1744
|
19615
|
11360
|
-28
|
-350
|
1147924
|
1387,301
|
1256,2
|
187,2
|
1
|
1305
|
14117
|
8021
|
-91
|
-332
|
439747
|
938,686
|
613,3
|
62,5
|
1
|
729
|
6295
|
3588
|
-56
|
-80
|
46889
|
467,94
|
312,4
|
300,6
|
1
|
1854
|
19266
|
10957
|
-53
|
-214
|
242482
|
1579,155
|
1288,9
|
311,6
|
1
|
1323
|
15044
|
8711
|
-56
|
-141
|
503935
|
777,823
|
605,6
|
233,7
|
1
|
1706
|
20023
|
12079
|
-42
|
-409
|
1100547
|
1499,939
|
420,5
|
753,6
|
1
|
3845
|
20930
|
11986
|
-1
|
-207
|
1244414
|
981,445
|
1118,0
|
527,1
|
1
|
4610
|
19697
|
11434
|
-21
|
270
|
2049705
|
5675,299
|
697,3
|
227,8
|
1
|
2106
|
14245
|
8234
|
-42
|
-368
|
810820
|
887,002
|
759,7
|
1029,1
|
1
|
628
|
11970
|
6677
|
-49
|
-202
|
893611
|
1425,039
|
360,6
|
954,1
|
1
|
3578
|
18212
|
10596
|
3
|
-366
|
1262219
|
1898,362
|
1140,8
|
751,4
|
1
|
2787
|
17834
|
10248
|
-78
|
-335
|
175433
|
1386,77
|
1031,7
|
222,8
|
1
|
2140
|
16201
|
8986
|
4
|
-55
|
682235
|
1251,11
|
699,4
|
260
|
1
|
1300
|
19156
|
11139
|
-136
|
-321
|
749743
|
1854,257
|
895,3
|
71,6
|
1
|
1118
|
11249
|
6297
|
-58
|
-283
|
72820
|
961,319
|
429,2
|
243,7
|
1
|
3002
|
23010
|
13603
|
-41
|
-169
|
475434
|
940,31
|
2428,9
|
406
|
1
|
1267
|
11026
|
6225
|
-89
|
-205
|
1008038
|
751,802
|
543,7
|
232,9
|
1
|
651
|
13862
|
7881
|
-40
|
-277
|
83315
|
1639,758
|
959,7
|
250,1
|
1
|
3129
|
29478
|
17077
|
-184
|
-673
|
627077
|
1372,82
|
1510,0
|
415,7
|
1
|
4329
|
23416
|
13377
|
-106
|
-281
|
693879
|
1597,323
|
1680,7
|
791,2
|
1
|
4610
|
21105
|
12151
|
-30
|
-172
|
1112328
|
1200,445
|
1187,1
|
161,4
|
1
|
1074
|
17637
|
10287
|
114
|
-285
|
675132
|
1776,93
|
837,4
|
125,7
|
1
|
1300
|
9911
|
5619
|
-65
|
-53
|
207181
|
1008,896
|
526,8
|
1058,2
|
1
|
7851
|
40286
|
23648
|
-32
|
0
|
3110296
|
3273,66
|
2356,5
|
138,4
|
1
|
897
|
13308
|
7539
|
-56
|
-219
|
268785
|
1146,83
|
638,2
|
1606,7
|
1
|
28335,3
|
50087
|
29792
|
-13
|
869
|
1128905
|
3 130,88
|
2878,7
|
217,2
|
1
|
1413
|
12415
|
7037
|
-65
|
-1
|
523098
|
1389,286
|
666,7
|
304,7
|
1
|
3020
|
24710
|
14126
|
-42
|
-168
|
1887598
|
1785,93
|
2443,4
|
858,7
|
1
|
2320
|
24511
|
13807
|
-78
|
-179
|
2293038
|
2282,45
|
1327,8
|
264,4
|
1
|
1507
|
20690
|
12082
|
-67
|
-391
|
513768
|
1697,84
|
1789,9
|
316
|
1
|
1374
|
13159
|
7493
|
-78
|
-144
|
226208
|
1671,48
|
684,5
|
1392,8
|
1
|
1961
|
24471
|
14351
|
-44
|
11
|
4078448
|
1721,834
|
848,9
|
166,3
|
1
|
1215
|
10172
|
5939
|
8
|
-7
|
42397
|
662,351
|
363,0
|
444,5
|
1
|
4803
|
23860
|
12980
|
-94
|
-22
|
476079
|
1699,97
|
1445,6
|
860,7
|
1
|
2008
|
16425
|
9072
|
-51
|
-61
|
1061548
|
2136,827
|
854,2
|
123,3
|
1
|
857
|
10634
|
6221
|
-31
|
-335
|
129652
|
836,59
|
645,5
|
128,4
|
1
|
603
|
8574
|
4849
|
-59
|
-206
|
59821
|
905,41
|
361,1
|
72,9
|
1
|
259
|
5088
|
2922
|
-5
|
-150
|
127438
|
572,12
|
172,0
|
134,9
|
1
|
913
|
10014
|
5725
|
8
|
-327
|
701045
|
824,166
|
608,4
|
1635,4
|
1
|
9834
|
46727
|
27118
|
-109
|
114
|
1764973
|
1706,52
|
3604,5
|
136,3
|
1
|
516
|
8462
|
4768
|
-24
|
-138
|
115394
|
1183,79
|
484,0
|
253,4
|
1
|
2335
|
23277
|
13324
|
-78
|
-277
|
1008859
|
2049,39
|
777,8
|
202,8
|
1
|
2049
|
14140
|
8236
|
9
|
-330
|
733493
|
1317,351
|
849,7
|
415,3
|
1
|
6396
|
24801
|
13943
|
-199
|
-283
|
573602
|
1668,30
|
1612,4
|
369,9
|
1
|
1114
|
15646
|
9135
|
-96
|
-269
|
268651
|
1357,76
|
674,3
|
137,4
|
1
|
2011,6
|
13875
|
8007
|
-41
|
-196
|
446016
|
571,39
|
487,2
|
168
|
1
|
1420
|
15298
|
8737
|
-103
|
-210
|
1160261
|
1473,92
|
825,7
|
179,3
|
1
|
523
|
13307
|
7317
|
-110
|
-285
|
191044
|
1231,16
|
661,2
|
262,6
|
1
|
2208
|
16365
|
9265
|
-39
|
-301
|
1219164
|
1585,39
|
1062,1
|
511,4
|
1
|
878
|
16361
|
8988
|
-75
|
-115
|
1431027
|
2058,683
|
686,0
|
214,3
|
1
|
709
|
12283
|
7109
|
-47
|
-168
|
124816
|
936,504
|
458,5
|
447,3
|
1
|
1248
|
13621
|
7869
|
-46
|
-153
|
335491
|
1482,31
|
489,7
|
385,7
|
1
|
2834
|
33199
|
19816
|
-106
|
-371
|
458218
|
2632,36
|
1252,0
|
Таблица 2.
В расчете на 1 муниципальное образование
|
Удельный вес в экономике муниципальных образований группы, %
|
численность населения в трудоспособном возрасте, чел.
|
миграционный прирост, чел.
|
естественный прирост, чел.
|
ввод в действие жилых домов, общей площади, м2
|
промышленность
|
сельское хозяйство
|
сфера услуг
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Алгоритм решения:
1. Копируем таблицу в MS Excel
2. Выбираем сервис в MS Excel: Вставка – Сводная таблица
3. В окне «Создание сводной таблицы» отмечаем диапазон (если курсор мыши находится в поле таблицы, то диапазон задается автоматически)
4. Для группировки исходных данных по критерию «Инвестиции в основной капитал, млн. руб» предварительно заполним макет сводной таблицы
|
Отмечаем все поля в верхнем поле. Автоматически они появляются в нижнем правом поле. Левое нижнее поле при этом остается пустым. Группировочный признак следует переместить в левое нижнее поле вручную при помощи мыши
|
5. Для группировки щелкнем правой кнопкой мыши по полю критерия «Инвестиции в основной капитал, млн. руб» (не в «шапке») и выберем из контекстного меню команду «Группировать»….
… а в следующем окне отразить шаг группировки 300,0 млн. руб. (в соответствии с заданием)
6. В результате получим группировку по критерию «Инвестиции в основной капитал, млн. руб.» с шагов в 300,0 млн. руб.
Полученная группировка нарушает требование построения статистических группировок относительно количества элементов в группе (должно быть не менее 5 элементов в группах). Впоследствии 4 группы районов с инвестициями в основной капитал свыше 962,5 млн. руб. следует сгруппировать в одну отдельную группу с общим количеством районов в группе на уровне шести.
7. Для построения вторичной группировки по критерию «Численность населения, всего» с шагом 10000 чел. следует вернуться в список полей сводной таблицы и из нижнего правового поля критерий «Численность населения, всего» мышью переместить в левое нижнее поле. В результате, в левом нижнем поле будет два критерия группировки:
«Инвестиции в основной капитал, млн. руб»
«Численность населения, всего»:
8. Для группировки щелкнем правой кнопкой мыши по полю критерия «Численность населения, всего» (не в «шапке») и выберем из контекстного меню команду «Группировать»1, а в следующем окне отразить шаг группировки 10000 чел. (в соответствии с заданием)
Получим поле:
9. Для определения численности населения в трудоспособном возрасте в расчете на 1 муниципальное образование (см. таблицу аналитической группировки) расчет соответствующего показателя осуществить путем деления значений по критерию «Численность населения трудоспособного возраста на 1 января, чел.» на значения по критерию «Сумма по полю № п/п».
Здесь возможны два варианта:
Варианты
|
Методика расчета
|
Комментарий
|
Используя математические функции
|
=С5/I5 (округляем до целого)
|
Не позволяет «протянуть» формулу вниз, тем самым автоматизировать методику расчета критерия по оставшимся группам.
В результате расчет приходится делать вручную по каждой группе
|
Используя встроенные функции сервиса «Работа со сводными таблицами», в частности сервис «Формулы» (мышь должна находиться в ячейках поля «Сводная таблица»)
|
 
Алгоритм приведен ниже
|
В окне «Вставка вычисляемого поля» в имени вместо «Поле1» следует набрать «Численность населения в трудоспособном возрасте в расчете на 1 муниципальное образование, чел.», а для расчета использовать поле «Формула».
Пошагово заполнение поля «Формула»:
Среди полей снизу выбрать критерий «Численность населения трудоспособного возраста на 1 января, чел.»
Нажать ниже «Добавить поле»
В поле «Формула» появится поле «=Численность…»
Набрать знак деления «/» в после формула
Среди полей снизу выбрать критерий «№ п/п»
Нажать ниже «Добавить поле»
Нажать внизу «ОК»
В сводной таблице будет осуществлен расчет искомого показателя. Если же он не отразился, следует проверить отмечен ли он в перечне полей
Аналогично заполняются оставшиеся поля таблицы аналитической группировки. Для расчета отраслевой структуры экономики муниципальных образований следует сначала определить общую стоимость валовой продукции по промышленности, сельскому хозяйству и сфере услуг, а затем уже определять удельный вес валовой продукции промышленности, сельского хозяйства и сферы услуг в общей стоимости валовой продукции.
1 Как вариант можно воспользоваться параметрами сводной таблицы «Группировка по выделенному»  |
Скачать 0.93 Mb.
