Функция ФИЛЬТР в Excel (эксель): несколько условий, И и ИЛИ

=ФИЛЬТР(A2:C7;B2:B7="North") возвращает все строки A2:C7, в которых регион в столбце B равен North. Это функция ФИЛЬТР (по-английски FILTER). Формула вводится в одну ячейку, а подходящие строки разливаются в ячейки ниже и правее. Замените регион в столбце B на North или North на South, и список обновится. В таблицах формулы записаны по-английски, но их можно вводить и по-русски, с точкой с запятой, как здесь.

ФормулыСбросить

E2fx

=FILTER(A2:C7,B2:B7="North")

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200		Finn	North	60
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ФИЛЬТР(A2:C7;B2:B7="North")

Формула есть только в E2. Остальные заполненные ячейки в E:G это её разлитый результат: щёлкните F3, и вы увидите, что ячейка относится к формуле в E2. Если в эту область что-то введено, ФИЛЬТР вместо строк показывает #ПЕРЕНОС! (по-английски #SPILL!; см. ошибки #ПЕРЕНОС!). Таблицы на этой странице показывают ошибки под английскими именами.

Синтаксис ФИЛЬТР

=FILTER(array, include, [if_empty])

• array (массив) это то, что нужно получить: один столбец, несколько столбцов или вся таблица.
• include (включить) это условие с одним значением ИСТИНА или ЛОЖЬ на каждую строку array, например B2:B7="North". В нём должно быть ровно столько строк, сколько в array. (Чтобы фильтровать столбцы, дайте по одному значению на столбец.)
• if_empty (если_пусто) это то, что показать, когда ни одна строка не подошла. Без него пустой результат даёт ошибку #ВЫЧИСЛ! (по-английски #CALC!).

ФИЛЬТР требует Excel 2021, Excel 2024 или Microsoft 365. В Excel 2019 и старше она показывает #ИМЯ? (по-английски #NAME?), и фильтровать там можно кнопкой «Фильтр» на вкладке «Данные». В Google Таблицах FILTER тоже есть, и там каждое условие можно передать отдельным аргументом.

Сравнение текста не учитывает регистр: B2:B7="north" совпадает с North. ФИЛЬТР сохраняет исходный порядок строк; сортировка результата это отдельный шаг, он показан ниже.

Фильтр по значению ячейки

Если вписать "North" прямо в формулу, её придётся править каждый раз. Поместите значение в ячейку и сравнивайте с ячейкой. Выберите другой регион в F1, и результат изменится:

ФормулыСбросить

E3fx

=FILTER(A2:C7,B2:B7=F1,"No sales")

	A	B	C	D	E	F	G
1	Name	Region	Sales		Region	North▾
2	Ann	North	120
3	Ben	South	80		Ann	North	120
4	Cara	North	200		Cara	North	200
5	Dan	East	150		Finn	North	60
6	Eve	South	95
7	Finn	North	60

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ФИЛЬТР(A2:C7;B2:B7=F1;"No sales")

У West строк нет, поэтому при выборе этого региона показывается текст if_empty, No sales.

С числами всё так же. C2:C7>=F1 при 100 в F1 оставляет все строки с продажами не меньше 100, а C2:C7>F1 делает условие строго «больше».

ФИЛЬТР с несколькими условиями (И)

Чтобы оставить строку, только когда оба условия истинны, перемножьте их. Эта формула возвращает строки North с продажами больше 100:

ФормулыСбросить

E2fx

=FILTER(A2:C7,(B2:B7="North")*(C2:C7>100))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ФИЛЬТР(A2:C7;(B2:B7="North")*(C2:C7>100))

Ann (120) и Cara (200) проходят. Finn из North, но его 60 не больше 100, поэтому он не попадает в результат.

Почему умножение: каждое условие это столбец из ИСТИНА и ЛОЖЬ, а в арифметике ИСТИНА считается как 1, а ЛОЖЬ как 0. Строка получает 1, только когда каждый множитель равен 1, поэтому * работает как И. Каждое условие берётся в свои скобки, и цепочка может быть любой длины: (B2:B7="North")*(C2:C7>100)*(C2:C7<500).

И() здесь не работает. И(B2:B7="North";C2:C7>100) сводит весь диапазон к одному ИСТИНА или ЛОЖЬ вместо значения на каждую строку, и ФИЛЬТР получает данные неправильной формы.

ФИЛЬТР с ИЛИ

Сложите условия, чтобы оставить строку, когда истинно хотя бы одно из них. Эта формула возвращает строки North и East:

ФормулыСбросить

E2fx

=FILTER(A2:C7,(B2:B7="North")+(B2:B7="East"))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200		Dan	East	150
5	Dan	East	150		Finn	North	60
6	Eve	South	95
7	Finn	North	60

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ФИЛЬТР(A2:C7;(B2:B7="North")+(B2:B7="East"))

Строка, которая подходит под оба условия, даёт в сумме 2, а ФИЛЬТР оставляет любую строку с ненулевым результатом, поэтому сумма работает как ИЛИ. Их можно сочетать: ((B2:B7="North")+(B2:B7="East"))*(C2:C7>100) означает (North или East) и больше 100. Здесь это вернёт Ann, Cara и Dan.

