Skip to content
Learni
Voir tous les tutoriels
Excel

Comment créer des tableaux croisés dynamiques VBA en 2026

14 minINTERMEDIATE
Tableaux croisés dynamiquesAnalyse de donnéesExcel VBAAutomatisation Excel
Read in English

Introduction

Les tableaux croisés dynamiques (TCD) sont essentiels pour analyser de grands volumes de données dans Excel, mais les recréer manuellement chaque fois est chronophage. Avec VBA, vous automatisez leur création, configuration et mise à jour, gagnant des heures sur des rapports récurrents. Ce tutoriel intermédiaire vous guide pas à pas pour générer un TCD complet à partir de données de ventes : ajout de champs lignes, colonnes, valeurs, filtres et groupements. Imaginez transformer une table de 1000 lignes en dashboard interactif en un clic. Nous utilisons des exemples concrets sur des données ventes (produits, régions, montants), avec codes 100% fonctionnels à copier-coller dans l'éditeur VBA (Alt+F11). À la fin, vous saurez gérer des scénarios avancés comme les slicers ou les calculs personnalisés. Idéal pour analystes BI ou financiers intermédiaires.

Prérequis

  • Excel 2016 ou supérieur (365 recommandé pour performances).
  • Connaissances de base en VBA : procédures Sub, boucles, objets Worksheet.
  • Données sources prêtes : une feuille 'Donnees' avec en-têtes en A1:E1 (Date, Produit, Region, Quantite, Montant).
  • Activez les macros : Fichier > Options > Centre de gestion de la confidentialité > Paramètres > Activer toutes les macros.

Insérer les données d'exemple

