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

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

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

Задача

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

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

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

Решение

Выбор Типа поиска организуем с помощью Элементов управления формы Группа и Переключатель. Свяжем все Переключатели с ячейкой 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 ТЕКСТом с применением Условного форматирования.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (1 оценка)
Яндекс.Метрика