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

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

Вот пример:

Данные

  • Ячейка 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") и затем применяет условный формат для Истинных выходов, но это не работает, поскольку это зависит от жестко закодированных значений.

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

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

Источник

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)

Источник
Другие вопросы по тегам