Условно отформатируйте ячейки на основе совпадения на другом листе

Question

У меня есть таблица Excel с 2 листами. Первый - это просто строка заголовка и один столбец имен элементов. Второй - это список групп элементов со строкой заголовка и заголовком в крайнем левом столбце, где каждая последующая строка является тем или иным элементом другого листа:

Sheet1:            Sheet2:
+-------+--+--+    +-------+-------+-------+-------+-------+
| Item  |  |  |    | Group | Item1 | Item2 | Item3 | ...
+-------+--+--+    +-------+-------+-------+-------+-------+
| Shirt |  |  |    | A     | Shirt | Hat   | Tie   |
+-------+--+--+    +-------+-------+-------+-------+-------+
| Hat   |  |  |    | B     | Socks | Shirt | SHOES |
+-------+--+--+    +-------+-------+-------+-------+-------+
| Socks |  |  |    | C     | Hat   | Socks |       |
+-------+--+--+    +-------+-------+-------+-------+-------+
| Tie   |  |  |    | D     | Tie   | Tie   | Socks |
+-------+--+--+    +-------+-------+-------+-------+-------+
| ...   |  |  |
+-------+--+--+

Я хотел бы условно отформатировать все ячейки в "Sheet2" так, чтобы любое значение, которое не соответствует значению в первом столбце "Sheet1", было отмечено красным фоном; те, которые делают, отмечены зеленым фоном. Таким образом, все ячейки в этом примере, начинающиеся с B2, будут зелеными, кроме значения "ОБУВЬ". Значение под которым ничего не введено, поэтому не будет форматироваться вообще.

Правило форматирования для зеленого цвета, которое я пробовал:

=AND(NOT(ISBLANK(B2)), COUNTIF(Sheet1!$A2:$A1000,B2)>0)

Для красного примерно так же:

=AND(NOT(ISBLANK(B2)), COUNTIF(Sheet1!$A2:$A1000,B2)<1)

Оба правила "применяются" к некоторому произвольному диапазону (я бы хотел, чтобы он применялся ко всему листу, за исключением самого верхнего и самого левого ряда / столбца):

=$C$3:$E$10,$C$36:$Q$50,$E$11,$C$11,$C$2,$E$2:$Q$2,$C$12:$E$35,$F$3:$Q$35

Это полуработы, но результаты непредсказуемы. Некоторые значения выделяются, как я ожидаю, но только для нескольких строк, а другие нет. Возможно, мои диапазоны как-то не в порядке, но я не использую Excel почти так же часто, как раньше. Кто-нибудь может протянуть руку?

Спасибо!

Answer 1

Как отмечает доктор Рейхард, для этого вы хотите использовать условное форматирование . В этом конкретном случае вы хотите иметь три правила:

1. Если ячейка пуста, не меняйте фон
2. Если ячейка соответствует, сделайте фон зеленым
3. Если ячейка не совпадает, сделайте фон красным

_{Извините, мой Excel - японский. Это многоязычный день.}

Для этого нам нужно 3 формулы, которые будут возвращать TRUE или FALSE для каждого из этих условий. Я предполагаю, что ваши данные выглядят следующим образом:

Лист1

Sheet2

Правило № 1

Следующая формула вернет, пустая ячейка или нет. Я выбрал

=ISBLANK(B2)

Обратите внимание, что я выбрал ячейки B2:D5 с относительными ссылками. Будет применена та же формула, изменяющая ссылку на ячейку для каждой ячейки в выбранном диапазоне. Установите цвет фона на белый (или как вам больше нравится), когда это условие истинно.

Правило № 2

Следующая формула вернет, есть ли в списке на листе 1 идеальное совпадение:

=NOT(ISERROR(MATCH(B2,Sheet1!$A:$A,0)))

Правило № 3

Следующая формула будет возвращать, нет ли идеального соответствия в списке на листе 1:

=ISERROR(MATCH(B2,Sheet1!$A:$A,0))

порядок

Правило сверху будет выполнено первым. Таким образом, поскольку все пустые ячейки не совпадают, вам нужно сначала поставить пустое правило. Порядок № 2 и № 3 не имеет значения (они никогда не будут перекрываться).

Answer 2

Похоже, что вам нужно, ячеистое условное форматирование.

Вот ссылка, описывающая, что это такое.

Мои рисунки взяты из португальской версии Excel 2003, но функциональность должна присутствовать и в Office 2010. На самом деле Office 2010 допускает условное форматирование между листами, чего не может 2003, и поэтому я делаю все на одном листе.

Прежде всего, таблица:

Что вы хотите сделать, это сравнить элемент из 2-й таблицы со всеми элементами из 1-й. Итак, вам нужно написать такую функцию:

=OR(EXACT($B$6;E3);EXACT($B$5;E3);EXACT($B$4;E3);EXACT($B$3;E3))

EXACT() выполняет сравнение двух строк текста. Что OR() делает, чтобы стать True если любое логическое условие внутри True .

При этом вы выбираете все ячейки из 2-й таблицы и затем нажимаете «Условное форматирование», как показано на рисунке.

Зная об Office 2010 и его новом интерфейсе ленты, вы должны посмотреть на панели форматирования для этого. Если я помню, это стоит как иконка.

Нажатие на эту иконку вызовет окно, подобное этому:

Там сначала нужно выбрать нужную формулу, а затем вставить формулу, о которой я упоминал ранее. Чтобы все ячейки, которые проверяют условие, были зелеными, просто измените формат. Чтобы сделать красное форматирование, просто используйте NOT(OR(...)) ; это вернет обратное условие, которое вы установили.

Чтобы убедиться, что он не форматирует ячейки, в которых ничего нет, создайте третье условие, в котором формула будет ISBLANK(E3) (E3 - верхний левый угол).

Что-то, что я проверил после написания первоначального проекта, является приоритетом По крайней мере, в моей версии, Условие 1 проверяется перед Условием 2 и так далее. Таким образом, вы должны упорядочить условия таким образом, чтобы они не мешали им самим. Так:

1st condition - =ISBLANK(E3)
2nd condition - =OR(...)
3rd condition - =NOT(OR(...))

Поэтому у вас должно быть окно, подобное этому:

Попробуйте адаптировать это к вашей ситуации. Если я помню, это не так уж отличается от того, что я показываю. Результат должен быть примерно таким: