Поиск нескольких значений / по нескольким условиям в Excel

Найти несколько значений в Excel за один заход – нетривиальная задача. Дело в том, что штатная процедура поиска (Найти и заменить), вызываемая сочетанием клавиш Ctrl + F, способна искать максимум по двум условиям. Да, эти условия могут быть абсолютно разными, но их всего два.

Если же хочется использовать сразу несколько условий поиска (3,4,5 и т.д.), эта процедура не подходит.

Как правило, такое нужно для текстовых критериев (полное совпадение, начинается на, содержит, заканчивается на). Потому что для числовых значений двух критериев обычно достаточно.

Давайте рассмотрим варианты решения задачи с поиском ячеек по нескольким условиям.

Поиск нескольких значений по точному совпадению

Когда нужно выяснить, совпадает ли содержимое ячейки с одним из нескольких значений одновременно, сгодится простое текстовое сравнение в формуле массива:

=ИЛИ(A1=$D$1:$D$4)

Как работает:

• Равенство в скобках (A1=$D$1:$D$4) проверяет, равно ли значение в ячейке A1 любому из значений в диапазоне $D$1:$D$4 и возвращает булевый массив (ИСТИНА-ЛОЖЬ)
• Знак доллара $ в ссылке $D$1:$D$4 создает абсолютную ссылку, что означает, что при копировании формулы эта ссылка не будет меняться.
• Функция ИЛИ возвращает ИСТИНА, если хотя бы одно из сравнений A1=D1, A1=D2, A1=D3 или A1=D4 дает результат ИСТИНА.
• Если значение в ячейке A1 совпадает с любым значением из списка в D1:D4, формула вернет ИСТИНА. В противном случае будет возвращено ЛОЖЬ.

Эта формула особенно полезна для проверки принадлежности значения к определенному списку допустимых значений.

Если важен учёт регистра символов, вместо обычного сравнения потребуется использовать функцию СОВПАД.

Функции ПОИСК и НАЙТИ в формулах массива

Если нам нужен текстовый поиск не по полному, а по частичному совпадению, в зависимости от типа поиска (начинается с, заканчивается на, содержит) нам потребуются разные формулы.

Все они используют функции ПОИСК и НАЙТИ. Разница функций лишь в том, что одна из них учитывает регистр, а другая – нет. Подробнее о них можно почитать в соответствующих статьях.

Поиск нескольких значений по условию “содержит”

При обычном поиске нескольких значений (“содержит”) нам потребуется такая формула:

=СЧЁТ(ПОИСК(B1:B10; A1))>0

Объяснение логики:

• ПОИСК(B1:B10; A1) – ищет каждое значение из диапазона B1:B10 в ячейке A1 и возвращает массив чисел/ошибок.
• СЧЁТ – считает числа в массиве
• >0 – возвращает ИСТИНА если есть хотя бы одно число, ЛОЖЬ если чисел нет.

Подобным способом, например, можно найти символы английского алфавита или найти цифры в текстовых ячейках.

Поиск нескольких значений по условию “Начинается с”

Если мы хотим узнать, что ячейка начинается хотя бы с одного из нескольких значений в диапазоне, можем использовать такую формулу:

=ИЛИ(ЕСЛИОШИБКА(ПОИСК(B1:B10; A1)=1;ЛОЖЬ))

• ПОИСК(B1:B10; A1) – ищет каждое значение из диапазона B1:B10 в ячейке A1, возвращает позицию первого вхождения или ошибку #ЗНАЧ!
• =1 – проверяет, равно ли значение 1, это означает: “начинается ли текст с искомого значения”
• ЕСЛИОШИБКА(…; ЛОЖЬ) – обрабатывает ошибки, если ПОИСК возвращает ошибку (значение не найдено), заменяет ее на ЛОЖЬ
• ИЛИ(…) – проверяет массив результатов и возвращает ИСТИНА, если хотя бы один элемент массива равен ИСТИНА, и ЛОЖЬ, если все элементы ЛОЖЬ

Что делает формула: Проверяет, начинается ли текст в ячейке A1 с ЛЮБОГО из значений из списка B1:B10

• Пример: A1 = “Проект отчет 2024”
• B1:B10 содержит: [“Отчет”, “План”, “Задача”]
• Формула вернет ЛОЖЬ, потому что: “Проект” не начинается с “Отчет”, “План” или “Задача”
• Если бы A1 = “Отчет по проекту”, формула вернула бы ИСТИНА