Module1.bas
Sub InsererDonneesExemple()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets.Add
    ws.Name = "Donnees"
    
    ' En-têtes
    ws.Range("A1:E1").Value = Array("Date", "Produit", "Region", "Quantite", "Montant")
    
    ' Données d'exemple (10 lignes réalistes)
    ws.Range("A2:E11").Value = Array( _
        Array(#1/1/2026#, "Laptop", "Nord", 5, 2500), _
        Array(#1/2/2026#, "Souris", "Sud", 20, 25), _
        Array(#1/3/2026#, "Laptop", "Nord", 3, 2500), _
        Array(#1/4/2026#, "Clavier", "Est", 10, 80), _
        Array(#1/5/2026#, "Ecran", "Ouest", 2, 400), _
        Array(#1/6/2026#, "Laptop", "Sud", 4, 2500), _
        Array(#1/7/2026#, "Souris", "Est", 15, 25), _
        Array(#1/8/2026#, "Clavier", "Nord", 8, 80), _
        Array(#1/9/2026#, "Ecran", "Ouest", 1, 400), _
        Array(#1/10/2026#, "Laptop", "Est", 6, 2500) _
    )
    
    ws.Columns("A:E").AutoFit
    MsgBox "Données d'exemple insérées dans 'Donnees'"
End Sub

Ce code crée une feuille 'Donnees' avec 10 lignes réalistes de ventes, incluant dates et montants. Il utilise Array pour insérer rapidement les valeurs, évitant les boucles lentes. Lancez-le d'abord pour tester les TCD suivants ; piège : vérifiez que les dates sont au format Date pour les groupements.

Étape 1 : Comprendre le PivotCache

Le PivotCache est le moteur des TCD : il stocke les données en mémoire pour des rafraîchissements ultra-rapides (jusqu'à 10x plus vite qu'une requête directe). Sans lui, chaque TCD recopie les données, gaspillant RAM. Nous le créons sur la plage A1:E11 de 'Donnees', base pour tous les TCD. Analogie : comme un réservoir d'eau alimentant plusieurs robinets (TCD).

Créer le PivotCache et le premier TCD

Module1.bas
Sub CreerPremierTCD()
    Dim wsData As Worksheet, wsPivot As Worksheet
    Dim pc As PivotCache
    Dim pt As PivotTable
    
    Set wsData = Sheets("Donnees")
    Set wsPivot = Sheets.Add
    wsPivot.Name = "TCD_Ventes"
    
    ' Créer PivotCache
    Set pc = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=wsData.Range("A1:E11"))
    
    ' Créer TCD
    Set pt = pc.CreatePivotTable( _
        TableDestination:=wsPivot.Range("A3"), _
        TableName:="TCD_Ventes_Total")
    
    MsgBox "Premier TCD créé dans 'TCD_Ventes'"
End Sub

Ce Sub génère un PivotCache sur vos données et un TCD vide dans une nouvelle feuille. SourceType xlDatabase cible les plages tabulaires ; TableDestination place le TCD en A3 pour laisser de l'espace aux titres. Piège : si la plage change, mettez-la à jour manuellement pour éviter les erreurs 'Invalid procedure'.

Étape 2 : Ajouter champs lignes et valeurs

Maintenant, peuplons le TCD : champs lignes (Region, Produit) pour la hiérarchie, valeurs (Somme de Montant, Moyenne de Quantite). VBA accède aux champs via PivotFields : .Orientation = xlRowField positionne en lignes. Exemple concret : total des ventes par région/produit.

Configurer lignes et valeurs

Module1.bas
Sub ConfigurerLignesValeurs()
    Dim pt As PivotTable
    Set pt = Sheets("TCD_Ventes").PivotTables("TCD_Ventes_Total")
    
    ' Champs lignes : Region puis Produit
    With pt.PivotFields("Region")
        .Orientation = xlRowField
        .Position = 1
    End With
    With pt.PivotFields("Produit")
        .Orientation = xlRowField
        .Position = 2
    End With
    
    ' Valeurs : Somme Montant, Moyenne Quantite
    With pt.AddDataField(pt.PivotFields("Montant"), "Somme de Montant", xlSum)
        .NumberFormat = "#,##0 €"
    End With
    pt.AddDataField pt.PivotFields("Quantite"), "Moyenne Quantite", xlAverage
    
    MsgBox "Lignes et valeurs configurées"
End Sub

Ce code ajoute Region/Produit en lignes hiérarchiques et deux valeurs calculées. .NumberFormat formate les euros ; Position contrôle l'ordre. Piège : omettre .Subtotals(1) = False peut surcharger avec des sous-totaux inutiles.

Étape 3 : Ajouter filtres et colonnes

Les filtres (Date) permettent de slicer les données ; colonnes (ex. groupement par mois) croisent avec les lignes pour une vue matricielle. Utilisez PivotItems pour filtrer dynamiquement, comme 'Nord' uniquement.

Ajouter filtres et colonnes

Module1.bas
Sub AjouterFiltresColonnes()
    Dim pt As PivotTable
    Set pt = Sheets("TCD_Ventes").PivotTables("TCD_Ventes_Total")
    
    ' Filtre sur Date
    With pt.PivotFields("Date")
        .Orientation = xlPageField
        .Position = 1
        .PivotItems("01/01/2026").Visible = True  ' Filtre exemple
    End With
    
    ' Colonnes : Quantite (pour croisement)
    With pt.PivotFields("Quantite")
        .Orientation = xlColumnField
        .Position = 1
    End With
    
    MsgBox "Filtres et colonnes ajoutés"
End Sub

Filtre xlPageField crée un dropdown ; .Visible masque les items inutiles. xlColumnField croise Quantite avec lignes. Piège : sur dates, utilisez ClearAllFilters avant pour éviter les conflits.

Étape 4 : Grouper et formater

Groupement sur dates (par mois) automatise les agrégations temporelles. Ajoutez mise en forme conditionnelle via VBA pour highlighter les tops ventes.

Grouper dates et formater

Module1.bas
Sub GrouperEtFormater()
    Dim pt As PivotTable
    Set pt = Sheets("TCD_Ventes").PivotTables("TCD_Ventes_Total")
    
    ' Grouper Date par mois
    Dim pfDate As PivotField
    Set pfDate = pt.PivotFields("Date")
    pfDate.ClearManualFilter
    pfDate.PivotItems.Group Start:=True, End:=True, Periods:=Array(False, False, False, False, True, False, False)
    
    ' Mise en forme : grand total en bold
    pt.TableStyle2 = "PivotStyleLight16"
    pt.RowGrand = True
    pt.ColumnGrand = True
    
    MsgBox "Groupement et format appliqués"
End Sub

Periods Array groupe par mois (index 4). TableStyle2 applique un thème pro. Piège : groupement échoue si doublons ; triez d'abord les données sources.

Rafraîchir et ajouter slicer

Module1.bas
Sub RafraichirEtSlicer()
    Dim pt As PivotTable
    Set pt = Sheets("TCD_Ventes").PivotTables("TCD_Ventes_Total")
    
    ' Rafraîchir cache
    pt.PivotCache.Refresh
    
    ' Ajouter slicer sur Region (Excel 2010+)
    Dim sc As SlicerCache
    Dim sl As Slicer
    Set sc = ThisWorkbook.SlicerCaches.Add2(pt, "Region")
    Set sl = sc.Slicers.Add(Sheets("TCD_Ventes"), , "Slicer_Region", "Régions", 400, 50, 200, 200)
    
    MsgBox "TCD rafraîchi avec slicer Region"
End Sub

Refresh met à jour après changements sources. SlicerCaches.Add2 lie interactivement au TCD. Piège : slicers indisponibles < Excel 2010 ; testez ActiveWorkbook.SlicerCaches.Count >0.

Bonnes pratiques

  • Toujours utiliser PivotCache partagé : liez plusieurs TCD au même cache pour économiser 50-70% RAM.
  • Validez la plage SourceData dynamiquement : wsData.UsedRange au lieu de fixe A1:E11.
  • Gérez les erreurs : On Error Resume Next avant .PivotItems pour éviter crashes sur items manquants.
  • Optimisez : Désactivez ScreenUpdating avant (Application.ScreenUpdating=False) et réactivez après.
  • Documentez : Ajoutez commentaires sur chaque PivotField pour maintenance.

Erreurs courantes à éviter

  • Plage SourceData invalide : Erreur 1004 si données vides ; solution : If wsData.UsedRange.Rows.Count < 2 Then Exit Sub.
  • Champs déjà orientés : Double ajout crash ; clear d'abord pt.PivotFields("Champ").Orientation = xlHidden.
  • Rafraîchissement ignoré : Sans .Refresh, changements sources invisibles ; forcez-le après inserts.
  • Mémoire leak : Ne Set pas les objets sans Nothing ; ajoutez Set pt=Nothing en fin Sub.

Pour aller plus loin

  • Documentation MS : PivotTable VBA Reference.
  • Power Pivot pour datasets >1M lignes.
  • Intégrez à Power Automate pour TCD auto-envoyés.
Découvrez nos formations Learni sur Excel VBA avancé pour monétiser vos skills BI.