Graficos Excel con Ejes Quebrados

Supongamos que queremos representar en un gráfico de Excel esta tabla de datos



En este tipo de situaciones, en las cuales una de las series es desproporcionadamente más grande que las demás, son difíciles de representar en un gráfico.
Al usar una única escala, las series más pequeñas "desaparecen" de la vista o pierden significación.



Soluciones posibles son usar un gráfico con dos ejes de Y o usar uno con escala logarítmica.
Convengamos que ambas soluciones son poco atractivas, ya que por general escaparán a la comprensión del público promedio.

En esta nota veremos una técnica sencilla para crear gráficos con ejes Y quebrados.

Partiendo de los datos de la tabla, creamos dos gráficos de barras agrupadas. En el primero fijamos la escala del eje de Y de 0 a 200,000 y dejamos bastante espacio libre entre el gráfico y el título para acomodar allí el segundo gráfico



Luego creamos el mismo gráfico, pero fijamos la escala de Y de 500,000 a 800,000. Además volvemos transparentes el borde y el fondo del área del gráfico



Ahora sobreponemos el segundo gráfico en el área libre del primero, cuidando que las proporciones coincidan



Sólo nos queda por agregar una línea que indique el "quiebre" de la serie. Esto lo hacemos, por ejemplo, con líneas de Autoformas. Luego ubicamos la autoforma en el lugar correspondiente



Esta técnica es sencilla y sólo requiere una cierta dosis de paciencia para hacer coincidir los dos gráficos.
En la próxima nota veremos un método más elaborado para lograr este tipo de gráficos.

Graficos Excel Dinamicos – Determinando cuantos datos exhibir

Tengo una tabla de ventas de un producto que quiero representar con un gráfico de Excel. El problema es que la tabla tiene los datos de 278 semanas!

Nuestro objetivo es crear un gráfico dinámico, donde podamos establecer la cantidad de datos a ser exhibidos.

Hay más de una solución a este tipo de situación. En esta nota mostraré dos de ellas.

En el primer ejemplo, establecemos cuantos puntos exhibir a partir del primer punto de la serie.

El primer paso es crear un gráfico de líneas (en nuestro ejemplo), a partir de la tabla. Como ven, este gráfico es imposible de interpretar




Nuestro objetivo es crear un gráfico donde podamos determinar dinámicamente el rango de puntos a ser exhibido.

Para convertir este gráfico en dinámico, empezamos por definir nombres para los rangos de las series:

Semana_Final =Hoja1!$C$1
Fecha =DESREF(Hoja1!$A$2,0,0,Semana_Final,1)
Ventas =DESREF(Hoja1!$B$2,0,0,Semana_Final,1)

En la celda C1 ponemos el valor 10, para evitar recibir resultados de error más adelante. Este valor cambiará automáticamente luego.

El segundo paso consiste en reemplazar los rangos estáticos de la fórmula SERIES del gráfico por los rangos dinámicos que acabamos de crear



Podemos reemplazar los rangos por los nombres directamente en la barra de fórmulas, o en el diálogo de Origen de Datos



En las ventanillas correspondientes de escribimos el nombre del rango precedido por el nombre de la hoja y el signo de exclamación. En nuestro caso Hoja1!Ventas y Hoja1!Fecha.

Dado que al rango Semana_Final (la celda C1) le hemos dado un valor de 10, veremos en nuestro gráfico 10 puntos (semanas), empezando por la fecha 03/01/2002 que es el primer punto de la tabla.



En lugar de cambiar manualmente el valor de la celda C1, podemos poner un control, más precisamente, una barra de desplazamiento.

Para esto activamos el menú de formularios y hacemos un clic al icono de la barra de desplazamiento



Ubicamos la barra de deslazamiento en el gráfico y activamos el menú de formato del control



En la ventanilla de Vincular con Celda ponemos Semana_Final (o C1). El resto de los parámetros son obvios.

El resultado es



Ahora podemos usar la barra de desplazamiento para fijar la cantidad de puntos que queremos representar en el gráfico.

La segunda solución consiste en establecer no solo la cantidad de puntos, sino también el punto del principio.

Copiamos la tabla de datos y el mismo gráfico a una nueva hoja (puntos), y definimos estos rangos con nombres

Fecha_2 =DESREF(Hoja2!$A$2,Inicio-1,0,Final,1)
Ventas_2 =DESREF(Hoja2!$B$2,Inicio-1,0,Final,1)
Final =Hoja2!$I$4
Inicio =Hoja2!$G$4

En los dos primeros nombres la sintaxis de la función DESREF es distinta a la usada anteriormente. En mi blog sobre Excel hay una explicación detallada sobre la función DESREF.

