Si eres una persona que crea constantemente tablas dinámicas para el análisis de datos, es altamente recomendable utilizar una macro para automatizar la creación de una tabla dinámica de manera que puedas mejorar tu productividad en Excel.
La creación y manipulación de una tabla dinámica desde código VBA es un tema que puede ser muy extenso, así que es necesario determinar el alcance para este artículo. Al final de nuestro ejercicio sabrás cómo crear una tabla dinámica en una nueva hoja de Excel, cuyos datos de origen se encuentren en una tabla de Excel.
He dividido el proceso en varios pasos de manera que sea más fácil explicar el desarrollo de la macro:
- Insertar una nueva hoja de Excel
- Crear el Pivot Cache
- Crear la Tabla Dinámica en blanco
- Insertar columnas a la tabla dinámica
- Insertar filas a la tabla dinámica
- Insertar valores a la tabla dinámica
La siguiente imagen muestra la tabla que utilizaré como los datos de origen para la tabla dinámica.
La tabla tiene el nombre Tabla1 y está en la hoja Datos. Dicha tabla tiene más de 30 mil registros y la utilizaremos para crear un reporte que me permitirá visualizar rápidamente las ventas por cada una de las zonas en los diferentes meses del año.
La macro se llamará CrearTablaDinamica y colocaré su código en un nuevo módulo que he creado para ese propósito:
A continuación detallaré cada parte de la macro y al final encontrarás el enlace para descargar el archivo que contiene todo el código.
Insertar una nueva hoja de Excel
En el primer paso del proceso crearemos la hoja llamada TablaDinamica donde posteriormente insertaré la nueva tabla dinámica. Pero antes de insertarla utilizaré el método Delete de la colección Worksheets para eliminar la hoja en caso de que exista previamente.
'Eliminar Hoja (Si Existe)
On Error Resume Next
Worksheets("TablaDinamica").Delete
'Crear Hoja TablaDinamica
Worksheets.Add(Before:=ActiveSheet).Name = "TablaDinamica"
El método Add es el encargado de agregar la nueva hoja y la colocará antes de la hoja activa. La propiedad Name me ayuda a indicar el nombre de la hoja insertada.
Crear el Pivot Cache
Las tablas dinámicas en Excel tienen un caché, que es conocido por su nombre en inglés como Pivot Cache, y es un objeto que tiene una réplica de los datos y que se encuentra en la memoria del computador.
Este objeto en memoria (Pivot Cache) hace que las tablas dinámicas respondan de una manera rápida y eficiente al analizar los datos. Para crear la Pivot Cache utilizamos la siguiente instrucción:
'Crear Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, SourceData:="Tabla1")
Observa que el parámetro SourceData especifica el nombre de la tabla de Excel que contiene los datos y que para nuestro ejemplo es la Tabla1.
Crear la Tabla Dinámica en blanco
Ahora que hemos creado el Pivot Cache, podemos crear una nueva tabla dinámica que quedará asociada a dicho cache. La instrucción que utilizaremos es la siguiente:
'Crear Tabla dinámica Set TDinamica = PCache.CreatePivotTable( _ TableDestination:="TablaDinamica!R3C1", TableName:="Tabla dinámica1")
El parámetro TableDestination indica la ubicación de la nueva tabla dinámica, que para nuestro ejemplo será la hoja TablaDinamica en la celda R3C1, que es la referencia R1C1 para la celda A3. El parámetro TableName nos permite indicar el nombre de la nueva tabla dinámica.
Si ejecutáramos el código que llevamos hasta ahora, tendríamos una hoja de Excel con una tabla dinámica en blanco como se muestra en la siguiente imagen:
En los siguientes pasos agregaremos las columnas, filas y valores a la tabla dinámica que hemos creado.
Insertar columnas a la tabla dinámica
Para insertar una columna a la tabla dinámica recién creada, utilizaremos el método PivotFields indicando el nombre del campo que vamos a insertar.
'Insertar columnas
With TDinamica.PivotFields("Zona")
.Orientation = xlColumnField
.Position = 1
End With
La diferencia entre una columna y una fila dentro de la tabla dinámica lo hace el valor de la propiedad Orientation que para una columna deberá ser xlColumnField.
Insertar filas a la tabla dinámica
Para nuestra tabla dinámica de ejemplo, insertaré las filas Año y Mes utilizando el siguiente código:
'Insertar filas
With TDinamica.PivotFields("Año")
.Orientation = xlRowField
.Position = 1
End With
With TDinamica.PivotFields("Mes")
.Orientation = xlRowField
.Position = 2
End With
Observa el valor de la propiedad Orientation que tiene el valor xlRowField que convierte a ambos campos en filas para nuestra tabla dinámica.
Ya que tendré dos filas, es importante indicar la posición que tendrá cada una de ellas. En este caso, la columna Año tendrá la primera posición y la columna Mes la segunda para tener un resultado como el siguiente:
Insertar valores a la tabla dinámica
Lo único que falta en nuestra tabla dinámica son los valores y también los podemos insertar con el método PivotFields pero debemos cambiar el valor de la propiedad Orientation para que su valor sea xlDataField.
Además, podemos indicar algunas propiedades adicionales como la propiedad Function que nos ayudará a indicar el tipo de cálculo que será aplicado a los valores de la tabla dinámica. Para nuestro ejemplo, utilizaremos la suma indicada por el valor xlSum.
'Insertar Valores
With TDinamica.PivotFields("Monto")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.NumberFormat = "#,##0"
End With
También puedes ver en el código la propiedad NumberFormat que me permite indicar el formato de los valores.
Ya hemos completado el código de las partes principales de una tabla dinámica y al ejecutar la macro obtendremos el resultado siguiente:
Este ejemplo nos ha ayudado a crear una tabla dinámica con código VBA. Te sugiero descargar el archivo de trabajo donde encontrarás el código completo de la macro y además encontrarás algunas indicaciones adicionales para cambiar el código y utilizar la macro con tus propios datos.
Además, la hoja Datos tiene un botón llamado “Crear tabla dinámica” donde podrás hacer clic para ejecutar la macro que acabamos de crear.