Créer un Tableau Croisé Dynamique (TCD), Excel VBA
Voici une explication détaillée du code VBA pour créer un Tableau Croisé Dynamique (TCD) dans Excel :
Vue d’ensemble :
Un Tableau Croisé Dynamique (TCD) est un tableau qui s’ajuste automatiquement lorsque de nouvelles données sont ajoutées. Ce code VBA crée un TCD dynamique basé sur une plage de données qui peut changer, par exemple, lorsque des lignes ou des colonnes sont ajoutées.
Code avec explication détaillée :
Sub CreateDynamicPivotTable()
' Déclaration des variables
Dim wsSource As Worksheet
Dim wsPivot As Worksheet
Dim lastRow As Long
Dim lastCol As Long
Dim pivotRange As Range
Dim pivotTable As PivotTable
Dim pivotCache As PivotCache
Dim pivotSheetName As String
' Définir la feuille de données source
Set wsSource = ThisWorkbook.Sheets("Sheet1") ' Changez le nom de votre feuille de données ici
' Trouver la dernière ligne et la dernière colonne avec des données dans la feuille source
lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row ' Supposons que les données commencent dans la colonne A
lastCol = wsSource.Cells(1, wsSource.Columns.Count).End(xlToLeft).Column ' Supposons que la ligne d'en-tête soit dans la ligne 1 *
' Définir la plage dynamique de données pour le tableau croisé dynamique (y compris les en-têtes)
Set pivotRange = wsSource.Range(wsSource.Cells(1, 1), wsSource.Cells(lastRow, lastCol))
' Vérifier si une feuille de tableau croisé dynamique existe déjà et la supprimer si nécessaire
On Error Resume Next
Set wsPivot = ThisWorkbook.Sheets("PivotTableSheet") ' Changez le nom de votre feuille de tableau croisé dynamique ici
On Error GoTo 0
If Not wsPivot Is Nothing Then
Application.DisplayAlerts = False
wsPivot.Delete
Application.DisplayAlerts = True
End If
' Créer une nouvelle feuille pour le tableau croisé dynamique
Set wsPivot = ThisWorkbook.Sheets.Add
wsPivot.Name = "PivotTableSheet"
' Créer un cache de tableau croisé dynamique à partir de la source de données
Set pivotCache = ThisWorkbook.PivotTableWizard(wsSource:=pivotRange)
' Créer le tableau croisé dynamique dans la nouvelle feuille
Set pivotTable = wsPivot.PivotTables.Add(PivotCache:=pivotCache, TableDestination:=wsPivot.Cells(1, 1), TableName:="DynamicPivotTable")
' Ajouter des champs au tableau croisé dynamique (exemple)
' Champs de ligne
pivotTable.PivotFields("Category").Orientation = xlRowField
pivotTable.PivotFields("Category").Position = 1
' Champs de colonne
pivotTable.PivotFields("Region").Orientation = xlColumnField
pivotTable.PivotFields("Region").Position = 1
' Champs de données
pivotTable.PivotFields("Sales").Orientation = xlDataField
pivotTable.PivotFields("Sales").Function = xlSum
pivotTable.PivotFields("Sales").NumberFormat = "#,##0"
' Paramètres optionnels : mise en forme et disposition
With pivotTable
.RowAxisLayout xlTabularRow
.ColumnGrand = True
.RowGrand = True
End With
' Ajuster la largeur des colonnes pour une meilleure présentation
wsPivot.Columns.AutoFit
End Sub
Explication détaillée :
1. Déclaration des variables :
- wsSource : La feuille contenant les données brutes.
- wsPivot : La feuille où sera créé le tableau croisé dynamique.
- lastRow et lastCol : Déterminent la taille de la plage de données.
- pivotRange : La plage de données à utiliser pour le tableau croisé dynamique.
- pivotCache : Un cache qui contient les données du tableau croisé dynamique.
- pivotTable : L’objet du tableau croisé dynamique lui-même.
2. Définir la plage de données :
- Nous utilisons lastRow et lastCol pour déterminer la taille dynamique de la plage de données. Cela permet d’adapter le tableau croisé dynamique si de nouvelles lignes ou colonnes sont ajoutées.
3. Supprimer une feuille existante de tableau croisé dynamique (si nécessaire) :
- Avant de créer un nouveau tableau croisé dynamique, nous vérifions si une feuille avec le nom « PivotTableSheet » existe déjà. Si elle existe, elle est supprimée.
4. Créer une nouvelle feuille pour le tableau croisé dynamique :
- Une nouvelle feuille est créée pour héberger le tableau croisé dynamique. Elle est nommée « PivotTableSheet ».
5. Créer un cache de tableau croisé dynamique :
- Un cache est créé à partir de la plage dynamique de données. Ce cache contient toutes les données nécessaires pour le tableau croisé dynamique.
6. Ajouter des champs au tableau croisé dynamique :
- Vous pouvez définir les champs de lignes, de colonnes et de données. Dans cet exemple :
- « Category » est utilisé comme champ de ligne.
- « Region » est utilisé comme champ de colonne.
- « Sales » est utilisé comme champ de données, et nous affichons la somme des ventes.
7. Paramètres optionnels : mise en forme et disposition :
- La disposition est définie sur xlTabularRow, ce qui permet d’afficher les lignes dans un format tabulaire.
- Les totaux des lignes et des colonnes sont activés avec .RowGrand et .ColumnGrand.
8. Ajuster la largeur des colonnes :
- Enfin, nous ajustons la largeur des colonnes pour une présentation plus claire en utilisant AutoFit.
Personnalisation :
- Vous pouvez adapter la plage de données en fonction des colonnes spécifiques de votre source de données.
- Modifiez les champs de lignes, de colonnes et de données en fonction de vos besoins spécifiques.
- Vous pouvez également changer la fonction de résumé des données (Function), comme par exemple utiliser xlAverage pour la moyenne ou xlCount pour le nombre d’éléments.
Conclusion :
Ce code permet de créer un tableau croisé dynamique qui s’adapte automatiquement aux nouvelles données ajoutées. Il vous évite d’avoir à mettre à jour manuellement la plage de données chaque fois que vous ajoutez de nouvelles lignes ou colonnes. Vous pouvez personnaliser ce code pour répondre à vos besoins spécifiques en matière de reporting.