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

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

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

Появление функции ФИЛЬТР (в английской версии – FILTER) в Excel 365 и Excel 2021 становится долгожданной альтернативой традиционному фильтру. В отличие от него, формулы Excel автоматически пересчитываются при каждом изменении листа, поэтому вам нужно будет настроить фильтрацию только один раз.

Основные сведения

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

Если вы можете создать выражение, которое возвращает ИСТИНА или ЛОЖЬ, то вы можете использовать это условие для извлечения данных с помощью ФИЛЬТР.

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

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

Синтаксис функции ФИЛЬТР следующий:

ФИЛЬТР(массив; включить; [если_пусто])

Где:

  • Массив (обязательно) — диапазон или массив значений, которые вы хотите отфильтровать.
  • Включить (обязательно) — критерии, предоставляемые в виде логического массива (значения ИСТИНА и ЛОЖЬ).
    Его высота (когда данные находятся в столбцах) или ширина (когда данные находятся в строках) должны быть равны высоте аргумента массива.
  • Если_пусто (необязательно) — если функция не находит ни одного значения, удовлетворяющего условию, то она выдаёт ошибку #ВЫЧИСЛ! Чтобы вывести вместо ошибки что-то более осмысленное, можно использовать этот третий аргумент.

Функция ФИЛЬТР доступна только в Excel для Microsoft 365 и Excel 2021. В Excel 2019, Excel 2016 и более ранних версиях она не поддерживается.

Базовая формула ФИЛЬТР в Excel

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

Предположим, что из приведенного ниже набора данных вы хотите извлечь записи с определенным значением в столбце Группа, скажем, группу А. Чтобы это сделать, мы передаем выражение B2:B13="А" в аргумент включить , который создаст требуемый логический массив, где ИСТИНА соответствует значениям «А».

=ФИЛЬТР(A2:C13; B2:B13=”A”; "Нет результата")

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

=ФИЛЬТР(A2:C13; B2:B13=F1; "Нет результата") 

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

простой пример функции ФИЛЬТР

Если ни одна запись не соответствует указанным критериям, формула возвращает значение, которое вы указали в аргументе если_пусто: «Нет результата» в нашем примере.

нет результата фильтрации

Если в случае отрицательного результата вы предпочитаете ничего не возвращать, укажите пустую строку ("") в качестве последнего аргумента:

=ФИЛЬТР(A2:C13; B2:B13=F1; "") 

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

=ФИЛЬТР(B1:M3;B2:M2= B5; "Нет результата")

необходима одинаковая ширина диапазонов в аргументах функции ФИЛЬТР

Что нужно помнить 

Чтобы эффективно фильтровать в Excel с помощью формул, следует обратить внимание на несколько важных моментов:

  • Функция ФИЛЬТР автоматически распределяет результаты по вертикали или горизонтали на листе, в зависимости от того, как организованы исходные данные. Поэтому убедитесь, что у вас всегда достаточно пустых ячеек внизу и справа, иначе вы получите ошибку #ПЕРЕНОС! .
  • Результаты функции ФИЛЬТР в Excel являются динамическими, то есть они обновляются автоматически при каждом изменении значений в исходном наборе данных. Однако диапазон, указанный для аргумента массив, не обновляется при добавлении новых записей в исходные данные. Если вы хотите, чтобы размер массива изменялся автоматически, преобразуйте данные в таблицу Excel и используйте структурированные ссылки на строку или столбец таблицы, или создайте динамический именованный диапазон.

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

Фильтр с несколькими критериями (логика И)

Чтобы фильтровать данные по нескольким критериям, вы объединяете два или более логических выражения:

ФИЛЬТР(массив; ( диапазон1 = критерий1 ) * ( диапазон2 = критерий2 ); «Нет результата»)

Операция умножения обрабатывает массивы с помощью логики И, предусматривая, что возвращаются только те записи, которые соответствуют всем критериям. Технически это работает так:

Результатом каждого логического выражения является массив логических значений, где ИСТИНА соответствует 1, а ЛОЖЬ — это 0. (диапазон1 = критерий1 ) * ( диапазон2 = критерий2 )  - это два массива логических значений. Элементы этих массивов, находящиеся в одинаковых  позициях, перемножаются. Поскольку умножение на ноль всегда дает ноль, в итоговом массиве значение 1 будуи иметь только те элементы, для которых все критерии имеют значение ИСТИНА. Только эти элементы и будут извлечены.

