Хід виконання роботи:

Створіть документ Ms Excel з ім’ям Лаборат_9 і зберігайте усі дані лабораторної роботи у папці «Мои документы/Прізвище». Послідовно виконайте Завдання 1, Завдання 2, Завдання 3, Завдання 4, Завдання 5, переходячи до робочих аркушів кни­ги Лаборат_9 з аналогічними іменами.

 

ЗАВДАННЯ 1. Для виконання сортування записів бази даних виконайте такі дії:

1)  активна комірка має розміщуватися в межах бази даних;

2)  меню ДанныеСортировка;

3)  у діалоговому вікні Сортировка диапазона (рис.1) за­дайте ключі сортування і порядок сортування;

 

Рис.1. Діалогове вікно Сортировка диапазона

 

4) встановіть альтернативний перемикач у положення Идентифицировать поля по подписям і натисніть на кнопку ОК.

Робочий аркуш Завдання 1:

Створіть базу даних відповідно до табл.1 і виконайте сор­тування записів. Перший ключ – назва фірми за зростанням; другий ключ – інвестиційні витрати за спаданням; третій ключ – термін окупності за спаданням. Збережіть внесені зміни.

Робочий аркуш Завдання 2

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

1.  Створіть автофільтр і відберіть записи з інвестиційними витратами понад 100 тис. грн і терміном окупності більше ніж півроку.

2.  Створіть розширений фільтр, що відбирає записи бази да­них із назвою фірми «Лідер», інвестиційні витрати яких не менші 100 тис. грн і термін окупності більше одного року.

Таблиця.1

 

Назва фірми	Інвестиційні витрати, тис. грн	Виручка від реалізації продукції, тис. грн	Поточні витрати, тис. грн	Податки, тис. грн	Термін окупності, роки
Лідер	90,00	196,96	20,00	25,60	0,6
Термопласт	109,28	257,32	50,00	33,45	0,6
Лідер	100,00	196,96	20,00	25,60	0,7
Юнітрейд	153,42	297,95	40,00	38,73	0,7
Юнітрейд	153,42	297,95	40,00	38,73	0,7
Юнітрейд	153,42	297,95	40,00	38,73	0,7
Лідер	105,00	196,96	20,00	25,60	0,7
Термопласт	124,36	247,33	40,00	32,15	0,7
Термопласт	124,36	247,33	40,00	32,15	0,7
Термопласт	125,36	247,33	40,00	32,15	0,7
Лідер	129,28	257,32	50,00	33,45	0,7
Термопласт	139,28	257,32	50,00	33,45	0,8
Юнітрейд	98,01	143,15	35,00	18,61	ІД
Юнітрейд	98,01	143,15	35,00	18,61	ІД
Юнітрейд	98,01	143,15	35,00	18,61	1,1
Лідер	140,00	170,00	30,00	9,10	1,2
Лідер	140,00	170,00	30,00	9,10	1,2
Термопласт	140,00	170,00	30,00	9,10	1,2
Лідер	143,46	121,47	25,00	15,79	1,8
Лідер	144,46	121,47	25,00	15,79	1,8

 

Фільтрація записів виконується тільки у відсортованій базі даних.

Створення автофільтра потрібно виконати таким чином:

1)  активна комірка має розміщуватися в межах бази даних;

2)  меню Данные→Филътр→Автофилътр;

3)      за допомогою кнопок полів (рис.2) Інвестиційні ви­трати та Термін окупності виберіть пункт Условие й у діало­говому вікні Пользовательский фильтр задайте умову відбору записів спочатку по полю Інвестиційні витрати (рис.3), а потім по полю Термін окупності (рис.4); натисніть на кноп­ку ОК  у діалогових вікнах Пользовательский фильтр;

4)  перегляньте відображені записи і переконайтеся, що вони відповідають заданим умовам відбору.

Зауваження. Для відображення всіх записів бази даних ви­конайте пункт 2.

Для створення розширеного фільтра слід виконати такі дії:

1)      створіть критерій фільтрації в комірках за межами бази даних (рис.5);

2)  активна комірка має розміщуватися в межах бази даних;

3)  меню Данные→Фильтр→Расширенный фильтр;

4)  у діалоговому вікні Расширенный фильтр (рис.6) за­дайте необхідні параметри (Исходный диапазон – це діапазон комірок бази даних, Диапазон условий – діапазон комірок критерію), потім натисніть на кнопку ОК.

 

 

Pиc.2. Результат виконання пункту 2

 

Рис.3. Діалогове вікно Пользовательский фильтр. Умова відбору записів по полю Інвестиційні витрати

Рис.4. Діалогове вікно Пользовательский фильтр. Умова відбору записів по полю Термін окупності

 

Рис.5. Критерій для Розширеного фільтра

Рис.6. Діалогове вікно Расширенный фильтр за бажання розміщення результатів в іншому діапазоні комірок стосовно роз­міщення всієї бази даних

 

