Якщо для даних, які потрібно відфільтрувати, потрібні умови для кількох полів, наприклад фільтрування за кількома умовами, які мають бути істинними, або рядки, які відповідають будь-якій із кількох різних умов (наприклад, Тип = "Овочі" АБО Продавець = "Давидова"), можна скористатися діалоговим вікном Розширений фільтр .
Щоб відкрити діалогове вікно Розширений фільтр, натисніть кнопку>Додатково.
| Розширений фільтр | Приклад |
|---|---|
| Огляд розширених умов фільтрування | |
| Кілька умов, один стовпець, будь-яка з умов має логічне значення true | Продавець = "Давидова" АБО Продавець = "Пустовіт" |
| Кілька умов, кілька стовпців, усі умови мають логічне значення true | Тип = "Овочі" І Продажі > 1000 |
| Кілька умов, кілька стовпців, будь-яка з умов має логічне значення true | Тип = "Овочі" АБО Продавець = "Пустовіт" |
| Кілька наборів умов, один стовпець у всіх наборах | (Продажі > 6000 ТА Продажі < 6500) АБО (Продаж < 500) |
| Кілька наборів умов, кілька стовпців у кожному наборі | (Торговий представник = "Давидова" І Продажі >3000) АБО (Торговий представник = "Пустовіт" І Продажі > 1500) |
| Умови з узагальненням | Продавець = ім’я з другою буквою "у" |
Огляд розширених умов фільтрування
Розширений фільтр працює не так, як фільтр.
- Команда "Додатково" відображає діалогове вікно Розширений фільтр, а не меню автофільтра.
- Ви створюєте діапазон умов (окремі клітинки над даними), де ви вводите умови фільтра, а потім повідомляєте діалоговому вікні Розширений фільтр, щоб використовувати цей діапазон.
- Розширений фільтр НЕ оновлює автоматично під час змінення значень умов
Примітка.
Розширений фільтр залишається доступним для складних сценаріїв фільтрування, хоча нові функції, такі як Copilot в Excel, тепер можуть допомогти користувачам аналізувати дані та фільтрувати запити природною мовою як альтернативний підхід для деяких випадків використання.
Загальні відомості про and OR Logic
| Тип логіки | Налаштування | Приклад | Що він знаходить |
|---|---|---|---|
| Логіка AND (усі умови мають бути істинними) | Розміщення умов в одному рядку | Введіть = "Овочі" у стовпці 1 Sales > 1000 in column 2 (обидва в одному рядку) |
Лише рядки, у яких тип IS "Овочі" ТА "Продаж" перевищує 1000 |
| ЛОГІКА OR (будь-яка умова може бути істинною) | Розміщення умов в іншому рядку | Рядок 1: тип = "Овочі" Ряд 2: Тип = "М'ясо" (різні рядки, один стовпець) |
Рядки, де введіть IS "Овочі" АБО Тип IS "М'ясо" (або обидва) |
Зразок даних
Наведені нижче зразки даних використовуються для всіх процедур, описаних у цій статті.
Дані містять три пусті рядки над вихідним діапазоном, який використовуватиметься як діапазон умов (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
Логічний вираз: (Продавець = "Давидова" АБО Продавець = "Пустовіт")
Використовуйте цей параметр, якщо потрібно відфільтрувати рядки, у яких один стовпець відповідає будь-якому з кількох значень. Буде відображено обидва рядки з рядками Давидова AND із Пустовітом.
Щоб знайти рядки, які відповідають кільком умовам для одного стовпця, введіть умови безпосередньо одну під одною в окремі рядки діапазону умов. У прикладі введіть такі рядки в перші два рядки діапазону умов:
Тип Продавець Продаж, грн. ="=Давидова" ="=Пустовіт" Клацніть клітинку у вихідному діапазоні.
На вкладці Дані у групі Сортування й фільтр виберіть пункт Додатково.
Виберіть параметр Фільтрувати список на місці, приховувати рядки, які не відповідають умовам, або Копіювати до іншого розташування, копіювати рядки, які відповідають умовам, до іншої області аркуша.
У полі Діапазон умов введіть посилання на діапазон умов, зокрема підписи умов. Відповідно до прикладу введіть $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 знайде "Давидова", "Давид" і "Давиденко".
Використайте символи узагальнення.
Символ Щоб знайти ? (знак питання) Будь-який символ
Наприклад, sm?th знаходить слова "коваль" і "смит"* (зірочка) Будь-яка кількість символів
Наприклад, *схід знаходить "Північний схід" і "Південний схід"~ (тильда) зі знаком ?, * або ~ в кінці Знак питання, зірочка або тильда
Наприклад, фр91~? Наприклад, за умовою "фр91~?" буде знайдено "фр91?".
Вставте принаймні три пусті рядки над вихідним діапазоном, які можна використовувати як діапазон умов. У діапазоні умов мають міститися підписи стовпців. Переконайтеся, що між значеннями умов і вихідним діапазоном міститься принаймні один пустий рядок.
У рядках під підписами стовпців введіть умови, які потрібно використовувати. Відповідно до прикладу введіть:
Тип Продавець Продаж, грн. ="=М’я*" ="=?у*" Клацніть клітинку у вихідному діапазоні. Відповідно до прикладу клацніть будь-яку клітинку у вихідному діапазоні A6:C10.
На вкладці Дані у групі Сортування й фільтр виберіть пункт Додатково.
Виберіть параметр Фільтрувати список на місці, приховувати рядки, які не відповідають умовам, або Копіювати до іншого розташування, копіювати рядки, які відповідають умовам, до іншої області аркуша.
У полі Діапазон умов введіть посилання на діапазон умов, зокрема підписи умов. Відповідно до прикладу введіть $A$1:$B$3.
Відповідно до прикладу відфільтровані результати для вихідного діапазону будуть такі:
Тип Продавець Продаж, грн. Напої Семенів 5 122 М’ясо Давидова 450 грн. Овочі Пустовіт 6 328
Видалення або видалення розширеного фільтра
Застосувавши розширений фільтр, можна видалити його, щоб переглянути всі дані знову. Ось як це зробити:
- Клацніть будь-яку клітинку в діапазоні відфільтрованих даних.
- Перейдіть на вкладку Дані .
- У групі Сортування & фільтр натисніть кнопку Очистити.
- Усі рядки знову відображатимуться.
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті tech Excel або отримати підтримку в спільнотах.