Для переважної більшості користувачів Excel, коли в голові виникає слово «фільтрування даних», лише звичайний класичний фільтр із вкладки Дані – Фільтр (Дані — Фільтр):
Такий фільтр, безсумнівно, звична річ, і для більшості випадків він підійде. Однак бувають ситуації, коли потрібно виконати фільтрацію за великою кількістю складних умов відразу в кількох стовпцях. Звичайний фільтр тут не дуже зручний і хочеться чогось потужнішого. Таким інструментом може бути вдосконалений фільтр, особливо з невеликою «доробкою напилком» (за традицією).
Основа
Для початку вставте кілька порожніх рядків над таблицею даних і скопіюйте туди заголовок таблиці – це буде діапазон умов (виділено жовтим для ясності):
Між жовтими клітинками та вихідною таблицею має бути принаймні один порожній рядок.
Саме в жовті комірки потрібно ввести критерії (умови), за якими потім буде здійснюватися фільтрація. Наприклад, якщо вам потрібно вибрати банани в московському «Ашані» в III кварталі, то умови будуть виглядати так:
Для фільтрації виберіть будь-яку комірку в діапазоні з вихідними даними, відкрийте вкладку дані І натисніть кнопку Додатково (Дані — Додатково). У вікні, що відкриється, діапазон з даними вже повинен бути автоматично введений і залишиться тільки вказати діапазон умов, тобто A1:I2:
Зверніть увагу, що діапазон умов не можна виділяти «з запасом», тобто не можна виділяти зайві порожні жовті рядки, оскільки порожня клітинка в діапазоні умов сприймається Excel як відсутність критерію, а ціла порожня рядок як запит на відображення всіх даних без розбору.
перемикач Скопіюйте результат в інше місце дозволить відфільтрувати список не прямо на цьому аркуші (як у звичайному фільтрі), а вивантажити вибрані рядки в інший діапазон, який потім потрібно буде вказати в полі Помістіть результат у діапазон. У цьому випадку ми не використовуємо цю функцію, залишаємо Список фільтрів на місці і натисніть кнопку OK. Вибрані рядки будуть відображені на аркуші:
Додавання макросу
«Ну де ж тут зручність?» запитаєте ви і будете праві. Потрібно не тільки руками ввести умови в жовті комірки, а й відкрити діалогове вікно, ввести туди діапазони, натиснути OK. Сумно, погоджуюсь! Але «все змінюється, коли вони приходять ©» – макроси!
Роботу з розширеним фільтром можна значно прискорити та спростити за допомогою простого макросу, який автоматично запускатиме розширений фільтр під час введення умов, тобто зміни будь-якої жовтої клітинки. Клацніть правою кнопкою миші вкладку поточного аркуша та виберіть команду Вихідний текст (Вихідний код). У вікні, що відкриється, скопіюйте та вставте наступний код:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:I5")) Is Nothing Then On Error Resume Next ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Діапазон("A1").CurrentRegion End If End Sub Ця процедура запускатиметься автоматично, коли буде змінено будь-яку клітинку на поточному аркуші. Якщо адреса зміненої комірки потрапляє в жовтий діапазон (A2:I5), то цей макрос видаляє всі фільтри (якщо такі є) і повторно застосовує розширений фільтр до таблиці вихідних даних, починаючи з A7, тобто все буде відфільтровано миттєво, негайно після введення наступної умови:
Так все набагато краще, правда? 🙂
Реалізація складних запитів
Тепер, коли все фільтрується на льоту, ми можемо трохи заглибитися в нюанси і розібрати механізми більш складних запитів у розширеному фільтрі. Окрім введення точних збігів, ви можете використовувати різні символи узагальнення (* та ?) і знаки математичної нерівності в ряді умов для здійснення приблизного пошуку. Регістр символів не має значення. Для наочності я зведено всі можливі варіанти в таблицю:
| критерій | Результат |
| гр* або гр | усі клітинки, починаючи з GrIe Grвухо, Grбадьяк, Grанат і т.п. |
| = цибуля | всі клітинки точно і тільки зі словом Лук, тобто точний збіг |
| *лів* або *лів | клітини, що містять Лів як підкреслити, тобто ОЛівЩо, Лівep, ЗаЛів і т.п. |
| =p*v | слова, що починаються з П і закінчується на В ie Ппершийв, Пефірв і т.п. |
| а*с | слова, що починаються з А і далі містить СIe Апелсin, АНанас, Asai і т.п. |
| =*s | слова, що закінчуються на С |
| =???? | всі комірки з текстом з 4 символів (букви або цифри, включаючи пробіли) |
| =m??????n | усі комірки з текстом із 8 символів, починаючи з М і закінчується на НIe Мandariн, Мнеспокійн і т.п. |
| =*n??a | всі слова, що закінчуються на А, де 4-та літера з кінця НIe Проміньнikа, Занozа і т.п. |
| >=e | всі слова, що починаються з Э, Ю or Я |
| <>*о* | всі слова, які не містять букви О |
| <>*вич | всі слова, крім тих, що закінчуються на ВІЛ (наприклад, фільтрувати жінок за іменем по батькові) |
| = | всі порожні клітинки |
| <> | всі непорожні клітинки |
| > = 5000 | усі клітинки зі значенням більше або рівним 5000 |
| 5 або =5 | усі клітинки зі значенням 5 |
| > = 3/18/2013 | усі комірки з датою після 18 (включно) |
Тонкі моменти:
- Знак * означає будь-яку кількість будь-яких символів, а ? – будь-який символ.
- Логіка обробки текстових і числових запитів дещо відрізняється. Так, наприклад, комірка умови з цифрою 5 не означає пошук усіх чисел, що починаються з п’ятірки, а комірка умови з літерою B дорівнює B*, тобто шукатиме будь-який текст, що починається з літери B.
- Якщо текстовий запит не починається зі знака =, то можна подумки поставити * в кінці.
- Дати потрібно вводити в американському форматі місяць-день-рік і через дріб (навіть якщо у вас є Excel і регіональні налаштування).
Логічні сполучники І-АБО
Умови, записані в різних клітинках, але в одному рядку, вважаються зв'язаними між собою логічним оператором И (І):
Ті. фільтр банани у мене в третьому кварталі, саме в москві і заодно з ашану.
Якщо потрібно зв’язати умови за допомогою логічного оператора OR (АБО), то їх просто потрібно ввести в різні рядки. Наприклад, якщо нам потрібно знайти всі замовлення менеджера Воліна на московські персики і всі замовлення на цибулю в третьому кварталі в Самарі, то це можна вказати в ряді умов наступним чином:
Якщо потрібно накласти дві або більше умов на один стовпець, то можна просто продублювати заголовок стовпця в діапазоні критеріїв і ввести під ним другий, третій і т.д. умови. Так, наприклад, ви можете вибрати всі транзакції з березня по травень:
Загалом, після «кінчання з файлом» розширений фільтр виходить цілком пристойним інструментом, місцями не гірше класичного автофільтра.
- Суперфільтр на макросах
- Що таке макроси, куди і як вставити код макросу у Visual Basic
- Розумні таблиці в Microsoft Excel