Créer un tableau croisé dynamique (TCD), Excel VBA

Créer un tableau croisé dynamique (TCD), Excel VBA

Voici une explication détaillée et un exemple de code VBA pour créer un tableau croisé dynamique (TCD) dans Excel. Ce code permet d’automatiser la création de TCD à partir de données brutes.
Étapes principales :
1. Préparer les données : Assurez-vous que vos données sont structurées sous forme de tableau, avec des en-têtes dans la première ligne, car elles seront utilisées pour créer le tableau croisé dynamique.
2. Créer le tableau croisé dynamique : Vous pouvez utiliser la méthode PivotTableWizard ou l’objet PivotCache. Nous allons utiliser PivotCache pour avoir plus de contrôle et de flexibilité.
3. Définir la plage de données du tableau croisé dynamique : Vous devez spécifier la plage contenant les données.
4. Spécifier la destination du tableau croisé dynamique : Choisir où le tableau croisé dynamique sera placé (nouvelle feuille ou feuille existante).
5. Ajouter les champs au tableau croisé dynamique : Ajouter des champs de ligne, de colonne et de valeur pour définir la structure du TCD.
Exemple de code VBA :

Sub CreerTableauCroiseDynamique()
    Dim wsSource As Worksheet
    Dim wsPivot As Worksheet
    Dim pivotCache As PivotCache
    Dim pivotTable As PivotTable
    Dim dataRange As Range
    Dim pivotDestination As Range
    ' Étape 1 : Définir la feuille de données source et la plage de données
    Set wsSource = ThisWorkbook.Sheets("Data")  ' Supposons que les données sont sur une feuille nommée "Data"
    Set dataRange = wsSource.Range("A1:D100")  ' Ajustez la plage en fonction de vos données
    ' Étape 2 : Créer une nouvelle feuille pour le Tableau Croisé Dynamique
    Set wsPivot = ThisWorkbook.Sheets.Add
    wsPivot.Name = "FeuilleTCD"  ' Vous pouvez changer le nom de la feuille si nécessaire
    ' Étape 3 : Créer le Pivot Cache à partir de la plage de données source
    Set pivotCache = ThisWorkbook.PivotTableWizard(dataRange)
    ' Étape 4 : Créer le Tableau Croisé Dynamique et définir sa destination (nouvelle feuille)
    Set pivotDestination = wsPivot.Cells(1, 1)  ' Placer le TCD à partir de la cellule A1
    Set pivotTable = wsPivot.PivotTableWizard(pivotCache)
    ' Étape 5 : Ajouter les champs au Tableau Croisé Dynamique
    With pivotTable
        ' Ajouter les champs de lignes (par exemple, "Produit")
        .PivotFields("Produit").Orientation = xlRowField
        .PivotFields("Produit").Position = 1
        ' Ajouter les champs de colonnes (par exemple, "Région")
        .PivotFields("Région").Orientation = xlColumnField
        .PivotFields("Région").Position = 1
        ' Ajouter les champs de valeurs (par exemple, "Ventes")
        .PivotFields("Ventes").Orientation = xlDataField
        .PivotFields("Ventes").Function = xlSum  ' Vous pouvez changer cette fonction en d'autres agrégations comme Count, Average, etc.
        .PivotFields("Ventes").Position = 1
    End With
    ' Optionnel : Personnaliser le Tableau Croisé Dynamique (par exemple, styles, totaux)
    pivotTable.TableStyle2 = "PivotStyleLight16"  ' Appliquer un style prédéfini
    pivotTable.ColumnGrand = False  ' Désactiver le total des colonnes si nécessaire
    pivotTable.RowGrand = True  ' Activer les totaux des lignes
    ' Informer l'utilisateur que le Tableau Croisé Dynamique a été créé
    MsgBox "Le Tableau Croisé Dynamique a été créé avec succès !", vbInformation
End Sub

Explication du code :
1. Définir la plage de données source :

• Set wsSource = ThisWorkbook.Sheets("Data") définit la feuille contenant vos données.
• Set dataRange = wsSource.Range("A1:D100") spécifie la plage de données. Vous pouvez ajuster la plage en fonction de vos données réelles.

2. Créer une nouvelle feuille pour le Tableau Croisé Dynamique :

• Set wsPivot = ThisWorkbook.Sheets.Add crée une nouvelle feuille pour placer le TCD.
• Vous pouvez changer le nom de la feuille par wsPivot.Name.

3. Pivot Cache :

• Set pivotCache = ThisWorkbook.PivotTableWizard(dataRange) crée l’objet PivotCache à partir de la plage de données.
• Un Pivot Cache est nécessaire pour créer le TCD, car il permet de gérer efficacement les données.

4. Créer le Tableau Croisé Dynamique :

• Set pivotDestination = wsPivot.Cells(1, 1) définit la cellule de départ du TCD (dans ce cas, la cellule A1).
• Set pivotTable = wsPivot.PivotTableWizard(pivotCache) crée le TCD à partir du Pivot Cache.

5. Ajouter des champs au Tableau Croisé Dynamique :

• Champs de ligne (.PivotFields("Produit").Orientation = xlRowField) : Ce champ regroupe les données par produit.
• Champs de colonne (.PivotFields("Région").Orientation = xlColumnField) : Ce champ regroupe les données par région.
• Champs de valeurs (.PivotFields("Ventes").Orientation = xlDataField) : Ce champ résume les données de ventes, avec la fonction d’agrégation (par défaut, la somme).

6. Personnalisation du Tableau Croisé Dynamique :

• Vous pouvez appliquer des styles (pivotTable.TableStyle2 = "PivotStyleLight16") et configurer les totaux (par exemple, pivotTable.ColumnGrand = False désactive le total des colonnes).

Notes supplémentaires :

• Plages dynamiques : Pour plus de flexibilité, vous pouvez utiliser des plages nommées dynamiques (via Définir un nom dans Excel) afin que votre plage de données s’ajuste automatiquement à l’ajout de nouvelles données.
• Personnalisation : Vous pouvez personnaliser davantage le TCD en ajoutant des filtres, en modifiant les fonctions d’agrégation (par exemple, Count, Moyenne), et bien plus encore.