Урок (практична робота) Excel Аналіз даних. Фільтрація даних й обчислення підсумкових характеристик вибірки

ПРАКТИЧНА РОБОТА № 7 (13)

ТЕМА: MS Excel. Аналіз даних. Фільтрація даних й обчислення підсумкових характеристик вибірки

МЕТА: сформувати поняття сортування, фільтрація, автофільтр, розширений фільтр; пояснити правила впорядкування і пошуку даних; сформувати вміння створювати і застосовувати фільтри різних типів, обчислювати проміжні підсумки.

Обладнання: ПК, робочий зошит, тестові завдання

В результаті виконання практичної роботи студент повинен

знати:

•                  способи фільтрації даних у таблицях;
•      призначення зведених таблиць і засобів знаходження проміжних підсумків, а також відмінність між ними;
•      методику автоматизованої вибірки значень з одного стовпця таблиці за значеннями іншого стовпця;

вміти:

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

Тривалість роботи 2 години

Питання для підготовки до практичної роботи

1. Що таке фільтрація даних?
2. Які засоби фільтрації даних передбачені в Microsoft Excel?
3. Які переваги має розширений фільтр порівняно з автофільтром?
4. Як виконується сортування таблиці за двома параметрами?
5. Як виконати розрахунок проміжних підсумків?
6. Як побудувати діаграму по розрахованим підсумкам?

Короткі теоретичні відомості

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

Фільтрація – процес вибирання із таблиці рядків, що задовольняють певній умові.

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

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

Для відбору даних за допомогою автофільтра необхідно послідовно виконати такі дії:

1. Встановити курсор у таблицю, яку потрібно відфільтрувати.
2. Виконати команду ДанныеФильтрАвтофильтр. У клітинках першого рядка праворуч буде відображено кнопки з трикутрими позначками .
3. Клацніть кнопку в клітинці з назвою того стовпця, на значення якого накладатиметься умова. Із списку, що відкриється (рис. 7.1 ), виберіть необхідну умову фільтрації.

Рис. 7.1 Список умов фільтрації

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

Варіант Первые 10… дозволяє відібрати визначене число (чи відсоток) записів за яким-небудь критерієм (рис. 7.2).

Рис. 7.2 Вибір кількох найбільших чи найменших об’єктів

Варіант Условие… дозволяє задати спеціальну умову фільтрування. На екран буде виведено вікно Пользовательский автофильтр (рис. 7.3) з чотирма розкривними списками. У лівому верхньому вибирають знак операції порівняння, а правому верхньому – значення, яке можна розташувати справа від нерівності.

Рис. 7.3 Вікно Пользовательский автофильтр

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

Щоб скасувати фільтрування таблиці відразу за всіма параметрами, виконайте команду ДанныеФильтрОтобразить все.

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

Для відбору даних за допомогою розширеного фільтра необхідно послідовно виконати такі дії:

1. У порожніх клітинках аркуша створити критерій фільтрації.

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

У розширенних фільтрах використовуються прості та складені умови добору. У складених умовах використовуються логічні операції І та АБО. Операція І спрацьовує під час запису в блоці критеріїв значень полів в одному рядку, операція АБО – у разі запису полів у різних рядках.

2. Встановити курсор на будь-яку клітинку таблиці з даними.
3. Виконати команду ДанныеФильтрРасширенный фильтр. Буде відображено вікно Расширенный фильтр (рис. 31).
4. У полі Исходный диапазон вкажіть адресу діапазону, де розміщується таблиця, а в полі Диапазон условий – адресу діапазону критерію і клаціть кнопку OK.

рис. 7.4 Створення розширеного фільтра

Після застосування розширеного фільтра відображатимуться тільки ті рядки, що відповідають його критерію, а їхні номери матимуть синій колір. Так, на рис. 32 показано результат застосування розширеного фільтра, процес створення якого продемонстровано на рис. 31: відібрано пансіонати, які мають назву Золотой колос або ціна путівки в які перевищує 2000 грн. (Муссон).

рис. 7.5 Результат застосування розширеного фільтра

Під час фільтрації дані, що не відповідають певному критерію, приховуються. У випадках, коли необхідно бачити всі дані, але деякі певним чином виділити застосовують умовне форматування.

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

Для умовного форматування діапазону клітинок необхідно послідовно виконати такі дії:

1. Виділити діапазон, що підлягає умовному форматуванню.
2. Виконати команду ФорматУсловное форматирование…
3. В діалоговому вікні Условное форматирование у списку зліва виберіть один із двох елементів:

• значение – умова, від якої залежитиме формат, накладатиметься на значення саме тієї клітинки, яка форматується (рис. 7.6, а);
• формула – умову буде задано як формулу, що набуває логічного значення (рис. 7.6, б).

4. Клацніть кнопку Формат і у вікні Формат ячеек задайте формат шрифту, меж і кольору комірок, який встановлюватиметься в разі виконання умови. Клацніть кнопку OK.

а)

б)

рис. 7.6 Вікно Условное форматирование: а) - умову накладено на значення тієї клітинки, яка форматується; б) - умову визначено як формулу, що є істиною або хибною

Сортування – це процес впорядкування даних за певною умовою.

Для сортування рядків по одному полю за зростанням або спаданням необхідно указати комірку в полі, по якому потрібно відсортувати дані, і клацнути кнопку стандартної панелі інструментів Сортировать по возрастанию або кнопку Сортировать по убыванию відповідно.

Для сортування рядків по двох або трьох полях необхідно вибрати комірку в сортованому списку і виконати команду Данные  Сортировка і вказати параметри сортування.

рис. 7.7  Вікно Сортировка диапазона

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

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

рис. 7.8 Дані про продаж путівок

Для обчислення підсумків необхідно виконати такі дії:

1. Відсортувати таблицю по полю для якого розраховуються підсумки. (В даному випадку необхідно відсортувати таблицю по полю Прізвище агента).
2. Виконати команду ДанныеИтоги.
3. В діалоговому вікні Промежуточные итоги (рис. 7.9) зазначити параметри:

• зі списку При каждом изменении в виберіть назву стовпця, по якому здійснювалося сортування (Прізвище агента);
• зі списку Операция виберіть узагальнюючу операцію, яка виконуватиметься для кожної групи об’єктів: обчислення кількості, суми, середньої арифметичної тощо (Сумма);
• зі списку Добавить итоги по виберіть назву стовпця, до значень якого застосовуватиметься узагальнююча операція (Кількість путівок та Сума замовлення).

рис. 7.9 Вікно Промежуточные итоги

4. Клацніть кнопку Ok, і під кожною групою об’єктів буде відображено узагальнене значення (рис. 7.10).

рис. 7.10  Табличні та підсумкові значення таблиці

Натиснувши на кнопку зліва можна переглядати тільки розраховані підсумки і будувати по ним діаграми (див. рис. 7.11 і 7.12)

рис. 7.11  Підсумкові значення таблиці

рис. 7.12 Діаграма по  підсумковим значенням  таблиці

Щоб видалити з таблиці підсумкову інформацію, слід виділити всю таблицю разом з підсумками, виконати команду ДанныеИтоги й у вікні Промежуточные итоги клацнути кнопку .

Завдання для практичного виконання

Увага! Під час роботи з комп’ютером дотримуйтеся вимог безпеки життєдіяльності та санітарно-гігієнічних норм.

Завдання на 4-6  бали

1. Скопіюйте файл ДО ЛПЗ 7(13).xls до власної папки та відкрийте його.
2.    На листі Оригінал виконайте розрахунок в стовпці Сума замовлення (В комірку G2 введіть формулу =E2*F2)
3.    Задайте грошовий формат діапазону клітинок F2:G19.
4.    Виконайте копіювання вмісту листа Оригінал на новий лист. Дайте назву листу: Сортування 1. На листі Сортування 1 виконайте сортування таблиці по полю Прізвище агента.
5.    Виконайте копіювання вмісту листа Оригінал на новий лист. Дайте назву листу: Сортування 2. На листі Сортування 2 виконайте сортування таблиці по полю Форма розрахунку.
6.    Виконайте копіювання вмісту листа Оригінал на новий лист. Дайте назву листу: Сортування 3. На листі Сортування 3 виконайте сортування таблиці по полям Прізвище агента, Дата замовлення та Кількість путівок.
7.    Виконайте копіювання вмісту листа Оригінал на новий лист. Дайте назву листу: Автофільтр 1. На листі Автофільтр 1 виберіть дані про путівки, які продані агентом Терещенко В.

              ДанныеФильтрАвтофильтр

