Добір із використанням розширеного фільтра

Увага! : Цю статтю перекладено за допомогою служби машинного перекладу; див. застереження. Версію цієї статті англійською мовою див. тут для отримання довідки.

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

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

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

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

Приклад

Огляд

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

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

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

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

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

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

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

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

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

(Продавець = "Давидова" І Продаж, грн. > 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 не розрізняє великі й малі букви. Проте, можна використовувати формулу для виконання пошуку з урахуванням регістру. Наприклад у розділі умови з узагальненням.

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

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

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

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

  • Формула має обчислювати значення TRUE або FALSE.

  • Формула в умові вводиться у звичайному форматі для формул, а не у форматі:

    =''= запис ''

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

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

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

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

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

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

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

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

    Символи

    Продавець

    Продажі:

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

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

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

  4. На вкладці Data, у групі Sort & Filter клацніть елемент Advanced.

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

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

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

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

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

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

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

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

    Символи

    Продавець

    Продажі:

    М'ясо

    Давиденко

    450 грн.

    продукти

    Петренко

    6 328 грн.

    Продукти

    Давиденко

    6 544 грн.

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

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

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

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

    Символи

    Продавець

    Продажі:

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

    >1000

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

  4. На вкладці Data, у групі Sort & Filter клацніть елемент Advanced.

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

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

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

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

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

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

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

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

    Символи

    Продавець

    Продажі:

    продукти

    Петренко

    6 328 грн.

    Продукти

    Давиденко

    6 544 грн.

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

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

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

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

    Символи

    Продавець

    Продажі:

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

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

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

  4. На вкладці Data, у групі Sort & Filter клацніть елемент Advanced.

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

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

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

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

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

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

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

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

    Символи

    Продавець

    Продажі:

    продукти

    Петренко

    6 328 грн.

    Продукти

    Давиденко

    6 544 грн.

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

Булева логіка:     ( (Продаж > 6000 AND Продаж < 6500 ) OR (Продаж < 500) )

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

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

    Символи

    Продавець

    Продажі:

    Продажі:

    >6000

    <6500

    <500

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

  4. На вкладці Data, у групі Sort & Filter клацніть елемент Advanced.

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

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

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

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

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

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

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

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

    Символи

    Продавець

    Продажі:

    М'ясо

    Давиденко

    450 грн.

    продукти

    Петренко

    6 328 грн.

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

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

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

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

    Символи

    Продавець

    Продажі:

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

    >3000

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

    >1500

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

  4. На вкладці Data, у групі Sort & Filter клацніть елемент Advanced.

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

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

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

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

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

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

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

    Символи

    Продавець

    Продажі:

    продукти

    Петренко

    6 328 грн.

    Продукти

    Давиденко

    6 544 грн.

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

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

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

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

    • Скористайтеся знаками підстановки.

      Знак

      Об’єкт пошуку

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

      Будь-який окремий символ.
      Наприклад, умові «ма?ка» відповідають результати «мавка» та «марка».

      * (зірочка)

      Будь-який набір символів.
      Наприклад, умові «пів*» відповідають результати «південь» і «північ».

      ~ (тильда), за якою йде знак ?, * або ~

      Знак питання, зірочку або тильду
      Наприклад, за умовою «ан91~?» буде знайдено слово «ан91?»

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

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

    Символи

    Продавець

    Продажі:

    ="=М’*"

    ="=?а*"

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

  5. На вкладці Data, у групі Sort & Filter клацніть елемент Advanced.

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

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

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

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

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

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

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

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

    Символи

    Продавець

    Продажі:

    Напої

    Василенко

    5 122 грн.

    М'ясо

    Давиденко

    450 грн.

    продукти

    Петренко

    6 328 грн.

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

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

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

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

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

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

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

×