Лабораторна робота 9
Лабораторна робота № 9 Обробка та аналіз структурованих баз даних середовищі в MS Excel 2016.
Мета роботи: Ознайомлення з елементами інформаційного забезпечення (джерелами інформації) господарської діяльності сільськогосподарських підприємств України в середовищі MS Excel 2016 , опрацювання використання MS Excel 2016 для вирішення задач аналізу господарської діяльності цих підприємств.
Завдання: Ознайомлення з структурою баз даних та змістом довідників та оперативною інформацією баз даних (файл Джерела інформації для виконання лабораторних робіт), які сформовані згідно показників форми 50 сг в середовищі MS Excel (Звіт про основні економічні показники роботи сільськогосподарських підприємств ) та використання цієї інформації для вирішення задач , зв’язаних з аналізом економічної ефективності вирощування та виробництва продукції сільського господарства в MS Excel.
Хід виконання робіт:
Крок 1. Ознайомитися з Методичними вказівками до виконання завдань (дивись нижче).
Крок 2. Ознайомитися з файлами даних, які необхідно використати для виконання студентами завдань лабораторної роботи.
Крок 3. Відкрити файл з оперативною інформацією Т1_15, виконати фільтрацію даних в ньому, забезпечити відбір необхідної порції даних та її збереження в новому Excel-файлі.
Крок 4. Зробити копію змісту цього файлу на інший аркуш Excel-файлу, в якому необхідно залишити тільки ті показники, які необхідні для проведення аналізу. Виконати всі необхідні розрахунки та оформити результативні матеріали в зручному для перегляду та аналізу вигляді.
Крок 5. Зробити короткі висновки в Excel-файлу та зберегти проміжні та результативні матеріали у вигляді файлу MS Excel. Файл в середовищі MS Excel повинен містити:
- аркуш з відібраними записами після фільтрації,
- аркуш з відібраними показниками, розрахунками та висновками.
Форма подання результатів виконаної роботи –файл в середовищі MS Excel з виконаним завданням лабораторної з іменем: <прізвище студента> та відправленим через систему “elearn” на перевірку.
Необхідною умовою є захист виконаного студентом завдання, після якого викладач нараховує студенту відповідну кількість балів.
Критерії оцінювання:Максимальна кількість балів – 10, якщо все виконано вірно,
- 5-9 балів, якщо в програмах, створених студентом є незначні погрішності,
- 1 - 4 балів, якщо студент зробив суттєві помилки, або слабо орієнтується у своєму індивідуальному завданні,
- 0 балів, якщо студент не може пояснити хід виконання завдання.
Термін подання результатів – згідно календарного плану.
Методичні вказівки до виконання завдань.
Завдання 1. Ознайомитися з джерелами даних та їх використанням для виконання завдань лабораторних робіт 9-10 .
Зчитати з електронного курсу файл Джерела інформації для виконання лабораторних робіт, розархівувати та ознайомитись з його змістом.
Доступ до цієї інформації можна отримати за посиланням:
https://drive.google.com/drive/folders/1oR_HdCF-4LxVgsb5cNnwmYt5euWj4V6a?usp=drive_link
До складу інформаційного забезпечення входять бази даних (файли) в середовищі MS Excel, які можна розділити на дві категорії:
- перша: файли постійної інформації, так звані довідники, що служать для відбору та групування інформації по окремим ознакам, кодам;
- друга: файли оперативної інформації: це показники звітних матеріалів сільськогосподарських підприємств України, форма 50-сг (річна) - Звіт про основні економічні показники роботи сільськогосподарських підприємств.
Файли з постійною інформацією:
1. OBLZONA – довідник областей, дивись рис 1., в 1 рядку задані імена показників, інші рядки – значення цих показників, імена показників:
- ZONA – код зони,
- KOBL – код області,
- NAME – назва області.
Рис.1. Довідник областей (OBLZONA)
1. RAYONY - довідник районів, одна сторінка якого дивись рис 2., де:
Рис.2. Довідник районів (RAYONY).
OBL – код області,
RN – код району,
NAMEUKR – назва району.
1. OPF - довідник організаційно-правових форм господарювання підприємств, дивись рис. 3.:
Рис.3. Довідник організаційно-правових форм
господарювання підприємств (OPF).
KOD – код форми господарювання підприємства,
NAME – назва форми господарювання підприємства.
KAT - довідник категорій господарств, дивись рис.4.:
Рис.4. Довідник категорій підприємств (KAT).
KOD – код категорії господарства,
KAT – назва категорії господарства.
1. ZONA - довідник кліматичних зон України, дивись рис.5.:
Рис.5. Довідник кліматичних зон України (ZONA).
Файли оперативної інформації.
Містять економічні показники роботи сільськогосподарських підприємств (Форма № 50 сг) за поточний рік, які розподілені в 4 Excel-файлах: T1_15- T4_15, де
T1_15 – показники виробництва і реалізації сільськогосподарської продукції рослинництва;
T2_15 - показники виробництва і реалізації сільськогосподарської продукції тваринництва, рибництва, реалізація робіт і послуг;
T3_15– показники державної підтримки сільського господарства, витрати на основне виробництво, середньооблікова чисельність працівників, землекористування протягом звітного року тощо;
T4_15 – показники структури виробничої собівартості за видами сільськогосподарської продукції рослинництва і тваринництва.
Перший рядок файлів T1_15 - T4_15: імена показників, другий і всі наступні рядки –значення показників, один рядок відповідає інформації по одному господарству України, які можна відбирати по ключовим ознакам ( коду зони, області, району, категорії підприємства, форми господарювання ) або по іменам показників.
Перша сторінка файлу T1_15 дивись рис.6.:
Рис.6. Перша сторінка файлу T1_15(показники виробництва і реалізації сільськогосподарської продукції рослинництва).
Перший рядок файлу T1_15, як і інших файлів T2_15- T4_15 має однакове призначення: це імена показників. Перші 7 імен показників однакові для всіх файлів T1_15- T4_15, а їх значення співпадають з відповідними кодами файлів-довідників. Це такі показники:
GOD – рік за який проводилось обстеження,
ZONA – код зони до якої відноситься підприємство,
TER – код області даного підприємства,
RAN – код району підприємства,
NOMER – номер господарства в базі,
KAT – код категорії господарства,
OPF – код форми господарювання.
Інші клітини першого рядка – це імена показників господарської діяльності господарств Форми № 50-сг. Відкривши файл 50_sg (Звіт про основні економічні показники роботи сільськогосподарських підприємств), можна визначити ім’я любого показника файлів T1_15- T4_15.
Ім’я показника в файлах T1_15- T4_15 визначається наступним чином: наприклад, в файлі T1_15 показник Т0010_3 можна розшифрувати як (згідно текстового файлу 50_sg):
Т0 – постійний ідентифікатор імені,
010 – трьохсимвольний номер рядка, який відповідає: Продукція рослинництво, всього (текстовий файл 50_sg),
_3 – номер стовпчика для рядка 010, якому відповідає Виробнича собівартість, тис. грн., тобтоТ0010_3 – виробнича собівартість в тис. грн. продукції рослинництва, всього;
Т0060_5 – виробнича собівартість в тис. грн. реалізованої картоплі (показник знаходиться в T1_15 );
Т0180_6 – повна собівартість реалізованого молока, тис. грн. реалізованого молока (показник знаходиться в T2_15 ).
Завдання для контролю:
Визначити імена таблиці та показника:
- Площа посіву зернових та зернобобових культур?
- Виробнича собівартість вирощування озимої пшениці?
- Виручка від реалізації цукрового буряку?
- Середньорічне поголів’я свиней?
- Дійне стадо корів?
- Площа сільськогосподарських угідь господарства?
- Повна собівартість реалізованої продукції тваринництва.
Завдання 2. Зробити аналіз існуючого стану господарської діяльності об’єкту дослідження та результативні матеріали подати у вигляді таблиці, дивись таблиці 1.
Таблиця 1. Аналіз вирощування зернових та зернобобових в господарствах Яготинського району Київської області в 2015 році.
Київській області відповідає код -32 , файл OBLZONA (довідник областей) , Яготинському району Київській області відповідає код -255, файл RAJONY (довідник районів).
Для отримання результативної таблиці 1 необхідно:
- з файлу Т1_15 відібрати записи (підприємства), які відповідають умовам: TER = 32, RAN=255 тобто провести фільтрацію даних, вибравши опцію: фільтрувати – Фільтр, дивись рис.7.:
Рис.7. Фільтрація інформації в файлі Т1_15.
Задаємо умови для фільтрації для показників TER і RAN, дивись рис.8.:
Рис.8. Умови для виконання фільтрації інформації в файлі Т1_15.
В результаті будуть відібрані 24 підприємства Київської області Яготинського району ( рядок з іменами показників та наступні 24 записи, кожний з яких відповідає показникам одного підприємства).
Отримані дані копіюємо в новий Excel-файл, робимо копію відібраних записів на інший аркуш Excel-файлу, в якому необхідно залишити тільки ті показники, які необхідні для проведення аналізу. Це наступні показники:
- NOMER – номер господарства в файлі,
- Т0020_1 – площа посіву зернових та зернобобових в господарстві, га,
- Т0020_2 – валовий збір даної культури, ц,
- Т0020_3 – виробнича собівартість зібраної продукції, тис. грн.
В результаті отримуємо тільки ті дані, які необхідні для подальшого використання, дивись рис.9.:
Для подальшого використання залишились показники NOMER, Т0020_1,
Т0020_2 та Т0020_3. Нульові значення в окремих рядках свідчать про те, господарство не займається вирощуванням даної продукції ( в даному випадку вирощування зернових та зернобобових), ці рядки необхідно видалити з файлу.
Рис.9. відібрані показники файлу Т1_15.
Для розрахунку нових показників використовуємо відповідні формули:
=C2/B2 – урожайність в господарстві, ц. га,
=D2/C2*1000 – собівартість вирощування 1 ц. продукції грн.,
=SUM(B2:B23) – загальна площа посіву по господарствам району,
=SUM(C2:C23) – загальний валовий збір по району, ц,
=SUM(D2:D23) – загальні виробничі витрати на вирощування в районі,
=SUM(C2:C23)/ SUM(B2:B23) – середня урожайність в розрахунку на 1 господарство, ц,
=SUM(D2:D23)/ SUM(C2:C23) - середні витрати 1 ц продукції в розрахунку на 1 господарство, грн.
Після проведених розрахунків отримуємо результативні дані, дивись Таблицю 2.:
Таблиця 2. Аналіз виробництва зернових та зернобобових в господарствах Яготинського району Київської області в 2015 році.
* -в середньому на 1 господарство
Короткі висновки.
Як свідчать дані таблиці 2 урожайність зернових та зернобобових культур в 2015 р. в господарствах Яготинського району Київської області склала від 23.53 до 92.51 ц, в середньому по району 66.92 ц, суттєво відрізняється по господарствам і виробнича собівартість 1 ц в гривнях. Цей вид таблиць дозволяє визначити існуючий стан виробництва або вирощування певних видів продукції, порівнювати між собою результати окремих господарств, виявляти причини кращих або гірших результатів та робити відповідні висновки.
Завдання 3. Освоїти графічні можливості Ms Excel.
Використовуючи дані таблиці 3 показати динаміку зміни показників вирощування зернових та зернобобових в господарствах Яготинського району Київської області шляхом побудови стовпчикової діаграми.
Таблицю 3. Показники вирощування зернових та зернобобових в Яготинському району Київської області за 2013-2015 роки.
Для виконання завдання необхідно:
1. Перенести показники таблиці 3 в Excel-файл, рис.10.:
Рис.10. Показників вирощування зернових та
зернобобових для побудови діаграми.
1. Виділяємо діапазон клітин для побудови діаграми: В4:Е6.
2. Через головне меню активізуємо опції: Вставлення та Вставити об’ємну стовпчасту діаграму або гістограму , серед існуючих варіантів діаграм вибираємо Об’ємну стовпчасту діаграму , в результаті будується початковий варіант діаграми, рис.11.:
4. Внести зміни в елементи діаграм: - назва діаграми; - назви осей; - підписи даних; таблиця даних; сітки; легенди; розміри діаграми тощо. В результаті вибраних доповнень отримуємо діаграму, рис.12.:
Рис.12. Діаграма показників вирощування зернових та зернобобових в Яготонському району Київської області за 2013-2015 роки.
Дані рис.10. використовуємо для побудови графіку. Для цього виконаємо послідовність дій: через головне меню активізуємо опції Вставлення та Вставити лінійчату діаграму або діаграму з областями, серед існуючих варіантів вибираємо Графік з маркерами , в побудований графік вносимо доповнення та отримуємо кінцевий варіант, рис.13.:
Рис.13. Графік показників виробництва зернових та зернобобових в Яготонському району Київської області за 2013-2015 роки.
Завдання 4. Визначити спеціалізацію господарств Яготинському району Київської області в 2015 році та побудувати кругову діаграму.
Для визначення спеціалізації певного регіону використовуємо відношення величини грошових надходжень певної галузі до загальних по господарству в процентному відношенні, вихідні дані в таблиці 4.:
Для виконання завдання необхідно:
1. Перенести показники таблиці 4 в Excel-файл, рис.14.:
Рис.14. Спеціалізація господарств Яготинського району.
2. Виділяємо діапазон клітин для побудови діаграми: С4:Е6.
3. Через головне меню активізуємо опції: Вставлення та Вставити секторну або кільцеву діаграму , серед існуючих варіантів діаграм вибираємо Об’ємну секторну діаграму , в яку вносимо доповнення та отримуємо кінцевий варіант, рис.15.:
Рис.15 Кругова діаграма спеціалізації Яготинського району Київської області в 2015 році. (виручка від реалізації продукції: млн.грн.)
Як свідчать дані рис.1 господарства Яготинського району спеціалізуються на вирощуванні рослинницької продукції, доля якої складає 90.4 відсотка, 6.4 % складає доля тваринницьких галузей.
Результативні графічні матеріали розміщуємо на окремих аркушах Excel-файлу.
Шрифти
Розмір шрифта
Колір тексту
Колір тла
Кернінг шрифтів
Видимість картинок
Інтервал між літерами
Висота рядка
Виділити посилання