Общая формула
=ПОИСК(текст;сцепленные_столбцы)
=SEARCH(text,cocatenated_columns)
Объяснение
Если вы хотите выделить строки в таблице, содержащей определенный текст, вы используете условное форматирование с формулой, которая возвращает ИСТИНА, когда текст найден. Трюк состоит в том, чтобы объединить (склеить) столбцы, которые вы хотите найти, и заблокировать ссылки столбцов, чтобы изменить только строки.
Например, предположим, что у вас есть простая таблица данных в B4:E11, и вы хотите выделить все строки, содержащие текст «собака». Просто выберите все данные в таблице и создайте новое правило условного форматирования, которое использует эту формулу:
=ПОИСК(«собака»;$B4&$C4&$D4&$E4)
=SEARCH(«dog»,$B4&$C4&$D4&$E4)
Примечание: важно, чтобы формула условного форматирования была указана относительно «активной ячейки», которой в данном случае считается B4.
Как работает формула
Функция ПОИСК возвращает позицию текста, который вы ищете, в виде числа (если он существует). Условное форматирование автоматически обрабатывает любое положительное число как ИСТИНА, поэтому правило запускается всякий раз, когда поиск возвращает число. Когда ПОИСК не находит текст, который вы ищете, он возвращает ошибку #ЗНАЧ, которое условное форматирование относится к ЛОЖЬ.
Используя амперсанд (&), мы объединяем все значения в каждой строке вместе, а затем ищем результат с помощью ПОИСК. Все адреса ячеек вводятся в «смешанном» виде, при этом столбцы фиксируются, а строки остаются относительными. Это означает, что все 4 ячейки в каждой строке проверяются с точно такой же формулой.
Использование других ячеек для указания подстроки
Обратите внимание, что вам не нужно жестко фиксировать любые значения, которые могут быть изменены в правиле. Вместо этого вы можете использовать другую ячейку в качестве «входной» ячейки, чтобы ее можно было легко изменить позже. Например, в этом случае вы можете назвать «значение» ячейки E2 и переписать формулу следующим образом:
=ПОИСК(значение;$B4&$C4&$D4&$E4)
=SEARCH(input,$B4&$C4&$D4&$E4)
Затем вы можете поместить любое текстовое значение в E2, и правило условного форматирования будет выполняться мгновенно, выделяя строки, содержащие этот текст. Более подробное описание см. В приведенной ниже ссылке.
Вариант, чувствительный к регистру
Если вам нужен вариант с учетом регистра, вы можете использовать функцию НАЙТИ вместо ПОИСК следующим образом:
=НАЙТИ(значение;$B4&$C4&$D4&$E4)
=FIND(input,$B4&$C4&$D4&$E4)
Функция НАЙТИ работает так же, как ПОИСК, но также соответственно использованию
