☰

Использовать условное форматирование Excel на основе отображения ячейки, а не содержания формулы ячейки

tag-icon • tag-icon microsoft-excel worksheet-function conditional-formatting
Использовать условное форматирование Excel на основе отображения ячейки, а не содержания формулы ячейки

На моем листе Excel есть ячейка, содержащая следующую формулу:

=IF(COUNT(Y11:AA11)>0,SUM(Y11:AA11),#N/A)

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

Проблема в том, что есть много #N/Aотображаемых значений, которые выглядят грязно, поэтому я хочу использовать условное форматирование, чтобы случаи, когда #N/Aотображается, имели белый текст. До сих пор каждая попытка приводила к тому, что каждое значение имело белый текст, независимо от того, является ли отображаемое значение #N/Aчислом. Кто-нибудь знает, как это сделать?

Что я пробовал:

  • Использование условного форматирования с условием формулы =ISERROR($X$4:$X$37), примененным к диапазону $X$4:$X$37. (Все ячейки в диапазоне отформатированы для использования белого текста; допустимые числа и значения ошибок.)

  • Использование условного форматирования с условием формулы =ISNA($X$4:$X$37) (тот же результат).

  • Использование условного форматирования с аффектацией только для дубликатов (отображение числа все еще затронуто)

  • Изменение формулы в ячейках ( X4:X37) на =IF(COUNT(Y11:AA11)>0,SUM(Y11:AA11),"#N/A"), затем условное форматирование этого текста. (Нулевое значение теперь отображается на графике, поскольку "#N/A"является текстом, а #N/Aне был.)

Вот изображение того, что я попробовал:
Менеджер правил условного форматирования

решение1

  • Выберите данные, которые вы хотите отформатировать.
  • перейти на главную страницу - условное форматирование - новое правило
  • выберите «использовать формулу для определения форматируемых ячеек»
  • введите формулу:
    =ISERROR(A2)
    (заменить A2на адрес активной ячейки)
  • выберите нужный вам формат (в примере ниже я использовал желтое выделение, чтобы сделать свой ответ понятнее)

введите описание изображения здесь

решение2

Проблема в абсолютных ссылках в вашей ISNA()функции.

ISNA()являетсяТОЛЬКОвозвращая результат для первой ячейки в используемом диапазоне. Таким образом, если он активирует CF-ing, он сделает это для всех ячеек, к которым применяется правило, потому что независимо от того, к какой ячейке применено правило, оно никогда не будет смотреть ни на что, кроме первой ячейки в этом абсолютном диапазоне.

ISNA()никогда не будет рассматривать другие ячейки в диапазоне, даже если вы разрешите им быть относительными ссылками.

НОэто работает, если вы хотя бы позволите ссылке на строку быть относительной. Это происходит потому, что диапазон, просматриваемый по, ISNA()постоянно меняется, поэтому первая ячейка — это ячейка, которую вы на самом деле хотите просмотреть. Это происходит в каждой новой строке, так что, хотя это выглядит одинаково, в основном, вы фактически изменяете первую ячейку в диапазоне, не видя этого визуально, и поэтому ISNA()каждый раз смотрите на новую первую ячейку.

Если вы внесете изменения и посмотрите на правило CF с выделением ячеек каждый раз в разных строках, вы увидите, что показанное правило изменяет этот диапазон.

(Вы используете неспособность функции работать так, как можно было бы подумать, чтобы на самом деле стать оператором, достигающим вашей цели. Это похоже на желание убрать кучу мусора, убедить людей, что вещи в куче ценны, отвести взгляд, пока они что-то крадут, и в конечном итоге не оставить там никакого мусора. Вы используете неспособность, чтобы достичь стоящей цели.)

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