« Назад
ІНДИВІДУАЛЬНА РОБОТА
|
Тема:
|
Аналіз даних у середовищі MS Excel з використанням зведених таблиць
|
|
|
|
|
Мета роботи:
|
Створення, редагування та форматування зведених таблиць.
|
|
|
|
|
Завдання:
|
- Вивчити призначення та запуск Майстра зведених таблиць, основні поняття та обмеження.
- Освоїти технологію створення зведеної таблиці.
- Освоїти технологію редагування зведеної таблиці з використанням панелі інструментів Майстра зведених таблиць, контекстного меню, головного меню та технології Drag&Droup.
- Освоїти технологію групування даних та відображення підсумків у зведеній таблиці.
- Освоїти технологію керування відображенням даних зведеної таблиці.
- Освоїти технологію форматування даних зведеної таблиці та друку зведеної таблиці.
|
|
|
|
|
Тривалість:
|
Лабораторне заняття – 4 годин.
Самостійна робота - 4 години
|
|
|
|
|
Лабораторне завдання:
|
І. Виконати індивідуальне завдання
ІІ. Створити зведену таблицю, з використанням функції Группировка и структура
|
|
|
|
|
Звіт:
|
Файл з виконаним індивідуальним завданням
|
|
|
|
|
Контрольні питання:
|
Тести
|
|
|
|
ІНДИВІДУАЛЬНІ ЗАВДАННЯ
І. Варіант індивідуального завдання для виконання лабораторної роботи визначає викладач.
|
Варіанти
|
|
|
|
1
|
2
|
3
|
4
|
5
|
6
|
7
|
8
|
9
|
10
|
11
|
12
|
13
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Задача 1
На основі даних наведених у таблиці 1 та використовуючи Майстер зведених таблиць побудувати дві зведені таблиці для аналізу успішності на факультеті по курсам (макет таблиці наведен у табл. 2).
Задача 2
На основі даних, наведених у таблиці 1 та використовуючи Майстер зведених таблиць, побудувати зведену таблицю для аналізу успішності на факультеті по курсам у розрізі груп (макет таблиці наведен у табл. 3)
Задача 3
На основі даних наведених у таблиці 1 та використовуючи Майстер зведених таблиць побудувати зведену таблицю для аналізу успішності на факультеті по спеціалізаціям у розрізі груп (макет таблиці наведен у табл. 4)
Задача 4
На основі даних наведених у таблиці 5 та використовуючи Майстер зведених таблиць побудувати зведену таблицю для аналізу грошових доходів, витрат та заощаджень населення України (макет таблиці наведен у табл. 6)
Таблиця 5
|
Грошові доходи та витрати населення України у 1997 році7, млн. гри.
|
|
|
|
|
В Т.Ч.
|
|
Доходи
|
1996р.
|
1997р.
|
1 кв.97 р.
|
1 кв.98 р.
|
|
Оплата праці
|
23723
|
27340
|
5963
|
6150
|
|
Надходження виручки від продажу сіл ьгосп продукті в
|
1081
|
1493
|
266
|
306
|
|
Пенсії та допомоги
|
9783
|
10072
|
2341
|
2869
|
|
Доходи від продажу іноземної валюти
|
2693
|
6185
|
1014
|
1569
|
|
Інші доходи
|
3031
|
5674
|
1099
|
1158
|
|
Покупка товарів і оплата послуг
|
27450
|
33067
|
7132
|
7629
|
|
Обов'язкові платежі та добровільні внески
|
3918
|
6233
|
1280
|
1652
|
|
Інші витрати
|
229
|
491
|
81
|
136
|
|
Приріст заощаджень у вкладах та придбання цінних паперів
|
2436
|
1115
|
306
|
287
|
|
Витрати населення на придбання іноземної валюти
|
4928
|
7758
|
1528
|
2122
|
Задача 5
На основі даних наведених у таблиці 7 та використовуючи Майстер зведених таблиць побудувати зведену таблицю для аналізу проектів по яким укладені угоди з ЄБРР у розрізі секторів, наприклад “Фінанси” (макет таблиці наведен у табл. 8)
Таблиця 7
|
Проекти, щодо яких укладено прямі угоди з ЄБРР млн. екю за курсом від 31 грудня 1997 р.
|
|
Назва проекту
|
Сектор
|
Обсяг кредиту ЄБРР
|
Обсяг інвестицій ЄБРР
|
|
Українська хвиля
|
Комунікації
|
13,60
|
0
|
|
Оболонський пивзавод
|
Агробізнес
|
36,2
|
0
|
|
Кондитерська фабоіка "Світоч" Агробізнес
|
Агробізнес
|
5,4
|
1,8
|
|
ВАбанк
|
Фінанси
|
4,6
|
4
|
|
Торгово-фінансова програма України/Приватбанк
|
Фінанси
|
4,5
|
0
|
|
Торгово-фінансова програма України/Перший український міжнародний банк
|
Фінанси
|
9
|
0
|
|
Термінал добрив "Південний"
|
Транспорт
|
0
|
4,6
|
|
ІВЕКО-КРАЗ
|
Машинобудування
|
0
|
16,3
|
|
Київський міжнародний банк
|
Фінанси
|
0
|
1,8
|
|
Кредитна лінія для малих та середніх підприємств
|
Фінанси
|
109,7
|
|
|
Сільськогосподарський сервіс центр
|
Агробізнес
|
4,4
|
5
|
|
Фонд "Україна (плюс приріст капіталу)
|
Фінанси
|
0
|
6,1
|
|
Укррічфлот
|
Транспорт
|
7,5
|
0
|
|
Полтавська нафтова компанія
|
Нафтогазова промисловість
|
7,2
|
0
|
|
Газові лічильники
|
Енергетика
|
73
|
0
|
|
Модернізація Старобешівської ТЕС
|
Енергетика
|
102,5
|
0
|
|
ІТУР
|
Комунікації
|
48,1
|
0
|
|
Міжнародний аеропорт "Бориспіль"
|
Транспорт
|
4,6
|
0
|
|
Євробачення — ТБ України
|
Комунікації
|
0,9
|
|
Задача 6
На основі даних наведених у таблиці 9 та використовуючи Майстер зведених таблиць побудувати зведену таблицю для аналізу обсягу продажу доларів США по місяцям кожного квартала (макет таблиці наведен у табл. 10)
Таблиця 9
|
Обсяги продажу доларів США
|
|
Місяць
|
1996р.
|
1997р.
|
1998р.
|
|
Січень
|
332083
|
1 080 386
|
2243710
|
|
Лютий
|
377543
|
1 419 807
|
2 452 830
|
|
Березень
|
489826
|
1 577 602
|
2374133
|
|
Квітень
|
413590
|
1746398
|
2350275
|
|
Травень
|
535794
|
1 508 352
|
2 584 278
|
|
Червень
|
871510
|
1 734 522
|
3033755
|
|
Липень
|
901035
|
2 051 792
|
3 624 108
|
|
Серпень
|
1010515
|
2294292
|
2 973 142
|
|
Вересень
|
1 042 022
|
1 654 626
|
4 195 302
|
|
Жовтень
|
1 135 325
|
1 992 884
|
4364619
|
|
Листопад
|
1009800
|
1 789216
|
3382889
|
Задача 7
На основі даних наведених у таблиці 11 та використовуючи Майстер зведених таблиць побудувати зведену таблицю для аналізу обсягу продажу доларів США по місяцям кожного кварталу (макет таблиці наведен у табл. 12)
Таблиця 11
|
Розподіл населення за рівнем середньодушового сукупного доходу в 1996 році
|
|
|
Млн. чоловік
|
|
|
|
у тому числі
|
|
Доходи
|
усього
|
у міських поселеннях
|
у сільській місцевості
|
|
до 20,0
|
0,1
|
0
|
0,1
|
|
20,1—30,0
|
0,8
|
0,5
|
0,3
|
|
30,1-60,0
|
9,6
|
6,6
|
3
|
|
60,1—90,0
|
15,4
|
10,2
|
5,2
|
|
90,1—120,0
|
10,8
|
7,1
|
3,7
|
|
120,1-150,0
|
6,2
|
4,2
|
2
|
|
150,1—180,0
|
3,3
|
2,3
|
1
|
|
180,1—210,0
|
1.7
|
1,2
|
0.5
|
|
210,1—240,0
|
1
|
0,8
|
0,2
|
|
240,1-270,0
|
0,6
|
0,5
|
0,1
|
|
270,1-300,0
|
0.3
|
0,2
|
0.1
|
|
понад 300,0
|
1,3
|
1,1
|
0,2
|
Задача 8
На основі даних наведених у таблиці 13 та використовуючи Майстер зведених таблиць побудувати зведену таблицю для аналізу структури доходної частини зведеного бюджету Черкаськогї області по видам податків. (макет таблиці наведен у табл. 14)
Таблиця 13
|
Структура дохідної частини зведеного бюджету Черкаської області
|
|
в 1995—1997 рр. (тис. гри.).
|
|
|
Фактично надійшло
|
Фактично надійшло
|
Прогноз
|
|
Вид податку
|
1995 рік
|
1996 рік
|
1997 рік
|
|
Акцизний збір
|
4146
|
5894
|
29499
|
|
Власні та закріплені доходи
|
24781
|
52250
|
54420
|
|
Державне мито
|
1054
|
2025
|
3520
|
|
Дотації з державного бюджету
|
40957
|
|
91939
|
|
Збори та інші неподаткові доходи
|
3262
|
7103
|
10000
|
|
Крім того, дотації, передані бюджетам районів та міст
|
30247
|
17677
|
134397
|
|
Лісовий дохід
|
218
|
164
|
241
|
|
Місцеві податки та збори
|
3076
|
7203
|
10000
|
|
Надходження коштів від приватизації майна
|
1974
|
7544
|
2600
|
|
Надходження сум від перевищення розрахункового фонду споживання
|
57
|
1629
|
1091
|
|
ПДВ
|
131021
|
135208
|
|
|
Плата за воду
|
482
|
283
|
958
|
|
Плата за землю
|
10666
|
14764
|
19357
|
|
Плата за спецвикористання надр
|
44
|
43
|
115
|
|
Податок із власників транспортних засобів
|
1758
|
4390
|
5000
|
|
Податок із прибутку
|
89872
|
106589
|
118057
|
|
Податок на промисел
|
70
|
110
|
188
|
|
Прибутковий податок із громадян
|
19083
|
31943
|
81468
|
Задача 9
На основі даних наведених у таблиці 15 та використовуючи Майстер зведених таблиць побудувати зведену таблицю для аналізу забаргованності перед бюджетом адміністративно-територіальних утворень. (макет таблиці наведен у табл. 16)
Таблиця 15
|
Забаргованість перед бюджетом
|
|
(млн.грн.)
|
|
|
|
Заборгованість станом на:
|
|
№
|
Адміністративно-територіальні утворення
|
01.01.98
|
01.09.98
|
01.10.98
|
|
1
|
Автономна Республіка Крим
|
88,4
|
185,1
|
192,4
|
|
2
|
Вінницька
|
57,8
|
122,1
|
117,1
|
|
3
|
Волинська
|
28,3
|
55,9
|
54,7
|
|
4
|
Дніпропетровська
|
302,6
|
2213,3
|
2234,6
|
|
5
|
Донецька
|
189,2
|
806,7
|
827,8
|
|
6
|
Житомирська
|
26,1
|
51,2
|
56,8
|
|
7
|
Закарпатська
|
14,8
|
41,9
|
46,6
|
|
8
|
Запорізька
|
111,4
|
496,3
|
501,8
|
|
9
|
Івано-Франківська
|
60,6
|
114,8
|
126,7
|
|
10
|
Київська
|
194,5
|
422,2
|
424,3
|
|
11
|
Кіровоградська
|
39,9
|
122,1
|
115,6
|
|
12
|
Луганська
|
32,1
|
452,9
|
439,5
|
|
13
|
Львівська
|
51,3
|
268
|
256,6
|
|
14
|
,Миколаївська ,
|
52,3
|
234,9
|
268,3
|
|
15
|
Одеська
|
39,1
|
86,5
|
75,3
|
|
16
|
Полтавська
|
185,1
|
474,7
|
483,1
|
|
17
|
Рівненська
|
39,2
|
159,2
|
171
|
|
18
|
Сумська
|
25,3
|
222,4
|
222,7
|
|
19
|
Тернопільська
|
32,6
|
74,9
|
72,5
|
|
20
|
Харківська
|
59
|
477,5
|
476,9
|
|
21
|
Херсонська
|
39,8
|
79,1
|
76,1
|
|
22
|
Хмельницька
|
42,1
|
61,7
|
65,3
|
|
23
|
Черкаська
|
35,5
|
97,6
|
94,2
|
|
24
|
Чернівецька
|
22,4
|
42,7
|
42
|
|
25
|
Чернігівська
|
10,3
|
33,5
|
39,2
|
|
26
|
М,Київ
|
439,4
|
803,3
|
817,1
|
|
27
|
М,Севастополь
|
12,1
|
13,9
|
14,2
|
Задача 10
На основі даних наведених у таблиці 17 та використовуючи Майстер зведених таблиць побудувати зведену таблицю для аналізу індексу цін споживчого ринку та оптових цін промисловості України по півріччям та кварталам. (макет таблиці наведен у табл. 18)
Таблиця 17
|
ІНДЕКСИ ЦІН СПОЖИВЧОГО РИНКУ
|
|
ТА ОПТОВИХ ЦІН ПРОМИСЛОВОСТІ УКРАЇНИ
|
|
|
|
|
|
|
|
|
Індекс цін споживчого ринку
|
Індекс оптових цін
|
|
Місяць
|
1996
|
1997
|
1996
|
1997
|
|
січень
|
109,4
|
102,2
|
103,4
|
100,4
|
|
лютий
|
107,4
|
101,2
|
102,9
|
100,4
|
|
березень
|
103
|
100,1
|
102,9
|
100,6
|
|
квітень
|
102,4
|
100,8
|
101,5
|
100,9
|
|
травень,
|
100,7
|
100,8
|
100,8
|
100,3
|
|
червень
|
100,1
|
100,1
|
100,4
|
100,4
|
|
липень
|
100,1
|
100,1
|
100,6
|
100,4
|
|
серпень
|
105,7
|
100
|
100,4
|
100
|
|
вересень
|
102
|
101,2
|
101
|
100,1
|
|
жовтень
|
101,5
|
100,9
|
100,1
|
101,1
|
|
листопад
|
101,2
|
100,9
|
101,2
|
99,8
|
|
грудень
|
100,9
|
101,4
|
100,9
|
100,5
|
Таблиця 18
|
ІНДЕКСИ ЦІН СПОЖИВЧОГО РИНКУ
ТА ОПТОВИХ ЦІН ПРОМИСЛОВОСТІ УКРАЇНИ
|
|
|
|
Індекс цін споживчого ринку
|
Індекс оптових цін
|
|
|
|
Сумма по полю 1996
|
Сумма по полю 1997
|
Сумма по полю 1996
|
Сумма по полю 1997
|
|
1 полугодие
|
1 квартал
|
319,8
|
303,5
|
309,2
|
301,4
|
|
|
2 квартал
|
303,2
|
301,7
|
302,7
|
301,6
|
|
2 полугодие
|
2 полугодие
|
611,4
|
604,5
|
604,2
|
601,9
|
|
Общий итог
|
|
1234,4
|
1209,7
|
1216,1
|
1204,9
|
Задача 11
На основі даних наведених у таблиці 19 та використовуючи Майстер зведених таблиць побудувати зведену таблицю для аналізу обсягу продажу доларів США по місяцям кожного квартала (макет таблиці наведен у табл. 20)
Таблиця 19
|
Обсяги продажу доларів США
|
|
Місяць
|
1996р.
|
1997р.
|
1998р.
|
|
Січень
|
332083
|
1 080 386
|
2243710
|
|
Лютий
|
377543
|
1 419 807
|
2 452 830
|
|
Березень
|
489826
|
1 577 602
|
2374133
|
|
Квітень
|
413590
|
1746398
|
2350275
|
|
Травень
|
535794
|
1 508 352
|
2 584 278
|
|
Червень
|
871510
|
1 734 522
|
3033755
|
|
Липень
|
901035
|
2 051 792
|
3 624 108
|
|
Серпень
|
1010515
|
2294292
|
2 973 142
|
|
Вересень
|
1 042 022
|
1 654 626
|
4 195 302
|
|
Жовтень
|
1 135 325
|
1 992 884
|
4364619
|
|
Листопад
|
1009800
|
1 789216
|
3382889
|
Задача 12
На основі даних наведених у таблиці 21 та використовуючи Майстер зведених таблиць побудувати зведену таблицю для аналізу обсягу продажу доларів США по місяцям кожного кварталу (макет таблиці наведен у табл. 22)
Таблиця 21
|
Розподіл населення за рівнем середньодушового сукупного доходу в 1996 році
|
|
|
Млн. чоловік
|
|
|
|
у тому числі
|
|
Доходи
|
усього
|
у міських поселеннях
|
у сільській місцевості
|
|
до 20,0
|
0,1
|
0
|
0,1
|
|
20,1—30,0
|
0,8
|
0,5
|
0,3
|
|
30,1-60,0
|
9,6
|
6,6
|
3
|
|
60,1—90,0
|
15,4
|
10,2
|
5,2
|
|
90,1—120,0
|
10,8
|
7,1
|
3,7
|
|
120,1-150,0
|
6,2
|
4,2
|
2
|
|
150,1—180,0
|
3,3
|
2,3
|
1
|
|
180,1—210,0
|
1.7
|
1,2
|
0.5
|
|
210,1—240,0
|
1
|
0,8
|
0,2
|
|
240,1-270,0
|
0,6
|
0,5
|
0,1
|
|
270,1-300,0
|
0.3
|
0,2
|
0.1
|
|
понад 300,0
|
1,3
|
1,1
|
0,2
|
Задача 13
На основі даних наведених у таблиці 23 та використовуючи Мастер сводных таблиц побудувати зведену таблицю для анализу обсягів кредиту та інвестицій у розрізі секторів економіки. Структура зведеної таблиці наведена на рис.1.
|
Таб.23
|
|
Проекти, щодо яких укладено прямі угоди з ЄБРР
млн. екю за курсом від 31 грудня 1997 р.
|
|
Назва проекту
|
Сектор
|
Обсяг кредиту ЄБРР
|
Обсяг інвестицій ЄБРР
|
|
Українська хвиля
|
Комунікації
|
13,60
|
|
|
Оболонський пивзавод
|
Агробізнес
|
36,2
|
|
|
Кондитерська фабрика "Світоч" Агробізнес
|
Агробізнес
|
5,4
|
1,8
|
|
ВАбанк
|
Фінанси
|
4,6
|
4
|
|
Торгово-фінансова програма України/Приватбанк
|
Фінанси
|
4,5
|
|
|
Торгово-фінансова програма України/Перший український міжнародний банк
|
Фінанси
|
9
|
|
|
Термінал добрив "Південний"
|
Транспорт
|
|
4,6
|
|
ІВЕКО-КРАЗ
|
Машинобудування
|
|
16,3
|
|
Київський міжнародний банк
|
Фінанси
|
|
1,8
|
|
Кредитна лінія для малих та середніх підприємств
|
Фінанси
|
109,7
|
|
|
Сільськогосподарський сервіс центр
|
Агробізнес
|
4,4
|
5
|
|
Фонд "Україна (плюс приріст капіталу)
|
Фінанси
|
|
6,1
|
|
Укррічфлот
|
Транспорт
|
7,5
|
|
|
Полтавська нафтова компанія
|
Нафтогазова промисловість
|
7,2
|
|
|
Газові лічильники
|
Енергетика
|
73
|
|
|
Модернізація Старобешівської ТЕС
|
Енергетика
|
102,5
|
|
|
ІТУР
|
Комунікації
|
48,1
|
|
|
Міжнародний аеропорт "Бориспіль"
|
Транспорт
|
4,6
|
|
|
Євробачення — ТБ України
|
Комунікації
|
0,9
|
|
ІІ. На основі даних наведених у таблиці 1 та використовуючи Майстер зведених таблиць побудувати зведену таблицю для аналізу розміщення облігацій внутрішньої державної позики по півріччям, кварталам і місяцям. (макет таблиці наведен у табл. 2)
Таблиця 1
|
РЕЗУЛЬТАТИ ПРОВЕДЕННЯ АУКЩОНІВ
|
|
З РОЗМІЩЕННЯ ОБЛІГАЦІЙ ВНУТРІШНЬОЇ ДЕРЖАВНОЇ
|
|
ПОЗИКИ
|
|
|
|
|
|
|
Період та строки обігу
|
Кількість проведених аукціонів
|
Кількість розміщених облігацій,
|
Сума коштів, залучених до бюджету
|
|
січень
|
44
|
10138,8
|
766,6
|
|
лютий
|
35
|
7382,7
|
587,6
|
|
березень
|
55
|
7988,2
|
667,5
|
|
квітень
|
84
|
10096,8
|
794,6
|
|
травень
|
40
|
6090,7
|
490,7
|
|
червень
|
26
|
16219,1
|
1274,3
|
|
липень
|
40
|
13781,8
|
1088,5
|
|
серпень
|
36
|
20062,8
|
952,1
|
|
вересень
|
36
|
3352,7
|
275,1
|
|
жовтень
|
76
|
5030,1
|
397,6
|
|
листопад
|
57
|
3349,3
|
271,9
|
|
грудень
|
62
|
9591,3
|
755,3
|
З повагою ІЦ "KURSOVIKS"!
|
