Glissez pour afficher le menu

Un tableau croisé dynamique résume une table en faisant glisser des champs dans les zones Lignes, Colonnes et Valeurs — et chacune de ces actions de glisser-déposer possède un équivalent direct en VBA, ce qui signifie qu’un rapport de tableau croisé dynamique complet peut être reconstruit à partir de zéro par une macro à chaque arrivée de nouvelles données.

Création d’un tableau croisé dynamique

Sub BuildProfitPivot()
    Dim wsData As Worksheet, wsPivot As Worksheet
    Dim tbl As ListObject
    Dim pc As PivotCache
    Dim pt As PivotTable
 
    Set wsData = ThisWorkbook.Worksheets("Reports")
    Set tbl = wsData.ListObjects("tblReports")
 
    ' start clean: remove an existing Pivot sheet if this has run before
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets("Pivot").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
 
    Set wsPivot = ThisWorkbook.Worksheets.Add
    wsPivot.Name = "Pivot"
 
    Set pc = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, SourceData:=tbl.Range)
 
    Set pt = pc.CreatePivotTable( _
        TableDestination:=wsPivot.Range("A3"), _
        TableName:="ptProfitByRegion")
 
    With pt
        .PivotFields("Region").Orientation = xlRowField
        .PivotFields("Month").Orientation = xlColumnField
        .AddDataField .PivotFields("Profit"), "Sum of Profit", xlSum
    End With
End Sub

Analyse ligne par ligne

L'utilisation de On Error Resume Next associée à l'option DisplayAlerts et à .Delete constitue un modèle sécurisé pour "supprimer si cela existe" : supprimer une feuille qui n'existe pas provoquerait normalement une erreur et arrêterait la macro, mais On Error Resume Next indique à VBA de continuer silencieusement au-delà de cette erreur spécifique ; DisplayAlerts = False supprime la fenêtre contextuelle de confirmation d'Excel "êtes-vous sûr de vouloir supprimer cette feuille ?" ;
Error GoTo 0 immédiatement après réactive le signalement normal des erreurs — laisser On Error Resume Next actif pour le reste du Sub masquerait silencieusement toute erreur ultérieure non liée, ce qui est un piège à éviter ;
ThisWorkbook.PivotCaches.Create prend un instantané des données du tableau — le PivotCache, et non le PivotTable lui-même — qui est l'objet à partir duquel chaque PivotTable est réellement construit en arrière-plan ;
pc.CreatePivotTable transforme cet instantané en un PivotTable visible, placé à partir de la cellule A3 sur la nouvelle feuille Pivot et nommé ptProfitByRegion afin que le code ultérieur (RefreshTable, par exemple) puisse le retrouver par son nom ;
PivotFields("Region").Orientation = xlRowField et la ligne Month juste en dessous sont l'équivalent direct dans le code du glisser-déposer de Region dans la zone Lignes et de Month dans la zone Colonnes dans la liste des champs ;
AddDataField remplit la zone Valeurs — le deuxième argument ("Sum of Profit") est simplement l'étiquette qu'Excel affiche comme en-tête de colonne, et xlSum indique d'additionner les valeurs plutôt que de les moyenner ou de les compter.

Actualisation des rapports

Une fois qu'un PivotTable existe, il n'est pas nécessaire de le reconstruire à chaque arrivée de nouvelles données — il suffit de l'actualiser, ce qui est plus rapide et préserve toute modification manuelle de la mise en page effectuée par l'utilisateur :

ThisWorkbook.Worksheets("Pivot").PivotTables("ptProfitByRegion").RefreshTable

Cette seule ligne relit le PivotCache à partir de l'état actuel de tblReports et met à jour chaque valeur du PivotTable en conséquence — mais elle laisse la mise en page exactement telle quelle, y compris la largeur des colonnes, le format des nombres ou l'organisation des champs ajustés manuellement après la création initiale du Pivot. C'est le principal avantage par rapport à un nouvel appel de BuildProfitPivot : une reconstruction complète recréerait la feuille et effacerait toutes ces modifications manuelles.

Mise à jour des PivotCharts

Un PivotChart basé sur un PivotTable actualise automatiquement ses données chaque fois que le PivotTable est actualisé — ainsi, actualiser le tableau suffit généralement à maintenir à jour un graphique associé :

Dim pt As PivotTable
Set pt = ThisWorkbook.Worksheets("Pivot").PivotTables("ptProfitByRegion")
pt.RefreshTable
' any PivotChart based on pt updates automatically — no extra code needed

Cela contraste avec les graphiques ordinaires abordés dans la section suivante : un graphique classique nécessite un appel explicite à SetSourceData pour pointer vers de nouvelles données, tandis qu'un PivotChart est lié en permanence à son PivotTable et se met à jour automatiquement. Si un tableau de bord nécessite un graphique reflétant toujours les dernières valeurs du PivotTable avec un minimum de code, le construire comme PivotChart plutôt que comme graphique autonome est généralement le meilleur choix.

