Как использовать фильтры в Microsoft Excel

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

Вы можете использовать базовый фильтр, который добавляет кнопки для фильтрации данных, расширенный фильтр для поиска данных, соответствующих различным конкретным условиям, или функцию FILTER, чтобы отобразить значение, если данные не найдены.

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

Содержание

• Как использовать базовый автофильтр в Excel
• Используйте числовые, текстовые или дата-фильтры
• Очистить или отключить фильтры
• Как использовать расширенный фильтр в Excel
• Введите критерии
• Примените фильтр
• Как использовать функцию FILTER в Excel
• Используйте AND или OR с функцией FILTER
• Часто задаваемые вопросы

Как использовать базовый автофильтр в Excel

Базовый автофильтр в Excel — это самый простой метод и он хорошо работает с большинством таблиц. Выберите данные, которые хотите отобразить, затем посмотрите, как ваш существующий набор данных обновляется, чтобы показать только эти значения.

1. Выберите данные, которые хотите отфильтровать, перетаскивая по диапазону ячеек или выбирая целые столбцы и строки.

1. Перейдите на вкладку “Данные” и нажмите “Фильтр“ в разделе “Сортировка и фильтр“ на ленте.

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

1. Выберите кнопку для столбца, который хотите отфильтровать. В всплывающем окне введите значение в поле поиска или отметьте флажки внизу для тех элементов, которые хотите отобразить, и нажмите “ОК”.

1. Ваш набор данных обновится, чтобы отобразить только отфильтрованные данные.

Используйте числовые, текстовые или дата-фильтры

Используя базовый фильтр в Excel, вы можете выйти за рамки основ. Вместо того чтобы выбирать точные элементы, которые хотите видеть, вы можете добавить условие. Это работает для данных, которые включают числа, текст и даты. Давайте рассмотрим пример каждого.

Следуйте тем же шагам выше, чтобы выбрать данные и применить фильтр для отображения кнопок.

Выберите кнопку фильтра для столбца, который хотите использовать для сортировки. В всплывающем окне перейдите к числовым фильтрам, текстовым фильтрам или дата-фильтрам.

Числовые фильтры

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

1. Выберите условие, которое хотите добавить, в выпадающем меню.

1. Если ваш выбор требует дополнительного ввода, вы увидите появление окна “Пользовательский автофильтр“. Подтвердите условие в первом выпадающем списке, затем добавьте значение в текстовое поле или используйте его выпадающий список для выбора.

1. При желании вы можете добавить другое условие, используя оператор AND или OR. Используйте поля для настройки этого условия.

1. Когда закончите, нажмите “ОК”, чтобы увидеть ваши отфильтрованные данные.

Текстовые фильтры

Текстовые фильтры работают аналогично числовым фильтрам в Excel. Вы можете добавить условие, например, “начинается с”, “заканчивается на”, “содержит” или “не содержит”.

1. Выберите условие, которое хотите добавить, в выпадающем меню.

1. Введите ваше условие(я) в окне “Пользовательский автофильтр“. Обратите внимание, что вы можете ввести вопросительный знак (?) для обозначения одного символа или звездочку (*) для серии символов.

1. Нажмите “ОК”, и вы увидите ваш обновленный набор данных.

Дата-фильтры

Как и числа и текст, вы можете добавить условия к базовому дата-фильтру. Выберите условие, например, “до”, “после”, “между”, “сегодня” или “вчера”, а также “следующая” или “последняя неделя”, “месяц”, “год” или “квартал”.

1. Выберите условие, которое хотите добавить, в выпадающем меню.

1. Если ваш выбор требует ввода, введите его в окне “Пользовательский автофильтр“. При желании добавьте другое условие, если хотите, и нажмите “ОК”, чтобы применить дата-фильтр.

1. Ваш набор данных обновится.

Очистить или отключить фильтры

• Чтобы удалить фильтр позже, нажмите ту же кнопку фильтра и выберите “Очистить фильтр из”.

• Чтобы отключить фильтр и удалить кнопки, снимите выделение с кнопки “Фильтр“ на ленте.

Как использовать расширенный фильтр в Excel

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

1. Чтобы использовать расширенный фильтр, убедитесь, что ваши данные содержат заголовки столбцов или “метки”. В этом примере вы можете увидеть наши метки Супервизор, Сектор, Отдел и Продажи.

1. Вставьте как минимум несколько строк над вашим набором данных. Это место, где вы введете критерии для вашего фильтра. Введите те же метки, что и в вашем наборе данных.

Введите критерии

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

Ниже приведены некоторые примеры:

• Равно Север: ="=Север"
• Не равно Север: ="<>Север"
• Больше 500: =">500"
• Меньше 500: ="<500"
• Больше или равно 500: =">=500"
• Меньше или равно 500: ="<=500"

Когда вы вводите условие, Excel распознает его и форматирует его по мере необходимости для фильтра.

• Для простого примера мы фильтруем наши данные по имени Супервизора Браун. В первой строке в столбце Супервизор мы ввели условие как ="=Браун".

