Construir gráficos combinados en Excel

Uno de mis lectores me envía el siguiente gráfico



Admito que el gráfico es muy bueno desde el punto de vista estético. El efecto de los rectángulos rojos que parecen deslizarse dentro de las columnas azules es muy vistoso.
Pero, ¿es este gráfico efectivo? A primera vista no está claro que significan las columnas azules y las rojas y las relaciones entre ellas. Después de estudiar detenidamente el gráfico, vemos que la columnas "flotante" roja representa el valor de la serie D2, la columna azul el valor de D1. El tamaño de la columna roja muestra la relación entre los valores D1 y D2.
El hecho de tener que aplicar algún esfuerzo para entender el gráfico contradice el objetivo de estas herramientas. El gráfico debe ayudarnos a entender de un "golpe de vista" los datos de una tabla y sus relaciones.

De todas maneras no hay que desmerecer la calidad estética del gráfico, y aunque mi lector no me lo pide expresamente, veremos cómo se puede construir.

Para analizar el gráfico empezamos por ver qué tipo de gráfico es. Si abrimos el menú de Tipo de gráfico, vemos que se trata de un gráfico combinado que no figura entre los gráficos disponibles de Excel.



Esta es nuestra primera pista. Ahora abrimos el menú Datos de origen para ver cuantas series de datos tiene el gráfico



Vemos que hay cuatro series, y además dos ejes X. Para ver a que rango pertenece cada serie, seleccionamos la serie en la ventanilla Serie y nos fijamos en Valores. Así podemos ver que las series 4 y 3 usan el mismo rango.

Otra forma de investigar es con este método: después de seleccionar algún elemento del gráfico (con un clic), presionamos sucesivamente alguna de las flechas. Las flechas ascendente y descendente seleccionan con cada presión una serie de elementos del gráfico. Las flechas izquierda y derecha seleccionarán los elementos de las series.



Ahora nos fijamos en el menú Opciones del gráfico, en la pestaña Eje



Vemos que tenemos dos ejes X y dos Y.

Como se trata de un gráfico combinado, queremos investigar qué tipos de gráficos se han usado para cada serie. Seleccionamos una serie y abrimos el menú Tipo de Gráfico



Para la serie 4 vemos que se ha usado el tipo Columna Apilada. Si nos fijamos en la serie 1 veremos que el tipo es Columna Agrupada.

Con todos estos datos ya podemos empezar a sacar nuestras conclusiones sobre cómo han construido este gráfico. El único truco no evidente es el uso de la serie 4, Diff/2, cuyo objetivo veremos más adelante.

El primer paso es construir un gráfico de columnas agrupadas con los datos de la serie D1



Eliminamos la leyenda y el título. Damos fondos distintos al área del gráfico y al área de trazado.



Seleccionamos la serie haciendo clic a alguna de las columnas y en la opción Trama-Degradado-Estilo de sombreado-Vertical. En la pestaña Opciones llevamos el valor de Ancho de rango a 100. Nuestro gráfico se verá así



Ahora la serie Diff/2. Señalamos el rango E2:E6 y lo copiamos (Ctrl+C). Seleccionamos el grafico, abrimos el menú Edición y elegimos Pegado Especial con las opciones "Nueva Serie" y "Columna"



Seleccionamos la serie que acabamos de agregar y abrimos el menú Formato serie de datos. En la pestaña Eje, elegimos la opción "Eje_Secundario"; en la pestaña Opciones llevamos el valor de Ancho de rango a 250. El resultado es



Seleccionamos la serie que acabamos de agregar y cambiamos el tipo de gráfico a Columna Apilada. También cambiamos el color de la serie para que se mimetice con la serie 1 y eliminamos los bordes.

Ahora seleccionamos el rango C2:C6 y volvemos a hacer el mismo proceso para la serie D2



El próximo paso es agregar nuevamente la serie Diff/2, con el mismo método que hemos empleado para las dos series anteriores



Con esto casi hemos terminado. Sólo nos queda por eliminar el eje secundario. Antes de hacerlo no aseguramos que la escala de ambos ejes Y coincida plenamente.
Señalamos el eje Y secundario y abrimos el menú de formato de eje. En la pestaña Tramas ponemos "ninguna" en las opciones de los marcadores



Y este será el resultado final:



Como decía al principio, este gráfico no ayuda a entender los datos que representa. Pero si hacemos un pequeño cambio podemos obtener un gráfico más representativo.
Seleccionamos la serie D2 (serie 3, en rojo) y abrimos el menú de formato de datos. En la pestaña de Orden de Serie apretamos "subir" para que la serie 3 quede en primer lugar



El resultado final es un gráfico de tipo "Termómetro" que muestra la relación exacta entre las series D1 y D2



El archivo con todos los pasos de la construcción se puede descargar aquí.

Otros métodos de construir gráficos tipo termometro pueden verse en esta nota y en esta otra.