Los valores de los nombres Inicio y Final, argumentos de la función DESREF en los nombre de los rangos dinámicos, los estableceremos con Controles de Números de la barra de Formularios.

Nuestro objetivo es insertar dos controles, uno para establecer el primer punto de la serie y el segundo para establecer el número de puntos a representar.

Para insertar estos controles abrimos el menú de Formularios y hacemos clic al icono de control de número



Para crear el control del punto inicial colocamos el control por encima del gráfico y creamos la celda vinculada (G4 en nuestro caso) de manera que quede oculta debajo del control.



En la celda F4 escribimos esta fórmula: =INDICE(Fecha_2,1)

Para el segundo control, el número de semanas (puntos), creamos un control similar, definiendo como celda vinculada I4


Una vez terminado nuestro modelo se ve así



En este modelo podemos controlar el punto de partida y la cantidad de datos a representar en el gráfico.



Technorati Tags: Excel, Graficos en Excel

Gráficos Excel - Agregar rótulos al eje de los valores

Esta nota fue publicada originariamente en mi blog sobre Excel

Después de leer mi nota sobre como agregar líneas dinámicas en gráficos de Excel, el jefe de control de calidad de mi empresa me pidió que le preparara el siguiente gráfico:

-en el eje de las X (categorías) aparecen meses del año.

-los valores (Y) son representados por columnas que representan un índice entre 0% y 100% de productos aprobados de la producción del mes.

-en el eje de las Y en lugar de los valores quiere que aparezcan los rótulos "reprobado", "aceptable", "bueno", "muy bueno", "excelente", de acuerdo a los resultados de cada mes.

-partiendo de cada rótulo debe haber una línea horizontal que ayude a interpretar el nivel de resultados alcanzado en cada mes.

Supongamos que esta es la tabla de calificaciones del laboratorio





Y esta la tabla de índices para el año:



El gráfico que queremos construir es el siguiente:



Para lograr este gráfico, con rótulos de texto en el eje de las Y en lugar de valores numéricos, usaremos una combinación de gráfico de columnas y puntos XY.
Mostraremos el proceso paso por paso:

1 - Seleccionamos la tabla de datos (el rango A1:B9) y creamos un gráfico de columnas, sin leyenda y con el título.




2 - Creamos una tabla auxiliar para definir la nueva serie de puntos con los que definiremos las líneas auxiliares. En el rango E3:F9 entramos los valores que definirán los puntos



3 - Seleccionamos el rango E3:F9 y lo copiamos (Ctrl+C). Luego seleccionamos el gráfico con un clic, abrimos el menú Edición—Pegado Especial, y elegimos las opciones señaladas en la imagen



Al pulsar Aceptar aparece una nueva serie de columnas



4 - Seleccionamos la nueva serie y en el menú Tipo de Grafico elegimos el tipo XY (Dispersión) con puntos conectados por líneas. Como resultado aparecen los puntos sobre el eje de las Y y dos ejes secundarios a la derecha y por encima del gráfico



5 - Seleccionamos el eje secundario de las Y, abrimos el menú de formato del eje. En la pestaña Escala fijamos el valor máximo en 100 y el mínimo en 0. En la pestaña Tramas ponemos todos los valores en "ninguna", con lo cual ocultamos el eje.

6 - Hacemos lo mismo con el eje secundario de las X.

7 - Seleccionamos la serie de los puntos XY que aparecen sobre el eje de las Y y abrimos el menú de Formato de serie de datos. En la pestaña Tramas elegimos el color negro para la línea; elegimos un marcador adecuado, con color negro




8 - En la pestaña Barras de Error X, señalamos la opción "por exceso" y ponemos 100 para "valor fijo" (coincide con la escala del eje secundario de las X).



9 - En la pestaña Rótulos de Datos elegimos "nombre de la serie". Luego de pulsar aceptar, seleccionamos los rótulos con un clic y abrimos el menú Formato de rótulos de Datos. En la pestaña Alineación seleccionamos "izquierda" para Posición.



10 – Seleccionamos el eje principal de la Y y los hacemos "desaparecer" poniendo "ninguno" en todas las opciones de la pestaña Tramas.



11 – Ahora nos ocuparemos de los rótulos "Serie 2". En primer lugar borramos el rótulo inferior que aparece en el punto de intersección de las coordenadas. Para esto seleccionamos el rotulo con dos clics y pulsamos "borrar".
Para cambiar el texto de los restantes rótulos tenemos dos opciones: seleccionar cada rótulo con dos clis y escribir el texto correspondiente, o crear un vínculo dinámico a los textos que aparecen en el rango D4:D9.
Para crear un vínculo dinámico, seleccionamos, por ejemplo, el rótulo superior, que debe decir "sobresaliente", luego ponemos el signo "=" en la barra de fórmulas y cliqueamos la celda D9 (donde aparece el texto "sobresaliente").




