Лист1

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, будут зелеными, за исключением значения "SHOES". Значение под ним не содержит ничего, поэтому не будет отформатировано вообще.

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

=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

Лист2

Правило №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.

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

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

Нажатие на этот значок вызовет окно, похожее на это:

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

Чтобы убедиться, что он не форматирует ячейки, в которых ничего нет, создайте третье условие, в котором формула будет ЕПУСТО(E3) (где E3 — верхний левый угол).

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

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

Таким образом, у вас должно появиться такое окно:

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