8.    Виконайте копіювання вмісту листа Оригінал на новий лист. Дайте назву листу: Автофільтр 2. На листі Автофільтр 2 виберіть дані про путівки, ціна яких перевищує 1000 грн.
9.    Виконайте копіювання вмісту листа Оригінал на новий лист. Дайте назву листу: Розширений фільтр 1. На листі Розширений фільтр 1 виберіть дані про пансіонати, які мають назву Золотой колос або ціна путівки в які перевищує 2000 грн.

Див. теоретичні відомості ст. 45-46.

10. Виконайте копіювання вмісту листа Оригінал на новий лист. Дайте назву листу: Підсумки 1. На листі Підсумки 1 за допомогою проміжних підсумків визначити кількість проданих путівок кожним агентом. Побудуйте діаграму по розрахованим підсумкам.

              Див. теоретичні відомості ст. 48-49

Завдання на 7-8 балів

11. Виконайте копіювання вмісту листа Оригінал на новий лист. Дайте назву листу: Автофільтр 3. На листі Автофільтр 3 виберіть дані про путівки, які продані агентом Думанов И., назва пансіонату – Песчаное, форма розрахунку – кредит.
12. Виконайте копіювання вмісту листа Оригінал на новий лист. Дайте назву листу: Автофільтр 4. На листі Автофільтр 4 дати відповідь на таке питання: по якому пансіонату не було оплати, якщо замовлення було зроблено в інтервалі з 10.07 по 17.07.12.
13. Виконайте копіювання вмісту листа Оригінал на новий лист. Дайте назву листу: Розширений фільтр 2. На листі Розширений фільтр 2 виберіть дані про путівки, які були замовлені 03.07.12 або 10.07.12 та не були сплачені.
14. Виконайте копіювання вмісту листа Оригінал на новий лист. Дайте назву листу: Підсумки 2. На листі Підсумки 2 за допомогою проміжних підсумків визначити для кожного пансіонату кількість замовлених путівок. Побудувати діаграму за зразком.

Завдання на 9-10 балів

15. Виконайте копіювання вмісту листа Оригінал на новий лист. Дайте назву листу: Умовне форматування.
16. На листі Умовне форматування до таблиці застосувати форматування:

• в стовпчику Сплачено комірки, які містять слово ні автоматично виділялися жовтим кольором;
• в стовпчику Форма розрахунку комірки, які містять слово готівка автоматично виділялися червоним кольором;
• в стовпчику Ціна путівки комірки, які містять вартість путівок від 1000 до 2000 грн. автоматично виділялися зеленим кольором;

              Див. теоретичні відомості ст. 46-47

17. Виконайте копіювання вмісту листа Оригінал на новий лист. Дайте назву листу: Підсумки 3. На листі Підсумки 3 за допомогою проміжних підсумків з’ясуйте долю кожного виду Форми розрахунку по сумі замовлених путівок на фірмі. Порівняйте результат зі зразком

Завдання на 11-12 балів

18. Виконайте копіювання вмісту листа Оригінал на новий лист. Дайте назву листу: Розрахунок комісії. На листі Розрахунок комісії додати стовпчик Комісія та розрахувати суму комісії по сумі замовлення для кожного агента за умовою:

якщо кількість путівок < 10 - 0%

якщо кількість путівок від 10 до 30 - 12 %

якщо кількість путівок від 31 до 50 - 14 %

якщо кількість путівок > 50 - 20 %

Використати вкладену функцію Если.

19. В стовпчику Комісія комірки, з сумою < 2000 повинні виділятися червоним кольором, з сумою від 2000 до 5000 – синім, з сумою >5000  - жовтим.
20. Розрахувати підсумки – сума комісії по кожному агенту. Побудувати по підсумкам циліндричну діаграму. На діаграмі розмістити заголовок, підписи даних, легенду ліворуч, колір фону діаграми з використанням градієнтної заливки.
21. Закінчіть роботу.

Виконайте тестові завдання на ПК