📊 Excel : comment créer un tableau croisé dynamique (TCD) en 5 étapes
Recommandés
Des masses de chiffres ne sont quasiment sans valeur si elles ne peuvent pas être analysées instantanément. Le tableau dynamique d’Excel ou TCD est un instrument extraordinaire qui permet de résumer, de filtrer, de comparer et de regarder vos chiffres sans écrire une seule formule compliquée. Les analystes quantitatifs utilisant Excel peuvent passer du temps pour le GFE, les spécialistes financiers pour les obligations et les actions, le marketing pour les données des consommateurs, les ressources humaines et la chaîne logistique. Quelques clics peuvent effectuer des questions clés.
- Quel service a généré le plus de chiffre d’affaires ce trimestre ?
- Combien d’heures un employé a-t-il déclaré en janvier ?
- Quel est le produit le plus vendu par région ?
🧩 Étape 1 : préparer la base de données
Un bon tableau croisé dynamique repose sur une structure de données propre. Voici les règles d’or :
- En-têtes bien nommés sur la première ligne
- Une seule information par colonne (ex : Date, Catégorie, Ventes…)
- Aucune cellule fusionnée
- Pas de lignes vides au milieu
💡 Astuce : transformez la plage de données en tableau structuré avec Ctrl + T. Cela rendra le TCD automatiquement extensible si vous ajoutez des lignes.
📐 Étape 2 : insérer un tableau croisé dynamique
Voici la démarche simple pour générer un TCD :
- Sélectionner n’importe quelle cellule de la table
- Aller dans Insertion > Tableau croisé dynamique
- Choisir :
- Emplacement (nouvelle feuille ou existante)
- Source (table ou plage)
Une interface intuitive apparaît ensuite sur la droite, permettant de glisser-déposer les champs.
🔍 Étape 3 : organiser les champs
Un TCD se construit par zones :
- 🧱 Lignes : ce que l’on veut détailler (ex : Mois, Région)
- 🧮 Valeurs : ce qu’on mesure (ex : Somme des ventes)
- 🎛 Filtres : pour sélectionner une portion des données
- 🔄 Colonnes : pour créer des comparaisons transversales (ex : mois côte à côte)
Excel agrège automatiquement les données (somme, moyenne, nombre…). D’un clic droit, on peut modifier le type de calcul.
📊 Étape 4 : rendre le TCD interactif
Pour passer d’un simple tableau à un outil de pilotage :
- Ajouter des segments : filtres visuels sur les champs
- Insérer une chronologie pour filtrer par date
- Appliquer un style visuel professionnel via l’onglet « Création »
- Ajouter des graphiques croisés dynamiques (colonne, camembert, etc.)
Le tout sans toucher à vos données d’origine.
🧠 Cas d’usage concrets
| Cas métier | Analyse via TCD |
|---|---|
| Marketing | Ventes par canal + produit |
| RH | Heures travaillées par service |
| Comptabilité | Dépenses par type et fournisseur |
| Logistique | Retards de livraison par entrepôt |
🎯 Étape 5 : exploiter pleinement votre tableau croisé dynamique
Une fois le TCD mis en place, l’analyse peut aller bien plus loin grâce à certaines fonctionnalités avancées.
🔄 Actualisation automatique
Chaque fois que vous ajoutez de nouvelles lignes à votre tableau structuré, vous devez actualiser le TCD :
- Clic droit > Actualiser
- Ou via l’onglet Analyser > Actualiser tout
💡 Astuce : Créez un raccourci clavier ou un bouton macro si vous travaillez avec plusieurs TCD.
🖼️ Personnaliser la mise en forme
Un tableau croisé dynamique n’a pas besoin d’être gris et rigide.
✅ Appliquez un style prédéfini :
Onglet Création > Styles de tableau croisé dynamique
✅ Ajoutez une mise en forme conditionnelle :
- Sur les valeurs (ex : rouges si en-dessous de la moyenne)
- Par rang ou classement (ex : barres de données pour visualiser l’échelle)
✅ Choisissez l’affichage :
- En % du total
- Cumul progressif
- Écarts (différences absolues ou relatives entre périodes)
🧩 Étape 6 : relier à des filtres dynamiques
Pour rendre votre tableau interactif :
- Insérez un segment :
→ Sélectionnez le TCD, allez dans Analyser > Insérer un segment
→ Choisissez une colonne (ex : Catégorie) - Ajoutez une chronologie :
→ Si vos données incluent une colonne Date, vous pouvez créer un filtre par mois, trimestre ou année
Ces éléments transforment votre TCD en tableau de bord cliquable, sans VBA.
💡 Bonus : astuces avancées
| Fonction | Utilisation |
|---|---|
| Filtrer les 10 premiers | Montrer les produits les plus vendus |
| Regrouper par mois ou année | Synthétiser les ventes temporelles |
| Afficher plusieurs valeurs | Somme, moyenne, % du total… côte à côte |
| Afficher les différences | Suivre l’évolution entre périodes |
📁 Prochaine étape : créer un tableau de bord complet
Avec le TCD comme moteur, vous pouvez :
- Ajouter des graphiques croisés dynamiques
- Créer une page de synthèse (KPI visuels, filtres globaux)
- Préparer des rapports automatisés réutilisables chaque mois
📥 Résultat dans Excel
Inclut :
- Données simulées
- TCD déjà configuré
- Segments pour filtrer par département et date
💡 Conseils d’optimisation
- Éviter les noms de champs ambigus (“Champ1”, “Colonne B”)
- Toujours actualiser le TCD après modification (
clic droit > Actualiser) - Nommer votre tableau source pour une meilleure gestion
- Garder vos données dans un seul onglet source
Modèle Excel dynamique : tableau structuré + TCD + script VBA
✅ Objectif
Créer un modèle Excel prêt à l’emploi avec tableau structuré, une feuille dédiée à un tableau croisé dynamique (TCD), et un script VBA intégré (optionnel) permettant de générer automatiquement le TCD à l’ouverture du fichier.
🔧 Étapes réalisées
1. Création du fichier Excel TCD_Donnees_TCD.xlsx
- Une feuille « Données » contient un tableau structuré nommé
Table_Ventes, avec des colonnes typiques :DateCatégorieProduitVentes
- Ce tableau est formaté dynamiquement pour permettre des TCD extensibles.
- Une deuxième feuille nommée « TCD » est ajoutée, prête à recevoir le tableau croisé dynamique.
2. Génération d’un script VBA TCD_VBA_Generation.bas
Ce fichier contient un code VBA prêt à être importé dans l’éditeur VBA (Alt + F11). Ce script :
- S’exécute automatiquement à l’ouverture du fichier (
Workbook_Open) - Nettoie la feuille « TCD »
- Crée un cache de données à partir de la table
Table_Ventes - Génère un TCD dans la feuille « TCD » à partir de :
- Lignes :
Catégorie - Colonnes :
Produit - Valeur : Somme des
Ventes
- Lignes :
📁 Script VBA à importer :
📌 Utilisation
Dans Excel :
- Ouvrir le fichier Excel
.xlsx - Aller dans l’éditeur VBA (
Alt + F11) - Importer le fichier
.bas:Fichier > Importer un fichier... - Enregistrer le fichier en
.xlsmpour activer les macros - Activer les macros à l’ouverture → Le TCD se génère automatiquement
🧩 Résultat
Vous disposez maintenant :
- D’un modèle de données propre et structuré
- D’une feuille de TCD prête
- D’un code VBA optionnel permettant d’automatiser l’analyse à l’ouverture
Private Sub Workbook_Open()
Dim wsData As Worksheet
Dim wsTCD As Worksheet
Dim ptCache As PivotCache
Dim pt As PivotTable
Script VBA : Générer un TCD à l’ouverture du fichier
Private Sub Workbook_Open()
Dim wsData As Worksheet
Dim wsTCD As Worksheet
Dim ptCache As PivotCache
Dim pt As PivotTable
' Définir les feuilles
Set wsData = ThisWorkbook.Sheets("Données")
Set wsTCD = ThisWorkbook.Sheets("TCD")
' Nettoyer l’ancienne table croisée
wsTCD.Cells.Clear
' Créer un cache de données à partir de la table Excel nommée
Set ptCache = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:="Table_Ventes")
' Créer le tableau croisé dynamique à l’endroit souhaité
Set pt = ptCache.CreatePivotTable( _
TableDestination:=wsTCD.Range("B3"), _
TableName:="TCD_Ventes")
' Configurer le TCD
With pt
.PivotFields("Catégorie").Orientation = xlRowField
.PivotFields("Produit").Orientation = xlColumnField
.AddDataField .PivotFields("Ventes"), "Somme des Ventes", xlSum
End With
End Sub
🧰 Manipulations utiles avec les TCD dans Excel
1. 🎛️ Actualiser les données
- Manuellement : clic droit dans le TCD →
Actualiser - Tous les TCD : onglet Données > Actualiser tout
- Automatiquement : avec une macro
Workbook_Open(voir plus bas)
2. 📌 Changer l’agrégation
Par défaut, Excel utilise SOMME. Vous pouvez changer pour :
MOYENNENB% du totalÉcart par rapport à…
➡️ Clic droit dans une cellule du TCD > Afficher les valeurs en tant que
3. 🧮 Afficher plusieurs mesures en parallèle
Ajouter plusieurs fois le même champ dans la zone Valeurs :
Exemple :
- Somme des ventes
- Moyenne des ventes
- % des ventes globales
4. 📅 Regrouper les dates
Clic droit sur un champ Date dans le TCD → Grouper
- Par jour, mois, trimestre, année
- Très utile pour créer une chronologie ou un comparatif temporel
5. 🔍 Filtrer le TCD
- Filtres standards : glisser un champ dans la zone
Filtres - Filtrer les N premiers : clic droit sur un champ →
Filtrer > 10 premiers éléments - Segments (Slicers) :
- Sélectionner le TCD
- Onglet
Analyse > Insérer un segment - Choisissez un champ (ex : Catégorie, Région)
6. 🖼️ Personnaliser l’apparence
- Onglet
Création:- Appliquer des styles prédéfinis
- Afficher ou masquer les sous-totaux et totaux généraux
- Ajouter une mise en forme conditionnelle sur les valeurs (barres, couleurs, icônes)
7. 📊 Créer un graphique croisé dynamique
- Sélectionner le TCD
- Onglet
Insertion > Graphique croisé dynamique - Se met à jour automatiquement avec le TCD
8. 🧼 Nettoyer ou réinitialiser un TCD
- Supprimer tous les champs d’un TCD :
clic droit > Afficher la liste des champspuis retirer les éléments - Supprimer le cache mémoire : pas possible sans VBA, mais on peut recréer un TCD propre
9. ⚙️ Créer un TCD à partir de plusieurs sources (Power Query)
Avec Power Query :
- Fusionner ou ajouter plusieurs tables
- Charger le résultat dans un tableau structuré
- Créer un TCD sur la base consolidée
10. 🤖 Automatiser avec du VBA
Exemple pour actualiser tous les TCD à l’ouverture :
Private Sub Workbook_Open()
ThisWorkbook.RefreshAll
End Sub
Recommandés
