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

Перегляд відео - Розширений фільтр Excel

Розширений фільтр Excel-одна з найбільш недооцінених та недостатньо використовуваних функцій, з якими я стикався.

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

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

Але спочатку … Що таке розширений фільтр Excel?

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

Ось деякі відмінності між звичайним фільтром і розширеним фільтром:

• Хоча звичайний фільтр даних фільтруватиме наявний набір даних, ви можете використовувати розширений фільтр Excel для вилучення набору даних у інше місце.
• Розширений фільтр Excel дозволяє використовувати складні критерії. Наприклад, якщо у вас є дані про продажі, ви можете відфільтрувати дані за критерієм, де представником з продажу є Боб, а регіон - Північний або Південний (ми побачимо, як це зробити у прикладах). Служба підтримки Office має хороші пояснення з цього приводу.
• Ви можете використовувати розширений фільтр Excel для вилучення унікальних записів з ваших даних (докладніше про це за секунду).

EXCEL ADVANCED FILTER (Приклади)

Тепер давайте розглянемо приклад використання розширеного фільтра в Excel.

Приклад 1 - Вилучення унікального списку

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

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

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

Припустимо, у вас є набір даних, як показано нижче:

Як бачите, у цьому наборі даних є дублікати записів (виділено помаранчевим кольором). Це може бути через помилку введення даних або результат компіляції даних.

У такому випадку ви можете скористатися інструментом Excel Advanced Filter, щоб швидко отримати список усіх унікальних записів в іншому місці (щоб ваші вихідні дані залишалися неушкодженими).

Нижче описано, як отримати всі унікальні записи:

• Виберіть весь набір даних (включаючи заголовки).
• Перейдіть на вкладку Дані -> Сортування та фільтр -> Додатково. (Ви також можете скористатися комбінацією клавіш - Alt + A + Q). Відкриється діалогове вікно Розширений фільтр.
• У діалоговому вікні Розширений фільтр використовуйте такі відомості:
  • Дія: Виберіть опцію "Скопіювати в інше місце". Це дозволить вам вказати місце розташування, де можна отримати список унікальних записів.
  • Діапазон списку: Переконайтеся, що він посилається на набір даних, з якого ви хочете знайти унікальні записи. Також переконайтеся, що заголовки в наборі даних включені.
  • Діапазон критеріїв: Залиште це порожнім.
  • Скопіювати до: Вкажіть адресу комірки, за якою потрібно отримати список унікальних записів.
  • Копіювати лише унікальні записи: Перевірте цей варіант.
• Натисніть OK.

Це миттєво дасть вам список усіх унікальних записів.

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

Приклад 2 - Використання критеріїв у розширеному фільтрі Excel

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

Його основна корисність полягає в його здатності дозволяти використовувати складні критерії для фільтрації даних.

Ось що я маю на увазі під складними критеріями. Припустимо, у вас є набір даних, як показано нижче, і ви хочете швидко отримати всі записи, де продажі перевищують 5000, а регіон - США.

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

• Перший крок при використанні розширеного фільтра Excel зі складними критеріями - це визначення критеріїв. Для цього скопіюйте заголовки та вставте їх кудись на аркуші.
• Вкажіть критерії, за якими потрібно фільтрувати дані. У цьому прикладі, оскільки ми хочемо отримати всі записи для США з продажами понад 5000, введіть "США" у клітинку під Регіоном та> 5000 у клітинку під Продажами. Тепер це буде використовуватися як вхід у Розширеному фільтрі для отримання відфільтрованих даних (як показано в наступних кроках).
• Виберіть весь набір даних (включаючи заголовки).
• Перейдіть на вкладку Дані -> Сортування та фільтр -> Додатково. Відкриється діалогове вікно Розширений фільтр.
• У діалоговому вікні Розширений фільтр використовуйте такі відомості:
  • Дія: Виберіть опцію "Скопіювати в інше місце". Це дозволить вам вказати розташування, де можна отримати список унікальних записів.
  • Діапазон списку: Переконайтеся, що він посилається на набір даних, з якого ви хочете знайти унікальні записи. Також переконайтеся, що заголовки в наборі даних включені.
  • Діапазон критеріїв: Вкажіть критерії, які ми побудували на кроках вище. У цьому прикладі це буде F1: I3.
  • Скопіювати до: Вкажіть адресу комірки, за якою потрібно отримати список унікальних записів.
  • Копіювати лише унікальні записи: Перевірте цей варіант.
• Натисніть OK.

Це миттєво дасть вам усі записи, де регіон - США, а продажі - понад 5000.

Наведений вище приклад - це випадок, коли фільтрація проводиться за двома критеріями (США та продажі більше 5000).

Розширений фільтр Excel дозволяє створювати багато різних комбінацій критеріїв.

Ось кілька прикладів того, як можна побудувати ці фільтри.

Використання критеріїв І

Якщо ви хочете використовувати критерії AND, потрібно вказати його під заголовком.

Наприклад:

• Для фільтрації записів, коли регіоном є США, а представником відділу продажів є Джо.
• Для фільтрації записів, коли регіоном є США І вартість продажів перевищує 5000.
• Якщо регіоном є США І продажі реєструються після 31-03-2017.

Використання критеріїв АБО

Якщо ви хочете використовувати критерії АБО, вам потрібно вказати критерії в цьому ж стовпці.

Наприклад:

• Для фільтрації записів, коли регіоном є США АБО регіоном є Азія.
• Для фільтрації записів, коли представником відділу продажів є БОБ АБО Марта.

Ви, напевно, вже зрозуміли, що коли у нас є критерії в одному рядку, це є І критерії, і коли ми маємо його в різних рядках, це є АБО критерії.

Приклад 3 - Використання символів WILDCARD у Розширеному фільтрі в Excel

Розширений фільтр Excel також дозволяє використовувати символи підстановки під час складання критеріїв.

В Excel є три символи підстановки:

1. * (зірочка) - Він позначає будь -яку кількість символів. Наприклад, ex* може означати excel, excels, наприклад, експерт тощо.
2. ? (знак питання) - Він символізує одного символу. Наприклад, Tr? Mp може означати Трампа або Бродягу.
3. ~ (тильда) - Він використовується для ідентифікації символу підстановки (~, *,?) У тексті.

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

• Для фільтрації записів, де ім’я торгового представника починається з J.

Зверніть увагу, що * представляє будь -яку кількість символів. Таким чином, будь -який представник з назвою, що починається на J, буде відфільтрований за цими критеріями.

Подібним чином можна використовувати й інші два символи підстановки.

Примітка: Якщо ви використовуєте Office 365, перевірте функцію ФІЛЬТР. Він може зробити багато речей, які просунутий фільтр може зробити за допомогою простої формули.

ПРИМІТКА:

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