Guide VBA des Tableaux Croisés Dynamiques

Ce tutoriel montre comment travailler avec des tableaux croisés dynamiques en utilisant VBA

Les tableaux croisés dynamiques sont des outils de synthèse des données que vous pouvez utiliser pour obtenir des informations et des résumés clés à partir de vos données. Prenons un exemple : nous avons un ensemble de données source dans les cellules A1:D21 contenant les détails des produits vendus, comme indiqué ci-dessous :

Utilisation de GetPivotData pour Obtenir une Valeur

Supposons que vous disposiez d’un tableau croisé dynamique appelé TableauCroisé1 avec Ventes dans le champ Valeurs/Données, Produit dans le champ Lignes et Région dans le champ Colonnes. Vous pouvez utiliser la méthode PivotTable.GetPivotData pour renvoyer les valeurs des tableaux croisés dynamiques.

Le code suivant renvoie 1 130,00 $ (le total des ventes pour la région Est) à partir du tableau croisé dynamique :

MsgBox ActiveCell.PivotTable.GetPivotData("Ventes", "Région", "Est")

Dans ce cas, Ventes est le « DataField », « Field1 » est la région et « Item1 » est Est.

Le code suivant renvoie 980 $ (le total des ventes du produit ABC dans la région Nord) à partir du tableau croisé dynamique :

MsgBox ActiveCell.PivotTable.GetPivotData("Ventes", "Produits", "ABC", "Region", "North")

Dans ce cas, Ventes est le « DataField », « Field1 » est Produit, « Item1 » est ABC, « Field2 » est Région et « Item2 » est Nord.

Vous pouvez également inclure plus de deux champs.

La syntaxe de GetPivotData est la suivante :

GetPivotData(DataField, Field1, Item1, Field2, Item2…) où :

Paramètre	Description
DataField	Champ de données tel que les ventes, la quantité, etc. qui contient des chiffres.
Field 1	Nom d’un champ de colonne ou de ligne dans le tableau.
Item 1	Nom d’un élément du champ 1 (facultatif).
Field 2	Nom d’un champ de colonne ou de ligne dans le tableau (facultatif).
Item 2	Nom d’un élément dans le champ 2 (facultatif).

Création d’un Tableau Croisé Dynamique sur une Feuille

Pour créer un tableau croisé dynamique basé sur la plage de données ci-dessus, dans la cellule J2 de la feuille 1 du classeur actif, nous devons utiliser le code suivant :

Sub AjouterTableauCroisé()

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
    SourceData:="Feuil1!R1C1:R21C4", _
    Version:=xlPivotTableVersion15).CreatePivotTable _
    TableDestination:="Feuil1!R2C10", TableName:="TableauCroisé1", _
    DefaultVersion:=xlPivotTableVersion15

End Sub

Le résultat est le suivant :

Création d’un Tableau Croisé Dynamique sur une Nouvelle Feuille

Pour créer un tableau croisé dynamique basé sur la plage de données ci-dessus, sur une nouvelle feuille du classeur actif, nous devons utiliser le code suivant :

Sub AjouterTableauCroiséNouvelleFeuille()

    Set NouvelleFeuille = Sheets.Add

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
    SourceData:="Feuil1!R1C1:R21C4", _
    Version:=xlPivotTableVersion15).CreatePivotTable _
    TableDestination:=NouvelleFeuille.Name & "!R2C10", TableName:="TableauCroisé1", _
    DefaultVersion:=xlPivotTableVersion15

End Sub

Ajouter des Champs au Tableau Croisé Dynamique

Vous pouvez ajouter des champs au tableau croisé dynamique nouvellement créé, appelé TableauCroisé1, en fonction de la plage de données ci-dessus. Remarque : la feuille contenant votre tableau croisé dynamique doit être la feuille active.

Pour ajouter le produit au champ Lignes, vous devez utiliser le code suivant :

ActiveSheet.PivotTables("TableauCroisé1").PivotFields("Produit").Orientation = xlRowField

ActiveSheet.PivotTables("TableauCroisé1").PivotFields("Produit").Position = 1

Pour ajouter la région au champ Colonnes, vous devez utiliser le code suivant :

ActiveSheet.PivotTables("TableauCroisé1").PivotFields("Région").Orientation = xlColumnField

ActiveSheet.PivotTables("TableauCroisé1").PivotFields("Région").Position = 1

