Hace unos días me bajé desde el INE una estadística con los datos de población en España por provincias desde 1998. Lo que en un principio era una tarea para comprobar unos datos, resultó ser el tema mi próxima entrada.

Cuando vi los datos, lo primero que se me ocurrió fue generar un gráfico y comparar todas las tendencias en el mismo cuadro, pero luego consideré la posibilidad de usar minigráficos para ver la evolución por provincia de forma individual al final de cada una de las filas, como sabéis, la ventaja de los minigráficos es que los puedes generar en una sola celda.

Pero después de pensarlo, me pregunté si podría automatizar esa tarea, es decir, mediante vba hacer posible que se generase automáticamente un minigráfico al final de cada linea de datos. Y lo logré  🙂

Veamos antes de nada la tabla con los datos y el lugar en donde se generarán los minigráficos:

Así es, los minigráficos irán al final de cada línea, es ahí donde representaremos la evolución poblacional de cada provincia. El problema lo encontraremos cuando queramos asignar una variable que marque el rango de los datos necesarios para generar el gráfico, dado que el proceso debería recorrer cada fila, captar el rango adecuado y luego generar el gráfico automáticamente hasta el final.

Para que podáis ver las modificaciones necesarias para realizar la tarea propuesta, este sería el código si insertamos un minigráfico y grabamos el proceso:

Sub MacroGrabada()
Range("S2").Select
Range("$S$2").SparklineGroups.Add _
Type:=xlSparkColumn, SourceData:="B2:R2"
End Sub

Para poder convertirlo en algo que funcione automáticamente, en primer lugar debemos definir la longitud de los datos, es decir contar cuantas celdas con datos existen desde “A” hasta el final. Lo siguiente es resolver lo más complicado, ¿cómo introducir la variable en “SourceData”?. Y es aquí donde he tenido que realizar varias pruebas porque las técnicas que empleo habitualmente con SQL, Tablas Dinámicas …etc no funcionaban.

La solución os la presento con el código completo, que es como mejor se puede ver:

Sub GenerarMinigrafico()
'Declaramos las variables necesarias
Dim i As Double
'Verificamos la longitud de los datos
Final = Application.CountA(Worksheets("EJEMPLO").Range("a:a"))
'Borramos cualquier gráfico creado cada vez que active el código
Columns("S:S").Select
Selection.SparklineGroups.Clear
Range("S1").Select
'Comenzamos la variable desde la línea dos hasta el final, evitando los encabezados
For i = 2 To Final
'Definimos el rango y lo combinamos con la variable inicial
rango = "B" & i & ":" & "R" & i
'Creamos el minigráfico en la última celda "S"
If Worksheets("EJEMPLO").Cells(i, 1) <> "" Then
Worksheets("EJEMPLO").Range("S" & i).SparklineGroups.Add _
Type:=xlSparkColumn, SourceData:=rango
End If
Next
End Sub

Así es como se introduce una variable en este tipo de gráficos, básicamente componemos el rango y relacionamos la primera columna y la variable con la última columna y la variable, de forma que el código siempre va a saber cuál es el rango de la fila que va indicando la variable “i”.

El resultado es muy visual, pero sobre todo práctico, ideal para este tipo de datos o los relacionados con índices bursátiles:

Aquí finaliza el ejercicio de hoy, espero que os haya resultado curioso y que lo tengáis en cuenta para implementarlo en vuestras hojas de trabajo.

Importante: Los minigráficos se introducen en Excel a partir de la versión 2010, por lo tanto en versiones anteriores no se guardarán. Es necesario que la hoja sea .xlsm, pero dado que en WordPress no se puede subir este tipo de archivos, lo subo como xls en modo de compatibilidad para que conserve la macro. Lo único que tendréis que hacer es abrirlo y guardarlo como xlsm en vuestros equipos. De todas formas lo podréis ejecutar si vuestra versión de excel es 2010 o superior, pero al guardarlo, si no lo hacéis como xlsm borrará los minigráficos creados.

Descarga el archivo de ejemplo pulsando en: AUTOMATIZAR MINIGRAFICOS