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

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

Эта статья создана для пользователей, уже уверенно применяющих Условное форматирование. Для тех, кто только начинает использовать этот инструмент, рекомендуется ознакомиться с базовой статьей по этой теме Условное форматирование в MS EXCEL.

Исходная таблица с текстовыми значениями

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

Задача 1 (поиск значений в списке, совпадающих с единственным критерием)

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

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

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

Решение

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

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

  • выделите ячейки, в которые введен перечень инструментов (А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) возвращает номер позиции текстовой строки из ячейки А9, с которой начинается текст, указанный в критерии (если он найден).

Например, для ячейки А14 формула ПОИСК($A$6;A14) вернет 1, а для А15 формула ПОИСК($A$6;A15) вернет 8 и обе ячейки будут выделены. Если в ячейке не содержится искомый текст, то формула возвращает ошибку и ячейка не выделяется.

Примечание: Обратите внимание, что будет выделена ячейка со словом Мотодрель, как ячейка содержащая критерий. Но, если нужно найти ячейки содержащие слово Дрель (т.е. текст, отделенный от других слов пробелами), то нужно обратиться к решению Задачи 2 (см. ниже).

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

Примечание: Обратите внимание, что будет выделена ячейка со словом Мотодрель, как ячейка заканчивающаяся на критерий. Но, если нужно найти ячейки содержащие слово Дрель (т.е. текст, отделенный от других слов пробелами), то нужно обратиться к решению Задачи 2 (см. ниже).

Если критерий содержится в начале текстовой строки, то ПОИСК() вернет 1. Следовательно, формула ПОИСК($A$6;A9)=1 вернет ИСТИНА только для ячеек начинающихся с искомого текста.

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

Задача 2 (поиск СЛОВ в списке, совпадающих с единственным критерием)

Определить, есть ли слово в списке несколько сложнее, чем определить содержится ли в списке некий текст. Разница в этих подходах проявляется при наличии в списке слов, содержащих искомый текст, но не совпадающих с ним. Например, слово в списке Мотодрель содержит текст Дрель, но слово Дрель не содержится в этой ячейке.

Чтобы выделить ячейки в списке, которые содержат слово Дрель, нужно воспользоваться формулами в файле примера на Листе "1 критерий (слово)".

Понятно, что нужно выделить ячейки, в которых:

  • точно содержится слово Дрель ИЛИ;
  • текстовая строка начинается со слова Дрель ИЛИ;
  • текстовая строка заканчивается словом Дрель ИЛИ;
  • слово Дрель содержится в середине текстовой строки (критерий обрамлен пробелами).

В принципе можно записать одну большую формулу и не использовать дополнительные столбцы (C:F)

=ИЛИ(ИЛИ(ЕСЛИОШИБКА(A9=$A$6;0));
ИЛИ(ЕСЛИОШИБКА(ПОИСК(" "&$A$6&" ";A9);0));
ИЛИ(ЕСЛИОШИБКА(ПОИСК($A$6&" ";ЛЕВСИМВ(A9;ДЛСТР($A$6)+1));0));
ИЛИ(ЕСЛИОШИБКА(" "&$A$6=ПРАВСИМВ(A9;ДЛСТР($A$6)+1);0))
)

Задача 3 (поиск нескольких слов в списке)

Рассмотрим списки, элементами которых являются отдельные слова (не фразы). Выделять будем только ту ячейку, которая содержит любое из слов-критериев (см. файл примера Лист Неск критериев (слово точно)).

Это можно сделать несколькими способами.

Вариант с использованием Массива констант и имени. В этом случае в правиле Условного форматирования нужно ввести формулу =ИЛИ($A2=Слова), где Слова - это Имя массива констант.

Этот способ не очень удобен, т.к. при изменении критериев придется изменять массив констант в Диспетчере имен.

Вариант с использованием именованного диапазона. Формула =ИЛИ($B2=Слова_в_диапазоне) аналогична предыдущей, но имя Слова_в_диапазоне теперь ссылается на диапазон ячеек листа (см. статью Именованный диапазон). При добавлении новых слов в ячейки в столбце F, придется переопределять именованный диапазон.

Вариант с использованием динамического диапазона. Динамический диапазон позволяет добавлять новые слова-критерии в столбец F и при этом не требуется модифицировать формулы и имена.

Вариант без использования имен. Формула =ИЛИ($D2="слово1";$D2="слово2";$D2="слово3") позволяет отказаться от использования имен. Платой за это станет необходимость изменения правила Условного форматирования при каждом изменении слов-критериев.

Задача 4 (поиск нескольких слов в списке, общий случай)

Рассмотрим списки, элементами которых являются фразы. Выделять будем только ту ячейку, которая содержит любое из слов-критериев: в начале, в середине или конце фразы или если фраза целиком совпадает с одним из слов-критериев (см. файл примера Лист Неск критериев (слово)).

Слово Разряд в списке не встречается (склонения не считаются). А вот слово Техник встречается как в начале фраз, так и в середине и конце.

Не смотря на то, что формулы похожи на те, что использовались в Задаче 2, они от них принципиально отличаются, т.к. являются формулами массива.

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

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

Комментарии

Александр (не проверено)

Несомненно полезный пример. Но как быть с немного более простой задачей - необходимо чтобы выделялись ячейки удовлетворяющие двум условиям? Например в строке необходимо выделять ячейки со значениями "Сб" и "Вс"?
Заранее спасибо!

Creator

В статье http://excel2.ru/articles/sozdaem-kalendar-odnoy-formuloy-massiva дни недели, относящиеся к выходным, выделены с помощью Условного форматирования. Основная статья о выделении дней недели - http://excel2.ru/articles/vydelyaem-dni-nedeli-cvetom

ton (не проверено)

а можно приложить Файлик с примером у меня что то руки крюки не выходит что то каменный цветок

Wasp

Наш Автор сейчас загнивает во Франции, сможет ответить или добавить файл только вечером :)

ton (не проверено)

C нетерпением буду ждать спасибо

Creator

Файл загружен.

ton (не проверено)

Вы мои хорошие спасибо вам огромное

Wasp

и Вам всего хорошего :)

Romul

Добрый день.
Возможно ли применение одного правила для нескольких текстовых значаний? т.е. если ячейка содержит "слово1" или "слово2" или "слово3", то применяется условное форматирование (одно общее для этих ячеек). Понятно, что можно создать три правила, но нужно одно, на несколько значений. офис 2010

Creator

Очень хороший вопрос!

сначала создайте Имя Слова для константы массива ={"слово1";"слово2";"слово3"}.

В правиле Условного форматирования для ячейки А1 запишите =ИЛИ(A1=Слова)

Если не хотите заморачиваться с именами, напишите в правиле =ИЛИ(A1="слово1";A1="слово2";A1="слово3")

Romul

Благодарю, но это не совсем, что нужно. Видимо я не совсем понятно объяснил суть вопроса. Ваш ответ подойдет если ячейка в диапазоне содержит только слово в составе массива "А1="слово", мне же нужно , чтобы ячейка выделялась как при условии "А1 содержит слово". Например: Ячейка содержит текст "д/ж э80-100% РС Костюченко выв фонд НКТ 2.5-166што зав тех 2.5-245шт", к ней должно быть применено условное форматирование, потому, что она содержит "д/ж э" или "м/ж э" или "дж э" или "мж э" или "монтаж Э" или "демонтаж Э"... и т.д.
Таблицу заполняют разные люди из разных организаций, поэтому приходится учитывать все возможные варианты. Когда все это записывается в макрос, получается очень увесисто и комп серьезно задумывается. Вот я и подумал заменить 10 условий форматирования на одно, что бы при неоходимости можно было добавить очередную модификацию искомого сочетания без добавления веса макроса.

Creator

Обновил файл примера и статью под Вашу задачу. Но, все же этот подход использовать не рекомендую для Вашей задачи. Чтобы не было произвола в наборе стандартных фраз разными пользователями нужно заставить их не вводить, а выбирать определенные Вами значения с помощью выпадающего списка. Например, ввод фразы "д/ж э 80-100% Костюченко" можно разбить на 3 ячейки с выпадающими списками: вид работ (д/ж э; м/ж э; ...), процент выполнения (10%; 20%;...>80%) и ФИО. Окончательная фраза собирается с помощью амперсанда или функции СЦЕПИТЬ().

Still

Добрый день. Существует множество столбцов с разными данными, в столбце "A" даты, во столбце "B" категории к которым относятся расходы которые сформированы следующим образом: (Ячейкам K1,L1,M1,N1,O1 задал общее имя - "Категория", затем в ячейках с B2:B100 применил опцию данные-проверка данных и тип данных выбрал "список" указав диапазон своего имени, то есть (=Категория). Теперь при нажатии на ячейки с B2:B100 из выпадающего меню есть возможность выбирать нужное слово из ячеек ( K1,L1,M1,N1,O1)). В "С" столбце описание расходов. В "D" цена расходов. Теперь вопрос: Нужно сделать так чтобы при нажатии в списке категорий (B2:B100) то бишь к примеру: личные расходы, работа, дорога в "D" столбце нужного цвета помечались цифры, то есть если выбираю работа в столбце "B", то напротив ячейки B5, в ячейки D5 цена подкрашивается синим, при выборе дорога в ячейке B7, в ячейке D7 цвет зелёный. Это нужно для того, что когда сформируется цвет в столбце D, произвести подсчёт данных именно по цвету, а не по категории, макрос для подсчёта цвета написать удалось, но вот как сделать чтобы при выборе определённой категории из выпадающего меню подкрашивалась соответсвующая ячейка в другом столбце на той же строке ? Заранее спасибо за ответ.

Creator

Вы все подробно изложили. См. статью Выделение строк таблицы в MS EXCEL в зависимости от условия в ячейке 

PS. Из вашего комментария следует, что цвет определяет именно категория, хотя вы пишете обратное.

Уверен, что с макросами здесь возиться незачем, можно сделать подсчет по условию.

Romul

Благодарен Вам за решение вопроса (хоть и с большой задержкой). Судя по примеру- это то, что нужно. Правда осуществить это с первого подхода не вышло (надо с дин. диапазонами поупрожняться). К сожалению влиять на качество вводимой информации не имею возможности, так как условно говоря нахожусь между пунктами А и Б информационного потока, а пункт Б все устраивает.

Яндекс.Метрика