vacíos - Crear un gráfico en Excel que ignore#N/A o celdas en blanco
ocultar datos vacíos en gráficos de excel (18)
Cuando refiere el gráfico a un Rango definido, traza todos los puntos en ese rango, interpretando (con el fin de trazar) errores y espacios en blanco como valores nulos.
Tiene la opción de dejar esto como nulo (brecha) o forzarlo a cero. Pero ninguno de estos cambia el tamaño de la GAMA a la que apuntan los datos de la serie de gráficos. Por lo que veo, ninguno de estos es adecuado.
Si oculta toda la fila / columna donde existen los datos # N / A, la tabla debe ignorarlos por completo. Puede hacer esto manualmente haciendo clic derecho | ocultar fila, o usando la tabla Autofiltro. Creo que esto es lo que quieres lograr.
Estoy intentando crear un gráfico con una serie de datos dinámicos. Cada serie en el gráfico proviene de un rango absoluto, pero solo una cierta cantidad de ese rango puede tener datos, y el resto será #N/A
El problema es que el gráfico pega todas las celdas #N/A como valores en lugar de ignorarlos. He trabajado alrededor usando rangos dinámicos con nombre (es decir, Insertar> Nombre> Definir), pero eso es extremadamente ineficiente, ya que cada gráfico tiene 4 series dinámicas, y debo hacer 25 de estos gráficos.
¿Hay otras soluciones que me permitan especificar un rango, como es normal, para una serie de datos, pero le digo al cuadro que ignore todas las "N / A" o celdas en blanco?
Encontré una manera de hacerlo.
puedes hacer un diagrama de dispersión x, y. ignorará los registros nulos (es decir, las filas)
Estaba teniendo el mismo problema al usar una instrucción IF para devolver un valor no deseado a "", y el diagrama haría lo que usted describió.
Sin embargo, cuando utilicé #N/A lugar de "" (importante, tenga en cuenta que es sin las comillas como en #N/A y no "#N/A" ), el gráfico ignoró los datos no válidos. Incluso intenté poner una declaración FALSE inválida y funcionó de la misma manera, ¿la única diferencia fue #NAME? devuelto como el error en la celda en lugar de #N/A Utilizaré una declaración de IF compuesta para mostrarle lo que quiero decir:
=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)
Esto es lo que encontré, ya que estaba trazando solo 3 celdas de cada 4 columnas agrupadas. Mi gráfico tiene una celda fusionada con la fecha que es mi eje x. El problema: BC26-BE27 está trazando como CERO en mi carta. enter image description here
Hago clic en el filtro que se encuentra al costado del cuadro y encuentro dónde se muestran todas las columnas para las que se grafican los puntos de datos. Desmarqué las casillas que no tienen valores. enter image description here
Funcionó para mí
Hay otra solución mucho menos conocida que tiene mérito en algunos lugares. Use el #NULL! error que no traza y no crea un punto cero para el diagrama.
Entonces, su columna de trama se llena con la siguiente construcción. Suponga datos reales en la columna A y que la versión graficada está en la columna C.
= If (Isnúmero (A2), A2, A2 A3)
Ese espacio es un operador poco conocido para producir la intersección de las dos direcciones o rangos de celda. Si no hay intersección, entonces produce el mensaje #NULL!
¡Células que contienen #NULL! se omiten para fines de trazado.
Aparte (para Microsoft) solo necesita obtener las ecuaciones matemáticas para ignorar tales células de alguna manera?
La mejor forma es 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
Puede usar la función "= IF (ISERROR (A1); 0; A1)" esto mostrará cero si la celda A1 contiene un error o el valor real si no lo hace.
Seleccione las etiquetas encima de la barra. Formato de etiquetas de datos. En lugar de seleccionar "VALOR" (no hacer clic). SELECCIONE el valor de las celdas. Seleccione el valor Use la siguiente instrucción: if (cellvalue = "", "", cellvalue) donde cellvalue es lo que sea que el cálculo esté en la celda.
Si bien esta es una publicación anterior, la encontré recientemente cuando estaba buscando una solución para el mismo problema. Si bien las soluciones anteriores impiden que los gráficos grafiquen datos (cuando las celdas de origen son # N / A o se visualizan en blanco), no resuelve el problema de que las etiquetas de datos de gráfico aún muestren una etiqueta cero.
Había buscado y buscado y casi me había dado por vencido, cuando me encontré con la solución publicada en línea @ https://www.extendoffice.com/documents/excel/2031-excel-hide-zero-data-labels.html
Funcionó como un encanto. Se adjunta una imagen que muestra un ejemplo de cómo se muestran los datos (etiquetas) antes, el Gráfico n. ° 1 y cómo se muestra con este cuadro de sugerencias n. ° 2.
Si hace que la escala vertical en su gráfico (utilizando el eje de formato) se ejecute desde 0,0001 (por ejemplo), entonces no se trazará un valor que Excel piense que es cero. Su eje en el gráfico seguirá pareciendo que se ejecuta desde cero hacia arriba.
Si tiene una columna y que quiere hacer un diagrama de dispersión, pero no todas las celdas en una de las columnas están pobladas con valores significativos (es decir, algunas tienen #DIV/0! ), Inserte una nueva columna junto a la columna ofensiva y type =IFERROR(A2, #N/A) , donde A2 es el valor en la columna ofensiva.
¡Esto devolverá #N/A si hay un #DIV/0! y devolverá el buen valor de lo contrario. Ahora haga su trazado con su nueva columna y Excel omite #N/A valor #N/A y no los representará como ceros.
Importante : no muestre "#N/A" en la fórmula, simplemente envíe #N/A
Si usa PowerPivot y PivotChart, excluirá las filas que no existen.
Solo quería poner a mis 2cents sobre este tema ...
Tenía una necesidad similar cuando estaba extrayendo datos de otra tabla a través de INDEX / MATCH, y era difícil distinguir entre un valor de 0 real en lugar de un valor de 0 porque no coinciden (por ejemplo, para un gráfico de columnas que muestra el progreso de valores durante los 12 meses y en los que solo estamos en febrero, pero los datos del resto de los meses aún no están disponibles y el gráfico de columnas todavía mostró 0 en todas partes para marzo a diciembre)
Lo que terminé haciendo es crear una nueva serie y trazar esta nueva serie en el gráfico como un gráfico de líneas y luego oculté el gráfico de líneas al elegir no mostrar la línea en las opciones y puse las etiquetas de datos en la parte superior, la fórmula para los valores de esta nueva serie era algo así como:
= IF (LEN ([@ [columna1]]) = 0, NA () , [@ [columna1]])
Utilicé LEN como una validación porque ISEMPTY / ISBLANK no funcionó porque el resultado del ÍNDICE / MATCH siempre devolvió algo más que un espacio en blanco a pesar de que había puesto un "" después del IFERROR ...
En el gráfico de líneas, el valor de error NA () lo hace para que el valor no se muestre ... así que esto funcionó para mí ...
Supongo que es un poco difícil seguir este procedimiento sin imágenes, pero espero que represente un tipo de imagen que te permita utilizar una solución alternativa si tienes un caso similar al mío.
Tenga en cuenta que al trazar un gráfico de líneas, usar = NA () (salida # N / A) para evitar el trazado de valores no existentes solo funcionará para los extremos de cada serie, primer y último valor. Cualquier # N / A entre dos otros valores será ignorado y puenteado.
Tiene razón en que los espacios en blanco "" o una cadena "# N / A" se interpretan de hecho como que tienen valores en excel. Necesita usar una función NA ().
Tuve el mismo problema con una línea completa que aparece.
El final de mis datos solo tenía # N / A.
Cuando cambié el tipo de gráfico de una línea apilada a una línea , al final de la línea se eliminó del gráfico.
Esto no funcionó si el # N / A estaba en el medio de los datos, solo cuando estaba en los datos finales.
Tuve un problema similar al utilizar un gráfico X / Y, pero también tuve que calcular la función de correlación en los dos conjuntos de datos.
=IF(A1>A2,A3,#N/A) permite trazar el gráfico, pero la correlación de X e Y falla.
Lo resolví con =IF(A1>A2,A3,FALSE)
El FALSO se puede removed utilizando el formateo condicional u otros trucos
Una solución es que el gráfico / gráfico no muestra las filas ocultas .
Puede probar estas funciones haciendo: 1) haga clic derecho en el número de fila 2) haga clic en ocultar.
Para hacerlo automáticamente, este es el código simple:
For Each r In worksheet.Range("A1:A200")
If r.Value = "" Then
r.EntireRow.Hidden = True
Else:
r.EntireRow.Hidden = False
Next