5)  перегляньте і проаналізуйте результат фільтрації;

6)  збережіть зміни у документі.

        Робочий аркуш Завдання 3.

Скопіюйте відсортовану базу даних із робочого аркуша За­вдання 1 на цей робочий аркуш.

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

База даних має бути відсортована. Проміжні підсумки мож­на створити лише для полів, значення яких повторюються.

Алгоритм створення проміжних і загальних підсумків по­трібно виконати таким чином:

1)     активна комірка має розташовуватися в межах бази даних;

2)  меню ДанныеИтоги;

3)  у діалоговому вікні Промежуточные итоги (рис.7) вкажіть поле групування (Назва фірми), ім’я операції (Сума) і за допомогою прапорця вкажіть поле, за яким виконувати­меться задана операція (Інвестиційні витрати) та місце роз­міщення проміжних підсумків (Итоги под данными); натис­ніть кнопку OK;

Рис.7. Діалогове вікно Промежуточные итоги

 

4)      зверніть увагу на кнопку Убрать все діалогового вікна Промежуточные итоги, виконавши пункт 2 і клацнувши на цю кнопку, можна видалити всі проміжні та загальні під­сумки;

5)  перегляньте результат: він має бути таким, як на рис.8; згорніть і розгорніть структуру;

6)  збережіть зміни у документі.

Рис.8. Проміжні та загальний підсумки за згорнутої структури

Робочий аркуш Завдання 4

Скопіюйте на цей робочий аркуш відсортовану базу даних із робочого аркуша Завдання 1.

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

1.   Активна комірка має розміщуватися в межах бази даних.

2.   Меню Данные→Сводная таблица.

3.       У діалоговому вікні Майстра зведених таблиць і діаграм встановіть альтернативні перемикачі в положення як на рис.9 і клацніть на кнопку Далее.

4.   У наступному вікні Майстра зведених таблиць і діаграм (рис.10) перевірте правильність задания діапазону бази да­них (якщо не виконали крок 1, то задайте цей діапазон) і клац­ніть на кнопку Далее.

 

 

 Рис.9. Діалогове вікно Мастер сводных таблиц и диа­грамм (крок 1)

 

 

Рис.10. Діалогове вікно Мастер сводных таблиц и диа­грамм (крок 2)

 

5. У діалоговому вікні Майстра зведених таблиць і діаграм (рис.11) встановіть альтернативний перемикач у положен­ня Существующий лист і в текстовому полі вкажіть адресу лі­вої верхньої комірки (натисніть на кнопку цієї комірки) зведе­ної таблиці як на рис.11 і клацніть на кнопку Макет.

 

Рис.11. Діалогове вікно Мастер сводных таблиц и диа­грамм (крок 3)

 

6.  У діалоговому вікні Майстра зведених таблиць і діаграм – Макет (рис.12) перетягніть поле Назва фірми на об­ласть макета Столбец, поля Інвестиційні витрати та Вируч­ка від реалізації – область Данные.

 

Рис.12. Діалогове вікно Мастер сводных таблиц и диа­грамм – Макет

 

7.  Двічі клацніть на кнопку поля Інвестиційні витрати в області Данные у діалоговому вікні майстра Макет.

8.  У діалоговому вікні Вычисление поля сводной таблицы (рис.13) оберіть операцію Сумма та клацніть на кнопку До­полнительно.

Рис.13. Діалогове вікно Вычисление поля сводной таблицы

 

9.  У діалоговому вікні Вычисление поля сводной таблицы змініть назву поля, оберіть зі списку Дополнительно операцію Доля от суммы по строке (рис.14) і натисніть на кнопку ОК у двох наступних діалогових вікнах, а в діалоговому вікні як на рис.13 – на кнопку Готово.

 

Рис.14. Діалогове вікно Вычисление поля сводной табли­цы. Список додаткових обчислень

 

10. На поточному робочому аркуші з’явиться зведена табли­ця (рис.15). Збережіть зміни в документі.

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

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

13.   Побудуйте зведену діаграму, клацнувши на кнопку Діаграма панелі інструментів Сводные таблицы. Дослідіть можливості зведеної діаграми.

14.   Збережіть зміни в документі.

 

Рис.15. Зведена таблиця Список полей сводной таблицы та панеллю Сводные таблицы

Робочий аркуш Завдання 5

Скопіюйте на цей робочий аркуш відсортовану базу даних із робочого аркуша Завдання 1.

Використовуючи функції баз даних, визначте:

1)   для кожної фірми сумарні інвестиційні витрати з термі­ном окупності понад півроку і з виручкою від реалізації біль­шою 150 тис. грн;

2) назву фірми, інвестиційні витрати якої перевищують 130 тис. грн, термін окупності понад півроку, а поточні витрати є максимальними.