Напевно, всі користувачі, які постійно працюють з Microsoft Excel, знають про таку корисної функціїцієї програми як фільтрація даних. Але не кожен в курсі, що існують також і розширені можливості цього інструменту. Розгляньмо, що вміє робити розширений фільтр Microsoft Excel і як ним користуватися.
Недостатньо відразу запустити розширений фільтр – для цього необхідно виконати ще одну умову. Далі ми розповімо про послідовність дій, які слід зробити.
Крок 1: Створення таблиці з умовами відбору
Щоб встановити розширений фільтр, потрібно створити додаткову таблицю з умовами відбору. Її шапка точно така, як у основної, яку ми, власне, і фільтруватимемо. Для прикладу ми розмістили додаткову таблицю над основною та пофарбували її комірки у помаранчевий колір. Хоча розміщувати її можна у будь-якому вільному місці і навіть на іншому аркуші.
Тепер вписуємо в додаткову таблицю відомості, які потрібно відфільтрувати з основної таблиці. У нашому конкретному випадку зі списку виданої працівникам заробітної плати ми вирішили обрати дані щодо основного персоналу чоловічої статі за 25.07.2016.
Крок 2: Запуск розширеного фільтра
Тільки після того, як додаткову таблицю створено, можна переходити до запуску розширеного фільтра.
Таким чином, можна дійти невтішного висновку, що розширений фільтр надає більше можливостей, ніж звичайна фільтрація даних. Але не можна не відзначити, що робота з цим інструментом менш зручна, ніж зі стандартним фільтром.
Фільтрація, безумовно, є одним із найзручніших і швидких способіввиділити з найбільшого списку даних саме те, що необхідно в даний момент. В результаті процесу роботи фільтра користувач отримає невеликий список із необхідних даних, з яким вже можна легко та спокійно працювати. Ці дані будуть відібрані згідно з певним критерієм, який можна настроювати самостійно. Природно, що з відібраними даними можна працювати повноцінно використовуючи всі інші можливості Excel 2010.
При роботі з таблицями, дані можна відбирати двома способами - використовувати користувальницький автофільтр в Excel, або орієнтуватися на невеликий набір базових функцій та формул. Другий варіант набагато простіше і легше, а отже саме з нього ми і почнемо наше знайомство з можливостями оновленого табличного процесора.
Отже, у вас є таблиця з масивом різних даних, які ви отримали на роботі, і тепер необхідно відібрати якісь певні значення. На щастя можна використовувати автофільтр в Excel, який залишить на екрані тільки ту інформацію, яка необхідна.
Тут ми бачимо основні функції фільтрації на вкладці «Головна». Також можна подивитися на вкладку «Дані», де запропонують розгорнутий варіант управління фільтрацією. Щоб упорядкувати дані, необхідно вибрати необхідний діапазон осередків, або, як варіант, просто позначити верхню комірку необхідного стовпця. Після цього необхідно натиснути кнопку «Фільтр», після чого праворуч у комірці з'явиться кнопочка з невеликою стрілочкою, що вказує вниз.
Фільтри можна спокійно прикріпити до всіх стовпців.
Це спростить сортування інформації для майбутньої обробки.
Тепер розглянемо меню кожного фільтра (вони будуть однакові):
— сортування за зростаючим або спадаючим («від мінімального до максимального значення» або навпаки), сортування інформації за кольором (так зване – користувальницьке);
- Фільтр за кольором;
- Можливість зняти фільтр;
— параметри фільтрації, до яких належать числові фільтри, текстові та дати (якщо такі значення є в таблиці);
— можливість «виділити все» (якщо зняти цей прапорець, то всі стовпці просто перестануть відображатися на аркуші);
— «назви стовпців», де видно всі стовпці, що використовуються в таблиці. Якщо зняти прапорець біля певних найменувань, вони також перестануть відображатися, але тут це можна робити вибірково.
Якщо застосувати будь-який фільтр, то зображення кнопочки зміниться і набуде вигляду вибраного вами фільтра.
Якщо говорити про числових фільтрів , то тут програма також пропонує достатньо велика кількістьнайрізноманітніших варіантів сортування існуючих значень. Це: «Більше», «Більше чи одно», «Рівне», «Менше чи одно», «Менше», «Не дорівнює», «Між зазначеними значеннями». Активуємо пункт «Перші 10» і з'являється віконце
Тут можна вибрати, скільки перших значень нас цікавить (просто цифра, яку можна вибрати за допомогою стрілочок, або вказати самостійно) і як їх необхідно сортувати (найменших, найбільших або взагалі відсоток від загальної кількості елементів).
Якщо вибрати пункт «Вище (або нижче) середнього», вашій увазі представлять ті рядки, значення яких відповідатимуть запиту. Обчислення середнього арифметичного відбувається автоматично, на основі даних зібраних зі стовпця.
Автофільтр, що налаштовується Excel 2010, як і було сказано, дає розширений доступ до параметрів фільтрації. З його допомогою можна задати умову (складається з 2 виразів або «логічних функцій» АБО/І), згідно з якою буде проведено відбір даних.
Текстові фільтри створені виключно для роботи з текстовими значеннями. Тут для відбору використовуються такі параметри, як: Містить, Не містить, Починається з ..., Закінчується на ..., а також Рівно, Не рівно. Їх налаштування досить схоже на налаштування будь-якого числового фільтра.
Давайте застосуємо одночасну фільтрацію за різними параметрами до різних стовпців нашого звіту щодо роботи складів. Отже, «Найменування» нехай починаються з «А», а ось у графі «Склад 1» зазначимо, щоб результат був більшим за 25. Результат такого відбору представлений нижче
Фільтрацію, коли в ній більше немає потреби, можна скасувати будь-яким можливих способів– використовувати комбінацію кнопок «Shift+Ctrl+L», натиснути кнопку «Фільтр» (вкладка «Головні», велика піктограма «Сортування та фільтр», що входить до групи «Редагування»). Або ж просто натиснути кнопку «Фільтр» на вкладці «Дані».
Звичайно, фільтрація є досить зручним елементом відбору даних в Excel 2010, однак часом може виникнути потреба зробити більше докладний відбірінформації, яка просто не передбачена стандартними функціями.
Припустимо, сьогодні нам потрібно провести фільтрацію з використанням певної умови, яке, своєю чергою, є об'єднанням умов для фільтрації одночасно декількох стовпців (їх може бути і більше двох). У цьому випадку можливе застосування тільки розширеного фільтра користувача, в якому умови можна об'єднати, використовуючи логічні функції «І / АБО».
Давайте розглянемо приклад, щоб краще уявити можливості фільтра. Припустимо, у нас є таблиця з пошуковими запитамиу системах «Яндекс» та «Google». Нам потрібно визначити, який із наявних запитів має позицію менше 10 у кожній із зазначених систем. Оскільки запитів може бути дуже багато, необхідно виконувати низку простих дій.
В окремі та повністю вільні осередки копіюємо назви стовпців, за якими ми збираємося здійснювати фільтрацію даних. Як було сказано вище, це буде "Видимість в Яндекс" і "Видимість в Google". Назви можна скопіювати в будь-які комірки, що стоять поруч, але ми зупинимо вибору на В10 і С10.
Саме під цими осередками будемо вводити умови майбутньої фільтрації. Якщо необхідно (а це нам необхідно) враховувати відразу обидві умови (ця логічна функція називається І), то умови фільтрації варто розташувати в одному рядку. Якщо необхідно враховувати лише одне (функція АБО), то умови необхідно розташувати у різних рядках.
Тепер шукаємо вкладку «Дані», «Сортування та фільтр» та натискаємо невелику піктограму «Додатково» і бачимо таке віконце
«Розширений фільтр» дозволяє вибрати один з можливих варіантів дій - фільтрувати список прямо тут або взяти отримані дані і скопіювати їх в інше, зручне вам, місце.
У Вихідному діапазоні необхідно вказати адресу (якщо програма не зробить цього самостійно) всього діапазону осередків, дані яких необхідно відфільтрувати. Це можна зробити або вручну вказавши необхідні адреси, або просто виділивши за допомогою миші межі необхідної таблиці.
"Діапазон умов", як ви здогадалися, містить адресу тих осередків, в яких зберігаються умови фільтрації та назви стовпців. Для нас це буде «В10: С12».
Якщо ви вирішили відійти від прикладу і вибрали функцію "скопіювати результат ...", то в третій графі необхідно вказати адресу діапазону тих осередків, куди програмі необхідно відправити дані фільтр, що пройшли. Тому ми також виберемо цю нагоду і вкажемо «А27:С27».
Підтверджуємо програму і якщо все було зроблено без помилок, а задані умови здійсненні, то ми побачимо ось такий результат
Успіхів у роботі.
Багато користувачів ПК добре знайомі з пакетом продуктів для роботи з різними документами під назвою 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, тоді робота з фільтром стає простою.
Microsoft Excel - це поширений і зручний інструмент для роботи з електронними таблицями. Широкі функціональніші можливостіроблять цю програму другою за популярністю після MS Word серед усіх офісних програм. Використовують її економісти, бухгалтери, вчені, студенти та представники інших професій, яким необхідно обробляти математичні дані.
Однією з найзручніших функцій у програмі є фільтрація даних. Розглянемо, як налаштувати та використовувати MS Excel фільтри.
Де в Excel фільтри – їх види
Знайти фільтри в цій програмі легко – потрібно відкрити головне меню або просто затиснути клавіші Ctrl+Shift+L.
Як задати фільтр у Excel
Основні функції фільтрації в Excel:
- відбір за кольором: дає можливість відсортувати дані щодо кольору шрифту або заливки,
- текстові фільтри в excel: дозволяють задати ті чи інші умови для рядків, наприклад: менше, більше, рівно, не одно та інші, а також задати логічні умови - і, або,
- числові фільтри: відсортують за числовими умовами, наприклад, нижче середнього, перші 10 та інші,
- ручний: відбір можна виконувати за обраними самостійно критеріями.
Користуватися ними нескладно. Необхідно виділити таблицю та вибрати в меню розділ з фільтрами, а потім уточнити, за яким критерієм буде проводитися відсівання даних.
Як використовувати розширений фільтр у Excel - як його налаштувати
Стандартний фільтр має істотний недолік - щоб згадати, які саме критерії відбору використані, потрібно відкривати меню. І тим більше це викликає незручності, коли поставлено не один критерій. З цієї точки зору зручніший розширений фільтр, який відображається окремою таблицею над даними.
ВІДЕО ІНСТРУКЦІЯ
Порядок налаштування:
- Створити таблицю з даними подальшої роботи з нею. У ній не повинно бути порожніх рядків.
- Створити таблицю з умовами відбору.
- Запустіть розширений фільтр.
Розглянемо приклад налаштування.
У нас є таблиця зі стовпцями Товар, Кількість та Ціна.
Наприклад, потрібно відсортувати рядки, назви товарів яких починаються зі слова «Цвяхи». Під цю умову потрапляють кілька рядків.
Таблиця з умовами розміститься в осередках А1: А2. Важливо вказати назву стовпця, де відбуватиметься відбір (осередок А1) і саме слово для відбору – Цвяхи (осередок А2).
Найзручніше розташовувати її над даними або збоку. Під нею теж не заборонено, але не завжди зручно, тому що періодично може виникати потреба додавати додаткові рядки. Відступіть хоча б один порожній рядок між двома таблицями.
Потім необхідно:
- виділити будь-яку з осередків,
- відкрити «Розширений фільтр» шляхом: Дані – Сортування та фільтр – Додатково,
- перевірити, що в полі «Вихідний діапазон» - туди має потрапити вся таблиця з інформацією,
- у «Діапазоні умов» необхідно задати значення осередків з умовою відбору, у цьому прикладі це діапазон А1:А2.
Після натискання кнопки «ОК» відбудеться відбір потрібної інформації, і у таблиці з'являться лише рядки з потрібним словом, у разі це «Цвяхи». Номери рядків, що залишилися, пофарбуються в синій колір. Щоб скасувати заданий фільтр, достатньо натиснути клавіші CTRL+SHIFT+L.
Також легко налаштувати відбір по рядках, що містять слово «Цвяхи» без урахування регістру. У діапазоні В1:В2 розмістимо стовпчик з новим критерієм відбору, не забувши вказати заголовок стовпця, в якому виконуватиметься відсів. У комірці В2 необхідно вказати таку формулу = цвяхи.
- виділити будь-яку з осередків таблиці,
- відкрити «Розширений фільтр»,
- перевірити, що у «Вихідний діапазон» потрапила вся таблиця з даними,
- у «Діапазоні умов» вказати В1: В2.
Після натискання «ОК» відбудеться відсівання даних.
Це самі прості приклади роботи з фільтрами в excel. У розширеному варіанті зручно задавати інші умови для відбору, наприклад, відсів з параметром «АБО», відсів з параметром «Цвяхи» і значенням у стовпці «Кількість» >40.
Як зробити фільтр в Excel по стовпцях
Інформацію в таблиці можна фільтрувати по стовпцях – одному чи кількох. Розглянемо з прикладу таблиці зі стовпцями «Місто», «Місяць» і «Продажи».
Якщо необхідно відсіяти дані по стовпцю з назвами міст за абеткою, потрібно виділити будь-яку з осередків цього стовпця, відкрити «Сортування» та «Фільтр» і вибрати параметр «АЯ». В результаті інформація з'явиться з урахуванням першої літери у назві міста.
Для отримання інформації за зворотним принципом необхідно скористатися параметром "ЯА".
Необхідно відсів інформації по місяцях, а також місто з великим обсягом продажів має стояти в таблиці вище міста з меншим обсягом продажів. Для вирішення завдання потрібно в «Сортування та фільтрі» вибрати параметр «Сортування». У вікні з налаштуваннями уточнити «Сортувати за» - «Місяць».
Далі потрібно додати другий рівень сортування. Для цього потрібно вибрати в «Сортуванні» – «Додати рівень» та вказати стовпець «Продаж». У колонці налаштувань "Порядок" вказати "За спаданням". Після натискання "ОК" відбудеться відбір даних за заданими параметрами.
ВІДЕО ІНСТРУКЦІЯ
Чому можуть не працювати фільтри в Excel
У роботі з таким інструментом, як фільтри, у користувачів часто виникають складності. Зазвичай вони пов'язані з порушенням правил використання тих чи інших налаштувань.
Проблема з фільтром за датою – одна з найпопулярніших. Виникає після вивантаження даних з облікової системи як масиву. При спробі фільтрації рядків по стовпцю, що містить дати, відсівання відбувається не за датою, а за текстом.
Рішення проблеми:
- виділити стовпець з датами,
- відкрити вкладку Excel у головному меню,
- вибрати кнопку «Комірки», у випадаючому списку вибрати параметр «Перетворити текст на дату».
До популярних помилок користувачів під час роботи з цією програмою також варто віднести:
- відсутність заголовків у стовпців (без них не працюватимуть фільтрація, сортування, а також цілий ряд інших важливих параметрів),
- наявність порожніх рядків та стовпців у таблиці з даними (це збиває систему сортування, Excel сприймає інформацію як дві різні незалежні один від одного таблиці),
- розміщення на одній сторінці кількох таблиць (зручніше розташовувати кожну таблицю на окремому аркуші),
- розміщення в кількох стовпцях даних одного типу,
- розміщення даних на декількох аркушах, наприклад, по місяцях або роках (обсяг роботи можна одразу множити на кількість аркушів з інформацією).
І ще одна критична помилка, яка дозволяє повною мірою використовувати можливості Excel – це застосування неліцензійного продукту.
Гарантувати його правильну роботу не можна, до того ж помилки з'являтимуться постійно. Якщо ви маєте намір використовувати цей інструмент обробки математичних відомостей на постійній основі, придбайте повноцінну версію програми.
Розширений фільтр в Excel надає ширші можливості управління даними електронних таблиць. Він більш складний у налаштуваннях, але значно ефективніший у дії.
За допомогою стандартного фільтра користувач Microsoft Excel може вирішити далеко не всі поставлені завдання. Немає візуального відображення застосованих умов фільтрації. Неможливо застосувати більше двох критеріїв відбору. Не можна фільтрувати дублювання значень, щоб залишити лише унікальні записи. Та й самі критерії схематичні та прості. Набагато багатший функціонал розширеного фільтра. Придивимося до його можливостей ближче.
Як зробити розширений фільтр у Excel?
Розширений фільтр дозволяє фільтрувати дані щодо необмеженого набору умов. За допомогою інструмента користувач може:
- поставити більше двох критеріїв відбору;
- скопіювати результат фільтрації на інший аркуш;
- задати умову будь-якої складності за допомогою формул;
- отримати унікальні значення.
Алгоритм застосування розширеного фільтра простий:
- Робимо таблицю з вихідними даними чи відкриваємо існуючу. Наприклад, так:
- Створюємо таблицю умов. Особливості: рядок заголовків повністю збігається з «шапкою» таблиці, що фільтрується. Щоб уникнути помилок, копіюємо рядок заголовків у вихідній таблиці і вставляємо цей самий аркуш (збоку, зверху, знизу) чи інший лист. Вносимо до таблиці умов критерії відбору.
- Переходимо на вкладку "Дані" - "Сортування та фільтр" - "Додатково". Якщо відфільтрована інформація повинна відобразитись на іншому аркуші (НЕ там, де знаходиться вихідна таблиця), то запускати розширений фільтр потрібно з іншого аркуша.
- У вікні «Розширеного фільтра» вибираємо спосіб обробки інформації (на цьому ж аркуші або на іншому), задаємо вихідний діапазон (табл. 1, приклад) і діапазон умов (табл. 2, умови). Рядки заголовків мають бути включені до діапазонів.
- Щоб закрити вікно "Розширеного фільтра", натискаємо ОК. Бачимо результат.
Верхня таблиця – результат фільтрації. Нижня табличка з умовами дана для наочності поряд.
Як користуватись розширеним фільтром у Excel?
Щоб скасувати дію розширеного фільтра, поставимо курсор у будь-якому місці таблиці і натиснемо клавіші Ctrl + Shift + L або «Дані» - «Сортування та фільтр» - «Очистити».
Знайдемо за допомогою інструмента «Розширений фільтр» інформацію про значення, що містять слово «Набір».
У таблицю умов внесемо критерії. Наприклад, такі:
Програма в даному випадку шукатиме всю інформацію щодо товарів, у назві яких є слово «Набір».
Для пошуку точного значення можна використовувати знак =. Внесемо до таблиці умов такі критерії:
Excel сприймає знак "=" як сигнал: зараз користувач задасть формулу. Щоб програма працювала коректно, у рядку формул має бути запис виду: =»=Набір обл.6 кл.»
Після використання «Розширеного фільтра»:
Відфільтруємо вихідну таблицю за умовою «АБО» для різних стовпців. Оператор "АБО" є і в інструменті "Автофільтр". Але там його можна використати в рамках одного стовпця.
У табличку умов введемо критерії відбору: =»=Набір обл.6 кл. (у стовпець «Назва») і =»
Зверніть увагу: критерії необхідно записати під відповідними заголовками в різних рядках.
Результат відбору:
Розширений фільтр дозволяє використовувати як критерій формули. Розглянемо приклад.
Відбір рядка із максимальною заборгованістю: =МАКС(Таблица1).
Таким чином ми отримуємо результати, як після виконання кілька фільтрів на одному аркуші Excel.
Як зробити кілька фільтрів у Excel?
Створимо фільтр за кількома значеннями. Для цього введемо в таблицю умов відразу кілька критеріїв відбору даних:
Застосуємо інструмент «Розширений фільтр»:
Тепер із таблиці з відібраними даними вилучимо нову інформацію, відібрану за іншими критеріями. Наприклад, лише відвантаження за 2014 рік.
Вводимо новий критерій у табличку умов та застосовуємо інструмент фільтрації. Вихідний діапазон - таблиця з відібраними за попереднім критерієм даними. Так виконується фільтр кількома стовпцями.
Щоб використати кілька фільтрів, можна сформувати декілька таблиць умов на нових аркушах. Спосіб реалізації залежить від поставленого користувачем завдання.
Як зробити фільтр у Excel по рядках?
Стандартними методами – ніяк. Програма Microsoft Excel відбирає дані лише у стовпцях. Тому потрібно шукати інші рішення.
Наводимо приклади рядкових критеріїв розширеного фільтра Excel:
- Перетворити таблицю. Наприклад, із трьох рядків зробити список із трьох стовпців і до перетвореного варіанту застосувати фільтрацію.
- Використовувати формули для відображення тих даних у рядку, які потрібні. Наприклад, зробити якийсь показник випадаючим списком. А в сусідній осередок ввести формулу, використовуючи функцію ЯКЩО. Коли зі списку вибирається певне значення, поруч з'являється його параметр.
Щоб навести приклад, як працює фільтр по рядках в Excel, створимо табличку:
Для списку товарів створимо список, що випадає:
Над таблицею з вихідними даними вставимо порожній рядок. У комірки введемо формулу, яка показуватиме, із яких стовпців береться інформація.
Поряд із списком, що випадає, осередок введемо таку формулу: Її завдання – вибирати з таблиці ті значення, які відповідають певному товару
Завантажити приклади розширеного фільтра
Таким чином, за допомогою інструмента «Випадаючий список» та вбудованих функцій Excelвідбирає дані у рядках за певним критерієм.
Фільтрування даних в Excel дозволить відобразити в стовпцях таблиці інформацію, яка цікавить користувача в конкретний момент. Вона значно полегшує процес роботи з великими таблицями. Ви зможете контролювати дані, які будуть відображатися в стовпці, так і виключені зі списку дані.
Якщо Ви робили таблицю Ексель через вкладку «Вставка» – «Таблиця», або вкладка «Головна» – «Форматувати як таблицю», то в такій таблиці, фільтр включений за замовчуванням. Відображається він у вигляді стрілочки, яка розташована у верхньому осередку справа боку.
Якщо Ви просто заповнили осередки даними, а потім відформатували їх у вигляді таблиці – фільтр потрібно увімкнути. Для цього виділіть весь діапазон осередків таблиці, включаючи заголовки стовпців, так як кнопочка фільтра розташовується у верхньому стовпці, і якщо виділити стовпець, починаючи з комірки з даними, то вона не буде ставитися до даних цього стовпця, що фільтрується. Потім перейдіть на вкладку «Дані» та натисніть кнопку «Фільтр».
У прикладі стрілочка фільтра знаходиться в заголовках, і це правильно - фільтруватимуться всі дані в стовпці, розташовані нижче.
Якщо Вас цікавить питання, як зробити таблицю в Ексель, перейдіть за посиланням і прочитайте статтю на цю тему.
Тепер давайте розглянемо, як працює фільтр Ексель. Наприклад скористаємося наступною таблицею. У ній три стовпці: «Назва продукту», «Категорія» та «Ціна», до них застосовуватимемо різні фільтри.
Натисніть стрілку у верхній комірці потрібного стовпця. Тут Ви побачите список неповторних даних зі всіх осередків, розташованих у цьому стовпці. Навпроти кожного значення стоятимуть галочки. Зніміть галочки з тих значень, які потрібно виключити зі списку.
Наприклад, залишимо в Категорії тільки фрукти. Знімаємо галочку в полі «овоч» та натискаємо «ОК».
Для стовпців таблиці, до яких застосований фільтр, у верхній комірці з'явиться відповідний значок.
Якщо Вам потрібно видалити фільтр даних в Excel, натисніть у комірці на значок фільтра та виберіть з меню «Видалити фільтр з (назва стовпця)».
Зробити фільтр даних у Excel можна у різний спосіб. Розрізняють текстові та числові фільтри. Застосовуються вони відповідно, якщо у осередках стовпця записаний або текст, чи числа.
Застосуємо "Числовий фільтр" до стовпця "Ціна". Клацаємо на кнопку у верхньому осередку і вибираємо відповідний пункт з меню. З випадаючого списку можна вибрати умову, яку потрібно застосувати до даних стовпця. Наприклад, відобразимо всі товари, ціна яких нижча за «25». Вибираємо "менше".
У відповідному полі вписуємо потрібне значення. Для фільтрації даних можна застосовувати декілька умов, використовуючи логічне «І» та «АБО». При використанні «І» – повинні дотримуватися обох умов, при використанні «АБО» – одна із заданих. Наприклад, можна задати: "менше" - "25" - "І" - "більше" - "55". Таким чином ми виключимо товари з таблиці, ціна яких знаходиться в діапазоні від 25 до 55.
Таблиця з фільтром по стовпцю "Ціна" нижче 25.
«Текстовий фільтр» у таблиці прикладу можна застосувати до стовпця «Назва продукту». Натискаємо на кнопку фільтра в стовпці і вибираємо з меню однойменний пункт. У списку, що відкривається, для прикладу використовуємо «починається з».
Залишимо в таблиці продукти, що починаються з «ка». У наступному вікні в полі пишемо: «ка*». Натискаємо "ОК".
"*" у слові, замінює послідовність знаків. Наприклад, якщо задати умову "містить" - "с * л", залишаться слова стіл, стілець, сокіл і так далі. "?" замінить будь-який знак. Наприклад, «бітон» - батон, бутон. Якщо потрібно залишити слова, що складаються з 5 букв, напишіть "?????".
Фільтр для стовпчика «Назва продукту».
Фільтр можна настроїти за кольором тексту або за кольором комірки.
Зробимо "Фільтр за кольором" комірки для стовпця "Назва продукту". Клацаємо по кнопці фільтра і вибираємо з меню однойменний пункт. Виберемо червоний колір.
У таблиці залишилися лише продукти червоного кольору.
Фільтр за кольором тексту можна застосувати до стовпця «Категорія». Залишимо лише фрукти. Знову вибираємо червоний колір.
Тепер у таблиці прикладу відображено лише фрукти червоного кольору.
Якщо Ви хочете, щоб були видні всі осередки таблиці, але спочатку йшов червоний осередок, потім зелений, синій і так далі, скористайтеся сортуванням в Excel. Перейшовши за посиланням, Ви зможете прочитати статтю на тему.
Фільтри в Ексель допоможуть Вам працювати з великими таблицями. Основні моменти, як зробити фільтр та як з ним працювати, ми розглянули. Підбирайте необхідні умови і залишайте в таблиці дані, що цікавлять.
Іноді під час роботи з великою базою даних виникає необхідність відібрати інформацію за певним критерієм (наприклад, показати лише тих людей, які народилися 1980 року). Для виконання подібних завдань використовується фільтр в Excel.
Відео з фільтрації та сортування даних в Excel
Види фільтрів в Excel
Фільтрування - це вибір необхідних даних зі списку для подальшої роботи з ними.Результатом цієї процедури будуть певні рядки, які відповідають критеріям відбору. Інші записи тимчасово ховаються і не використовуються, доки користувач не відключить фільтр. З відібраними даними можна виконувати стандартні дії: редагування, форматування, друк, побудова графіків, діаграм тощо.
Всього в Екселі є 2 способи фільтрації: автофільтр та розширений фільтр.Запустити їх можна через панель меню, клацнувши "Дані - Фільтр". За допомогою першого варіанта здійснюється швидкий відбір необхідної інформації із простими критеріями пошуку. У режимі автофільтра великий рядок таблиці в кожній колонці міститиме кнопку зі стрілочкою, клікнувши на яку можна вказати критерії для відбору. Для кожного стовпця можна встановити налаштування. У цьому режимі можна встановити такі параметри:
Щоб видалити фільтр із однієї колонки, потрібно клікнути у переліку елементів на пункт «Все». Якщо потрібно скасувати його для всієї таблиці, необхідно вибрати в панелі меню пункти "Дані - Фільтр - Відобразити все". Видалення автофільтра здійснюється так само, як і його запуск.
Розширений фільтр
Приклад створення розширеного фільтра — необхідно встановити діапазон вихідних даних, фільтрів і діапазон, куди поміщаються відфільтровані дані
Другий варіант фільтрації надає користувачеві більше можливостей для відбору необхідних даних. Щоб запустити розширену опцію, необхідно створити копію заголовків таблиці (тобто просто скопіювати шапку). Це буде діапазон умов. Потім треба заповнити цей діапазон критеріями відбору. Але тут важливо дотримуватися правил: якщо потрібно, щоб значення відбиралися за двома параметрами (наприклад, прізвище студента та оцінка), умови записуються в один рядок; якщо ж критерії будуть відбиратися в режимі "АБО" (марка машини або об'єм двигуна), тоді вони записуються в різні рядки.
Припустимо, є таблиця з двома колонками - найменування товару та кількість. Усього товарів 3 — банани, апельсини, мандарини, а кількість — 10, 20 та 15 штук відповідно. Після того, як буде скопійовано шапку, можна створити умову, наприклад, показати товари, кількість яких менша або дорівнює 15. Тобто під скопійованою шапкою в колонці «Кількість» треба написати<=15. Затем надо запустить расширенный фильтр, указать исходный диапазон (исходная таблица), диапазон условий (таблица, где указано «кол-во <=15») и нажать «ОК». Исходная таблица изменится: теперь тут будут отображены только бананы (10 штук) и мандарины (15 штук).
Таким чином, принцип роботи фільтрації має бути зрозумілим. Цим способом можна відбирати будь-які елементи з бази даних, наскільки великою вона не була б. Така процедура допоможе спростити роботу користувача з більшими обсягами даних.