Exercice

Exécuter BuildProfitPivot exactement comme indiqué et vérifier qu'une nouvelle feuille "Pivot" apparaît avec Region en lignes et Month en colonnes.
Ajouter manuellement une nouvelle ligne March à tblReports pour une sixième région fictive, puis exécuter uniquement la ligne RefreshTable — vérifier que le Pivot se met à jour sans être reconstruit.
Modifier BuildProfitPivot pour résumer Sales au lieu de Profit, et inverser Region et Month afin que Month soit en lignes et Region en colonnes.

Assistant

Voici le code pour ajouter une sixième région comme nouvelle ligne de mars dans tblReports, en utilisant ListRows.Add au lieu de la saisir manuellement :

Sub AddSixthRegion()
    Dim tbl As ListObject
    Dim newRow As ListRow

    Set tbl = ThisWorkbook.Worksheets("Reports").ListObjects("tblReports")
    Set newRow = tbl.ListRows.Add

    newRow.Range(1, 1).Value = "March"        ' Month
    newRow.Range(1, 2).Value = "Southwest"    ' Region
    newRow.Range(1, 3).Value = 41500          ' Sales
    newRow.Range(1, 4).Value = 28200          ' Expenses
    newRow.Range(1, 5).Value = 13300          ' Profit
    newRow.Range(1, 6).Value = 39000          ' Target
End Sub

Exécutez ce code une fois, puis exécutez la procédure RefreshProfitPivot vue précédemment — le tableau croisé dynamique doit maintenant afficher "Southwest" comme nouvelle ligne aux côtés de North, South, East, West et Central, sans avoir modifié la macro de création du tableau croisé dynamique.

Astuce

1. Exécution de Sub tel quel

Copier la procédure tblReports exactement comme écrite dans le chapitre dans votre module et l’exécuter une fois.
Vérifier l’Explorateur de projet ou vos onglets de feuille — une nouvelle feuille nommée littéralement "Pivot" doit apparaître, avec Region listé en lignes et Month en colonnes, totalisant Profit.

2. Ajout d’une sixième région et simple actualisation

Saisir la nouvelle ligne directement dans la feuille de calcul (pas via le code) — aller en bas de BuildProfitPivot et ajouter une ligne de mars pour une région fictive, par exemple "Southwest".
Ne pas relancer BuildProfitPivot — cela supprimerait et recréerait toute la feuille Pivot, ce qui va à l’encontre de l’objectif de cet exercice.
À la place, exécuter uniquement l’instruction RefreshTable d’une ligne vue dans le chapitre — il faudra référencer le tableau croisé dynamique existant par son nom, comme dans l’exemple d’actualisation du chapitre.

3. Échanger les champs et changer la valeur résumée

Trois lignes à l’intérieur du bloc With pt doivent être modifiées : quel champ est xlRowField, lequel est xlColumnField et sur quel champ pointe AddDataField.
Donner à cette version modifiée un nom de procédure Sub différent et un TableName différent — réutiliser les mêmes noms que l’original provoquerait soit une erreur, soit l’écrasement silencieux du premier tableau croisé dynamique.
Le libellé passé à AddDataField (le deuxième argument, comme "Sum of Profit") est simplement un texte d’affichage — le mettre à jour pour correspondre à la donnée réellement résumée.

Solution

Point 2 — après avoir ajouté manuellement la ligne de la sixième région, exécuter simplement ceci :

Sub RefreshProfitPivot()
    ThisWorkbook.Worksheets("Pivot").PivotTables("ptProfitByRegion").RefreshTable
End Sub

Point 3 — une version modifiée distincte :

Sub BuildSalesPivotByMonth()
    Dim wsData As Worksheet, wsPivot As Worksheet
    Dim tbl As ListObject
    Dim pc As PivotCache
    Dim pt As PivotTable

    Set wsData = ThisWorkbook.Worksheets("Reports")
    Set tbl = wsData.ListObjects("tblReports")

    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets("SalesPivot").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    Set wsPivot = ThisWorkbook.Worksheets.Add
    wsPivot.Name = "SalesPivot"

    Set pc = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, SourceData:=tbl.Range)

    Set pt = pc.CreatePivotTable( _
        TableDestination:=wsPivot.Range("A3"), _
        TableName:="ptSalesByMonth")

    With pt
        .PivotFields("Month").Orientation = xlRowField
        .PivotFields("Region").Orientation = xlColumnField
        .AddDataField .PivotFields("Sales"), "Sum of Sales", xlSum
    End With
End Sub

Exécutez BuildSalesPivotByMonth et une nouvelle feuille "SalesPivot" doit apparaître avec Month en lignes, Region en colonnes et les totaux de Sales dans le corps — l’inverse du format original.

Tout était clair ?

Merci pour vos commentaires !

Section 4. Chapitre 3

Demandez à l'IA

Posez n'importe quelle question ou essayez l'une des questions suggérées pour commencer notre discussion

Création de tableaux croisés dynamiques avec VBA