Проверить, совпадает ли ячейка с одним из нескольких значений, в Excel можно формулой =ИЛИ(A2=$E$2:$E$5), а содержит ли она хотя бы одно из слов списка — формулой =СЧЁТ(ПОИСК($E$2:$E$5;A2))>0. Найти строку сразу по нескольким условиям (город и товар) помогает связка =ИНДЕКС(C2:C6;ПОИСКПОЗ(1;(A2:A6=E2)*(B2:B6=F2);0)). Отметить ячейки, подходящие под любое из десятков условий, без формул можно процедурой !SEMTools.
Найти несколько значений в Excel за один заход - нетривиальная задача. Дело в том, что текстовый фильтр в автофильтре принимает максимум два условия. Расширенный фильтр умеет больше, но требует отдельной таблицы критериев.
Если же хочется использовать сразу несколько условий поиска (3,4,5 и т.д.), эта процедура не подходит.
Как правило, такое нужно для текстовых критериев (полное совпадение, начинается на, содержит, заканчивается на). Потому что для числовых значений двух критериев обычно достаточно.
Поиск нескольких слов и фраз в Excel
Если нужно найти не ячейки целиком, а отдельные слова или фразы внутри текста, смотрите статьи:
Извлечь несколько слов из текста в Excel
Поиск нескольких значений по точному совпадению
Когда нужно выяснить, совпадает ли содержимое ячейки с одним из нескольких значений одновременно, сгодится простое текстовое сравнение в формуле массива:
=ИЛИ(A1=$E$1:$E$4)Как работает:
- Равенство в скобках (A1=$E$1:$E$4) проверяет, равно ли значение в ячейке A1 любому из значений в диапазоне $E$1:$E$4 и возвращает булевый массив (ИСТИНА-ЛОЖЬ)
- Знак доллара $ в ссылке $E$1:$E$4 создает абсолютную ссылку, что означает, что при копировании формулы эта ссылка не будет меняться.
- Функция ИЛИ возвращает ИСТИНА, если хотя бы одно из сравнений A1=E1, A1=E2, A1=E3 или A1=E4 дает результат ИСТИНА.
- Если значение в ячейке A1 совпадает с любым значением из списка в E1:E4, формула вернет ИСТИНА. В противном случае будет возвращено ЛОЖЬ.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Город | Есть в списке? | Список | |
| 2 | Москва | ИСТИНА=ИЛИ(A2=$D$2:$D$5) | Москва | |
| 3 | Тверь | ЛОЖЬ=ИЛИ(A3=$D$2:$D$5) | Казань | |
| 4 | Казань | ИСТИНА=ИЛИ(A4=$D$2:$D$5) | Самара | |
| 5 | Омск | ЛОЖЬ=ИЛИ(A5=$D$2:$D$5) | Пермь | |
| 6 | Самара | ИСТИНА=ИЛИ(A6=$D$2:$D$5) |
Каждая ячейка столбца A сравнивается со всеми четырьмя городами из D2:D5. В Excel 2019 и раньше формулу вводят сочетанием Ctrl+Shift+Enter.
В английском Excel: =OR(A2=$D$2:$D$5)
Эта формула особенно полезна для проверки принадлежности значения к определенному списку допустимых значений.
Если важен учёт регистра символов, вместо обычного сравнения потребуется использовать функцию СОВПАД.
Найти частичное совпадение: ПОИСК и НАЙТИ
Если нам нужен текстовый поиск не по полному, а по частичному совпадению, в зависимости от типа поиска (начинается с, заканчивается на, содержит) нам потребуются разные формулы.
Все они используют функции ПОИСК и НАЙТИ. Разница функций лишь в том, что одна из них учитывает регистр, а другая - нет. Подробнее о них можно почитать в соответствующих статьях.
Ячейка содержит одно из значений
При обычном поиске нескольких значений ("содержит") нам потребуется такая формула:
=СЧЁТ(ПОИСК(B1:B10; A1))>0Объяснение логики:
- ПОИСК(B1:B10; A1) - ищет каждое значение из диапазона B1:B10 в ячейке A1 и возвращает массив чисел/ошибок.
- СЧЁТ - считает числа в массиве
- >0 - возвращает ИСТИНА если есть хотя бы одно число, ЛОЖЬ если чисел нет.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Запрос | Содержит слово? | Список | |
| 2 | купить ноутбук asus | ИСТИНА=СЧЁТ(ПОИСК($D$2:$D$4;A2))>0 | ноутбук | |
| 3 | чехол для телефона | ИСТИНА=СЧЁТ(ПОИСК($D$2:$D$4;A3))>0 | планшет | |
| 4 | наушники | ЛОЖЬ=СЧЁТ(ПОИСК($D$2:$D$4;A4))>0 | телефон |
ПОИСК возвращает позицию для найденных слов и ошибку для остальных, СЧЁТ считает только числа. «телефона» подходит: ищется фрагмент, а не слово целиком.
В английском Excel: =COUNT(SEARCH($D$2:$D$4,A2))>0
Подобным способом, например, можно найти символы английского алфавита или найти цифры в текстовых ячейках.
Ячейка начинается с одного из значений
Если мы хотим узнать, что ячейка начинается хотя бы с одного из нескольких значений в диапазоне, можем использовать такую формулу:
=ИЛИ(ЕСЛИОШИБКА(ПОИСК(B1:B10; A1)=1;ЛОЖЬ))- ПОИСК(B1:B10; A1) - ищет каждое значение из диапазона B1:B10 в ячейке A1, возвращает позицию первого вхождения или ошибку #ЗНАЧ!
- =1 - проверяет, равно ли значение 1, это означает: "начинается ли текст с искомого значения"
- ЕСЛИОШИБКА(...; ЛОЖЬ) - обрабатывает ошибки, если ПОИСК возвращает ошибку (значение не найдено), заменяет ее на ЛОЖЬ
- ИЛИ(...) - проверяет массив результатов и возвращает ИСТИНА, если хотя бы один элемент массива равен ИСТИНА, и ЛОЖЬ, если все элементы ЛОЖЬ
Что делает формула: Проверяет, начинается ли текст в ячейке A1 с ЛЮБОГО из значений из списка B1:B10
- Пример: A1 = "Проект отчет 2024"
- B1:B10 содержит: ["Отчет", "План", "Задача"]
- Формула вернет ЛОЖЬ, потому что: "Проект" не начинается с "Отчет", "План" или "Задача"
- Если бы A1 = "Отчет по проекту", формула вернула бы ИСТИНА
Особенности:
- Чувствительна только к началу строки
- Не находит значения в середине или конце текста
- Учитывает точное начало (позиция = 1)
- Работает как логическое ИЛИ по всем значениям списка
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Текст | Начинается? | Список | |
| 2 | Отчет за май | ИСТИНА=ИЛИ(ЕСЛИОШИБКА(ПОИСК($D$2:$D$4;A2)=1;ЛОЖЬ)) | отчет | |
| 3 | Проект отчет 2024 | ЛОЖЬ=ИЛИ(ЕСЛИОШИБКА(ПОИСК($D$2:$D$4;A3)=1;ЛОЖЬ)) | план | |
| 4 | План продаж | ИСТИНА=ИЛИ(ЕСЛИОШИБКА(ПОИСК($D$2:$D$4;A4)=1;ЛОЖЬ)) | задача |
«Проект отчет 2024» содержит «отчет», но не в начале: ПОИСК вернул 8, а не 1. Регистр не важен.
В английском Excel: =OR(IFERROR(SEARCH($D$2:$D$4,A2)=1,FALSE))
Ячейка заканчивается на одно из значений
=ИЛИ(ЕСЛИОШИБКА(ПОИСК(B1:B10; A1)=ДЛСТР(A1)-ДЛСТР(B1:B10)+1;ЛОЖЬ))Формула проверяет, заканчивается ли текст в ячейке A1 на ЛЮБОЕ из значений из списка B1:B10.
- ДЛСТР(A1) определяет общую длину текста в ячейке A1.
- ДЛСТР(B1:B10) вычисляет длины всех искомых значений из списка.
- Выражение ДЛСТР(A1)-ДЛСТР(B1:B10)+1 вычисляет позицию, с которой должно начинаться искомое значение в конце строки.
- ПОИСК ищет каждое значение из списка и проверяет, находится ли оно в вычисленной конечной позиции.
- ЕСЛИОШИБКА обрабатывает ошибки, заменяя их на ЛОЖЬ.
- ИЛИ возвращает ИСТИНА, если хотя бы одно значение из списка находится в конце строки.
Формула работает по принципу: значение заканчивается на искомый текст, если он начинается с позиции (общая длина - длина искомого текста + 1).
А так она выглядит в действии (диапазоны слегка другие):
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Файл | Заканчивается? | Список | |
| 2 | отчёт.pdf | ИСТИНА=ИЛИ(ЕСЛИОШИБКА(ПОИСК($D$2:$D$3;A2)=ДЛСТР(A2)-ДЛСТР($D$2:$D$3)+1;ЛОЖЬ)) | ||
| 3 | pdf-инструкция | ЛОЖЬ=ИЛИ(ЕСЛИОШИБКА(ПОИСК($D$2:$D$3;A3)=ДЛСТР(A3)-ДЛСТР($D$2:$D$3)+1;ЛОЖЬ)) | docx | |
| 4 | итог.docx | ИСТИНА=ИЛИ(ЕСЛИОШИБКА(ПОИСК($D$2:$D$3;A4)=ДЛСТР(A4)-ДЛСТР($D$2:$D$3)+1;ЛОЖЬ)) |
Значение стоит в конце, если его позиция равна длине текста минус длина значения плюс один. «pdf-инструкция» начинается с pdf, поэтому не подходит.
Если ячейка содержит одно из слов — вернуть значение
Проверку «содержит» удобно обернуть в ЕСЛИ, чтобы получить свою метку, а функция ПРОСМОТР с приёмом «2; 1/…» вернёт само найденное слово:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Запрос | Метка | Найденное слово | Слова |
| 2 | Купить ноутбук Lenovo | целевой=ЕСЛИ(СЧЁТ(ПОИСК($D$2:$D$4;A2))>0;"целевой";"прочее") | ноутбук=ПРОСМОТР(2;1/ЕЧИСЛО(ПОИСК($D$2:$D$4;A2));$D$2:$D$4) | ноутбук |
| 3 | Чехол для iPhone 15 | целевой=ЕСЛИ(СЧЁТ(ПОИСК($D$2:$D$4;A3))>0;"целевой";"прочее") | iPhone=ПРОСМОТР(2;1/ЕЧИСЛО(ПОИСК($D$2:$D$4;A3));$D$2:$D$4) | iPhone |
| 4 | Мышь беспроводная | прочее=ЕСЛИ(СЧЁТ(ПОИСК($D$2:$D$4;A4))>0;"целевой";"прочее") | #Н/Д=ПРОСМОТР(2;1/ЕЧИСЛО(ПОИСК($D$2:$D$4;A4));$D$2:$D$4) | планшет |
| 5 | Ноутбук ASUS б/у | целевой=ЕСЛИ(СЧЁТ(ПОИСК($D$2:$D$4;A5))>0;"целевой";"прочее") | ноутбук=ПРОСМОТР(2;1/ЕЧИСЛО(ПОИСК($D$2:$D$4;A5));$D$2:$D$4) |
ПОИСК не различает регистр, поэтому «Ноутбук» в A5 тоже найден. ПРОСМОТР возвращает последнее подходящее слово из списка, а #Н/Д — если ни одно не найдено (оберните в ЕСЛИОШИБКА).
В английском Excel: =LOOKUP(2,1/ISNUMBER(SEARCH($D$2:$D$4,A3)),$D$2:$D$4)
Найти строку по нескольким условиям: ИНДЕКС и ПОИСКПОЗ
ВПР ищет только по одному столбцу. Чтобы найти значение по двум и более условиям — например, продажи товара в конкретном городе, — условия перемножают: (A2:A6=E2)*(B2:B6=F2) даёт 1 только в строке, где выполнены оба. ПОИСКПОЗ находит эту единицу, а ИНДЕКС возвращает значение из нужного столбца:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Город | Товар | Продажи | Город? | Товар? | Результат | |
| 2 | Москва | Чай | 120 | Казань | Кофе | 210=ИНДЕКС(C2:C6;ПОИСКПОЗ(1;(A2:A6=E2)*(B2:B6=F2);0)) | |
| 3 | Москва | Кофе | 340 | ||||
| 4 | Казань | Чай | 95 | ||||
| 5 | Казань | Кофе | 210 | ||||
| 6 | Самара | Чай | 80 |
Поменяйте город или товар в E2:F2 — формула найдёт другую строку. В Excel 2019 и раньше вводите её через Ctrl+Shift+Enter. Если нужна сумма по условиям, а не одно значение, используйте СУММЕСЛИМН.
В английском Excel: =INDEX(C2:C6,MATCH(1,(A2:A6=E2)*(B2:B6=F2),0))
В Excel 365 то же самое короче делают ФИЛЬТР (FILTER) и ПРОСМОТРX (XLOOKUP): =ФИЛЬТР(C2:C6;(A2:A6=E2)*(B2:B6=F2)) вернёт все подходящие значения, а не только первое. Сумму по нескольким условиям считает СУММЕСЛИМН.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Город | Товар | Цена | Город | Товар | Цены | |
| 2 | Москва | чай | 120 | Москва | чай | 120=ФИЛЬТР(C2:C5;(A2:A5=E2)*(B2:B5=F2)) | |
| 3 | Москва | кофе | 300 | 150 | |||
| 4 | Казань | чай | 110 | ||||
| 5 | Москва | чай | 150 |
Перемножение условий — это «И»: строка подходит, только если оба сравнения дали ИСТИНА. ФИЛЬТР вывел обе цены «чая в Москве», а ИНДЕКС+ПОИСКПОЗ вернул бы только первую.
В английском Excel: =FILTER(C2:C5,(A2:A5=E2)*(B2:B5=F2))
Есть ли в диапазоне одно из значений: ВПР
Чтобы узнать, есть ли в диапазоне хоть одно из нескольких значений, можно использовать формулу массива на основе функции ВПР:
=СУММ(ДЛСТР(ЕСЛИОШИБКА(ВПР(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 + Enter, в новых этого делать не нужно.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Диапазон | Список | Проверка | Результат | ||
| 2 | Москва | Каз | Есть одно из значений | ЛОЖЬ=СУММ(ДЛСТР(ЕСЛИОШИБКА(ВПР(C2:C3;A2:A5;1;0);"")))>0 | ||
| 3 | Санкт-Петербург | бург | Есть начинающиеся на | ИСТИНА=СУММ(ДЛСТР(ЕСЛИОШИБКА(ВПР(C2:C3&"*";A2:A5;1;0);"")))>0 | ||
| 4 | Казань | Есть заканчивающиеся на | ИСТИНА=СУММ(ДЛСТР(ЕСЛИОШИБКА(ВПР("*"&C2:C3;A2:A5;1;0);"")))>0 | |||
| 5 | Новосибирск |
«Каз» и «бург» не совпадают ни с одной ячейкой целиком, поэтому первая проверка — ЛОЖЬ. Со звёздочкой ВПР ищет по маске: «Каз*» находит «Казань», «*бург» — «Санкт-Петербург».
Отличие этих формул от формул массива на основе функций ПОИСК и НАЙТИ в том, что она проверяет сразу целый диапазон, а не каждое его значение по отдельности.
Найти ячейки по нескольким условиям в 2 клика с !SEMTools
Формулы выше прекрасно справляются с поставленными задачами. Однако, если подобные задачи приходится решать часто, поиск сохраненной копии формулы или её ручной ввод могут здорово замедлить процесс.
В подобной ситуации бывал неоднократно и автор этого сайта, поэтому придумал автоматизировать решение задачи, и создал под это несколько удобных процедур. А потом для множества других и т.д. Так однажды родилась целая надстройка для Excel.
Надстройка !SEMTools позволяет выполнять поиск по нескольким условиям без сложных формул. Вы можете выделить диапазон и задать критерии фильтрации — надстройка автоматически вернёт ИСТИНА и ЛОЖЬ напротив значений, удовлетворяющих нескольким условиям.
Почему поиск по нескольким условиям не работает
- Формула возвращает только одно значение или #ЗНАЧ!. В Excel 2019 и раньше формулы с диапазоном внутри (A2=$D$2:$D$5, ПОИСК(диапазон;…)) нужно вводить сочетанием Ctrl+Shift+Enter. Исключение — СЧЁТ(ПОИСК(…)) и СУММПРОИЗВ, они работают и без него.
- Находится лишнее. ПОИСК ищет подстроку: «кот» найдётся и в «котёл». Если нужно целое слово, ищите с пробелами: ПОИСК(" "&$D$2:$D$4&" ";" "&A2&" ").
- Пустая ячейка в списке ломает результат. ПОИСК пустой строки возвращает 1, и формула «содержит» срабатывает на любой ячейке. Список условий не должен содержать пустых ячеек, или ограничьте диапазон.
- Не находится из-за лишних пробелов или регистра. Сравнение = и ПОИСК не учитывают регистр, а НАЙТИ и СОВПАД — учитывают. Лишние пробелы в данных удалите заранее.
Поиск по нескольким условиям — частые вопросы
Как найти несколько значений в Excel одновременно?
Чтобы проверить, совпадает ли ячейка с одним из значений списка, используйте =ИЛИ(A2=$D$2:$D$5). Чтобы проверить, содержит ли она одно из слов, — =СЧЁТ(ПОИСК($D$2:$D$5;A2))>0.
Если ячейка содержит слово, как вернуть значение?
Оберните проверку в ЕСЛИ: =ЕСЛИ(СЧЁТ(ПОИСК("ноутбук";A2))>0;"да";"нет"). Для списка слов вместо "ноутбук" укажите диапазон.
Как сделать ВПР по двум условиям?
Через ИНДЕКС и ПОИСКПОЗ с перемножением условий: =ИНДЕКС(C2:C6;ПОИСКПОЗ(1;(A2:A6=E2)*(B2:B6=F2);0)). В Excel 365 — ФИЛЬТР или ПРОСМОТРX.
Сколько условий принимает фильтр Excel?
Текстовый фильтр в автофильтре — два условия. Больше — через расширенный фильтр с таблицей условий, вспомогательный столбец с формулой из этой статьи или процедуру !SEMTools.
Отметить ячейки, которые совпадают, начинаются, содержат или заканчиваются на любое из десятков значений списка, можно в пару кликов с !SEMTools — без формул массива и Ctrl+Shift+Enter.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как искать по нескольким условиям в Excel
Надеюсь, на этой странице вы нашли то, что искали, и смогли найти нужные значения. Коротко о выборе способа:
| Задача | Формула |
|---|---|
| Ячейка равна одному из значений | ИЛИ(A2=список) |
| Ячейка содержит одно из слов | СЧЁТ(ПОИСК(список;A2))>0 |
| Начинается / заканчивается на одно из значений | ИЛИ(ЕСЛИОШИБКА(ПОИСК(…)=позиция;ЛОЖЬ)) |
| Вернуть найденное слово | ПРОСМОТР(2;1/ЕЧИСЛО(ПОИСК(…));список) |
| Значение по двум условиям | ИНДЕКС + ПОИСКПОЗ или ФИЛЬТР (365) |
| Десятки условий, регулярно | !SEMTools |
Вас наверняка заинтересуют и похожие операции: найти слово в ячейках и заменить несколько значений сразу.