Функция ФИЛЬТР (на английском FILTER) возвращает все строки таблицы, которые подходят под условие, и сама раскладывает их на листе: =ФИЛЬТР(A2:C6;B2:B6="Москва") выведет все сделки из Москвы целиком, со всеми столбцами. В отличие от ВПР и ПРОСМОТРX, которые находят одно значение, ФИЛЬТР отдаёт сразу все совпадения и пересчитывается при изменении данных. Функция есть в Excel 2021, 2024 и Microsoft 365.
| Раздел функций | Ссылки и массивы |
| Название на английском | FILTER |
| Версии Excel | Excel 2021, Excel 2024, Excel для Microsoft 365 (Windows и Mac), Excel в интернете |
| Волатильность | Не волатильная |
| Похожие функции | СОРТ, УНИК, ПРОСМОТРX, ВПР |
Синтаксис функции ФИЛЬТР
=ФИЛЬТР(массив;включить;[если_пусто])
- массив — что фильтруем: диапазон или таблица целиком;
- включить — условие той же высоты, что и массив: B2:B6="Москва", C2:C6>30000;
- если_пусто — что показать, если ничего не нашлось; без него будет ошибка #ВЫЧИСЛ!.
Формула пишется в одну ячейку, а результат «разливается» вниз и вправо на столько строк и столбцов, сколько нужно. Важный момент: ячейки под формулой должны быть пустыми, иначе вместо результата появится #ПЕРЕНОС!.
Отфильтровать строки по одному условию
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Менеджер | Город | Сумма | Город | Менеджер | Город | Сумма | ||
| 2 | Иванов | Москва | 45000 | Москва | Иванов=ФИЛЬТР(A2:C6;B2:B6=E2) | Москва | 45000 | ||
| 3 | Петрова | Казань | 38000 | Сидоров | Москва | 27000 | |||
| 4 | Иванов | Казань | 12500 | Петрова | Москва | 51000 | |||
| 5 | Сидоров | Москва | 27000 | ||||||
| 6 | Петрова | Москва | 51000 |
Формула стоит только в G2, остальные ячейки результата заполнились сами. Впишите в E2 «Казань», и вместо трёх строк останутся две: Петрова и Иванов.
В английском Excel: =FILTER(A2:C6,B2:B6=E2)
Условие B2:B6=E2 даёт массив ИСТИНА/ЛОЖЬ, по одному значению на строку. ФИЛЬТР оставляет строки, где стоит ИСТИНА. Поэтому высота условия и массива должна совпадать: B2:B6 и A2:C6 — по пять строк.
ФИЛЬТР по нескольким условиям: И и ИЛИ
Функции И и ИЛИ здесь не работают: они сворачивают весь массив в одно значение. Вместо них условия перемножают (И) или складывают (ИЛИ), каждое в своих скобках.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Менеджер | Город | Сумма | Менеджер | Город | Сумма | ||
| 2 | Иванов | Москва | 45000 | Иванов=ФИЛЬТР(A2:C6;(B2:B6="Москва")*(C2:C6>30000)) | Москва | 45000 | ||
| 3 | Петрова | Казань | 38000 | Петрова | Москва | 51000 | ||
| 4 | Иванов | Казань | 12500 | |||||
| 5 | Сидоров | Москва | 27000 | |||||
| 6 | Петрова | Москва | 51000 |
Умножение работает как И: 1×1 = 1, а если хоть одно условие ложно, получится 0. Сидоров из Москвы не попал — у него 27 000.
В английском Excel: =FILTER(A2:C6,(B2:B6="Москва")*(C2:C6>30000))
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Менеджер | Город | Сумма | Менеджер | Город | Сумма | ||
| 2 | Иванов | Москва | 45000 | Петрова=ФИЛЬТР(A2:C6;(B2:B6="Казань")+(C2:C6>50000)) | Казань | 38000 | ||
| 3 | Петрова | Казань | 38000 | Иванов | Казань | 12500 | ||
| 4 | Иванов | Казань | 12500 | Петрова | Москва | 51000 | ||
| 5 | Сидоров | Москва | 27000 | |||||
| 6 | Петрова | Москва | 51000 |
Сложение работает как ИЛИ: строка проходит, если сумма условий больше нуля. Последняя строка попала по второму условию, хотя город у неё не Казань.
В английском Excel: =FILTER(A2:C6,(B2:B6="Казань")+(C2:C6>50000))
Фильтр по части текста
Звёздочки в условии ФИЛЬТР не понимает: B2:B6="*чайник*" будет искать текст со звёздочками буквально. Для поиска подстроки условие строят из ПОИСК и ЕЧИСЛО:
| A | B | C | |
|---|---|---|---|
| 1 | Товар | Найдено | |
| 2 | Чайник Bosch 1,7 л | Чайник Bosch 1,7 л=ФИЛЬТР(A2:A6;ЕЧИСЛО(ПОИСК("чайник";A2:A6));"Нет") | |
| 3 | Тостер Philips | Чайник Tefal стекло | |
| 4 | Чайник Tefal стекло | Электрочайник Xiaomi | |
| 5 | Блендер Bosch | ||
| 6 | Электрочайник Xiaomi |
ПОИСК возвращает позицию слова или ошибку, ЕЧИСЛО превращает это в ИСТИНА/ЛОЖЬ. Регистр не важен, поэтому нашлись и «Чайник», и «Электрочайник». Если нужен учёт регистра, замените ПОИСК на НАЙТИ.
В английском Excel: =FILTER(A2:A6,ISNUMBER(SEARCH("чайник",A2:A6)),"None")
В Microsoft 365 условие можно задать и регулярным выражением через РЕГТЕСТ: =ФИЛЬТР(A2:A6;РЕГТЕСТ(A2:A6;"\d")) оставит строки, где есть хотя бы одна цифра.
Если ничего не найдено: третий аргумент
Когда под условие не подходит ни одна строка, ФИЛЬТР возвращает #ВЫЧИСЛ!. Третий аргумент заменяет ошибку понятным текстом:
=ФИЛЬТР(A2:C6;B2:B6="Самара";"Нет данных")
Самары в таблице нет, поэтому формула вернёт «Нет данных». Если результат дальше идёт в другие формулы, удобнее пустая строка "" — тогда СЧЁТЗ или СУММ не споткнутся о текст.
ФИЛЬТР вместе с СОРТ и УНИК
Динамические функции вкладываются друг в друга. Самые частые связки:
| Задача | Формула |
|---|---|
| Отфильтровать и отсортировать по сумме, от большей | =СОРТ(ФИЛЬТР(A2:C6;B2:B6="Москва");3;-1) |
| Уникальные менеджеры из Москвы | =УНИК(ФИЛЬТР(A2:A6;B2:B6="Москва")) |
| Сколько строк прошло фильтр | =ЧСТРОК(ФИЛЬТР(A2:C6;B2:B6="Москва")) |
| Сумма по отфильтрованным | =СУММ(ФИЛЬТР(C2:C6;B2:B6="Москва")) |
| Только нужные столбцы (Менеджер и Сумма) | =ФИЛЬТР(ВЫБОРСТОЛБЦ(A2:C6;1;3);B2:B6="Москва") |
Первая формула по нашей таблице вернёт Петрову (51 000), Иванова (45 000) и Сидорова (27 000). Последняя использует ВЫБОРСТОЛБЦ, которая есть только в Microsoft 365 и Excel 2024. Подробнее о сортировке формулой — на странице функции СОРТ, об уникальных значениях — у УНИК.
Почему ФИЛЬТР не работает
- #ВЫЧИСЛ! — ни одна строка не подошла. Добавьте третий аргумент или проверьте условие.
- #ПЕРЕНОС! — результату некуда разлиться: под формулой или справа от неё есть данные. Очистите область или перенесите формулу.
- #ЗНАЧ! — условие другой высоты, чем массив, например A2:C100 и B2:B99.
- #ИМЯ? — функции нет в вашей версии Excel (2019 и раньше). Там помогает обычный автофильтр или расширенный фильтр на вкладке «Данные».
- Строки «не находятся» — в данных лишние пробелы, числа, сохранённые как текст, или в условии использованы И и ИЛИ вместо умножения и сложения.
- ФИЛЬТР внутри «умной» таблицы — динамические массивы в ней не разливаются, формула вернёт #ПЕРЕНОС!. Ставьте формулу рядом с таблицей.
Функция ФИЛЬТР — частые вопросы
Что делает функция ФИЛЬТР в Excel?
Возвращает все строки диапазона, подходящие под условие: =ФИЛЬТР(A2:C6;B2:B6="Москва").
Как ФИЛЬТР называется на английском?
FILTER. Формула =ФИЛЬТР(A2:C6;B2:B6=E2) в английском Excel — =FILTER(A2:C6,B2:B6=E2).
Как задать в ФИЛЬТР несколько условий?
Перемножить условия для И: =ФИЛЬТР(A2:C6;(B2:B6="Москва")*(C2:C6>30000)), сложить для ИЛИ.
Почему ФИЛЬТР выдаёт #ВЫЧИСЛ!?
Ни одна строка не подошла под условие. Укажите третий аргумент: =ФИЛЬТР(A2:C6;B2:B6="Самара";"Нет данных").
Есть ли ФИЛЬТР в Excel 2019?
Нет, функция появилась в Excel 2021 и Microsoft 365. В старых версиях используйте автофильтр или расширенный фильтр.
Если отобранные строки нужно не показать рядом, а оставить или удалить в самой таблице, формула не поможет. !SEMTools удаляет строки по условию, по списку слов или по содержимому ячеек прямо на листе, без вспомогательных столбцов.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: функция ФИЛЬТР в Excel
| Задача | Формула |
|---|---|
| Строки по условию | =ФИЛЬТР(A2:C100;B2:B100="Москва") |
| Два условия (И) | =ФИЛЬТР(A2:C100;(B2:B100="Москва")*(C2:C100>30000)) |
| Два условия (ИЛИ) | =ФИЛЬТР(A2:C100;(B2:B100="Казань")+(C2:C100>50000)) |
| По части текста | =ФИЛЬТР(A2:A100;ЕЧИСЛО(ПОИСК("чайник";A2:A100))) |
| Текст, если пусто | =ФИЛЬТР(A2:C100;B2:B100="Самара";"Нет данных") |
| С сортировкой | =СОРТ(ФИЛЬТР(A2:C100;B2:B100="Москва");3;-1) |
Надеюсь, на этой странице вы нашли то, что искали, и собрали нужные строки одной формулой. ФИЛЬТР хорош для живых отчётов, которые должны пересчитываться сами. Если же задача разовая — убрать лишние строки из таблицы, — быстрее удалить строки по условию прямо на листе.