Формула условного форматирования текста, содержащего значение из другой ячейки

У меня есть столбец A, содержащий фразы из 1-4 слов в каждой ячейке.

У меня также есть 4 ячейки, содержащие значения 1 слова:

B1 C1 D1 и E1

Мне нужно настроить условное форматирование таким образом, чтобы:

1) Если текст в ячейке из столбца A содержит слово, которое соответствует значению из ОДНОЙ из ячеек, упомянутых выше, выделите эту ячейку (из столбца A) красным цветом.

2) Если текст в ячейке из столбца A содержит слова, которые соответствуют значению из ДВУХ ячеек, упомянутых выше, выделите эту ячейку (из столбца A) синим цветом.

3) Если текст в ячейке из столбца A содержит слова, которые соответствуют значению из ТРЕХ ячеек, упомянутых выше, выделите эту ячейку (из столбца A) желтым цветом.

4) Если текст в ячейке из столбца A содержит слова, которые соответствуют значениям из всех ЧЕТЫРЕХ ячеек, упомянутых выше, выделите эту ячейку (из столбца A) зеленым цветом.

Просмотрите прикрепленное изображение для иллюстрации:

Пример

Когда я меняю значение в любой из ячеек B1 C1 D1 или E1, я хочу, чтобы оно отражалось в столбце A, если не сразу, то посредством запуска какого-то макроса.

Я подозреваю, что это должно быть условное форматирование по формуле или запуск какого-то макроса ...

P.S: я использую Excel 2010


excel conditional-formatting excel-formula excel-2010
person Acidon    schedule 05.03.2014    source источник

Ответы (1)


arrow_upward
4
arrow_downward

Используйте эту формулу для условного форматирования:

=SUM(COUNTIF(A1,"*" & $B$1:$E$1 & "*")) = 1

Очевидно, вам нужно будет добавить формулу для 2, 3 и 4 и выбрать подходящие цвета, но это сработает.

Если вы хотите проверить формулу в ячейке, ее необходимо ввести как формулу массива с помощью Ctrl-Shift-Enter. Но условное форматирование распознает формулы массива без каких-либо сложных действий.

Формула говорит, что суммируйте количество вхождений значений, содержащихся в B1, в E1, окруженных любым текстом, отсюда и подстановочные знаки. Если вы возьмете только часть формулы СЧЁТЕСЛИ и нажмете F9, вы увидите, что она оценивается примерно так:

=SUM({1,0,0,0}) = 1

введите описание изображения здесь

Чтобы применить условное форматирование ко всему столбцу A, просто введите $ A: $ A в поле «Применимо к» для каждой формулы:

введите описание изображения здесь

person Doug Glancy    schedule 05.03.2014
comment
Есть ли способ применить формулу ко всему столбцу независимо от того, сколько строк в столбце, не копируя его, как вы предложили? Я делаю макрос записи, и длина столбца будет каждый раз отличаться - person Acidon; 05.03.2014
comment
Конечно. Формулы условного форматирования не меняются. Копирование - не лучший способ сказать это на самом деле. В любом случае вам просто нужно указать диапазон, к которому применяются формулы, в поле «Применимо к» рядом с каждой формулой в диалоговом окне CF. Смотрите мою правку. - person Doug Glancy; 05.03.2014
comment
Спасибо, это именно то, что я искал, ты лучший! - person Acidon; 05.03.2014