Перейти до основного
Office

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

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

Якщо дані, які потрібно відфільтрувати, потребують складних умов (наприклад, Type = "продукція" або "продавець =" Давиденко "), можна скористатися діалоговим вікном розширеного фільтра .

Щоб відкрити діалогове вікно Розширений фільтр , виберіть пункт дані _ gt/_ Додатково.

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

Приклад

Overview

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

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

Кілька умов, кілька стовпців, усі умови мають логічне значення true

Тип = "Овочі" І Продаж, грн. > 1000

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

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

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

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

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

(Продавець = "Давидова" і продажі >3000) АБО
(Продавець = "Петренко" та "продажі _ Gtо _ 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 визначає та відображає

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

=Давидова

="=3 000"

=3 000

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

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

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

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

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

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

  • Формула має повертати результат TRUE або FALSE.

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

    =''= запис ''

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

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

  • У формулі, яка використовується для створення умов, має застосовуватися відносне посилання на відповідну клітинку в першому рядку даних.

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

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

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

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

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

    Тип

    Продавець

    Продаж, грн.

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

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

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

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

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

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

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

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

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

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

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

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

    Тип

    Продавець

    Продаж, грн.

    М’ясо

    Давидова

    450 грн.

    Овочі

    Пустовіт

    6 328

    Овочі

    Давидова

    6 544

Кілька умов, кілька стовпців, усі умови мають логічне значення true

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

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

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

    Тип

    Продавець

    Продаж, грн.

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

    >1 000

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

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

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

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

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

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

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

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

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

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

    Тип

    Продавець

    Продаж, грн.

    Овочі

    Пустовіт

    6 328

    Овочі

    Давидова

    6 544

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

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

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

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

    Тип

    Продавець

    Продаж, грн.

    ="=Овочі"

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

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

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

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

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

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

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

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

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

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

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

    Тип

    Продавець

    Продаж, грн.

    Овочі

    Пустовіт

    6 328

    Овочі

    Давидова

    6 544

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

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

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

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

    Тип

    Продавець

    Продажі

    Продаж, грн.

    >6 000

    <6 500

    <500

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

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

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

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

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

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

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

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

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

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

    Тип

    Продавець

    Продаж, грн.

    М’ясо

    Давидова

    450 грн.

    Овочі

    Пустовіт

    6 328

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

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

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

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

    Тип

    Продавець

    Продаж, грн.

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

    >3 000

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

    >1 500

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

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

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

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

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

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

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

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

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

    Тип

    Продавець

    Продаж, грн.

    Овочі

    Пустовіт

    6 328

    Овочі

    Давидова

    6 544

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

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

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

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

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

      Символ

      Щоб знайти

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

      Будь-який
      один символ Наприклад, sm?th знаходить "Коваль" і "Smyth"

      * (зірочка)

      Будь-яку кількість
      символів Наприклад, * Схід знаходить "Північний схід" і "Південний схід"

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

      Знак запитання, зірочка або Тильда
      Наприклад, ФР 91 ~? Наприклад, за умовою "фр91~?" буде знайдено "фр91?".

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

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

    Тип

    Продавець

    Продаж, грн.

    ="=М’я*"

    ="=?у*"

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

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

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

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

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

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

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

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

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

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

    Тип

    Продавець

    Продаж, грн.

    Напої

    Семенів

    5 122

    М’ясо

    Давидова

    450 грн.

    Овочі

    Пустовіт

    6 328

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

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

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

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

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

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

×