Les tableaux croisés dynamiques dans Excel
Un tableau croisé dynamique (TCD) résume des centaines de lignes en quelques secondes : chiffre d’affaires par région et par mois, nombre de stagiaires par formation, part de chaque formateur… sans écrire une seule formule. On glisse des champs, Excel calcule, et l’on réorganise l’analyse à volonté.
CréerInsertion › Tableau croisé dynamique
ActualiserAlt + F5
Tout actualiserCtrl + Alt + F5
GraphiqueAnalyse › Graphique croisé
1
Créer un tableau croisé dynamique
La source doit être une liste propre : une ligne de titres, une information par colonne, pas de ligne vide, pas de cellules fusionnées. Idéalement, un tableau de données (Ctrl + L) : le TCD suivra automatiquement les nouvelles lignes.
- Cliquez dans la liste, puis Insertion › Tableaux croisés dynamiques recommandés : Excel propose plusieurs synthèses toutes faites (la suggestion la plus rapide pour débuter).
- Ou Insertion › Tableau croisé dynamique : vérifiez la plage, choisissez Nouvelle feuille de calcul, OK.
- Dans le volet Champs de tableau croisé dynamique, cochez ou faites glisser les champs vers les zones Filtres, Colonnes, Lignes et Valeurs.
Analyse rapideSélectionnez la liste puis Ctrl + Q › Tableaux : un aperçu des TCD possibles s’affiche au survol.
Titres obligatoiresChaque colonne doit avoir un titre unique : un titre vide provoque le message « Le nom du champ de tableau croisé dynamique n’est pas valide ».
Simulateur : ventes de formations du 1er semestre
Filtres
Colonnes
Lignes
Valeurs
Segment chronologique (Date) :
Voir les données sources (24 lignes)
2
Modifier l’organisation des données
| Zone | Rôle | Exemple |
|---|---|---|
| Lignes | Une ligne par valeur du champ | Formation |
| Colonnes | Une colonne par valeur du champ | Région |
| Valeurs | Le calcul : Somme pour les nombres, Nombre pour le texte | Somme de Montant HT |
| Filtres | Limite tout le tableau à un élément | Formateur = Julie |
PivoterFaites glisser un champ de Lignes vers Colonnes (ou l’inverse) : le tableau se réorganise instantanément. C’est le « croisé » de tableau croisé.
DispositionCréation › Disposition du rapport : Forme compactée, Plan ou Tabulaire, avec ou sans répétition des étiquettes.
3
Grouper des informations et segments chronologiques
| Type | Méthode | Résultat |
|---|---|---|
| Date | Clic droit sur une date › Grouper › Mois, Trimestres, Années | Janvier… Juin, ou Trim1, Trim2 |
| Numérique | Clic droit › Grouper › Début, Fin, Par (5) | 1-5, 6-10, 11-15 stagiaires |
| Texte | Sélectionnez Word et Outlook (Ctrl) › clic droit › Grouper, puis renommez Groupe1 | « Traitement de texte » |
Le segment chronologique est un filtre visuel pour les dates : Analyse du tableau croisé dynamique › Insérer une chronologie, cochez Date, puis faites glisser la barre sur la période voulue. Un segment classique (Insérer un segment) fait de même avec des boutons pour un champ texte.
Relier plusieurs TCDClic droit sur la chronologie › Connexions de rapport : un seul segment pilote tous les TCD issus de la même source, idéal pour un tableau de bord.
Groupement impossible« Impossible de grouper cette sélection » : la colonne contient une cellule vide ou un texte parmi les dates. Nettoyez la source puis actualisez.
4
Calculer une répartition et modifier le calcul
- Clic droit sur une valeur › Synthétiser les valeurs par : Somme, Nombre, Moyenne, Max, Min…
- Clic droit › Afficher les valeurs : % du total général (répartition), % du total de la colonne, % du total de la ligne, Différence par rapport, Cumul…
- Pour voir à la fois le montant et sa part, glissez deux fois Montant HT dans Valeurs et changez l’affichage du second.
Paramètres des champs de valeursDouble-cliquez sur l’en-tête « Somme de Montant HT » : on y renomme le champ, on change le calcul et le format de nombre (€, %).
Dans le simulateurFormation en lignes, Région en colonnes, puis « % du total de la ligne » : on lit la part de chaque région dans chaque formation.
5
Créer une formule personnalisée
| Outil | Principe | Exemple |
|---|---|---|
| Champ calculé | Nouvelle colonne virtuelle calculée à partir d’autres champs | Marge = 'Montant HT' - Coût |
| Élément calculé | Nouvel élément à l’intérieur d’un champ, calculé à partir d’autres éléments | Dans Région : « Nord + Est » = Nord + Est |
Analyse du tableau croisé dynamique › Calculs › Champs, éléments et jeux › Champ calculé (ou Élément calculé). Tapez le nom, construisez la formule en double-cliquant sur les champs, Ajouter, OK.
Somme d’abordUn champ calculé travaille sur les sommes des champs :
=Montant/Stagiaires donne un prix moyen correct, mais une moyenne de ratios n’est pas possible.Élément calculéIl est incompatible avec le groupement et les champs de date groupés : préférez-le pour de petites listes de texte.
6
Actualiser, trier et mettre en forme
- Actualiser : un TCD ne se met pas à jour tout seul. Clic droit › Actualiser (Alt + F5) ou Données › Actualiser tout. Si la source a grandi hors d’un tableau de données : Changer la source de données.
- Trier : clic droit sur une valeur › Trier du plus grand au plus petit, ou flèche de l’étiquette de ligne › Trier de A à Z.
- Mise en forme conditionnelle : sélectionnez une valeur › Accueil › Mise en forme conditionnelle, puis choisissez l’option d’application « Toutes les cellules affichant les valeurs « Somme de Montant HT » » pour qu’elle suive le tableau.
Actualiser à l’ouvertureOptions du tableau croisé dynamique › Données › cochez Actualiser les données lors de l’ouverture du fichier.
Dans le simulateurAjoutez une vente en juillet : le TCD ne bouge pas tant que vous n’avez pas cliqué sur Actualiser.
7
Mettre deux tables en relation
Les ventes contiennent le nom du formateur ; une autre table donne son statut (salarié, indépendant) et son tarif. Plutôt que de recopier ces colonnes avec RECHERCHEV, on relie les deux tables dans le modèle de données.
VentesDateRégionFormationFormateur 🔑Montant HT
∞ ─────── 1
FormateursFormateur 🔑StatutTarif jour
- Mettez chaque liste sous forme de tableau (Ctrl + L) et nommez-les Ventes et Formateurs.
- Données › Relations › Nouveau : Ventes[Formateur] (colonne externe) ↔ Formateurs[Formateur] (colonne associée).
- Insertion › Tableau croisé dynamique › À partir du modèle de données (ou cochez « Ajouter ces données au modèle de données »).
- Dans le volet, onglet Tous : prenez Statut dans Formateurs en Lignes et Montant HT dans Ventes en Valeurs.
Clé uniqueDans la table Formateurs, chaque nom ne doit apparaître qu’une fois : c’est le côté « 1 » de la relation.
Nombre distinctAvec le modèle de données, « Synthétiser par » propose aussi Nombre distinct (par exemple le nombre de formations différentes par région).
8
Transformer le TCD en graphique
- Cliquez dans le TCD › Analyse du tableau croisé dynamique › Graphique croisé dynamique, choisissez un histogramme groupé.
- Le graphique est lié au TCD : filtres, groupements, tris et segments s’appliquent aux deux.
- Masquez les boutons de champ (clic droit › Masquer tous les boutons de champ sur le graphique) pour une présentation propre.
Tableau de bordUn TCD + un graphique croisé + une chronologie connectée : le trio de base d’un tableau de bord interactif.
Types interditsLes nuages de points, bulles et boursiers ne sont pas disponibles pour un graphique croisé dynamique.
Exercice de synthèse : analyse des ventes du semestre
Télécharger le fichier d’exercice (Énoncé et Corrigé)
- Feuille Ventes (tableau de données, 24 lignes) : créez un TCD Formation en lignes, Région en colonnes, Somme de Montant HT. Total général : 53 690 € ; Excel : 21 420 €.
- Groupez les dates par trimestres, insérez une chronologie et filtrez avril à juin.
- Affichez les valeurs en % du total général : Excel représente 39,90 % des ventes.
- Ajoutez le champ calculé Marge = ‘Montant HT’ – Coût : marge totale 23 070 €.
- Reliez Ventes et Formateurs (Données › Relations) et analysez le montant par Statut. Terminez par un graphique croisé dynamique.