Розширений фільтр в excel: приклади. Як зробити розширений фільтр в excel і як ним користуватися?
Багато співробітників всіляких організацій, кому доводиться якимось чином працювати з Mircosot Excel, будь то звичайні бухгалтери або аналітики, часто стикаються з необхідністю вибору ряду значень з величезного масиву даних. Для спрощення виконання даного завдання і була створена система фільтрації.
Звичайний і розширений фільтр
В Excel представлений найпростіший фільтр, який запускається з вкладки «Дані» - «Фільтр» (Data - Filter в англомовній версії програми) або за допомогою ярлика на панелі інструментів, схожого на конусоподібну воронку для переливання рідини в ємності з вузьким горлечком.
Відео: Розширений фільтр в Excel
Для більшості випадків даний фільтр є цілком оптимальним варіантом. Але, якщо необхідно здійснити відбір по великій кількості умов (та ще й за кількома стовпцями, рядками і осередкам), багато хто задається питанням, як зробити розширений фільтр в 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
Перша умова дозволяє формувати результат на місці, відведеному під осередку вихідного діапазону. Друга умова дозволяє сформувати список результатів в окремому діапазоні, який слід вказати в полі «Помістити результат в діапазон». Користувач вибирає зручний варіант, наприклад, перший, вікно «Розширений фільтр» в Excel закривається.
Грунтуючись на введених даних, фільтр сформує наступну таблицю.
Відео: Майстер-клас: Розширений фільтр в Excel
| A | B | C | D | E | F | |
| 1 | Продукція | Найменування | місяць | День тижня | Місто | Замовник |
| 2 | овочі | Краснодар | "Ашан" | |||
| 3 | ||||||
| 4 | Продукція | Найменування | місяць | День тижня | Місто | Замовник |
| 5 | овочі | помідор | лютий | понеділок | Краснодар | "Ашан" |
При використанні умови "Копіювати результат в інше місце» значення з 4 і 5 рядків відобразяться в заданому користувачем діапазоні. Вихідний діапазон ж залишиться без змін.
Зручність використання
Описаний спосіб не зовсім зручний, тому для удосконалення зазвичай використовують мову програмування VBA, за допомогою якого складають макроси, що дозволяють автоматизувати розширений фільтр в Excel.
Якщо користувач має знання VBA, рекомендується вивчити ряд статей даної тематики і успішно реалізовувати задумане. При зміні значень осередків рядка 2, відведеної під Excel розширений фільтр, діапазон умов буде змінюватися, настройки скидатися, відразу запускатися заново і в необхідному діапазоні будуть формуватися потрібні відомості.
складні запити
Крім роботи з точно заданими значеннями, розширений фільтр в Excel здатний обробляти і складні запити. Такими є введені дані, де частина знаків замінена підстановочних символами.
Таблиця символів для складних запитів приведена нижче.
| приклад запиту | результат | |
| 1 | п * | повертає всі слова, що починаються з літери П:
|
| 2 | = | результатом буде виведення всіх порожніх клітинок, якщо такі є в рамках заданого діапазону. Буває дуже корисно вдаватися до даної команді з метою редагування вихідних даних, адже таблиці можуть з плином часу змінюватися, вміст деяких осередків віддалятися за непотрібністю або неактуальністю. Застосування даної команди дозволить виявити порожні клітинки для їх подальшого заповнення, або реструктуризації таблиці. |
| 3 | lt; gt; | виведуться всі непусті осередки. |
| 4 | * Ію * | всі значення, де є буквосполучення «ію»: червень, липень. |
| 5 | = ????? | всі комірки стовпчика, що мають чотири символи. За символи прийнято вважати літери, цифри і знак пробілу. |
Варто знати, що значок * може означати будь-яку кількість символів. Тобто при введеному значенні «п *» будуть повернуті всі значення, незалежно від кількості символів після букви «п».
Відео: Використання розширеного фільтра в таблиці Excel
Знак «?» Має на увазі тільки один символ.
Зв`язки 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. Вони дозволяють запустити значну кількість фільтрів, що сприяють відбору значень і виведення їх у відповідні діапазони.
Макроси успішно замінюють багатогодинна праця зі складання зведеної, періодичної та іншої звітності, замінюючи тривалий час аналізу величезних масивів всього лише односекундного кліком.
Використання макросів виправдано і незручно. Той, хто стикався з необхідністю застосування, завжди знайде при бажанні досить матеріалу для розвитку своїх знань і пошуку відповідей на питання, що цікавлять.
