Фільтрування з використанням складних умов

Примітка.:  Ми хочемо надавати найновіший вміст довідки рідною мовою користувачів якомога швидше. Цю сторінку перекладено за допомогою засобу автоматичного перекладу, тому вона може містити смислові, синтаксичні або граматичні помилки. Ми вважаємо, що цей вміст стане вам у пригоді. Повідомте нас, чи була інформація корисною, унизу цієї сторінки. Для зручності цю статтю можна переглянути англійською мовою .

Щоб відфільтрувати дані, які потребують складних умов (наприклад, Тип = "Продукти" АБО Продавець = "Давидова"), скористайтеся діалоговим вікном Розширений фільтр.

Щоб відкрити діалогове вікно Розширений фільтр клацніть Дані > Додатково.

Група "Сортування й фільтр" на вкладці "Дані"

Розширений фільтр

Приклад

Огляд

Кілька умов, один стовпець, виконується будь-яка умова

Продавець = "Давидова" АБО Продавець = "Пустовіт"

Кілька умов, кілька стовпців, виконуються всі умови

Тип = "Продукти" І Продаж, грн. > 1000

Кілька умов, кілька стовпців, виконується будь-яка умова

Тип = "Овочі" АБО Продавець = "Пустовіт"

Кілька наборів умов, один стовпець для всіх наборів

(Продаж > 6000 І Продаж, грн. < 6500) АБО (Продаж, грн. < 500)

Кілька наборів умов, кілька стовпців для всіх наборів

(Продавець = «Давидова» AND продажі > 3000) АБО
(Продавець = "Пустовіт" і продаж > 1500)

Умови з узагальненням

Продавець = ім’я з другою буквою "у"

Огляд

Між командою Додатково та командою Фільтр є кілька суттєвих відмінностей.

  • Відображається діалогове вікно Розширений фільтр замість меню «Автофільтр».

  • Додаткові умови вводяться в окремому діапазоні умов на аркуші над діапазоном клітинок або таблицею, які слід відфільтрувати. У програмі 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

="=Давидова"

=Давидова

="=3000"

=3000

Урахування регістру букв

Фільтруючи текстові дані, програма Excel не розрізняє великі та малі букви. Проте пошук виразу з урахуванням регістру можна виконати за допомогою формули. Приклад див. в розділі Умови з узагальненням.

Використання попередньо визначених імен

Якщо діапазону Умови призначити ім’я, посилання для діапазону автоматично відобразиться в полі Діапазон умов. Ви також можете призначити ім’я База даних вихідному діапазону, який потрібно відфільтрувати, та ім’я Видобування — області, у яку слід вставити рядки. Ці діапазони автоматично відобразяться в полях Вихідний діапазон і Діапазон для результату відповідно.

Створення умов за допомогою формули

Обчислюване значення, отримане як результат формули, можна використовувати як умову. Слід пам’ятати про такі важливі моменти:

  • Формула має повертати результат ІСТИНА або ХИБНІСТЬ.

  • Оскільки ви використовуєте формулу, введіть її, як зазвичай, не потрібно вводити вираз у вигляді:

    =''= запис ''

  • Не використовуйте підписи стовпців як підписи умов. Залиште поля з підписами умов пустими або використовуйте підпис, який не збігається з підписом стовпця у вихідному діапазоні (у наведених нижче прикладах — це "Обчислене середнє" та "Точний збіг").

    Якщо замість відносного посилання на клітинку або імені діапазону у формулі використовується підпис стовпця, у клітинці з умовою відображається значення помилки, наприклад #NAME? або #VALUE!. Цю помилку можна пропустити, оскільки вона не впливає на результати фільтрування вихідного діапазону.

  • Формула в умові має використовувати відносне посилання на відповідну клітинку в першому рядку даних.

  • Решта посилань у формулі мають бути абсолютні.

Кілька умов, один стовпець, виконується будь-яка умова

