Как изменить цвет ячейки в зависимости от того, являются ли предыдущие ячейки пустыми в Excel?

Question

У меня есть электронная таблица с именами в столбце А. Я сохраняю посещаемость, помещая "X" в каждом последующем столбце, когда человек присутствует (неделя за неделей).

Я бы хотел, чтобы Excel автоматически изменил диапазон ячеек на КРАСНЫЙ, когда ячейка плюс две предшествующие ячейки пустые, эффективно показывая красный цвет, когда кто-то отсутствовал три недели подряд.

Как я могу сделать это с помощью Excel 2010?

Answer 1

Как говорит Чаф в своем комментарии, вы можете сделать это с помощью условного форматирования. На самом деле создание необходимой формулы довольно сложно, поскольку Excel не позволяет легко ссылаться на ячейку, которая должна быть отформатирована, для прямой ссылки. Вот решение, которое решает эту проблему, хотя я уверен, что оно далеко не самое элегантное из возможных:

=3=COUNTBLANK(INDIRECT(CHAR(COLUMN()+62)&ROW()&":"&CHAR(COLUMN()+64)&ROW()))

Идея здесь состоит в том, чтобы создать строку, содержащую интересующий диапазон (ячейку, которую нужно отформатировать, и два соседних элемента слева), используя ROW () и COLUMN (), которые INDIRECT () затем делает ссылкой на этот диапазон. Надеемся, что функция COUNTBLANK () очевидна.

РЕДАКТИРОВАТЬ: Как отмечено в комментариях ниже, в вашем запросе есть некоторая двусмысленность. Приведенная выше формула должна работать, если вы хотите начать выделение с третьего отсутствия. То есть при прогоне ровно 3 пропусков будет выделен только третий. Возможно, вместо этого вам нужно выделить всю совокупность отсутствий длины 3 или больше.

Это даже менее красиво, но мое решение, безусловно, может быть расширено для этого. Выше мы проверяем, является ли ячейка последней в серии из трех. Мы также можем проверить, находится ли он в середине или в начале такого прогона, а затем ИЛИ все вместе:

=OR(  
    3=COUNTBLANK(INDIRECT(CHAR(COLUMN()+62)&ROW()&":"&CHAR(COLUMN()+64)&ROW())),
    3=COUNTBLANK(INDIRECT(CHAR(COLUMN()+63)&ROW()&":"&CHAR(COLUMN()+65)&ROW())),
    3=COUNTBLANK(INDIRECT(CHAR(COLUMN()+64)&ROW()&":"&CHAR(COLUMN()+66)&ROW())),
   )

Answer 2

Я не смог получить проверки типа COUNTBLANK или IF(OR({Cell} = "x")) для меня, поэтому я просто сделал это:

1. Начиная с третьего столбца дат посещаемости первого студента
2. Нажмите "Условное форматирование" -> «Новое правило ...»
3. Выберите «Использовать формулу, чтобы определить, какие ячейки форматировать»
4. Введите (при условии, что участники начинаются со строки 1, а даты начинаются со столбца B) =COUNTBLANK(B1:D1)>2 в поле « Формат значений, где эта формула верна »
5. Нажмите «Форматировать ...»
6. Перейдите на вкладку "Заполнить", выберите красный, нажмите ОК
7. Нажмите ОК.

Затем вам просто нужно нажать на перекрестие в правом нижнем углу ячейки и скопировать форматирование по всей строке. Снова нажмите перекрестие и скопируйте форматирование в ряд (ы) ниже, затем повторите для этих строк (по какой-то причине я не смог скопировать форматирование для этого условия в массовый диапазон ячеек)