Algunas empresas recogen datos históricos para analizar desde varios años antes. Si ponemos todos los datos podemos hacer un gráfico de serie de tiempo. En algunas ocasiones, puede ser interesante comparar los datos año a año para poder ver la evolución anual y mensual. Nos puede ayudar a entender si se repite el mismo patrón. Serian las series “desestacionalizadas”. Es lo que llamaremos gráfico plurianual comparativo.
Ejemplo de datos continuos para el índice de Precios industriales según el INE http://www.ine.es y datos comparativos para los años 2014 y 2015.
¿Qué series resaltamos?
Extraemos los datos de Indice de precios industriales desde el año 1975 hasta 2016
Introducimos unas celdas que nos ayudarán a calcular el año inicial, el final y los años con datos:
introducimos las siguientes fórmulas:
En la fórmula del “año inicial de la serie” calculamos el año de la primera fecha con datos.
En la fórmula del “año final de la serie”, hay que hacerlo en tres pasos:
- Con la función “=CONTAR()” averiguamos cuántos datos tenemos.
https://excelyvba.com/funciones-contar-count-en-ingles/
2. La función “=INDICE()” nos devolverá el año final de la serie.
https://excelyvba.com/funcion-indice-en-excel/
3. La función “=AÑO()” nos convierte el valor de la función índice a formato año.
https://excelyvba.com/funcion-ano-en-excel/
A continuación damos la opción de introducir el año inicial y la cantidad de años, por ejemplo:
Construcción de la tabla para hacer el gráfico.
En primer lugar debemos convertir la serie de datos en una tabla. Esto nos permitirá mantener las fórmulas cuando tengamos que añadir nuevos datos:
https://excelyvba.com/tablas-de-datos-excel-2013/
Ahora tenemos nuestros datos en “Tabla”:
A continuación tenemos que colocar las series de datos que deberemos graficar. Para ello debemos poner en diferentes columnas los datos de los años solicitados. Para el caso que se soliciten datos de 3 años a partir del año 2010, tenemos las siguientes series de datos:
¿Cómo nos traemos los datos desde la tabla inicial de datos?
Primer paso: rellenamos en una fila los números del 1 al 10 (10 será el máximo número de series a graficar). Rellenamos en una columna los números del 1 al 12 que serán los meses. Nos servirán como referencia:
En la siguiente fila traemos los años que vamos a añadir al gráfico. Para el ejemplo que estamos desarrollando:
y lo hacemos con las siguientes fórmulas:
=F5 para el primer año. Coincide con la celda donde preguntamos por el año inicial para representar los datos.
Para el siguiente año introducimos la siguiente fórmula:
=SI.ERROR(SI((I24+1)>=($F$5+$F$6);””;I24+1);””)
Con esta fórmula verificamos si no se ha llegado al final de los años a representar. Se comprueba si el año anterior +1 (I24+1) es mayor que la suma del año inicial + años a analizar ($F$5+$F$6). Si es así el valor será “” (nada). Si no se ha llegado al final, suma 1 año al valor de la celda anterior.En caso de error, también devuelve el valor “”.
En la celda que cruza el mes 1 (enero) con la gráfica 1 introducimos la siguiente fórmula:
=SI.ERROR(BUSCARV(FECHA(I$24;$G25;1);Tabla;2;0);NOD())
Vayamos por partes:
La función FECHA(I$24;$G25;1) nos convierte en una fecha tomando como valores:
a) El año según la celda I$24. Será el mismo para toda las columnas (por eso el signo $ en el 24 que es la fila donde se indica el año)
b) El mes según la celda $G25. Será el mismo para todas las filas (por eso el signo $ en la G que es la columna donde se va indicando el mes).
c) El día 1 (vale cualquier día). Sólo nos importa la información del mes y año.
La función BUSCARV(FECHA(I$24;$G25;1);Tabla;2;0 nos busca el valor de la fecha en Tabla (conjunto de todos los valores a analizar) y nos devuelve el valor en la misma fila y en la 2ª columna. Nos devolverá el valor exacto al indicarlo con un 0.
Posteriormente arrastramos (o copiamos) la fórmula al resto de la tabla de valores.
Si queremos que no muestre los valores #NA, le aplicamos un formato condicional:
https://excelyvba.com/anadir-formato-condicional/
Nos quedará el siguiente conjunto de valores:
Construcción del gráfico.
Ahora ya podemos insertar el gráfico con los valores de las celdas anteriores:
https://excelyvba.com/crear-un-grafico-en-excel/
Si es necesario, podemos “maquillar” el gráfico para que se entienda mejor: colores, color de texto, fondo de texto, etc.
Ejercicio nivel avanzado.
En este tutorial se ha explicado cómo hacer un gráfico comparando los datos anuales para años consecutivos. Dejo como ejercicio cómo se podría hacer si dejamos que el usuario pueda elegir años alternos, no seguidos. Se puede hacer con VBA (listbox) o simplemente que el usuario escriba los años en las celdas correspondientes (ya se me ha escapado una pista).
Agradezco de antemano vuestros comentarios. No dudéis en escribirme para cualquier consulta, aclaración que necesitéis.
Bonus track: descarga este ejemplo
Aquí os dejo el archivo que hemos utilizado como base para este tutorial. Regálanos un “like” en Facebook y podrás descargarlo totalmente gratis.
[sociallocker]
Convertir grafica plurianual en comparativa anual
[/sociallocker]