- Usar GetPivotData para obtener un valor
- Crear una tabla dinámica en una hoja
- Crear una tabla dinámica en una hoja nueva
- Agregar campos a la tabla dinámica
- Cambio del diseño del informe de la tabla dinámica
- Eliminar una tabla dinámica
- Dar formato a todas las tablas dinámicas en un libro de trabajo
- Eliminar campos de una tabla dinámica
- Crear un filtro
- Actualizar su tabla dinámica
Este tutorial demostrará cómo trabajar con tablas dinámicas usando VBA.
Las tablas dinámicas son herramientas de resumen de datos que puede utilizar para extraer información clave y resúmenes de sus datos. Veamos un ejemplo: tenemos un conjunto de datos de origen en las celdas A1: D21 que contiene los detalles de los productos vendidos, que se muestran a continuación:
Usar GetPivotData para obtener un valor
Suponga que tiene una tabla dinámica llamada PivotTable1 con Ventas en el campo Valores / Datos, Producto como campo Filas y Región como campo Columnas. Puede utilizar el método PivotTable.GetPivotData para devolver valores de tablas dinámicas.
El siguiente código devolverá $ 1,130.00 (las ventas totales para la región este) de la tabla dinámica:
| 1 | MsgBox ActiveCell.PivotTable.GetPivotData ("Ventas", "Región", "Este") |
En este caso, Sales es el "DataField", "Field1" es la Región y "Item1" es East.
El siguiente código devolverá $ 980 (las ventas totales del Producto ABC en la región norte) de la tabla dinámica:
| 1 | MsgBox ActiveCell.PivotTable.GetPivotData ("Ventas", "Producto", "ABC", "Región", "Norte") |
En este caso, Ventas es el “Campo de datos”, “Campo1” es Producto, “Artículo1” es ABC, “Campo2” es Región y “Artículo2” es Norte.
También puede incluir más de 2 campos.
La sintaxis de GetPivotData es:
GetPivotData (Campo de datos, Campo1, Objeto 1, Campo2, Item2… ) dónde:
| Parámetro | Descripción |
|---|---|
| Campo de datos | Campo de datos como ventas, cantidad, etc. que contiene números. |
| Campo 1 | Nombre de un campo de columna o fila de la tabla. |
| Objeto 1 | Nombre de un artículo en el campo 1 (opcional). |
| Campo 2 | Nombre de un campo de columna o fila en la tabla (Opcional). |
| Ítem 2 | Nombre de un artículo en el campo 2 (opcional). |
Crear una tabla dinámica en una hoja
Para crear una tabla dinámica basada en el rango de datos anterior, en la celda J2 de la Hoja1 del libro de trabajo activo, usaríamos el siguiente código:
| 1234567891011 | Hojas de trabajo ("Hoja1"). Celdas (1, 1) .SeleccionarActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"Sheet1! R1C1: R21C4", Versión: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet1! R2C10", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Hojas ("Hoja1"). Seleccione |
El resultado es:
Crear una tabla dinámica en una hoja nueva
Para crear una tabla dinámica basada en el rango de datos anterior, en una nueva hoja, del libro de trabajo activo, usaríamos el siguiente código:
| 12345678910111213 | Hojas de trabajo ("Hoja1"). Celdas (1, 1) .SeleccionarSábanas.AñadirActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"Sheet1! R1C1: R21C4", Versión: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet2! R3C1", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Hojas ("Hoja2"). Seleccione |
Agregar campos a la tabla dinámica
Puede agregar campos a la tabla dinámica recién creada llamada PivotTable1 según el rango de datos anterior. Nota: La hoja que contiene su tabla dinámica debe ser la hoja activa.
Para agregar Producto al campo Filas, usaría el siguiente código:
| 123 | ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Producto"). Orientación = xlRowFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Producto"). Posición = 1 |
Para agregar Región al campo Columnas, usaría el siguiente código:
| 123 | ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Región"). Orientación = xlColumnFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Región"). Posición = 1 |
Para agregar Ventas a la Sección de Valores con el formato de número de moneda, usaría el siguiente código:
| 123456789 | ActiveSheet.PivotTables ("PivotTable1"). AddDataField ActiveSheet.PivotTables (_"PivotTable1"). PivotFields ("Ventas"), "Suma de ventas", xlSumCon ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Suma de ventas").NumberFormat = "$ #, ## 0.00"Terminar con |
El resultado es:
Cambio del diseño del informe de la tabla dinámica
Puede cambiar el diseño del informe de su tabla dinámica. El siguiente código cambiará el diseño del informe de su tabla dinámica a forma tabular:
| 1 | ActiveSheet.PivotTables ("PivotTable1"). TableStyle2 = "PivotStyleLight18" |
Eliminar una tabla dinámica
Puede eliminar una tabla dinámica usando VBA. El siguiente código eliminará la tabla dinámica llamada PivotTable1 en la hoja activa:
| 12 | ActiveSheet.PivotTables ("PivotTable1"). PivotSelect "", xlDataAndLabel, TrueSelection.ClearContents |
Dar formato a todas las tablas dinámicas en un libro de trabajo
Puede formatear todas las tablas dinámicas en un libro de trabajo usando VBA. El siguiente código usa una estructura de bucle para recorrer todas las hojas de un libro de trabajo y eliminar todas las tablas dinámicas en el libro de trabajo:
| 12345678910111213 | Sub FormattingAllThePivotTablesInAWorkbook ()Dim wks como hoja de trabajoDim wb como libro de trabajoEstablecer wb = ActiveWorkbookDim pt como tabla dinámicaPara cada semana en wb.Para cada punto en semanas.pt.TableStyle2 = "PivotStyleLight15"Siguiente ptPróximas semanasEnd Sub |
Para obtener más información sobre cómo usar Loops en VBA, haga clic aquí.
Eliminar campos de una tabla dinámica
Puede eliminar campos en una tabla dinámica usando VBA. El siguiente código eliminará el campo Producto en la sección Filas de una tabla dinámica denominada PivotTable1 en la hoja activa:
| 12 | ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Producto"). Orientación = _xlHidden |
Crear un filtro
Se ha creado una tabla dinámica llamada PivotTable1 con Producto en la sección Filas y Ventas en la sección Valores. También puede crear un filtro para su tabla dinámica usando VBA. El siguiente código creará un filtro basado en Región en la sección Filtros:
| 123 | ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Región"). Orientación = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Región"). Posición = 1 |
Para filtrar su tabla dinámica en función de un elemento de informe único en este caso, la región este, debe usar el siguiente código:
| 12345 | ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Región"). ClearAllFiltersActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Región"). CurrentPage = _"Este" |
Supongamos que desea filtrar su tabla dinámica en función de varias regiones; en este caso, este y norte, usaría el siguiente código:
| 1234567891011121314 | ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Región"). Orientación = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Región"). Posición = 1ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Región"). _EnableMultiplePageItems = VerdaderoCon ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Región").PivotItems ("Sur"). Visible = False.PivotItems ("Oeste"). Visible = FalseTerminar con |
Actualizar su tabla dinámica
Puede actualizar su tabla dinámica en VBA. Usaría el siguiente código para actualizar una tabla específica llamada PivotTable1 en VBA:
| 1 | ActiveSheet.PivotTables ("PivotTable1"). PivotCache.Refresh |
