excel-vba
Tablas dinamicas

Observaciones

Creación de una tabla dinámica

Una de las capacidades más poderosas en Excel es el uso de tablas dinámicas para clasificar y analizar datos. Usar VBA para crear y manipular los Pivots es más fácil si entiende la relación de Pivot Tables con los Pivot Caches y cómo hacer referencia y usar las diferentes partes de las Tablas.

En su forma más básica, sus datos de origen son un área de datos de Range en una Worksheet . Esta área de datos DEBE identificar las columnas de datos con una fila de encabezado como la primera fila en el rango. Una vez que se crea la tabla dinámica, el usuario puede ver y cambiar los datos de origen en cualquier momento. Sin embargo, es posible que los cambios no se reflejen de forma automática o inmediata en la propia Tabla dinámica porque existe una estructura de almacenamiento de datos intermedia denominada Caché de pivote que está directamente conectada a la propia Tabla dinámica.

Si se necesitan varias tablas dinámicas, basadas en los mismos datos de origen, la memoria caché dinámica puede reutilizarse como almacén de datos interno para cada una de las tablas dinámicas. Esta es una buena práctica porque ahorra memoria y reduce el tamaño del archivo de Excel para el almacenamiento.

Como ejemplo, para crear una tabla dinámica basada en los datos de origen que se muestran en las figuras anteriores:

Sub test()
    Dim pt As PivotTable
    Set pt = CreatePivotTable(ThisWorkbook.Sheets("Sheet1").Range("A1:E15"))
End Sub

Function CreatePivotTable(ByRef srcData As Range) As PivotTable
    '--- creates a Pivot Table from the given source data and
    '    assumes that the first row contains valid header data
    '    for the columns
    Dim thisPivot As PivotTable
    Dim dataSheet As Worksheet
    Dim ptSheet As Worksheet
    Dim ptCache As PivotCache
    
    '--- the Pivot Cache must be created first...
    Set ptCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                                  SourceData:=srcData)
    '--- ... then use the Pivot Cache to create the Table
    Set ptSheet = ThisWorkbook.Sheets.Add
    Set thisPivot = ptCache.CreatePivotTable(TableDestination:=ptSheet.Range("A3"))
    Set CreatePivotTable = thisPivot
End Function

Referencias MSDN Pivot Table Object

Rangos de tabla de pivote

Estas excelentes fuentes de referencia proporcionan descripciones e ilustraciones de los diversos rangos en las tablas dinámicas.

Referencias

• Referencia a los rangos de tablas dinámicas en VBA - del blog de tecnología de Jon Peltier
• Referencia a un rango de tabla dinámica de Excel mediante VBA - de globaliconnect Excel VBA

Agregar campos a una tabla dinámica

Dos cosas importantes a tener en cuenta al agregar campos a una tabla dinámica son la orientación y la posición. A veces, un desarrollador puede asumir dónde se coloca un campo, por lo que siempre es más claro definir explícitamente estos parámetros. Estas acciones solo afectan a la tabla dinámica dada, no al caché dinámico.

Dim thisPivot As PivotTable
Dim ptSheet As Worksheet
Dim ptField As PivotField

Set ptSheet = ThisWorkbook.Sheets("SheetNameWithPivotTable")
Set thisPivot = ptSheet.PivotTables(1)

With thisPivot
    Set ptField = .PivotFields("Gender")
    ptField.Orientation = xlRowField
    ptField.Position = 1
    Set ptField = .PivotFields("LastName")
    ptField.Orientation = xlRowField
    ptField.Position = 2
    Set ptField = .PivotFields("ShirtSize")
    ptField.Orientation = xlColumnField
    ptField.Position = 1
    Set ptField = .AddDataField(.PivotFields("Cost"), "Sum of Cost", xlSum)
    .InGridDropZones = True
    .RowAxisLayout xlTabularRow
End With

Formato de los datos de la tabla dinámica

Este ejemplo cambia / establece varios formatos en el área de rango de datos ( DataBodyRange ) de la Tabla de Pivot dada. Todos los parámetros formateables en un Range estándar están disponibles. El formateo de los datos solo afecta a la propia tabla dinámica, no a la memoria caché dinámica.

NOTA: la propiedad se llama TableStyle2 porque la propiedad TableStyle no es un miembro de las propiedades de objeto de la PivotTable .

Dim thisPivot As PivotTable
Dim ptSheet As Worksheet
Dim ptField As PivotField

Set ptSheet = ThisWorkbook.Sheets("SheetNameWithPivotTable")
Set thisPivot = ptSheet.PivotTables(1)

With thisPivot
    .DataBodyRange.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)"
    .DataBodyRange.HorizontalAlignment = xlRight
    .ColumnRange.HorizontalAlignment = xlCenter
    .TableStyle2 = "PivotStyleMedium9"
End With