ІНДИВІДУАЛЬНЕ ЗАВДАННЯ 9 13.10.2016 06:49

 

 

Таблиця 3

 

Довідник пива

 

Код пива	Найменування пива	Код типу пива	Ціна за одиницю продукції, грн
K_P	N_P	K_TP	C_GR
110030	«Світле»	1	0,98
110040	«Оксамитове»	2	0,83
110050	«Магнат»	1	1,13
110060	«Біле»	3	1,31
110070	«Преміум»	1	0,87
110080	«Пшеничне»	1	0,98
110090	«Фірмове»	1	1,43

Таблиця 4

Вміст реалізації

 

Номер реалізації	Код пива	Кількість реалізації
N_R	K_P	KIL
1	110030
1	110040
1	110050
1	110060
1	110070
2	110080
2	110090
2	110030
2	110040
2	110050
3	110060	20
3	110070	35
3	110080	65
3	110090	12
3	110030	1
4	110040	2
4	110050	32
4	110060	10
4	110070	18
4	110080	21
5	110090	11
5	110030	19
5	110040	1
5	110050	2
5	110060	30

 

Таблиця 5

Реалізація пива

 

Номер реалізації	Код супермаркету	Дата реалізації	Дата сплати
N_R	K_SM	D_R	D_S
1	11	02.01.05	02.01.05
2	22	22.01.05	23.01.05
3	33	11.02.05	21.02.05
4	44	02.03.05	05.03.05
5	55	22.03.05
6	11	01.05.05	01.05.05
7	22	21.05.05	21.05.05
8	33	10.06.05	29.06.05
9	44	30.06.05	30.06.05
10	55	20.07.05	21.07.05

І. Створити на окремих аркушах книги табличного процесора MS Excel два вихідних документи, використовуючи вхідні таблиці. Для їх ство­рення необхідно виконати такі дії:

1. Ввести назву таблиці та сформувати «шапку»:

&  Для першого документа назва таблиці - «Рахунок-фактура на постачання пивної продукції», назви стовпців: Місяць реалі­зації, Номер реалізації, Найменування товару, Кількість реалізації, Ціна реалізації, Вартість, Дата реалізації, Дата сплати. Елементи поля Найменування товару будуються на основі поля Найменування пива та поля Найменування типу пива. Поле Вартість розраховуються як добуток полів Ціна реалізації та Кількість реалізації. Розрахункове поле Ціна реалізації обчислюється за таким правилом: якщо Кількість реалізованої пивної продукції не перевищує 15, то дані поля Ціна за одиницю продукції збільшуються на 5%, в іншому випадку – дані поля Ціна за одиницю продукції залишаються незмінними.

&  Для другого документа назва таблиці - «Аналіз реалізації продукції клієнтами», назви стовпців: Номер реалізації, Найме­нування супермаркету, Найменування пива, Кількість реалі­зації, Вартість, Дата реалізації, Дата сплати. Розрахункове поле Вартість будується аналогічно полю Вартість, що описано у попередньому пункті.

1. Використовуючи необхідні прямі посилання, формули та функції, сформувати перший рядок даних вихідного документа. Для цього необхідно скористатися такими правилами:

у клітини, ідентифікатори яких збігаються з ідентифікато­рами даних таблиці «Вміст реалізації», ввести прямі поси­лання (використати відносні адреси);

дані клітини, ідентифікатори яких збігаються з ідентифікато­рами даних таблиць «Реалізація пива» та «Довідник пива», одержати, використовуючи функцію ВПР(.). Вміти викорис­товувати такі функції пошуку: ПРОСМОТР(.) та ИНДЕКС(.) у поєднанні з ПОИСКПОЗ(.);

дані клітини, ідентифікатори яких збігаються з ідентифіка­торами даних таблиць «Довідник супермаркетів» та «Довідник типів пива», одержати, використовуючи вкладену функцію ВПР(ВПР(.));

дані клітини, що належить стовпцю Найменування товару, одержати, використовуючи функцію СЦЕПИТЬ(.) у поєднанні з ВПР(.);

