AGRUPAR EN TRAMOS CON VBA

Llevo una temporada trabajando con el denominado concepto “Big Data” y algunos programas específicos,  y lo cierto es que sí, que es una tendencia consolidada y con un gran potencial. Lo verdaderamente importante, como siempre, son las herramientas con las que vamos a analizar esa enorme cantidad de datos y la figura del analista para trasladar e interpretar los resultados.

En este sentido, Excel se enfrenta a un importante desafío, la capacidad que pueda ofrecer para asumir la gestión de grandes cantidades de datos y las herramientas con las que pueda analizar esas informaciones.

Teniendo en cuenta esto último, he decidido realizar un pequeño ejemplo donde voy a utilizar la máxima cantidad de datos en una hoja Excel y realizar varias macros para comprobar la usabilidad de la herramienta.

El ejemplo que voy a utilizar es de la agrupación de datos en tabla dinámica utilizando VBA. Para ello cuento con tres columnas de datos (Población, Edad y Provincia) y con 1.048.576 registros, que es el número máximo que admiten las versiones superiores a Excel 2003:

Con estos datos, lo que realizaremos es una agrupación por Provincia y por Edades y que cuente el total de personas en dichos tramos. Es decir, por ejemplo para Albacete, que los datos que se muestren sean estos: (Nota: los datos son ficticios, son solo válidos para un ejemplo, no es la población real de cada provincia).

Este trabajo lo vamos a realizar con la siguiente macro:

Sub AGRUPAR()
Application.ScreenUpdating = False
Sheets("AGRUPADO").Select
Sheets("AGRUPADO").Cells.Select
Selection.Delete Shift:=xlUp
'INSERTAMOS LA TABLA DINÁMICA EN LA HOJA "AGRUPADO", RANGO A1:C1048576
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"DATOS!R1C1:R1048576" & "C3", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="AGRUPADO!R1C1", TableName:="Tabla dinámica1", _
DefaultVersion:=xlPivotTableVersion14
'INCLUIMOS EDAD Y POR PROVINCIA EN LA TABLA DINÁMICA
Sheets("AGRUPADO").Select
Cells(1, 1).Select
With Sheets("AGRUPADO").PivotTables("Tabla dinámica1").PivotFields("EDAD")
.Orientation = xlRowField
.Position = 1
End With
With Sheets("AGRUPADO").PivotTables("Tabla dinámica1").PivotFields("PROVINCIA")
.Orientation = xlRowField
.Position = 1
End With
'AÑADIMOS EL CAMPO CUENTA DE POBLACIÓN AL INFORME AGRUPADO
Sheets("AGRUPADO").PivotTables("Tabla dinámica1").AddDataField ActiveSheet.PivotTables _
("Tabla dinámica1").PivotFields("POBLACION"), "Cuenta de POBLACION", xlCount
'DEFINIMOS LA EDAD MÍNIMO COMO EL ITEM MÁS BAJO DESDE EL QUE AGRUPAMOS
Minimo = Application.WorksheetFunction.Min(Worksheets("DATOS").Range("B:B"))
'AGRUPAMOS LOS DATOS EN TRAMOS DE 10 AÑOS EMPEZANDO POR EL MÍNIMO (18 AÑOS EN ESTE CASO)
Sheets("AGRUPADO").PivotTables("Tabla dinámica1").PivotSelect Minimo, xlDataAndLabel + xlFirstRow, True
Selection.Group Start:=True, End:=True, By:=10
Application.ScreenUpdating = True
End Sub

Con este código vamos lograr agrupar los datos de la forma que necesitamos, sin embargo me gustaría comentar algunos detalles que me parecen importantes:

• El origen de los datos, SourceData:= «DATOS!R1C1:R1048576» & «C3«, que escribo de esta forma cuando lo normal es que utilizase una variable para hacer referencia a los 1.048.576 registros. El motivo de no hacerlo es que en el momento en el que la variable hace referencia a más de 99.998 registros arroja un error de referencia en los datos, e impide continuar con el proceso. Por eso debemos detallar todo el rango, lo que implica que el código recorra todas las filas, y aunque en este ejemplo está correcto porque usamos toda la hoja, en caso de que tuviésemos 200.000 registros, estaríamos obligados, o bien a escribir los 200.000 registros en rango, algo que no es viable si nuestros datos varían o indicar el final de la hoja como el fin del rango, incrementando recursos y tiempo de ejecución en la macro. Con una variable detectaría los 200.000 y se ejecutaría hasta ese número.

• En una agrupación siempre debemos definir el rango con el que vamos a agrupar, en este caso, desde los 18 años a los 95 años. Pues bien, en el código quedaría así:

   Sheets("AGRUPADO").PivotTables("Tabla dinámica1").PivotSelect ("'18'")

• De forma que estaríamos obligados a cada vez que cambien las edades en nuestra base de datos y por ejemplo no existiese personas de 18 años, la macro arrojaría un error. Por ello, debemos establecer que la edad inicial es la menor de las edades:

    Minimo = Application.WorksheetFunction.Min(Worksheets("DATOS").Range("B:B"))

• Ahora sabemos que la menor de las edades es 18 años y ya podemos indicarlo así en la macro:

    Sheets("AGRUPADO").PivotTables("Tabla dinámica1").PivotSelect Minimo

