Con gran urgencia me llamó un antiguo jefe hace unos días para preguntarme si se podía hacer un gráfico de columnas apiladas y agrupadas en Excel. Tuve que pensarlo un poco pero mi respuesta fue “creo que sí”. Después de pelearme aquí está el resultado.
Paso 1: Los datos de partida
Los datos de partida que necesitamos usar son una tabla como la siguiente:
Queremos que el Valor 1 y el Valor 2 sean una misma columna apilada y el valor Suma sea la columna agrupada paralela.
Si introducimos un gráfico tipo columna apiladas como el de la siguiente imagen:
No conseguiremos nuestro objetivo así que tenemos que ser un poco más pillos y organizar la información de la tabla inicial de la siguiente manera.
Paso 2: Organizar la información
Organizamos la tabla de datos como puede verse en la siguiente imagen:
Fíjate bien. La primera fila y la ultima fila deberás tenerlas en cuenta aunque no haya números. Cada una de las filas representa una de las barras apiladas del gráfico que vamos a poner a continuación. Es decir, el tipo de columna es el que vemos a continuación:
Donde a la derecha hemos escrito el tipo de columna para que os sirva de guía.
Paso 3: Insertar gráfico de columnas apiladas
Seleccionamos todos los valores numéricos incluidos las cabeceras e insertamos un gráfico de barras apiladas. Como puedes ver en la siguiente imagen la distribución es la misma que habíamos comentado en el gráfico anterior.
Esto ya empieza a parecerse a lo que queremos pero todavía queda trabajo por hacer. Como puedes ver en las filas de la tabla donde habíamos indicado “Nada” no se ha dibujado ninguna barra. En las que habíamos indicado “Apilada” tenemos las barras azul y naranja y en la que que ponía “Acumulada” tenemos la barra gris.
Paso 4: aumentar el grosor de las barras
Para que el efecto de comparación de las dos barras quede bien deberemos ampliar el grosor de las mismas. Para ello seleccionamos una barra cualquiera y hacemos click con el botón derecho. Seleccionamos la opción “Dar formato a serie de datos…”.
Automáticamente en el panel de la derecha de Excel nos aparecerá un cuadro con opciones del gráfico. En este cuadro deberemos modificar el “Ancho del intervalo” como puede verse en la siguiente imagen:
y lo pondremos en el valor 0%. De esta manera las barras del gráfico se habrán ampliado hasta estar tocándose.
Paso 5: Añadimos una serie nueva con las categorías del eje horizontal
Para poner en el eje horizontal las etiquetas que queramos deberemos hacerlo añadiendo una nueva serie. Esta nueva serie está formada por los nombres de las categorías y por los valores 0. Vayamos paso por paso para entenderlo mejor.
Previamente añadiremos una columna auxiliar a nuestra tabla inicial como puede verse marcado en rojo en la siguiente tabla de Excel:
Después, añadiremos esta nueva serie auxiliar a nuestro gráfico.
Para añadir una nueva serie al gráfico hacemos click con el botón derecho sobre el gráfico y elegimos la opción “Seleccionar datos”. Después, en el recuadro de la derecha seleccionamos la opción “Agregar”. El nombre de la serie será “Auxiliar” y los valores serán 0, 0 y 0.
Paso 6: Cambiar tipo de gráfico de la serie Auxiliar
Para ello seleccionamos una barra cualquier del gráfico de columnas apiladas y agregadas que estamos creando. Hacemos click sobre dicha barra con el botón derecho y seleccionamos la opción “Cambiar tipo de gráfico de series…”. Inmediatamente aparecerá una venta como la siguiente:
Donde en la serie “Auxiliar” hemos cambiado el tipo de gráfico a “Línea con marcadores” y hemos seleccionado la opción de “Eje secundario”.
Paso 7: Añadir etiquetas a la serie Auxiliar
Volvemos a hacer click sobre el gráfico y elegimos la opción de “Seleccionar datos”. En el recuadro de la izquierda seleccionamos la serie “Auxiliar y en el recuadro de la derecha seleccionaremos la opción Editar para poder editar las categorías y seleccionaremos el rango de las categorías, es decir: Tipo A, Tipo B y Tipo C.
En la siguiente imagen se puede ver la serie seleccionada y el recuadro de categorías modificado.
Una vez cambiado esto haremos click en “Aceptar”. Una vez hecho esto aparecerá un gráfico como el de la imagen en el que se ha añadido una línea de puntos en la parte inferior izquierda del gráfico en Excel y ha aparecido el Eje vertical secundario.
Paso 8: añadir el eje horizontal secundario
Nuevamente seleccionamos el gráfico y en la esquina superior derecha, donde la cruceta verde que aparece seleccionamos la opción de “Eje” y marcamos “Horizontal secundario”.
Inmediatamente nos aparecerá en el gráfico el eje horizontal secundario que se colocará en la parte de arriba:
Puf, ¿difícil verdad?. Bueno, ten un poco de paciencia que ya no queda casi nada.
Paso 9: Cambiamos los ejes superior e inferior
Como el eje superior es el que me indica las categorías de mi gráfico lo quiero bajar a la parte de abajo del gráfico, donde está los números del 1 al 10. Para ello:
- Selecciono el eje horizontal superior
- Hago click con el botón derecho sobre el mismo y selecciono la opción “Dar formato al eje”.
- En las opciones del eje selecciono el desplegable de “Etiquetas”
- Y en “Etiquetas”, en la opción de “Posición de etiqueta” selecciono la opción “Bajo”.
Después selecciono el eje con los números y repito la misma operación pero en el último paso selecciono la “Posición de etiqueta”: Alto.
Paso 10: Cuadrar el eje horizontal
Como puedes ver en la siguiente imagen las bolitas no están perfectamente alineadas con el centro de los gráficos de barras. Aunque no se nota mucho con 3 grupos de barras con un mayor grupo de barras sí que se notaría… hazme caso, lo he probado 🙂
Para solucionar este pequeño problema selecciona el eje de arriba (el que ahora tiene los números). En el desplegable de “Opciones del eje” selecciona la opción “En marcas de graduación” para mover los números y colocarlos sobre las supuestas marcas de graduación.
Al hacer esto, las etiquetas del eje inferior se colocarán automáticamente centradas como puedes ver en la siguiente imagen:
Paso 11: los toques maestros
El último paso no es el más difícil pero puede que sea un poco complicado de hacer pero puede ser un poco lioso porque hay que dar un montón de pequeños pasos.
- Ocultar el eje vertical secundario. Para ello selecciona el eje y en el grupo de opciones “Etiqueta” selecciona la opción de “Posición del eje” Ninguno.
- Ocultar el eje horizontal superior. Para ello haz lo mismo que en el paso anterior. Y quita el color de la línea.
- Oculta el eje vertical principal (el de la izquierda)
- Quita las líneas del fondo (a no ser que sean imprescindibles).
- Quita el marco alrededor del gráfico (normalmente no queda muy bien)
- Añade los valores de las barras
- Selecciona la línea auxiliar y en las opciones de “Relleno y línea” selecciona en “Línea” la opción “Sin línea” y después en “Marcador” selecciona la opción “Ninguno”.
- En la leyenda selecciona la palabra “Auxiliar” y bórrala.
- Modifica los colores de las barras a tú gusto.
- Añade un título interesante al gráfico.
- Y cualquier otra cosa de formato que te interese.
Bonus track: descargar gráfico de columnas agrupadas y apiladas gratis
Puf… si has llegado hasta aquí te mereces descargarte este ejemplo. Creo que es uno de los gráficos más “chulos” y más “laboriosos” que hemos explicado en esta web. La verdad es que hacerlo lleva su trabajo ¡eh!… pues explicarlo ni te cuento así que te agradezco que compartas este artículo en las redes sociales y nos sigas en Facebook. Una vez hecho esto te podrás descargar el archivo sin ningún problema.
[sociallocker]
[/sociallocker]