Описать задачу
Функции Excel › Поиск и ссылки › ФИЛЬТР

Функция ФИЛЬТР в Excel (FILTER): как работает, пример и формула под вашу таблицу

пример проверен пересчётомПоиск и ссылки Excel 2021 и новее

Редакция Формулника · обновлено · примеры проверены пересчётом LibreOffice 26.8 на сервере Формулника · как мы проверяем

Коротко. ФИЛЬТР возвращает строки, подходящие под условие, и обновляется сам при изменении данных. Есть в Excel 2021/365.

Синтаксис в русском Excel

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

В английском Excel, Google Таблицах и внутри файла .xlsx

=FILTER(массив, включить, [если_пусто])
Сделать по моим данным

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

Аргументы

массивчто возвращаем
включитьусловие: B2:B5>90000
[если_пусто]что показать, если ничего

Пример

ABCD
1МенеджерСумма
2Петрова184500Петрова
3Сидоров72300
4Петрова96200
5Ким15000
=ФИЛЬТР(A2:B5;B2:B5>90000;"нет")

Результат в ячейке D2 посчитан LibreOffice на сервере Формулника.

Частые ошибки

#ВЫЧИСЛ!

ничего не нашлось — третий аргумент «если пусто»

Где работает

ПрограммаФИЛЬТР
Excel 2010нетпоявилась в Excel 2021
Excel 2013нетпоявилась в Excel 2021
Excel 2016нетпоявилась в Excel 2021
Excel 2019нетпоявилась в Excel 2021
Excel 2021есть
Excel 2024есть
Excel 365есть
LibreOffice Calcестьпроверено
Google Таблицыпроверьтеновая функция Excel 2021 — в Google Таблицы не проверена
Яндекс Таблицыпроверьтеновая функция Excel 2021 — в Яндекс Таблицы не проверена
Р7-Офиспроверьтеновая функция Excel 2021 — в Р7-Офис не проверена
МойОфиспроверьтеновая функция Excel 2021 — в МойОфис не проверена
MOffice Sheetsестьпроверено

Похожие функции

УНИКСУММЕСЛИ

ФИЛЬТР: несколько условий, поиск по части текста, сортировка, нужные столбцы и совместимость

ФИЛЬТР возвращает все строки, подходящие под условие, и сама «разливается» на нужное число ячеек. Несколько условий объединяют умножением (все сразу) или сложением (любое): (C2:C500="Москва")*(D2:D500>100000). Поскольку результат — массив, его можно сразу передать в СОРТ, УНИК, СУММ или ВЫБОРСТОЛБЦ. Работает в Excel 2021, Microsoft 365 и LibreOffice 24.8 и новее; в Excel 2019 и раньше — #ИМЯ?.

По шагам

  1. Освободите место под и справа от ячейки с формулой — результат займёт столько строк, сколько найдётся.
  2. Первым аргументом выделите всю таблицу без заголовков или только нужные столбцы, вторым — условие той же высоты.
  3. Третьим аргументом задайте текст на случай пустого результата, иначе получите #ВЫЧИСЛ!.
  4. Заголовки над результатом напишите вручную или ссылкой на шапку исходной таблицы — ФИЛЬТР их не возвращает.
  5. Чтобы сослаться на весь результат в другой формуле, используйте адрес первой ячейки со знаком #: F2#.

Отчёт по менеджеру из выпадающего списка

В H1 — выпадающий список менеджеров (Данные → Проверка данных → Список). Под ним ФИЛЬТР выводит все сделки выбранного менеджера, СОРТ упорядочивает их по сумме от большей к меньшей (четвёртый столбец таблицы, порядок −1). Получается мини-отчёт, который меняется при выборе другого имени, без макросов и сводных таблиц.

=СОРТ(ФИЛЬТР(A2:D500;B2:B500=H1;"нет сделок");4;-1)

Поиск по части названия

Сравнение = в ФИЛЬТР не понимает звёздочек. Чтобы найти все позиции, где в названии есть «шуруп», используйте ПОИСК: он возвращает число, если текст найден, и ошибку, если нет; ЕЧИСЛО превращает это в условие. Регистр ПОИСК не различает.

=ФИЛЬТР(A2:C500;ЕЧИСЛО(ПОИСК(H1;B2:B500));"ничего не найдено")

Только нужные столбцы и в нужном порядке

Из реестра в 12 столбцов для письма нужны только номер счёта, контрагент и сумма по строкам, где заполнен столбец L «Дата оплаты». ВЫБОРСТОЛБЦ (Microsoft 365) выбирает столбцы по номерам в любом порядке. В Excel 2021 без неё можно отфильтровать каждый столбец отдельной формулой с тем же условием.

=ВЫБОРСТОЛБЦ(ФИЛЬТР(A2:L900;L2:L900<>"";"нет");1;3;9)

Сделки за период и из двух регионов

Условие «и» — умножение, «или» — сложение. Внешние скобки вокруг суммы обязательны, иначе порядок действий смешает условия. Границы периода берите из ячеек H1 и H2, чтобы менять их без правки формулы.

=ФИЛЬТР(A2:D500;(A2:A500>=H1)*(A2:A500<=H2)*((C2:C500="Москва")+(C2:C500="Тверь"));"нет")

ФИЛЬТР в связке

ЗадачаФормула
Сумма отфильтрованного=СУММ(ФИЛЬТР(D2:D500;B2:B500=H1;0))
Сколько строк нашлось=ЧСТРОК(ФИЛЬТР(A2:A500;B2:B500=H1))
Уникальные клиенты региона=УНИК(ФИЛЬТР(B2:B500;C2:C500=H1))
Первые 5 крупных сделок=ВЗЯТЬ(СОРТ(ФИЛЬТР(A2:D500;D2:D500>100000);4;-1);5)
Ссылка на весь результат=СЧЁТЗ(F2#)

Частые ошибки

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

Что увидит коллега с Excel 2019, если отправить ему файл с ФИЛЬТР?

Сохранённые значения он увидит, но при пересчёте появится #ИМЯ?, а в строке формул — префикс _xlfn. Если получатель на старой версии, отправьте результат значениями или замените ФИЛЬТР автофильтром.

Можно ли отфильтровать данные с другого листа?

Да, достаточно сослаться на диапазоны другого листа: =ФИЛЬТР(Продажи!A2:D500;Продажи!B2:B500=H1). Результат обновится сам при изменении исходных данных.

Как сделать ФИЛЬТР по нескольким значениям из списка?

Используйте СЧЁТЕСЛИ как условие: =ФИЛЬТР(A2:D500;СЧЁТЕСЛИ(H2:H6;C2:C500)) вернёт строки, у которых регион есть в списке H2:H6.

Почему результат ФИЛЬТР не сохраняет форматы исходной таблицы?

Функция возвращает только значения. Даты придут числами, если ячейкам под формулой не задан формат «Дата», — задайте форматы для всей области разлива заранее, с запасом строк.

Есть ли ФИЛЬТР в Р7-Офис?

В новых версиях Р7-Офис динамические массивы и ФИЛЬТР поддерживаются, в старых — нет. Проверьте на своей версии одну формулу, прежде чем строить на ней отчёты.