• Los tramos de edad que vamos agrupar para este ejemplo es de 10 en 10 años, para establecerlo, lo podremos hacer en la siguiente línea de código:
  Group Start:=True, End:=True, By:=10
  Se puede indicar el tramo que mejor se ajuste a nuestras necesidades.

Una vez ejecutada la macro, el resultado sería el descrito en la primera imagen para todas las provincias:

Aprovechando que tenemos ese informe, se me ocurre que quizás alguien nos pueda pedir que marquemos en cada provincia el tramo de edad con más población. Y además nos sugieran que sería ideal marcar los datos en rojo.

Podemos hacerlo de diferentes formas, pero teniendo en cuenta que estamos trabajando con tablas dinámicas, creo que debemos utilizar todos los recursos que tenemos a nuestro alcance con esta herramienta.
Os dejo el código utilizado y luego lo explico:

Sub MARCAR()
'DECLARAMOS VARIABLES QUE VAMOS A UTILIZAR
Dim i As Double
final = Application.CountA(Worksheets("AGRUPADO").Range("a:a"))
Application.ScreenUpdating = False
'INCLUIMOS UN CONDICIONAL PARA BORRAR LOS DATOS MARCADOS CUANDO ACTUALICEMOS
Sheets("AGRUPADO").Select
If Sheets("AGRUPADO").Range("C1") <> Empty Then
Sheets("AGRUPADO").PivotTables("Tabla dinámica1").PivotFields("Cuenta de POBLACION2"). _
Orientation = xlHidden
End If
If Sheets("AGRUPADO").Range("a1") = Empty Then
MsgBox ("DEBES GENERAR LOS DATOS AGRUPADOS ANTES DE GENERAR EL RANKING")
Exit Sub
End If
'DUPLICAMOS EL CAMPO POBLACIÓN Y LO DENOMINAMOS POBLACIÓN2
Sheets("AGRUPADO").PivotTables("Tabla dinámica1").AddDataField ActiveSheet.PivotTables  ("Tabla dinámica1").PivotFields("POBLACION"), "Cuenta de POBLACION2", xlCount
'SOBRE EL CAMPO POBLACIÓN2 MOSTRAMOS LOS VALORES COMO RANKING
With Worksheets("AGRUPADO").PivotTables("Tabla dinámica1").PivotFields( "Cuenta de POBLACION2")
.Calculation = xlRankDecending
.BaseField = "EDAD"
End With
'SI EL VALOR ES IGUAL A 1 (MAYOR) LO MARCAMOS EN ROJO
For i = 2 To final
If Sheets("AGRUPADO").Cells(i, 3) = 1 Then
With Sheets("AGRUPADO")
.Cells(i, 3).Interior.Color = vbRed
.Cells(i, 2).Interior.Color = vbRed
.Cells(i, 1).Interior.Color = vbRed
End With
End If
Next
Application.ScreenUpdating = True
End Sub

Para empezar, lo que debemos hacer es incluir de nuevo el mismo campo «POBLACIONES en la tabla dinámica, pero que vamos a llamar POBLACIONES2 (no pueden existir dos campos con el mismo nombre):

Sheets("AGRUPADO").PivotTables("Tabla dinámica1").AddDataField ActiveSheet.PivotTables  ("Tabla dinámica1").PivotFields("POBLACION"), "Cuenta de POBLACION2", xlCount

El siguiente paso sería, sobre ese nuevo campo “Poblaciones2” mostrar los valores que cuentan a las personas, como un ranking, donde el 1 sería el valor más alto, es decir, el tramo donde la población es más elevada:

With Worksheets("AGRUPADO").PivotTables("Tabla dinámica1").PivotFields( "Cuenta de POBLACION2")
.Calculation = xlRankDecending
.BaseField = "EDAD"
End With


Una vez que contamos con este dato, solo tendríamos que marcar todos los valores “1” en rojo y destacar así la información que nos están solicitando:

For i = 2 To final
If Sheets("AGRUPADO").Cells(i, 3) = 1 Then
With Sheets("AGRUPADO")
.Cells(i, 3).Interior.Color = vbRed
.Cells(i, 2).Interior.Color = vbRed
.Cells(i, 1).Interior.Color = vbRed
End With
End If

Y este sería nuestro informe final, datos agrupados por edad y provincia y destacando el tramo de edad en cada provincia con más población.

Reflexionando, a pesar de la restricción de los registros es de 1.048.576 en Excel, es una excelente herramienta para procesar grandes cantidades de datos. Sin embargo, cuando hablamos de Big Data, hacemos referencia a millones de datos y actualmente Excel no resulta viable como alternativa para tratar ese tipo de informaciones, teniendo que acudir a otros lenguajes de programación y plataformas para realizar el trabajo.

No obstante, como complemento de análisis, ya sea de resultados resumidos o porciones asumibles, es un excelente complemento para el Gran Dato 🙂

Como siempre os dejo el archivo para facilitar su consulta y las pruebas.

Importante: Solo válido para versiones superiores a Excel 2003

Descarga el archivo de ejemplo pulsando en: AGRUPAR EN TRAMOS CON VBA