ІС та ІТ - ЧАСТИНА 1

				Таблиця 1.
Назва	Дата продажу	Кількість, кг	Ціна, грн.	Вартість, грн
Груша	23.01.2015	30	28	360
Яблуко	10.01.2015	10	15	150
Апельсин	12.01.2015	15	21	105

Послідовність виконання дій така:

1)Оскільки підбиття підсумків потрібно проводити в розрізі назв товарів, то спочатку необхідно здійснити сортування за полем Назва.

2)В діалоговому вікні Промежуточные итоги слід задати:

-При каждом изменении в - поле Назва;

-Операция - функція Сумма;

-Добавить итоги по - обрати поля Кількість та Вартість.

Результатом виконання Задачі 1 буде Таблиця 2, наведена нижче.

Таблиця 2

Лабораторна робота 2-7

Хід роботи

1. Відкрийте файл ЛАБОРАТОРНА РОБОТА 2-7.xlsx. Збережіть роботу на

власному носії інформації з ім’ям ЛР 2-7 _ Ваше прізвище.xlsx.

2. Перейдіть на аркуш Сортування 1, де знаходиться Таблиця 4 (показана

фрагментом).

РЕАЛІЗАЦІЯ АВТОМОБІЛІВ МЕРЕЖЕЮ МАГАЗИНІВ “ПОДОРОЖ”

					Таблиця 4
			Модель
№	Дата	Місто	автомобіля	Колір		Вартість, грн
1	04.01.2015	Луцьк	Сенс	Білий		68000
2	04.01.2015	Одеса	Славута	Зелений		50400
3	04.01.2015	Харків	Таврія нова	Зелений		41300
4	04.01.2015	Львів	Сенс	Синій		69000
5	04.01.2015	Одеса	Славута	Синій		49990
6	04.01.2015	Київ	Сенс	Червоний		68000

3.Виконайте сортування даних Таблиці 4 за назвою автомобілів таким чином, щоб розмістити їх в алфавітному порядку від А до Я.

4.Перейдіть на аркуш Сортування 2, де знаходиться Таблиця 5, дані якої треба впорядкувати в першу чергу за маркою автомобіля, а в другу чергу - за назвою міста.

5.Перейдіть на аркуш Підсумки 1, де знаходиться Таблиця 6.

Виконайте Завдання 1: Визначити суму продажів автомобілів за січень по кожному місту окремо. Додайте підсумки по максимальній вартості автомобілів по кожному місту.

6. Перейдіть на аркуш Підсумки 2, де знаходиться Таблиця 7.

Виконайте Завдання 2: Визначити середню вартість автомобілів кожної моделі. Додати підсумки по кількості проданих автомобілів по кожній моделі.

7. Перейдіть на аркуш Підсумки 3, де знаходиться Таблиця 8.

Виконайте Завдання 3: Визначити суму продажів автомобілів по кожному місту та кількість проданих автомобілів кожної моделі в місті.

8. Збережіть роботу.

Тема 7. Робота зі списками. Фільтрація даних.

ТЕОРЕТИЧНІ ВІДОМОСТІ

1. Використання інструменту Фільтр для пошуку даних.

Для пошуку даних у списку за певними критеріями та відображення його результату у вигляді таблиці існує інструмент Фильтр з двома його видами: Автофильтр та Расширенный фильтр.

В поясненнях, як початкова, використана Таблиця 1. з попередньої лабораторної роботи, фрагмент якої показано нижче, на Рис. 2.62.

	Назва			Дата продажу			Кількість,			Ціна,			Вартість,
							кг			грн			грн
	Груша			23.01.2015			30			28			840
	Яблуко			10.01.2015			10			15			150
	Апельсин			12.01.2015			15			21			315
	Лимон			14.01.2015			22			40			880

Рис. 2.62. Фрагмент Таблиці 1, яка використовується для пояснення роботи інструменту Фильтр.

Автофільтр. Для ввімкнення автофільтру потрібно виділити заголовний рядок списку і виконати послідовність дій: вкладка Данные

група Сортировка и Фильтр кнопка Фильтр .

За цією командою в полях заголовного рядка з’являються кнопки-

списки, натискання на які викликає випадаюче меню, з якого можна вибрати необхідну команду, та яке надає доступ до автофільтру - команди

Настраиваемый фильтр (Рис. 2.63.).

Рис. 2.63. Випадаюче меню автофільтру.

Команда Первые 10 … - відкриває діалогове вікно Наложение условия по списку, в якому можна задати: пошук яких значень здійснювати

(найбільші/найменьші) та яку кількість. Наприклад, коли потрібно знайти три найбільших значення (Рис. 2.64.).

Рис. 2.64. Приклад використання команди Первые 10 …

Команда Настраиваемый фильтр – відкриває діалогове вікно

Пользовательский автофильтр і дозволяє задати складний критерій пошуку з допомогою функцій И чи ИЛИ. Наприклад, якщо потрібно знайти дані про товари, продані за період з 10.01.2015 по 20.01.2015, то для розв’язання такої задачі потрібно розкрити список – меню в полі Дата продажу й обрати команду Настраиваемый фильтр. В діалоговому вікні

Пользовательский автофильтр (Рис. 2.65.) задати відповідні критерії.

При фільтрації дані, що не відповідають заданому критерію,

приховуються, але їх порядок розташування залишається незмінним.

Рис. 2.65. Приклад використання команди Условие.

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

