Recientemente, vi un gráfico que me dejó con muchas dudas y pensé en escribir un post para ver cómo se podría mejorar. El gráfico era de barras apiladas y tenía 5 series por 7 años.
Me pareció muy confuso y creo que sería mejorar el gráfico tal cual.
El problema con las barras apiladas
Para establecer la base de por qué tenemos que resaltar las series de datos en el gráfico de columnas apiladas, quiero hablar de lo que está mal con estos gráficos apilados.
- Demasiados datos al mismo tiempo - Si nos fijamos en el gráfico anterior hay 35 puntos de datos (7 años x 5 agentes) brillando a la vez. Es posible aunque muy difícil que quieras que analizar todos los puntos de datos juntos
- Dado que tiene demasiados datos para ver a la vez, los colores siguen aumentando - Cuanto más se realiza el apilado, más colores se necesitan para diferenciar las divisiones de barras. Demasiados colores hacen que la carta distraiga y sea difícil de leer
- El etiquetado indirecto es otro problema en los gráficos apilados - Y debido a que las barras se apilan una encima de la otra, tendría que ver el color y luego mirar la etiqueta (que es el etiquetado indirecto)
- Las barras no empiezan al mismo nivel - Puesto que las barras están apiladas no necesariamente comienzan en el mismo nivel. Se hace un poco difícil ser más preciso cuando las barras no están comenzando en el mismo nivel
Bueno pues veamos lo que proponemos!
Romper los datos y mostrar un agente cada vez
La solución es bastante simple y supone romper el gráfico y centrarse en una serie de datos cada vez. Y vamos a hacer el gráfico dinámico también!
Aquí están los datos
1) Creamos una lista desplegable con validación de datos
Si necesitas ayuda con la validación de datos, en este enlace lo explico bien: La validación de datos en Excel
Creamos una lista de validación de datos sencilla para crear una lista desplegable
- Desde la ficha Datos hacemos clic en Validación de datos (Atajo Alt D + VV + V)
- Seleccione Lista y vincule el origen a la lista de agentes en la hoja
- Esto nos ayudará a seleccionar el agente a resaltar
2) Usamos un simple BuscarV para extraer los datos del agente seleccionado
Si necesitas ayuda con la función BuscarV, en este enlace lo explico bien: La función BuscarV
En la celda de al lado de la lista desplegable (b11), escribimos la siguiente fórmula:
=BUSCARV($A$11,$A$4:$H$8,B1,FALSO)
Y la copiamos a las celdas C11:H11
Tengamos en cuenta que he creado una nueva fila Columna Ficticia para cambiar los números de columna automáticamente. Aunque esto no es ciertamente la forma más robusta, para este caso funciona bien! Sigamos avanzando
3) La fórmula ficticia
Necesitamos una serie ficticia para resaltar los datos del Agente seleccionado. Así que el dato ficticio será la suma de todos los valores de los agentes por encima del agente seleccionado. Ahora todo lo que necesitamos es una fórmula para calcular dinámicamente la suma de todos los agentes antes de que el agente resaltado.
Esa fórmula es:
=SUMA(DESREF(B3,,,COINCIDIR($A$11,$A$4:$A$8,0)))
Para entender mejor la función DESREF, pincha aquí: La función DESREF
Veamos cómo funciona esta fórmula. Lo que básicamente hacer es sumar para cada año los valores de los agentes por encima del seleccionado, de tal manera que si el Agente E está seleccionado la suma será la de los Agentes A-D que obviamente es 28+58+54+37=177
Teoricamente está fórmula sería así:
=SUMA(DESREF(Empieza por la celda vacía,,,COINCIDIR(Agente seleccionado,Lista de agentes,0)))
- La Función de DESREF hará un rango de todos los valores antes del agente seleccionado
- La función suma tomará un total de todos los valores que se dan por DESREF
¡Y eso es exactamente lo que queríamos!
4) Creamos un gráfico de columnas apiladas
Vamos a insertar un gráfico de columnas apiladas con los datos originales.
- Todo lo que he hecho es un simple gráfico de barras apiladas con todos los datos de los agentes.
- Coloreo todas las series de gris, con el borde blanco. También quito la líneas de cuadrícula.
5) Ahora agregamos la serie ficticia al gráfico de columnas apiladas
Vamos a insertar un gráfico de columnas apiladas con los datos originales.
Hay 5 cosas principales que hacer
- Añada el Agente seleccionado y la serie ficticia al gráfico que hemos creado
- Poner ambos (Agente seleccionado y ficticia) en el eje secundario
- Cambie el orden de la serie en el cuadro de selección de datos. La serie resaltada debe estar encima de la Serie Ficticia
- Cambiar el color de la Serie Ficticia a 'Sin Relleno'
- Fije el punto máximo del eje a 250. Esto asegurará que el eje no cambie automáticamente incluso si los datos cambian
Y ya estaría hecho!
Para descargarse el archivo pincha aquí.
Pues espero que os haya gustado.
Comenta cómo te fue u otras maneras de hacerlo en los comentarios. Muchas gracias
Te ha gustado el artículo?
No te olvides de mostrar tu gratitud hacia el autor (yo) mediante una de las 3 siguientes acciones:
1. Puedes dejar una donación pinchando en la siguiente imagen.
2. Puedes hacer clic en los anuncios mostrados en la página
3. Puedes recomendar el artículo en Google haciendo clic en g+1 (justo después del artículo), también puedes compartirlo en Facebook, y/o dejar comentarios.
1. Puedes dejar una donación pinchando en la siguiente imagen.
2. Puedes hacer clic en los anuncios mostrados en la página
3. Puedes recomendar el artículo en Google haciendo clic en g+1 (justo después del artículo), también puedes compartirlo en Facebook, y/o dejar comentarios.
Quieres aprender más en Excel o tienes algún problema que necesita solución?
Puedo ofrecer servicios de consultoría así como cursos personalizados online. Mírate el siguiente enlace si estás interesado/a.
El Sabio de Excel busca colaborades del blog
Si te interesa compartir tu conocimiento sobre Excel de tal manera que puedas ayudar a otras personas, por favor mándame un email a elsabiodeexcel@gmail.com
No hay comentarios:
Publicar un comentario