lunes, 21 de noviembre de 2011
viernes, 18 de noviembre de 2011
Graficos Excel con Ejes Quebrados
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").
Suscribirse a:
Entradas (Atom)