Індивідуальне завдання 3 Індивідуальне завдання 3
А. У середовищі табличного процесора MS Excel на основі наведених нижче таблиць створити базу даних, що відображає діяльність відділу збуту продукції м’ясопереробного підприємства. Таблиці бази розмістити на окремих сторінках книги, назви яких повинні відповідати назвам розташованих на них таблиць. Виконати форматування полів таблиць відповідно до характеру розміщених у них даних. Заголовки таблиць виконати з використанням обмежувальних ліній та заливки кольором.
Таблиця 1
Довідник продукції
Код продукції |
Найменування продукції |
Ціна за 1кг продукції, грн |
KODPR |
NAMEPR |
CINA |
110100 |
Ковбаса «Салямі» |
22,10 |
110101 |
Ковбаса «Сервелат» |
23,80 |
110102 |
Шинка «Козацька» |
13,60 |
110103 |
Шинка «До сніданку» |
11,05 |
110104 |
Сосиски молочні |
13,18 |
110105 |
Caрдельки |
11,48 |
110106 |
Куряча буженіна |
16,15 |
110107 |
М’ясний балик |
27,20 |
Таблиця 2
Довідник клієнтів
Код клієнта |
Найменування клієнта |
KODKL |
NAMEKL |
11 |
Ресторан «Дубки» |
22 |
Їдальня № 2 |
33 |
Кафе «Світлана» |
44 |
Кафе «Вікторія» |
55 |
Ресторан «Сатурн» |
Таблиця 3
Довідник доставки
Код доставки |
Код доставки |
KODDOS |
TUPDOS |
1 |
Самовивезення (без доставки) |
2 |
З доставкою |
Таблиця 4
Замовлення продукції
Номер замовлення |
Код клієнта |
Дата замовлення |
Дата сплати |
Код доставки |
NZ |
KODKL |
DATEZ |
DATESP |
KODDOS |
10101 |
11 |
01.01.05 |
01.01.05 |
1 |
20202 |
22 |
01.02.05 |
10.02.05 |
2 |
30303 |
33 |
01.03.05 |
|
1 |
40404 |
44 |
01.05.05 |
01.05.05 |
2 |
50505 |
55 |
01.05.05 |
01.05.05 |
1 |
60606 |
11 |
01.06.05 |
01.06.05 |
2 |
70707 |
22 |
01.07.05 |
11.07.05 |
1 |
80808 |
33 |
01.08.05 |
01.08.05 |
1 |
90909 |
44 |
01.09.05 |
21.09.05 |
1 |
101010 |
55 |
01.10.05 |
01.10.05 |
2 |
Таблиця 5
Вміст замовлень
Номер замовлення |
Код продукції |
Кількість, кг |
NZ |
KODPR |
KIL |
10101 |
110100 |
12 |
10101 |
110101 |
13 |
10101 |
110102 |
15 |
10101 |
110103 |
14 |
20202 |
110104 |
18 |
20202 |
110105 |
13 |
20202 |
110106 |
9 |
30303 |
110107 |
8 |
30303 |
110100 |
5 |
40404 |
110101 |
12 |
40404 |
110102 |
13 |
40404 |
110103 |
16 |
40404 |
110104 |
10 |
40404 |
110105 |
9 |
50505 |
110106 |
8 |
50505 |
110107 |
15 |
50505 |
110100 |
19 |
60606 |
110101 |
17 |
Закінчення табл. 5
NZ |
KODPR |
KIL |
60606 |
110102 |
15 |
70707 |
110103 |
13 |
70707 |
110104 |
18 |
70707 |
110105 |
13 |
80808 |
110106 |
9 |
80808 |
110107 |
8 |
80808 |
110100 |
5 |
90909 |
110102 |
13 |
90909 |
110103 |
14 |
101010 |
110105 |
16 |
101010 |
110106 |
17 |
I. Створити на окремих аркушах книги табличного процесора MS Excel два вихідних документи, використовуючи вхідні таблиці. Для їх створення необхідно виконати такі дії:
Ввести назву таблиці та сформувати «шапку»:
Для першого документа назва таблиці «Рахунок-фактура на постачання м’ясної продукції», назви стовпців: Місяць замовлення, Номер замовлення, Найменування продукції, Кількість, Ціна замовлення, Вартість, Дата замовлення, Дата сплати, Тип доставки. Поле Вартість розраховується як добуток полів Ціна замовлення та Кількість. Розрахункове поле Ціна замовлення обчислюється за таким правилом: якщо Тип доставки «Самовивезення», то Ціна за 1кг продукції залишається незмінною, в іншому випадку – Ціна за 1кг продукції замовлення збільшується на 10%.
Для другого документа назва таблиці «Аналіз замовлення продукції клієнтами», назви стовпців: Номер замовлення, Найменування клієнта, Найменування продукції, Кількість, Вартість, Дата замовлення, Дата сплати, Тип доставки. Розрахункове поле Вартість будується аналогічно полю Вартість, що описано у попередньому пункті.
Використовуючи необхідні прямі посилання, формули та функції, сформувати перший рядок даних вихідного документа. Для цього необхідно скористатися такими правилами:
у клітини, ідентифікатори яких збігаються з ідентифікаторами даних таблиці «Вміст замовлення», ввести прямі посилання (використати відносні адреси);
дані клітини, ідентифікатори яких збігаються з ідентифікаторами даних таблиць «Замовлення продукції» та «Довідник продукції», одержати, використовуючи функцію ВПР(.). Вміти використовувати такі функції пошуку: ПРОСМОТР(.) та ИНДЕКС(.) у поєднанні з ПОИСКПОЗ(.);
дані клітини, ідентифікатори яких збігаються з ідентифікаторами даних таблиць «Довідник клієнтів» та «Довідник доставки», одержати, використовуючи вкладену функцію ВПР(ВПР(.));
для першого звіту дані клітин, що належать стовпцю Ціна та Вартість, одержати, використовуючи функцію ЕСЛИ(.) у поєднанні з ВПР(.);
для другого звіту дані клітини, що належить стовпцю Вартість, одержати, використовуючи функції ЕСЛИ(.) у поєднанні з ВПР(.).
Використовуючи автозаповнення, сформувати повністю вихідний документ. Вихідний документ повинен містити стільки рядків з даними, скільки їх містить таблиця «Вміст замовлення».
У вихідній таблиці створити рядки з проміжними та підсумковими результатами (перед цим необхідно скопіювати таблицю вихідного документа на окремий аркуш книги табличного процесора MS Excel).
для першого документа дані згрупувати по полю Місяць реалізації, в межах однієї групи по полю Найменування продукції. Проміжні та підсумкові результати додати для полів Кількість та Вартість;
для другого документа дані згрупувати по полю Найменування клієнта. Проміжні та підсумкові результати додати для поля Вартість.
Виконати оформлення таблиць лініями та кольором.
II. Використовуючи Майстер діаграм:
на основі підсумкових даних документа «Аналіз замовлення продукції клієнтами» створити кругову діаграму об’ємного типу, яка відображає обсяги замовлення супермаркетами окремих видів м’ясної продукції у вартісному вимірі. На діаграмі показати відсоткові значення обсягів реалізації та відокремити від загального масиву сегмент з найбільшою часткою реалізації;
на основі підсумкових даних документа «Рахунок-фактура на постачання м’ясної продукції» створити стовпчикову діаграму (гістограму) з двома осями, що відображає помісячні обсяги реалізації продукції у кількісному та вартісному вимірах. На діаграмі показати назви осей та розмірності прив’язаних до них даних. Позначити стовпчик найбільш вдалого місяця реалізації (у вартісному вимірі) значенням обсягу реалізації цього місяця та вставити в нього графічний об’єкт (рисунок).
III. Виконати аналіз даних другої вихідної таблиці.
Використовуючи Автофільтр, відобразити дані реалізованої м’ясної продукції, для яких номер замовлення знаходиться в діапазоні [30303;70707].
Використовуючи Автофільтр, відобразити дані реалізованої м’ясної продукції для їдальні № 2 та кафе «Світлана» за перший квартал 2005 р.
Використовуючи Автофільтр, відобразити дані м’ясної продукції, для яких найменування ковбасної продукції містить слово «Салямі».
Виконати пункти 1–2, застосувавши Розширений фільтр. Результати застосування Розширеного фільтру відобразити у двох окремих таблицях.
Використовуючи Розширений фільтр з обчислювальним критерієм, відобразити дані, в яких вартість реалізованої м’ясної продукції більше максимальної вартості реалізованої продукції для кафе «Вікторія». Для побудови критерію фільтрації скористатися функцією МАКС(.). Результати фільтрації подати у вигляді таблиць з такими полями: Найменування клієнта, Найменування продукції, Кількість продукції, Ціна замовлення, Вартість.
ІV. На окремому аркуші книги табличного процесора MS Excel виконати аналіз даних за допомогою Майстра зведених таблиць (команда Данные/Сводная таблица). Зведену таблицю побудувати на основі даних другої вихідної таблиці без проміжних та підсумкових результатів.
Сформувати макет зведеної таблиці:
Поля Дата замовлення та Найменування клієнта перенести у область рядків.
Поле Найменування продукції перенести у область сторінки.
Поля Кількість продукції та Вартість перенести у область даних. Для поля Кількість продукції знайти максимальне значення кожної групи, а для поля Вартість − сумарне значення.
Згрупувати по кварталах дані поля Дата замовлення.
Б. За допомогою системи управління базами даних MS Access створити програмне забезпечення, що відображає діяльність відділу збуту продукції м’ясопереробного підприємства.
І. Розробити на основі індивідуального завдання концептуальну, логічну та фізичну модель предметної області (таблиці предметної області наведені в завданні А).
ІІ. Створити власну базу даних.
У режимі Конструктор визначити структуру кожної з таблиць бази даних відповідно до варіанта завдання таким чином:
Ввести поля для кожної таблиці.
Для кожного поля вказати тип даних.
Визначити властивості полів та ключові поля.
Створити схему даних власної бази даних. Встановити зв’язки між таблицями.
Ввести дані в таблиці.
ІІІ. Побудова запитів для відбору, пошуку та формування інформаційної бази.
На основі запитів на вибірку побудувати динамічний набір записів, що містять:
Інформацію про замовлення м’ясної продукції рестораном «Дубки» та кафе «Світлана», кількість продукції не перевищує 10 кг. Динамічний набір записів складається з таких полів: Номер замовлення, Найменування клієнта, Найменування продукції, Дата замовлення, Кількість, Ціна замовлення, Вартість.
Поле Вартість розраховується як добуток полів Ціна замовлення та Кількість. Розрахункове поле Ціна замовлення обчислюється за таким правилом: якщо у полі Тип доставки є тип доставки «Самовивезення», то дані поля Ціна за 1кг продукції залишаються незмінними, в іншому випадку дані поля Ціна за 1кг продукції збільшуються на 10%.
Інформацію про продукцію, яка була замовлена у третьому кварталі 2005 р. Динамічний набір записів повинен містити поля: Найменування клієнта, Найменування продукції, Тип доставки, Дата замовлення, Дата сплати, Кількість, Вартість.
Дані про м’ясну продукцію, яка найбільше замовлялась (кількість записів про найбільше замовлення не повинна перевищувати чотирьох). Динамічний набір записів складається з таких полів: Найменування клієнта, Найменування продукції, Ціна замовлення, Кількість, Дата замовлення.
Інформацію про замовлену за певний період часу та сплачену м’ясну продукцію, найменування якої починається з літери «Ш». Дата замовлення (початковий та кінцевий терміни) повинна вводитися під час виконання запиту у вигляді параметра, передбачити також можливість отримання інформації для всього періоду часу. Динамічний набір записів повинен мати поля: Найменування торговельної марки, Найменування продукції, Вартість, Дата реалізації, Дата сплати.
Дані, в яких кількість замовленої м’ясної продукції більше середньої кількості замовленої відповідної м’ясної продукції. Динамічний набір записів складається з таких полів: Код продукції, Найменування продукції, Кількість.
Визначити дані про замовлення м’ясної продукції за останні дні, за основу взяти кінцеву дату реалізації. Кількість останніх днів має вводитись у вигляді параметра. Динамічний набір записів складається з таких полів: Найменування клієнта, Найменування продукції, Кількість, Дата замовлення, Дата сплати.
На основі групових запитів знайти інформацію, яка визначає:
Для всіх клієнтів загальну кількість та загальну вартість замовленої м’ясної продукції (найменування м’ясної продукції повинне задаватися під час виконання запиту у вигляді параметра, передбачити можливість отримання інформації за всіма найменуваннями м’ясної продукції).
Для всієї м’ясної продукції загальну кількість та загальну вартість замовлення за деякий місяць деякого року (значення параметрів для розрахункових полів Рік та Місяць, що будуються по полю Дата замовлення, повинні вводитись під час виконання запиту).
На основі перехресного запиту отримати дані про продаж м’ясної продукції та її загальну вартість, які б відображувалися у таблиці:
Найменування продукції |
Їдальня № 2 |
… |
Ресторан «Сатурн» |
Сардельки |
Загальна вартість |
… |
Загальна вартість |
… |
… |
… |
… |
Шинка «Козацька» |
Загальна вартість |
… |
Загальна вартість |
Використовуючи зовнішнє об’єднання між таблицями та запитами, визначити найменування м’ясної продукції та її ціну, для якої не було замовлень за обраний період часу. Нижній та верхній проміжки часу повинні вводитися під час виконання запиту у вигляді параметрів.
Модифікація даних за допомогою запитів-дій.
Збільшити на 3% ціну м’ясної продукції, найменування якої повинне вводитися під час виконання запиту.
З таблиці Замовлення продукції видалити записи, в яких сплата за продукцію прострочена більше ніж на тиждень. До видалення даних зберегти в окремій таблиці Заборгованість записи, що підлягають видаленню.
IV. Для введення, модифікації та перегляду даних створити такі форми:
Форму Доставка для введення та модифікації даних таблиці Довідник доставки.
Форму Продукція для введення та модифікації даних таблиці Довідник продукції.
Форму Клієнти для введення та модифікації даних таблиці Довідник клієнтів.
Ієрархічну форму Продукція та клієнти для перегляду даних таблиць Довідник клієнтів та Довідник продукції. Ієрархічна форма містить головну та підпорядковану (Клієнти) форми.
Ієрархічну форму Замовлення м’ясної продукції для одночасного введення, модифікації та перегляду даних Замовлення продукції, Вміст замовлень. На головній формі розмістити елементи управління, пов’язані з полями таблиці Замовлення продукції та підпорядковану форму Вміст замовлень. Для введення даних у поле Код клієнта використати поле зі списком Клієнт. Список будується за таблицею Довідник клієнтів. Для введення даних у поле Код доставки використати поле зі списком Доставка. Список будується за таблицею Довідник доставки.
На підпорядкованій формі Вміст замовлень елементи управління пов’язані з полями однойменної таблиці Вміст замовлень. Передбачити для введення даних у поле Код продукції поле зі списком Продукція. Список будується за таблицею Довідник продукції. На цій формі, крім полів Загальна кількість замовленої продукції та Загальна вартість замовленої продукції, передбачити розрахункові поля Ціна замовлення та Вартість.
Елементи поля Ціна замовлення обчислюються за таким правилом: якщо у полі Тип доставки стоїть запис «Самовивезення», то дані поля Ціна за 1кг продукції залишаються незмінними, в іншому випадку – дані поля Ціна за 1кг продукції збільшуються на 10%.
Поле Вартість розраховується як добуток полів Ціна замовлення та Кількість.
Ієрархічну форму Клієнти та замовлення для перегляду інформації про замовлення клієнтами м’ясної продукції. Ієрархічна форма містить дві підпорядковані форми, які розміщуються на двох вкладках Клієнти та Замовлення клієнтів. Вкладка Клієнти містить елементи управління головної форми Клієнти, які аналогічні елементам однойменної форми.
Елементи управління іншої підпорядкованої форми розміщуються на вкладці Замовлення клієнтів і аналогічні елементам управління підпорядкованої форми Вміст замовлень в ієрархічній формі Замовлення м’ясної продукції. Вони відображають інформацію стосовно замовлень м’ясної продукції та типу її доставки, обраного клієнта на однойменній вкладці. Елемент управління прапорець Доставка використовується для введення даних у поле Код доставки.
V. Підготовка вихідних документів.
Створити звіт «Рахунок-фактура на постачання м’ясної продукції», дані якого згруповані по кварталах, а в межах однієї групи по полю Найменування продукції. Звіт повинен містити такі поля: Місяць реалізації, Номер замовлення, Найменування продукції, Кількість, Ціна замовлення, Вартість, Дата замовлення, Дата сплати, Тип доставки. Розрахункові поля Ціна замовлення та Вартість будуються аналогічно полям Вартість та Ціна замовлення підпорядкованої форми Вміст замовлень. Передбачити можливість перерахунку ціни в інші одиниці (євро, долар тощо) зі змінним коефіцієнтом перерахунку, який вводиться під час відкриття звіту. Проміжні та підсумкові результати додати для полів Кількість та Вартість.
Створити звіт «Аналіз замовлення продукції клієнтами», дані якого згруповані по полю Найменування клієнта. Звіт повинен містити такі поля: Найменування клієнта, Найменування продукції, Кількість, Вартість, Дата замовлення, Дата сплати, Тип доставки. Передбачити можливість перерахунку поля Вартість в інші одиниці (євро, долар тощо) зі змінним коефіцієнтом перерахунку, який вводиться під час відкриття звіту. Проміжні та підсумкові результати додати для поля Вартість.
VI. Для роботи з таблицями, запитами, формами та звітами розробити головну кнопкову форму такого змісту. Форма повинна:
автоматично відкриватися відразу після відкриття бази даних.
складатися з чотирьох вкладок, кожна з яких призначена для роботи відповідно з таблицями, запитами, формами та звітами. На правій частині форми повинні бути кнопки для закриття головної кнопкової форми та для виходу з MS Access. Кнопки повинні бути доступними незалежно від того, яка вкладка відкрита на головній кнопковій формі.
мати такі властивості:
Полосы прокрутки – отсутствуют;
Область выделения – нет;
Поле номера записи – нет;
Разделительные линии – нет;
Автоматический размер – да;
Выравнивание по центру – да;
Тип границы – тонкая;
Кнопка оконного меню – нет;
Кнопка размеров окна – нет;
Кнопка закрытия – нет.
мати підпис з номером варіанта.