Логічний вираз:    (Продавець = "Давидова" АБО Продавець = "Пустовіт")

  1. Вставте принаймні три пусті рядки над вихідним діапазоном, які використовуватимуться як діапазон умов. Діапазон умов має містити підписи стовпців. Переконайтеся, що між значеннями умов і вихідним діапазоном є принаймні один пустий рядок.

  2. Щоб знайти рядки, які відповідають кільком умовам для одного стовпця, введіть умови одну під одною в окремі рядки діапазону умов. Використовуючи наведений приклад, введіть:

    Тип

    Продавець

    Продажі

    ="=Давидова"

    ="=Пустовіт"

  3. Клацніть клітинку у вихідному діапазоні. Відповідно до прикладу клацніть будь-яку клітинку в діапазоні A6:C10.

  4. На вкладці Дані у групі Сортування й фільтр виберіть пункт Додатково.

    Група "Сортування й фільтр" на вкладці "Дані"

  5. Виконайте одну з таких дій:

    • Щоб відфільтрувати вихідний діапазон, приховавши рядки, які не відповідають указаним умовам, виберіть варіант фільтрувати список на місці.

    • Щоб відфільтрувати вихідний діапазон, скопіювавши рядки, які відповідають указаним умовам, до іншої області аркуша, виберіть варіант скопіювати результат до іншого розташування. Потім клацніть поле Діапазон для результату та в лівому верхньому куті області, куди потрібно вставити рядки.

      Порада   . Під час копіювання відфільтрованих рядків до іншого розташування можна вказати, які стовпці потрібно копіювати. Перш ніж фільтрувати, скопіюйте підписи потрібних стовпців до першого рядка області, куди потрібно вставити відфільтровані рядки. Під час фільтрування введіть посилання на скопійовані підписи стовпців у полі Діапазон для результату. Скопійовані рядки міститимуть лише стовпці, для яких скопійовано підписи.

  6. У полі Діапазон умов введіть посилання на діапазон умов, зокрема підписи умов. Відповідно до прикладу введіть $A$1:$C$3.

    Щоб перемістити діалогового вікна Розширений фільтр до речі, тимчасово, коли ви вибираєте діапазон умов, натисніть кнопку Згорнути діалогове вікно Зображення кнопки .

  7. Відповідно до прикладу відфільтровані результати для вихідного діапазону будуть такі:

    Тип

    Продавець

    Продажі

    М’ясо

    Давидова

    450 грн.

    продукти

    Пустовіт

    6 328 грн.

    Овочі

    Давидова

    6 544 грн.

Кілька умов, кілька стовпців, виконуються всі умови

Логічний вираз:    (Тип = "Овочі" І Продаж, грн. > 1000)

  1. Вставте принаймні три пусті рядки над вихідним діапазоном, які використовуватимуться як діапазон умов. Діапазон умов має містити підписи стовпців. Переконайтеся, що між значеннями умов і вихідним діапазоном є принаймні один пустий рядок.

  2. Щоб знайти рядки, які відповідають кільком умовам у кількох стовпцях, введіть усі умови в одному рядку діапазону умов. Використовуючи наведений приклад, введіть:

    Тип

    Продавець

    Продажі

    ="=Продукти"

    >1000

  3. Клацніть клітинку у вихідному діапазоні. Використовуючи наведений приклад, клацніть будь-яку клітинку діапазону A6:C10.

  4. На вкладці Дані у групі Сортування й фільтр виберіть пункт Додатково.

    Група "Сортування й фільтр" на вкладці "Дані"

  5. Виконайте одну з таких дій:

    • Щоб відфільтрувати вихідний діапазон, приховавши рядки, які не відповідають указаним умовам, виберіть варіант фільтрувати список на місці.

    • Щоб відфільтрувати вихідний діапазон, скопіювавши рядки, які відповідають указаним умовам, до іншої області аркуша, виберіть варіант скопіювати результат до іншого розташування. Потім клацніть поле Діапазон для результату та в лівому верхньому куті області, куди потрібно вставити рядки.

      Порада   . Під час копіювання відфільтрованих рядків до іншого розташування можна вказати, які стовпці потрібно копіювати. Перш ніж фільтрувати, скопіюйте підписи потрібних стовпців до першого рядка області, куди потрібно вставити відфільтровані рядки. Під час фільтрування введіть посилання на скопійовані підписи стовпців у полі Діапазон для результату. Скопійовані рядки міститимуть лише стовпці, для яких скопійовано підписи.

  6. У полі Діапазон умов введіть посилання на діапазон умов, зокрема підписи умов. Відповідно до прикладу введіть $A$1:$C$2.

    Щоб перемістити діалогового вікна Розширений фільтр до речі, тимчасово, коли ви вибираєте діапазон умов, натисніть кнопку Згорнути діалогове вікно Зображення кнопки .

  7. Відповідно до прикладу відфільтровані результати для вихідного діапазону будуть такі:

    Тип

    Продавець

    Продажі

    Овочі

    Пустовіт

    6 328 грн.

    Овочі

    Давидова

    6 544 грн.