для першого звіту дані клітин, що належать стовпцю Ціна та Вартість, одержати, використовуючи функцію ЕСЛИ(.) у поєднанні з ВПР(.);

для другого звіту дані клітини, що належить стовпцю Вар­тість, одержати, використовуючи функції ЕСЛИ(.) у поєд­нанні з ВПР(.).

1. Використовуючи авто-заповнення, сформувати повністю вихід­ний документ. Вихідний документ повинен містити стільки рядків з даними, скільки їх містить таблиця «Вміст реалізації».
2. У вихідній таблиці створити рядки з проміжними та підсумко­вими результатами (перед цим необхідно скопіювати таблицю вихідного документа на окремий аркуш книги табличного про­цесора MS Excel).

&   для першого документа дані згрупувати по полю Місяць реалізації, в межах однієї групи - по полю Найменування товару. Проміжні та підсумкові результати додати для полів Кількість реалізації та Вартість;

&   для другого документа дані згрупувати по полю Наймену­вання супермаркету. Проміжні та підсумкові результати додати для поля Вартість.

1. Виконати оформлення таблиць лініями та кольором.

ІІ. Використовуючи Майстер діаграм:

@  на основі підсумкових даних документа «Аналіз замовлення про­дукції клієнтами» створити кругову діаграму об’ємного типу, яка відображає обсяги реалізації окремих видів пива у вартіс­ному вимірі. На діаграмі показати відсоткові значення обсягів реалізації та відокремити від загального масиву сегмент з най­більшою часткою реалізації;

@  на основі підсумкових даних документа «Рахунок-фактура на постачання пивної продукції» створити стовпчикову діаграму (гістограму) з двома осями, що відображає помісячні обсяги реалі­зації супермаркетам типів пива у кількісному та вартісному вимі­рах. На діаграмі показати назви осей та розмірності прив’язаних до них даних. Позначити стовпчик найбільш вдалого місяця реалізації (у вартісному вимірі) значенням обсягу реалізації цього місяця та вставити в нього графічний об’єкт (рисунок).

III. Виконати аналіз даних другої вихідної таблиці.

1. Використовуючи Автофільтр, відобразити дані реалізованої пивної продукції, для якої кількість реалізації знаходиться в діапазоні [5;15].
2. Використовуючи Автофільтр, відобразити дані реалізованої пивної продукції, найменування якої починається з літери «П», за лютий та березень 2005 р.
3. Використовуючи Автофільтр, відобразити дані реалізованої пивної продукції для супермаркетів «Караван» та «Сільпо».
4. Виконати пункти 1–2, застосувавши Розширений фільтр. Резуль­тати застосування Розширеного фільтру відобразити у двох окремих таблицях.
5. Використовуючи Розширений фільтр з обчислювальним крите­рієм, відобразити дані, в яких вартість реалізованої пивної продукції більше максимальної вартості реалізованої продукції для супермаркету «Фуршет». Для побудови критерію фільтра­ції скористатися функцією МАКС(.). Результати фільтрації подати у вигляді таблиць з такими полями: Найменування клієнта, Найменування продукції, Кількість реалізації, Ціна реалізації, Вартість.

ІV. На окремому аркуші книги табличного процесора MS Excel вико­нати аналіз даних за допомогою Майстра зведених таблиць (команда Данные/Сводная таблица). Зведену таблицю побудувати на основі даних другої вихідної таблиці без проміжних та підсумкових резуль­татів.

Сформувати макет зведеної таблиці:

Ä   Поля Дата реалізації та Найменування пива перенести у область рядків.

Ä   Поле Найменування супермаркету перенести у область сторінки.

Ä   Поля Кількість реалізації та Вартість перенести у область даних. Для поля Кількість реалізації знайти мінімальне значення кожної групи, а для поля Вартість − сумарне значення.

Ä   Згрупувати по кварталах дані поля Дата реалізації.

Б. За допомогою системи управління базами даних MS Access створити програмне забезпечення, що відображає діяльність оптової торговельної бази з реалізації пивної продукції.

 

