Como este blog trata sobre gráficos y presentación de datos, combinaremos ambos temas y hablaremos de formato de números en gráficos de Excel.
Supongamos esta tabla que nos muestra el saldo de una cuenta imaginaria al final de cada mes
Creamos un gráfico de tipo Líneas
Los valores del eje de las Y son numéricos, y por lo tanto podemos aplicar formato de números, tal como lo hacemos con los rangos de los valores numéricos en las hojas de cálculo.
Por ejemplo, si queremos resaltar los valores negativos, haciéndolos aparecer en rojo, podemos aplicar el siguiente formato: #,##0;[Rojo]-#,##0
Los pasos a seguir son sencillos:
1 – Apuntando al eje de las Y abrimos el menú de formato del eje
2 – En la pestaña "Número" elegimos la opción Personalizado y seleccionamos el formato deseado
Podemos enriquecer un poco más la representación de los valores usando la opción de formato condicional en el patrón de números. Excel permite un formato condicional básico en el formato personalizado de acuerdo al siguiente patrón:
[condición]número positivo;[condición]número negativo;cero
Por ejemplo, si queremos que los números negativos aparezcan en rojo, los números positivos hasta 5,000 aparezcan en negro y los números mayores de 5,000 aparezcan en verde, aplicamos este formato personalizado:
[Verde][>5000] #,##0; [Rojo][<0]-#,##0>
Ahora supongamos que el límite inferior deseado del saldo es -6,000 y el máximo 6,000. Podemos agregar dos líneas que marquen estos límites
Para esto agregamos una columna de valores para cada límite en nuestra tabla para crear dos nuevas series
Seleccionamos el rango C2:D7, aplicamos Ctrl+C, seleccionamos el gráfico y en el menú de pegado especial marcamos "nueva serie" y "columnas", cambiamos un poco el formato estándar de Excel y obtenemos
El toque final que podemos dar es colorear el área definida por las líneas de límite, para enfatizar los puntos que caen en la "zona permitida".
Para hacer esto agregamos dos nuevas series, usando los datos ya existentes en el rango C2:D7 y les cambiamos el tipo de gráfico a "columnas apiladas".
Los pasos son los siguientes:
1 - Seleccionamos el rango C2:D7 y pulsamos Ctrl+C
2 - Seleccionamos el gráfico y en el menú Edición activamos Pegado Especial. En el diálogo marcamos "nueva series" y "columnas".
3 - Las nuevas series aparecen como líneas que se superponen a las ya existentes. Para seleccionarlas apuntamos a los marcadores, pulsamos el botón derecho y en el menu que se abre activamos "tipo de gráfico". Elegimos "columnas apiladas". Hacemos lo mismo con la segunda serie y obtenemos este gráfico
4 - Seleccionamos una de las series y abrimos el menú de formato de series de datos. En la pestaña "opciones" fijamos el ancho del rango en 0 (cero),
y en la pestaña "Tramas" seleccionamos la opción "ninguna" para Bordes y cambiamos el color del fondo de manera que coincida con el de la otra serie
Elegimos la otra serie y en el menú de formato de datos de serie, seleccionamos la opción "ninguna" para Bordes.
5 - Ahora tenemos que corregir la definición del eje de la X, para evitar el espacio en blanco entre el 0 y el primer valor de las categorías. Para esto, señalamos el eje de las X y on el botón derecho del mouse abrimos el menú de formato de ejes. En la pestaña "Escala" quitamos la marca de la opción "Eje de valores (Y) cruza entre categorías"
Finalmente obtenemos este gráfico.
Los valores de los límites, y el área comprendida entre ellos, son dinámicos. A medida que cambiamos los valores de los límites, las líneas y el área se irán adaptando.
8 comentarios:
Hola una consulta:
Como se puede hacer para hacer que excel no asuma como ceros los datos que estan en blanco y deje discontinua la linea en vez de completarla con ceros?
gracias
mi mail
rodrigo.arellano@gmail.com
gracias
Saludos
Hola Rodrigo
en el menú Herramientas--Opciones, abres la pestaña Gráfico y marcas la opción No Trazar en Trazar celdas vacías como
gracias JOrge, me sirvio totalmente tu ayuda.
Saludos Cordiales
Hola Jorge:
Siguiendo con el tema de las celdas en blanco. Yo las tengo con una fórmula, de manera que puenden estar en blanco o no. Si elimino la fórmula no me considera los blancos como ceros, pero en cuanto meto la fómula si que me los considera.
Tienes una idea de como solucionar esto??
Lo malo es cuando en una celda tienes una función. Por ejemplo en B2 pongo =A2. Cuando A2 es un blanco B2 lo toma como cero. Esto en la tabla lo arreglo con un SI(ESBLANCO...), pero al representar gráficamente no con sigo deshacerme de ese cero
LAM
Hola Carlos
una celda vacís es una celda que no contiene nada. Una celda que contiene una fórmula, también si el resultado es vacía, no está en blanco. Supongo que la fórmula es algo del tipo
=SI(condición,valor,"")
En lugar de usar "" pon la función NOD
=SI(condición,valor,NOD())
Esto hara que aparezca #N/A!, lo que solucionará tu problema en el gráfico. Para ocultar este resultado en la tabla (por razones estéticas) puedes usar formato condicional.
Hola Jorge, viendo el último comentario que tenías en la entrada, he sustituido las "" por NOD para que no me marque en el gráfico la línea cuando el valor es 0 y no me ocurre nada, se queda igual, la fórmula me la cambia por #NOMBRE pero en el gráfico se sigue viendo
Gracias de antemano
Mil disculpas por la demora en responder.
El error #NOMBRE! indica que Excel ve a NOD como nombre, no como función y este nombre no está definido.
El problema puede ser:
1 - has puesta NOD en la fórmula en lugar de NOD(), con los paréntesis.
2 - Estás usando Excel en inglés y por lo tanto la función tiene que ser NA().
Publicar un comentario