Как использовать функцию ФИЛЬТР в Microsoft Excel

Резюме:

Чтобы использовать функцию ФИЛЬТР, просто введите массив и диапазон для ваших критериев. Чтобы избежать ошибки Excel для пустых результатов фильтра, используйте третий необязательный аргумент для отображения пользовательского индикатора.

Microsoft Excel предлагает встроенную функцию фильтрации, а также возможность использования расширенного фильтра. Но если вы хотите отфильтровать по нескольким критериям и даже отсортировать результаты, воспользуйтесь функцией ФИЛЬТР в Excel.

Используя функцию ФИЛЬТР, вы можете использовать операторы «и» и «или» для объединения критериев. В качестве бонуса мы покажем вам, как применить функцию СОРТИРОВКИ к формуле, чтобы отобразить ваши результаты в порядке возрастания или убывания по определенному столбцу.

Что такое функция ФИЛЬТР в Excel?

Синтаксис формулы следующий: FILTER(array, range=criteria, if_empty), где требуются только первые два аргумента. Вы можете использовать ссылку на ячейку, число или текст в кавычках для критериев, в зависимости от ваших данных.

Используйте третий необязательный аргумент, если ваш набор данных может вернуть пустой результат, поскольку он будет отображать #CALC! ошибка по умолчанию. Чтобы заменить сообщение об ошибке, вы можете заключить текст, букву или число в кавычки или просто оставить кавычки пустыми для пустой ячейки.

Как создать базовую формулу фильтра

Для начала мы начнем с базового фильтра, чтобы вы могли увидеть, как работает функция. На каждом снимке экрана вы увидите результаты нашего фильтра справа.

Для фильтрации данных в ячейках с A2 по D13 с использованием содержимого ячейки B2 (Электроника) в качестве критерия используется следующая формула:

=FILTER(A2:D13,B2:B13=B2)

Чтобы разбить формулу, вы видите, что аргумент массив имеет значение A2:D13, а аргумент range=criteria имеет значение B2:B13=B2. Это возвращает все результаты, содержащие Electronics.

Другой способ написать формулу — ввести содержимое ячейки B2 в кавычках следующим образом:

=FILTER(A2:D13,B2:B13="Electronics")

Вы также можете использовать критерии из другой ячейки для фильтрации данных в области range=criteria. Здесь мы будем использовать данные в ячейке B15.

=FILTER(A2:D13,B2:B13=B15)

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

=FILTER(A2:D13,D2:D13=10)

Если вы не получаете никаких результатов для своей формулы или видите сообщение #CALC! ошибка, вы можете использовать третий аргумент if_empty. Например, мы отобразим None, если результат пуст.

=FILTER(A2:D13,D2:D13=75,"None")

Как видите, данные range=criteria не включают 75, поэтому наш результат — None.

Фильтр с использованием нескольких критериев в функции FILTER

Преимущество функции ФИЛЬТР в Excel заключается в том, что вы можете фильтровать по нескольким критериям. Вы добавите оператор И (*) или ИЛИ (+).

Например, мы будем фильтровать наш набор данных как по A3 (Запад), так и по B2 (электроника), используя звездочку (*) по этой формуле:

=FILTER(A2:D13,(A2:A13=A3)*(B2:B13=B2))

Как видите, у нас есть один результат, который включает в себя и West, и Electronics.

Чтобы использовать другой оператор, мы будем фильтровать либо A3, либо B2, используя знак плюс (+) следующим образом:

=FILTER(A2:D13,(A2:A13=A3)+(B2:B13=B2))

Теперь вы можете видеть, что наши результаты содержат пять записей с West или Electronics.

Как отсортировать отфильтрованные данные в Excel

Если вы хотите отсортировать результаты, полученные с помощью функции ФИЛЬТР, вы можете добавить в формулу функцию СОРТИРОВКА. Это просто альтернатива использованию функции сортировки на вкладке «Данные», но она не требует от вас изменения положения ваших данных.

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

Здесь мы будем использовать наш базовый фильтр из начала этого руководства: FILTER(A2:D13,B2:B13=B2). Затем мы добавим SORT с его аргументами для сортировки по четвертому столбцу (Потери) в порядке убывания (-1):

=SORT(FILTER(A2:D13,B2:B13=B2),4,-1)

Чтобы разбить эту формулу, у нас есть формула FILTER в качестве аргумента array для функции SORT. После этого у нас есть 4 для сортировки по четвертому столбцу в наборе данных и -1 для отображения результатов в порядке убывания.

Чтобы вместо этого отобразить результаты в порядке возрастания, замените -1 на 1:

=SORT(FILTER(A2:D13,B2:B13=B2),4,1)

Встроенный фильтр Excel отлично подходит для быстрого просмотра определенных записей в наборе данных. А расширенный фильтр хорошо работает для фильтрации по диапазону критериев на месте или в другом месте. Но для одновременного использования нескольких критериев и сортировки воспользуйтесь функцией ФИЛЬТР.