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

• Видобути унікальний список
• Фільтрувати в одному стовпці з кількома критеріями (відповідати будь-яким критеріям)
• Фільтруйте в кількох стовпцях за кількома критеріями
  • З логікою І (відповідає всім критеріям)
  • З логікою АБО (відповідає будь-яким критеріям)
  • З логікою І, а також АБО
• Розширений фільтр із символом підстановки
• Витягувати лише певні стовпці

Розширений фільтр проти звичайного фільтра

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

• Дозволяє використовувати кілька критеріїв у різних стовпцях.
• Надання можливості отримувати унікальні значення з набору даних.
• Увімкнення використання символів підстановки для більш гнучкого, часткового зіставлення.
• Дозволяє витягувати відфільтровані дані в окреме місце.

Приклади використання розширеного фільтра

У цьому розділі наведено різні практичні приклади, щоб показати вам, як використовувати розширені фільтри в Excel для досягнення різних ефектів фільтрації.

Дивовижний! Використання ефективних вкладок у Excel, таких як Chrome, Edge, Firefox і Safari!
Заощаджуйте 50% свого часу та зменшуйте тисячі клацань миші щодня!

Видобути унікальний список

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

1. Перейти до дані вкладка, виберіть Advanced в Сортувати та фільтрувати група.
   
2. У Розширений фільтр діалогове вікно, вам потрібно налаштувати наступним чином.
   1. У дію виберіть потрібний варіант. Оскільки я хочу розмістити унікальний список в іншому місці, я вибираю Скопіюйте в інше місце варіант.
   2. Укажіть розділ діапазону списку:
      • Видобути унікальне значення з одного стовпця:
        Виберіть стовпець, який містить значення, з яких потрібно витягти унікальні записи. Наприклад, щоб отримати унікальні імена клієнтів у цьому випадку, виберіть A1:A11.
      • Витягніть унікальні рядки на основі кількох стовпців:
        Виберіть діапазон, який включає всі стовпці, які ви розглядаєте. У цьому випадку, оскільки я хочу отримати унікальні рядки на основі імен клієнтів, продажу та регіону, я вибираю весь діапазон A1:C11.
   3. У Скопіювати до укажіть, куди потрібно вставити унікальний список.
   4. Перевірте Тільки унікальні записи прапорець.
   5. Натисніть OK кнопку. Дивіться знімок екрана:
      

Результат

Як показано на знімку екрана нижче, унікальні рядки витягуються з вихідного діапазону даних.

Фільтрувати в одному стовпці з кількома критеріями (відповідати будь-яким критеріям)

Фільтрування даних в одному стовпці за кількома критеріями дозволяє відображати рядки, які відповідають будь-якій із заданих умов. Це може бути особливо корисним, коли ви працюєте з великими наборами даних і вам потрібно звузити інформацію на основі кількох потенційних збігів. Ось як цього можна досягти за допомогою функції розширеного фільтра Excel:

Крок 1: Підготуйте вихідні дані діапазону списку

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

Крок 2: Налаштування діапазону критеріїв

1. У діапазоні вище або окремо від діапазону списку створіть свій діапазон критеріїв. Заголовки, які ви вводите в діапазон критеріїв, повинні точно відповідати заголовкам у діапазоні списку, щоб працювати правильно. Тут мій діапазон критеріїв розташований над діапазоном списку.
2. Під заголовком перелічіть усі критерії, яким потрібно відповідати. Кожен критерій має бути в окремій клітинці, безпосередньо під попереднім. Це налаштування повідомляє Excel відповідати будь-якому з цих критеріїв.
   У цьому прикладі я шукаю студентів з бали більше 95 або менше 60 так що я можу ефективно відфільтрувати діапазон списку, щоб включити як високих, так і низьких балів. Тому я вводжу кожен критерій в окремі рядки під заголовком Оцінка. Весь діапазон критеріїв показано нижче:
   

Крок 3. Застосуйте розширений фільтр

Тепер ви можете застосувати розширений фільтр, щоб виконати завдання наступним чином.

1. Перейти до дані Вкладка і виберіть Advanced в Сортувати та фільтрувати група.
   
2. У Розширений фільтр діалогове вікно, вам потрібно налаштувати наступним чином.
   1. У дію виберіть потрібний варіант. Тут, оскільки я хочу розмістити відфільтрований результат в іншому місці, я вибираю Скопіюйте в інше місце варіант.
   2. У Діапазон списку виберіть весь діапазон списку A7: D17.
   3. У Діапазон критеріїв виберіть весь діапазон критеріїв A2: D4.
   4. У Скопіювати до укажіть, куди потрібно вставити відфільтрований результат (тут я вибираю клітинку F8).
   5. Натисніть OK щоб застосувати фільтр. Перегляньте скріншот:
      

Результат

Тоді ви побачите, що вилучаються лише ті рядки, у яких стовпець «Оцінка» відповідає будь-якому критерію (>95 або <60).

Фільтруйте в кількох стовпцях за кількома критеріями

