ІНДИВІДУАЛЬНЕ ЗАВДАННЯ 7 13.10.2016 06:48

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

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

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

 

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

5.1.    Зменшити на 5% ціну товарів для фірм-виробників, назва яких задається при відкритті запиту.

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

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

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

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

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

 

 

 

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

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

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

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

 

 

 

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

 

 

 

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

 

 

 

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

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

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

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

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

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

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

 

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

 

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

 

 

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

 

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

 

 

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

 

 

 

 

 

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

 

 

 

 

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

 

 

 

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

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

 

 

 

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

 

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

 

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

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

 

 

 

 

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

 

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

 

 

 

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

 

 

 

 

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

 

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