Кілька умов, кілька стовпців, виконується будь-яка умова

Логічний вираз:     (Тип = "Овочі" АБО Продавець = "Пустовіт")

  1. Вставте принаймні три пусті рядки над вихідним діапазоном, які використовуватимуться як діапазон умов. Діапазон умов має містити підписи стовпців. Переконайтеся, що між значеннями умов і вихідним діапазоном є принаймні один пустий рядок.

  2. Щоб знайти рядки, які відповідають кільком умовам у кількох стовпцях, де будь-які умови можуть бути істинні, введіть умови в окремих стовпцях і рядках діапазону умов. Використовуючи наведений приклад, введіть:

    Тип

    Продавець

    Продажі

    ="=Продукти"

    ="=Пустовіт"

  3. Клацніть клітинку у вихідному діапазоні. Відповідно до прикладу клацніть будь-яку клітинку у вихідному діапазоні A6:C10.

  4. На вкладці Дані у групі Сортування й фільтр виберіть пункт Додатково.

    Група "Сортування й фільтр" на вкладці "Дані"

  5. Виконайте одну з таких дій:

    • Щоб відфільтрувати вихідний діапазон, приховавши рядки, які не відповідають указаним умовам, виберіть варіант фільтрувати список на місці.

    • Щоб відфільтрувати вихідний діапазон, скопіювавши рядки, які відповідають указаним умовам, до іншої області аркуша, виберіть варіант скопіювати результат до іншого розташування. Потім клацніть поле Діапазон для результату та в лівому верхньому куті області, куди потрібно вставити рядки.

    Порада.: У разі копіювання відфільтрованих рядків до іншого місця можна вказати стовпці, які долучатимуться до копії. Перед фільтруванням скопіюйте заголовки потрібних стовпців до першого рядка області вставлення результатів. Виконуючи фільтрування, введіть посилання на скопійовані заголовки стовпців у полі Діапазон для результату. Скопійовані рядки будуть містити лише стовпці, заголовки яких скопійовано.

  6. У полі Діапазон умов введіть посилання на діапазон умов, зокрема підписи умов. Відповідно до прикладу введіть $A$1:$B$3.

    Щоб перемістити діалогового вікна Розширений фільтр до речі, тимчасово, коли ви вибираєте діапазон умов, натисніть кнопку Згорнути діалогове вікно Зображення кнопки .

  7. Відповідно до прикладу відфільтровані результати для вихідного діапазону будуть такі:

    Тип

    Продавець

    Продажі

    Овочі

    Пустовіт

    6 328 грн.

    Овочі

    Давидова

    6 544 грн.

Кілька наборів умов, один стовпець для всіх наборів