Теперь рассмотрим как это выглядит на примерах.

Пример 1. Фильтрация нескольких столбцов в Excel

Давайте отфильтруем наши условные данные по двум столбцам: Группа (столбец B) и Посещения (столбец C).

Для этого мы установили следующие критерии: введите название целевой группы в F2 ( критерий1 ) и минимально необходимое количество посещений в F3 ( критерий2 ).

Учитывая, что наши исходные данные находятся в A2:C13 ( массив ), группы — в B2:B13 ( диапазон1 ), а количество посещений — в C2:C13 ( диапазон2 ), формула принимает такой вид:

=ФИЛЬТР(A2:C13; (B2:B13=F2) * (C2:C13>=F3); "Нет результата") 

В результате вы получите список студентов группы А, посетивших занятия 5 и более раз:

Фильтрация нескольких столбцов в Excel

Пример 2. Фильтрация данных по датам

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

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

Обратите внимание, что в данном случае оба критерия применяются к одному и тому же диапазону:

=ФИЛЬТР(A2:D13; (D2:D13>=G2) * (D2:D13<=G3); "Нет результата") 

Здесь G2 и G3 — начальная и конечная даты.

фильтр по датам в Excel

Фильтр с несколькими условиями (логика ИЛИ)

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

Когда логические массивы, возвращаемые выражениями, суммируются, результирующий массив будет иметь 0 для записей, которые не соответствуют ни одному из критериев (т. е. все критерии ЛОЖЬ), и такие записи будут исключены. Будут извлечены только записи, для которых хотя бы один критерий имеет значение ИСТИНА.

Вот общая формула для фильтрации данных на основе логики ИЛИ:

ФИЛЬТР(массив; ( диапазон1 = критерий1 ) + ( диапазон2 = критерий2 ); «Нет результатов»)

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

Предполагая, что исходные данные находятся в A2:C13, список количества посещений — в C2:C13, а интересующие нас  — в F2 и F3, формула будет выглядеть следующим образом:

=ФИЛЬТР(A2:C13; (C2:C13=F2) + (C2:C13=F3); "Нет результата") 

В итоге вы узнаете, какие студенты посетили все занятия (8), а какие не появились ни разу (0):

фильтр с несколькими условиями И

Фильтр на основе нескольких критериев И и ИЛИ 

В ситуации, когда вам необходимо применить оба типа критериев, помните это простое правило: объединяйте критерии «И» знаком умножения (*), а критерии «ИЛИ» знаком сложения (+).

Например, чтобы вернуть список учеников, которые имеют заданное количество посещений (F2) И принадлежат к группе, указанной в E2 ИЛИ E3, постройте следующую цепочку логических выражений:

=ФИЛЬТР(A2:C13; (C2:C13=F2) * ((B2:B13=E2) + (B2:B13=E3)); "Нет результата") 

И вы получите следующее:

фильтр с множеством условий И и ИЛИ

Как фильтровать дубликаты в Excel

При работе с огромными рабочими листами или при объединении данных из разных источников часто существует вероятность появления дубликатов записей.

Если ваша цель — фильтровать дубликаты, то есть извлекать записи, которые встречаются более одного раза, используйте функцию ФИЛЬТР вместе с  СЧЁТЕСЛИМН.

Идея состоит в том, чтобы получить количество вхождений для всех записей и извлечь те, которые встречаются более одного раза. Чтобы получить количество, вы указываете один и тот же диапазон для каждой пары диапазон_условия / условие в  СЧЁТЕСЛИМН, например:

ФИЛЬТР( массив ; СЧЁТЕСЛИМН( столбец1 ; столбец1 ; столбец2 ; столбец2 )>1 ; «Нет результатов»)

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

=ФИЛЬТР(A2:C20; СЧЁТЕСЛИМН(A2:A20; A2:A20; B2:B20; B2:B20; C2:C20; C2:C20)>1; "Нет результатов")

Как фильтровать дубликаты в Excel

Примечание. Чтобы отфильтровать дубликаты на основе значений в определенных столбцах, включите в функцию СЧЁТЕСЛИМН только эти конкретные столбцы.

Как отфильтровать пустые ячейки 

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

Чтобы идентифицировать непустые ячейки, вы используете оператор «не равно» (<>) вместе с пустой строкой («»), например:

ФИЛЬТР(массив ; ( столбец1 <>"") * ( столбец2 =<>"") ; "Нет результата")

Если исходные данные записаны в A2:C12, то для фильтрации строк, содержащих одну или несколько пустых ячеек, в E3 вводится следующая формула:

=ФИЛЬТР(A2:C12;(A2:A12<>"")*(B2:B12<>"")*(C2:C12<>"");"Нет результата")

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

Как отфильтровать пустые ячейки

Как фильтровать ячейки, содержащие определенный текст

Функция ФИЛЬТР не поддерживает подстановочные знаки (*? ~). Тем не менее, вы можете обойти это ограничение, объединив функцию ФИЛЬТР вместе с функцией ПОИСК, которая позволит отпределить частичное совпадение текстовых значений.

ФИЛЬТР(массив ; ЕЧИСЛО(ПОИСК(" искомый_текст "; текст_для_поиска )); "Нет результата")

Вот как это работает:

  • Функция ПОИСК ищет указанную текстовую строку в заданном диапазоне и возвращает либо число (положение первого символа), либо ошибку #ЗНАЧ! (то есть, текст не найден).
  • Функция ЕЧИСЛО преобразует все эти числа в ИСТИНА, а ошибки – в ЛОЖЬ, и передает результирующий логический массив во второй  аргумент функции, который определяет, какие записи включить в фильтр.

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

=ФИЛЬТР(A2:C13;ЕЧИСЛО(ПОИСК(F2;A2:A13)); "Нет результата")

В результате формула возвращает список товаров, в названии категорий которых встречается “сок”:

Как фильтровать ячейки, содержащие определенный текст

Фильтровать и вычислить (Сумма, Среднее, Минимум, Максимум и т. д.)

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

Для этого объедините ее с математическими функциями, такими как  СУММ, СРЗНАЧ, СЧËТ, МАКС или МИН.

Например, вы можете использовать следующие формулы:

Всего посещений занятий:

=СУММ(ФИЛЬТР(C2:C13;B2:B13=F1; 0))

Среднее число явок на каждого учащегося:

=СРЗНАЧ(ФИЛЬТР(C2:C13;B2:B13=F1; 0))

Максимальное:

=МАКС(ФИЛЬТР(C2:C13;B2:B13=F1; 0))

Минимальное:

=МИН(ФИЛЬТР(C2:C13;B2:B13=F1; 0))

Обратите внимание, что во всех формулах мы используем ноль в качестве аргумента если_пусто, поэтому формулы возвращают 0, если не найдено значений, соответствующих условиям.

Ввод любого текста, например «Нет результата», приведет к ошибке #ЗНАЧ а это, очевидно, совсем не то, что вам нужно :)

вычисления в фильтре Excel

Формула ФИЛЬТР с учетом регистра

Стандартная формула Excel ФИЛЬТР нечувствительна к регистру, то есть не делает различий между строчными и прописными буквами. Чтобы различать регистр текста, используйте функцию СОВПАД в условии фильтрации. Это заставит формулу выполнить логическую проверку с учетом регистра букв:

ФИЛЬТР(массив ; СОВПАД( диапазон ; критерии ) ; «Нет результата»)

Предположим, у вас есть группы A и a , и вы хотите извлечь записи, в которых группа представляет собой строчную букву «a». Для этого используйте следующую формулу, где A2:C13 — исходные данные, а B2:B13 — группы для фильтрации:

=ФИЛЬТР(A2:C13;СОВПАД(B2:B13;F1); "Нет результата")

Как обычно, вы можете ввести целевую группу в заранее определенную ячейку, скажем, F1, и использовать эту ссылку на ячейку вместо жестко запрограммированного текста:

Формула ФИЛЬТР с учетом регистра

Как фильтровать и возвращать только определенные столбцы

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

Пример 1. Фильтр нескольких соседних столбцов

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

Предположим, в базовом примере формулы ФИЛЬТР вы хотите вернуть первые два столбца ( Имя и Группа ). Итак, вы указываете A2:B13 в качестве первого аргумента:

=ФИЛЬТР(A2:B13; B2:B13=F1; "Нет результата")

В результате получаем список участников группы, указанной в F1:

как фильтровать отдельные столбцы

Пример 2. Фильтр несмежных столбцов