Volvemos sobre lo mismo para cada uno de los rótulos. De esta manera los valores de los rótulos y de las líneas se adaptarán automáticamente a los cambios en la tabla auxiliar. Es decir, nuestro gráfico es totalmente dinámico.



Technorati Tags: Excel, Graficos en Excel

Graficos tipo termometro con Excel

Supongamos una empresa con 5 departamentos de ventas. En esta tabla mostramos las ventas del año anterior, el plan de ventas para el presente año y las ventas acumuladas al mes de noviembre

Queremos crear un gráfico que muestre las relaciones entre las tres sumas (ventas año anterior, plan y ventas corrientes). La opción más inmediata, y la que generalmente es elegida es el gráfico de columnas.
Se lo puede crear fácilmente seleccionando el rango de la tabla (A1:D6), activar el asistente para gráficos y elegir Columnas Agrupadas



Personalmente no me agrada este gráfico y además opino que es difícil de interpretar; exige cierto esfuerzo el comparar las tres columnas entender la relación entre ellas.

La alternativa al gráfico de columnas agrupadas, en este tipo de situaciones, es un gráfico de tipo "termómetro". Este gráfico no figura en la galería de gráficos de Excel y, por lo tanto, tendremos que crearlo combinando distintos tipos de gráficos en uno.

Mi idea es llegar a este gráfico:



donde la columna celeste representa el plan, la línea roja las ventas del año corriente y la línea gruesa negra las ventas del año anterior.

En este gráfico, los tres datos de cada departamento están representados sobre una misma superficie, con tres distintos tipo de gráficos, lo que facilita la interpretación de los datos.

Para crear este gráfico damos los siguientes pasos:

1 - creamos un gráfico de columnas agrupadas sólo con los datos del plan para cada departamento (rango de datos A1:A6, C1:C6)

2 – Ahora agregamos la serie de datos Corriente (D2:D6). Esto podemos hacerlo copiando el rango, seleccionando luego el gráfico y activando Pegado Especial o seleccionando el gráfico y activando el menú Gráfico—Datos de Origen—Agregar



Seleccionamos la nueva serie y en el menú de formato de serie de datos, en la pestaña Tramas, seleccionamos para Borde y Área la opción "ninguno".

En la pestaña Barras de Error Y seleccionamos "por defecto" y en "porcentaje" ponemos 100%. Finalmente seleccionamos la pestaña Opciones y en la ventanilla Superposición establecemos el valor 100 para superponerlas.
El resultado es

Damos un formato conveniente a las barras de error con el menú de formato de barras de error (apuntar a una de las barras y abrir el menú pulsando el botón derecho del mouse).



3 – Agregamos la serie de ventas del año anterior, como en el caso anterior. Después de agregar la nueva serie al gráfico cambiamos el tipo de gráfico a Líneas



Ahora hacemos "desaparecer" las líneas que unen los puntos de la serie con el menú Formato de series de datos—Tramas, poniendo los valores de Línea a "ninguna".



Queremos transformar el marcador de la serie, el pequeño rombo, en una línea que cruce la barra a la altura adecuada. Para esto usamos la técnica descrita en mi nota sobre autoformas en gráficos Excel.

Creamos la línea con autoformas y la adaptamos al tamaño indicado. Seleccionamos la autoforma que acabamos de crear y la copiamos al clipboard (Ctrl+C). Luego seleccionamos uno de los marcadores de la serie con un solo clic y pegamos la autoforma



Sólo nos falta agregar una leyenda para facilitar la interpretación de los datos. Hacemos esto con el menú Gráficos—Opciones de gráfico



Podemos ver que no tenemos un marcador para la serie Corriente. Esto se debe a que hemos puesto las definiciones de marcador y línea de la serie a "ninguna" y la serie está representada por las barras de error.
Lo que podemos hacer es agregar una línea de la barra de dibujo para simular el marcador que nos falta con lo que llegamos al resultado final

Technorati Tags: Excel, Graficos en Excel

Color, contraste y dimension

Una de las consideraciones a tomar en cuenta al crear un gráfico en Excel son los colores. Cuando creamos un gráfico Excel determina colores de acuerdo a los existentes en la paleta de colores.
En mi opinión, estas elecciones suelen ser poco exitosas, tanto del punto de vista estético como del punto de vista de los objetivos del gráfico.

En el sitio Unofficial Office Stuff encuentro un enlace a un proyecto interesante del sitio Poynter.org.

Se trata de un proyecto sobre como el uso del color, contraste y dimensión afecta la atención del lector:

