GRAFICO COMPARAR MESES DE MUCHOS AÑOS

La vez anterior les mostraba como comparar ventas mensuales de 2 años diferentes por producto, ahora les traigo un grafico para comparar meses de muchos años. En realidad el grafico puede utilizarse de muchas formas.

Instrucciones de preparación:

1.- En primer lugar debemos tener la información ordenada y depurada para poder trabajarla. Importante! Insertar una columna entre año y año para poder hacer el tipo de grafico que deseamos. Además debemos tener 2 hojas de trabajo. Data (donde está la información) y Presentación (donde vamos a realizar el gráfico)



2.- Insertar una Barra de Desplazamiento en la hoja Presentación. Para eso nos vamos a la pestaña Desarrollador > Insertar > Cuadro Combinado:




Ahora vamos a configurar la barra de desplazamiento. Vincular la barra de desplazamiento a una celda de la hoja Data, para mi ejemplo será la celda B6:



3.- En la celda C6 vamos a jalar el mes de acuerdo al valor de la celda vinculada B6. Para eso usaremos la siguiente formula: =INDICE(C3:N3;B6).

 4.- Ahora vamos a hacer una tabla paralela en la parte inferior de nuestra tabla para poder insertar el grafico de forma más rápida y sencilla.



Abr. Mes: Es la primera letra del mes. Para eso en la celda C8 uso la formula =IZQUIERDA(C3) y lo arrastramos hasta el final de los datos.
MS% Área: Copiamos tal cual los datos de la tabla original.
MS% Línea: Copiamos tal cual los datos de la tabla original.
MS% Puntos Borde: Aquí queremos mostrar solo los valores del mes de enero y diciembre, en el resto que salga #N/A. Para eso en la celda C11 uso la formula =SI(O(C3="ENE";C3="DIC");C4;NOD()) y lo arrastramos hasta el final de los datos.
MS% Puntos Mes: Esto dependerá de la celda vinculada a la barra de desplazamiento. Para eso en la celda C12 uso la formula BUSCARH($C$6;C$3:C$4;2;0) y lo arrastramos hasta el final de los datos.
Línea 0.1: Aquí ponemos 0.1 y copiamos hasta el final de los datos. Hacemos lo mismo hasta Línea 0.9.

5.- Seleccionamos el rango B8:CN21 e insertamos un gráfico de Líneas. Copiamos el grafico y lo llevamos a la hoja Presentación.



6.- Ahora comenzamos a darle el formato a nuestro gráfico, primero eliminamos las leyendas.

7.- Clic derecho sobre el grafico, le damos Seleccionar datos > Celdas ocultas y vacías y seleccionamos Rangos.



8.- Seleccionamos la imagen, vamos a la pestaña Diseño > Cambiar tipo de grafico > Cuadro combinado y lo configuramos las series con el siguiente tipo de gráfico:

MS% Área: Área
MS% Línea: Líneas
MS% Puntos Borde: Líneas con marcadores
MS% Puntos Mes: Líneas con marcadores
Línea 0.1: Líneas. Hacemos lo mismo hasta la serie Línea 0.9.




Luego tendremos el siguiente resultado:



9.- Ya falta poco. Clic derecho sobre la imagen > Formato del área del gráfico.

MS% Área: Cambiamos el color del Relleno. En mi caso un color Celeste.
MS% Línea: Cambiamos el color de la Línea. En mi caso un Azul Claro.
MS% Puntos Borde: Formato de Línea > Sin línea. Formato de Marcador > Cambiamos el color del relleno y borde por un color parecido al de la serie MS% Línea (Azul Claro)
MS% Puntos Mes: Formato de Línea > Sin línea. Formato de Marcador > Cambiamos el color del relleno, borde y el tamaño del marcador. En mi caso lo pongo de color naranja. Agregamos etiqueta de datos y lo ponemos en la parte superior. También agregamos un elemento de gráfico llamado Barra de Error > Error típico. Lo configuramos según la imagen y le damos un color que combine (Azul claro):



Línea 0.1: Cambiamos el color de la Línea (color blanco), disminuimos el ancho (1,25) y el tipo de guion. Hacemos lo mismo para todas las demás series hasta la serie Línea 0.9.

Como resultado tendremos un gráfico así:



10.- Finalmente haremos un truco para darle mayor dinámica a la barra de desplazamiento:

Celda C22: =SI(D22="ENE";"";DESREF(Data!B3;;Data!B6-1))
Celda D22: =Data!C6
Celda E22: =SI(D22="DIC";"";DESREF(Data!B3;;Data!B6+1))
Y colocamos la barra de desplazamiento debajo de estas celdas.



11.- Ponemos todo junto y terminamos de darlos los retoque finales.



Espero que hayan disfrutado el tutorial, a ponerlo en práctica y recuerden siempre: En Excel el límite lo pones tú.

Las Gracias a http://chandoo.org/ por enseñarnos algunas técnicas valiosas. El grafico lo saque de su página y estoy seguro de que nuestros lectores encontrarán estas ideas muy útiles.

Descarga el archivo aquí: COMPARAR MESES DE MUCHOS AÑOS.