Якщо дані, які потрібно відфільтрувати, вимагають застосування умов за кількома полями, наприклад фільтрування за кількома умовами, які мають відповідати всім умовам, або відображення рядків, які відповідають будь-якій із кількох умов (наприклад, тип = "Овочі" АБО Продавець = "Давидова"), ви можете скористатися діалоговим вікном "Розширений фільтр ".
Щоб відкрити діалогове вікно "Розширений фільтр", виберіть елемент"Додатковідані>".
| Розширений фільтр | Приклад |
|---|---|
| Огляд розширених умов фільтра | |
| Кілька умов, один стовпець, будь-яка з умов має логічне значення true | Продавець = "Давидова" АБО Продавець = "Пустовіт" |
| Кілька умов, кілька стовпців, усі умови мають логічне значення true | Тип = "Овочі" І Продаж > 1000 |
| Кілька умов, кілька стовпців, будь-яка з умов має логічне значення true | Тип = "Овочі" АБО Продавець = "Пустовіт" |
| Кілька наборів умов, один стовпець у всіх наборах | (Продаж > 6000 І Продаж < 6500) АБО (Продаж < 500) |
| Кілька наборів умов, кілька стовпців у кожному наборі | (Продавець = "Давидова" І Продаж >3000) АБО (Торговий представник = "Пустовіт" І Продаж > 1500) |
| Умови з узагальненням | Продавець = ім’я з другою буквою "у" |
Огляд розширених умов фільтра
Розширений фільтр і фільтр має кілька важливих відмінностей.
- Команда "Додатково" відображає діалогове вікно Розширений фільтр, а не меню автофільтра.
- Ви створюєте діапазон умов (окремі клітинки над даними), у який вводите умови фільтрування, а потім повідомляєте діалоговому вікну "Розширений фільтр" використовувати цей діапазон.
- Розширений фільтр НЕ оновлюється автоматично, коли змінюється значення умов
Примітка.
Розширений фільтр залишається доступним для складних сценаріїв фільтрування, хоча нові функції, як-от Copilot в Excel, тепер можуть допомогти користувачам з аналізом даних і фільтруванням запитів природною мовою як альтернативний підхід у деяких випадках використання.
Пояснення логіки "І" та "Або"
| Тип логіки | Як налаштувати | Приклад | Результат пошуку |
|---|---|---|---|
| Логіка AND (усі умови мають бути істинними) | Розміщення умов в одному рядку | Тип = "Овочі" в стовпці 1 Продаж > 1000 у стовпці 2 (обидва в одному рядку) |
Лише рядки, у яких тип IS "Овочі" І обсяг збуту більше 1000 |
| Логіка АБО (можуть бути істинні будь-які умови) | Put criteria in different row | Рядок 1: Тип = "Овочі" Рядок 2: Тип = "М'ясо" (різні рядки, один стовпець) |
Рядки, де тип – "Овочі" АБО Тип – "М'ясо" (або обидва) |
Зразок даних
Наведені нижче зразки даних використовуються для всіх процедур у цій статті.
Дані містять три пусті рядки над вихідним діапазоном, який буде використано як діапазон умов (A1:C4), і вихідний діапазон (A6:C10). Діапазон умов має підписи стовпців і містить принаймні один пустий рядок між значеннями умов і вихідним діапазоном.
Для роботи з цими даними виділіть їх у таблиці нижче, а потім скопіюйте та вставте їх у клітинку A1 нового аркуша Excel.
| Тип | Продавець | Продаж, грн. |
|---|---|---|
| Напої | Семенів | 5122 грн. |
| М’ясо | Давидова | 450 грн. |
| Овочі | Пустовіт | 6328 грн. |
| Овочі | Давидова | 6 544 грн. |
У цьому прикладі отриманий аркуш матиме такий вигляд: діапазон умов фільтра виділено синім кольором, а вихідний діапазон (дані, які потрібно відфільтрувати) – червоною.
Оператори порівняння
Нижче наведено оператори, за допомогою яких можна порівняти два значення. Результатом порівняння буде логічне значення: TRUE (істина) або FALSE (хибність).
| Оператор порівняння | Значення | Приклад |
|---|---|---|
| = (знак рівності) | Дорівнює | A1=B1 |
| > (знак ''більше'') | Більше | A1>B1 |
| < (знак ''менше'') | Менше | A1<B1 |
| >= (знак ''більше або дорівнює'') | Більше або дорівнює | A1>=B1 |
| <= (знак ''менше або дорівнює'') | Менше або дорівнює | A1<=B1 |
| <> (знак ''не дорівнює'') | Не дорівнює | A1<>B1 |
Введення тексту або значення за допомогою знака рівності
Оскільки знак рівності (=) використовується для позначення формули, коли в клітинку вводиться текст або значення, програма Excel обчислює введені дані. Однак це може призвести до неочікуваних результатів фільтрування. Щоб указати оператор порівняння "дорівнює" для тексту або значення, введіть умови як рядковий вираз у відповідній клітинці в діапазоні умов.
=''=запис''.
Де запис – це текст або значення, які потрібно знайти. Наприклад:
| Дані, що вводяться у клітинку | Програма Excel визначає та відображає |
|---|---|
| ="=Давидова" | =Давидова |
| ="=3 000" | =3 000 |
Урахування регістру
Фільтруючи текстові дані, Excel не розрізняє великі та малі букви. Проте, пошук виразу з урахуванням регістру можна виконати за допомогою формули. Приклад див. в розділі "Умови з узагальненням".
Використання попередньо визначених імен
Якщо дати діапазону ім’я Умова, посилання на діапазон автоматично відображатиметься в полі Діапазон умов. Вихідному діапазону, який потрібно відфільтрувати, також можна дати ім’я База даних, а області, куди потрібно вставити рядки, – ім’я Видобування, тоді ці діапазони автоматично відображатимуться в полях Вихідний діапазон і Діапазон для результату відповідно.
Створення умов за допомогою формули
Обчислюване значення, отримане як результат формули, можна використовувати як умову. Слід пам’ятати про такі важливі моменти:
- Формула має повертати результат TRUE або FALSE.
- Оскільки використовується формула, потрібно вводити її звичайним способом. Не вводьте її як вираз, тобто:
=''=entry'' - Не використовуйте заголовок стовпця як заголовок умови; залиште умову без заголовка або використайте заголовок, який не є заголовком стовпця у вихідному діапазоні (у наведених нижче прикладах: "Обчислене середнє значення" та "Точна відповідність").
Якщо замість відносного посилання на клітинку або імені діапазону вказати підпис стовпця, програма Excel відобразить значення помилки, таке як #NAME? або #VALUE! у клітинці, яка містить умову. Ця помилка не критична, оскільки не впливає на фільтрування вихідного діапазону. - У формулі, яка використовується для створення умов, має застосовуватися відносне посилання на відповідну клітинку в першому рядку даних.
- Решта посилань у формулі мають бути абсолютні.
Кілька умов, один стовпець, будь-яка з умов має логічне значення true
Логічний вираз: (Продавець = "Давидова" АБО Продавець = "Пустовіт")
Використовуйте цей параметр, щоб відфільтрувати рядки, у яких один стовпець відповідає БУДЬ-ЯКОМУ з кількох значень. Будуть показані обидва рядки з Давидом І рядки з Б'юкененом.
Щоб знайти рядки, які відповідають кільком умовам для одного стовпця, введіть умови безпосередньо одну під одною в окремі рядки діапазону умов. Наприклад, введіть у перші два рядки діапазону умов:
Тип Продавець Продаж, грн. ="=Давидова" ="=Пустовіт" Клацніть клітинку у вихідному діапазоні.
На вкладці Дані у групі Сортування й фільтр виберіть пункт Додатково.
Виберіть фільтрування списку, фільтрування на місці, приховання рядків, що не відповідають певним умовам, або копіювання рядків, що відповідають певним умовам, до іншої області аркуша.
У полі Діапазон умов введіть посилання на діапазон умов, зокрема підписи умов. Відповідно до прикладу введіть $A$1:$C$3.
Відповідно до прикладу відфільтровані результати для вихідного діапазону будуть такі:
Тип Продавець Продаж, грн. М’ясо Давидова 450 грн. Овочі Пустовіт 6 328 Овочі Давидова 6 544
Кілька умов, кілька стовпців, усі умови мають логічне значення true
Логічний вираз: (Тип = "Овочі" І Продаж > 1000)
Щоб знайти рядки, які відповідають кільком умовам у кількох стовпцях, введіть усі умови в один рядок діапазону умов. Наприклад, введіть:
Тип Продавець Продаж, грн. ="=Продукти" >1000 Клацніть клітинку у вихідному діапазоні.
На вкладці Дані у групі Сортування й фільтр виберіть пункт Додатково.
Виберіть фільтрування списку, фільтрування на місці, приховання рядків, що не відповідають певним умовам, або копіювання рядків, що відповідають певним умовам, до іншої області аркуша.
У полі Діапазон умов введіть посилання на діапазон умов, зокрема підписи умов. Відповідно до прикладу введіть $A$1:$C$2.
Відповідно до прикладу відфільтровані результати для вихідного діапазону будуть такі:
Тип Продавець Продаж, грн. Овочі Пустовіт 6 328 Овочі Давидова 6 544
Кілька умов, кілька стовпців, будь-яка з умов має логічне значення true
Логічний вираз: (Тип = "Овочі" АБО Продавець = "Пустовіт")
Щоб знайти рядки, які відповідають кільком умовам у кількох стовпцях, коли будь-яка умова може бути істиною, введіть умови в різних стовпцях і рядках діапазону умов. Наприклад, введіть:
Тип Продавець Продаж, грн. ="=Овочі" ="=Пустовіт" Клацніть клітинку у вихідному діапазоні.
На вкладці " Дані " в групі "Сортування & фільтр " натисніть кнопку "Додатково".
Виберіть фільтрування списку, фільтрування на місці, приховання рядків, що не відповідають певним умовам, або копіювання рядків, що відповідають певним умовам, до іншої області аркуша.
У полі Діапазон умов введіть посилання на діапазон умов, зокрема підписи умов. Відповідно до прикладу введіть $A$1:$B$3.
Відповідно до прикладу відфільтровані результати для вихідного діапазону будуть такі:
Тип Продавець Продаж, грн. Овочі Пустовіт 6 328 Овочі Давидова 6 544
Кілька наборів умов, один стовпець у всіх наборах
Логічний вираз : ( (Продаж > 6000 І Продаж < 6500) АБО (Продаж < 500) )
Щоб знайти рядки, які відповідають кільком наборам умов, у яких кожний набір містить умови для одного стовпця, включіть кілька стовпців з одним заголовком. Наприклад, введіть:
Тип Продавець Продаж, грн. Продажі >6000 <6500 <500 Клацніть клітинку у вихідному діапазоні. Відповідно до прикладу клацніть будь-яку клітинку у вихідному діапазоні A6:C10.
На вкладці Дані у групі Сортування й фільтр виберіть пункт Додатково.
Виберіть фільтрування списку, фільтрування на місці, приховання рядків, що не відповідають певним умовам, або копіювання рядків, що відповідають певним умовам, до іншої області аркуша.
Порада.
Під час копіювання відфільтрованих рядків до іншого розташування можна вказати, які стовпці потрібно копіювати. Перш ніж фільтрувати, скопіюйте підписи потрібних стовпців до першого рядка області, куди потрібно вставити відфільтровані рядки. Під час фільтрування введіть посилання на скопійовані підписи стовпців у полі Діапазон для результату. Скопійовані рядки міститимуть лише стовпці, для яких скопійовано підписи.
У полі Діапазон умов введіть посилання на діапазон умов, зокрема підписи умов. Відповідно до прикладу введіть $A$1:$D$3.
Відповідно до прикладу відфільтровані результати для вихідного діапазону будуть такі:
Тип Продавець Продаж, грн. М’ясо Давидова 450 грн. Овочі Пустовіт 6 328
Кілька наборів умов, кілька стовпців у кожному наборі
Логічний вираз: ( (Торговий представник = "Давидова" І Продаж >3000) АБО (Торговий представник = "Пустовіт" І Продаж > 1500) )
Щоб знайти рядки, які відповідають кільком наборам умов, коли кожний набір містить умови для кількох стовпців, введіть кожний набір умов в окремі стовпці й рядки. Наприклад, введіть:
Тип Продавець Продаж, грн. ="=Давидова" >3000 ="=Пустовіт" >1500 Клацніть клітинку у вихідному діапазоні. Відповідно до прикладу клацніть будь-яку клітинку у вихідному діапазоні A6:C10.
На вкладці Дані у групі Сортування й фільтр виберіть пункт Додатково.
Виберіть фільтрування списку, фільтрування на місці, приховання рядків, що не відповідають певним умовам, або копіювання рядків, що відповідають певним умовам, до іншої області аркуша.
У полі Діапазон умов введіть посилання на діапазон умов, зокрема підписи умов. Відповідно до прикладу введіть $A$1:$C$3.
Відповідно до прикладу відфільтровані результати для вихідного діапазону будуть такі:
Тип Продавець Продаж, грн. Овочі Пустовіт 6 328 Овочі Давидова 6 544
Умови з узагальненням
Логічний вираз: Продавець = ім’я з другою буквою "у"
Щоб знайти текстові значення, що містять кілька спільних символів (але не всі), виконайте одну або кілька таких дій:
Введіть один або кілька символів без знака рівності (=), щоб знайти рядки з текстовим значенням у стовпці, який починається цими символами. Наприклад, якщо використати текст Дав як умову, програма Excel знайде "Давидова", "Давид" і "Давиденко".
Використайте символи узагальнення.
Символ Щоб знайти ? (знак питання) Будь-який символ
Наприклад умові "ма?ка" відповідають результати "мавка" та "марка".* (зірочка) Будь-яка кількість символів
Наприклад, умові "пів*" відповідають результати "північ" і "південь"~ (тильда) зі знаком ?, * або ~ в кінці Знак питання, зірочку або тильду
Наприклад, ан91~? Наприклад, за умовою "фр91~?" буде знайдено "фр91?".
Вставте принаймні три пусті рядки над вихідним діапазоном, які можна використовувати як діапазон умов. У діапазоні умов мають міститися підписи стовпців. Переконайтеся, що між значеннями умов і вихідним діапазоном міститься принаймні один пустий рядок.
У рядках під підписами стовпців введіть умови, які потрібно використовувати. Відповідно до прикладу введіть:
Тип Продавець Продаж, грн. ="=М’я*" ="=?у*" Клацніть клітинку у вихідному діапазоні. Відповідно до прикладу клацніть будь-яку клітинку у вихідному діапазоні A6:C10.
На вкладці Дані у групі Сортування й фільтр виберіть пункт Додатково.
Виберіть фільтрування списку, фільтрування на місці, приховання рядків, що не відповідають певним умовам, або копіювання рядків, що відповідають певним умовам, до іншої області аркуша.
У полі Діапазон умов введіть посилання на діапазон умов, зокрема підписи умов. Відповідно до прикладу введіть $A$1:$B$3.
Відповідно до прикладу відфільтровані результати для вихідного діапазону будуть такі:
Тип Продавець Продаж, грн. Напої Семенів 5 122 М’ясо Давидова 450 грн. Овочі Пустовіт 6 328
Видалення розширеного фільтра
Після застосування розширеного фільтра, можливо, потрібно буде видалити його, щоб знову переглянути всі дані. Ось як це зробити:
- Клацніть будь-яку клітинку у відфільтрованому діапазоні даних.
- Перейдіть на вкладку " Дані ".
- У групі "Сортування & фільтр" натисніть кнопку "Очистити".
- Усі рядки знову відобразяться.
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті Tech у Excel або отримати підтримку в спільнотах.