Создание диаграммы в Excel, которая игнорирует #N/A или пустые ячейки
Я пытаюсь создать диаграмму с динамическим рядом данных. Каждая серия на графике происходит из абсолютного диапазона, но только определенное количество этого диапазона может иметь данные, а остальные будут #N/A,
Проблема в том, что график придерживается всех #N/A ячейки в виде значений, а не игнорируя их. Я обошел его, используя именованные динамические диапазоны (например, Insert > Name > Define), но это крайне неэффективно, поскольку каждая диаграмма имеет 4 динамических ряда, и я должен сделать 25 из этих диаграмм.
Существуют ли другие решения, которые позволяют мне, как обычно, указывать диапазон для ряда данных, но при этом указывать диаграмме игнорировать все "#N/A" или пустые ячейки?
20 ответов
У меня возникла та же проблема с использованием оператора 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)
Когда вы отсылаете диаграмму к определенному диапазону, она строит все точки в этом диапазоне, интерпретируя (ради построения графика) ошибки и пропуски как нулевые значения.
Вам предоставляется возможность оставить это значение пустым (пробел) или принудительно установить нулевое значение. Но ни один из них не изменяет ДИАПАЗОН, на который указывают данные ряда диаграммы. Из того, что я понимаю, ни один из них не подходит.
Если вы скрываете всю строку / столбец, где существуют данные #N/A, диаграмма должна полностью их игнорировать. Вы можете сделать это вручную, щелкнув правой кнопкой мыши | скрыть строку или используя таблицу автофильтра. Я думаю, что это то, что вы хотите достичь.
Обратите внимание, что при построении линейного графика использование =NA() (выходной № N/A), чтобы избежать отображения несуществующих значений, будет работать только для концов каждой серии, первого и последнего значений. Любые #N/A между двумя другими значениями будут игнорироваться и соединяться.
Вы правы, что пробелы "" или строка "#N/A" действительно интерпретируются как имеющие значения в excel. Вам нужно использовать функцию NA().
Если у вас есть x а также y столбец, который вы хотите разбросать, но не все ячейки в одном из столбцов заполнены значимыми значениями (т.е. некоторые из них имеют #DIV/0!), затем вставьте новый столбец рядом с ошибочным столбцом и введите =IFERROR(A2, #N/A)где A2 - значение в ошибочном столбце.
Это вернется #N/A если есть #DIV/0! и вернет хорошее значение в противном случае. Теперь создайте свой график с новым столбцом, а Excel игнорирует #N/A значение и не будет изображать их как нули.
Важно: не выводить "#N/A" в формуле просто выведите #N/A,
У меня была такая же проблема.
Есть разница между Bar chart и Stacked Bar chart
Как есть разница между Line chart и Stacked Line chart,
С накоплением, не будет игнорировать 0 или пустые значения, но покажет совокупное значение в соответствии с другими легендами.
Просто щелкните правой кнопкой мыши на графике, нажмите Change Chart Type и выберите диаграмму без суммирования.
Вы можете использовать функцию "=IF(ISERROR(A1);0;A1)", которая будет показывать ноль, если ячейка A1 содержит ошибку или реальное значение, если нет.
Просто хотел поставить 2cents об этой проблеме...
У меня была похожая потребность, когда я извлекал данные из другой таблицы с помощью INDEX/MATCH, и было трудно отличить реальное значение 0 от значения 0 из-за отсутствия соответствия (например, для столбчатой диаграммы, которая показывает прогресс значения за 12 месяцев и где мы находимся только в феврале, но данные за остальные месяцы еще не доступны, и столбчатая диаграмма все еще показала 0 по всему миру с марта по декабрь)
Я закончил тем, что создал новую серию и нарисовал эту новую серию на графике в виде линейной диаграммы, а затем я спрятал линейную диаграмму, выбрав не отображать линию в опциях, и поставил метки данных сверху, формулу для значений для этой новой серии было что-то вроде:
= IF (LEN ([@ [column1]]) = 0,NA(), [@ [column1]])
Я использовал LEN в качестве проверки, потому что ISEMPTY/ISBLANK не работал, потому что результат INDEX / MATCH всегда возвращал что-то отличное от пробела, даже если я поставил "" после IFERROR...
На линейном графике значение ошибки NA() делает так, чтобы значение не отображалось... так что у меня получилось...
Я думаю, что немного сложно выполнить эту процедуру без картинок, но я надеюсь, что она рисует какую-то картинку, чтобы вы могли использовать обходной путь, если у вас есть аналогичный случай, как мой
Это то, что я обнаружил, когда строил только 3 ячейки из каждых 4 столбцов, объединенных вместе. Моя диаграмма имеет объединенную ячейку с датой, которая является моей осью X. Проблема: BC26-BE27 изображены на моем графике как ноль. введите описание изображения здесь
Я нажимаю на фильтр на боковой части графика и обнаруживаю, где он показывает все столбцы, для которых построены точки данных. Я снял флажки, которые не имеют значений. введите описание изображения здесь
Это сработало для меня.
Одним из решений является то, что диаграмма / график не показывает скрытые строки.
Вы можете проверить эту функцию, выполнив: 1) щелкните правой кнопкой мыши на строке № 2) нажмите на скрыть.
Для того, чтобы сделать это автоматически, это простой код:
For Each r In worksheet.Range("A1:A200")
If r.Value = "" Then
r.EntireRow.Hidden = True
Else:
r.EntireRow.Hidden = False
Next
Есть еще одно гораздо менее известное решение, которое в некоторых местах имеет свои достоинства. Используйте #NULL! ошибка, которая не строит и не создает нулевую точку для графика.
Таким образом, ваш столбец графика заполнен следующей конструкцией. Предположим, что реальные данные в столбце A и что построенная версия находится в столбце C.
= Если (Isnumber(A2),A2,A2 A3)
Это пространство - малоизвестный оператор для пересечения адресов или диапазонов двух ячеек. Если пересечения нет, то выдается сообщение #NULL!
Клетки, содержащие #NULL! пропускаются для целей построения.
Помимо (для Microsoft) просто нужно получить математические уравнения, чтобы как-то игнорировать такие ячейки?
У меня была похожая проблема с использованием графика X/Y, но затем мне также нужно было рассчитать корреляционную функцию для двух наборов данных.
=IF(A1>A2,A3,#N/A) позволяет построить график, но соотношение X & Y выходит из строя.
Я решил это=IF(A1>A2,A3,FALSE)
Ложь может быть removed используя условное форматирование или другие приемы
Хотя это старый пост, я недавно натолкнулся на него, когда искал решение той же проблемы. Хотя вышеприведенные решения не позволяют диаграммам выводить данные (когда исходные ячейки #N/A или сделаны пустыми), это не решает проблему, когда сами метки данных диаграммы все еще показывают нулевую метку.
Я искал, искал и почти сдался, когда наткнулся на решение, размещенное онлайн @ https://www.extendoffice.com/documents/excel/2031-excel-hide-zero-data-labels.html
Это работало, понравилось очарование. Прикреплено изображение, показывающее пример того, как данные (метки), отображаемые ранее, Диаграмма № 1, и как они отображаются с использованием этой подсказки № 2.
Если вы используете PowerPivot и PivotChart, вы исключите несуществующие строки.
Выберите метки над полосой. Формат данных меток. Вместо выбора "VALUE" (отмените клик). ВЫБРАТЬ Значение из ячеек. Выберите значение. Используйте следующий оператор: if(cellvalue="","",cellvalue) где cellvalue - это то, что вычисление находится в ячейке.
У меня была такая же проблема с появлением полной строки.
Конец моих данных был только #N/A.
Когда я изменил тип диаграммы со сложенной линии на линию, тогда конец линии был удален с диаграммы.
Это не сработало, если # N / A было в середине данных, только когда оно было в конечных данных.
Если вы установите вертикальную шкалу на диаграмме (используя ось форматирования) с 0,0001 (скажем), то значение, которое Excel считает нулевым, не будет отображаться. Ваша ось на графике будет выглядеть так, как будто она движется от нуля вверх.
Я нашел следующее решение:
Диаграммы Excel автоматически настраиваются на правильные концы оси, если все ячейки ряда дат, которые не должны быть включены в временные рамки оси, содержат
"". Итак, вы можете создать вспомогательный столбец и написать что-то вроде этого в ячейках вспомогательного столбца серии Date:
Helper Column Datecell = If( iserror (ValueCell);""; Datecell)
С другой стороны, если вы хотите изменить размер диаграммы в соответствии с заданным периодом времени, вы можете написать что-то вроде этого в ячейках серии дат вспомогательного столбца:
Helper Column Datecell = IF( OR(ISERROR(ValueCell); DateCell>DateInput) ;"";DateCell)
DateInput - это ячейка, в которую вы вводите дату для изменения размера диаграммы.
Здесь есть еще одна тема:
Что касается того, что если вы сделаете вспомогательный столбец для ячейки серии значений, дающей
NA() Errorряд значений также адаптируется к вашему ряду: вы даже можете сделать свои ячейки ряда значений зависимыми от заданной входной ячейки. Думаю, это тоже нужно.
Helper Column Value Cell: if( condition that date cell is too big or bigger than a given input cell; NA(); ValueCell)
Я нашел способ сделать это.
Вы можете сделать график рассеяния x,y. он будет игнорировать нулевые записи (т.е. строки)
Лучший способ это использовать Пусто
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