Ediciones ENI Editorial | líder en informática
Tablas dinámicas y VBA
Introducción
El objetivo de este capítulo no es el de convertirle en desarrollador VBA para Excel, pero si necesita automatizar tareas para personas que no dominan Excel, las macros y procedimientos VBA pueden serle de gran utilidad.
Dispone de dos métodos para crear macros:
-
Guardar primero la macro para editarla después.
-
Escribir directamente la macro en Visual Basic.
El primer método deja a Excel la tarea de escribir el texto de la macro, lo que evita los errores de sintaxis y evita que el usuario tenga que dominar el lenguaje Visual Basic.
El segundo método exige que el usuario domine el lenguaje Visual Basic.
En primer lugar, vamos a poner en práctica el primer método.
Abra el libro Mediciones.xlsx.
Cada fila de la hoja Lista contiene el nombre de mediciones realizadas por una persona en un centro industrial. Las hojas ListaCentroB y ListaCentroC contienen la misma información para los otros dos centros.
Se han creado tres rangos dinámicos, cada uno relativo a la hoja de un centro.
Para familiarizarse con la sintaxis VBA relativa a las tablas dinámicas, vamos a crear una tabla dinámica dejando que Excel grabe nuestras acciones.
Crear una tabla dinámica con la ayuda de una macro
1. Active la pestaña Desarrollador
Si no ha trabajado nunca con la pestaña Desarrollador, actívela:
Haga clic derecho en la cinta y haga clic en la opción Personalizar la cinta de opciones.
En la categoría Personalizar cinta de opciones, en la lista Pestañas principales, active la casilla Desarrollador y haga clic en Aceptar.
A la derecha de la pestaña Vista, se ha añadido la pestaña Desarrollador.
2. Grabar la macro
Durante la grabación de una macro, Excel transcribe todas nuestras acciones al lenguaje VBA. Si comete algún error durante la grabación, este error se grabará y se reproducirá en cada ejecución de la macro.
Tenga cuidado en la fase de grabación, vaya paso a paso.
Para que la macro pueda grabar la ubicación del cursor en la hoja correcta, en primer lugar haga clic en una celda de la hoja Lista.
En la pestaña Desarrollador, grupo Código, haga clic en el botón Grabar macro.
Introduzca el nombre de la macro en el cuadro de diálogo y haga clic en Aceptar.
El atajo de teclado le permitirá más tarde ejecutar rápidamente la macro. La descripción permite explicar qué hace la macro.
A partir de ahora, Excel graba todas sus acciones. Fíjese en la esquina inferior izquierda de su pantalla en el botón de detener grabación.
Haga clic en la celda A3 de la hoja TABDINAM.
En la pestaña Insertar, grupo Tablas, haga clic en el botón Tabla dinámica e introduzca el nombre del rango dinámico...
Modificar el rango de origen con una macro
Para que la macro pueda grabar la inserción del cursor en la tabla dinámica, haga clic en una celda de otra hoja que no sea TABDINAM.
En la pestaña Desarrollador, grupo Código, haga clic en Grabar una macro.
Introduzca el nombre de la macro ModifOrigen en el cuadro de diálogo y haga clic en aceptar.
A partir de ahora, Excel graba todas sus acciones.
Seleccione la hoja TABDINAM y haga clic en una celda de la tabla dinámica.
En la pestaña Herramientas de tabla dinámica - Analizar, grupo Datos, haga clic en el botón Cambiar origen de datos e introduzca el nombre del rango dinámico correspondiente al segundo centro:
Haga clic en Aceptar.
Haga clic en G1 para salir de la tabla dinámica.
Detenga la grabación.
A continuación, podemos ver el contenido de la macro:
Sub ModifOrigen()
' ModifOrigen Macro
'
Sheets("TABDINAM").Select
Range("B6").Select
ActiveSheet.PivotTables("Tabla Dinámica 1").PivotSelect "EMPLEADO[All]", _
xlLabelOnly + xlFirstRow, True
ActiveSheet.PivotTables("Tabla Dinámica1").ChangePivotCache _
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase...Vincular los filtros de diferentes tablas dinámicas
Cuando se crean varias tablas dinámicas a partir de un mismo origen de datos y estas se filtran a través del mismo campo, es posible sincronizar los filtros de todas las tablas.
El objetivo es que la modificación del filtro de la primera tabla dinámica repercuta automáticamente en el resto.
El libro Incidencias.xlsx nos va a permitir poner en práctica esta técnica. Este recoge las averías ocurridas en una fábrica. El campo COSTE MO corresponde al coste de la mano de obra.
Se han creado tres tablas dinámicas:
1. Duración total mensual de paradas por servicio (TD1)
2. Coste total mensual de piezas por servicio (TD2)
3. Coste total mensual de la mano de obra por servicio (TD3)
Cada tabla dinámica utiliza la función Suma y utiliza como filtro el tipo de avería.
Estas tres tablas dinámicas se han creado en la hoja TD.
Se ha modificado el nombre de las tres tablas: TD1, TD2 y TD3. (Clic derecho en la tabla dinámica, Opciones de tabla dinámica...).
A los números de la primera tabla se les ha aplicado el formato horario que permite exceder las 24 horas (sin los segundos).
Nuestro proceso se debe ejecutar cuando se actualicen las cálculas de la hoja al modificar una celda (opción de cálculo automática activada).
Ahora podemos crear la macro....
Complementos
Si tiene que crear otras macros, utilice siempre para empezar el modo de grabación, lo que le permitirá obtener fácilmente la sintaxis Visual Basic de las diferentes funcionalidades de las tablas dinámicas. Así podrá obtener y modificar el código para filtrar, agrupar, modificar los formatos…
A continuación, le mostramos algunos ejemplos de código.
No mostrar la lista de campos
ActiveWorkbook.ShowPivotTableFieldList = False
Dar formato a los valores de la tabla en euros
With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields( _
"Suma de IMPORTE")
.NumberFormat = "#.##0,00 €"
End With
Actualizar una tabla dinámica
ActiveSheet.PivotTables("Tabla dinámica1").PivotCache.Refresh
Actualizar todas las tablas dinámicas
ActiveWorkbook.RefreshAll
Eliminar los totales de filas
ActiveSheet.PivotTables("Tabla dinámica1").ColumnGrand = False
Eliminar los totales de columnas
ActiveSheet.PivotTables("Tabla dinámica1").RowGrand = False
Mostrar el total de columnas y ocultar el total de filas
With ActiveSheet.PivotTables("Tabla dinámica1")
.ColumnGrand = False ...