ФИЛЬТР возвращает #ВЫЧИСЛ!, когда ничего не подошло

Когда ни одна строка не проходит, ФИЛЬТР нечего вернуть. Без третьего аргумента это ошибка #ВЫЧИСЛ!; с ним вы получите свой текст:

ФормулыСбросить

E2fx

=FILTER(A2:A7,B2:B7="West")

	A	B	C	D	E	F
1	Name	Region	Sales		No if_empty	With if_empty
2	Ann	North	120		#CALC!	No match
3	Ben	South	80
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

#CALC! У вычисления нет результата, например ФИЛЬТР ничего не нашёл.В русском Excel: =ФИЛЬТР(A2:A7;B2:B7="West")

E2 показывает #CALC!, а F2 показывает No match. Замените в B3 South на West, и обе формулы вернут Ben. Чтобы не показывать ничего, используйте пустую строку: =ФИЛЬТР(A2:A7;B2:B7="West";"").

Эта таблица показывает и фильтрацию одного столбца: array равен A2:A7, поэтому возвращаются только имена. Чтобы получить часть столбцов таблицы, оберните результат в ВЫБОРСТОЛБЦ: =ВЫБОРСТОЛБЦ(ФИЛЬТР(A2:C7;B2:B7="North");1;3) возвращает имена и продажи без региона. ВЫБОРСТОЛБЦ требует Microsoft 365 или Excel 2024.

Отсортировать результат ФИЛЬТР

ФИЛЬТР возвращает строки в том порядке, в каком они стоят в таблице. Оберните её в СОРТ, чтобы упорядочить результат: здесь строки North отсортированы по продажам, от больших к меньшим. 3 это столбец результата, по которому идёт сортировка, а -1 означает убывание.

ФормулыСбросить

E2fx

=SORT(FILTER(A2:C7,B2:B7="North"),3,-1)

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Cara	North	200
3	Ben	South	80		Ann	North	120
4	Cara	North	200		Finn	North	60
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СОРТ(ФИЛЬТР(A2:C7;B2:B7="North");3;-1)

Первой идёт Cara (200), затем Ann (120) и Finn (60). Чтобы вернуть только верхние строки, оберните формулу ещё раз в ВЗЯТЬ: =ВЗЯТЬ(СОРТ(ФИЛЬТР(A2:C7;B2:B7="North");3;-1);2) оставляет первые две (ВЗЯТЬ требует Microsoft 365 или Excel 2024). Другие варианты сортировки описаны на странице СОРТ и СОРТПО.

ФИЛЬТР строк, содержащих текст

У ФИЛЬТР нет подстановочных знаков, поэтому B2:B7="*th*" ищет буквальный текст *th*. Чтобы оставить строки, в имени которых есть какой-то текст, проверьте каждую ячейку функцией ПОИСК, которая возвращает позицию, если текст найден, и ошибку, если нет, и оберните её в ЕЧИСЛО:

ФормулыСбросить

E2fx

=FILTER(A2:C7,ISNUMBER(SEARCH("an",A2:A7)))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Dan	East	150
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ФИЛЬТР(A2:C7;ЕЧИСЛО(ПОИСК("an";A2:A7)))

Результат: Ann и Dan. ПОИСК не учитывает регистр, поэтому "an" совпадает и с An в Ann. Для совпадения с учётом регистра используйте НАЙТИ вместо ПОИСК.

Практика: ФИЛЬТР с двумя условиями

ФормулыСбросить

E2fx

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120
3	Ben	South	80
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.

Ваша очередь: В E2 верните строки (все три столбца) продавцов из South с продажами больше 85.

ПроверитьПоказать ответ

Практика: ФИЛЬТР по ячейке с запасным значением

ФормулыСбросить

F3fx

	A	B	C	D	E	F
1	Name	Region	Sales		Region	North
2	Ann	North	120
3	Ben	South	80		Names
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.

Ваша очередь: В F3 перечислите имена (только столбец A) продавцов из региона, введённого в F1. Если таких нет, покажите None.

ПроверитьПоказать ответ

Частые ошибки с ФИЛЬТР

• Диапазоны разной высоты. =ФИЛЬТР(A2:C7;B2:B6="North") проверяет 5 строк для таблицы из 6, и Excel возвращает #ЗНАЧ! (по-английски #VALUE!). include должен начинаться и заканчиваться на тех же строках, что и array.
• Нули там, где исходная ячейка пуста. ФИЛЬТР возвращает 0 для пустой ячейки в array. Перед фильтрацией замените пустые ячейки пустым текстом: =ФИЛЬТР(ЕСЛИ(A2:C7="";"";A2:C7);B2:B7="North").
• Целые столбцы. =ФИЛЬТР(A:C;B:B="North") работает, но если сама формула стоит в столбцах от A до C, она ссылается на себя. Помещайте результат рядом с таблицей или используйте фиксированный диапазон, например A2:C1000.
• Кавычки вокруг чисел. C2:C7>"100" сравнивает числа с текстом и ничего не оставляет. Пишите C2:C7>100.
• Ожидание кнопки «Фильтр». ФИЛЬТР копирует подходящие строки в новое место и не трогает таблицу. Чтобы скрыть строки в самой таблице, используйте Данные > Фильтр.