Логічний вираз:    ((Продаж, грн. > 6000 І Продаж, грн. < 6500) АБО (Продаж, грн. < 500))

  1. Вставте принаймні три пусті рядки над вихідним діапазоном, які використовуватимуться як діапазон умов. Діапазон умов має містити підписи стовпців. Переконайтеся, що між значеннями умов і вихідним діапазоном є принаймні один пустий рядок.

  2. Щоб знайти рядки, які відповідають кільком наборам умов, де кожен набір містить умови для одного стовпця, введіть кілька стовпців з однаковими заголовками. Використовуючи наведений приклад, введіть:

    Тип

    Продавець

    Продажі

    Продажі

    >6000

    <6500

    <500

  3. Клацніть клітинку у вихідному діапазоні. Відповідно до прикладу клацніть будь-яку клітинку у вихідному діапазоні A6:C10.

  4. На вкладці Дані у групі Сортування й фільтр виберіть пункт Додатково.

    Група "Сортування й фільтр" на вкладці "Дані"

  5. Виконайте одну з таких дій:

    • Щоб відфільтрувати вихідний діапазон, приховавши рядки, які не відповідають указаним умовам, виберіть варіант фільтрувати список на місці.

    • Щоб відфільтрувати вихідний діапазон, скопіювавши рядки, які відповідають указаним умовам, до іншої області аркуша, виберіть варіант скопіювати результат до іншого розташування. Потім клацніть поле Діапазон для результату та в лівому верхньому куті області, куди потрібно вставити рядки.

      Порада.: У разі копіювання відфільтрованих рядків до іншого місця можна вказати стовпці, які долучатимуться до копії. Перед фільтруванням скопіюйте заголовки потрібних стовпців до першого рядка області вставлення результатів. Виконуючи фільтрування, введіть посилання на скопійовані заголовки стовпців у полі Діапазон для результату. Скопійовані рядки будуть містити лише стовпці, заголовки яких скопійовано.

  6. У полі Діапазон умов введіть посилання на діапазон умов, зокрема підписи умов. Відповідно до прикладу введіть $A$1:$D$3.

    Щоб перемістити діалогового вікна Розширений фільтр до речі, тимчасово, коли ви вибираєте діапазон умов, натисніть кнопку Згорнути діалогове вікно Зображення кнопки .

  7. Відповідно до прикладу відфільтровані результати для вихідного діапазону будуть такі:

    Тип

    Продавець

    Продажі

    М’ясо

    Давидова

    450 грн.

    продукти

    Пустовіт

    6 328 грн.

Кілька наборів умов, кілька стовпців для всіх наборів

Логічний вираз:    ( (Продавець = "Давидова" І Продаж, грн. >3000) АБО (Продавець = "Пустовіт" І Продаж, грн. > 1500) )

  1. Вставте принаймні три пусті рядки над вихідним діапазоном, які використовуватимуться як діапазон умов. Діапазон умов має містити підписи стовпців. Переконайтеся, що між значеннями умов і вихідним діапазоном є принаймні один пустий рядок.

  2. Щоб знайти рядки, які відповідають кільком наборам умов, де кожен набір містить умови для кількох стовпців, введіть кожен набір умов в окремий рядок і стовпець. Використовуючи наведений приклад, введіть:

    Тип

    Продавець

    Продажі

    ="=Давиденко"

    >3000

    ="=Петренко"

    >1500

  3. Клацніть клітинку у вихідному діапазоні. Використовуючи наведений приклад, клацніть будь-яку клітинку вихідного діапазону A6:C10.

  4. На вкладці Дані у групі Сортування й фільтр виберіть пункт Додатково.

    Група "Сортування й фільтр" на вкладці "Дані"

  5. Виконайте одну з таких дій:

    • Щоб відфільтрувати вихідний діапазон, приховавши рядки, які не відповідають указаним умовам, виберіть варіант фільтрувати список на місці.

    • Щоб відфільтрувати вихідний діапазон, скопіювавши рядки, які відповідають указаним умовам, до іншої області аркуша, виберіть варіант скопіювати результат до іншого розташування. Потім клацніть поле Діапазон для результату та в лівому верхньому куті області, куди потрібно вставити рядки.

      Порада   . Під час копіювання відфільтрованих рядків до іншого розташування можна вказати, які стовпці потрібно копіювати. Перш ніж фільтрувати, скопіюйте підписи потрібних стовпців до першого рядка області, куди потрібно вставити відфільтровані рядки. Під час фільтрування введіть посилання на скопійовані підписи стовпців у полі Діапазон для результату. Скопійовані рядки міститимуть лише стовпці, для яких скопійовано підписи.

  6. У полі діапазон умов введіть посилання на діапазон умов, включно з підписами умов. За допомогою приклад, введіть $A$1:$C$3.To зі шляху переміщення діалогового вікна Розширений фільтр , тимчасово, коли ви вибираєте діапазон умов, натисніть кнопку Згорнути діалогове вікно Зображення кнопки .

  7. Відповідно до прикладу відфільтровані результати для вихідного діапазону будуть такі:

    Тип

    Продавець

    Продажі

    Овочі

    Пустовіт

    6 328 грн.

    Овочі

    Давидова

    6 544 грн.

