Прості й розширені фільтри. Обчислення підсумків

Перегляньте відеоматеріал.

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

Фільтрування даних в Excel — це відображення в таблиці тільки тих рядків, дані яких відповідають певним умовам (умовам фільтрування).

Автоматичні фільтри

Найбільш простим способом включення режиму фільтрування є на вкладці Дані натиснути кнопку Фільтр.

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

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

Якщо потрібно відобразити об’єкти, певний параметр яких має певне значення, це значення слід вибрати у списку значень.

Приклад:

Відфільтрувати рядки таблиці, в яких значення поля Країна дорівнює Україна.

Як видно, у стовпці № відсутні деякі номери, що підтверджує про фільтрацію даних у таблиці. Так само зверніть увагу, як змінилась кнопка випадаючого списку на стовпці Країна. Оскільки фільтрація відбулася по цьому стовпцю, Excel зазначив його  значком воронки. Тепер якщо нам потрібно повернути початковий вигляд списку, ми знаємо, по яких стовпчиках потрібно змінити налаштування фільтрації. Скинути фільтр можна повторним натисканням кнопки Фільтр.

Якщо потрібно відобразити об’єкти, певний параметр яких задовольняє деякій умові, слід обрати команду Фільтри чисел (для параметрів рядкового типу — Текстові фільтри), у випадаючому меню вибрати тип умови.

Можна відмітити варіант Перші 10 і відобразити 10 найбільших (найменших) значень у списку; варіант вище середнього дозволяє відібрати значення, які перевищують середнє значення по стопцю. Варіанти логічних умов дозволяють задати спеціальну умову фільтрації у вікні Користувацький автофільтр.

Приклад:

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

Після встановлення нового фільтра в тому самому стовпці «старе» правило фільтрування анулюється.

Отже, побудуємо алгоритм використання інструменту Автофільтр:

1. Виділити будь—яку клітинку таблиці, що містить дані.
2. Викликати засіб Автофільтр одним зі способів.
3. Сформувати умови пошуку даних за допомогою вбудованих засобів.
Для створення складених умов скористатися вказівкою Користувацький фільтр.
4. Проаналізувати відображені дані.
5. Після аналізу даних скасувати дію фільтра — використати інструмент Очистити.

Розширений фільтр

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

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

.

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

• Таблиця має відповідати  вимогам до списків (див. п.«Упорядкування даних»): поля таблиці повинні мати заголовки (назви стовпців).

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

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

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

 
1. Скопіювати з таблиці заголовки стовпчиків, у яких здійснюється пошук, та вставити їх до першого порожнього рядка діапазону умов відбору. Діапазон умов не обов’язково має містити назви всіх полів таблиці з даними.
2. Ввести в рядки під заголовками діапазону умов необхідні умови відбору.
3. Виділити будь-яку клітинку таблиці з даними.
4. Виконати Дані  → Сортування й фільтрація→ Додатково.
5. У вікні Розширений фільтр встановити перемикач у положення Фільтрувати список на місці.
6. Ввести в полі Діапазон умов посилання на діапазон із записаними умовами відбору, що включає заголовки стовпчиків.

7. Натиснути ОК.

Приклад:

Відфільтрувати зі списку річок України річки, що впадають у Дніпро і мають протяжність в Україні 717 і 391 км.

У полі Вхідний діапазон має відобразитися: $A$1:$E$28.

У полі Діапазон умов уведемо посилання на діапазон $G$1:$H$3.

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

Для відображення всіх даних списку на екран потрібно обрати інструмент Очистити в групі Сортування й фільтрація на вкладці Дані.

Практичне завдання:

1. З'ясуй, скільки рядків з даними відобразиться в таблиці, якщо до діапазону В3:F11 застосувати розширений фільтр, діапазон умов якого зображено на малюнку.

   Зверни увагу: рядок заголовків враховувати не треба.

   

2. Знайди число рядків з даними, що відобразиться в таблиці, якщо до діапазону В3:В11 застосувати користувацький автофільтр, налаштування якого зображено на малюнку.

   Рядок заголовків не враховуй.

    

   

    

   Напиши число рядків:

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

   1) Запустіть програму Excel. Створіть таблицю за зразком.

   2) Застосуванням розширеного фільтра створіть нову таблицю, яка міститиме дані про вантажні автомобілі або такі, обсяг продажів яких перевищив 1000000.

   3) Для початкової таблиці, використовуючи проміжні підсумки, обчисліть сумарні обсяги продажів автомобілів кожного типу.

   4) Приховайте дані початкової таблиці, залишивши проміжні підсумки продажів автомобілів за типами.

   5) Збережіть файл з іменем Вправа 1 і відправте вчителю на перевірку.