Pour ajouter les ventes à la section des valeurs avec un format numérique de type devise, vous devez utiliser le code suivant :

ActiveSheet.PivotTables("TableauCroisé1").AddDataField ActiveSheet.PivotTables("TableauCroisé1"). _
PivotFields("Ventes"), "Somme des Ventes", xlSum

With ActiveSheet.PivotTables("TableauCroisé1").PivotFields("Somme des Ventes")
    .NumberFormat = "$#,##0.00"
End With

Le résultat est le suivant :

Modification de la Disposition du Tableau Croisé Dynamique dans le Rapport

Vous pouvez modifier la mise en page du rapport de votre tableau croisé dynamique. Le code suivant modifiera la disposition du rapport de votre tableau croisé dynamique en forme tabulaire :

ActiveSheet.PivotTables("TableauCroisé1").TableStyle2 = "PivotStyleLight18"

Suppression d’un Tableau Croisé Dynamique

Vous pouvez supprimer un tableau croisé dynamique à l’aide de VBA. Le code suivant supprimera le tableau croisé dynamique appelé TableauCroisé1 sur la feuille active :

ActiveSheet.PivotTables("TableauCroisé1").PivotSelect "", xlDataAndLabel, True
Selection.ClearContents

Formatage de tous les Tableaux Croisés Dynamiques d’un Classeur

Vous pouvez formater tous les tableaux croisés dynamiques d’un classeur en utilisant VBA. Le code suivant utilise une structure en boucle pour parcourir toutes les feuilles d’un classeur et formater tous les tableaux croisés dynamiques du classeur :

Sub FormaterTousLesTableauxCroisésDuClasseur()

Dim fc As Worksheet
Dim cl As Workbook
Set cl = ActiveWorkbook
Dim tc As PivotTable

For Each fc In cl.Sheets
    For Each tc In fc.PivotTables
        tc.TableStyle2 = "PivotStyleLight15"
    Next tc
Next fc

End Sub

Pour en savoir plus sur l’utilisation des boucles en VBA, cliquez ici.

Suppression des Champs d’un Tableau Croisé Dynamique

Vous pouvez supprimer les champs d’un tableau croisé dynamique à l’aide de VBA. Le code suivant supprimera le champ Produit de la section Lignes d’un tableau croisé dynamique nommé TableauCroisé1 dans la feuille active :

ActiveSheet.PivotTables("TableauCroisé1").PivotFields("Produit").Orientation = _
xlHidden

Création d’un Filtre

Un tableau croisé dynamique appelé TableauCroisé1 a été créé avec Produit dans la section Lignes et Ventes dans la section Valeurs. Vous pouvez également créer un filtre pour votre tableau croisé dynamique en utilisant VBA. Le code suivant va créer un filtre basé sur la région dans la section Filtres :

ActiveSheet.PivotTables("TableauCroisé1").PivotFields("Région").Orientation = xlPageField

ActiveSheet.PivotTables("TableauCroisé1").PivotFields("Région").Position = 1

Pour filtrer votre tableau croisé dynamique sur la base d’un seul élément de rapport, dans ce cas la région Est, vous devez utiliser le code suivant :

ActiveSheet.PivotTables("TableauCroisé1").PivotFields("Région").ClearAllFilters

ActiveSheet.PivotTables("TableauCroisé1").PivotFields("Région").CurrentPage = "Est"

Supposons que vous vouliez filtrer votre tableau croisé dynamique sur la base de plusieurs régions, dans ce cas l’Est et le Nord, vous utiliseriez le code suivant :

ActiveSheet.PivotTables("TableauCroisé1").PivotFields("Région").Orientation = xlPageField

ActiveSheet.PivotTables("TableauCroisé1").PivotFields("Région").Position = 1

ActiveSheet.PivotTables("TableauCroisé1").PivotFields("Région").EnableMultiplePageItems = True

With ActiveSheet.PivotTables("TableauCroisé1").PivotFields("Région")
    .PivotItems("Sud").Visible = False
    .PivotItems("Ouest").Visible = False
End With

Rafraîchir votre Tableau Croisé Dynamique

Vous pouvez rafraîchir votre tableau croisé dynamique en VBA. Vous pouvez utiliser le code suivant pour rafraîchir un tableau spécifique appelé PivotTable1 en VBA :

ActiveSheet.PivotTables("TableauCroisé1").PivotCache.Refresh