Условное форматирование в Excel с текущей ячейкой формулы
Что положить в формулу, чтобы определить условное форматирование для ссылки на текущую ячейку? Например, я хочу сделать условное форматирование, если ячейка содержит ошибку (#N/A) и использовать то же правило для всего столбца.
Кажется, что не может найти ссылку на ячейку, для которой выполняется оценка функции. Это вообще возможно?
3 ответа
Предположим, диапазон данных для условного форматирования равен A2:A10.
- Выберите первую ячейку
A2, - Из домашней вкладки нажмите "Условное форматирование", "Управление правилами", "Новое правило".
- Используйте формулу для определения ячейки для форматирования. В поле "Значения формата", где эта формула верна, введите
=ISNA($A2), - Нажмите "Формат", чтобы установить форматирование ячейки, затем нажмите "ОК".
- В диспетчере правил условного форматирования измените диапазон в разделе "Применяется для установки".
$A2:$A10, - Выберите Применить, затем ОК.
Использование относительных ссылок для ссылки на текущую ячейку
В формуле условного форматирования вы можете ссылаться на текущую ячейку, используя относительную форму ее обычного адреса. Например, если вы хотите отформатировать ячейку B2, вы можете использовать формулу, например:
=ISNA(B2)
Потому что вы используете относительную ссылку (B2 скорее, чем $B$2), когда вы копируете его в другую ячейку, формула корректируется относительно новой ячейки. Поэтому, если вы используете средство рисования формата, чтобы скопировать условный формат в ячейку C3 (или просто скопировать туда всю B2), затем проверьте C3 в диспетчере правил условного форматирования, и вы увидите, что формула автоматически обновилась до
=ISNA(C3)
Этот принцип также применим к диапазонам, но его немного сложнее понять. Для диапазона формула вводится относительно верхней левой ячейки, но интерпретируется относительно каждой ячейки по очереди. Так что если вы выбираете диапазон ячеек от B2 до D4 и применяете формулу =ISNA(B2)любая ячейка в диапазоне будет отформатирована, если она содержит #N/A, а не только B2.
Относительные ссылки на другие ячейки
То же самое относится и к другим относительным ссылкам: если ваша формула условного форматирования имеет относительную ссылку на ячейку справа от ячейки, которую вы форматируете, и вы копируете этот формат в другое место, тогда формат новой ячейки будет зависеть от значения справа от новой клетки.
Абсолютные ссылки
Вы также можете использовать абсолютные ссылки ($A$1 стиль), если вы хотите, чтобы формат множества ячеек зависел от значения одной ячейки. Абсолютные ссылки не меняются, когда вы применяете условный формат к другим ячейкам.
Полуотносительные ссылки
Использование сочетания абсолютных и относительных ссылок может быть очень полезным для форматирования целых строк или столбцов на основе значения одного элемента. Например, чтобы выделить всю строку данных в диапазоне от A2 до E10, если значение в первом столбце этой строки было #N/AВы можете выбрать этот диапазон и применить формулу условного форматирования:
=ISNA($A2)
Абсолютная часть формулы ($A) означает, что он всегда смотрит на первый столбец, но относительную часть (2) означает, что он использует значение из текущей строки.
Так же, =ISNA(A$2) может использоваться для форматирования целых столбцов диапазона от А2 до Е10, основываясь на значении верхней строки.
Вы можете использовать приведенное ниже в качестве правила условного форматирования:
=IFERROR(A1,"error")="error"
+ Изменить A1 к вашей первой ячейке, затем примените ее ко всему столбцу.