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

Багато користувачів ПК добре знайомі з пакетом продуктів для роботи з різного роду документами під назвою Microsoft Office. Серед програм цієї компанії є MS Excel. Дана утиліта призначена для роботи з електронними таблицями. У програмі присутній так званий розширений фільтр в Excel. Він призначений для зручності роботи і створення таблиць. Про нього і піде мова в нашій статті. Ми розповімо далі, як його робити і як користуватися.

Що це за функція? Опис

Що означає в Excel розширений фільтр? Це функція, яка дозволяє розмежовувати вибрані дані (за стовпчиками в "Ексель") щодо введених вимог. Приміром, якщо у нас є електронна таблиця з відомостями про всіх учнів школи (зростання, вага, клас, стать тощо), то ми з легкістю зможемо виділити серед них, скажімо, всіх хлопчиків з ростом 160 з 8-го класу. Зробити це можна, використовуючи функцію "Розширений фільтр" в Excel. Про неї ми і будемо детально розповідати далі.

Що значить автофільтр?

Які особливості має звичайний і розширений фільтр? Спочатку розповімо про перший. Автофільтр (звичайний) призначений для розмежування даних за заздалегідь відомим критеріям, кількість яких визначено. Його використовують, коли є електронна таблиця незначних розмірів, по стовпцях і заданим параметрам. Якщо нам необхідно задати необмежену кількість критеріїв для фільтрації, необхідно вибирати розширений.

Як робити правильно?

Як зробити в Excel розширений фільтр? Щоб було зрозуміло, яким чином відбувається процедура і як вона робиться, розглянемо приклад. Інструкція з розширеною фільтрації електронної таблиці: Необхідно створити місце вище основної таблиці. Там і будуть розташовуватися результати фільтрації. Повинно бути достатній кількості місця для готової таблиці. Також потрібен ще один рядок. Вона буде розділяти відфільтровану таблицю від основної. В саму першу рядок звільненого місця скопіювати всю шапку (назви колонок) основної таблиці. Ввести необхідні дані для фільтрації в потрібний стовпчик. Зазначимо, що запис має виглядати наступним чином: = "= фильтруемое значення". Тепер необхідно пройти в розділ "Дані". В області фільтрації (значок у вигляді воронки) вибрати "Додатково" (знаходиться в кінці правого списку від відповідного знака). Далі у спливаючому віконці потрібно ввести параметри розширеного фільтра Excel. "Діапазон умов" і "Вихідний діапазон" заповнюються автоматично, якщо була виділена клітинка початку робочої таблиці. Інакше їх доведеться вводити самостійно. Натиснути Ок. Відбудеться вихід з налаштувань параметрів розширеної фільтрації. Після пророблених кроків в основній таблиці залишаться лише записи по заданому разграничивающему значенням. Щоб скасувати останню дію (фільтрацію), потрібно натиснути на кнопку "Очистити", яка знаходиться в розділі "Дані".

Робота з розширеним фільтром в "Ексель"

Як користуватися розширеним фільтром в Excel? Для того щоб зрозуміти, звернемося до даної функції повторно. Припустимо, у нас в електронній таблиці є колонка з містами. До цього ми вже робили розширену фільтрацію на місто Ростов. Тепер же хочемо в цьому ж документі додати до отриманих по місту Ростову ще й ті записи, які відповідають місту Самарі. Для цього необхідно: Розмістити умови розмежування (="Самара") під попереднім запитом (="=Ростов"). Викликати меню розширеного фільтра (розділ "Дані", вкладка "Фільтрація та сортування", вибрати в ній "Додатково"). Натиснути Ок. Після цього розширена фільтрація закриється в Excel. А на екрані з'явиться готова таблиця, яка складається із записів, у яких вказано місто Самара або Ростов. Важливо!
При роботі слід враховувати наступний дуже важливий момент. Всі введені критерії повинні знаходитися тільки в тих стовпцях, яким йде фільтрування. Вище основної таблиці. Після того як був вивчений основний метод розширеної фільтрації, можна приступати до вивчення всіх можливостей даної функції. Мається на увазі саме використання формул.

Розширена фільтрація. Основні правила використання при роботі "Ексель"

Правила використання: Критеріями відбору називаються результати вихідної формули. Результатом можуть бути тільки два значення: "ІСТИНА" або "БРЕХНЯ". За допомогою абсолютних посилань вказується вихідний діапазон фільтрованої таблиці. В результатах формули будуть показані тільки ті рядки, які отримують за підсумком значення "ІСТИНА". Значення рядків, які отримали за підсумками формули "БРЕХНЯ", не будуть висвітлюватися. За допомогою цих правил можна створювати будь-які формули з допустимими операндами і значеннями.

Приклад "Ексель 2010"