Особенности:

• Чувствительна только к началу строки
• Не находит значения в середине или конце текста
• Учитывает точное начало (позиция = 1)
• Работает как логическое ИЛИ по всем значениям списка

Поиск нескольких значений по условию “Заканчивается на”

=ИЛИ(ЕСЛИОШИБКА(ПОИСК(B1:B10; A1)=ДЛСТР(A1)-ДЛСТР(B1:B10)+1;ЛОЖЬ))

Формула проверяет, заканчивается ли текст в ячейке A1 на ЛЮБОЕ из значений из списка B1:B10.

• ДЛСТР(A1) определяет общую длину текста в ячейке A1.
• ДЛСТР(B1:B10) вычисляет длины всех искомых значений из списка.
• Выражение ДЛСТР(A1)–ДЛСТР(B1:B10)+1 вычисляет позицию, с которой должно начинаться искомое значение в конце строки.
• ПОИСК ищет каждое значение из списка и проверяет, находится ли оно в вычисленной конечной позиции.
• ЕСЛИОШИБКА обрабатывает ошибки, заменяя их на ЛОЖЬ.
• ИЛИ возвращает ИСТИНА, если хотя бы одно значение из списка находится в конце строки.

Формула работает по принципу: значение заканчивается на искомый текст, если он начинается с позиции (общая длина – длина искомого текста + 1).

А так она выглядит в действии (диапазоны слегка другие):

Поиск нескольких значений в диапазоне функцией ВПР

Чтобы узнать, есть ли в диапазоне хоть одно из нескольких значений, можно использовать формулу массива на основе функции ВПР:

=СУММ(ДЛСТР(ЕСЛИОШИБКА(ВПР(B1:B10;A1:A11;1;0);"")))>0

Вместо ВПР можно использовать ПРОСМОТРX, но я выбрал её как более привычную и универсальную функцию.

Объяснение логики:

• ВПР(B1:B10;A1:A11;1;0) ищет каждое значение из диапазона B1:B10 в диапазоне A1:A11. Функция возвращает найденные значения или ошибку #Н/Д для значений, которые не были найдены.
• ЕСЛИОШИБКА(…;“”) обрабатывает возможные ошибки, заменяя все ошибки #Н/Д на пустые строки “”. Это позволяет избежать прерывания работы формулы из-за ошибок.
• ДЛСТР(…) вычисляет длину каждой строки в полученном массиве. Для найденных значений возвращается фактическая длина строки, а для пустых строк возвращается 0.
• СУММ(…) суммирует все длины строк из предыдущего шага. Если хотя бы одно значение было найдено, сумма будет больше нуля.
• >0 выполняет финальную проверку: если сумма длин больше нуля, формула возвращает ИСТИНА, что означает, что хотя бы одно значение из списка B1:B10 было найдено в диапазоне A1:A11. Если сумма равна нулю, формула возвращает ЛОЖЬ.

Похожая формула выявит, есть ли в диапазоне ячейки, начинающиеся на что-либо из списка:

=СУММ(ДЛСТР(ЕСЛИОШИБКА(ВПР(B1:B10&"*";A1:A11;1;0);"")))>0

А эта – заканчиваются ли ячейки диапазона на любое из значений:

=СУММ(ДЛСТР(ЕСЛИОШИБКА(ВПР("*"&B1:B10;A1:A11;1;0);"")))>0

В старых версиях Excel формулы нужно вводить сочетанием клавиш Ctrl + Shift + Ввод, в новых этого делать не нужно.

Отличие этих формул от формул массива на основе функций ПОИСК и НАЙТИ в том, что она проверяет сразу целый диапазон, а не каждое его значение по отдельности.

Найти ячейки по нескольким условиям с !SEMTools

Формулы выше прекрасно справляются с поставленными задачами. Однако, если подобные задачи приходится решать часто, поиск сохраненной копии формулы или её ручной ввод могут здорово замедлить процесс.

В подобной ситуации бывал неоднократно и автор этого сайта, поэтому придумал автоматизировать решение задачи, и создал под это несколько удобных процедур. А потом для множества других и т.д. Так однажды родилась целая надстройка для Excel.

Надстройка !SEMTools позволяет выполнять поиск по нескольким условиям без сложных формул. Вы можете выделить диапазон и задать критерии фильтрации — надстройка автоматически вернёт ИСТИНА и ЛОЖЬ напротив значений, удовлетворяющих нескольким условиям.