ti-enxame.com

Criando um gráfico no Excel que ignora # N/A ou células em branco

Eu estou tentando criar um gráfico com uma série de dados dinâmicos. Cada série no gráfico vem de um intervalo absoluto, mas apenas uma certa quantidade desse intervalo pode ter dados e o restante será #N/A.

O problema é que o gráfico cola todas as células #N/A em valores, em vez de ignorá-las. Eu trabalhei em torno dele usando intervalos dinâmicos nomeados (ou seja, Inserir> Nome> Definir), mas isso é extremamente ineficiente, pois cada gráfico tem 4 séries dinâmicas, e eu devo fazer 25 desses gráficos.

Existe alguma outra solução que me permita especificar um intervalo, como é normal, para uma série de dados, mas dizer ao gráfico para ignorar todas as células "# N/A" ou em branco?

31
James Skidmore

Eu estava tendo o mesmo problema usando uma instrução IF para retornar um valor indesejado para "", e o gráfico faria o que você descreveu. 

No entanto, quando eu usei #N/A em vez de "" (importante, note que é sem as aspas como em #N/A e não "#N/A"), o gráfico ignorou os dados inválidos. Eu até tentei colocar uma instrução FALSE inválida e ela funcionou da mesma forma, a única diferença foi #NAME? retornado como erro na célula ao invés de #N/A. Usarei uma instrução IF para mostrar o que quero dizer:

=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)
24
Mike Rhodes

Quando você indica o gráfico a um intervalo definido, ele plota todos os pontos desse intervalo, interpretando (por uma questão de plotagem) erros e espaços em branco como valores nulos. 

Chart with #N/A values as null

Você tem a opção de deixar isso como nulo (lacuna) ou forçá-lo ao valor zero. Mas nenhum desses redimensiona o RANGE para o qual os dados da série de gráficos estão apontando. Pelo que eu sei, nenhum destes é adequado.

Chart hidden/empty cell options

Se você ocultar toda a linha/coluna onde existem os dados # N/A, o gráfico deve ignorá-los completamente. Você pode fazer isso manualmente clicando com o botão direito do mouse | ocultar linha ou usando a tabela AutoFiltro. Eu acho que isso é o que você quer realizar. 

Chart with hidden source data rows ignores hidden data

19
David Zemens

Por favor, note que ao traçar um gráfico de linha, usando = NA () (saída # N/A) para evitar plotagem de valores não existentes só funcionará para as extremidades de cada série, primeiro e último valores. Qualquer # N/D entre dois outros valores será ignorado e colocado em ponte.

Example Plot Here

10
sebastian

Você está correto que espaços em branco "" ou uma seqüência de caracteres "# N/A" são realmente interpretados como tendo valores no Excel. Você precisa usar uma função NA ().

4
Tom B

Se você tem uma coluna x e y que deseja espalhar, mas nem todas as células em uma das colunas são preenchidas com valores significativos (ou seja, algumas delas têm #DIV/0!), insira uma nova coluna ao lado da coluna incorreta e tipo =IFERROR(A2, #N/A), onde A2 é o valor na coluna incorreta. 

Isso retornará #N/A se houver um #DIV/0! e retornará o valor bom caso contrário. Agora faça sua plotagem com sua nova coluna e o Excel ignora o valor #N/A e não os plotará como zeros. 

Importante : não imprima "#N/A" na fórmula, apenas imprima #N/A.

3
geodork

Você pode usar a função "= IF (ISERROR (A1); 0; A1)" isso mostrará zero se a célula A1 contiver uma errore ou o valor real se não tiver.

2
Nicolaesse

Há outra solução menos conhecida que tem mérito em alguns lugares. Use o # NULL! erro que não grava e não cria um ponto zero para o gráfico.

Portanto, sua coluna de plotagem é preenchida com a seguinte construção. Suponha dados reais na coluna A e que a versão plotada esteja na coluna C.

= Se (número (A2), A2, A2 A3)

Esse espaço é um operador pouco conhecido para produzir a interseção dos dois endereços ou intervalos de células. Se não houver interseção, ela produzirá a mensagem #NULL!

Células contendo #NULL! são ignorados para fins de plotagem.

Além disso (para a Microsoft) só precisa obter as equações matemáticas para ignorar essas células de alguma forma?

1
BobJordanB

só queria colocar meus 2cents sobre esta questão ...

Eu tive uma necessidade semelhante onde eu estava puxando dados de outra tabela via INDEX/MATCH, e era difícil distinguir entre um valor real de 0 vs. um valor de 0 por causa de não correspondência (por exemplo para um gráfico de colunas que mostra o progresso de valores ao longo dos 12 meses e onde estamos apenas em fevereiro, mas o resto dos dados de meses ainda não está disponível e o gráfico de colunas ainda mostrava 0 em todos os lugares de março a dezembro)

O que acabei fazendo é criar uma série new e traçar essa nova série no gráfico como um gráfico de linhas e então eu escondi o gráfico de linha escolhendo não exibir a linha nas opções e eu coloquei os rótulos de dados no topo, a fórmula para os valores desta nova série era algo como:

= IF (LEN ([@ [coluna1]]) = 0, NA () , [@ [coluna1]])

Eu usei o LEN como uma validação porque o ISEMPTY/ISBLANK não funcionava porque o resultado do INDEX/MATCH sempre retornava algo diferente de um espaço em branco mesmo que eu tivesse colocado um "" após o IFERROR ...

No gráfico de linhas, o valor de erro NA () faz com que o valor não seja exibido ... então isso funcionou para mim ...

Eu acho que é um pouco difícil seguir este procedimento sem fotos, mas eu espero que ele desenhe algum tipo de imagem para permitir que você use uma solução alternativa se você tiver um caso parecido com o meu

1
passing_by_1_time_post

Uma solução é que o gráfico/gráfico não mostra as linhas ocultas .

Você pode testar esses recursos fazendo: 1) clique direito no número da linha 2) clique em esconder.

Para fazer isso automaticamente, esse é o código simples:

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

Isto é o que eu encontrei enquanto eu estava plotando apenas 3 células de cada 4 colunas agrupadas. Meu gráfico tem uma célula mesclada com a data que é meu eixo x. O problema: BC26-BE27 estão traçando como ZERO no meu gráfico. digite a descrição da imagem aqui

Eu clico no filtro ao lado do gráfico e descobri onde ele está mostrando todas as colunas para as quais os pontos de dados são colocados no gráfico. Desmarquei as caixas que não possuem valores. insira a descrição da imagem aqui

Isso funcionou para mim.

1
JJann

Eu tive um problema semelhante usando um gráfico X/Y, mas depois também precisei calcular a função de correlação nos dois conjuntos de dados. 

=IF(A1>A2,A3,#N/A) permite que o gráfico seja plotado, mas a correlação de X e Y falha.

Eu resolvi isso por =IF(A1>A2,A3,FALSE)

O FALSE pode então ser removed usando formatação condicional ou outros truques

0
Miles

Embora este seja um post antigo, recentemente me deparei com ele quando procurava uma solução para o mesmo problema. Embora as soluções acima evitem que os gráficos gerem dados (quando as células de origem são # N/D ou parecem estar em branco), isso não resolve o problema dos próprios rótulos de dados do gráfico ainda mostrarem um rótulo zero. 

Eu tinha procurado e pesquisado e quase desistido, quando me deparei com a solução postada on-line @ https://www.extendoffice.com/documents/Excel/2031-Excel-hide-zero-data-labels.html

Funcionou gostou de um charme. Anexa-se uma imagem mostrando um exemplo de como os dados (rótulos) são exibidos antes, o Gráfico 1 e como ele é exibido usando o quadro de dicas nº2.

digite a descrição da imagem aqui

0
Heather Nygaard

Eu encontrei uma maneira de fazer isso.

você pode fazer um x, y scatterplot. ele irá ignorar registros nulos (ou seja, linhas)

0
thistleknot

Se você usar o PowerPivot e o PivotChart, excluirá linhas não existentes.

0
teitifeiti

Se você fizer a escala vertical em seu gráfico (usando o eixo de formatação) executar a partir de 0.0001 (digamos), então um valor que o Excel pensa ser zero não será plotado. Seu eixo no gráfico continuará parecendo que vai de zero para cima.

0
scott

A melhor maneira é usar Empty

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
0
gadolf

Eu tive o mesmo problema com uma linha completa aparecendo.

O final dos meus dados só tinha # N/A.

Quando mudei o tipo de gráfico de uma linha empilhada para linha então o final da linha foi removido do gráfico.

Isso não funcionou se o # N/A estava no meio dos dados, somente quando estava nos dados finais.

0
ChrisM

Eu estava tendo o mesmo problema.

Há uma diferença entre um Bar chart e um Stacked Bar chart

Como há uma diferença entre um Line chart e um Stacked Line chart.

O empilhado, não irá ignorar os valores 0 ou em branco, mas mostrará um valor cumulativo de acordo com as outras legendas.

Simplesmente clique com o botão direito no gráfico, clique em Change Chart Type e escolha um gráfico não empilhado.

0
Dr Yunke

Selecione os rótulos acima da barra. Formatar rótulos de dados. Em vez de selecionar "VALUE" (desmarque). SELECT Value from cells. Selecione o valor. Use a seguinte instrução: if (cellvalue = "", "" cellvalue) Onde cellvalue é o que quer que o cálculo esteja na célula.

0
Scott Guess