Exemple de tableau croisé dynamique : 3 rapports concrets dans Excel

Vous avez un tableau de ventes qui s’allonge, une colonne par information et des dizaines de lignes, mais aucune réponse claire aux questions qui comptent vraiment : quel produit rapporte le plus, quel client commande le plus souvent, quel pays pèse le plus lourd dans le chiffre d’affaires ? Écrire des formules pour chaque question prendrait la matinée, et il faudrait les réécrire à chaque nouvelle ligne.

Le tableau croisé dynamique fait exactement ce travail, sans une seule formule. Pour le montrer concrètement, on part d’un vrai tableau de ventes et on en tire trois rapports différents : le montant total par produit, le nombre de commandes par client, puis le pourcentage des ventes par pays. Chaque étape est illustrée, et à la fin les trois rapports se mettent à jour ensemble quand les données changent.

Étape 1 : nettoyer le tableau des ventes avant de commencer

Le tableau de départ est un journal de ventes classique. Chaque ligne correspond à une vente et chaque colonne à une information : la date, le code article, le nom du produit, le nombre d’unités commandées, le montant, le client et le pays. Rien d’exotique, et c’est précisément le format qui fonctionne.

Avant d’insérer quoi que ce soit, quatre règles doivent être respectées. Elles paraissent triviales mais ce sont elles qui expliquent la majorité des messages d’erreur au moment de la création :

1. Aucune colonne ni ligne vide au milieu des données. Excel s’appuie sur les cellules contiguës pour deviner l’étendue du tableau ; un trou coupe la plage en deux.
2. Aucune ligne de sous-total. Le tableau croisé calcule lui-même ses totaux. Un sous-total déjà présent dans la source serait additionné comme une vente ordinaire et gonflerait tous les résultats.
3. Un seul niveau d’en-têtes, avec un titre unique par colonne. Ces titres deviennent les champs que l’on manipule ensuite. Deux colonnes nommées pareil, ou un en-tête laissé vide, et la création échoue.
4. Aucune cellule fusionnée dans la zone de données, sous peine de lignes tronquées à l’arrivée.

Dans l’exemple, cela veut dire supprimer la colonne inutilisée, les lignes vides et les lignes de sous-total qui traînaient dans le fichier d’origine.

Étape 2 : transformer la plage en tableau pour que le rapport suive

On place le curseur dans une cellule des données, puis on ouvre l’onglet Insertion et on clique sur Tableau croisé dynamique. Excel propose alors la plage qu’il a détectée, ici de A1 à G45.

C’est là que se cache le piège le plus courant. Cette plage est figée : si l’on saisit une nouvelle vente en ligne 46 la semaine suivante, elle ne fera pas partie du rapport, puisque la source s’arrête à la ligne 45. Beaucoup de gens redéfinissent la source à la main chaque mois sans savoir qu’il existe une solution définitive.

Cette solution consiste à convertir la plage en tableau Excel avant de créer le rapport, avec le raccourci Ctrl+L (ou Ctrl+T selon la version). La plage devient un objet nommé, Tableau1 par défaut, que l’on peut renommer dans l’onglet Création de tableau. Un tableau nommé s’étend automatiquement vers le bas dès qu’on ajoute une ligne, et le rapport branché dessus en hérite.

Si la mise en forme à bandes colorées appliquée d’office ne vous plaît pas, choisissez le style Aucun dans la galerie : le tableau reste un tableau, il retrouve juste son apparence d’origine. On peut ensuite lancer la création depuis l’onglet Création de tableau, ou revenir dans Insertion : la source affichée est désormais Tableau1 et non plus une plage de cellules. On valide, et le rapport vide apparaît sur une nouvelle feuille.

Étape 3 : premier rapport, le montant total par produit

Le volet de droite liste les champs disponibles, qui sont exactement les en-têtes de colonnes du tableau source. C’est pour cette raison que chaque titre devait être unique. En bas du volet, quatre zones attendent qu’on y dépose ces champs :

• Lignes : ce qu’on veut lister verticalement, une valeur par ligne.
• Valeurs : ce qu’on veut calculer, additionné par défaut.
• Colonnes : ce qui découpe le résultat horizontalement.
• Filtres : ce qui restreint l’ensemble du rapport.

Pour obtenir le chiffre d’affaires par produit, on glisse Produit dans la zone Lignes : la liste des articles apparaît. On glisse ensuite Ventes dans la zone Valeurs, et chaque produit reçoit à sa droite la somme de toutes ses ventes. En ajoutant Pays dans la zone Colonnes, les résultats se répartissent en une colonne par pays, croisés avec les produits. Sur les données de l’exemple, le total général atteint 6 792 100 $, réparti entre le Canada (3 949 100 $), les États-Unis (1 557 400 $) et la France (1 285 600 $).

La zone Filtres complète l’ensemble. En y déposant Client, une liste déroulante s’installe au-dessus du rapport et permet de n’afficher que les produits d’un client, ou d’une sélection de clients.

Deux réglages font gagner du temps ici. D’abord, l’onglet Insertion propose des tableaux croisés déjà préparés : il y a de bonnes chances que l’un d’eux corresponde au rapport que vous cherchez, ou s’en approche assez pour servir de base. Une suggestion du type « somme des ventes par pays » se transforme en rapport par produit en glissant simplement Produit dans la zone Lignes et en retirant Pays. Ensuite, les montants s’affichent en nombres bruts : un clic droit sur les valeurs, Format de nombre, et l’on choisit un format monétaire pour rendre le rapport lisible.

Étape 4 : deuxième rapport, le nombre de commandes par client

Deuxième question : combien de commandes chaque client a-t-il passées ? Plutôt que de repartir de zéro, on copie le premier rapport et on le colle un peu plus loin sur la même feuille. Les deux coexistent sans se gêner et resteront branchés sur la même source.

Dans la copie, on remplace Produit par Client dans la zone Lignes. Excel affiche alors la somme des ventes par client, ce qui n’est pas ce qu’on cherche : on veut compter des commandes, pas les additionner. Il faut donc changer le calcul, pas les données. Un clic sur le champ posé dans la zone Valeurs, puis Paramètres des champs de valeurs, ouvre la liste des synthèses disponibles : Somme, Nombre, Moyenne, Max, Min et quelques autres. On choisit Nombre et le rapport affiche le total de commandes par client, 44 au total dans l’exemple.

Deux détails à connaître. Le format monétaire hérité du premier rapport n’a plus de sens pour un compteur : on le repasse en format standard. Et il existe un raccourci plus direct que la boîte de dialogue, le clic droit puis Synthétiser les valeurs par, qui donne accès aux mêmes calculs et permet de basculer de l’un à l’autre en deux clics.

Étape 5 : troisième rapport, le pourcentage des ventes par pays

Troisième question : quel pays représente la plus grosse part des ventes ? On copie de nouveau un rapport existant, on met Pays dans la zone Lignes, et on ajoute une deuxième fois le champ Ventes dans la zone Valeurs. Ce doublon est volontaire : une colonne gardera les montants, l’autre affichera leur poids relatif.

Sur cette seconde colonne, un clic droit puis Afficher les valeurs donne accès à toute une série de calculs relatifs. On sélectionne % du total général et chaque pays affiche instantanément sa part. Avec les chiffres de l’exemple, le Canada pèse près de 58 % des ventes, devant les États-Unis (environ 23 %) et la France (environ 19 %).

Ce menu mérite qu’on s’y attarde, car il remplace beaucoup de calculs manuels : pourcentage du total de la colonne ou de la ligne, différence par rapport à une autre valeur, résultat cumulé, classement du plus grand au plus petit. Autant d’analyses obtenues sans écrire une formule.

Étape 6 : présenter, renommer, trier et actualiser

Les trois rapports existent, il reste à les rendre présentables. Toutes ces options se trouvent dans les onglets Analyse du tableau croisé dynamique et Création.

On peut d’abord regrouper les données à deux niveaux. En déplaçant Pays vers la zone Colonnes et en glissant Client dans la zone Lignes juste au-dessus de Produit, chaque produit se retrouve rangé sous son client, avec des sous-totaux par client et des boutons pour replier ou déplier les groupes. Ces boutons se masquent depuis l’onglet Analyse, section Afficher.

Les totaux se contrôlent dans l’onglet Création : Sous-totaux permet de ne pas les afficher du tout, et Totaux généraux de les désactiver pour les lignes, pour les colonnes, ou pour les deux. Juste à côté, Disposition du rapport propose l’affichage sous forme tabulaire, qui sépare les clients et les produits dans deux colonnes distinctes, ainsi que l’option Répéter toutes les étiquettes pour remplir les cellules laissées vides. Pratique quand le rapport doit être relu ailleurs ou exporté. La galerie de styles termine la mise en forme.

Trois manipulations complètent le tout :

1. Retirer un champ : on le fait glisser hors de sa zone jusqu’à ce qu’une croix rouge apparaisse.
2. Renommer les étiquettes : on tape directement le nouveau libellé dans la cellule. Attention, Excel refuse un intitulé identique au nom d’un champ existant. L’astuce consiste à ajouter un simple espace à la fin, ce qui suffit à lever le conflit tout en restant invisible à l’écran. Le même truc fonctionne pour raccourcir « Total général ».
3. Trier les résultats : les valeurs sortent classées par ordre alphabétique. Un clic droit, Trier, puis du plus grand au plus petit, et le produit le plus rentable remonte en tête.

Reste le test décisif. On retourne dans le tableau source, on ajoute une vente avec un nouveau produit, un nouveau client et un nouveau pays. Dans la feuille des rapports, un clic droit puis Actualiser et les trois tableaux se mettent à jour d’un coup. Le nouveau produit se place tout seul au bon rang puisque le tri du plus grand au plus petit reste actif, et le nouveau client comme le nouveau pays apparaissent dans les rapports concernés.

Erreurs fréquentes et astuces

• Ajouter des lignes en dehors du tableau source. Si la source est une plage figée du type A1:G45, tout ce qui vient après est ignoré. Convertir en tableau avec Ctrl+L règle le problème une fois pour toutes.
• Oublier d’actualiser. Un tableau croisé ne se recalcule pas en direct quand la source change. Sans un passage par Actualiser, il affiche encore les anciens chiffres.
• Un en-tête vide ou dupliqué. C’est la cause la plus banale d’un refus à la création : chaque colonne doit porter un titre, et un titre différent des autres.
• Vouloir renommer une étiquette avec un nom de champ déjà pris. Ajoutez un espace à la fin du libellé et Excel accepte.
• Un format de nombre incohérent après un changement de calcul. Le format ne suit pas la nouvelle synthèse : repassez en standard pour un compteur, en monétaire pour des montants.
• Des sous-totaux restés dans la source. Ils sont comptés comme des ventes normales et faussent silencieusement tous les rapports.

Questions fréquentes

Faut-il connaître des formules pour faire un tableau croisé dynamique ?

Non, et c’est tout l’intérêt. Aucune formule n’est saisie du début à la fin : on dépose des champs dans des zones et Excel choisit le calcul, qu’on ajuste ensuite via Paramètres des champs de valeurs ou Synthétiser les valeurs par.

Où trouve-t-on le tableau croisé dynamique dans Excel ?

Dans l’onglet Insertion, bouton Tableau croisé dynamique. Si la plage a été convertie en tableau, l’onglet Création de tableau propose le même bouton. L’onglet Insertion suggère également des rapports déjà configurés, souvent proches du besoin.

Peut-on créer plusieurs tableaux croisés sur la même feuille ?

Oui, et c’est même la façon la plus rapide de procéder : on copie un rapport existant puis on modifie les champs de la copie. Les trois rapports de cet exemple vivent sur une seule feuille et s’actualisent ensemble.

Pourquoi mon nouveau produit n’apparaît-il pas dans le rapport ?

Deux causes possibles. Soit la source est une plage figée qui ne couvre pas la nouvelle ligne, soit le rapport n’a pas été actualisé. Vérifiez d’abord que la source est bien un tableau nommé, puis faites un clic droit et Actualiser.

Comment trier un tableau croisé du plus rentable au moins rentable ?

Un clic droit sur une valeur de la colonne concernée, puis Trier et l’option du plus grand au plus petit. Le tri reste actif après actualisation : les nouvelles données se rangent automatiquement au bon endroit.

Si vous travaillez plutôt dans le navigateur, la logique des quatre zones reste identique ailleurs : notre guide du tableau croisé dynamique pour les nuls dans Google Sheets reprend la même démarche. Et une fois vos données propres, elles se réutilisent facilement dans d’autres outils, par exemple en les passant par notre convertisseur Excel vers JSON, ou en publiant un rapport mis en forme grâce au convertisseur Word vers HTML.

Trois questions, trois rapports, et pas une seule formule saisie. C’est là que le tableau croisé dynamique devient vraiment utile : le temps investi une fois dans la préparation des données se rentabilise à chaque nouvelle question posée. Partez d’un tableau propre, convertissez-le en tableau nommé, puis testez les zones et les calculs sur vos propres chiffres. Vous découvrirez sans doute des analyses auxquelles vous n’auriez pas pensé.