В Excel розширений фільтр: приклади. Як зробити розширений фільтр в Excel і як ним користуватися?

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

Звичайний і розширений фільтр

В Excel представлений найпростіший фільтр, який запускається з вкладки «Дані» — «Фільтр» (Data — Filter в англомовній версії програми) або за допомогою ярлика на панелі інструментів, схожого на конусоподібну воронку для переливання рідини в ємності з вузьким горлечком.


Для більшості випадків цей фільтр є цілком оптимальним варіантом. Але, якщо необхідно здійснити відбір за великою кількістю умов (та ще й по декілька стовпців, рядків та клітинок), багато хто задається питанням, як зробити розширений фільтр в Excel. В англомовній версії називається Advanced filter.

Перше використання розширеного фільтра

В Excel велика частина роботи проходить з таблицями. По-перше, це зручно, по-друге, в одному файлі можна зберегти дані на кількох сторінках (вкладці). Над основною таблицею бажано створити кілька рядків, верхню з яких залишити для шапки, саме в дані рядка будуть вписуватися умови розширеного фільтра Excel. Надалі фільтр напевно буде змінено: якщо буде потрібно більше умов, завжди можна вставити у потрібному місці ще один рядок. Але бажано, щоб між осередками діапазону умов і осередками вихідних даних була одна незадіяна рядок.


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





A



B



C



D



E



F



1



Продукція



Найменування



Місяць



День тижня



Місто



Замовник



2



овочі















Краснодар



"Ашан"



3



























4



Продукція



Найменування



Місяць



День тижня



Місто



Замовник



5



фрукти



персик



січень



понеділок



Москва



"Пятерочка"



6



овочі



помідор



лютий



понеділок



Краснодар



"Ашан"



7



овочі



огірок



березень



понеділок



Ростов-на-Дону



"Магніт"



8



овочі



баклажан



квітень



понеділок



Казань



"Магніт"



9



овочі



буряк



травень



середа



Новоросійськ



"Магніт"



10



фрукти



яблуко



червень



четвер



Краснодар



"Бакаль"



11



зелень



кріп



липень



четвер



Краснодар



"Пятерочка"



12



зелень



петрушка



серпень



п'ятниця



Краснодар



"Ашан"

Застосування фільтра

У наведеній таблиці рядки 1 і 2 призначені для діапазону умов, рядки з 4 по 7 - для діапазону вихідних даних. Для початку слід ввести в рядок 2 відповідні значення, від яких буде відштовхуватися в Excel розширений фільтр. Запуск фільтра здійснюється з допомогою виділення клітинок вихідних даних, після чого необхідно вибрати вкладку «Дані» і натиснути кнопку «Додатково» (Data — Advanced відповідно). У вікні відобразиться діапазон виділених осередків в полі «Вихідний діапазон». Згідно наведеному прикладу, рядок приймає значення «$A$4:$F$12». Поле «Діапазон умов» має заповнитися значеннями «$A$1:$F$2». Віконце також містить дві умови:

фільтрувати список на місці;

скопіювати результат в інше місце.

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





A



B



C



D



E



F



1



Продукція



Найменування



Місяць



День тижня



Місто



Замовник



2



овочі















Краснодар



"Ашан"



3



























4



Продукція



Найменування



Місяць



День тижня



Місто



Замовник



5



овочі



помідор



лютий



понеділок



Краснодар



"Ашан"

При використанні умови «Скопіювати результат в інше місце» значення 4 і 5 рядків відобразяться в заданому користувачем діапазоні. Вихідний діапазон ж залишиться без змін.

Зручність використання

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

Складні запити

Крім роботи з точно заданими значеннями, в Excel розширений фільтр здатний обробляти складні запити. Такими є введені дані, де частина знаків замінена символи символами. Таблиця символів для складних запитів наведена нижче.





Приклад запиту



Результат



1



п*



повертає всі слова, що починаються з літери П:

персик, помідор, петрушка (якщо ввести в комірку B2);

Пятерочка (якщо ввести в комірку F2).

2



=



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



3



<>



виведуться всі непорожні осередку.



4



*ію*



всі значення, де є буквосполучення «січ»: червень, липень.



5



= ?????



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

Варто знати, що значок * може означати будь-яку кількість символів. Тобто при введеному значенні «п*» будуть повернуті всі значення, незалежно від кількості символів після літери «п». Знак «?» передбачає тільки один символ.

Зв'язки OR та AND

Слід знати, що відомості, задані одним рядком в «Діапазоні умов», розцінюються записаними у зв'язку логічним оператором (AND). Це означає, що кілька умов виконуються одночасно.
Якщо ж дані записані в один стовпець, розширений фільтр в Excel розпізнає їх пов'язаними логічним оператором (OR). Таблиця значень прийме наступний вигляд:





A



B



C



D



E



F



1



Продукція



Найменування



Місяць



День тижня



Місто



Замовник



2



фрукти























3



овочі























4



























5



Продукція



Найменування



Місяць



День тижня



Місто



Замовник



6



фрукти



персик



січень



понеділок



Москва



"Пятерочка"



7



овочі



помідор



лютий



понеділок



Краснодар



"Ашан"



8



овочі



огірок



березень



понеділок



Ростов-на-Дону



"Магніт"



9



овочі



баклажан



квітень



понеділок



Казань



"Магніт"



10



овочі



буряк



травень



середа



Новоросійськ



"Магніт"



11



фрукти



яблуко



червень



четвер



Краснодар



"Бакаль"

Зведені таблиці

Ще один спосіб фільтрування даних здійснюється з допомогою команди Вставка — Таблиця Зведена таблиця» (Insert Table — PivotTable в англомовній версії). Згадані таблиці аналогічно працюють з виділеним заздалегідь діапазоном даних і відбирають унікальні значення, щоб надалі піддати аналізу. На ділі це виглядає, як робота з випадаючим списком унікальних полів (наприклад, прізвищ співробітника компанії) і діапазоном значень, які видаються при виборі унікального поля. Незручність використання зведених таблиць полягає в необхідності ручного коректування вихідних даних при зміні таких.

Висновок

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