І. Розробити на основі індивідуального завдання концептуальну, логічну та фізичну модель предметної області (таблиці предметної області наведені в завданні А).

ІІ. Створити власну базу даних.

1. У режиміКонструкторвизначити структуру кожної з таблиць бази даних відповідно до варіанта завдання таким чином:

1.1. Ввести поля для кожної таблиці.

1.2. Для кожного поля вказати тип даних.

1.3. Визначити властивості полів та ключові поля.

1. Створити схему даних власної бази даних. Встановити зв’язки між таблицями.
2. Ввести дані в таблиці.

ІІІ. Побудова запитів для відбору, пошуку та формування інформацій­ної бази.

1. На основі запитів на вибірку побудувати динамічний набір записів, що містять:

1.1.    Дані про реалізацію пива в супермаркетах «Сільпо» та «Караван», в яких кількість реалізації не перевищує 10. Динамічний набір записів повинен містити поля: Наймену­вання супермаркету, Найменування пива, Ціна реалізації, Дата реалізації, Вартість.

Поле Вартість розраховується як добуток полів Ціна реалі­зації та Кількість реалізації. Розрахункове поле Ціна реалі­зації обчислюється за таким правилом: якщо Кількість реалізованої пивної продукції не перевищує 15, то дані поля Ціна за одиницю продукції збільшуються на 5%, в іншому випадку – дані поля Ціна за одиницю продукції залишаються незмінними.

1.2.    Інформацію про реалізацію пива у зимові місяці 2005 р. Динамічний набір записів повинен містити поля: Повна назва пива, Дата реалізації, Кількість реалізації, Вартість.

Елементи поля Повна назва пива будуються на основі полів Найменування пива та Найменування типу пива.

1.3.    Дані про найменування пива, яке має найбільшу реалізацію (кількість записів про найбільшу реалізацію не повинна перевищувати семи). Динамічний набір записів складається з таких полів: Найменування пива, Найменування типу пива, Ціна реалізації, Кількість реалізації, Дата реалізації.

1.4.    Інформацію про продане, але не сплачене пиво за певний період часу. Дата реалізації (початковий та кінцевий тер­міни) повинна задаватися під час виконання запиту у вигляді параметра, передбачити також можливість отримання інфор­мації для всього періоду часу. Динамічний набір записів повинен мати поля: Найменування пива, Найменування типу пива, Вартість, Дата реалізації, Дата сплати.

1.5.    Дані, в яких кількість реалізованого пива більше середньої кількості відповідно реалізованого пива. Динамічний набір записів складається з таких полів: Найменування пива, Найменування типу пива, Кількість реалізації.

1.6.    Визначити дані про реалізацію пивної продукції за останні дні, за основу взяти кінцеву дату реалізації. Кількість остан­ніх днів має вводитись у вигляді параметра. Динамічний набір записів складається з таких полів: Повна назва пива, Найменування супермаркету, Кількість реалізації, Дата реалізації, Дата сплати.

1. На основі групових запитів знайти інформацію, яка визначає:

2.1.    Для всіх супермаркетів загальну кількість та загальну вартість реалізації (найменування супермаркету повинне задаватися під час виконання запиту у вигляді параметра, передбачити можливість отримання інформації про всі супермаркети).

2.2.    Для всіх типів пива загальну кількість та загальну вартість реалізації за деякий місяць деякого року (значення пара­метрів для розрахункових полів Рік та Місяць, що буду­ються по полю Дата реалізації, повинні вводитись під час виконання запиту).

1. На основі перехресного запиту отримати дані про продаж пива супермаркетам та їх загальну вартість, які б відображалися у таблиці:

 

 

1. Використовуючи зовнішнє об’єднання між таблицями та запи­тами, визначити найменування продукції (найменування пива та його типу), що не реалізована за обраний період часу. Нижній та верхній проміжки часу повинні вводитися під час виконання запиту у вигляді параметрів.
2. Модифікація даних за допомогою запитів-дій.

5.1.    Збільшити на 3% ціну пива, назва якого повинна задава­тися при відкритті запиту.