Color, Contrast & Dimension in News Design

El proyecto está orientado al diseño de periódicos o revistas, pero se puede aplicar también a diseños de gráficos, que es el tema de este blog.

El proyecto está escrito en inglés.

Technorati Tags: Excel, Graficos en Excel

Graficos Excel – Agregar lineas verticales con barras de error

En las últimas notas vimos como usar barras de error en gráficos de Excel para agregar líneas horizontales.
Con la misma técnica podemos agregar líneas verticales.

Siguiendo con nuestro ejemplo de los saldos de una cuenta por mes, digamos que queremos poner una línea vertical que separe entre dos etapas.
Por ejemplo, en el mes de abril hubo un acontecimiento importante que influyó al saldo de la cuenta y queremos poner una línea para enfatizar el "antes" y "después".
Nuestro gráfico deberá verse de esta manera:

Para crear la línea vertical agregamos un gráfico de tipo XY, con un solo punto, a nuestro gráfico. Una vez agregada la nueva serie, definiremos una barra de error Y, cuyos parámetros coincidirán con el máximo y el mínimo de la escala del eje principal de las Y.

Nuestro primer paso es definir los valores del punto XY que generará la línea vertical. Como queremos que esta línea sea dinámica, los definiremos con fórmulas, no con constantes.

A nuestra tabla de valores agregamos 6 celdas en el rango E1:G2



En la celda E2 tenemos una lista desplegable para elegir el mes que queremos coincida con la línea. Para esto usamos Validación de Datos, con la opción "lista" usando como origen el nombre "meses" que contiene el rango A2:A7



En la celda F2 escribimos esta fórmula:

=COINCIDIR(E2,meses,0)/CONTARA(meses)

que nos da un número que representa el número de orden del mes (debemos tener en cuenta que los valores del eje de la X son texto, y por lo tanto Excel los representa por su número de orden).

En la celda G2 ponemos la constante 0.

Ahora tenemos que agregar esta nueva serie al gráfico. Con el gráfico seleccionado, abrimos el menú Gráfico—Datos de Origen
En el recuadro Serie apretamos el botón Agregar; en nombre elegimos la celda E1; en Valores el rango F2:G2



No tenemos forma de determinar el tipo de gráfico antes de esta etapa. Por lo tanto debemos corregir la "elección" de Excel. En nuestro ejemplo la serie aparece como un gráfico de tipo Líneas, con dos puntos



Seleccionamos la nueva serie y cambiamos el tipo de gráfico a XY. Debemos ser cuidadosos de seleccionar alguno de los dos puntos que acabamos de agregar.

En esta etapa nuestro gráfico se ve así



Ahora entramos en el menú de Datos de Origen y seleccionamos la serie Mes (la serie que acabamos de agregar). Como pueden ver tenemos ahora dos ventanillas para definir valores. En valores de X señalamos la celda F2 y en valores de Y señalamos la ventanilla G2



Seleccionamos el nuevo punto, y en el menú de Formato de series de Datos vamos a la pestaña Ejes. Allí seleccionamos "eje secundario



Si los ejes secundarios no han aparecido, los hacemos visibles apuntado al extremo de los ejes visibles hasta que veamos el rotulo del eje. Abrimos el menú de formato del eje (botón derecho del Mouse) y establecemos los valores de Líneas a "automáticas" y Marca de graduación y Rótulos a "exterior"

La escala del eje secunario de Y debe coincidir exactamente con el eje principal. El eje secundario de X debe tener una escala de 0 a 1.

El próximo paso es definir las barras de error de Y del punto. Para esto seleccionamos el punto y abrimos el menú de Formato de series de Datos.
En la pestaña de Barras de Error de Y, seleccionamos Presentar—Ambas, y en la ventanilla Personalizada ponemos el valor 12000 para "+" y para "-" (12000 es el valor absoluto tanto del máximo como del mínimo de la escala).



el resultado



le hacemos ahora los siguientes arreglos cosméticos

- Seleccionamos la barra de error y en el menú de formato de barras de error, elegimos el grosor y el color adecuados



- Seleccionamos el punto y en el menú de formato de puntos de serie, en la pestaña Tramas seleccionamos Marcador—Ninguno
- Hacemos desaparecer los ejes secundarios poniendo los valores de Tramas en el menú de formato de estos ejes a "ninguno".



Como pueden ver la línea pasa entre los rótulos Abril y Mayo. Para corregir esto reescribimos la fórmula que define el valor del punto X, en la celda F2, de la siguiente manera

=COINCIDIR(E2,meses,0)/CONTARA(meses)-(1/(CONTARA(meses)*2))

Ahora la línea coincide exactamente con el rótulo

Technorati Tags: Excel, Graficos en Excel