ІНДИВІДУАЛЬНЕ ЗАВДАННЯ 13 13.10.2016 06:50

 

 

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

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

&  для першого документа назва таблиці – «Використання кош­тів на відрядження», назви стовпців: Державний номер авто­мобіля, ПІБ водія, Країна, Дні відрядження, Витрати на відрядження. Елементи поля ПІБ водія будуються на основі конкатенації поля Прізвище та першої літери полів Ім’я та По батькові. Поле Дні відрядження розраховується як різниця початкового та кінцевого терміну відрядження. Поле Витрати на відрядження розраховується як сума двох полів Кошти на відрядження за кордон та Кошти на відрядження по Україні.

(Примітка: якщо кошти на відрядження вимірюється у валюті інших держав, то необхідно їх перевести у гривневий еквівалент).

&  для другого документа назва таблиці – «Аналіз виробничої діяльності автотранспортного підприємства», назви стовпців: Країна, ПІБ водія, Термін відрядження початок, Термін від­рядження кінець, Витрати на відрядження, Фрахт, Коригу­ючий коефіцієнт фрахта, Дохід. Елементи поля ПІБ водія будуються аналогічно полю ПІБ водія, що описано у попе­редньому пункті. Розрахункове поле Витрати на відрядження будується аналогічно полю Витрати на відрядження попе­реднього пункту. У поле Коригуючий коефіцієнт фрахта заноситься коефіцієнт 1,3, якщо Дні відрядження не переви­щують середнього терміну днів, в іншому випадку - зано­ситься коефіцієнт 1. Розрахункове поле Дохід розрахо­вується: Фрахт · Коригуючий коефіцієнт фрахта – Витрати на відрядження.

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

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

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

дані стовпця ПІБ водія сформувати, скориставшись функ­ціями СЦЕПИТЬ(.), ЛЕВСИМВ(.) та ВПР(.).

дані стовпця Коригуючий коефіцієнт фрахта сформувати, скориставшись функцією ЕСЛИ(.).

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

&  для першого вихідного документа провести сортування та групування по стовпцю Державний номер автомобіля. Проміжні та підсумкові результати додати для полів Дні відрядження та Витрати на відрядження.

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

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

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

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

на основі підсумкових даних документа «Використання коштів на відрядження» створити стовпчикову діаграму (гістограму) з двома осями, відобразивши на ній розподіл по автомобілях обсягів витрат на відрядження та часу (днів) використання автомобіля під час відряджень. На діаграмі показати назви осей та розмірності прив’язаних до них даних.

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

1. Використовуючи Автофільтр, відобразити дані про автомо­біль, Державний номер якого «199-48 КА».
2. Використовуючи Автофільтр, відобразити дані по Бельгії за квітень.
3. Використовуючи Автофільтр, відобразити дані про відряд­ження, кількість днів яких перевищує 14.
4. Виконати пункти 1–3, застосувавши Розширений фільтр. Резуль­тати фільтрації Розширеного фільтру відобразити в окремих таблицях.
5. Використовуючи Розширений фільтр з обчислювальним кри­терієм, відобразити дні відрядження, кількість яких переви­щує середній термін відрядження. Для побудови критерію фільтрації скористатися функцією СРЗНАЧ(.). Результати фільт­рації подати у вигляді таблиць з такими полями: Державний номер автомобіля, Дні відрядження, Витрати на відрядження.

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

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

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

Ä  Поле ПІБ водія перенести у область сторінки.

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

Ä  Згрупувати по кварталах дані поля Термін відрядження початок.

 

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

 

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

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

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

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

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

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

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

3. Ввести дані в таблиці.

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

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

1.1.    Інформацію про закордонні відрядження водіїв Чумаченка і Бурія, середній термін відрядження яких не перевищує 13 днів. Динамічний набір записів повинен містити поля: Дер­жавний номер автомобіля, Прізвище водія, Країна, Серед­ній термін відрядження, Витрати на відрядження.

Поле Витрати на відрядження розраховується як сума двох полів Кошти на відрядження за кордон та Кошти на від­рядження по Україні.

(Примітка: якщо кошти на відрядження вимірюється у валюті інших держав, то необхідно їх перевести у гривневий еквівалент).

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

Елементи поля ПІБ водія будуються на основі конкатенації поля Прізвище та першої літери полів Ім’я та По батькові. У поле Коригуючий коефіцієнт фрахта заноситься коефі­цієнт 1,3, якщо Дні відрядження не перевищують серед­нього терміну відрядження, в іншому випадку - заноситься коефіцієнт 1. Розрахункове поле Дохід розраховується: Фрахт · Коригуючий коефіцієнт фрахта - Витрати на від­рядження.

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

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

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

Поле Дні відрядження розраховується як різниця полів кінцевого терміну відрядження та початкового терміну.

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

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

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

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

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

 

 

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

5.1.    Збільшити середній термін відрядження на 5 днів до країни, назва якої повинна задаватися під час відкриття запиту.

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

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

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

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

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

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

 

 

 

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

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

Обчислювальне поле Витрати на відрядження розраховується як сума двох полів Кошти на відрядження за кордон та Кошти на відрядження по Україні.

(Примітка: якщо кошти на відрядження вимірюється у валюті інших держав, то необхідно їх перевести у гривневий еквівалент).

Поле Коригуючий коефіцієнт фрахта розраховується за таким правилом: у поле Коригуючий коефіцієнт фрахта заноситься коефіцієнт 1,3, якщо дані поля Дні відрядження не перевищують середнього терміну днів, в іншому випадку - поле Коригуючий коефіцієнт фрахта заноситься коефіцієнт 1.

Розрахункове поле Дохід обчислюється за формулою: Фрахт · Коригуючий коефіцієнт фрахта - Витрати на відрядження.

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

 

 

 

На підпорядкованій формі також міститься кнопка Термін відрядження, вми­кання якої призводить до появи однойменної форми, де відображаються дані про фрахт і середній тер­мін відрядження обраної країни.

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

 

 

 

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

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

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

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

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

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

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

 

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

 

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

 

 

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

 

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

 

 

 

 

 

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

 

 

 

 

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

 

 

 

 

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

 

 

 

 

 

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

 

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

 

 

 

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

 

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

 

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

 

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

 

 

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

 

 

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

 

 

 

 

 

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

 

 

 

 

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

 

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