1
Лабораторна робота № 3_1
Тема.Технологія проведення економічного аналізу засобами MS Excel: аналіз БД
MS Excel за допомогою вбудованих функцій (підручник, стор.263-271;271-290;339-
343).
Порядок виконання роботи:
1. Створити таблиці (див. вар. на стор. 2-16 ).
2. Ввести вирази (формули) для розрахунку показника, суми та середнього значення.
3. Для заповнення порожнього стовпчика першої таблиці записати функцію ВПР
(VLOOKUP)
для вибирання значень з другої таблиці.
4. Виконати форматування таблиць (ФОРМАТ ЯЧЕЙКИ).
5. У першу таблицю додати стовпчик, в якому записати вираз для визначення коду товару(прізвища, коду палива, назви товару і т. ін.) , в котрого ціна (розцінка, оклад, кількість і т. ін.) більша( менша) за середню.
6. Виконати сортування 1-ї таблиці (див. варіанти завдань)
7. Застосувати функції СУММЕСЛИ , СЧЁТЕСЛИ, СРЗНАЧЕСЛИ,
СУММЕСЛИМН, СРЗНАЧЕСЛИМН, СЧЁТЕСЛИМН. (див. варіанти завдань).
8. Зробити проміжні підсумки
9. Побудувати зведену таблицю
Оформлення результатів роботи.
1) Створити звіт про роботу, описавши методи та функції, які було використано під час виконання завдань.
2) Відправити викладачу виконану роботу (файл, створений у середовищі
EXCEL) та звіт (файл, створений у середовищі MS WORD з титульним аркушем).
3)
Зберегти файл для наступних лабораторних робіт
2
Варіанти таблиць
Варіант 1. Розрахунок середньої вартості квартир по районах.
Середня вартість квартири розраховується як добуток загальної площі та середньої ціни за 1 кв. м. у відповідному районі.
Номер картки
Код району
Район
Кількість кімнат
Площа
Ціна за 1 кв. м вартість
1234 2
3 68 1200 1230 1
3 70 1250 1228 3
3 60 1100 1123 1
2 55 900 1627 3
3 70 1500 1220 3
4 100 1200 1012 2
1 54 950 1432 1
3 75 1300 1543 2
1 35 1000
Усього:
Сума
Середня
Код району
Район
1
Деснянський
2
Шевченківський
3
Дарницький
4
Дніпровський
6. Виконати сортування 1-ї таблиці спочатку за кодом району, потім за кількістю кімнат
7. Знайти
Середню ціну3 кімнатних квартир
Кількість записів про квартири у Шевченківському районі
Сумарну вартість квартир, з площею, більшою за середню
Середню площу 3-кімнатних квартир
Середню площу 3-кімнатних квартир в Дніпровському районі
3
Варіант 2.
Розрахунок балансу зовнішньої торгівлі України.
Баланс розраховується як різниця між експортом та імпортом.
Рік
Квартал
Код країни
Країна
Імпорт
Експорт
Баланс
2018 1
EA2 2649,3 1782,7 2018 1
E56 105,0 170,5 2018 1
AZ4 312,7 72,5 2018 1
E2 1156,7 1179,2 2018 1
АZ1 165,8 779,3 2018 1
A12 22,3 67,1 2018 1
AM3 206,6 328,3 2018 1
AM1 86,0 261,9 2018 2
EA2 2700,2 1853,2 2019 2
E56 104,3 180,3 2019 2
AZ4 320,7 82,2 2019 2
E2 1158,2 1221,2 2019 2
АZ1 166,3 865,3 2019 2
A12 23,1 75,1 2019 2
AM3 207,3 350,3 2019 2
AM1 88,9 89,8
Усього:
Середній
Сума
Код країни
Країна
EA2
Росія
E56
Беларусь
AZ4
Туркменія
E2
Франція
АZ1
Китай
A12
ЮАР
AM3
Бразилія
AM1
США
6. Виконати сортування 1-ї таблиці спочатку за роком, потім за назвою країни
7. Знайти
Середній обсяг імпорту за 2019 рік
Кількість записів про США
Сумарний баланс торгівлі з Росією
Кількість записів з балансом, більшим за середній
Середній обсяг імпорту за 2-1 квартал 2019 року
4
Варіант 3.
Розрахунок заробітку робітників.
Залежно від професії та відпрацьованого часу робітнику нараховується заробіток.
Окремо, на основі показників виробництва, робітники отримують премію. Загальний заробіток розраховується як сума заробітку та премії.
Місяць Табельний номер
ПІБ
Заробіток, грн.
Премія, грн.
Загальний заробіток,грн.
6 1230 11022 1110,2 6
1231 11223,25 1112,25 6
1232 9331,56 0
6 1233 10135,34 2125,15 6
1234 8428,45 0
7 1230 8248,12 1858,12 7
1231 1549,25 1244,25 7
1232 10133,46 1069,23 7
1233 10138,14 0
7 1234 12134,12 1324,28
Усього:
Середній
Сума
Табельний номер
ПІБ
1230 Власюк Н.В.
1231 Кравчук Л.М.
1232 Марчук В.О.
1233 Мороз О.Н.
1234 Увсюк А.І.
6. Виконати сортування 1-ї таблиці спочатку за табельним номером, потім за загальним заробітком
7. Знайти
Кількість виплат без премії
Сумарний загальний заробіток за 6 місяць
Кількість виплат з заробітком, більшим за середній
Середній загальний заробіток з премією, більшою 1800
Сумарний загальний заробіток за 6 місяць для тих працівників, хто одержав премію
5
Варіант 4.
Розрахунок вартості палива.
На ПЕЦ відділом обліку витрат та раціонального використання сировини розраховується потреба у різних видах палива на місяць. Ці розрахунки виконуються на основі нормативно-довідкової інформації, котра відображає скільки і якого палива необхідно для функціонування виробництва. Вартість розраховується як добуток обсягу та розцінки.
Місяць
Код палива
Розцінка Назва палива
Обсяг
Вартість
10
A826 56,56 5867 10
Y124 55,23 4356 10
B325 59,12 3124 10
P345 60,25 7654 10
K345 14,34 1974 11
P345 60,25 2654 11
A826 57 3000 11
Y124 55,23 6345 11
A826 56,56 7123
Усього:
Середня
Сума
Сума
Код палива
Назва палива
A826
Мазут
Y124
Бензин 76
B325
Бензин 92
P345
Бензин 95
K345
Вугілля
6. Виконати сортування 1-ї таблиці спочатку за кодом палива, потім за розцінкою
7. Знайти
Сумарний обсяг вугілля
Середню вартість мазуту
Кількість поставок палива за 10 місяць
Сумарний обсяг палива з розцінкою, меншою за середню
Сумарний обсяг вугілля за 11 місяць
6
Варіант 5. Розрахунок завантаження устаткування.
Завантаження устаткування обчислюється як множення кількості деталей, котрі було виготовлено на даному устаткуванні, на норму часу.
Дата
ПІБ
Код деталі
Назва деталі
Код устаткування
Норма часу
Кількість деталей
Завантаження устаткування, год.
12.02.18
Увсюк А.Т.
AA.1234
G432 243 243 12.02.18
Власюк Н.В.
BB.1234
U543 7584 2323 12.02.18
Мороз О.Н.
CC.1234
G534 476 7584 12.02.18
Марчук В.О.
AA.5432
U123 2323 456 12.02.18
Кравчук Л.М.
BB.1234
U543 7584 476 14.02.18
Увсюк А.Т.
AA.1234
H432 265 263 14.02.18
Кравчук Л.М.
AA.1234
H432 265 263 14.02.18
Власюк Н.В.
BB.1234
U543 7584 1543 14.02.18
Мороз О.Н.
CC.1234
G534 476 8679 14.02.18
Марчук В.О.
AA.5432
U123 2323 2653 14.02.18
Кравчук Л.М.
BB.1234
U543 7584 365
Усього:
Середня
Сума
Сума
Код деталі
Назва деталі
AA.1234
Гайка
BB.1234
Корпус
CC.1234
Дріт
AA.5432
Елемент1 6. Виконати сортування 1-ї таблиці спочатку за датою, потім за кодом деталі
7. Знайти
Сумарну кількість деталей з кодом AA.1234
Середній час завантаження устаткування на дату 12.02.18
Кількість записів з нормою часу, більшою за середню
Сумарний час завантаження устаткування з нормою часу, більшою 7000
Сумарну кількість деталей з кодом AA.1234 за 14.02.18
7
Варіант 6. Розрахунок дивіденду за акціями фірм. Дивіденд за акціями фірми розраховується таким чином: прибуток поділити на (кількість акцій помножити на номінал).
Рік
Код фірми
Назва фірми
Номінал
Кількість акцій
Прибуток
Дивіденд
2018
F521 2005 1243 200564 2018
F245 2245 2323 243565 2018
F123 432 1584 134456 2018
F654 24 1456 423567 2018
F156 1192 476 216534 2018
F347 2123 265 213456 2018
F178 1543 2543 124567 2018
F275 434 679 546755 2019
F521 2100 2345 500234 2019
F245 2504 6543 754234 2019
F123 546 1765 345732 2019
F654 2674 3564 245632
Усього:
Середній
Сума
Код фірми
Назва фірми
F123
Borland
F156
Microsoft
F178
Apple
F245
Megadata
F275
Nuntucket
F347
Ashton-Tate
F521
Fox Software
F654
Merx
6. Виконати сортування 1-ї таблиці спочатку за датою, потім за номіналом
7. Знайти
Сумарний обсяг дивідендів з номіналом, більшим за 2000
Середній прибуток за 2018 рік
Кількість записів про фірму з кодом F123
Сумарний обсяг прибутку для акцій, з номіналом меншим за середній
Середній прибуток за 2018 рік для акцій з номіналом, не меншим за 3000
8
Варіант 7. Розрахунок рентабельності виробництва.
Рентабельність виробництва розраховується на основі обліку виторгу від реалізації продукції, витрат на виробництво продукції та прибутку. Прибуток розраховується як різниця між виторгом та витратами; рентабельність – як ділення прибутку на витрати.
Місяць
Код виробу
Назва виробу
Виторг
Витрати на виробництво
Прибуток Рентабельність
9
C586-1 5126 2186 9
K546-2 756 367 9
K546-2 800 400 10
P423-2 1000 800 10
C586-1 2654 1256 10
K546-2 5436 2456 10
A432-2 8574 4145 10
P423-2 1543 633
Усього:
Сума
Сума
Середній
Код виробу Назва виробу
C586-1
Міксер
K546-2
М'ясорубка
A432-2
Блендер
P423-2
Пароварка
6. Виконати сортування 1-ї таблиці спочатку за датою, потім за кодом
7. Знайти
Сумарний прибуток для виробу з кодом
C586-1
Середню рентабельність за 10 місяць
Кількість записів з виторгом, більшим за 5000
Сумарний обсяг прибутку для виробів з рентабельністю, більшу за середню
Середню рентабельність за 10 місяць для виробу з кодом
P423-2
9
Варіант 8. Розрахунок коштів, необхідних для закупівлі будматеріалів.
Сума коштів, необхідних для закупівлі будматеріалів, розраховується на основі необхідної їх кількості та середньо-біржової ціни на ці матеріали.
Місяць
Код матеріалу
Кількість, тонн
Назва матеріалу
Ціна за тонну
Вартість матеріалів
10
C81-5 2500 27 12
C81-5 3253 30 10
A23-4 2345 45 10
B45-2 4354 64 12
B12-7 4785 37 10
B12-7 2143 37 12
A23-4 345 45 10
P35-3 1456 85 12
P35-3 2345 87 12
B45-2 243 64
Усього:
Сума
Середня
Сума
Код матеріалу
Назва матеріалу
C81-5
Цемент
A23-4
Крейда
B45-2
Клей
B12-7
Алебастр
P35-3
Вапно
6. Виконати сортування 1-ї таблиці спочатку за кодом, потім за кількістю тонн
7. Знайти
Сумарну вартість матеріалів з кодом
P35-3
Середню ціну за тону матеріалу з кодом
C81-5
Кількість записів за 12 місяць
Сумарну вартість матеріалів з ціною за тонну, більшу за середню
Сумарну вартість матеріалів з ціною за тонну, більшу за середню за 10 місяць
10
Варіант 9. Розрахунок завантаження устаткування в розрізі детале-операцій.
На основі нормативно-довідкової інформації, котра відображає, скільки часу потрібно на кожну операцію, розраховується завантаження устаткування в розрізі детале-операцій
(кількість деталей помножити на норму часу).
Місяць Назва місяця
Код деталі
Номер опера- ції
Кількість дет.-опер.
Код устат.
Норма часу
Час обробки, год.
11
AA.1234 1
100
C142 3,3 12
BB.1234 1
156
C234 1,2 11
AA.1234 2
153
C861 12,00 11
CC.1234 2
312
C123 1,5 12
BB.1234 2
712
C435 2,6 11
CC.1234 1
512
C861 11,1 12
KK.1234 1
215
C222 2,22 11
CC.1234 3
555
C435 6,5
Усього:
Середня
Сума
Місяць
Назва місяця
7
Липень
8
Серпень
9
Вересень
10
Жовтень
11
Листопад
12
Грудень
6. Виконати сортування 1-ї таблиці спочатку за кодом, потім за місяцем
7. Знайти
Сумарний час обробки деталей з кодом
AA.1234
Середню норму часу для операції 1
Кількість записів за 12 місяць
Сумарний час обробки з нормою часу, більшу за середню
Середню норму часу для операції 1 за 11 місяць
11
Варіант 10. Розрахунок розміру процентної виплати за всіма рахунками.
Розмір процентної виплати розраховується за такою формулою: (залишок/100)*процентна ставка.
Номер рахунку
Вид рахунку
Залишок на кінець року, тис. грн.
Процентна ставка
Процентна виплата
861245
Терміновий
750,45 861232
Терміновий
243,33 861231
Простий
1425,22 861247
Терміновий
34,34 861244
Простий
89,43 861241
Простий
214,4 861242
Простий
134,5 861230
Терміновий
145,8 861233
Терміновий
234,34 861246
Простий
124,5
Усього:
Середній
Сума
Вид рахунку
Процентна ставка
Терміновий
15
Простий
5 6. Виконати сортування 1-ї таблиці спочатку за номером рахунку, потім за залишком на кінець року
7. Знайти
Сумарну процентну виплату за терміновими рахунками
Кількість рахунків із залишком, більшим за середній
Кількість термінових рахунків
Сумарну процентну виплату із залишком на кінець року, меншу за 100 тис.
Сумарну процентну виплату за простими рахунками, для яких залишок на кінець року не менший за 200 тис.
12
Варіант 11. Розрахунок суми стипендії.
Залежно від успішності студенти вузу отримують стипендію, яка розраховується за такою формулою:
).
4
(
)
0
(
);
4
(
)
0
(
,
0
успішність
та
ість
заборгован
якщо
стипендія
успішність
та
ість
заборгован
якщо
стипендії
Виплата
Факуль тет
Курс
Група
Прізвище
Заборго ваність
Успішність
Стипендія
Виплата стипендії
ФЕУ
1 1
Петренко А.М.
1 3
ФЕУ
1 2
Шевченко Т.Г.
0 5
ФЕУ
2 1
Увсюк А.Т.
1 4
ФЕУ
1 2
Власюк Н.В.
0 5
ФМ
1 2
Мороз О.Н.
1 3
ФМ
1 1
Марчук В.О.
1 3
ФМ
2 1
Кравчук Л.М.
0 4
ФМ
2 2
Шарпов Т.К.
0 4
Усього:
Середня
Сума
Успішність Стипендія
3 0
5 1250 4
900 2
0 6. Виконати сортування 1-ї таблиці спочатку факультетом, потім за курсом
7. Знайти
Сумарну виплату стипендій для 2 курсу
Кількість студентів з відмінною успішністю
Середню виплату стипендій для студентів ФЕУ
Сумарну виплату стипендій для студентів з успішністю, більшою за середню
Середню виплату стипендій для студентів ФМ 2 курсу
13
Варіант 12. Розрахунок податку з обігу.
Сума податку з обігу розраховується як добуток обсягу реалізації продукції на ставку податку з обігу.
Місяць
Код продукції
Обсяг реалізації, тис. грн.
Назва продукції
Ставка податку,
%
Сума податку
6
K-825 157,5 20 5
K-825 243,7 20 6
L-432 32,6 60 6
W-432 165,75 60 5
M-412 254,5 22 6
N-564 21,8 25 6
F-712 154,7 45 6
T-123 534,43 28 5
P-154 321,8 30 6
M-412 354,2 22 6
P-154 655,4 30 5
K-825 300 20
Усього:
Сума
Середній
Сума
Код продукції
Назва продукції
K-825
Молоко
L-432
Цигарки
W-432
Горілка
M-412
Борошно
N-564
Цукор
F-712
Вино
T-123
Рис
P-154
Цукерки
6. Виконати сортування 1-ї таблиці спочатку за кодом продукції, потім за обсягом реалізації
7. Знайти
Сумарну суму податку для продукції з кодом
K-825
Кількість записів з податком, більшим за середній
Середню ставку податку у 5 місяці
Загальну суму податку для продукції з обсягом реалізації, більшим за 300 тис.
Сумарну суму податку для продукції з кодом
K-825 за 5 місяць
14
Варіант 13. Розрахунок суми заробітку працівників медицини.
Сума заробітку розраховують за такою формулою:
Місяць Табельний номер
ПІБ
Посада
Премія,
%
Оклад Сума заро- бітку
11 1230 15 5184 11 1231 20 6291 12 1236 10 4084 11 1233 10 3295 12 1232 10 3291 12 1233 10 5484 12 1230 45 3395 11 1237 20 4784 11 1235 10 4000 12 1231 20 4760
Усього:
Серед ній
Сума
Табельний номер
ПІБ
Посада
1230 Петрик А.М.
Терапевт
1231 Бойко Т.Г.
Хірург
1232 Увсюк А.Т.
Терапевт
1233 Власюк Н.В.
Окуліст
1234 Мороз О.Н.
Хірург
1235 Марчук В.О.
Терапевт
1236 Шимчук Л.М.
Окуліст
1237 Туз Т.К.
Терапевт
6. Виконати сортування 1-ї таблиці спочатку за табельним номером, потім за місяцем
7. Знайти
Середню суму заробітку терапевтів
Кількість працівників з премією у 10%
Середню ставку податку у 5 місяці
Загальну суму заробітку за 12 місяць
Загальну суму заробітку терапевтів за 12 місяць
15
Варіант 14. Розрахунок заробітку робітників при підрядній формі оплати праці. Заробіток розраховується шляхом множення кількості деталей на розцінку.
Місяць Табельний номер
ПІБ
Код деталі
Кількість деталей
Розцінка Заробіток
10 155
AA.1234 645 26 11 156
BB.1234 546 17,5 11 156
AA.1234 235 25,5 11 157
CC.1234 721 16,4 11 155
KK.1234 912 29,3 10 156
CC.1234 1253 18 11 155
PP.1234 824 19,5 11 157
CC.1234 453 17
Усього
Сума
Середня
Сума
Табельний номер
ПІБ
155 Шевченко Т.Г.
156 Лисенко Н.А.
157 Кирилюк А.Н.
6. Виконати сортування 1-ї таблиці спочатку за табельним номером, потім за розцінкою
7. Знайти
Середній заробіток за 10 місяць
Кількість записів з розцінкою, більшою за середню
Середню розцінку деталей з кодом
CC.1234
Загальну суму заробітку за 11 місяць
Середню розцінку деталей з кодом
CC.1234 за 11 місяць
16
Варіант 15. Розрахунок податку на прибуток.
Розмір податку залежить від виду діяльності підприємства і розраховується як відповідний процент від прибутку.
Місяць Підприєм ство
Вид діяльності
Прибуток, тис. грн..
Податок,
%
Податок, тис. грн..
12
Аліса
Посередницький
500 11
Аліса
Посередницький
435 12
Алекс
Виробничий
856 11
Алекс
Виробничий
432 12
Меркс
Торгівельний
454 12
Мрія
Наук.-виробничий
634 12
Обрій
Торгівельний
723 11
Техінфо
Виробничий
1765 12
Фенікс
Торгівельний
472 12
Оріон
Торгівельний
923
Усього:
Сума
Середній
Сума
Вид діяльності
Податок, %
Посередницький
50
Виробничий
35
Торгівельний
45
Наук.-виробничий
36 6. Виконати сортування 1-ї таблиці спочатку за підприємством, потім за місяцем
7. Знайти
Середню суму податку за 12 місяць
Сумарний прибуток підприємств з торгівельним податком
Кількість підприємств з виробничим податком
Загальну суму податку для підприємств з прибутком, меншим за середній
Сумарний прибуток підприємств з торгівельним податком за 11 місяць