Алгоритм застосування розширеного фільтру:

1)Скопіювати заголовний рядок таблиці і розмістити його поряд з початковою таблицею.

2)Під відповідними полями скопійованого рядку слід прописати критерії (умови) пошуку. Умови, прописані в одному рядку в блоці критеріїв,

об’єднуються функцією И, в різних рядках – функцією ИЛИ.

3) Застосувати команду: вкладка Данные		група Сортировка и
фильтр	кнопка Дополнительно	. За цією

командою відкривається діалогове вікно (Рис. 2.66.), в якому необхідно

вказати:

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

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

Фильтровать список на месте, то результуючу таблицю буде розмішено на

місці початкової, і відновити початкову таблицю буде неможливо;

в полі Исходный диапазон вказати діапазон розміщення початкової таблиці;

в полі Диапазон условий вказати місце розташування діапазону

зблоком критеріїв, включаючи заголовний рядок цього блоку.

Розглянемо Задачу 1. На прикладі даних Таблиці 1. з попередньої лабораторної роботи (№ 3-7) знайти записи, що стосуються товарів, назва яких починається з літери А і кількість яких не більше 30 або товарів,

проданих до 20.01.2015.

На Рис. 2.66. разом з вихідною таблицею для фільтрації (ліворуч)

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

Рис. 2.66. Початкова таблиця, таблиця з критеріями пошуку та діалогове вікно Расширенный фильтр для встановлення параметрів фільтрації.

На Рис. 2.67. наведено результати фільтрації за умовою Задачі 1 з

допомогою розширеного фільтру.

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

Лабораторна робота 3-8

Хід роботи

1.Відкрийте файл ЛАБОРАТОРНА РОБОТА 3-8.xlsx. Збережіть роботу на власному носії інформації з ім’ям ЛР3-8 _ Ваше прізвище.xlsx.

2.Перейдіть на аркуш Автофільтр 1, де знаходиться Таблиця 1.

Скопіюйте таблицю і розмістіть її на аркушах: Автофільтр 2, Автофільтр 3,

Розширений фільтр 1, Розширений фільтр 2.

					Таблиця 1
			Модель
№	Дата	Місто	автомобіля	Колір	Вартість, грн
1	04.01.2015	Луцьк	Сенс	Білий	68000
2	04.01.2015	Одеса	Славута	Зелений	50400
3	04.01.2015	Харків	Таврія нова	Зелений	41300

3. З допомогою команди: вкладка Главная група Стили

кнопка Форматировать как таблицу виконайте форматування всіх таблиць. Використайте різні варіанти.

4. Користуючись можливостями автофільтру здійсніть пошук даних:

аркуш Автофільтр 1 – автомобілі, продані у Львові з 1 по

15 січня 2015;

аркуш Автофільтр 2 – автомобілі Сенс або Ланос червоного кольору;

аркуш Автофільтр 3 – знайти дані по 5 автомобілям, проданим за найвищою ціною.

5.Користуючись розширеним фільтром знайти дані за такими умовами:

Завдання 1. На аркуші Розширений фільтр 1 отримати нову таблицю,

розміщену в іншому діапазоні, з даними щодо автомобілів Сенс вартість яких менша за 55000 грн і які були продані не раніше 10.01.15 або

автомобілів Ланос і колір яких - чорний.

Завдання 2. На аркуші Розширений фільтр 2 отримати нову таблицю,

розміщену в іншому діапазоні, з даними щодо автомобілів, які були продані не пізніше 10.01.15 і мали синій колір або автомобілів, які мали ціну не менше 58000 і були реалізовані в місті Одесі.

6. Збережіть роботу.

Тема 8. Розв’язання задач оптимізації.

ТЕОРЕТИЧНІ ВІДОМОСТІ

1.Процедура пошуку розв’язку. Інструмент Поиск решения.

Здопомогою інструменту Поиск решения легко вирішуються задачі:

оптимального використання ресурсів; оптимального розподілу завдань з випуску однорідної продукції; оптимального використання потужностей;

оптимального кроєння матеріалів. Крім того, задача про суміші та транспортна задача, які також входять до задач лінійного програмування.

Якщо у вкладці Данные немає кнопки інструменту Поиск решения, то її слід встановити таким чином:

1.Під кнопкою ”Office”слід обрати кнопку Параметры Excel (Рис. 2.68.).

Рис. 2.68. Під кнопкою ”Office”слід обрати кнопку Параметры Excel.

2.В отриманому вікні Параметры Excel обрати команду Надстройки

в лівій частині вікна, а в правій його частині відповідно - ім’я Поиск решения, як показано на Рис. 2.69.

Рис. 2.69. Вікно Параметры Excel.

3. Натиснути кнопку Перейти та отримати вікно Надстройки (Рис. 2.70.), де треба поставити перемикач біля команди Поиск решения.

Рис. 2.70. Вікно Надстройки.

4.У вкладці Данные з’явиться група Анализ і кнопка Поиск решения у цій групі.

Розглянемо процедуру пошуку розв’язку на прикладі такої задачі :

ЗАДАЧА 1. Використовуючі дані Таблиці 1, планують виробничий процес підприємства таким чином, щоб кількість виробів, що випускаються,

була не меншою 100 шт., кількість різних виробів змінювалась би в межах від 10 до 40 шт. і сумарні витрати на виготовлення виробів були б мінімальними.

Таблиця 1