Коефіцієнт перерахунку сиропу залежно від температури

Асортимент

Розгорнутий асортимент

Режим виробництва

Індивідуальне завдання 15

А. У середовищі табличного процесора MS Excel на основінаве­дених нижче таблиць створити базу даних, що відображає виробничу діяльність підприємства по розливу лікеро-горілчаних виробів. Таблиці розмістити на окремих сторінках книги, назви яких повинні відпові­дати назвам розташованих на них таблиць. Виконати форматування полів таблиць відповідно до характеру розміщених у них даних. Заголовки таблиць виконати з використанням обмежувальних ліній та заливки кольором

Таблиця 1

Код групи	Код напою	Температура виробництва	Кількість сиропу, л/дал	Дата виробництва
Kod_gr	Kod_napou	Temperat	Cirop	D_VUROB
				10.01.05
				20.01.05
				30.01.05
			46,54	09.02.05
				19.02.05
				01.03.05
			50,9	11.03.05
				21.03.05
			19,4	31.03.05
			36,1	10.04.05
			40,8	20.04.05
			39,8	30.04.05
				10.05.05
				20.05.05
				30.05.05
				09.06.05
				19.06.05
				29.06.05
			46,54	09.07.05

Закінчення табл. 1

Kod_gr	Kod_napou	Temperat	Cirop	D_VUROB
				19.07.05
				29.07.05
			50,9	08.08.05
				18.08.05
			19,4	28.08.05
			36,1	07.09.05
			40,8	17.09.05
			39,8	27.09.05
				07.10.05
				17.10.05
				27.10.05

Таблиця 2

Код напою	Назва напою
Kod_napou	Name_napou
	«Апельсиновий»
	«Крупникас»
	«Ванільний»
	«Вишневий»
	«Кофейний Мокко»
	«Північна»
	«Горобинка»
	«Райське яблуко»
	«Колхіда»
	«Алтайська»
	«Кизилова»
	«Вишневий»
	«Сливовий»
	«Чорносмородиновий»
	«Яблучний»

Таблиця 3

Код групи	Назва групи
Kod_gr	Name_gr
	Лікер
	Крем
	Наливка
	Пунш

Таблиця 4

Температура	Коефіцієнт
Temperat	Koef
	1,005
	1,003
	1,003
	1,003
	1,002
	1,002
	1,001
	1,001
	1,001
	1,000
	1,000
	1,000
	0,999
	0,999
	0,998
	0,998
	0,997
	0,997
	0,997
	0,996
	0,996

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

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

& для першого документа назва таблиці «Виробництво напоїв», назва стовпців: Назва групи; Повна назва напоїв; Температура виробництва; Коефіцієнт; Необхідна кількість сиропу; Дата виробництва, Примітка. Елементи поля Повна назва напоїв будуються на основі поля Назва групи таполя Назва напою. Поле Необхідна кількість сиропу розраховуються як добуток коефіцієнта перерахунку продукції на кількість сиропу. У поле Примітка заноситься слово Економія, якщо коефіцієнт не перевищує 21, в іншому випадку – слово Перевитрата.

& для другого документа назва таблиці «Реалізація готової продукції», назва стовпців: Назва групи, Назва напою, Дата виробництва, Необхідна кількість сиропу, Загальний об’єм напою, Кількість пляшок, Кількість гофрокоробів, Вартість упаковки. Поле Загальний об’єм напою розраховується як сума нормативу Необхідної кількості сиропу та константи 1000 літрів. Розрахункове поле Кількість пляшок обчислю­ється за формулою: Загальний об’єм напою× 0,97/0,75. Поле Кількість гофрокоробів обчислюється як ділення поля Кіль­кість пляшок на 6. Поле Вартість упаковки обчислюється як добуток Кількості пляшок, Кількості гафрокоробів та коефіцієнта 0,97.

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

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

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

§ дані стовпця Повна назва напою побудувати, використавши функцію СЦЕПИТЬ(.);

§ дані стовпця Примітка сформувати через функцію ЕСЛИ(.).

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

4. Скопіювати таблиці вихідних документів на окремі листи книги та розрахувати проміжні та загальні результати таким чином:

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

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

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

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

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

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

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

1. Використовуючи Автофільтр, відобразити дані по продукції, Температура виробництва якої менша 20.

2. Використовуючи Автофільтр, відобразити дані по продукції, Температура виробництва якої більша 20, а Коефіцієнт менше 0,999.

3. Використовуючи Автофільтр, відобразити дані по Лікерах з коефіцієнтом, меншим 1.

4. Виконати пункти 1–2, застосувавши Розширений фільтр. Резуль­тати фільтрації Розширеного фільтру відобразити у окремих таблицях.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1.1. Інформацію про групи, що мають назву «лікер» і «пунш», а температура виробництва яких менша 20. Динамічний набір записів повинен містити поля: Назва групи, Назва напою, Температура виробництва, Необхідна кількість сиропу.

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

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

Елементи поля Повна назва напоїв будуються на основі поля Назва групи таполя Назва напою. Поле Загальний об’єм напою розраховується як сума нормативу Кількість сиропу та константи 1000 літрів. Розрахункове поле Кількість пляшок обчислюється за формулою: Загальний об’єм напою × 0,97/0,75. Поле Кількість гофрокоробів обчислю­ється як ділення поля Кількість пляшок на 6. Розрахункове поле Вартість упаковки обчислюється як добуток полів Кількості пляшок і Кількості гофрокоробів такоефіцієнта0,97.

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

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

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

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

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

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

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

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

Назва напою	Крем	…	Пунш
«Алтайська»	Загальна кількість гофрокоробів	…	Загальна кількість гофрокоробів
…	…	…	…
«Яблучний»	Загальна кількість гофрокоробів	…	Загальна кількість гофрокоробів

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

5. Модифікація даних за допомогою запитів-дій.

5.1. Збільшити на 10% кількість сиропу для напою, назва якого задається під час відкриття запиту.

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

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

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

§ Форму Розгорнутий асортимент для введення та модифікації даних таблиці Розгорнутий асортимент.

§ Форму Коефіцієнт перерахунку сиропу для введення та моди­фікації даних таблиці Коефіцієнт перерахунку сиропу залежно від температури.

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

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

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

Обчислювальне поле Необхідна кількість сиропу розраховується як добуток коефіцієнта перерахунку продукції на кількість сиропу.

Поле Загальний об’єм напою розраховується як сума нормативу Необхідної кількості сиропу та константи 1000 літрів.

Розрахункове поле Кількість пляшок обчислюється за форму­лою: Загальний об’єм напою × 0,97/0,75.

Поля Загальний об’єм напою у розрізі групи, Загальна кількість сиропу та Загальна кількість пляшок виводять підсумкові дані про обсяги виробництва напоїв обраної групи.

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

Поля Загальний об’єм напою та Кількість пляшок обчислю­ється аналогічно полям підпорядкованої форми Реалізація ієрархічної форми Асортимент напоїв та виробництво.

Розрахункове поле Кількість гофрокоробів обчислюється як ділення поля Кількість пляшок на константу 6.

Поле Вартість упаковки обчислюється як добуток полів Кіль­кості пляшок, Кількості гофрокоробів та коефіцієнта 0,97.

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

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

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

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

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

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

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

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

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

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

Дата добавления: 2015-05-24