Как пользоваться функцией ФИЛЬТР в Excel: простые примеры
Обычный фильтр в Excel прячет лишние строки, но стоит данным измениться — и его нужно применять заново. А если нужен отдельный список, например «все неоплаченные заказы» на соседнем листе, приходится копировать строки руками.
Функция ФИЛЬТР (FILTER) делает это формулой: вы один раз задаёте условие, а Excel сам выводит подходящие строки и обновляет их при любом изменении исходной таблицы. Разберём синтаксис и несколько примеров — от одного условия до сочетаний «и» и «или».
- Синтаксис:
=ФИЛЬТР(массив; включить; [если_пусто])— что выводить, по какому условию и что показать, если ничего не найдено. - Функция работает в Excel 2021, 2024, Microsoft 365 и в веб-версии; в Excel 2019 и старше её нет.
- Условия «и» перемножаются звёздочкой
*, условия «или» складываются плюсом+; каждое берётся в скобки. - Результат сам занимает нужное число ячеек. Если им что-то мешает, появится ошибка
#ПЕРЕНОС!. - Исходные данные не меняются — список живёт отдельно и пересчитывается автоматически.
Как устроена функция
=ФИЛЬТР(массив; включить; [если_пусто])
- массив — диапазон, строки которого нужно вернуть: например,
A2:D100. Заголовки в него не включают. - включить — условие отбора: столбец той же высоты, сравнённый с чем-либо, например
C2:C100="Казань". - если_пусто — необязательный аргумент: текст, который появится, когда не подошла ни одна строка. Если его не указать, функция вернёт ошибку
#ВЫЧИСЛ!.
Формулу вводят в одну ячейку и нажимают Enter. Результат «разливается» вниз и вправо на столько ячеек, сколько нужно, — это называется динамическим массивом. Вокруг него при выделении видна тонкая синяя рамка, а редактировать можно только левую верхнюю ячейку.
Пример 1. Одно условие
Пусть на листе есть список заказов: в столбце A — номер, в B — клиент, в C — город, в D — сумма. Данные занимают строки 2–100. Нужно вывести все заказы из Казани.
- Скопируйте заголовки в свободное место, например в
F1:I1. - В ячейку
F2введите формулу:
=ФИЛЬТР(A2:D100;C2:C100="Казань";"Нет заказов")
Под заголовками появятся все строки, где в столбце C стоит «Казань». Добавьте в исходную таблицу новый казанский заказ — он сразу окажется в списке.

Пример 2. Условие берётся из ячейки
Чтобы не править формулу каждый раз, вынесите условие в отдельную ячейку. Напишите название города в K1 и сошлитесь на неё:
=ФИЛЬТР(A2:D100;C2:C100=K1;"Нет заказов")
Теперь достаточно поменять текст в K1 — и список перестроится. Ещё удобнее сделать в K1 выпадающий список с городами: получится мини-отчёт, которым можно пользоваться без клавиатуры.
С числами и датами работают знаки сравнения. Заказы дороже 50 000 рублей:
=ФИЛЬТР(A2:D100;D2:D100>50000;"Нет заказов")
Пример 3. Несколько условий сразу («и»)
Чтобы строка проходила отбор только при выполнении всех условий, каждое берут в скобки и перемножают:
=ФИЛЬТР(A2:D100;(C2:C100="Казань")*(D2:D100>50000);"Нет заказов")
Логика простая: каждое условие даёт для строки ИСТИНА или ЛОЖЬ, то есть 1 или 0. Произведение равно 1, только когда оба множителя — единицы. Третье условие добавляется так же — ещё одна пара скобок через звёздочку.
Пример 4. Любое из условий («или»)
Если достаточно, чтобы выполнялось хотя бы одно условие, вместо звёздочки ставят плюс:
=ФИЛЬТР(A2:D100;(C2:C100="Казань")+(C2:C100="Самара");"Нет заказов")
В список попадут заказы и из Казани, и из Самары. Сочетать можно как угодно: «(Казань или Самара) и сумма больше 50 000» записывается так:
=ФИЛЬТР(A2:D100;((C2:C100="Казань")+(C2:C100="Самара"))*(D2:D100>50000);"Нет заказов")
Следите за скобками: условия «или» объединяются в общую пару скобок, и только потом умножаются на остальное.
Пример 5. Поиск по части текста
Знак равенства ищет точное совпадение. Чтобы найти строки, где клиент содержит слово, используют связку ПОИСК (SEARCH) и ЕЧИСЛО (ISNUMBER):
=ФИЛЬТР(A2:D100;ЕЧИСЛО(ПОИСК("строй";B2:B100));"Не найдено")
Формула вернёт и «СтройМаркет», и «ООО Стройка» — ПОИСК не различает регистр букв.
Как отсортировать результат и вывести не все столбцы
- Сортировка. Оберните формулу в СОРТ (SORT):
=СОРТ(ФИЛЬТР(A2:D100;C2:C100=K1);4;-1)— отсортирует по четвёртому столбцу по убыванию. - Только нужные столбцы. В Microsoft 365 и Excel 2024 поможет ВЫБОРСТОЛБЦ (CHOOSECOLS):
=ВЫБОРСТОЛБЦ(ФИЛЬТР(A2:D100;C2:C100=K1);2;4)— оставит клиента и сумму. В Excel 2021 проще указать в первом аргументе сам нужный диапазон, напримерB2:B100. - Без повторов. УНИК (UNIQUE) вокруг ФИЛЬТР уберёт одинаковые строки.
Типичные ошибки
#ПЕРЕНОС!— результату некуда разместиться: ниже или правее стоят данные. Очистите ячейки. Эта же ошибка возникает, если ввести ФИЛЬТР внутри умной таблицы.#ВЫЧИСЛ!— ничего не найдено, а третий аргумент не задан. Добавьте"Нет данных".#ЗНАЧ!— диапазон условия не совпадает по высоте с массивом: например, массивA2:D100, а условие поC2:C90.#ИМЯ?— ваша версия Excel не знает такой функции. В Excel 2019 и старше придётся обходиться обычным фильтром или расширенным фильтром на вкладке «Данные».- Даты и суммы выглядят как числа. ФИЛЬТР возвращает значения без оформления. Задайте ячейкам результата нужный формат вручную.
Частые вопросы
Чем ФИЛЬТР лучше обычного фильтра?
Обычный фильтр скрывает строки в самой таблице и не обновляется сам. Функция выводит отдельный список, не трогает исходник и пересчитывается при каждом изменении. Зато обычный фильтр быстрее для разового просмотра.
А чем отличается от ВПР?
ВПР находит одно — первое — совпадение и возвращает одно значение. ФИЛЬТР возвращает все подходящие строки целиком.
Работает ли функция в Google Таблицах?
Да, там есть FILTER с похожим синтаксисом: условия перечисляются через точку с запятой как отдельные аргументы, а «или» тоже записывается через плюс.
© kak2.ru



Оставить комментарий