Abra el archivo Comercial Center.xls, haciendo clic aquí

En la sección anterior hicimos dos macros relativos a una tabla dinámica:

- Grabar una macro, que luego comprobamos que sirve sólo si la hoja creada (sea Hoja1, Hoja2, Hoja3, etc.) existe y está vacía. Deficiencia encontrada en las versiones superiores al Excel 2007.

- Modificar dicha macro de forma que se pueda usar cuantas veces quiera y no importando en qué hoja se creó la tabla anterior.

Dejamos para esta sección, modificar la macro de forma que se pueda generar estando en cualquier hoja.

Pero vamos a hacer algo más: Vamos a modificar dicha macro de forma que genere una tabla dnámica básica estando en cualquier hoja y con cualquier tabla existente en dicho libro. Más aún, esta macro va a generar una tabla dinámica básica desde cualquier libro abierto o cerrado, siempre que cumpla algunas condiciones como lo vamos a indicar.

¿Por qué una tabla dinámica básica?

Todos sabemos que, al generar una tabla dinámica, podemos añadirle otros campos categóricos sea en fila o columna e incluso como pestaña o cabecera o página. Del mismo modo, podemos añadirle más campos cuantitativos sobre los cuales deseamos un resumen.

Nuestra macro, siendo general, obtendrá estas tablas básicas y a partir de ella, el usuario podrá añadir o quitar campos categóricos o de resumen.

Nota 1:

Puesto que la macro trabajará con datos de cualquier libro y de cualquier hoja, la vamos a codificar en un libro nuevo. Ante todo, lo vamos a grabar como TablasDinamicas.xlsm, habilitado para macro.

Nota 2:

La tabla o base de datos desde la cual se desea generar la tabla dinámica, debe tener nombre de rango. Se ingresará por teclado. Estos rangos deben incluir la cabecera o nombre de columna o nombre de campo. La hoja donde ella se encuentra, será ingresada por teclado. De preferencia (no es indispensable), la cabecera o nombre de columnas deben ser una sola palabra; por ejemplo: MontoTotal, GrdoInst, FechaCompra, etc.

Nota 3:

Se debe tener claro los nombres de los campos (botones) categóricos que se colocarán en el área de fila de la tabla y el orden que se desee de ellas; por ejemplo: Primero Zona después Tienda, o Primero Tienda luego FormaPago. Si se desea al revés, luego de ejecutarse la macro se podrá modificar el orden de presentación.

Nota 4:

La macro que vamos a codificar, usará, a lo más, dos campos en fila y un campo de resumen. Los nombres de estos campos (nombre en la cabecera de columna) se ingresarán por teclado cuando la macro lo pida.

Estando en este libro vacío que contedrá nuestra macro, nos vamos al Editor del VBA e insertamos un módulo.

Nuestra macro será la siguiente:

Sub TablaDin()
………….
End Sub

Al interior de ella deberemos realizar las siguientes acciones:

- Abrir el libro donde se encuentran los datos.

- Seleccionar la hoja que contiene los datos. El nombre se leerá desde el teclado.

- Se pedirá el número de campos a colocarse en el área de fila. Nuestro procedimiento aceptará un máximo de dos campos; es decir, se digitará 1 ó 2. Por supuesto que se puede usar para 3 o más, pero esos son casos poco raros. Si así fuera, después de generada la tabla se podrá aumentar o cambiar el orden. Por eso lo hemos llamada Tabla dinámica básica.

- En un loop se ingresará por teclado, el o los nombres de campos que van a fila.

- Se ingresará por teclado el nombre del campo para sumarizar. Claro que se pudo haber pedido más de uno, pero después se puede añadir.

- Usaremos la sentencia Select Case para ingresar el o los campos.

En la hoja (Hoja1) vacía de este libro insertaremos un botón de comando que nos permita ejecutar esta macro.

He aquí la macro debidamente comentada:

Sub TablaDin()

'
' Este procedimiento también genera una tabla dinámica
' pero a diferencia de la anterior, ésta podrá generar
' una tabla dinámica para cualquier tabla o base de datos
' Para ello, el rango de datos y los campos usados en la tabla
' básica tendrán nombre de rango
' Para ello, estos nombres de rango serán leídos desde el teclado
'
' Abriremos el libro que contiene los datos
fName = Application.GetOpenFilename
Workbooks.Open fName
' Se ingresa el nombre de la hoja
nHoja = Trim(InputBox("Nombre de la hoja"))
' Activamos dicha hoja
Sheets(nHoja).Select
' Ingresamos el nombre del rango de datos
RangoDatos = Trim(InputBox("Ingresa el nombre de rango de los datos"))
' Usaremos para uno o dos campor pivot por fila
nC = Val(InputBox("Número de campos en el área de fila" + Chr(13) + _

"máximo dos campos o nombres de columna")) '
' Por comodidad sólo usaremos un campo numérico
Select Case nC
Case 1:
nCampoFila1 = Trim(InputBox("Nombre de la columna"))
Case 2:
nCampoFilab = Trim(InputBox("Nombre de la primera columna"))
nCampoFilac = Trim(InputBox("Nombre de la segunda columna"))
End Select
nCampoCol = Trim(InputBox("Nombre de la columna numérica" + Chr(13) + _
"para realizar el resumen"))
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
RangoDatos, Version:=6).CreatePivotTable TableDestination:= _
"", TableName:="TablaDinámica1", DefaultVersion:=6
'
Cells(3, 1).Select
Select Case nC
Case 1:
With ActiveSheet.PivotTables("TablaDinámica1").PivotFields(nCampoFila1)
.Orientation = xlRowField
.Position = 1
End With
Case 2:
With ActiveSheet.PivotTables("TablaDinámica1").PivotFields(nCampoFilab)
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("TablaDinámica1").PivotFields(nCampoFilac)
.Orientation = xlRowField
.Position = 2
End With
End Select
ActiveSheet.PivotTables("TablaDinámica1").AddDataField ActiveSheet.PivotTables( _
"TablaDinámica1").PivotFields(nCampoCol), "Suma de " + nCampoCol, xlSum
Range("D8").Select
End Sub
 

inalmente, vamos a insertar un botón en la primera hoja:

La secuencia es:

[Desarrollador en 2016, Programador en versiones anteriores] – [Controles] – [Insertar] – [Botón (Control de formulario)] . Trazar un rectángulo en una parte de la hoja, digamos en G3. Al soltar, haga clic en [TablaDin] y de inmediato digite “Genera tabla dinámica”. Usando los controles del botón extienda horizontalmente hasta que quepe el texto en el botón.

Recuerde que, el rango de los datos desde donde se construirá la tabla dinámica, debe tener un nombre de rango que incluya la cabecera. Y los datos deben ser realmente una tabla o base de datos sin filas vacías o de subtotales, sin columnas vacías, etc.

Antes de ejecutar la macro, volvemos a grabar el libro.

Haga clic en el botón para ejecutar la macro y “alas y buen viento”.

Para que pueda probar la macro, en la siguiente dirección encontrará el libro Ventas por tienda.xlsx, haciendo clic aquí. Los datos tienen nombre de rango el mismo del nombre de la hoja, precedido por la letra T, por ejemplo, TMegaPlaza.

La siguiente imagen muestra este procedimiento.

Vaya a la siguiente sesión haciendo aquí