Функция ФИЛЬТР в Excel: несколько условий и динамические массивы

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

Функция ФИЛЬТР и обычный фильтр решают разные задачи

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

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

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

Синтаксис функции

Массив — данные, которые нужно вернуть. Включить — последовательность ИСТИНА/ЛОЖЬ той же высоты или ширины. Необязательный аргумент если_пусто задаёт результат, если ни одна строка не прошла условие.

Если A2:D100 — таблица заказов, а B содержит менеджера, выборка строк Анны выглядит так: =ФИЛЬТР(A2:D100;B2:B100="Анна";"Нет строк"). Сравнение формирует массив ИСТИНА и ЛОЖЬ, по которому Excel возвращает полные строки из A:D.

Несколько условий через логику И

Для И перемножьте логические проверки. ИСТИНА превращается в 1, ЛОЖЬ — в 0; только строка, где все множители равны 1, остаётся в результате. Заказы Анны со статусом «Оплачен»: =ФИЛЬТР(A2:D100;(B2:B100="Анна")*(C2:C100="Оплачен");"Нет строк").

Скобки вокруг каждой проверки делают формулу читаемой и предотвращают ошибки приоритетов. Можно добавить дату и минимальную сумму: каждый новый критерий умножается на предыдущие. Не расширяйте один диапазон до строки 1000, оставляя другой до 100: размеры логического массива должны соответствовать возвращаемому массиву.

Условия ИЛИ и смешанная логика

Для ИЛИ логические массивы складывают. Выборка Москвы или Казани: =ФИЛЬТР(A2:D100;(B2:B100="Москва")+(B2:B100="Казань");"Нет строк"). Положительное значение означает, что выполнено хотя бы одно условие.

Смешанную логику группируйте скобками. Например, «(Москва ИЛИ Казань) И Оплачен» записывается как сумма двух городов, умноженная на проверку статуса. Без скобок формула может вернуть иной набор из-за порядка операций.

Даты, числа и поиск текста

Период задают двумя проверками: дата не раньше начала и меньше начала следующего периода. Если F1 — 1 марта, G1 — 1 апреля, условие равно (A2:A100>=F1)*(A2:A100<G1). Такая верхняя граница корректно включает строки 31 марта со временем.

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

Числа и даты должны храниться как числа. Если сравнение «больше 1000» возвращает странный результат, проверьте импортированные пробелы, апострофы и текстовый тип. Формула не заменяет очистку данных.

Почему возникает ошибка #ПЕРЕНОС!

Динамический массив разливается из одной ячейки на прямоугольную область. Если там есть значение, объединённая ячейка или другой массив, Excel возвращает #ПЕРЕНОС!. Выберите ячейку с ошибкой: программа покажет предполагаемую границу, которую нужно освободить.

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

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

Пустой результат и ошибки источника

Третий аргумент позволяет вернуть текст «Нет строк» вместо #ВЫЧИСЛ!. Для дальнейших расчётов иногда лучше вернуть пустую строку, но она тоже является значением и может влиять на диаграммы. Выбирайте результат в зависимости от следующего шага.

ФИЛЬТР не исправляет ошибки внутри исходных данных. Если условие содержит #Н/Д или #ЗНАЧ!, ошибка может перейти в итог. Сначала обработайте источник или логическую проверку через ЕСЛИОШИБКА на осознанной границе. Не оборачивайте всю формулу без анализа: так можно скрыть настоящую проблему данных.

Сочетание с СОРТ и УНИК

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

Длинную формулу разбивайте через именованные диапазоны или функцию LET в поддерживаемых версиях. Имя исходного массива и критериев делает вычисление проверяемым. Но не усложняйте формулу ради демонстрации возможностей: если требуется соединять файлы и выполнять десятки преобразований, лучше Power Query.

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

Пример динамического отчёта

В таблице Заказы есть столбцы Дата, Регион, Статус и Сумма. Пользователь выбирает регион в H1, начало периода в H2 и конец периода в H3. Нужно вывести оплаченные заказы выбранного региона.

Если H3 содержит дату без времени, условие меньше H3+1 включает весь последний день. Проверьте четыре случая: одна подходящая строка, несколько строк, отсутствие результата и строка ровно на границе. Добавьте новый заказ в исходную таблицу — динамический результат должен обновиться без изменения ссылок.

Ошибки и контрольный список

• Путать функцию ФИЛЬТР с кнопкой автофильтра.
• Использовать логические диапазоны другой высоты.
• Забывать скобки в сочетании И и ИЛИ.
• Размещать формулу там, где массиву некуда разлиться.
• Сравнивать текстовые даты и числа.
• Скрывать все ошибки общим ЕСЛИОШИБКА.
• Использовать функцию в файле для неподдерживаемой версии Excel.

Синтаксис и поведение динамического массива сверены с официальной документацией Microsoft по функции ФИЛЬТР и справкой о динамических массивах. Страницы проверены 27 июля 2026 года.

ФИЛЬТР превращает условие в обновляемое представление данных, если типы, размеры диапазонов и свободная область разлива подготовлены заранее.

Вопросы и ответы

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

  Автофильтр скрывает строки в исходной таблице. Функция создаёт отдельный динамический массив и не меняет отображение источника.

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

  Заключите каждую логическую проверку в скобки и перемножьте их. В результате остаются строки, где обе проверки дают ИСТИНА.

• Как задать условие ИЛИ?

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

• Что означает ошибка #ПЕРЕНОС!?

  В предполагаемой области динамического массива есть данные, объединённые ячейки или другое препятствие. Освободите показанную Excel границу разлива.

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

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

• Работает ли ФИЛЬТР в старых версиях Excel?

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

По теме