Розглянувши фільтрацію за кількома критеріями в одному стовпці, тепер ми звернемо увагу на фільтрацію за кількома стовпцями. Цей розділ допоможе вам застосувати кілька критеріїв до різних стовпців за допомогою І, АБО та комбінованої логіки І/АБО.

• Щоб застосувати логіку І, помістіть критерії в один рядок.
• Щоб застосувати логіку АБО, розмістіть умови в окремих рядках.

З логікою І (відповідає всім критеріям)

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

Крок 1: Підготуйте вихідні дані діапазону списку

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

Крок 2: Налаштування діапазону критеріїв

1. Створіть свій діапазон критеріїв над діапазоном списку або окремо від нього, ввівши заголовки, які точно відповідають заголовкам у діапазоні списку. Тут мій діапазон критеріїв розташований над діапазоном списку.
2. для І логіка, перерахуйте всі критерії в одному рядку під відповідними заголовками. Наприклад, якщо я хочу відфільтрувати учнів класу A з результатами понад 85, тоді діапазон критеріїв слід встановити так:
   

Крок 3. Застосуйте розширений фільтр

Тепер ви можете застосувати розширений фільтр, щоб виконати завдання наступним чином.

1. Перейти до дані Вкладка і виберіть Advanced в Сортувати та фільтрувати група.
   
2. У Розширений фільтр діалогове вікно, вам потрібно налаштувати наступним чином.
   1. У дію виберіть потрібний варіант. Тут, оскільки я хочу розмістити відфільтрований результат в іншому місці, я вибираю Скопіюйте в інше місце варіант.
   2. У Діапазон списку виберіть весь діапазон списку A7: D16.
   3. У Діапазон критеріїв виберіть весь діапазон критеріїв A2: D3.
   4. У Скопіювати до укажіть, куди потрібно вставити відфільтрований результат (тут я вибираю клітинку F6).
   5. Натисніть OK щоб застосувати фільтр. Перегляньте скріншот:
      

Результат

У результаті буде показано або скопійовано лише рядки, які відповідають усім критеріям у вказаних стовпцях. У нашому прикладі вилучаються лише учні класу А з балами вище 85.

З логікою АБО (відповідає будь-яким критеріям)

Щоб відфільтрувати дані в кількох стовпцях за допомогою логіки АБО (що відповідає будь-якому критерію) у розширеному фільтрі Excel, виконайте такі дії:

Крок 1: Підготуйте вихідні дані діапазону списку

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

Крок 2: Налаштування діапазону критеріїв

1. Створіть свій діапазон критеріїв над діапазоном списку або окремо від нього, ввівши заголовки, які точно відповідають заголовкам у діапазоні списку. Тут мій діапазон критеріїв розташований над діапазоном списку.
2. За допомогою логіки АБО розмістіть кожен набір критеріїв для одного стовпця в окремих рядках або вкажіть кожен критерій в окремих рядках під відповідним заголовком. Наприклад, якщо я хочу відфільтрувати студентів із балами, вищими за 90, або оцінками F, діапазон критеріїв потрібно встановити таким чином:
   

Крок 3. Застосуйте розширений фільтр

Тепер ви можете застосувати розширений фільтр, щоб виконати завдання наступним чином.

1. Перейти до дані Вкладка і виберіть Advanced в Сортувати та фільтрувати група.
   
2. У Розширений фільтр діалогове вікно, вам потрібно налаштувати наступним чином.
   1. У дію виберіть потрібний варіант. Тут, оскільки я хочу розмістити відфільтрований результат в іншому місці, я вибираю Скопіюйте в інше місце варіант.
   2. У Діапазон списку виберіть весь діапазон списку A7: D17.
   3. У Діапазон критеріїв виберіть весь діапазон критеріїв A2: D4.
   4. У Скопіювати до укажіть, куди потрібно вставити відфільтрований результат (тут я вибираю клітинку F8).
   5. Натисніть OK щоб застосувати фільтр. Перегляньте скріншот:
      

Результат

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

У цьому випадку фільтр поверне лише студентів, чий бал вище 90 або з оцінкою F.

З логікою І, а також АБО

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

Крок 1: Підготуйте вихідні дані діапазону списку

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

Крок 2: Налаштування діапазону критеріїв

1. Створіть діапазон критеріїв над діапазоном списку або поруч із ним. Додайте заголовки стовпців, які точно відповідають заголовкам у діапазоні списку. Тут мій діапазон критеріїв розташований над діапазоном списку.
2. Під заголовками введіть критерії, використовуючи комбінацію логіки І та АБО.
   • для І логіка, критерії з різних стовпців повинні бути розміщені в одному рядку.
   • для OR за логікою критерії слід розміщувати в окремих рядках.
   • для комбінована логіка І-АБОорганізуйте кожен набір умов АБО в окремих блоках рядків. У кожному блоці розмістіть критерії І в одному рядку.
     Наприклад, щоб відфільтрувати учнів у класі A з балами понад 90 або в класі B з оцінкою B, встановіть діапазон критеріїв таким чином:
     

