sábado, diciembre 09, 2006

Graficos Excel con Ejes Quebrados – Segunda Nota

En la nota anterior mostramos un método para representar datos en situaciones donde una de las series es desproporcionadamente más grande que las demás.
Ese método demanda mucho trabajo manual (que puede llegar a ser enervante para quien no esté dotado de cierta dosis de paciencia).
Veremos ahora un método mas elaborado, que no requiere (casi) ajustes manuales. Esta nota está basada en la de Jon Peltier sobre el tema.

Supongamos esta tabla



El gráfico que queremos lograr es el siguiente



Empezamos por representar los datos



Como se puede apreciar, las relaciones entre los distintos datos quedan oscurecidas por la desproporción de la línea de productos 2 en relación a las restantes.

Nuestro primer paso es "normalizar" los datos de la tabla. Para esto determinamos el valor donde queremos crear un "quiebre" en el eje de las Y, que en nuestro caso será 500000, y el factor de normalización que será 600000.
Lo que hacemos es restar 600000 de todo valor que sea mayor que el valor del punto de quiebre.
La tabla normalizada es la siguiente



La fórmula usada para normalizar los datos es =SI(B2>quiebre,B2-factor,B2), donde "quiebre" y "factor" son los nombres que señalan los rangos que contienen estos datos.

Ahora creamos un nuevo gráfico con los datos normalizados



Nuestro próximo paso es crear un eje de las Y que sirva a nuestros propósitos. El truco que empleamos es crear una serie de datos que representamos con el tipo de gráfico XY Dispersión. El valor de X será siempre 0, y el valor de Y coincidirá con los rótulos que queremos que aparezcan en el eje e las Y.

Para definir los valores de esta nueva serie creamos esta tabla



La fórmula para calcular el valor de Y (en la columna B) es

=SI(C38>quiebre,C38-factor,C38)

En la columna C ponemos los valores que queremos que aparezcan como rótulos.

Ahora copiamos los valores del rango B38:B44 y los agregamos como nueva serie al gráfico



Seleccionamos la nueva serie y cambiamos el tipo de gráfico a Dispersión XY. Aquí tenemos que manipular un poco las definiciones de la serie. Seleccionamos los puntos de la serie y en el menú Datos de origen agregamos el rango de los valores X (el rango A38:A44 en nuestra tabla).



Ahora Excel ha agregado dos ejes secundarios.



El eje secundario de las Y es innecesario y lo eliminamos (quitar la marca en el menú Opciones de Gráfico—Ejes).
Seleccionamos el eje secundario de la X y fijamos la escala con un mínimo de 0 y un máximo de 1. Luego hacemos "desaparecer" el eje eligiendo la opción "ninguno" en la pestaña de Tramas para las definiciones del eje.

Nuestro gráfico muestra ahora el eje de la Y con sus valores y los marcadores de la serie que acabamos de agregar (los pequeños cubos rosados).


Eliminamos los rótulos del eje de las Y, poniendo "ninguna" en las definiciones de las marcas de graduación en el menú de formato del eje Y.



Cambiamos el marcador con forma de cubo rosado a un guión negro, para crear la ilusión de marcadores de rótulo del eje

Luego, hacemos lugar a la izquierda del eje, achicando el área del gráfico, y ligamos los "marcadores" a los valores del rango C38:C44 de la tabla auxiliar. Aquí tenemos que usar un truco que ya hemos mostrado en una nota anterior.
Primero, agregamos rótulos a la serie. Luego seleccionamos uno de los rótulos (un clic seguido de otro), en la barra de fórmulas ponemos "=" (sin las comillas) y creamos una referencia a la celda que contiene el valor.


Por ejemplo, el valor 1400000 esta ligado a la celda C38



A esta altura de los acontecimientos, nuestro gráfico se ve así



Nuestro último paso consiste en crear el quiebre en el eje de las Y y en los puntos que superen el punto de quiebre (en nuestro caso sólo una).

También esto lo haremos creando una nueva serie que agregamos al gráfico. Para esto confeccionamos una tabla auxiliar



La tabla "posición de quiebre" nos está dividida en dos sectores. En "puntos" ponemos en la primera línea los coordenadas del primer punto (el eje de la Y) que será siempre (0,0). En las líneas subsiguientes ponemos los puntos que superan la línea de quiebre, en nuestro caso sólo uno (el punto 2 de la serie 1).
En la "posición" calculamos las coordenadas de los puntos que superan la línea de quiebre. La fórmula en la celda C133 es =SI(B133=0,0,(1/$B$128)*1.5)
Usamos esta fórmula para calcular la posición de un punto en el eje secundario que sea equivalente a la del eje principal. Como recordarán, el eje secundario de las X va de 0 a 1. Por lo tanto si hay 5 puntos Excel divide el eje secundario en intervalos de 0.2. El segundo punto estará ubicado, en términos del eje secundario, en el valor 0.3 (entre los valores 0.2 y 0.4). La fórmula divide 1 por el número de puntos en la serie y luego agrega un 50% para centrarlo.

Seleccionamos el rango C132:D134 y lo agregamos al gráfico como una nueva serie



