ІНДИВІДУАЛЬНЕ ЗАВДАННЯ 1 13.10.2016 06:47

 

 

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

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

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

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

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

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

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

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

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

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

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

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

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

III. Виконати аналіз даних першої вихідної таблиці.

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

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

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

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

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

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

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

 

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

 

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

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

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

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

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

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

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

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

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

1.1.    Інформацію про продукцію торговельних марок «Фані» та «Галактон», ціна якої не перевищує 0,9 грн. Динамічний набір записів складається з таких полів: Найменування тор­говельної марки, Найменування продукції, Ціна реалізації, Вартість.

Поле Вартість розраховується як добуток полів Ціна реалі­зації та Кількість продукції. Розрахункове поле Ціна реалі­зації обчислюється за правилом, що описано у завданні з побудови першого вихідного документа «Рахунок-фактура на постачання молочної продукції» в табличному процесорі MS Excel.

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

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

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

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

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

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

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

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

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

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

 

 

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

5.1. Збільшити на 7% ціну молочної продукції, що починається з літери, яка задається при відкритті запиту.

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

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

• Форму Продукція для введення та модифікації даних таблиці Довідник продукції.
• Форму Торговельна марка для введення та модифікації даних таблиці Довідник торговельної марки.
• Ієрархічну форму Продукція торговельних марок для перегляду даних таблиць Довідник торговельної марки та Прейскурант продукції. Ієрархічна форма містить головну та підпорядковану (Прейскурант продукції) форми. У підпорядкованій формі передбачити розрахункове поле Кількість продукції торговель­ної марки, в якому обчислюється загальна кількість продукції обраної торговельної марки. На формі використано елемент управління поле зі списком Продукція для введення даних у поле Код продукції. Список будується за таблицею Довідник продукції.

 

 

 

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

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

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

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

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

 

 

 

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

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

 

 

 

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

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

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

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

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

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

Ä  мати властивості[1]:

 

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

 

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

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

 

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

 

 

 

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

 

 

 

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

 

 

 

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

 

 

 

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

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

 

 

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

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

 

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

 

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

 

 

 

 

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

 

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

 

 

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

 

 

 

 

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

 

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