Agregando líneas dinámicas a gráficos de Excel

En mi nota sobre formato de números en un gráfico de Excel, mostraba como agregar líneas horizontales para señalar, por ejemplo, límites de valores.
El problema con el método propuesto es que las líneas que creamos no comienzan en el eje de las Y, lo cual hace que la intención de estas líneas no sea clara



La solución que dábamos era quitar la marca de la opción "Eje de valores (Y) cruza entre categorías" en el menú de formato del eje de las X.
Esto crea un nuevo problema, ya que el rótulo del primer punto de la serie aparece debajo del punto de intersección de los ejes.

En esta nota mostraré un método más avanzado, para agregar líneas a un gráfico. Nuestro objetivo es que estas líneas sean dinámicas. Primero describiremos la técnica y luego daremos algunas explicaciones.

Supongamos la tabla de datos del ejemplo anterior



Como en ese caso, queremos crear una línea que marque el límite superior aceptable (valor = 6,000) y una que marque el límite inferior aceptable (valor = -6,000). Los pasos a dar son los siguientes:

1 - Creamos dos tablas auxiliares, una para el límite superior y otra para el inferior




2 - Seleccionamos el rango G1:H3 y lo copiamos (Ctrl+C).
3 – Seleccionamos el gráfico con un clic, y abrimos el menú Edición—Pegado Especial. En el diálogo que se abre, seleccionamos las opciones "Nueva Serie" y "Columnas"




4 – Seleccionamos la nueva serie de datos, abrimos el menú de gráficos y en el menú Tipo de Gráficos seleccionamos XY (Dispersión) con datos conectados por líneas, sin marcadores de datos



5 – Excel agrega dos eje secundarios, a la izquierda y en la parte superior del gráfico.



Seleccionamos uno de los ejes, y definimos la escala con un máximo de 100, un mínimo de 0, y en la pestaña "Tramas" elegimos la opción "ninguno" para todas las marcas de graduación y para "Líneas" (con lo cual ocultamos el eje).

6 – Seleccionamos el otro eje definimos la escala y los formatos como en el caso anterior. También podemos apretamos F4 para copiar los formatos.



El resultado es el siguiente



Para introducir el límite inferior seleccionamos el rango K1:L3 y volvemos sobres lo indicado en el paso 3. Luego formamos las líneas con el color y el grosor adecuados



Ahora explicaremos el método, es decir, cómo calculamos los valores 75.00 y 25.00.
Hacemos reaparecer por un momento el eje secundario de la Y




Aquí podemos ver que el valor 6,000 en el eje de la izquierda coincide con el valor 75 del eje de la derecha. Y lo mismo respecto a -6,000 y 25.
En ambos ejes los valores están distribuidos a intervalos regulares. Por lo tanto tenemos que calcular en qué posición está ubicado el valor del límite en el eje principal teniendo en cuenta que el valor máximo de la escala (en nuestro caso 12,000) ocupa la posición 0 (cero), el segundo valor (10,000) la posición 1 y así sucesivamente.
En nuestro caso 6,000 ocupa la posición 3 (-6,000 ocupa la posición 9). Para calcular la posición equivalente en el eje secundario dividimos 100 (el valor máximo del eje) por la cantidad de puntos en el eje (sin tomar en cuenta el 0), des decir 100/12 = 8.33
Este es el intervalo entre los puntos del eje. Para calcular el valor multiplicamos este factor por el número de posición y lo restamos de 100; en nuestro caso 100 – (8.33*3) = 75
Construimos una tabla auxiliar




En el rango F3:F5 ponemos las definiciones de la escala del eje.
En la celda F6 calculamos el número de posiciones en el eje con la fórmula =SUMA(ABS(F3),ABS(F4))/F5
En la celda F7 calculamos el valor del intervalo entre cada posición con la fórmula =100/F6
Y en la celda F8 calculamos la posición (número de orden) del punto buscado con la fórmula =(F3-F2)/F5

De esta manera nuestro gráfico es totalmente dinámico. Lo único a tener en cuenta es que si cambiamos la escala del eje principal, debemos cambiar los valores correspondientes en la tabla auxiliar.




Categorías: Graficos_

Technorati Tags: Excel, Graficos en Excel