Подтвердить что ты не робот

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

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

Проблема состоит в том, что диаграмма вставляет все ячейки #N/A в качестве значений вместо их игнорирования. Я работал над ним, используя именованные динамические диапазоны (то есть Insert > Name > Define), но это крайне неэффективно, так как каждая диаграмма имеет 4 динамических ряда, и я должен сделать 25 из этих диаграмм.

Существуют ли какие-либо другие решения, которые позволяют мне указать диапазон, как обычно, для серии данных, но сообщить диаграмме игнорировать все "# N/A" или пустые ячейки?

4b9b3361

ОТВЕТЫ

Ответ 1

У меня была такая же проблема, используя оператор IF, чтобы вернуть нежелательное значение в значение "", и диаграмма будет работать так, как вы описали.

Однако, когда я использовал #N/A вместо "" (важно отметить, что он без кавычек, как в #N/A, а не "#N/A"), диаграмма игнорировала недопустимые данные. Я даже попытался ввести недопустимый оператор FALSE, и он работал так же, единственная разница была #NAME? возвращена как ошибка в ячейке вместо #N/A. Я буду использовать выраженный IF-оператор, чтобы показать вам, что я имею в виду:

=IF(A1>A2,A3,"")  
---> Returned "" into cell when statement is FALSE and plotted on chart 
     (this is unwanted as you described)

=IF(A1>A2,A3,"#N/A")  
---> Returned #N/A as text when statement is FALSE and plotted on chart 
     (this is also unwanted as you described)