• Чтобы удовлетворить двум условиям в одном столбце, введите критерии в соответствующий столбец в разных строках. Здесь мы фильтруем как по Брауну, так и по Мартину. Мы ввели ="=Браун" в первой строке столбца Супервизор и ="=Мартин" во второй строке столбца Супервизор.

• Чтобы удовлетворить двум условиям в разных столбцах, введите критерии в соответствующие столбцы и в одной строке. Мы фильтруем как по Брауну, так и по Югу. Мы ввели ="=Браун" в столбце Супервизор и ="=Юг" в столбце Сектор.

• Чтобы удовлетворить любое условие, введите критерии в соответствующие столбцы, но в разных строках. Мы отфильтровали по Брауну или Еде, введя ="=Браун" в первой строке столбца Супервизор и ="=Еда" во второй строке столбца Отдел.

• Чтобы удовлетворить все и любые условия, используйте комбинацию разных столбцов и строк. Чтобы отфильтровать по Брауну и Северу или Еде, мы ввели ="=Браун" и ="=Север" в первой строке столбцов Супервизор и Сектор, затем ввели ="=Еда" во второй строке столбца Отдел.

Примените фильтр

После того как вы ввели условия, которые хотите использовать, пришло время применить расширенный фильтр.

1. Перейдите на вкладку “Данные” и выберите кнопку “Расширенный фильтр“ на ленте.

1. Когда откроется окно “Расширенный фильтр“, настройте фильтр. Начните с выбора, где вы хотите разместить отфильтрованные данные. Если вы выберете “Копировать в другое место”, поле “Копировать в” станет доступным для ввода этого местоположения.

1. Введите набор данных в поле “Диапазон списка”. Вы также можете выбрать ячейки, перетаскивая курсор по ним, что заполнит это поле.

1. Введите “Диапазон критериев” в это поле. Это диапазон над вашим набором данных, где вы добавили новые строки. Снова вы можете перетаскивать курсор по диапазону, чтобы автоматически заполнить поле.

1. Если вы хотите найти уникальные записи, отметьте поле “Только уникальные записи”. Выберите “ОК”, чтобы применить фильтр.

1. Вы увидите отфильтрованные данные либо на месте, либо в выбранном вами месте.

Совет: если вы хотите найти дубликаты, а не уникальные значения, ознакомьтесь с нашим руководством по поиску и удалению дубликатов в Excel.

Как использовать функцию FILTER в Excel

Еще один способ фильтрации в Excel — это функция с тем же названием. Используя функцию FILTER, вы можете фильтровать по числу, тексту или дате, используя ссылку на ячейку. Кроме того, вы можете ввести значение, если формула не находит элементы.

Синтаксис формулы: FILTER(range, include, not_found), где только первые два аргумента обязательны.

Чтобы начать, мы используем простой пример. Мы фильтруем диапазон от A1 до D6 по диапазону от B1 до B6, где элемент равен значению в ячейке B2 (Юг).

=FILTER(A1:D6,B1:B6=B3)

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

=FILTER(A1:D6,B1:B6="Юг")

Мы получили два результата для нашего фильтра, используя обе формулы.

Теперь мы фильтруем те же диапазоны и включаем необязательный аргумент на случай, если результат не найден. Он будет отображаться как “Нет”.

=FILTER(A1:D6,B1:B6="Вниз","Нет")

Используйте AND или OR с функцией FILTER

Если вы хотите фильтровать по нескольким критериям с помощью функции, вы можете вставить звездочку (*) для удовлетворения обоих условий (AND) или знак плюс (+) для удовлетворения любого условия (OR).

Мы фильтруем тот же набор данных по Югу и Одежде, используя эту формулу:

=FILTER(A1:D6,(B1:B6=B3)*(C1:C6=C3))

Мы получили один результат, как и ожидалось. Хотя в диапазоне от B1 до B6 есть две записи с Югом, только одна из них также имеет Одежду в ячейках от C1 до C6.

Чтобы показать, как работает оператор OR для функции FILTER, мы фильтруем тот же набор данных по Югу или Еде с помощью этой формулы:

=FILTER(A1:D6,(B1:B6=B3)+(C1:C6=C6))

На этот раз мы получили три результата: два с Югом и один с Едой.

Часто задаваемые вопросы

В чем разница между фильтрацией и сортировкой?

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

Есть ли сочетание клавиш для фильтрации в Excel?

Чтобы активировать автофильтр в Excel, используйте сочетание клавиш Ctrl + Shift + L на Windows или Command + Shift + F на Mac.

Затем выберите столбец и нажмите Alt + стрелка вниз на Windows или Option + стрелка вниз на Mac, чтобы открыть всплывающее окно фильтра и сделать свой выбор.

Как я могу фильтровать ячейки в Excel с помощью формул?

Выберите диапазон ячеек или весь лист. Перейдите на вкладку “Главная” и выберите “Найти и выделить -> Перейти к специальному”. В окне отметьте “Формулы” и при желании отметьте элементы под ним, затем выберите “ОК”. Вы увидите выделенные ячейки, содержащие формулы.

Кредит изображения: Pixabay. Все скриншоты сделаны Сэнди Уритенхаус.