Розглянемо приклад розширеного фільтра Excel 2010 і використання в ньому формул. Приміром, розмежуємо значення якого-небудь стовпця з числовими даними по результату середнього значення (більше або менше).

Інструкція для роботи з розширеним фільтром в Excel за середнім значенням колонки: Для початку необхідно вибрати формулу середнього значення даних стовпця. Для цього потрібно вибрати діапазон результатів від початкової запису до кінцевої. Підсумувати їх і розділити на кількість рядків (записів). Після цього вибрати в розділі "Фільтрування та сортування" пункт "Додатково". Вписати потрібні дані (діапазонів). Після цього натиснути Ок. В результаті отримається таблиця, в якій є записи, що не перевищують середнього значення за заданим значенням. Таким чином можна використовувати розширену фільтрацію досить різноманітними способами. Зробити це можна, вдаючись до складання різного роду формул. Головне - не забувати вписувати критерії фільтрації над основною таблицею в стовпці, по якому буде проводитися розмежування. Увага! При роботі записи, які не пройшли критерій фільтрації, не видаляються. Вони просто не показуються (не відображаються). Розширена фільтрація призначена для того, щоб можна було скопіювати результат і вставити його куди-небудь в інше місце. Після цього необхідно натиснути на "Очистити" в розділі "Фільтрація та сортування". Тоді ваша вихідна таблиця повернеться на екран.

Автофільтр. Приклад використання

Автофільтр - це звичайний інструмент. Його можна застосувати, виключно задавши точні параметри. Наприклад, вивести всі значення таблиці, які перевищують значення 1000 ( < 1000), или показать точные данные, как было рассмотрено в примере с городами. Ним користуються набагато частіше, ніж розширеним. Однак параметрів для фільтрації, пропонованих автофільтром, часто буває недостатньо.

Плюси і мінуси розширеного фільтра у програмі "Ексель"

Так як розширена фільтрація - це додаток до звичайної, то в її використанні є свої переваги і недоліки. Їх ми зараз і розглянемо. Плюси розширеної фільтрації: можна використовувати формули. Мінуси розширеної фільтрації: щоб виконати дану операцію, потрібно витратити більше часу на розгляд з введенням діапазону і вихідних даних; для використання розширеного фільтрування з використанням формул необхідно знати і вміти користуватися правилами логіки та складання рівнянь MS Excel. Незважаючи на наявність мінусів, ця функція має все ж більшу кількість можливостей, ніж просто автофильтрация. Хоча при останній немає необхідності вводити щось своє, крім критерію, за яким буде проводитися виявлення значень.

Фільтрація за двома окремими критеріями. Як правильно зробити?

Розширена фільтрація дозволяє використовувати різні формули наявної електронної таблиці. Якщо вам необхідно розмежувати її спочатку по одному критерію, потім по іншому (окремо один для одного), необхідно: Створити місце для введення параметра фільтрування. Найзручніше залишати це місце над основною таблицею і не забувати копіювати шапку (надписів), щоб не заплутатися, в яку колонку вводити цей критерій. Ввести потрібний показник для фільтрації. Наприклад, всі записи, чиї значення стовпця більше 1000 (> 1000). Пройти у вкладку "Дані". В розділі "Фільтрація та сортування" вибрати пункт "Додатково". У віконці вказати діапазони розглянутих значень і клітинку зі значенням розглянутого критерію. Натиснути Ок. Після цього буде виведена відфільтрована за заданим критерієм таблиця. Скопіювати результат розмежування. Вставити відфільтровану таблицю куди-небудь убік на тому ж аркуші Excel. Можна скористатися іншою сторінкою. Вибрати "Очистити". Ця кнопка знаходиться у вкладці "Дані" в розділі "Фільтрація та сортування". Після її натискання відфільтрована таблиця повернуться в початковий вигляд. І можна буде працювати з нею. Далі необхідно виділити вільне місце для таблиці, яка буде відфільтрована. Потім потрібно скопіювати шапку (назви стовпців) основного поля і перенести їх у першу сходинку звільненого під відфільтровану структуру місця. Пройти у вкладку "Дані". В розділі "Фільтрація та сортування" вибрати "Додатково". У віконці, що відкрилося, вибрати діапазон записів (рядків), за якою буде проводитися фільтрація. Додати адресу комірки, в якій записаний критерій розмежування, наприклад, "місто Одеса". Натиснути Ок. Після цього відбудеться фільтрація за значенням "Одеса". Скопіювати відфільтровану таблицю і вставити її або на інший аркуш документа, або на тій же сторінці, але в стороні від основної. Знову натиснути на "Очистити". Все, готово. Тепер у вас є три таблиці. Основна, відфільтрована по одному значенню (>1000), а також та, що відфільтрована за іншим значенням (Одеса).

Невеликий висновок

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