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

Ручная выборка устаревает с первой правкой данных: строки скрыли, отчёт отправили, а в журнале появились новые продажи. Справка Microsoft описывает ФИЛЬТР как функцию, которая отбирает данные по логическому массиву той же высоты, что и диапазон^[1], — выборка пересобирается сама.

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

Ключевые факты: функция ФИЛЬТР

• ФИЛЬТР возвращает строки массива, для которых аргумент «включить» равен ИСТИНА, и выводит их в соседние ячейки^[1].
• У функции три аргумента, обязательны два — «массив» и «включить», а «если_пусто» можно опустить^[1].
• Пустой результат без третьего аргумента даёт #CALC!, потому что Excel пока не поддерживает пустые массивы^[1].
• В списке «Применяется к» для ФИЛЬТР стоят Microsoft 365, Excel 2024 и Excel 2021 с версиями для Mac, а Excel 2019 там нет^[1].
• В Google Таблицах FILTER принимает каждое условие отдельным аргументом, тогда как в Excel их объединяют умножением^[2].

Функция ФИЛЬТР в Excel: синтаксис и аргументы

Функция ФИЛЬТР в Excel возвращает строки массива, для которых аргумент «включить» равен ИСТИНА. Синтаксис по справке Microsoft: =ФИЛЬТР(массив;включить;[если_пусто])^[1]; в английском интерфейсе функция называется FILTER.

Аргумент «включить» — это массив логических значений, который по высоте или ширине совпадает с массивом^[1]. Условие не обязано быть сравнением с текстом. Подойдёт любое выражение, дающее ИСТИНА или ЛОЖЬ для каждой строки: D2:D9>50000, A2:A9>=G1 или ЕЧИСЛО(ПОИСК("ова";B2:B9)). ФИЛЬТР отбирает строки, если «включить» — столбец такой же высоты, и столбцы, если «включить» — строка такой же ширины^[1].

ФИЛЬТР выводит ответ в соседние ячейки, и редактировать можно только первую из них^[3]. Если источник оформлен таблицей Excel и в формуле стоят структурированные ссылки, результат сам меняет размер при добавлении строк^[1]. Абсолютные ссылки при этом не нужны: формула стоит в одной ячейке и протягивать её некуда.

В Google Таблицах FILTER работает по другой схеме: каждое условие передаётся отдельным аргументом, и оно должно совпадать по длине с диапазоном^[2]. От синтаксиса переходим к журналу продаж, на котором видна вся механика.

Пример по шагам: продажи одного региона

ФИЛЬТР отбирает продажи одного региона одной формулой и обновляет список, когда меняется журнал. Журнал занимает A1:D9.

1. Найдите свободное место справа от журнала, например F2, — под результат должно быть пусто.
2. Введите =ФИЛЬТР(A2:D9;C2:C9="Север") и нажмите Enter.
3. Проверьте ответ: формула выведет четыре строки по четыре столбца — продажи Иванова, Сидорова, Петровой и снова Иванова.
4. Замените «Север» на «Запад» и убедитесь, что без третьего аргумента появилась ошибка #CALC!.
5. Допишите третий аргумент: =ФИЛЬТР(A2:D9;C2:C9="Запад";"нет продаж") вернёт «нет продаж».

Если журнал вырастет за пределы A2:D9, расширьте диапазоны или оформите журнал таблицей Excel со структурированными ссылками: тогда ФИЛЬТР сам подхватит добавленные строки^[1].

Условие удобно вынести в ячейку: регион в H1, порог суммы в H2, и формула =ФИЛЬТР(A2:D9;(C2:C9=H1)*(D2:D9>H2)) превращается в маленький отчёт, который перестраивается при смене значения. Если сделать в H1 выпадающий список регионов, выборка будет меняться одним щелчком — как его создать, описано на странице о выпадающем списке через проверку данных.

Соберите такую выборку в тренажёре: урок подменяет журнал и проверяет, что формула отбирает нужные строки.

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

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

Несколько условий функция ФИЛЬТР принимает через арифметику: условия «и» перемножают, условия «или» складывают^[1]. Каждое сравнение берут в скобки, иначе порядок действий собьёт формулу.

Механика простая. Сравнение C2:C9="Север" даёт массив ИСТИНА и ЛОЖЬ, при умножении ИСТИНА превращается в единицу, а ЛОЖЬ — в ноль. Строка попадёт в результат, только если произведение не равно нулю, то есть выполнены все условия. При сложении достаточно, чтобы хотя бы одно слагаемое оказалось единицей.

Внутри ФИЛЬТР используют только * и +. Запись с функцией И, например =ФИЛЬТР(A2:D9;И(C2:C9="Север";D2:D9>50000)), получит одно значение вместо массива нужной высоты и вернёт ошибку. Смешанную логику записывают со скобками: ((усл1)+(усл2))*(усл3).

Даты сравниваются как числа, поэтому границы периода удобно держать в ячейках: в G1 — первое число месяца, в G2 — последнее. Именно поэтому формулу легко превратить в отчёт: стоит передать её результат в другую функцию.

ФИЛЬТР вместе с СОРТ, УНИК и СУММ: примеры отчётов

ФИЛЬТР возвращает массив, поэтому его результат сразу принимают СОРТ, УНИК, СУММ и другие функции. Справка Microsoft сама приводит связку СОРТ(ФИЛЬТР(…)) с сортировкой по четвёртому столбцу по убыванию^[1].

Три условия задают тремя перемноженными сравнениями той же высоты, что и массив: например, регион, порог суммы и начало периода. Сумма за октябрь через СУММ с выборкой по двум датам на этом журнале равна 307 000, а число крупных сделок — 5.

Связка с УНИК выдаёт список без повторов, который удобно подставлять в выпадающий список отчёта. Чтобы отобрать строки, где регион входит в короткий перечень в ячейках H1:H2, подойдёт =ФИЛЬТР(A2:D9;СЧЁТЕСЛИ(H1:H2;C2:C9)>0): на перечне «Юг» и «Центр» формула выведет четыре строки. Связка с СУММ заменяет СУММЕСЛИМН там, где условие сложнее простого сравнения, — скажем, поиск части текста.

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

Остаётся понять, какие ошибки ломают такие отчёты и когда от функции лучше отказаться.

Ошибки #CALC!, #ПЕРЕНОС! и когда ФИЛЬТР не подойдёт

ФИЛЬТР не подойдёт, если книгу откроют в Excel 2019 или Excel 2016: в списке «Применяется к» этих версий нет^[1]. Остальные отказы видны по коду ошибки в ячейке.

Пустой результат — частая причина вопросов. Excel пока не поддерживает пустые массивы, поэтому без третьего аргумента формула выдаёт #CALC!^[1], а не пустую ячейку.

• Если формулу нужно поставить внутри самой таблицы Excel, результат не разольётся: справка о переносе запрещает такие формулы внутри таблиц^[3].
• Если в аргументе «включить» встретится #Н/Д или значение, которое не превращается в ИСТИНА или ЛОЖЬ, функция вернёт ошибку^[1].
• При ссылке на закрытую книгу формула при обновлении вернёт #REF!^[1]

Для старых версий выборку собирают иначе, приёмы описаны на странице о поиске по составному ключу. Остальные формулы для отчётов — в сборнике формул Excel для отбора данных.