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