Guide VBA des tableaux croisés dynamiques

Ce didacticiel montrera comment travailler avec des tableaux croisés dynamiques à l'aide de VBA.

Les tableaux croisés dynamiques sont des outils de synthèse de données que vous pouvez utiliser pour tirer des informations et des résumés clés 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, illustrés ci-dessous :

Utilisation de GetPivotData pour obtenir une valeur

Supposons que vous ayez un tableau croisé dynamique appelé Tableau croisé dynamique1 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 des valeurs à partir de tableaux croisés dynamiques.

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

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

Dans ce cas, Sales est le "DataField", "Field1" est la région et "Item1" est East.

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

1	MsgBox ActiveCell.PivotTable.GetPivotData("Ventes", "Produit", "ABC", "Région", "Nord")

Dans ce cas, Sales est le "DataField", "Field1" est Product, "Item1" est ABC, "Field2" est Region et "Item2" est North.

Vous pouvez également inclure plus de 2 champs.

La syntaxe de GetPivotData est :

GetPivotData (Champ de données, Champ1, Objet 1, Champ2, Article2… ) où:

Paramètre	La description
Champ de données	Champ de données tel que les ventes, la quantité, etc. qui contient des nombres.
Champ 1	Nom d'un champ de colonne ou de ligne dans la table.
Objet 1	Nom d'un élément dans le champ 1 (facultatif).
Champ 2	Nom d'un champ de colonne ou de ligne dans le tableau (facultatif).
Article 2	Nom d'un élément dans le champ 2 (facultatif).

Création d'un tableau croisé dynamique sur une feuille

Afin de créer un tableau croisé dynamique basé sur la plage de données ci-dessus, sur la cellule J2 de la feuille Sheet1 du classeur actif, nous utiliserions le code suivant :

1234567891011

Feuilles de calcul("Feuille1").Cellules(1, 1).SélectionnezActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _"Feuille1!R1C1:R21C4", Version:=xlPivotTableVersion15).CreatePivotTable _TableDestination:="Sheet1!R2C10", TableName:="PivotTable1", DefaultVersion _:=xlPivotTableVersion15Feuilles("Feuille1").Sélectionnez

Le résultat est:

Création d'un tableau croisé dynamique sur une nouvelle feuille

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

12345678910111213

Feuilles de calcul("Feuille1").Cellules(1, 1).SélectionnezFeuilles.AjouterActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _"Feuille1!R1C1:R21C4", Version:=xlPivotTableVersion15).CreatePivotTable _TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion _:=xlPivotTableVersion15Feuilles("Feuille2").Sélectionnez

Ajout de champs au tableau croisé dynamique

Vous pouvez ajouter des champs au tableau croisé dynamique nouvellement créé appelé Tableau croisé dynamique1 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 Product au champ Rows, vous devez utiliser le code suivant :

123	ActiveSheet.PivotTables("PivotTable1").PivotFields("Product").Orientation = xlRowFieldActiveSheet.PivotTables("PivotTable1").PivotFields("Product").Position = 1

Pour ajouter la région au champ de colonnes, vous utiliseriez le code suivant :

123	ActiveSheet.PivotTables("PivotTable1").PivotFields("Région").Orientation = xlColumnFieldActiveSheet.PivotTables("PivotTable1").PivotFields("Région").Position = 1

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

123456789

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _"PivotTable1").PivotFields("Ventes"), "Somme des ventes", xlSumAvec ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Sales").NumberFormat = "$#,##0.00"Terminer par

Le résultat est:

Modification de la présentation du rapport du tableau croisé dynamique

Vous pouvez modifier la présentation du rapport de votre tableau croisé dynamique. Le code suivant changera la présentation du rapport de votre tableau croisé dynamique en forme tabulaire :

1	ActiveSheet.PivotTables("PivotTable1").TableStyle2 = "PivotStyleLight18"

Supprimer un tableau croisé dynamique

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

12	ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, TrueSelection.ClearContents

Mettre en forme tous les tableaux croisés dynamiques d'un classeur

Vous pouvez formater tous les tableaux croisés dynamiques d'un classeur à l'aide de VBA. Le code suivant utilise une structure de boucle pour parcourir toutes les feuilles d'un classeur et supprimer tous les tableaux croisés dynamiques du classeur :

12345678910111213

Sous-formatageTousLesTableauxPivotsDansUn classeur()Dim sem comme feuille de travailDim wb comme classeurDéfinir wb = ActiveWorkbookDim pt en tant que tableau croisé dynamiquePour chaque semaine dans wb.SheetsPour chaque pt dans wks.PivotTablespt.TableStyle2 = "PivotStyleLight15"Point suivantSemaines suivantesFin du sous-marin

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

Supprimer des champs d'un tableau croisé dynamique

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

12	ActiveSheet.PivotTables("PivotTable1").PivotFields("Product").Orientation = _xlCaché

Création d'un filtre

Un tableau croisé dynamique appelé PivotTable1 a été créé avec Product dans la section Rows et Sales dans la section Values. Vous pouvez également créer un filtre pour votre tableau croisé dynamique à l'aide de VBA. Le code suivant créera un filtre basé sur la région dans la section Filtres :

123	ActiveSheet.PivotTables("PivotTable1").PivotFields("Région").Orientation = xlPageFieldActiveSheet.PivotTables("PivotTable1").PivotFields("Région").Position = 1

Pour filtrer votre tableau croisé dynamique en fonction d'un élément de rapport unique dans ce cas la région Est, vous utiliseriez le code suivant :

12345

ActiveSheet.PivotTables("PivotTable1").PivotFields("Région").ClearAllFiltersActiveSheet.PivotTables("PivotTable1").PivotFields("Région").CurrentPage = _"Est"

Supposons que vous vouliez filtrer votre tableau croisé dynamique en fonction de plusieurs régions, dans ce cas Est et Nord, vous utiliseriez le code suivant :

1234567891011121314

ActiveSheet.PivotTables("PivotTable1").PivotFields("Région").Orientation = xlPageFieldActiveSheet.PivotTables("PivotTable1").PivotFields("Région").Position = 1ActiveSheet.PivotTables("PivotTable1").PivotFields("Région"). _EnableMultiplePageItems = TrueAvec ActiveSheet.PivotTables("PivotTable1").PivotFields("Région").PivotItems("Sud").Visible = False.PivotItems("Ouest").Visible = FalseTerminer par

Rafraîchir votre tableau croisé dynamique

Vous pouvez actualiser votre tableau croisé dynamique en VBA. Vous utiliseriez le code suivant pour actualiser une table spécifique appelée PivotTable1 dans VBA :

1	ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh