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

У меня есть рабочая книга с двумя листами. Я хочу отформатировать цвет фона ячеек в первом столбце листа 1 на основе значений во втором столбце листа 2.

Например, если значение строки 6 столбца 2 листа 2 равно, скажем, 4, то я хочу, чтобы цвет фона листа 1, строки 4 столбца 1 был зеленым. Если ни одно из значений в столбце 2 листа 2 не ссылается на конкретную строку листа 1, я хотел бы оставить его без цвета. Нет запрета на то, чтобы одно и то же значение появлялось несколько раз во втором столбце листа 2. Бонусы, если вы подскажете мне, как снять установку цвета, если последнее значение в листе 2, указывающее на строку в листе 1, будет удалено.

Я уверен, что для мастеров Excel это может быть тривиально, но у меня редко бывают случаи использования excel и, конечно, у меня нет времени, чтобы стать черным поясом в нем. Может ли кто-нибудь дать мне совет, подсказку или быструю формулу для этого? Если для этого потребуется сложный код на VB, то оно того не стоит.

Спасибо!

Программирование
excel
16-го января 2009 в 5:31
26 просмотров
Решение

Вот как я сделал это в Excel 2003 с помощью условного форматирования.

Чтобы применить условное форматирование к Sheet1, используя значения из Sheet2, необходимо зеркально отразить значения в Sheet1.

Создание зеркального отображения Листа2, столбец B в Листе1#.

  1. Перейдите в Sheet1.

  2. Вставьте новый столбец, щелкнув правой кнопкой мыши заголовок столбца A' и выбрав "Вставить".

  3. Введите в A1 следующую формулу:

    =IF(ISBLANK(Sheet2!B1),"",Sheet2!B1)

  4. Скопируйте A1, щелкнув его правой кнопкой мыши и выбрав "Копировать".

  5. Вставьте формулу в столбец A, щелкнув правой кнопкой мыши его заголовок и выбрав "Paste".

Sheet1, столбец A теперь должен в точности повторять значения в Sheet2, столбец B.

(Примечание: если вам не нравится значение в столбце A, его можно разместить в столбце Z или в любом другом месте).

Применение условного форматирования

  1. Оставайтесь на Sheet1.
  2. Выберите столбец B, щелкнув левой кнопкой мыши его заголовок.
  3. Выберите пункт меню Формат >Условное форматирование...
  4. Измените Условие 1 на "Формула есть" и введите эту формулу:
<code>=MATCH(B1,$A:$A,0)</code>
  1. Нажмите кнопку Формат... и выберите зеленый фон.

Теперь вы должны увидеть зеленый фон, примененный к соответствующим ячейкам в Sheet1.

Скрытие зеркального столбца

  1. Оставайтесь на Sheet1.
  2. Щелкните правой кнопкой мыши на заголовке столбца A и выберите "Скрыть".

Это должно автоматически обновлять Sheet1 при каждом изменении чего-либо в Sheet2.

mskfisher
Редактировал ответ 16-го января 2009 в 9:13
Комментарии (2)

Я использую Excel 2003 -

Проблема с использованием условного форматирования здесь заключается в том, что вы не можете ссылаться на другой рабочий лист или рабочую книгу в ваших условиях. Что вы можете сделать, так это установить некоторый столбец на листе 1 равным соответствующему столбцу на листе 2 (в вашем примере =Sheet2!B6). Я использовал столбец F в моем примере ниже. Затем вы можете использовать условное форматирование. Выберите ячейку на Листе 1, строка , столбец 1, а затем перейдите в меню условного форматирования. Выберите "Формула есть" из выпадающего списка и установите условие "=$F$6=4". Нажмите на кнопку "Формат", а затем выберите вкладку "Узоры". Выберите нужный вам цвет и готово.

Вы можете использовать инструмент рисования формата для применения условного форматирования к другим ячейкам, но имейте в виду, что по умолчанию Excel использует абсолютные ссылки в условиях. Если вы хотите, чтобы они были относительными, вам нужно убрать знаки доллара из условия.

К ячейке можно применить до 3 условий (используйте кнопку "Добавить >>" в нижней части диалога "Условное форматирование"), поэтому если последняя строка фиксирована (например, вы знаете, что это всегда будет строка 10), вы можете использовать ее в качестве условия для установки цвета фона "нет". Предположим, что последнее значение, о котором вы заботитесь, находится в строке 10, тогда (все еще предполагая, что вы установили столбец F на листе 1 в соответствующие ячейки на листе 2) установите первое условие в Formula Is =$F$10="", а шаблон в None. Сделайте это условие первым, и оно отменит все последующие противоречивые утверждения.

Комментарии (2)

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

Комментарии (1)