Excel : créer un tableau croisé dynamique (TCD) et comprendre champs, valeurs et agrégations

Créer un premier TCD depuis un Tableau Excel

Un tableau croisé dynamique (TCD) sert à résumer rapidement une base de données en regroupant des informations (ex. par Catégorie, Région, Canal) et en calculant des indicateurs (ex. Somme de CA, Somme de Quantité). La logique repose sur 4 zones : Lignes, Colonnes, Valeurs et Filtres.

Insertion d’un TCD (source = Tableau)

• Cliquez dans une cellule de votre Tableau (pas besoin de sélectionner toute la plage).
• Onglet Insertion > Tableau croisé dynamique.
• Vérifiez la source : elle doit pointer vers le nom du Tableau (ex. TableauVentes), pas une plage “à la main”.
• Choisissez l’emplacement : Nouvelle feuille de calcul (recommandé pour débuter).
• Validez avec OK.

Excel crée une feuille avec une zone vide de TCD et, à droite, le panneau Liste des champs de tableau croisé dynamique.

Comprendre la liste des champs : dimensions vs mesures

Dans la liste des champs, vous voyez les colonnes de votre Tableau. Pour construire un TCD, on distingue généralement :

• Dimensions (champs de regroupement) : Catégorie, Région, Commercial, Canal, Année… Elles servent à découper l’analyse.
• Mesures (champs à calculer) : CA, Quantité… Elles vont dans Valeurs et sont agrégées (Somme, Nombre, Moyenne…).

Règle pratique : si un champ répond à “par quoi je veux regrouper ?” il va plutôt en Lignes/Colonnes/Filtres. S’il répond à “qu’est-ce que je veux mesurer ?” il va plutôt en Valeurs.

Construction pas à pas du TCD demandé

Objectif : obtenir une synthèse du CA et de la Quantité par Catégorie (lignes) et Canal (colonnes), filtrable par Année.

Continuez dans notre application.

Vous pouvez écouter le livre audio écran éteint, recevoir un certificat gratuit pour ce cours et accéder également à 5 000 autres cours en ligne gratuits.

Ou poursuivez votre lecture ci-dessous...

Téléchargez l'application

1) Placer les champs dans les zones

• Lignes : cochez ou glissez Catégorie dans la zone Lignes.
• Colonnes : glissez Canal dans la zone Colonnes.
• Valeurs : glissez CA puis Quantité dans la zone Valeurs.
• Filtres : glissez Année dans la zone Filtres.

Vous obtenez un tableau où chaque cellule correspond à une intersection Catégorie × Canal, avec deux indicateurs (CA et Quantité). En haut, le filtre Année permet de limiter l’analyse.

2) Vérifier les agrégations (Somme de CA, Somme de Quantité)

Par défaut, Excel met souvent Somme pour des colonnes numériques. Pour contrôler :

• Dans la zone Valeurs, cliquez sur la flèche à droite de Somme de CA > Paramètres des champs de valeurs.
• Dans Résumer les valeurs par, choisissez Somme.
• Répétez pour Quantité afin d’obtenir Somme.

Paramétrer les valeurs : Somme vs Nombre vs Moyenne, formats, renommage

Changer le type d’agrégation

Dans Paramètres des champs de valeurs, vous pouvez choisir :

• Somme : additionne les valeurs (ex. total de CA).
• Nombre : compte le nombre de lignes non vides (utile pour compter des enregistrements).
• Moyenne : calcule la moyenne des valeurs (ex. CA moyen par vente).

Exemple concret : si Quantité représente des unités vendues, Somme donne le volume total. Moyenne donnerait la quantité moyenne par ligne de vente (souvent une autre question métier).

Afficher le CA en devise (format de nombre)

Pour éviter de “mettre en forme” uniquement visuellement, utilisez le format du champ de valeurs :

• Flèche sur Somme de CA > Paramètres des champs de valeurs.
• Cliquez sur Format de nombre… (en bas).
• Choisissez Monétaire ou Comptabilité, sélectionnez le symbole (€) et le nombre de décimales.
• Validez.

Faites de même pour Somme de Quantité en choisissant un format Nombre (souvent sans décimales) et, si besoin, un séparateur de milliers.

Renommer les champs de valeurs

Pour rendre le TCD lisible :

• Dans Valeurs, ouvrez Paramètres des champs de valeurs.
• Dans Nom personnalisé, remplacez par exemple Somme de CA par CA (€).
• Remplacez Somme de Quantité par Quantité (u).

Astuce : évitez un nom identique à un champ existant (Excel peut refuser). Ajoutez un suffixe comme (€) ou Total.

Tri, mise en forme et lisibilité du TCD

Tri (ex. trier les catégories par CA décroissant)

• Cliquez sur une valeur de la colonne CA (€) (dans le TCD).
• Clic droit > Trier > Trier du plus grand au plus petit.

Le tri s’applique au champ de ligne (ici Catégorie) selon la mesure sélectionnée.

Sous-totaux et totaux généraux

• Cliquez dans le TCD > onglet Création (Outils de TCD).
• Sous-totaux : activez/désactivez selon le besoin (souvent utiles si vous avez plusieurs niveaux de lignes).
• Totaux généraux : affichez pour lignes et colonnes pour obtenir les totaux globaux, ou désactivez si vous voulez une matrice “pure”.

Disposition : compacte vs tabulaire, répétition des étiquettes

• Onglet Création > Disposition du rapport.
• Afficher en forme compacte : plus dense, pratique au début.
• Afficher en forme tabulaire : une colonne par champ de ligne, plus lisible et idéal pour copier/coller ou exporter.
• Répéter toutes les étiquettes d’élément : utile en forme tabulaire pour éviter les cellules vides dans les champs de lignes.

Lisibilité des nombres

• Utilisez Format de nombre (dans les paramètres du champ de valeurs) plutôt qu’un format appliqué manuellement aux cellules.
• Choisissez des décimales cohérentes : souvent 0 pour Quantité, 0 ou 2 pour CA selon le contexte.

Erreurs courantes et corrections

1) Un champ texte placé dans Valeurs

Symptôme : Excel affiche Nombre de … au lieu de Somme de … (car il ne peut pas sommer du texte).

• Vérifiez que le champ est bien une mesure numérique.
• Si vous vouliez compter des ventes, Nombre est peut-être correct (compte des lignes).
• Sinon, corrigez la colonne source pour qu’elle soit numérique, puis actualisez le TCD.

2) CA stocké en texte (Somme impossible ou résultats incohérents)

Symptômes fréquents : Nombre de CA au lieu de Somme de CA, ou des totaux à zéro/incorrects.

• Corrigez la colonne CA dans la source (conversion en nombre), puis Actualiser le TCD.
• Dans le TCD : clic droit > Actualiser (ou onglet Analyser > Actualiser).

3) TCD “vide” ou incomplet (source mal définie)

Symptômes : le TCD n’affiche rien, ou il manque des lignes récentes.

• Vérifiez que la source est bien le Tableau (nom de tableau) et non une plage figée.
• Onglet Analyser > Modifier la source de données : sélectionnez le Tableau correct.
• Actualisez.

4) Champ mal catégorisé (dimension vs mesure)

Symptôme : vous avez mis Année en Valeurs ou CA en Lignes, ce qui rend le TCD illisible.

• Déplacez Année vers Filtres (ou Lignes/Colonnes si vous voulez comparer plusieurs années).
• Déplacez CA et Quantité vers Valeurs.

Exercices (à rendre)

Consignes

Créez 3 feuilles de TCD (une par exercice) à partir du même Tableau source. Pour chaque TCD, appliquez une mise en page lisible et cohérente (formats, disposition, totaux…).

Exercice 1 — TCD par Région

• Lignes : Région
• Colonnes : Canal (ou aucun, si vous préférez une vue simple)
• Valeurs : Somme de CA, Somme de Quantité
• Filtre : Année

Objectif : choisir la meilleure disposition (compacte ou tabulaire) pour comparer les régions.

Exercice 2 — TCD par Commercial

• Lignes : Commercial
• Colonnes : Année (pour comparer les années) ou Canal (pour comparer les canaux)
• Valeurs : Somme de CA
• Filtre : Région (optionnel si disponible)

Objectif : trier les commerciaux par CA décroissant et appliquer un format monétaire.

Exercice 3 — TCD par Produit

• Lignes : Produit
• Colonnes : Canal
• Valeurs : Somme de Quantité (et éventuellement Somme de CA)
• Filtre : Année

Objectif : tester l’affichage des totaux généraux et vérifier la lisibilité des grands tableaux (répétition des étiquettes en tabulaire).

Question de compréhension (à répondre par écrit)

Expliquez la différence entre Nombre et Somme sur une colonne numérique. Donnez un exemple où Nombre est utile et un exemple où Somme est indispensable.

Livrables attendus

• 3 feuilles contenant chacune un TCD (Région, Commercial, Produit).
• Une check-list (copiée sous chaque TCD ou sur une feuille dédiée) indiquant les réglages appliqués :
  • Source = nom du Tableau
  • Lignes / Colonnes / Valeurs / Filtres conformes
  • Agrégation (Somme/Nombre/Moyenne) vérifiée
  • Formats : CA en devise, Quantité en nombre
  • Champs de valeurs renommés
  • Tri appliqué si pertinent
  • Disposition (compacte/tabulaire) choisie
  • Totaux généraux et sous-totaux réglés
  • Répétition des étiquettes (si tabulaire)