☰

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

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
Как изменить цвет ячейки в Excel в зависимости от того, пустые ли предыдущие ячейки?

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

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

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

решение1

Как говорит chuff в своем комментарии, вы можете сделать это с помощью условного форматирования. На самом деле, создание необходимой формулы является довольно сложным, поскольку 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())),
   )

решение2

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

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

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

Связанный контент