3.Використовуючи Автофільтр, відобразити дані, кількість яких дорівнює 15.

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

5.Використовуючи Розширений фільтр з обчислювальним критерієм, відобразити дані, в яких Вартість більша максимальної вартості по "Кептариках вовняних". Для побудови критерію фільтрації скористатися функцією

МАКС(.).

Варіант 13

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

						Таблиця 1
			Відрядження
Номер	Державний	Код		Термін відрядження
замов-	номер	країни	початок	кінець	За	По Україні,грн
лення	автомобіля				кордон,євро
	Navto	kod_kr	pochatok	end	koshti, e	koshti, grn
	199-
123	50КА	100	12.02.2005	25.02.2005	215	72
	199-
124	47КА	200	10.02.2005	28.02.2005	190	72
	199-
125	50КА	300	11.03.2005	26.03.2005	200	72
	199-
126	48КА	400	15.02.2005	06.03.2005	215	72
	199-
127	48КА	500	08.03.2005	26.03.2005	200	72
	199-
128	47КА	600	01.03.2005	18.03.2005	210	72
	199-
129	49КА	700	20.02.2005	06.03.2005	250	72
	199-
130	47КА	800	22.03.2005	04.04.2005	100	72

60

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

	Navto	kod_kr	pochatok	end	koshti, e	koshti, grn
	199-
131	51КА	900	18.03.2005	03.04.2005	190	72
	199-
132	50КА	600	02.04.2005	22.04.2005	220	72
	199-
133	48КА	700	09.03.2005	20.03.2005	250	72
	199-
134	48КА	800	28.03.2005	05.05.2005	105	72
	199-
135	47КА	900	07.04.2005	21.04.2005	220	72
	199-
136	51КА	200	12.04.2005	25.04.2005	220	72

				Таблиця 2
	Довідник водіїв
Державний		ПІП водія
номер	Прізвище	Ім’я		По батькові
автомобіля
№ avto	prisvishe	ima		po batkovi
199-50КА	Бурій	Михайло		Петрович
199-47КА	Чумаченко	Анатолій		Іванович
199-48КА	Назаренко	Володимир		Борисович
199-49КА	Касьян	Володимир		Анатолієвич
199-51КА	Сало	Леонид		Миколайович

		Таблиця 3
Термін відрядження
Код	Фракт,	середній
країни	євро	термін, днів
kod_kr	frakt	termin
100	3700	16
200	2600	13
300	4000	13
400	4500	15
500	3200	16
600	3300	16
700	5600	18
800	1250	8
900	3600	12

	Таблиця 4
Довідник країн
Код країни	Країна
kod_kr	kraina
100	Франція
200	Німеччина
300	Швеція
400	Норвегія
500	Голландія
600	Бельгія
700	Англія
800	Польща
900	Італія

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

61

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

для першого документа назва таблиці − "Використання коштів на відрядження", назви стовпців: Державний номер автомобіля,

ПІП водія, Країна, Дні відрядження, Витрати на відрядження.

Елементи поля ПІП водія будуються на основі конкатенації поля Прізвище та першої літери полів Ім’я та По батькові. Поле Дні відрядження розраховується як різниця поля End та поля Pochatok. Поле Витрати на відрядження розраховується як сума двох полів Koshti, grn та Koshti, e.

для другого документа назва таблиці − "Аналіз виробничої діяльності автотранспортного підприємства", назви стовпців:

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

заноситься коефіцієнт 1,3, якщо Дні відрядження не перевищують termin (середній термін, дні), в іншому випадку фракта заноситься коефіцієнт 1. Розрахункове поле Дохід

розраховується: Фракт* Коригуючий коефіцієнт фракта – Витрати на відрядження.

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

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

дані клітин, ідентифікотори яких збігаються з ідентифікаторами даних таблиць "Довідник водіїв", "Довідник країн", "Термін відрядження" одержати, використовуючи функцію ВПР(.). Вміти виконувати такі функції пошуку: ПРОСМОТР(.), та

ИНДЕКС(.) у поєднанні з ПОИСКПОЗ(.);

дані стовпця ПІП водія сформувати, скориставшись функціями

СЦЕПИТЬ(.), ЛЕВСИМВ(.) та ВПР(.).

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

62

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

4.У вихідній таблиці створити рядки з проміжними та підсумковими

результатами (перед цим необхідно скопіювати таблицю вихідного документа на окремий аркуш книги табличного процесора

MS Excel).

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

Проміжні та підсумкові результати додати для полів Дні відрядження та Витрати на відрядження.

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

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

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

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

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

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

1.Використовуючи Автофільтр, відобразити дані по автомобілю,

Державний номер якого "199-48 КА".

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

3.Використовуючи Автофільтр, відобразити дані про відрядження, кількість днів яких перевищує 14.

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

63