Функция ФИЛЬТР в Excel (FILTER): как работает, пример и формула под вашу таблицу
Синтаксис в русском Excel
В английском Excel, Google Таблицах и внутри файла .xlsx
Аргументы
массив | что возвращаем |
включить | условие: B2:B5>90000 |
[если_пусто] | что показать, если ничего |
Пример
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Менеджер | Сумма | ||
| 2 | Петрова | 184500 | Петрова | |
| 3 | Сидоров | 72300 | ||
| 4 | Петрова | 96200 | ||
| 5 | Ким | 15000 |
Результат в ячейке 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 и раньше — #ИМЯ?.
По шагам
- Освободите место под и справа от ячейки с формулой — результат займёт столько строк, сколько найдётся.
- Первым аргументом выделите всю таблицу без заголовков или только нужные столбцы, вторым — условие той же высоты.
- Третьим аргументом задайте текст на случай пустого результата, иначе получите #ВЫЧИСЛ!.
- Заголовки над результатом напишите вручную или ссылкой на шапку исходной таблицы — ФИЛЬТР их не возвращает.
- Чтобы сослаться на весь результат в другой формуле, используйте адрес первой ячейки со знаком #:
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 пунктиром показывает, куда хочет разлиться результат.
- #ЗНАЧ! сразу после ввода — массив и условие разной высоты, например
A2:D500иB2:B499. - ФИЛЬТР внутри «умной» таблицы не работает — формулы с разливом нельзя ставить внутрь таблицы Ctrl+T. Поставьте ФИЛЬТР на свободное место листа, а таблицу используйте как источник.
Вопросы и ответы
Что увидит коллега с Excel 2019, если отправить ему файл с ФИЛЬТР?
Сохранённые значения он увидит, но при пересчёте появится #ИМЯ?, а в строке формул — префикс _xlfn. Если получатель на старой версии, отправьте результат значениями или замените ФИЛЬТР автофильтром.
Можно ли отфильтровать данные с другого листа?
Да, достаточно сослаться на диапазоны другого листа: =ФИЛЬТР(Продажи!A2:D500;Продажи!B2:B500=H1). Результат обновится сам при изменении исходных данных.
Как сделать ФИЛЬТР по нескольким значениям из списка?
Используйте СЧЁТЕСЛИ как условие: =ФИЛЬТР(A2:D500;СЧЁТЕСЛИ(H2:H6;C2:C500)) вернёт строки, у которых регион есть в списке H2:H6.
Почему результат ФИЛЬТР не сохраняет форматы исходной таблицы?
Функция возвращает только значения. Даты придут числами, если ячейкам под формулой не задан формат «Дата», — задайте форматы для всей области разлива заранее, с запасом строк.
Есть ли ФИЛЬТР в Р7-Офис?
В новых версиях Р7-Офис динамические массивы и ФИЛЬТР поддерживаются, в старых — нет. Проверьте на своей версии одну формулу, прежде чем строить на ней отчёты.