Выделение ячеек c ТЕКСТом с применением Условного форматирования (с учетом РЕгиСТра) в EXCEL

Выделяем ячейки, содержащие искомый текст с учетом РЕгиСТра. Рассмотрим разные варианты: выделение ячеек, содержащих значения в точности совпадающих с искомым текстом; выделение ячеек, которые содержат искомый текст в начале, в конце или середине строки.

Пусть в диапазоне А9:A17 имеется таблица с перечнем инструментов (см. файл примера )

Задача

Выделим ячейки, содержащих значения, удовлетворяющих критерию (с учетом РЕгиСТра ). Критерий введем в ячейку B 6 . Кроме того, зададим 4 Типа поиска:

• Точно совпадает (выделение ячеек, содержащих значения в точности совпадающих с искомым текстом);
• Содержит (выделение ячеек, которые содержат искомый текст в начале, в конце или середине строки);
• Начинается (выделение ячеек, которые содержат искомый текст в начале строки);
• Заканчивается (выделение ячеек, которые содержат искомый текст в конце строки).

В качестве примера Критерия используем слово « Дрель », которое введем в ячейку B 6 . Для критерия можно использовать любое другое слово, часть слова или фразы.

Решение

Выбор Типа поиска организуем с помощью Элементов управления формы Группа и Переключатель. Свяжем все Переключатели с ячейкой B9 . Теперь при выборе Типа поиска Точно совпадает в этой ячейке будет содержаться число 1, при выборе Содержит - 2, Начинается - 3, Заканчивается - 4.

Настроим Условное форматирование для выделения ячеек, удовлетворяющих критерию (с учетом РЕгиСТра). Для этого:

• выделите ячейки, в которые введен перечень инструментов ( А9:A17 );
• вызовите инструмент Условное форматирование ( Главная/ Стили/ Условное форматирование/ Создать правило )
• выберите Использовать формулу для определения форматируемых ячеек;
• в поле « Форматировать значения, для которых следующая формула является истинной » введите =ВЫБОР($B$9;СОВПАД($A$6;A9);НАЙТИ($A$6;A9);НАЙТИ($A$6;A9)=1; СОВПАД($A$6;ПРАВСИМВ(A9;ДЛСТР($A$6))))
• выберите требуемый формат, например, красный цвет фона .

Если в качестве примера Критерия используется слово « Дрель », а Тип поиска выбран Точно совпадает (1), то красным фоном выделится только ячейка A14 , содержащая значение в точности совпадающее с искомым текстом.

Разберем работу формулы из вышеуказанного правила Условного форматирования .

Функция ВЫБОР() позволяет в одной формуле совместить 4 формулы для всех Типов поиска:

• Точно совпадает: СОВПАД($A$6;A9) ;
• Содержит: НАЙТИ($A$6;A9) ;
• Начинается: НАЙТИ($A$6;A9)=1 ;
• Заканчивается: СОВПАД($A$6;ПРАВСИМВ(A9;ДЛСТР($A$6))) .

В зависимости от значения в ячейке B9 , связанной с Переключателями, функция ВЫБОР() возвращает соответствующую формулу.

Функция СОВПАД() сравнивает аргументы и возвращает значение ИСТИНА, если они в точности совпадают, и ЛОЖЬ — в противном случае. Функция СОВПАД() учитывает РЕгиСТр.

Функция НАЙТИ() пытается найти первое вхождение Критерия в каждом значении перечня с учетом РегиСТра и, в случае успеха, возвращает начальную позицию найденного значения. В противном случае возвращается ошибка #ЗНАЧ!

Формула СОВПАД($A$6;ПРАВСИМВ(A9;ДЛСТР($A$6))) возвращает правую часть каждого значения перечня длиной равной критерию, т.е. в нашем случае 5 (т.к. в слове Дрель 5 букв), а затем сравнивает ее с критерием. Если оба выражения совпадают, то это означает, что значение перечня заканчивается на слово, указанное в Критерии.

Другой пример

В качестве другого примера найдем все названия инструментов, содержащие строчную букву р . Для этого в ячейку A6 вместо слова Дрель введем букву р . Тип поиска установим Содержит . В результате получим:

Теперь в качестве критерия введеи ПРОПИСНУЮ букву Р . Тип поиска установим Содержит . В результате получим:

Как видим, результаты отличаются, т.к. сравнение значений с критерием происходит с учетом РЕгиСТра .

Совет : Для настройки поиска ячеек, значения которых удовлетворяют критерию без учета РЕгиСТра, читайте статью Выделение ячеек c ТЕКСТом с применением Условного форматирования .