excel-vba
Tableaux pivotants

Remarques

Création d'un tableau croisé dynamique

L'une des fonctionnalités les plus puissantes d'Excel est l'utilisation des tableaux croisés dynamiques pour trier et analyser les données. Utiliser VBA pour créer et manipuler les pivots est plus facile si vous comprenez la relation entre les tableaux croisés dynamiques et les caches pivotants et comment référencer et utiliser les différentes parties des tableaux.

À la base, vos données sources sont une zone de données Range sur une Worksheet . Cette zone de données DOIT identifier les colonnes de données avec une ligne d'en-tête comme première ligne de la plage. Une fois le tableau croisé dynamique créé, l'utilisateur peut afficher et modifier les données source à tout moment. Cependant, les modifications peuvent ne pas être automatiquement ou immédiatement reflétées dans le tableau croisé dynamique lui-même car il existe une structure de stockage de données intermédiaire appelée cache de tableau croisé dynamique qui est directement connectée au tableau croisé dynamique lui-même.

Si plusieurs tableaux croisés dynamiques sont nécessaires, basés sur les mêmes données source, le cache pivot peut être réutilisé en tant que magasin de données interne pour chacun des tableaux croisés dynamiques. C'est une bonne pratique car cela économise de la mémoire et réduit la taille du fichier Excel pour le stockage.

Par exemple, pour créer un tableau croisé dynamique basé sur les données source indiquées dans les figures ci-dessus:

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

Références Objet tableau croisé dynamique MSDN

Plates-formes de tableau pivotant

Ces excellentes sources de référence fournissent des descriptions et des illustrations des différentes gammes de tableaux croisés dynamiques.

Les références

• Référencement des plages de tableau croisé dynamique dans VBA - sur le blog Tech de Jon Peltier
• Référencement d'une plage de tableau croisé dynamique Excel à l' aide de VBA - à partir de globaliconnect Excel VBA

Ajout de champs à un tableau croisé dynamique

Deux points importants à noter lors de l'ajout de champs à un tableau croisé dynamique sont l'orientation et la position. Parfois, un développeur peut supposer où un champ est placé, il est donc toujours plus clair de définir explicitement ces paramètres. Ces actions n'affectent que le tableau croisé dynamique donné, pas le cache de tableau croisé dynamique.

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

Formatage des données du tableau croisé dynamique

Cet exemple modifie / définit plusieurs formats dans la zone de plage de données ( DataBodyRange ) du tableau DataBodyRange donné. Tous les paramètres formatables dans une Range standard sont disponibles. Le formatage des données affecte uniquement le tableau croisé dynamique lui-même, pas le cache de tableau croisé dynamique.

REMARQUE: la propriété est nommée TableStyle2 car la propriété TableStyle n'est pas membre des propriétés de l'objet du 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