Крок 3. Застосуйте розширений фільтр

Тепер ви можете застосувати розширений фільтр, щоб виконати завдання наступним чином.

1. Перейти до дані Вкладка і виберіть Advanced в Сортувати та фільтрувати група.
   
2. У Розширений фільтр діалогове вікно, вам потрібно налаштувати наступним чином.
   1. У дію виберіть потрібний варіант. Тут, оскільки я хочу розмістити відфільтрований результат в іншому місці, я вибираю Скопіюйте в інше місце варіант.
   2. У Діапазон списку виберіть весь діапазон списку A7: D17.
   3. У Діапазон критеріїв виберіть весь діапазон критеріїв A2: D4.
   4. У Скопіювати до укажіть, куди потрібно вставити відфільтрований результат (тут я вибираю клітинку F8).
   5. Натисніть OK щоб застосувати фільтр. Перегляньте скріншот:
      

Результат

Excel відображатиме лише ті рядки, які відповідають вашій складній комбінації критеріїв.

У цьому прикладі розширений фільтр поверне лише студентів із балами понад 90 у класі A або студентів із оцінкою B у класі B.

Розширений фільтр із символом підстановки

Використання символів узагальнення з розширеним фільтром Excel забезпечує більш гнучкий і потужний пошук даних. Символи узагальнення — це спеціальні символи, які представляють один або кілька символів у рядку, що полегшує фільтрування текстових шаблонів. Ось докладні інструкції щодо використання розширеного фільтра із символами підстановки в Excel.

Крок 1: Підготуйте вихідні дані діапазону списку

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

Крок 2: Налаштування діапазону критеріїв

1. Створіть діапазон критеріїв над діапазоном списку або поруч із ним. Додайте заголовки стовпців, які точно відповідають заголовкам у діапазоні списку. Тут мій діапазон критеріїв розташований над діапазоном списку.
2. Під заголовком введіть критерії за допомогою символів підстановки.
   • *: представляє будь-яку кількість символів і може використовуватися до, після або всередині рядка.
   • ?: представляє один символ у певній позиції.
   У цьому прикладі я хочу відфільтрувати імена, які починаються з букви «J», тому я вводжу J* під заголовком Ім’я діапазону критеріїв. Перегляньте скріншот:
   

Крок 3. Застосуйте розширений фільтр

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

1. Перейти до дані Вкладка і виберіть Advanced в Сортувати та фільтрувати група.
   
2. У Розширений фільтр діалогове вікно, налаштуйте наступним чином.
   1. У дію виберіть потрібний варіант. Тут, оскільки я хочу розмістити відфільтрований результат в іншому місці, я вибираю Скопіюйте в інше місце варіант.
   2. У Діапазон списку виберіть весь діапазон списку A6: B11.
   3. У Діапазон критеріїв виберіть весь діапазон критеріїв A2: B3.
   4. У Скопіювати до укажіть, куди потрібно вставити відфільтрований результат (тут я вибираю клітинку D7).
   5. Натисніть OK щоб застосувати фільтр. Перегляньте скріншот:
      

Результат

Розширений фільтр відображатиме лише ті рядки зі стовпця «Ім’я», імена яких починаються з літери «J», дотримуючись шаблону, визначеного символом підстановки в діапазоні критеріїв.

Витягувати лише певні стовпці

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

Припустімо, що ваш набір даних знаходиться в діапазоні A7:D17, і ви хочете відфільтрувати ці дані на основі критеріїв, указаних у B2:D4, і витягнути лише ІМ'Я, Рахунок і Grade колонки. Ось як це зробити.

Крок 1. Укажіть стовпці для вилучення

Під набором даних або поруч із ним впишіть заголовки стовпців, які потрібно витягти. Це визначає діапазон «Копіювати до», де відображатимуться відфільтровані дані. У цьому прикладі я вводжу ІМ'Я, Рахунок та Grade заголовки в діапазоні F7:H7.

Крок 2. Застосуйте розширений фільтр

Тепер ви можете застосувати розширений фільтр, щоб фільтрувати лише певні стовпці на основі вказаних критеріїв.

1. Перейти до дані Вкладка і виберіть Advanced в Сортувати та фільтрувати група.
   
2. У Розширений фільтр діалогове вікно, налаштуйте наступним чином.
   1. У дію виберіть розділ Скопіюйте в інше місце варіант.
   2. У Діапазон списку виберіть весь діапазон списку A7: D17.
   3. У Діапазон критеріїв виберіть весь діапазон критеріїв A2: D4.
   4. У Скопіювати до виберіть діапазон (F7:H7 у цьому випадку), де ви написали заголовки стовпців, які хочете витягти.
   5. Натисніть OK щоб застосувати фільтр. Перегляньте скріншот:
      

Результат

Ви бачите, що результат вилучення включає лише вказані стовпці.

Примітки для розширеного фільтра

Статті по темі