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

Посмотреть видео - Расширенный фильтр Excel

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

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

В этом руководстве я покажу вам несколько интересных вещей, которые вы можете делать с помощью расширенного фильтра Excel.

Но сначала … Что такое расширенный фильтр Excel?

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

Вот некоторые различия между обычным фильтром и расширенным фильтром:

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

EXCEL ADVANCED FILTER (Примеры)

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

Пример 1 - Извлечение уникального списка

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

В Excel 2007 и более поздних версиях есть возможность удалить дубликаты из набора данных. Но это меняет ваш существующий набор данных. Чтобы сохранить исходные данные нетронутыми, вам необходимо создать копию данных, а затем использовать опцию «Удалить дубликаты». Расширенный фильтр Excel позволит вам выбрать место для получения уникального списка.

Давайте посмотрим, как использовать расширенные фильтры, чтобы получить уникальный список.

Предположим, у вас есть набор данных, как показано ниже:

Как видите, в этом наборе данных есть повторяющиеся записи (выделены оранжевым). Это могло быть связано с ошибкой при вводе данных или результатом компиляции данных.

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

Вот шаги, чтобы получить все уникальные записи:

• Выберите весь набор данных (включая заголовки).
• Перейдите на вкладку «Данные» -> «Сортировка и фильтр» -> «Дополнительно». (Вы также можете использовать сочетание клавиш - Alt + A + Q). Это откроет диалоговое окно Advanced Filter.
• В диалоговом окне Advanced Filter используйте следующие данные:
  • Действие: Выберите вариант «Копировать в другое место». Это позволит вам указать место, где вы можете получить список уникальных записей.
  • Диапазон списка: Убедитесь, что он относится к набору данных, из которого вы хотите найти уникальные записи. Также убедитесь, что в набор данных включены заголовки.
  • Диапазон критериев: Оставьте это пустым.
  • Скопировать в: Укажите адрес ячейки, по которой вы хотите получить список уникальных записей.
  • Копировать только уникальные записи: Отметьте эту опцию.
• Щелкните ОК.

Это мгновенно предоставит вам список всех уникальных записей.

Осторожность: Если вы используете расширенный фильтр для получения уникального списка, убедитесь, что вы также выбрали заголовок. В противном случае первая ячейка будет считаться заголовком.

Пример 2 - Использование критериев в расширенном фильтре Excel

Получение уникальных записей - одна из многих вещей, которые вы можете сделать с помощью расширенного фильтра Excel.

Его основная полезность заключается в том, что он позволяет использовать сложные критерии для фильтрации данных.

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

Вот как можно использовать расширенный фильтр Excel для фильтрации записей на основе заданных критериев:

• Первым шагом при использовании расширенного фильтра Excel со сложными критериями является определение критериев. Для этого скопируйте заголовки и вставьте их где-нибудь на листе.
• Укажите критерии, по которым вы хотите фильтровать данные. В этом примере, поскольку мы хотим получить все записи для США с продажами более 5000, введите «США» в ячейку под регионом и> 5000 в ячейку под продажами. Теперь это будет использоваться в качестве входных данных в расширенном фильтре для получения отфильтрованных данных (как показано в следующих шагах).
• Выберите весь набор данных (включая заголовки).
• Перейдите на вкладку «Данные» -> «Сортировка и фильтр» -> «Дополнительно». Это откроет диалоговое окно Advanced Filter.
• В диалоговом окне Advanced Filter используйте следующие данные:
  • Действие: Выберите вариант «Копировать в другое место». Это позволит вам указать место, где вы можете получить список уникальных записей.
  • Диапазон списка: Убедитесь, что он относится к набору данных, из которого вы хотите найти уникальные записи. Также убедитесь, что в набор данных включены заголовки.
  • Диапазон критериев: Укажите критерии, которые мы создали на шагах выше. В этом примере это будет F1: I3.
  • Скопировать в: Укажите адрес ячейки, по которой вы хотите получить список уникальных записей.
  • Копировать только уникальные записи: Отметьте эту опцию.
• Щелкните ОК.

Это мгновенно предоставит вам все записи, где регион находится в США, а продажи превышают 5000.

Приведенный выше пример представляет собой случай, когда фильтрация выполняется на основе двух критериев (США и объем продаж более 5000).

Расширенный фильтр Excel позволяет создавать множество различных комбинаций критериев.

Вот несколько примеров того, как вы можете создать эти фильтры.

Использование критериев И

Если вы хотите использовать критерий И, вам нужно указать его под заголовком.

Например:

• Для фильтрации записей, когда регион - США, а торговый представитель - Джо.
• Чтобы отфильтровать записи, когда регион находится в США, И значение продаж превышает 5000.
• Когда регион находится в США, И продажи регистрируются после 31 марта 2017 г.

Использование критерия ИЛИ

Если вы хотите использовать критерий ИЛИ, вам необходимо указать критерии в том же столбце.

Например:

• Для фильтрации записей, когда регион - США ИЛИ регион - Азия.
• Для фильтрации записей, когда торговым представителем является Боб ИЛИ Марта.

К настоящему времени вы, должно быть, поняли, что когда у нас есть критерии в одной строке, это И критериев, и когда они у нас есть в разных строках, это ИЛИ критерии.

Пример 3 - Использование символов WILDCARD в расширенном фильтре в Excel

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

В Excel есть три подстановочных символа:

1. * (звездочка) - Он представляет собой любое количество символов. Например, ex * может означать excel, excels, example, expert и т. Д.
2. ? (вопросительный знак) - Он представляет собой один-единственный персонаж. Например, Tr? Mp может означать Трампа или Бродяги.
3. ~ (тильда) - Он используется для обозначения подстановочного знака (~, *,?) В тексте.

Теперь давайте посмотрим, как мы можем использовать эти подстановочные знаки для расширенной фильтрации в Excel.

• Чтобы отфильтровать записи, в которых имя торгового представителя начинается с J.

Обратите внимание, что * обозначает любое количество символов. Таким образом, любой представитель с именем, начинающимся с J, будет отфильтрован по этим критериям.

Точно так же вы можете использовать два других подстановочных знака.

Примечание. Если вы используете Office 365, вам следует проверить функцию ФИЛЬТР. Он может делать много вещей, которые расширенный фильтр может делать с помощью простой формулы.

ПРИМЕЧАНИЕ:

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