5.2.    З таблиці Реалізація пива видалити записи, в яких сплата за товари прострочена більше ніж на 5 днів. До видалення даних зберегти в окремій таблиці Заборгованість записи, що підлягають видаленню.

IV. Для введення, модифікації та перегляду даних створити такі форми:

Форму Типи пива для введення та модифікації даних таблиці Довідник типів пива.

Форму Супермаркети для введення та модифікації даних таб­лиці Довідник супермаркетів.

Форму Пиво для введення та модифікації даних таблиці Довід­ник пива.

Ієрархічну форму Типи пива та їх ціна для перегляду даних таблиць Довідник типів пива та Довідник пива. Ієрархічна форма містить головну та підпорядковану (Довідник пива) форми.

 

 

 

 

Ієрархічну форму Дані про реалізацію пива для одночасного введення, модифікації та перегляду даних таблиць Реалізація пива, Вміст реалізації. На головній формі розмістити елементи управління, пов’язані з полями таблиці Реалізація пива та підпорядковану форму Вміст реалізації. Для введення даних у поле Код супермакету використати поле зі списком Супер­маркет. Список будується за таблицею Довідник супермакетів.

На підпорядкованій формі Вміст реалізації передбачити еле­менти управління, аналогічні елемен­там управління однойменної таблиці.

На формі також використовується поле зі списком Найменування пива для введення даних у поле Код пива. Список будується по допоміжному запиту, що поєднує поля таблиць Довідник пива та Довідник типів пива.

На підпорядкованій формі, крім полів Загальна кількість реалі­зованого пива та Загальна вартість реалізованого пива, перед­бачити розрахункові поля Ціна реалізації та Вартість.

Розрахункове поле Ціна реалізації обчислюється за таким правилом: якщо Кількість реалізованої пивної продукції не перевищує 15, то дані поля Ціна збільшуються на 5%, в іншому випадку – дані поля Ціна залишаються незмінними.

Поле Вартість розраховується як добуток полів Ціна реалі­зації та Кількість реалізації.

 

 

 

Ієрархічну форму Супермакети та реалізація пива для пере­гляду інформації про реалізацію пивної продукції. Ієрархічна форма складається з двох вкладок Дані про супермакети та Вміст реалізації. Вкладка Дані про супермакети містить еле­менти управління головної та підпорядкованих форм. Елементи управління головної форми аналогічні елементам управління форми Супермакети.

Елементи управління підпорядкованої форми Реалізація пива аналогічні елементам управління однойменної таблиці.

 

 

Вкладка Вміст реалізації містить підпорядковану форму з еле­ментами управління аналогічними елементам управління одно­йменної підпорядкованої форми попереднього завдання

 

 

 

V. Підготовка вихідних документів.

@  Створити звіт «Рахунок-фактура на постачання пивної про­дукції», дані якого згруповані по кварталах, а в межах однієї групи - по полю Найменування товару. Звіт повинен містити такі поля: Номер реалізації, Найменування товару, Дата реалі­зації, Дата сплати. Кількість, Ціна реалізації, Вартість. Елементи поля Найменування товару будуються на основі поля Найменування пива та поля Найменування типу пива. Розра­хункові поля Ціна реалізації та Вартість будуються анало­гічно полям Вартість та Ціна реалізації підпорядкованої форми Вміст реалізації. Передбачити можливість перерахунку ціни продажу в інші одиниці (євро, долар тощо) зі змінним коефіцієнтом перерахунку, який вводиться під час відкриття звіту. Проміжні та підсумкові результати додати для полів Кількість реалізації та Вартість.

@  Створити звіт «Аналіз реалізації продукції клієнтами», дані якого згруповані по полю Найменування супермаркету. Звіт повинен містити такі поля: Найменування супермаркету, Найме­нування пива, Кількість, Вартість, Дата реалізації, Дата сплати. Передбачити можливість перерахунку поля Вартість в інші одиниці (євро, долар тощо) зі змінним коефіцієнтом перера­хунку, який вводиться під час відкриття звіту. Проміжні та підсумкові результати додати для поля Вартість.

