Індивідуальне завдання 14 Індивідуальне завдання 14
А. У середовищі табличного процесора MS Excel на основі наведених нижче таблиць створити базу даних, що відображає діяльність кондитерської фабрики з реалізації виробленої продукції. Таблиці розмістити на окремих сторінках книги, назви яких повинні відповідати назвам розташованих на них таблиць. Виконати форматування полів таблиць відповідно до характеру розміщених у них даних. Заголовки таблиць виконати з використанням обмежувальних ліній та заливки кольором.
Таблиця 1
Асортимент
Код продукції |
Назва продукції |
Код типу продукції |
KPROD |
NAME |
K_TPR |
12 |
«Пташка» |
101 |
15 |
«Молочна» |
101 |
18 |
«Театральна» |
101 |
25 |
«Хід королеви» |
202 |
26 |
«Мулатка» |
202 |
29 |
«Ромашка» |
202 |
32 |
«Чайка» |
303 |
33 |
«Оленка» |
303 |
36 |
«Гвардейский» |
303 |
Таблиця 2
Виробництво за зміну
Код продукції |
№ цеху |
Собівартість, грн |
|
||
KPROD |
KCEX |
COBIV |
12 |
1 |
5,02 |
15 |
1 |
4,69 |
18 |
1 |
3,85 |
25 |
2 |
5,89 |
26 |
2 |
6,25 |
29 |
2 |
7,01 |
32 |
3 |
1,52 |
33 |
3 |
1,48 |
36 |
3 |
1,63 |
Таблиця 3
Продукція
Код типу продукції |
Тип продукції |
K_TPR |
TUP_PROD |
101 |
Крамель |
202 |
Цукерки |
303 |
Шоколад |
Таблиця 4
Технологи
№ цеху |
Технолог цеху |
||
Прізвище |
Ім’я |
По батькові |
|
KCEX |
name |
ima |
po bat |
1 |
Проценко |
Іван |
Семенович |
2 |
Кравченко |
Кирило |
Сергійович |
3 |
Жовніров |
Юрій |
Петрович |
Таблиця 5
Реалізація продукції
Код продукції |
Кількість продукції |
Дата реалізації |
Дата сплати |
KPROD |
KIL |
DATAOPL |
DATAPROPL |
12 |
100 |
11.04.05 |
12.04.05 |
15 |
85 |
15.04.05 |
26.04.05 |
18 |
74 |
19.04.05 |
20.04.05 |
26 |
65 |
23.04.05 |
24.04.05 |
25 |
85 |
27.04.05 |
28.04.05 |
29 |
49 |
01.05.05 |
|
32 |
100 |
05.05.05 |
06.05.05 |
33 |
125 |
09.05.05 |
15.05.05 |
36 |
160 |
13.05.05 |
14.05.05 |
12 |
125 |
17.05.05 |
18.05.05 |
15 |
135 |
21.05.05 |
22.05.05 |
18 |
98 |
25.05.05 |
26.05.05 |
26 |
76 |
29.05.05 |
30.05.05 |
25 |
102 |
02.06.05 |
|
29 |
165 |
06.06.05 |
07.06.05 |
32 |
102 |
10.06.05 |
18.06.05 |
33 |
118 |
14.06.05 |
25.06.05 |
36 |
92 |
18.06.05 |
19.06.05 |
12 |
58 |
22.06.05 |
23.06.05 |
15 |
56 |
26.06.05 |
27.06.05 |
18 |
113 |
30.06.05 |
01.07.05 |
26 |
118 |
05.07.05 |
15.07.05 |
25 |
129 |
08.07.05 |
09.07.05 |
29 |
201 |
12.07.05 |
|
32 |
203 |
16.07.05 |
27.07.05 |
33 |
90 |
20.07.05 |
21.07.05 |
І. Створити на окремих аркушах книги табличного процесора MS Excel два вихідних документи, використовуючи вихідні таблиці. Для їх створення необхідно виконати такі дії:
Ввести назву таблиці та сформувати «шапку»:
для першого документа назва таблиці – «Реалізація продукції кондитерської фабрики», назви стовпців: Повна назва продукції, Кількість продукції, Собівартість, Термін сплати, Прогноз ціни, Попит, Реалізація. Елементи поля Термін сплати розраховується як різниця Дата сплати та Дата реалізації. Поле Прогноз ціни – розраховуються як добуток Собівартості на коефіцієнт. Якщо Кількість виготовленої продукції не перевищує 20, то дані поля Собівартість перемножуються на коефіцієнт 1,27, в іншому випадку дані поля Собівартість збільшуються на 1,25. У поле Попит заноситься словосполучення Користується попитом, якщо поле Термін сплати не перевищує 3, в іншому випадку – словосполучення Не користуються попитом. Розрахункове поле Реалізація обчислюється як добуток полів Кількість продукції та Прогноз ціни.
для другого документа назва таблиці «Аналіз виробництва продукції в розрізі цехів» назви стовпців: Номер цеху, Технолог цеху, Назва продукції, Прогноз ціни, Попит, Вартість від реалізації, Дата реалізації, Преміальні. Елементи поля Технолог цеху будуються на основі конкатенації поля Прізвище та першої літери полів Ім’я та По батькові. Розрахункові поля Прогноз ціни та Попит будуються аналогічно полям Прогноз ціни та Попит, що описано у попередньому пункті. Поле Вартість від реалізації обчислюється як добуток поля Прогноз ціни на поле Кількість продукції. Поле Преміальні розраховується за таким правилом: поле Вартість від реалізації збільшується на коефіцієнт 0,05, якщо у полі Попит стоїть словосполучення Користується попитом, в іншому випадку заноситься значення 0.
Використовуючи прямі посилання, формули та функції, сформувати перший рядок даних вихідного документа. Для цього необхідно скористатися такими правилами:
у комірки, ідентифікатори яких збігаються з ідентифікаторами даних таблиці «Реалізація продукції» ввести прямі посилання (використати відносну адресацію);
дані клітини, ідентифікатори яких збігаються з ідентифікаторами таблиці «Асортимент» та «Виробництво за зміну» одержати, використовуючи функцію ВПР(.). Вміти виконувати такі функції пошуку: ПРОСМОТР(.) та ИНДЕКС(.) у поєднанні з ПОИСКПОЗ(.);
дані клітини ідентифікатори яких збігаються з ідентифікаторами таблиць «Продукція» та «Технологи» одержати, використовуючи вкладену функцію ВПР(ВПР(.));
дані стовпців Прогноз ціни, Попит та Преміальні одержати, скориставшись функцією ЕСЛИ(.) та формулами з відносними посиланнями.
дані стовпця Повна назва продукції сформулювати, скориставшись функціями СЦЕПИТЬ(.), ВПР(ВПР(.)) та ВПР(.).
дані стовпця Технолог цеху формують функції СЦЕПИТЬ(.), ВПР(ВПР(.)) та ЛЕВСИМВ(.).
Використовуючи автозаповнення, сформувати повністю вихідний документ. Вихідний документ повинен містити стільки рядків з даними, скільки їх містить таблиця «Реалізація продукції».
У вихідній таблиці створити рядки з проміжними та підсумковими результатами (перед цим необхідно скопіювати таблицю вихідного документа на окремий аркуш книги табличного процесора MS Excel).
для першого вихідного документа провести сортування та групування по полю Повна назва продукції. Проміжні та підсумкові результати додати для полів Кількість продукції та Реалізація.
для другого вихідного документа дані відсортувати та згрупувати по полю Номер цеху. Проміжні та підсумкові результати додати для полів Вартість від реалізації та Преміальні.
Виконати оформлення таблиць лініями та кольором.
ІІ. Використовуючи Майстер діаграм:
на основі підсумкових даних документа «Аналіз виробництва продукції в розрізі цехів» створити кругову діаграму об’ємного типу, яка відображає обсяги реалізації продукції по цехах у вартісному вимірі. На діаграмі показати відсоткові значення обсягів реалізації та відокремити від загального масиву сегмент з найбільшим обсягам реалізації;
на основі підсумкових даних документа – «Реалізація продукції кондитерської фабрики» створити стовпчикову діаграму (гістограму) з двома осями, що відображає обсяги реалізації кожного виду продукції у кількісному та вартісному вимірах (реалізація). На діаграмі показати назви осей та розмірності прив’язаних до них даних. Позначити стовпчик найбільш вдалого по обсягу реалізації виробу значенням обсягу реалізації та вставити в нього графічний об’єкт (рисунок).
III. Виконати аналіз даних першої вихідної таблиці.
Використовуючи Автофільтр, відобразити дані по Повній назві продукції, яка починається зі слова «Карамель».
Використовуючи Автофільтр, відобразити дані по всій продукції «Шоколад», кількість якої більша за 123.
Використовуючи Автофільтр, відобразити дані по продукції, яка користується Попитом у травні.
Виконати пункти 1–2, застосувавши Розширений фільтр. Результати фільтрації Розширеного фільтру відобразити у окремих таблицях.
Використовуючи Розширений фільтр з обчислювальним критерієм, відобразити інформацію по продукції, кількість якої перевищує мінімальну кількість по «Цукерках». Для побудови критерію фільтрації скористатися функцією МИН(.). Результати фільтрації подати у вигляді таблиць з такими полями: Повна назва продукції, Кількість продукції, Собівартість.
ІV. На окремому аркуші книги табличного процесора MS Excel виконати аналіз даних за допомогою Майстра зведених таблиць (команда Данные/Сводная таблица). Зведену таблицю побудувати на основі даних другої вихідної таблиці без проміжних та підсумкових результатів.
Сформувати макет зведеної таблиці:
Поля Дата реалізації та Назва продукції перенести у область рядків.
Поле Технолог цеху перенести у область сторінки.
Поля Прогноз ціни та Вартість від реалізації перенести у область даних. Для поля Прогноз ціни знайти середнє значення кожної групи, а для поля Вартість від реалізації − максимальне значення.
Згрупувати по місяцях дані поля Дата реалізації.
Б. За допомогою системи управління базами даних MS Access створити програмне забезпечення, що відображає діяльність оптової торговельної бази з реалізації кондитерських виробів.
І. Розробити на основі індивідуального завдання концептуальну, логічну та фізичну модель предметної області (таблиці предметної області наведені в завданні А).
ІІ. Створити власну базу даних.
У режимі Конструктор визначити структуру кожної з таблиць бази даних відповідно до варіанта завдання таким чином:
Ввести поля для кожної таблиці.
Для кожного поля вказати тип даних.
Визначити властивості полів та ключові поля.
Створити схему даних власної бази даних. Встановити зв’язки між таблицями.
Ввести дані в таблиці.
ІІІ. Побудова запитів для відбору, пошуку та формування інформаційної бази.
На основі запитів на вибірку побудувати динамічний набір записів, що містять:
Інформацію про виробництво шоколадних і карамельних виробів, собівартість яких лежить у межах [1,5; 3,5] грн. Динамічний набір записів повинен містити поля: Назва продукції, Тип продукції, Кількість продукції, Собівартість, Термін сплати, Прогноз ціни.
Елементи поля Термін сплати розраховуються як різниця Дата сплати та Дата реалізації. Розрахункове поле Прогноз ціни розраховується як добуток Собівартості на коефіцієнт. Якщо Кількість продукції не перевищує 20, то дані поля Собівартість перемножуються на коефіцієнт 1,27, в іншому випадку – дані поля Собівартість збільшуються на 1,25.
Інформацію про продукцію кондитерської фабрики, що була реалізована у третьому кварталі 2005 р. Динамічний набір записів повинен містити поля: Повна назва продукції, Кількість продукції, Технолог цеху, Дата реалізації, Прогноз ціни, Реалізація.
Елементи поля Технолог цеху будуються на основі конкатенації поля Прізвище та першої літери полів Ім’я та По батькові. Розрахункове поле Реалізація обчислюється як добуток полів Кількість продукції та Прогноз ціни.
Дані про продукцію, яка має найбільшу реалізацію (кількість записів про найбільшу реалізацію не повинна перевищувати восьми). Динамічний набір записів складається з таких полів: Код продукції, Повна назва продукції, Кількість продукції, Прогноз ціни.
Інформацію про реалізовану за певний період часу та несплачену продукцію кондитерської фабрики. Дата впровадження (початковий та кінцевий терміни) повинна задаватися під час виконання запиту у вигляді параметра, передбачити також можливість отримання інформації для всього періоду часу. Динамічний набір записів повинен мати поля: Повна назва продукції, Кількість продукції, Дата реалізації, Дата сплати, Прогноз ціни, Реалізація.
Дані, в яких кількість реалізованої продукції менше середньої кількості реалізованої продукції кондитерської фабрики. Динамічний набір записів складається з таких полів: Код продукції, Повна назва продукції, Кількість продукції.
Визначити дані про реалізацію кондитерських виробів за останні дні, за основу взяти кінцеву дату реалізації. Кількість останніх днів має вводитись у вигляді параметра. Динамічний набір записів складається з таких полів: Технолог цеху, Повна назва продукції, Кількість продукції, Дата реалізації, Дата сплати.
На основі групових запитів знайти інформацію, яка визначає:
Для кондитерської продукції загальну кількість виробництва та загальну реалізацію (назва повинна задаватися під час виконання запиту у вигляді параметра, передбачити можливість отримання інформації про всю продукцію одночасно).
Для технологів загальну кількість виробництва кондитерської продукції та загальну реалізацію готової продукції за деякий місяць деякого року (значення параметрів для розрахункових полів Рік та Місяць, що будуються по полю Дата впровадження, повинні вводитися під час виконання запиту).
На основі перехресного запиту отримати дані про виробництво продукції технологами цеху та її реалізацію, які б відображалися у таблиці:
Повна назва продукції |
Жовніров Ю.П. |
… |
Проценко І.С. |
«Молочна» карамель |
Загальна реалізація |
… |
Загальна реалізація |
… |
… |
… |
… |
«Гвардейский» шоколад |
Загальна реалізація |
… |
Загальна реалізація |
Використовуючи зовнішнє об’єднання між таблицями та запитами, визначити назву кондитерських виробів та їх собівартість, що не мали реалізації за обраний період часу. Нижній та верхній проміжки часу повинні вводитися під час виконання запиту у вигляді параметрів.
Модифікація даних за допомогою запитів-дій.
Збільшити на 3% собівартість кондитерських виробів, назва яких повинна задаватися при відкритті запиту.
З таблиці Реалізація продукції видалити записи, в яких сплата за реалізацію продукції прострочена більше ніж на 6 днів. До видалення даних зберегти в окремій таблиці Заборгованість записи, що підлягають видаленню.
IV. Для введення, модифікації та перегляду даних створити такі форми:
Форму Асортимент для введення та модифікації даних таблиці Асортимент.
Форму Продукція для введення та модифікації даних таблиці Продукція.
Форму Технологи для введення та модифікації даних Технологи.
Ієрархічну форму Асортимент продукції для перегляду даних таблиць Продукція та Асортимент. Ієрархічна форма містить головну та підпорядковану (Асортимент продукції та ціна) форми. У підпорядкованій формі передбачити поле Собівартість, з таблиці Виробництво за зміну та обчислювальне поле Кількість продукції даного типу, в якій підраховується загальна кількість продукції обраного типу.
Ієрархічну форму Технологи та продукція для перегляду даних таблиць Технологи, Продукція та Асортимент. Ієрархічна форма містить головну форму, в якій елементи управління аналогічні елементам управління форми Технологи, та підпорядковану форму Продукція для перегляду інформації про виробництво та прейскурант продукції. Форма будується по допоміжному запиту, що містить поля Назва продукції, Тип продукції, Собівартість. У підпорядкованій формі також передбачити поле Кількість виробленої продукції, в якій обчислюється загальна кількість виробленої продукції обраного цеху.
Ієрархічну форму Виробництво продукції для одночасного введення та перегляду даних у таблиці Виробництво за зміну та Реалізація продукції. На головній формі розмістити елементи управління, пов’язані з полями таблиці Виробництво за зміну, та підпорядковану форму Реалізація.
Для введення даних у поле Код продукції на формі використати поле зі списком. Продукція, що будується за таблицею Асортимент. У підпорядкованій формі Реалізація передбачити елементи управління, які відповідають елементам управління таблиці Реалізація продукції, а також елементи управління:
Обчислювальне поле Прогноз ціни розраховується за таким правилом: якщо Кількість виготовленої продукції не перевищує 20, то дані поля Собівартість перемножуються на коефіцієнт 1,27, в іншому випадку дані поля Собівартість збільшуються на 1,25.
Розрахункове поле Реалізація обчислюється як добуток полів Кількість та Прогноз ціни.
Поля Загальна кількість реалізації та Загальна вартість реалізації містять підсумкову інформацію про загальну кількість і вартість реалізації виробленої продукції кондитерської фабрики.
Г оловна форма, крім елементів управління, містить кнопку Технолог цеху, ввімкнення якої призводить до виведення на екран підпорядкованої форми Технологи, де відображається інформація про технолога обраного цеху. Форма будується по допоміжному запиту і містить розрахункове поле Технолог, яке є конкатенацією поля Прізвище та першої літери полів Ім’я та По батькові таблиці Технологи.
V. Підготовка вихідних документів.
Створити звіт «Реалізація продукції кондитерської фабрики», дані якого згруповані по місяцях, а в межах однієї групи по полю Повна назва продукції. Звіт повинен містити такі поля: Повна назва продукції, Кількість продукції, Собівартість, Термін сплати, Прогноз ціни, Попит, Реалізація. Розрахункові поля Термін сплати та Прогноз ціни будуються аналогічно полям Термін сплати та Прогноз ціни підпорядкованої форми Реалізація. Розрахункові поля Повна назва продукції та Реалізація будуються аналогічно полям Термін сплати та Прогноз ціни динамічного набору записів запиту ІІ.1.2. У поле Попит заноситься словосполучення Користується попитом, якщо поле Термін сплати не перевищує 3, в іншому випадку – словосполучення Не користується попитом. Передбачити можливість перерахунку собівартості в інші одиниці (євро, долар тощо) зі змінним коефіцієнтом перерахунку, який вводиться під час відкриття звіту. Проміжні та підсумкові результати додати для полів Кількість продукції та Реалізація.
Створити звіт «Аналіз виробництва продукції в розрізі цехів», дані якого згруповані по полю Номер цеху. Звіт повинен містити такі поля: Номер цеху, Технолог цеху, Назва продукції, Прогноз ціни, Попит, Вартість від реалізації, Преміальні. Елементи поля Технолог цеху будуються на основі конкатенації поля Прізвище та першої літери полів Ім’я та По батькові. Поле Вартість від реалізації обчислюється як добуток поля Прогноз ціни на поле Кількість продукції. Поле Преміальні розраховується за таким правилом: поле Вартість від реалізації збільшується на коефіцієнт 0,05, якщо у полі Попит міститься словосполучення Користується попитом, в іншому випадку заноситься значення 0. Передбачити можливість перерахунку Вартість від реалізації в інші одиниці (євро, долар тощо) зі змінним коефіцієнтом перерахунку, який вводиться під час відкриття звіту. Проміжні та підсумкові результати додати для полів Вартість від реалізації та Преміальні.
VI. Для роботи з таблицями, запитами, формами та звітами розробити головну кнопкову форму такого змісту. Форма повинна:
автоматично відкриватися відразу після відкриття бази даних.
складатися з чотирьох вкладок, кожна з яких призначена для роботи відповідно з таблицями, запитами, формами та звітами. На правій частині форми повинні бути кнопки для закриття головної кнопкової форми та для виходу з MS Access. Кнопки повинні бути доступними незалежно від того, яка вкладка відкрита на головній кнопковій формі.
мати такі властивості:
|
|
|
|
|
|
|
|
|
|
мати підпис з номером варіанта.