Excel Multi Условное Форматирование Вопросы

Question

Мне нужна формула, которая будет смотреть на текст в одной ячейке, сравнивать его с тремя другими столбцами и выделять его указанным цветом.

Вот пример:

Данные

• Ячейка A2 содержит "Это образец текста в ячейке"
• Ячейка B2 содержит "образец"
• Ячейка C2 содержит "текст"
• Ячейка D2 содержит "ячейку"

Желаемая формула

1. Если A2 содержит B2 ИЛИ C2 ИЛИ D2, выделите желтый
2. Если A2 содержит B2 AND C2, OR содержит B2 AND D2, OR содержит C2 AND D2, затем выделите синим цветом
3. Если A2 содержит B2, C2 и D2, выделите зеленым

Что я пробовал

• Я пытался использовать =SUM(COUNTIF(A2,"*"&B2&"*")) . Это работает, как требуется для # 1, но требует, чтобы я создал отдельные правила форматирования, одно для B2, C2 и D2. Так что это не очень элегантно, и я не мог понять правильный синтаксис, чтобы он работал для # 2.

• Для # 3 я пытался использовать =SUM(COUNTIF(A2,"*"&B2:D2&"*")) но это возвращает ложные срабатывания и отображается, когда только одна ячейка соответствует критериям.

• Я также попытался =SUMPRODUCT(--(A2=things))>0 (things = именованный массив), как подробно описано здесь: https://exceljet.net/formula/cell-equals-one-of-many-things Но это не работает, так как требует, чтобы я создал уникальные имена списков для каждой строки.

• И я также попытался =IF(OR(A2="sample",A2="text",A2="cell"),"True","False") а затем применить условный формат для выходных данных True, но это не работает, так как зависит от жестко закодированных значений.

Формула должна иметь возможность использовать динамические значения (номер ячейки A2, B2, C2 и т.д.), Она не может зависеть от каких-либо жестких значений. Мне нужно будет использовать эту формулу для сотен строк, с различными значениями, включенными в каждую ячейку. Таким образом, формула должна работать одинаково для ячеек A3, B3, C3 и D3, и мне не нужно ничего менять. Это должна быть прямая копия и вставка в оцениваемый столбец, в этом примере это будет все в столбце A.

Я новичок здесь, поэтому я прошу прощения, если на этот вопрос ответили в другом месте, я просто не могу найти его. Если это так, пожалуйста, направьте меня к ответу!

Answer 1

Это должно сделать трюк:

=SUMPRODUCT(-- ISNUMBER(SEARCH(B2:D2,$A$2)))

Это похоже на вашу третью попытку, но -

• вам не нужно использовать именованный диапазон. Вы можете просто использовать B2:D2 , который будет работать, как вы надеетесь, когда вы скопируете его в следующий ряд.
• вам нужно проверить, содержит ли строка тестовые значения, а не равны ли они.

SEARCH возвращает начальную точку одной строки внутри другой, ISNUMBER преобразует ее в true или false в зависимости от того, была ли найдена строка, и -- преобразует логическое значение в 0 или 1. Передача диапазона значений в SEARCH означает, что выходные данные представляют собой массив из 0 и 1, а не одно значение.

Использование SUMPRODUCT вместо SUM позволяет сложить эти значения без необходимости использовать формулу массива в Excel. Конечным результатом является число от 0 до 3, в зависимости от того, сколько сравнений выполнено успешно, и вы можете проверить это, чтобы использовать условное форматирование.

Обратите внимание, что если какая-либо из ваших ячеек сравнения будет пустой, сравнение будет выполнено успешно (так как все строки содержат пустую строку).

(На основании инструкций на https://exceljet.net/formula/cell-contains-one-of-many-things)