Не показывать #N/A в ячейке Excel, но и не рассматривать его как значение 0 на диаграмме

У меня есть столбец, содержимое которого зависит от другого столбца:

=IF(B8<>"";B8/($J$2 * $J$2);"")

Этот столбец представляет собой линейные данные для диаграммы. Он отлично работает, когда выполняется условие IF. Однако любое значение ELSE "" рассматривается как 0 на диаграмме, и линия падает вниз.

Альтернативой является использование

=IF(B8<>"";B8/($J$2 * $J$2);NA())

Но тогда это выглядит уродливо в электронной таблице с множеством #N/A в столбце.

Любое решение, кроме размещения условного форматирования сверху, которое делает цвет шрифта белым, когда значение равно #N/A?


excel excel-formula excel-charts
person Dee J. Doena    schedule 23.06.2020    source источник
comment
попробуйте эту формулу =IF(AND(B8<>"",$J$2 <>0),B8/($J$2 * $J$2),"")   -  person Mo Khalefa    schedule 23.06.2020
comment
@MoKhalefa Это не условие IF вызывает проблему, а результирующее "" для случая else, которое затем вызывает проблемы на диаграмме. Я уточнил вопрос по этой части.   -  person Dee J. Doena    schedule 23.06.2020
comment
Вы имеете в виду, что "" или 0 не разрешены?   -  person Mo Khalefa    schedule 23.06.2020
comment
Я думаю, что предложенное вами условное форматирование - это то, что вам нужно.   -  person Ron Rosenfeld    schedule 23.06.2020

Ответы (1)


arrow_upward
1
arrow_downward

Вы можете построить график с именованным диапазоном, который будет оценивать от 0 до #NA

Если данные, которые вы рисуете, находятся в C3:C8:

  1. Создайте имя для этого диапазона, которое оценивает 0 как #NA (я назвал Range PLOT, но вы можете дать ему любое имя): =IF(Sheet1!$C$3:$C$8=0,NA(),Sheet1!$C$3:$C$8)
  2. Используйте именованный диапазон на диаграмме: =SERIES(,,Book1!PLOT,1)

Это позволит вам сохранить ELSE-ячеек как "" на вашем листе, в то же время отображая их как #NA на диаграмме и, следовательно, не сворачивая строку до нуля.

NB: смотрите здесь, чтобы узнать, как использовать именованные диапазоны в диаграммах, в частности:

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

=SERIES(Лист1!ИмяСерии,Лист1!ЗначенияX,Лист1!ЗначенияY,1)

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

person Prebsus    schedule 23.06.2020
comment
Спасибо, это сработало! Это заняло у меня немного больше времени, потому что я сделал классическую ошибку копирования и вставки, и по какой-то странной причине MS решила перевести имена функций в каждую местность. Итак, NA() выдало ошибку, потому что это должно было быть NV() в моей локальной версии Excel. Спасибо за вашу помощь! :-) - person Dee J. Doena; 24.06.2020