Фільтрація - це швидкий спосіб виділення зі списку підмножини даних для подальшої роботи з ними. В результаті фільтрації на екран виводяться ті рядки списку, які або містять певні значення, або задовольняють деякому набору умов пошуку, так званого критерію. Решта записи ховаються і не беруть участі в роботі до скасування фільтра.
Виділене підмножина списку можна редагувати, форматувати, друкувати, використовувати для побудови діаграм.
Excel містить два варіанти фільтрації: автофильтр і посилений фільтр. Автофільтр здійснює швидку фільтрацію списку відповідно до вмісту комірок або відповідно до простим критерієм пошуку. Активізація автофильтра здійснюється командою Фільтр - Автофільтр меню ДАНІ (покажчик повинен бути встановлений всередині області списку). Головна рядок списку в режимі автофільтра містить в кожному стовпці кнопку зі стрілкою.
Ріс.124. Таблиця в режимі автофільтра
Клацання розкриває списки, елементи якого беруть участь у формуванні критерію. Кожне поле (стовпець) може використовуватися в якості критерію. Список містить наступні елементи:
1) ВСЕ - будуть обрані всі записи.
2) ПЕРШІ 10 - призначені для створення нового списку, що складається з 10 вихідних або максимальних значень попереднього списку. Число 10 встановлюється за умовчанням. При виборі цього елемента відкривається вікно Накладення умови за списком. в якому можна вказати число елементів в створюваному списку; визначити, які за значенням елементи слід вибирати (найбільші або найменші); встановити чисельне або відсоткове обмеження на кількість виведених елементів. Процентне - задається частка виведених елементів, що мають найбільші або найменші значення з усього списку.
3) УМОВА - призначені для створення призначеного для користувача критерію відбору при фільтрації. Вибір елемента відкриває вікно для користувача автофильтр. в якому можна задати дві умови відбору, об'єднуючи їх за допомогою логічних операторів І і АБО.
4) Елементи, що представляють собою не повторюються значення даного поля зі списку і призначені для формування критерію відбору.
5) ПОРОЖНІ - призначені для формування критерію відбору для тих записів зі списку, які не мають значення в даному полі (тобто комірки стовпчика порожні).
6) непорожньої - призначені для формування критерію відбору тих записів зі списку, які мають значення в даному полі.
Елементи ПОРОЖНІ і непорожньої можна використовувати, тільки якщо в стовпці містяться порожні клітинки.
Встановлений фільтр можна видалити. Щоб видалити фільтр з одного стовпчика списку, слід вибрати в списку елементів елемент ВСЕ. Щоб видалити фільтри з усіх стовпців списку, необхідно виконати команду Фільтр - Відобразити все меню ДАНІ. Щоб видалити автофильтр зі списку, необхідно повторно виконати команду Фільтр - Автофільтр меню ДАНІ.
Розширений фільтр призначений для фільтрації списку відповідно до заданих для користувача критеріями. На відміну від автофильтра, критерії розширеного фільтра формуються і розташовуються в області робочого листа. Перевагами цього способу є:
- можливість збереження критеріїв і їх багаторазового використання;
- можливість оперативного внесення змін до критеріїв відповідно до потреб;
- можливість мати у своєму розпорядженні результати фільтрації в будь-якій області робочого листа.
Мал. 125. Діалогове вікно Розширений фільтр з меню Дані \ Фільтр \ Розширений фільтр
Таким чином, розширений фільтр може бути застосований, якщо, по-перше, список містить заголовну рядок, тобто стовпці списку мають заголовки, по-друге, в окремій області робочого листа попередньо сформований критерій відбору. Критерій відбору рекомендується розташовувати до списку або після нього і відокремлювати від списку символом нового рядка. Критерій відбору повинен складатися як мінімум з двох рядків. Перший рядок містить заголовки стовпців, поля яких визначають критерії відбору. Другий рядок містить умови відбору.
Фільтрація списку за допомогою посиленого фільтра виконується командою Фільтр - Розширений фільтр меню ДАНІ. У вікні команди Розширений фільтр слід вказати:
1) В поле введення Вихідний діапазон - діапазон комірок, що містять список.
2) В поле введення Діапазон умов - діапазон комірок, що містять критерій відбору.
3) В полі введення Помістити результат в діапазон - верхнє ліве вічко області, починаючи з якої буде виведений результат фільтрації.
4) За допомогою перемикача Обробка визначити розташування результатів фільтрації на робочому аркуші:
- Фільтрувати список на місці - означає, що список залишається на місці, непотрібні рядки ховаються;
- Скопіювати результати в інше місце - дозволяє розташувати результати фільтрації в іншій області робочого листа.
5) Параметр Тільки унікальні записи - означає, що вибірка повинна містити тільки унікальні записи відповідно до критеріїв відбору.
При формуванні критерію відбору розширеного фільтра можливі наступні варіанти:
1) Критерій відбору містить одну або декілька умов, що накладаються на один стовпець (одне поле). Якщо критерій містить кілька умов, то вони зв'язуються логічною операцією АБО.
2) Критерій відбору містить кілька умов, що накладаються на декілька стовпців (полів) одночасно. Тут можливі наступні варіанти:
а) необхідно накласти кілька умов відбору на декілька стовпців, причому ці умови повинні зв'язуватися логічною операцією АБО. Тоді умови відбору задаються в різних рядках критерію.
б) необхідно одночасно накласти кілька умов відбору на кілька полів, причому умови відбору повинні бути пов'язані логічною операцією І. Тоді всі умови задаються в одному рядку критерію.
в) необхідно кілька умов накласти на кілька полів, причому зв'язуватися вони можуть обома логічними операціями І і АБО.
3) Обчислюваний критерій. Умови відбору можуть містити формулу. Отримане в результаті обчислення формули значення братиме участь в порівнянні. Правила формування обчислюваного критерію наступні:
- в діапазоні критерію не можна вказувати імена полів. Слід ввести нове ім'я заголовка або залишити осередок порожній;
- при створенні формул обчислюваних критеріїв слід використовувати перший рядок списку (не рядки заголовків), тобто перший осередок в порівнюваних стовпці.
- якщо у формулі використовуються посилання на клітинки списку, вони задаються, як відносні;
- якщо у формулі використовуються посилання на клітинки поза списком, вони задаються, як абсолютні;
- обчислювані критерії можна поєднувати з невичісляемимі;
- годі було звертати уваги на результат, що видається формулою в області критерію (зазвичай ІСТИНА або БРЕХНЯ).
Ще роботи з інформатики