=IF(A1>A2,A3,#N/A)  
---> Returned #N/A as Error when statement is FALSE and does not plot on chart (Ideal)

=IF(A1>A2,A3,a)  
---> Returned #NAME? as Error when statement is FALSE and does not plot on chart 
    (Ideal, and this is because any letter without quotations is not a valid statement)

Ответ 2

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

Chart with #N/A values as null

Вам предоставляется возможность оставить это как null (пробел) или заставить его использовать нулевое значение. Но ни один из них не изменяет размер RANGE, на который указывают данные ряда диаграмм. Из того, что я собираю, ни один из них не подходит.

Chart hidden/empty cell options

Если вы скрываете всю строку/столбец, где есть данные # N/A, диаграмма должна полностью игнорировать их. Вы можете сделать это вручную, щелкнув правой кнопкой мыши | скрыть строку или с помощью таблицы AutoFilter. Я думаю, что это то, чего вы хотите достичь.

Chart with hidden source data rows ignores hidden data

Ответ 3

Обратите внимание, что при построении линейной диаграммы использование = NA() (выход # N/A), чтобы избежать отображения не существующих значений, будет работать только для концов каждой серии - первого и последнего значений. Любые # N/A между двумя другими значениями будут игнорироваться и перекрываться.

Example Plot Here

Ответ 4

Вы правы, что пробелы "или строка" # N/A" действительно интерпретируются как имеющие значения в excel. Вам нужно использовать функцию NA().

Ответ 5

Если у вас есть столбец x и y, который вы хотите рассеять, но не все ячейки в одном из столбцов заполнены значимыми значениями (т.е. некоторые из них имеют #DIV/0!), тогда вставьте новый столбец рядом с столбцом оскорбления и введите =IFERROR(A2, #N/A), где A2 - значение в столбце "Нарушение".

Это вернет #N/A, если есть #DIV/0! и в противном случае вернет хорошее значение. Теперь сделайте свой сюжет с новым столбцом, а Excel игнорирует значение #N/A и не будет отображать их как нули.

Важно: не выводить "#N/A" в формулу, просто выводить #N/A.

Ответ 6

Вы можете использовать функцию "= IF (ISERROR (A1); 0; A1)", это будет показывать ноль, если ячейка A1 содержит ошибку или реальное значение, если это не так.

Ответ 7

просто хотел поместить мои 2cents в эту проблему...

У меня была аналогичная потребность, когда я вытягивал данные из другой таблицы через INDEX/MATCH, и было трудно отличить реальное значение 0 от значения 0 из-за отсутствия соответствия (например, для столбчатой ​​диаграммы, которая показывает прогресс ценностей за 12 месяцев, и где мы находимся только в феврале, но остальная часть данных по месяцам пока недоступна, и диаграмма столбцов все еще показывала 0 всюду за Мар до декабря)

То, что я закончил, - создать серию new и построить эту новую серию на графике в виде линейной диаграммы, а затем я спрятал линейную диаграмму, выбрав отобразить строку в параметрах, и я поместил метки данных сверху, формула для значений для этой новой серии была примерно такой:

= IF (LEN ([@[столбец1]]) = 0, NA(), [@[столбец1]])

Я использовал LEN в качестве проверки, потому что ISEMPTY/ISBLANK не работал, потому что результат INDEX/MATCH всегда возвращал нечто, отличное от пробела, даже если я поставил "после IFERROR...

На линейной диаграмме значение ошибки NA() делает это так, что значение не отображается... так что это сработало для меня...

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

Ответ 8

Одно из решений заключается в том, что в диаграмме/графике не отображаются скрытые строки.

Вы можете протестировать эти функции: 1) щелкните правой кнопкой мыши на строке номер 2) нажмите на кнопку hide.

Для этого автоматически, это простой код:

For Each r In worksheet.Range("A1:A200")
   If r.Value = "" Then 
      r.EntireRow.Hidden = True 
   Else: 
      r.EntireRow.Hidden = False
Next

Ответ 9

Существует еще одно гораздо менее известное решение, которое имеет преимущество в некоторых местах. Используйте #NULL! ошибка, которая не строится и не создает нулевую точку для графика.

Таким образом, ваш столбец будет заполнен следующей конструкцией. Предположим, что реальные данные в столбце A и что построенная версия находится в столбце C.

= If (Isnumber (A2), A2, A2 A3)

Это пространство является малоизвестным оператором для создания пересечения двух адресов или диапазонов ячеек. Если нет пересечения, то он выдает сообщение #NULL!

Клетки, содержащие #NULL! пропускаются для целей печати. ​​

Помимо (для Microsoft) просто нужно заставить уравнения математики игнорировать такие ячейки каким-то образом?

Ответ 10

Это то, что я нашел, когда я составлял только 3 ячейки из каждых 4 столбцов, собранных вместе. В моей диаграмме есть объединенная ячейка с датой, которая является моей осью x. Проблема: BC26-BE27 рисуют как ZERO на моей диаграмме. введите здесь описание изображения

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

Это сработало для меня.

Ответ 11

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

=IF(A1>A2,A3,#N/A) позволяет построить график, но корреляция X и Y не выполняется.

Я решил это =IF(A1>A2,A3,FALSE)

Тогда FALSE может быть removed с использованием условного форматирования или других трюков

Ответ 12

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

Ответ 13

Если вы используете PowerPivot и PivotChart, вы исключите несуществующие строки.

Ответ 14

Я нашел способ сделать это.

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

Ответ 15

Лучший способ - использовать Пустой

Dim i as Integer
For i = 1 to 1000
    If CPT_DB.Cells(i, 1) > 100 Then
       CPT_DB.Cells(i, 2) = CPT_DB.Cells(i, 1)
    Else
       CPT_DB.Cells(i, 2) = Empty //**********************
    End If
Next i

Ответ 16

У меня была та же проблема с появлением полной строки.

В конце моих данных было только # N/A.

Когда я изменил тип диаграммы из сложенной строки на строка, то конец строки был удален из диаграммы.

Это не сработало, если # N/A находился посредине данных, только когда он находился в конечных данных.

Ответ 17

Выберите метки над полосой. Формат данных меток. Вместо выбора "VALUE" (отмените клик). ВЫБРАТЬ Значение из ячеек. Выберите значение. Используйте следующий оператор: if (cellvalue = "", "", cellvalue) где cellvalue - это то, что вычисление находится в ячейке.

Ответ 18

Хотя это старый пост, я недавно натолкнулся на него, когда искал решение той же проблемы. Хотя приведенные выше решения не позволяют диаграммам выводить данные (когда исходные ячейки # N/A или сделаны пустыми), это не решает проблему, когда сами метки данных диаграммы все еще показывают нулевую метку.

Я искал, искал и почти сдался, когда наткнулся на решение, размещенное онлайн @https://www.extendoffice.com/documents/excel/2031-excel-hide-zero-data-labels.html

Это работало, понравилось очарование. Прикреплено изображение, показывающее пример того, как данные (метки), отображаемые ранее, Диаграмма № 1, и как они отображаются с использованием этой подсказки № 2.

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

Ответ 19

Мне нужна помощь. Если ячейка пуста, я хочу скрыть связанный с ней график. Для этого есть код VBA?

Ответ 20

У меня была такая же проблема.

Существует разница между Bar chart и Bar chart с Stacked Bar chart

Так как есть разница между Line chart и Stacked Line chart.

С накоплением, не будет игнорировать 0 или пустые значения, но покажет совокупное значение в соответствии с другими легендами.

Просто щелкните правой кнопкой мыши по графику, выберите " Change Chart Type и выберите диаграмму без суммирования.