Чтобы формула ФИЛЬТР возвращала несмежные столбцы, используйте этот хитрый трюк:

  1. Создайте формулу ФИЛЬТР с желаемыми условиями, используя всю таблицу для аргумента массив. Мы это уже делали во всех примерах.
  2. Вложите эту формулу в другую функцию ФИЛЬТР. Чтобы настроить функцию-оболочку, используйте значения ИСТИНА и ЛОЖЬ  или 1 и 0 для аргумента включить, где 1 указывает столбцы, которые нужно показать, а 0 отмечает столбцы, которые следует исключить.

Например, чтобы вернуть только имена (первый столбец) и посещения (третий столбец), мы используем {1;0;1} или {ИСТИНА;ЛОЖЬ;ИСТИНА} в качестве аргумента внешней функции ФИЛЬТР:

=ФИЛЬТР(ФИЛЬТР(A2:C13; B2:B13=F1; "Нет результата");{1;0;1})

фильтр для несмежных столбцов

Как ограничить количество строк, возвращаемых формулой ФИЛЬТР

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

Давайте посмотрим, как это работает на примере уже рассмотренной нами простой формулы, которая извлекает имена из целевой группы:

=ФИЛЬТР(A2:C13; B2:B13=F1; "Нет результата") 

Она выводит все найденные записи, в нашем случае 4 строки. Но предположим, что у вас есть место только для двух, а ниже записаны какие-то другие нужные данные. Чтобы вывести только первые две найденные строки, вам нужно сделать следующее:

  • Подставьте формулу ФИЛЬТР в аргумент массив функции ИНДЕКС.
  • В качестве аргумента номер_строки функции ИНДЕКС используйте константу вертикального массива, например {1:2}. Он определяет, сколько строк нужно вернуть (в нашем случае 2).
  • В качестве аргумента номер_столбца используйте константу горизонтального массива, например {1;2;3}. Он указывает, какие столбцы возвращать (первые три столбца в этом примере).
  • Чтобы предотвратить возможные ошибки, если данные, удовлетворяющие вашим условиям, не найдены, вы можете обернуть формулу функцией ЕСЛИОШИБКА.

Полная формула принимает такой вид:

=ЕСЛИОШИБКА(ИНДЕКС(ФИЛЬТР(A2:C13; B2:B13=F1); {1:2}; {1;2;3}); "Нет результата")

как ограничить количество строк в формуле ФИЛЬТР

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

Нет проблем, функция ПОСЛЕД может автоматически генерировать для вас последовательные номера:

=ЕСЛИОШИБКА(ИНДЕКС(ФИЛЬТР(A2:C13; B2:B13=F1); ПОСЛЕД(2); ПОСЛЕД(1;ЧИСЛСТОЛБ(A1:C1);1;1)); "Нет результата")

Первая ПОСЛЕД генерирует вертикальный массив, содержащий столько последовательных чисел, сколько указано в первом (и единственном) аргументе. Вторая ПОСЛЕД использует функцию ЧИСЛСТОЛБ для подсчета количества столбцов в наборе данных и создает соответствующий горизонтальный массив.

Примечание. Чтобы вернуть данные из определенных столбцов , а не из всех, в константу горизонтального массива, которую вы используете для аргумента номер_столбца функции ИНДЕКС, включите только эти конкретные числа. Например, чтобы извлечь данные из 1-го и 3-го столбцов, используйте {1;3}.

Функция ФИЛЬТР Excel не работает?

В ситуации, когда ваша формула ФИЛЬТР приводит к ошибке, скорее всего, это будет одна из следующих:

#ВЫЧИСЛ! 

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

Ошибка #ЗНАЧ!

Происходит, когда массивы значений в первом и втором аргументах имеют несовместимые размеры.

#Н/Д, #ЗНАЧ и т. д.

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

Ошибка #ИМЯ!

Возникает при попытке использовать ФИЛЬТР в более старой версии Excel. Помните, что это новая функция, доступная только в Office 365 и Excel 2021.

В новой версии Excel ошибка #ИМЯ возникает, если вы случайно неправильно написали имя функции.

Ошибка #ПЕРЕНОС!

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

Чтобы это исправить, просто очистите или удалите непустые ячейки. 

#ССЫЛКА! ошибка

Происходит, когда формула ФИЛЬТР используется между разными книгами, а исходная книга с данными закрыта.

Вот как можно динамически фильтровать данные в Excel при помощи формул. 

Благодарю вас за чтение!