Узагальнені умови

Логічний вираз:    Продавець = ім’я з другою буквою "у"

  1. Щоб знайти текстові значення, що містять кілька спільних символів (але не всі), виконайте одну або кілька таких дій:

    • Введіть один або кілька символів без знака рівності (=), щоб знайти рядки з текстовим значенням у стовпці, який починається цими символами. Наприклад, якщо використати текст Дав як умову, програма Excel знайде "Давидова", "Давид" і "Давиденко".

    • Використайте символи узагальнення.

      Введіть

      Щоб знайти

      ? (знак питання)

      Будь-який один символ
      Наприклад, sm?th буде знайдено слова «Мавка» та «марка»

      * (зірочка)

      Будь-яку кількість символів
      Наприклад, *-Західний «Відповідають слова» і "Північ"

      ~ (тильда) зі знаком ?, * або ~ в кінці

      Знак питання, зірочку або тильду
      Наприклад, фр91 ~? знаходить "fy91?"

  2. Вставте принаймні три пусті рядки над вихідним діапазоном, які використовуватимуться як діапазон умов. Діапазон умов має містити підписи стовпців. Переконайтеся, що між значеннями умов і вихідним діапазоном є принаймні один пустий рядок.

  3. У рядках під підписами стовпців введіть умови, які потрібно використовувати. Відповідно до прикладу введіть:

    Тип

    Продавець

    Продажі

    ="=М’я*"

    ="=?у*"

  4. Клацніть клітинку у вихідному діапазоні. Відповідно до прикладу клацніть будь-яку клітинку у вихідному діапазоні A6:C10.

  5. На вкладці Дані у групі Сортування й фільтр виберіть пункт Додатково.

    Група "Сортування й фільтр" на вкладці "Дані"

  6. Виконайте одну з таких дій:

    • Щоб відфільтрувати вихідний діапазон, приховавши рядки, які не відповідають указаним умовам, виберіть варіант фільтрувати список на місці.

    • Щоб відфільтрувати вихідний діапазон, скопіювавши рядки, які відповідають указаним умовам, до іншої області аркуша, виберіть варіант скопіювати результат до іншого розташування. Потім клацніть поле Діапазон для результату та в лівому верхньому куті області, куди потрібно вставити рядки.

      Порада.: У разі копіювання відфільтрованих рядків до іншого місця можна вказати стовпці, які долучатимуться до копії. Перед фільтруванням скопіюйте заголовки потрібних стовпців до першого рядка області вставлення результатів. Виконуючи фільтрування, введіть посилання на скопійовані заголовки стовпців у полі Діапазон для результату. Скопійовані рядки будуть містити лише стовпці, заголовки яких скопійовано.

  7. У полі Діапазон умов введіть посилання на діапазон умов, зокрема підписи умов. Відповідно до прикладу введіть $A$1:$B$3.

    Щоб перемістити діалогового вікна Розширений фільтр до речі, тимчасово, коли ви вибираєте діапазон умов, натисніть кнопку Згорнути діалогове вікно Зображення кнопки .

  8. Відповідно до прикладу відфільтровані результати для вихідного діапазону будуть такі:

    Тип

    Продавець

    Продаж

    Напої

    Семенів

    5 122 грн.

    М’ясо

    Давидова

    450 грн.

    продукти

    Пустовіт

    6 328 грн.

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech (у розділі Excel), отримати підтримку в спільноті, що допомагає знайти відповіді на запитання, або запропонувати нову функцію чи вдосконалення на форумі Excel User Voice.

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×