Abra el archivo Comercial Center.xls, haciendo clic aquí
Una tabla dinámica es uno de las tablas de mayor utilidad en el uso del Excel cuando se trata de presentar un reporte resumen de una gran tabla.
Anteriormente ya hemos visto el uso de esta herramienta. Ahora se trata de codificar un procedimiento para generar una tabla dinámica base, a partir de la cual, podemos, en el momento, obtener diversas formas de presentar los datos, como ya lo hemos indicado y lo sabe Usted.
Vamos a dividir este tema en tres casos:
a) Grabar una macro que genere una tabla dinámica y reconocer el motivo por qué falla
b) Corregir esta falla y ejecutarla cuantas veces se quiera
c) Codificarla de forma que se ejecute desde cualquier hoja, no necesariamente desde la hoja que contiene los datos.
Luego de abrirlo, ubíquese en la hoja Ventas.
Caso a)
Volvamos al principio de macros: Vamos a grabar una macro que genere una tabla dinámica:
- Estando en la hoja Ventas del archivo ComercialCenter.xlsx, observe el rango de los datos: Empieza en la fila 10 y no usa la columna A. De manera que el rango de los datos deberá ser A10:O361.
- Dejando el cursor al interior de este rango, use la siguiente secuencia para grabar macros: [Vista] – [Macros] - [Macro] - [Grabar macro … ].
En la ventana que salga: Nombre: MacTablaDin, no le ponga método abreviado. [Aceptar].
Empezamos a grabar.
- Ahora use la secuencia: [Insertar] – [Tabla dinámica]. Observe que el rango que aparece sea el que hemos indicado arriba. Si no fuera igual, digite dicho rango. Deje todo el resto como está y haga clic en [Aceptar].
- Usemos los botones de campo de la ventana del lado derecho; vamos a diseñar una tabla muy simple: Haga clic en el botón [Zona] o arrástrelo hacia el área [Filas] de la parte inferior. Haga lo mismo con el botón [Tienda] y también con [FormaPago]. Si arrastra los botones debe dejarlo uno debajo del otro. Si desea otra forma de presentarlo, insértelo entre los otros o muévalos hacia arriba o abajo. Ahora haga clic en Cantidad y Monto Total. Los dos deben aparecer en el área de
- Es suficiente para una prueba. Ahora haga clic en el cuadradito que aparece en la barra de estado para detener la grabación, donde dice [Listo] o [Ready]. Otra forma de detener la grabación es usar la secuencia [Vista] – [Macro] – [Macros} – [Detener grabación].
Como puede apreciar, el resultado del procedimiento es correcto. Podemos hacer lo que querramos con dicha tabla.
Grabe su libro como habilitado para macros, para conservar la macro que hemos creado.
Vamos al Editor. En la ventana de Proyectos ubique la carpeta Módulos, haga doble clic y luego doble clic en el Móduo1.
Trate de entender lo que hace: El procedimiento MacTablaDin contiene 10 líneas o instrucciones: Primero añade una nueva hoja. Usando el libro activo, crea una taba pivot usando el rango B10:O361 de la hoja Ventas, sólo que lo maneja en formato de “Ventas!Fila10Col2:Fila361ColO”. El destino de la tabla es Fila3Col1 (A3) de la hoja “Hoja1”. Teniendo seleccionado esta celda (A3), usa With para insertar el botón Zona en el campo de fila y en la posición 1. Del mismo modo, usa otro With para insertar los botones Tienda y FormaPago en el campo Fila, en las posiciones 2 y 3, respectivamente. Ahora usa la octava y novena instrucción para insertar los campos numéricos Cantidad y Monto total en el campo Suma de valores o campo pivot.
Finalmente deja el cursor en la celda D8, que no es indispensable.
Bien.
Ahora, estando en la hoja Ventas, ejecute la macro sea desde el editor o desde el Excel usando la secuencia [Vista] – [Macro] – [Macros] – [Ver macros] - Seleccionar MacTablaDin y [Aceptar].
Ooops !!! Falló, no?
Bueno, esto es lo que viene ocurriendo con estas macros con todas la versiones del Excel posteriores a la versión 2007.
¿Por qué falla?
Volvamos al Editor. La primera línea que añade una nueva hoja, añadirá la Hoja2, Hoja2, etc. Sin embargo, la macro va a generar la tabla en la Hoja1. Esto lo querrá hacer cada vez que ejecutemos la macro.
¿Cómo vamos a corregir esto?
Seleccione toda la Hoja1 y elimine su contenido. Que se quede vacía. Ahora, estando en Ventas, ejecute la macro.
Caso b)
Nosotros vamos a corregir de otra forma, de manera que podemos ejecutar la macro cuantas veces querramos, con la única condición (en el caso c) lo resolveremos) de que la hoja activa debe ser Ventas.
Vayamos al Editor. Editemos el procedimiento MacTablaDin.
Correcciones que vamos a hacer, con mucho cuidado:
- Convierta en comentario la primera línea que dice: Sheets.Add. Para ello, digite apóstrofe ( ‘ ) al inicio de dicha línea y compruebe que está en verde.
- Donde dice: "Hoja1!R3C1", borre el contenido y que sólo quede: “”
- Convierta a: Sheets("Hoja1").Select en un comentario; para ello inserte el apóstrofe ( ‘ ), para que se convierta en una línea verde. Si no puede elimine la línea.
Nada más
Vuelva a grabar su libro, habilitado para macros.
Active la hoja Ventas y ejecute la macro cuantas veces desee.
Y si no desea ubicarse siempre en Ventas, en la macro, la primera instrucción debe ser: Sheets(“Ventas”).Select y ahora puedes estar en cualquier hoja.
Finalmente,
El caso c) lo resolveremos en la siguiente sección.
La macro que hemos comentado es la siguiente:
Sub MacTablaDin()
'
' MacTablaDin Macro
'
'
Sheets("Ventas").Select
' Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Ventas!R10C2:R361C15", Version:=6).CreatePivotTable TableDestination:= _
"", TableName:="TablaDinámica1", DefaultVersion:=6
' Sheets("Hoja1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("TablaDinámica1").PivotFields("Zona")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("TablaDinámica1").PivotFields("TIENDA")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("TablaDinámica1").PivotFields("Forma Pago")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("TablaDinámica1").AddDataField ActiveSheet.PivotTables( _
"TablaDinámica1").PivotFields("CANTIDAD"), "Suma de CANTIDAD", xlSum
ActiveSheet.PivotTables("TablaDinámica1").AddDataField ActiveSheet.PivotTables( _
"TablaDinámica1").PivotFields("MONTO TOTAL"), "Suma de MONTO TOTAL", xlSum
Range("D8").Select
End Sub
Nota:
Si desea ver la macro que genera tabla dinámica desde cualquier libro que tenga una hoja con datos que cumplan con la definición de base de datos (tabla), vea la sesión 085. Para aprender o revisar macros y formulario, espere las otras partes. Y, para aprender o revisar el Excel completo, vea todas las partes anteriores, en este mismo lugar.
La siguiente imagen muestra este procedimiento.
Vaya a la siguiente sesión haciendo aquí