VI. Для роботи з таблицями, запитами, формами та звітами розробити головну кнопкову форму такого змісту. Форма повинна:

Ä  автоматично відкриватися відразу після відкриття бази даних.

Ä  складатися з чотирьох вкладок, кожна з яких призначена для роботи відповідно з таблицями, запитами, формами та звітами. На правій частині форми повинні бути кнопки для закриття головної кнопкової форми та для виходу з MS Access. Кнопки повинні бути доступними незалежно від того, яка вкладка відкрита на головній кнопковій формі.

Ä  мати такі властивості:

 

Полосы прокрутки – отсутствуют; Выравнивание по центру – да; Область выделения – нет; Тип границы – тонкая; Поле номера записи – нет; Кнопка оконного меню – нет; Разделительные линии – нет; Кнопка размеров окна – нет; Автоматический размер – да; Кнопка закрытия – нет.

 

Ä  мати підпис з номером варіанта.

 

 

Завдання підвищеної складності

 

На формі передбачити поле (недоступне для користувача), в якому повинна виводитися системна дата:

 

 

Перша вкладка Таблиці призначена для роботи з таблицями бази даних. На вкладці повинні бути кнопки, кожна з яких призначена для відкриття відповідної таблиці. Для виконання цих дій необ­хідно розробити макрос із групою макрокоманд. Також необхідно передбачити кнопку для закриття всіх відкритих таблиць. Для досягнення цієї мети також необхідно розробити макрос із групою макрокоманд.

 

 

 

 

 

Друга вкладка Запити призначена для виклику запитів, розроб­лених у базі даних. На вкладці повинні бути група перемикачів та кнопка, за допомогою яких можна завантажити форми або для виклику основних запитів (запити 1.1, 1.2, 1.3, 1.4, 1.5 та 1.6), або додаткових запитів (запити 2.1, 2.2, 3 та 4).

 

 

 

 

При виборі основних запитів завантажується форма:

 

 

 

На формі передбачити шість незалежних прапорців, обведених рамкою, та кнопку «Відкрити». При натисканні на кнопку завантажу­ються ті запити, в прапорцях яких стоїть відмітка.

При виборі на головній формі додаткових запитів завантажу­ється форма:

 

 

 

На формі передбачити елемент керування полем зі списком, в якому вибирається запит, який потрібно завантажити, та кнопку «Відкрити». При натисканні на кнопку завантажується вибраний у полі зі списком запит.

 

Форма повинна мати такі властивості:

 

Полосы прокрутки – отсутствуют; Область выделения – нет; Поле номера записи – нет; Разделительные линии – нет; Автоматический размер – да; Выравнивание по центру – да; Тип границы – тонкая; Кнопка оконного меню – да; Кнопка размеров окна – да; Кнопка закрытия – да.

Третя вкладка Форми призначена для роботи з формами, розроб­леними в базі даних. На вкладці повинні бути список, в якому виводиться перелік форм для завантаження, та кнопка «Відкрити». Передбачається, що в списку можна вибрати лише одну форму:

 

 

 

 

Завдання підвищеної складності

 

На вкладці передбачити поле, в якому повинна виводитися кіль­кість відкриттів форм (відкриття будь-якої форми з використанням списку форм та кнопки «Відкрити» збільшує це число на 1) за остан­ній сеанс роботи з головною формою:

 

 

 

Четверта вкладка Звіти призначена для завантаження звітів бази даних. На вкладці передбачити групу перемикачів, в якій вибира­ється звіт для завантаження, кнопку «Відкрити», при натисканні на яку відкривається вибраний звіт, поля зі списками Найменування товару та Найменування супермаркету для фільтрації звітів по відповідних полях, поля Дата реалізації з та Дата реалізації по для фільтрації звітів за датою реалізації. Якщо в якомусь з полів зі списками або в полі для фільтрації по даті реалізації нічого не вводиться, то це означає, що за даною умовою фільтра­ція відбуватися не повинна:

 

 

 

 

Завдання підвищеної складності

 

При виборі звіту для перегляду поля, за якими фільтрація в даному звіті не передбачена, повинні ставати недоступними: