Якщо для даних, які потрібно відфільтрувати, потрібні складні умови (наприклад, Тип = "Овочі" АБО Продавець = "Давидова"), можна скористатися діалоговим вікном Розширений фільтр .
Щоб відкрити діалогове вікно Розширений фільтр , натисніть кнопку Дані > Додатково.
|
Розширений фільтр |
Приклад |
|---|---|
|
Кілька умов, один стовпець, будь-яка з умов має логічне значення true |
Продавець = "Давидова" АБО Продавець = "Пустовіт" |
|
Кілька умов, кілька стовпців, усі умови мають логічне значення true |
Тип = "Овочі" І Продаж, грн. > 1000 |
|
Кілька умов, кілька стовпців, будь-яка з умов має логічне значення true |
Тип = "Овочі" АБО Продавець = "Пустовіт" |
|
(Продаж, грн. > 6000 І Продаж, грн. < 6500) АБО (Продаж, грн. < 500) |
|
|
(Торговий представник = "Давидова" І Продажі >3000) АБО |
|
|
Продавець = ім’я з другою буквою "у" |
Огляд розширених умов фільтрування
Команди Додатково й Фільтр мають кілька суттєвих відмінностей.
-
Команда "Додатково" відображає діалогове вікно Розширений фільтр, а не меню автофільтра.
-
Ви вводите додаткові умови в окремому діапазоні умов на аркуші над діапазоном клітинок або таблицею, які потрібно відфільтрувати. Програма Microsoft Office Excel використовує окремий діапазон умов у діалоговому вікні Розширений фільтр як джерело для додаткових умов.
Зразок даних
Наведені нижче зразки даних використовуються для всіх процедур, описаних у цій статті.
Дані містять чотири пусті рядки над вихідним діапазоном, який використовуватиметься як діапазон умов (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.
-
Оскільки використовується формула, потрібно вводити її звичайним способом. Не вводьте її як вираз, тобто:
=''= запис ''.
-
Не використовуйте заголовок стовпця як заголовок умови; залиште умову без заголовка або використайте заголовок, який не є заголовком стовпця у вихідному діапазоні (у наведених нижче прикладах: "Обчислене середнє значення" та "Точна відповідність").
Якщо замість відносного посилання на клітинку або імені діапазону вказати підпис стовпця, Excel відобразить значення помилки, таке як #NAME? або #VALUE у клітинці, яка містить умову. Цю помилку можна ігнорувати, оскільки вона не впливає на фільтрування діапазону списку.
-
У формулі, яка використовується для створення умов, має застосовуватися відносне посилання на відповідну клітинку в першому рядку даних.
-
Решта посилань у формулі мають бути абсолютні.
Кілька умов, один стовпець, будь-яка з умов має логічне значення true
Логічний вираз: (Продавець = "Давидова" АБО Продавець = "Пустовіт")
-
Вставте принаймні три пусті рядки над вихідним діапазоном, які можна використовувати як діапазон умов. У діапазоні умов мають міститися підписи стовпців. Переконайтеся, що між значеннями умов і вихідним діапазоном міститься принаймні один пустий рядок.
-
Щоб знайти рядки, які відповідають кільком умовам для одного стовпця, введіть умови безпосередньо одну під одною в окремі рядки діапазону умов. Відповідно до прикладу введіть:
Тип
Продавець
Продаж, грн.
="=Давидова"
="=Пустовіт"
-
Клацніть клітинку у вихідному діапазоні. Відповідно до прикладу клацніть будь-яку клітинку в діапазоні A6:C10.
-
На вкладці Дані у групі Сортування й фільтр виберіть пункт Додатково.
-
Виконайте одну з наведених нижче дій.
-
Щоб відфільтрувати вихідний діапазон прихованням рядків, які не відповідають певним умовам, виберіть параметр фільтрувати список на місці.
-
Щоб відфільтрувати вихідний діапазон копіюванням рядків, які відповідають певним умовам, до іншої області аркуша, виберіть параметр скопіювати результат до іншого розташування, клацніть у полі Діапазон для результату, а потім у лівому верхньому куті клацніть область, куди потрібно вставити рядки.
Порада Під час копіювання відфільтрованих рядків до іншого розташування можна вказати, які стовпці потрібно копіювати. Перш ніж фільтрувати, скопіюйте підписи потрібних стовпців до першого рядка області, куди потрібно вставити відфільтровані рядки. Під час фільтрування введіть посилання на скопійовані підписи стовпців у полі Діапазон для результату. Скопійовані рядки міститимуть лише стовпці, для яких скопійовано підписи.
-
-
У полі Діапазон умов введіть посилання на діапазон умов, зокрема підписи умов. Відповідно до прикладу введіть $A$1:$C$3.
Щоб тимчасово перемістити діалогове вікно Розширений фільтр , коли ви вибираєте діапазон умов, натисніть кнопку Згорнути діалогове вікно
-
Відповідно до прикладу відфільтровані результати для вихідного діапазону будуть такі:
Тип
Продавець
Продаж, грн.
М’ясо
Давидова
450 грн.
Овочі
Пустовіт
6 328
Овочі
Давидова
6 544
Кілька умов, кілька стовпців, усі умови мають логічне значення true
Логічний вираз: (Тип = "Овочі" І Продаж, грн. > 1000)
-
Вставте принаймні три пусті рядки над вихідним діапазоном, які можна використовувати як діапазон умов. У діапазоні умов мають міститися підписи стовпців. Переконайтеся, що між значеннями умов і вихідним діапазоном міститься принаймні один пустий рядок.
-
Щоб знайти рядки, які відповідають кільком умовам у кількох стовпцях, введіть усі умови в один рядок діапазону умов. Відповідно до прикладу введіть:
Тип
Продавець
Продаж, грн.
="=Овочі"
>1 000
-
Клацніть клітинку у вихідному діапазоні. Відповідно до прикладу клацніть будь-яку клітинку в діапазоні A6:C10.
-
На вкладці Дані у групі Сортування й фільтр виберіть пункт Додатково.
-
Виконайте одну з наведених нижче дій.
-
Щоб відфільтрувати вихідний діапазон прихованням рядків, які не відповідають певним умовам, виберіть параметр фільтрувати список на місці.
-
Щоб відфільтрувати вихідний діапазон копіюванням рядків, які відповідають певним умовам, до іншої області аркуша, виберіть параметр скопіювати результат до іншого розташування, клацніть у полі Діапазон для результату, а потім у лівому верхньому куті клацніть область, куди потрібно вставити рядки.
Порада Під час копіювання відфільтрованих рядків до іншого розташування можна вказати, які стовпці потрібно копіювати. Перш ніж фільтрувати, скопіюйте підписи потрібних стовпців до першого рядка області, куди потрібно вставити відфільтровані рядки. Під час фільтрування введіть посилання на скопійовані підписи стовпців у полі Діапазон для результату. Скопійовані рядки міститимуть лише стовпці, для яких скопійовано підписи.
-
-
У полі Діапазон умов введіть посилання на діапазон умов, зокрема підписи умов. Відповідно до прикладу введіть $A$1:$C$2.
Щоб тимчасово перемістити діалогове вікно Розширений фільтр , коли ви вибираєте діапазон умов, натисніть кнопку Згорнути діалогове вікно
-
Відповідно до прикладу відфільтровані результати для вихідного діапазону будуть такі:
Тип
Продавець
Продаж, грн.
Овочі
Пустовіт
6 328
Овочі
Давидова
6 544
Кілька умов, кілька стовпців, будь-яка з умов має логічне значення true
Логічний вираз: (Тип = "Овочі" АБО Продавець = "Пустовіт")
-
Вставте принаймні три пусті рядки над вихідним діапазоном, які можна використовувати як діапазон умов. У діапазоні умов мають міститися підписи стовпців. Переконайтеся, що між значеннями умов і вихідним діапазоном міститься принаймні один пустий рядок.
-
Щоб знайти рядки, які відповідають кільком умовам у кількох стовпцях, коли будь-яка умова може бути істиною, введіть умови в різних стовпцях і рядках діапазону умов. Відповідно до прикладу введіть:
Тип
Продавець
Продаж, грн.
="=Овочі"
="=Пустовіт"
-
Клацніть клітинку у вихідному діапазоні. Відповідно до прикладу клацніть будь-яку клітинку у вихідному діапазоні A6:C10.
-
На вкладці Дані у групі Сортування й фільтр виберіть пункт Додатково.
-
Виконайте одну з наведених нижче дій.
-
Щоб відфільтрувати вихідний діапазон прихованням рядків, які не відповідають певним умовам, виберіть параметр фільтрувати список на місці.
-
Щоб відфільтрувати вихідний діапазон копіюванням рядків, які відповідають певним умовам, до іншої області аркуша, виберіть параметр скопіювати результат до іншого розташування, клацніть у полі Діапазон для результату, а потім у лівому верхньому куті клацніть область, куди потрібно вставити рядки.
Порада.: Під час копіювання відфільтрованих рядків до іншого розташування можна вказати, які стовпці потрібно копіювати. Перш ніж фільтрувати, скопіюйте підписи потрібних стовпців до першого рядка області, куди потрібно вставити відфільтровані рядки. Під час фільтрування введіть посилання на скопійовані підписи стовпців у полі Діапазон для результату. Скопійовані рядки міститимуть лише стовпці, для яких скопійовано підписи.
-
-
У полі Діапазон умов введіть посилання на діапазон умов, зокрема підписи умов. Відповідно до прикладу введіть $A$1:$B$3.
Щоб тимчасово перемістити діалогове вікно Розширений фільтр , коли ви вибираєте діапазон умов, натисніть кнопку Згорнути діалогове вікно
-
Відповідно до прикладу відфільтровані результати для вихідного діапазону будуть такі:
Тип
Продавець
Продаж, грн.
Овочі
Пустовіт
6 328
Овочі
Давидова
6 544
Кілька наборів умов, один стовпець у всіх наборах
Логічний вираз: ( (Продаж, грн > 6000 І Продаж, грн < 6500) АБО (Продаж, грн < 500) )
-
Вставте принаймні три пусті рядки над вихідним діапазоном, які можна використовувати як діапазон умов. У діапазоні умов мають міститися підписи стовпців. Переконайтеся, що між значеннями умов і вихідним діапазоном міститься принаймні один пустий рядок.
-
Щоб знайти рядки, які відповідають кільком наборам умов, у яких кожний набір містить умови для одного стовпця, включіть кілька стовпців з одним заголовком. Відповідно до прикладу введіть:
Тип
Продавець
Продаж, грн.
Продаж, грн.
>6 000
<6 500
<500
-
Клацніть клітинку у вихідному діапазоні. Відповідно до прикладу клацніть будь-яку клітинку у вихідному діапазоні A6:C10.
-
На вкладці Дані у групі Сортування й фільтр виберіть пункт Додатково.
-
Виконайте одну з наведених нижче дій.
-
Щоб відфільтрувати вихідний діапазон прихованням рядків, які не відповідають певним умовам, виберіть параметр фільтрувати список на місці.
-
Щоб відфільтрувати вихідний діапазон копіюванням рядків, які відповідають певним умовам, до іншої області аркуша, виберіть параметр скопіювати результат до іншого розташування, клацніть у полі Діапазон для результату, а потім у лівому верхньому куті клацніть область, куди потрібно вставити рядки.
Порада.: Під час копіювання відфільтрованих рядків до іншого розташування можна вказати, які стовпці потрібно копіювати. Перш ніж фільтрувати, скопіюйте підписи потрібних стовпців до першого рядка області, куди потрібно вставити відфільтровані рядки. Під час фільтрування введіть посилання на скопійовані підписи стовпців у полі Діапазон для результату. Скопійовані рядки міститимуть лише стовпці, для яких скопійовано підписи.
-
-
У полі Діапазон умов введіть посилання на діапазон умов, зокрема підписи умов. Відповідно до прикладу введіть $A$1:$D$3.
Щоб тимчасово перемістити діалогове вікно Розширений фільтр , коли ви вибираєте діапазон умов, натисніть кнопку Згорнути діалогове вікно
-
Відповідно до прикладу відфільтровані результати для вихідного діапазону будуть такі:
Тип
Продавець
Продаж, грн.
М’ясо
Давидова
450 грн.
Овочі
Пустовіт
6 328
Кілька наборів умов, кілька стовпців у кожному наборі
Логічний вираз: ( (Торговий представник = "Давидова" І Продаж >3000) АБО (Торговий представник = "Пустовіт" І Продаж > 1500) )
-
Вставте принаймні три пусті рядки над вихідним діапазоном, які можна використовувати як діапазон умов. У діапазоні умов мають міститися підписи стовпців. Переконайтеся, що між значеннями умов і вихідним діапазоном міститься принаймні один пустий рядок.
-
Щоб знайти рядки, які відповідають кільком наборам умов, коли кожний набір містить умови для кількох стовпців, введіть кожний набір умов в окремі стовпці й рядки. Відповідно до прикладу введіть:
Тип
Продавець
Продаж, грн.
="=Давидова"
>3 000
="=Пустовіт"
>1 500
-
Клацніть клітинку у вихідному діапазоні. Відповідно до прикладу клацніть будь-яку клітинку у вихідному діапазоні 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 чи отримати підтримку в спільнотах.