Luego cambiamos el tipo de gráfico a Dispersión XY (sin líneas de conexión entre los puntos). También hacemos desaparecer el eje secundario de las X que ha vuelto a aparecer



Tenemos ahora que crear una marca para el quiebre. Usamos el paralelogramo de Autoformas. Agregamos dos líneas que coincidan con las líneas superior e inferior. Luego definimos el paralelogramo sin líneas y finalmente agrupamos las tres formas. Giramos la forma agrupada hasta obtener el ángulo deseado. Todo este proceso se puede hacer sobre el gráfico, de manera que sea más fácil calcular el tamaño de los elementos.

Con la técnica que ya mostramos en la nota sobre Autoformas, reemplazamos los marcadores de la serie de quiebre, con la forma que acabamos de crear.

El resultado es




QED (No! No "que en paz descanse" sino "quod erat demoonstrandum").

13 comentarios:

guillermo dijo...

Jorge

te he posteado un par de veces y no se a donde revisar para ver si te parecio interesante mi duda o no.

creo que al blog le fata una direccion donde estan todas tus aclaraciones

de antemano agradecido

Gmo.
ggvillafane@gmail.com

Jorge L. Dunkelman dijo...

Hola Guillermo, en estos dias ando muy ocupado con un proyecto por lo que no he tenido tiempo de responder.
Todavia no he leido tus comentarios, pero te respondere en cuanto lo haga.
Gracias por leer el blog.

Guillermo dijo...

Jorge

Que gusto saber que estas atento a tu blog, y por problemas laborales no has podido responder

atento a tus comentarios

Guillermo
ggvillafane@gmail.com

melvin_sanabria@hotmail.com dijo...

Jorge en algún momento has elaborado un gráfico de barras apiladas donde el ancho de las barras dependa de un valor, es decir, que el grafico va a tener columnas de diferente ancho, semejante a como sucede con los gráficos de burbujas, por ejemplo para graficar en "y" precio y en "x" los diferentes productos y que el ancho de cada columna dependa del volumen vendido de cada producto, de esta forma el grafico tendra columnas de difentes anchos según volumen y tamaño según precio.He revisado Excel pero no logro ubicar una posible solución.
Saludos, Melvin

Jorge L. Dunkelman dijo...

Hola, interesane pregunta. Por defecto, sólo se puede definir el ancho de las barras para todas las barras.
Tal vez haya alguna solución, pero tendremos que investigarlo.
Si encuentro alguna solución, será publicada

melvin_sanabria@hotmail.com dijo...

Gracias Jorge por atender mi consulta, estaré antento al Blog
Saludos¡

Anónimo dijo...

Hola Jorge, me parecemuy interesante tu blog, y muy útil.
Felicitaciones

Benny

Anónimo dijo...

Hola Jorge. Me llamo Carlos. Estoy haciendo un modelo de plan financiero con macros, dentro del cuál intento hacer un gráfica (Tipo de gráfico: Líneas. Subtipo: Línea apilada con marcadores en cada valor de datos)que muestre el crecimiento mensual continuo del Efectivo (Nunca se disminuyen), por lo que al final tengo un efectivo muchísimo mayor al inicial y cuando la grafico, los primeros meses en la gráfica aparecen como si fueran ceros por lo pequeños que son comparado con el último periodo. Los usuarios de este modelo no podrán cambiarlo, simplemente podrán meter los datos, por lo que para fines de presentación, se vería mal la gráfica, qué podría hacer para que la gráfica tenga significación. Un punto importante es que como es un modelo, los datos podrían cambiar de acuerdo al usuario.

Ya he visto tus dos notas sobre las gráficas con ejes quebrados, pero no hallo una forma para que salga en mi gráfica.

De antemano, muchas gracias.

Carlos
sir_cygnus@hotmail.com

Fernando dijo...

Hola! llevo semanas investigando si se puede hacer una gráfica donde cada barra sea un rango de valores... es decir, por ej la primera barra vaya de 30 a 50, la segunda barra de 90 a 234 y la tercera de -23 a 45...
No se si me explicado bien... esto es posible? llevo tiempo indagando y no logro nada.
Saludos y ánimos a seguir con la web.
Gracias
Fer

Anónimo dijo...

Llegue a tu blog de casualidad buscando como hacer un gráfico que sólo a mi jefe se le podía ocurrir...use algunos de tus consejos y despues la inspiración propia...gracias por compartir tus datos.

Saludos

francisca

Jorge L. Dunkelman dijo...

Hola Carlos
mil disculpas por no haber contestado antes. He tenido un poco abandonado a este blog.
Me parece que la solución a tu problema es usar escala logarítmica en tu gráfico.
Dentro de poco estaré publicando una nota sobre el tema.

Jorge L. Dunkelman dijo...

Hola Fernando,
como a los demás, mil disculpas por no haber respondido antes. En breve estaré úblicando una nota sobre el tema, Gráficos de Columnas Flotantes.

Kagb dijo...

Hola, solo para agradecerte por este post...Yo pensé que este problema no tenía solución pero la verdad que luego de leer tus consejos resulta bastante más sencillo. Yo utilize Iworks para mac y también funcionó bastante bien.
Muchas gracias me ahorraste varios días de trabajo y consultas múltiples...
Saludos.