Функция ФИЛЬТР в Excel (FILTER): синтаксис, условия и ошибки

Функция ФИЛЬТР и автофильтр — в чём разница

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

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

С какой версии Excel доступна функция ФИЛЬТР

ФИЛЬТР входит в семейство динамических массивов и появилась начиная с Excel 2019.

В Excel 2016 и более ранних версиях используйте альтернативы: комбинацию ИНДЕКС + ПОИСКПОЗ или Расширенный фильтр (вкладка «Данные» → «Дополнительно»). Перед отправкой файла с функцией ФИЛЬТР проверяйте версию Excel у получателя.

Синтаксис функции ФИЛЬТР и её аргументы

Полная формула:

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

Два первых аргумента обязательны, третий — нет.

Если хотите разобраться с ФИЛЬТР и другими инструментами Excel на практике, посмотрите программу Excel. Системный аналитик: моделирование данных в табличных процессорах — 72 часа, обучение с нуля, бесплатно по нацпроекту «Кадры».

Аргумент «массив»

Диапазон или умная таблица (Ctrl+T) с данными для фильтрации: A2:D100 или Таблица[#Данные]. При использовании умной таблицы структурированная ссылка расширяется автоматически при добавлении новых строк.

Аргумент «включить»

Логический массив той же высоты, что и «массив»: ИСТИНА — строка проходит фильтр, ЛОЖЬ — отсеивается. Пример: B2:B100=»Анна» формирует массив ИСТИНА/ЛОЖЬ для каждой строки диапазона.

Аргумент «если_пусто»

Необязательный. Если ни одна строка не прошла условие, функция возвращает заданный текст вместо ошибки #ВЫЧИСЛ!. Рекомендация: всегда добавляйте ;»Нет строк» или ;»» — это страхует результат при пустой выборке.

Несколько условий в функции ФИЛЬТР: логика И и ИЛИ

Аргумент «включить» работает не с операторами AND()/OR(), а с числами 0 и 1: умножение (*) реализует логику И, сложение (+) — логику ИЛИ.

Условие И — оператор умножения (*)

Механика: ИСТИНА × ИСТИНА = 1, ЛОЖЬ × любое = 0. Строка проходит только если все условия дают ИСТИНА.

=ФИЛЬТР(A2:D100;(B2:B100=»Анна»)*(C2:C100=»Оплачен»);»Нет строк»)

Каждое условие — в скобках. Дополнительные критерии добавляются ещё одним множителем.

Условие ИЛИ — оператор сложения (+)

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

=ФИЛЬТР(A2:D100;(B2:B100=»Москва»)+(B2:B100=»Казань»);»Нет строк»)

Смешанная логика И + ИЛИ

«(Москва ИЛИ Казань) И Оплачен» записывается как ((усл1)+(усл2))*(усл3). Скобки обязательны из-за приоритета операций. Для отладки выделите условие в строке формул и нажмите F9 — Excel покажет массив из 0 и 1.

Текстовые, числовые и датовые условия в ФИЛЬТР

Числа и даты сравниваются операторами >, <, =. Данные должны храниться как числа — если в ячейке текстовое число или дата с апострофом, сравнение не сработает. Для дат верхняя граница <дата+1 корректно захватывает весь последний день, включая записи с временно́й составляющей. Апострофы, пробелы и числа в текстовом формате в исходнике дадут неверный результат — ФИЛЬТР источник не исправляет.

Поиск текстового фрагмента через ЕЧИСЛО и ПОИСК

Для поиска части строки используйте пару ПОИСК + ЕЧИСЛО в аргументе «включить»:

=ФИЛЬТР(A2:D100;ЕЧИСЛО(ПОИСК(F1;C2:C100));»Нет строк»)

ПОИСК находит позицию фрагмента без учёта регистра или возвращает ошибку; ЕЧИСЛО преобразует результат: позиция → ИСТИНА, ошибка → ЛОЖЬ. Критерий удобно вынести в именованный диапазон или отдельную ячейку.

Сочетание функции ФИЛЬТР с СОРТ и УНИК

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

• =СОРТ(ФИЛЬТР(…)) — сначала отбирает строки по условию, затем сортирует.
• =УНИК(ФИЛЬТР(…)) — возвращает уникальные значения среди прошедших фильтрацию.

Функция LET упрощает длинные вложения: позволяет задать имена промежуточным массивам и не повторять одни и те же диапазоны несколько раз. Power Query предпочтительнее, когда нужно объединить несколько источников или выполнить сложные преобразования данных перед выборкой.

Ошибки функции ФИЛЬТР: причины и устранение

Два типичных сбоя: #ПЕРЕНОС! — физическое препятствие для разлива, #ВЫЧИСЛ! — пустой результат без третьего аргумента.

Ошибка #ПЕРЕНОС! (#SPILL!) — причина и решение

Причина: данные, объединённая ячейка или другой массив занимают область разлива. При выделении ячейки с ошибкой Excel показывает предполагаемую границу разлива пунктиром. Освободите эту область — ошибка исчезнет. Не размещайте формулу ФИЛЬТР внутри строк самой умной таблицы.

Ошибка #ВЫЧИСЛ! (#CALC!) — причина и решение

Причина: ни одна строка не прошла условие, третий аргумент не задан. Добавьте ;»Нет строк» или ;»» — пустая строка подавляет ошибку. Не оборачивайте всю формулу в ЕСЛИОШИБКА: это скроет реальные ошибки в данных источника.

Пример: динамический отчёт с функцией ФИЛЬТР

Задача: таблица «Заказы» с колонками Дата, Регион, Статус, Сумма. Условия вводятся в ячейки H1 (регион), H2 (дата от), H3 (дата до).

=ФИЛЬТР(Заказы;(Заказы[Регион]=H1)*(Заказы[Статус]=»Оплачен»)*(Заказы[Дата]>=H2)*(Заказы[Дата]<H3+1);»Нет заказов»)

Результат разливается автоматически и пересчитывается при изменении любой ячейки условий. Проверьте четыре сценария: одна строка, несколько строк, пустой результат и граничная дата — убедитесь, что последний день включён корректно.

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

Чем функция ФИЛЬТР отличается от автофильтра в Excel?

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

Как называется функция ФИЛЬТР в английской версии Excel?

В англоязычной версии функция называется FILTER. Синтаксис полностью идентичен: =FILTER(array,include,[if_empty]). Аргументы соответствуют: array = массив, include = включить, if_empty = если_пусто. Функция доступна в Excel 2019, Excel 2021 и Microsoft 365 как в русской, так и в английской локализации.

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

Нет. ФИЛЬТР входит в семейство динамических массивов, появившееся в пакете 2019 года, и недоступна в Excel 2016, 2013 и более ранних версиях. В Excel 2016 используйте комбинацию ИНДЕКС + ПОИСКПОЗ или Расширенный фильтр (вкладка «Данные» → «Дополнительно»). Перед отправкой файла с функцией ФИЛЬТР обязательно проверяйте версию Excel у получателя.

Как задать три условия одновременно в функции ФИЛЬТР?

Перемножьте три логических массива оператором *: =ФИЛЬТР(A2:D100;(B2:B100=»Анна»)*(C2:C100=»Оплачен»)*(D2:D100>1000);»Нет строк»). Строка попадает в результат только если все три условия дают ИСТИНА — по принципу 1×1×1=1. Каждое новое условие добавляется ещё одним множителем. Следите, чтобы все диапазоны условий имели одинаковую высоту с аргументом «массив».

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

Выберите пустую ячейку вне исходной таблицы. Введите =ФИЛЬТР(, укажите диапазон данных (например, A2:C20), через точку с запятой задайте условие: B2:B20=»Москва», добавьте третий аргумент ;»Нет данных» и закройте скобку. Нажмите Enter — результат разольётся автоматически на нужное число строк. Ctrl+Shift+Enter нажимать не нужно: в динамических массивах это лишнее.

Почему функция ФИЛЬТР не включает последний день периода?

Ячейки с датами могут хранить время после 00:00:00, и условие <=дата_конца пропускает такие записи. Правильная верхняя граница — <H3+1 (меньше начала следующего дня): это корректно включает все записи последнего дня, в том числе те, у которых есть временна́я составляющая.

Можно ли разместить функцию ФИЛЬТР внутри таблицы Excel?

Формулу ФИЛЬТР нельзя помещать в ячейку, из которой разлив пойдёт по строкам самой таблицы — возникнет ошибка #ПЕРЕНОС!. Размещайте формулу в свободной области листа за пределами таблицы. Исходные данные, напротив, удобно оформить умной таблицей (Ctrl+T): структурированные ссылки вида Таблица[Столбец] автоматически охватывают новые строки без редактирования формулы.

Как получить уникальные значения из отфильтрованного диапазона?

Вложите ФИЛЬТР внутрь УНИК: =УНИК(ФИЛЬТР(A2:B100;C2:C100=»Активен»;»»)). Сначала ФИЛЬТР отберёт строки по условию, затем УНИК удалит дублирующиеся значения из результата. Обе функции доступны только в Excel 2019 и Microsoft 365. Результат разливается динамически и обновляется при изменении исходных данных.