Afficher, masquer ou changer les sous-totaux d’un TCD Excel
Pour personnaliser les sous-totaux d’un tableau croisé dynamique, fais un clic droit sur un élément du champ concerné, choisis Paramètres de champ..., puis sélectionne Personnalisé dans l’onglet « Sous-totaux et filtres » et coche les fonctions voulues, comme Somme, Moyenne, Nombre ou Max. Dès que tu en coches plusieurs, Excel ajoute une ligne par fonction au bas de chaque groupe. Le total général, lui, reste une somme et se règle à part, dans l’onglet Création.
Le classeur de cette page contient quinze ventes de trois régions, rangées dans le tableau « Ventes » avec le vendeur, le produit, la quantité et le montant. Les régions n’ont pas le même nombre de ventes, six pour le Nord, cinq pour le Sud et quatre pour l’Est, si bien que Somme, Moyenne et Nombre donnent des résultats bien différents d’une région à l’autre.
Sous-totaux d’un TCD
Télécharge le classeur de l’article pour refaire chaque étape sur les mêmes données.
L’onglet « Ventes », avec la ligne jaune où taper une nouvelle vente
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Région | Vendeur | Produit | Quantité | Montant |
| 2 | Nord | Claire | Écran | 3 | 540 € |
| 3 | Nord | Claire | Clavier | 5 | 290 € |
| 4 | Nord | Hugo | Souris | 10 | 250 € |
| 5 | Nord | Hugo | Écran | 2 | 360 € |
| 6 | Nord | Claire | Souris | 4 | 100 € |
| 7 | Nord | Hugo | Clavier | 2 | 116 € |
| 8 | Sud | Inès | Écran | 5 | 900 € |
| 9 | Sud | Inès | Clavier | 2 | 118 € |
| 10 | Sud | Marc | Clavier | 6 | 348 € |
| 11 | Sud | Marc | Écran | 1 | 180 € |
| 12 | Sud | Marc | Souris | 8 | 204 € |
| 13 | Est | Paul | Écran | 4 | 720 € |
| 14 | Est | Paul | Souris | 6 | 150 € |
| 15 | Est | Sarah | Clavier | 3 | 174 € |
| 16 | Est | Sarah | Écran | 6 | 1 080 € |
| 17 |
Créer des sous-totaux dans un tableau croisé dynamique
Un tableau croisé dynamique calcule un sous-total dès que deux champs se suivent dans la zone Lignes, sans qu’on le lui demande. Avec la région au-dessus du vendeur, chaque région résume ses vendeurs, et la ligne Total général additionne le tout. Il suffit donc de placer les champs dans le bon ordre pour obtenir les sous-totaux.
Insérer le rapport sur les ventes
- 01Clique dans le tableau de l’onglet « Ventes », puis choisis Tableau croisé dynamique dans l’onglet Insertion.
- 02Clique sur OK dans la fenêtre qui s’ouvre, et Excel place le rapport sur une nouvelle feuille.
- 03Dans le volet des champs, coche Montant pour l’envoyer dans la zone Valeurs.
Placer la région au-dessus du vendeur dans la zone Lignes
- 01Glisse Région dans la zone Lignes.
- 02Glisse ensuite Vendeur dans la même zone, sous Région.
Le rapport affiche chaque région avec son sous-total sur sa propre ligne, 2 124 € pour l’Est, 1 656 € pour le Nord et 1 750 € pour le Sud, et les vendeurs en retrait en dessous. Le total général de 5 530 € ferme la liste. Excel place les sous-totaux en haut du groupe tant que le rapport garde sa forme compactée, celle qu’il prend à l’insertion, et la partie sur la position des sous-totaux montre comment les descendre.
Ce sous-total est réglé sur Automatique, ce qui veut dire qu’il reprend la fonction du champ de valeurs, ici la somme. Pour en changer, ou pour en afficher plusieurs à la fois, il faut ouvrir les paramètres du champ Région, celui qui porte le sous-total, et non ceux de Montant.
Choisir Somme, Moyenne, Nombre ou Max dans les sous-totaux personnalisés
Les sous-totaux personnalisés remplacent la fonction automatique par celles que tu coches, et Excel en accepte plusieurs à la fois. Tu peux ainsi montrer sous chaque région son chiffre d’affaires, son nombre de ventes, sa vente moyenne et sa plus grosse vente, sans ajouter un seul champ dans la zone Valeurs.
Ouvrir les paramètres du champ Région
- 01Fais un clic droit sur une région du rapport, par exemple Est.
- 02Choisis Paramètres de champ... dans le menu, puis clique sur l’onglet « Sous-totaux et filtres » de la fenêtre qui s’ouvre.
Le bouton Paramètres de champs de l’onglet Analyse du tableau croisé dynamique ouvre la même fenêtre, à condition d’avoir d’abord cliqué sur une région.
Cocher les fonctions sous Personnalisé
- 01Dans la rubrique Sous-totaux, sélectionne Personnalisé à la place d’Automatique.
- 02Dans la liste « Sélectionnez une ou plusieurs fonctions », coche Somme, Nombre, Moyenne et Max.
- 03Clique sur OK.
Chaque région reçoit alors quatre lignes sous ses vendeurs, qui s’appellent pour l’Est « Somme Est », « Nombre Est », « Moyenne Est » et « Max. Est », et la ligne de la région elle-même reste vide. Excel range ces quatre lignes au bas du groupe même si le rapport demande les sous-totaux en haut, puisque quatre montants ne tiennent pas sur une seule ligne.
Le rapport avec les sous-totaux personnalisés de la région
| A | B | |
|---|---|---|
| 3 | Étiquettes de lignes | Somme de Montant |
| 4 | Est | |
| 5 | Paul | 870 |
| 6 | Sarah | 1254 |
| 7 | Somme Est | 2124 |
| 8 | Nombre Est | 4 |
| 9 | Moyenne Est | 531 |
| 10 | Max. Est | 1080 |
| 11 | Nord | |
| 12 | Claire | 930 |
| 13 | Hugo | 726 |
| 14 | Somme Nord | 1656 |
| 15 | Nombre Nord | 6 |
| 16 | Moyenne Nord | 276 |
| 17 | Max. Nord | 540 |
| 18 | Sud | |
| 19 | Inès | 1018 |
| 20 | Marc | 732 |
| 21 | Somme Sud | 1750 |
| 22 | Nombre Sud | 5 |
| 23 | Moyenne Sud | 350 |
| 24 | Max. Sud | 900 |
| 25 | Total général | 5530 |
Le total général garde ses 5 530 €, puisqu’il suit la fonction du champ de valeurs et ignore les fonctions cochées pour la région. Si tu as donné un format en euros au champ de valeurs, les lignes Nombre le prennent aussi et affichent 4 €, 6 € et 5 €, alors qu’elles comptent des ventes et non des montants.
Quand une seule fonction est cochée, Excel la place de nouveau sur la ligne de la région, comme le sous-total automatique. Avec Moyenne seule, l’Est affiche 531 €, le Nord 276 € et le Sud 350 €, sans aucune ligne en plus.
Pourquoi la moyenne d’un sous-total ne correspond pas aux vendeurs affichés
Un sous-total personnalisé applique sa fonction aux lignes de la source, et pas aux montants que le rapport affiche pour chaque vendeur. La Moyenne du Nord divise donc ses 1 656 € par ses six ventes, ce qui donne 276 €, alors que la moyenne de Claire et de Hugo, 930 € et 726 €, vaudrait 828 €.
Il en va de même pour Nombre, qui compte les six ventes du Nord alors que la région n’a que deux vendeurs, et pour Max, qui renvoie la plus grosse vente du Nord, 540 €, au lieu du total de son meilleur vendeur. Le sous-total répond donc à une question sur les ventes, et pour comparer les vendeurs entre eux, mieux vaut lire leurs lignes ou les trier.
Masquer les sous-totaux d’un tableau croisé dynamique
Les sous-totaux se retirent soit pour tout le rapport, depuis l’onglet Création, soit pour un seul champ, depuis ses paramètres. Dans les deux cas, la ligne de la région reste en place mais n’affiche plus de montant, et le total général ne bouge pas.
Supprimer tous les sous-totaux depuis l’onglet Création
- 01Clique dans le rapport, puis ouvre le menu Sous-totaux de l’onglet Création.
- 02Choisis Ne pas afficher les sous-totaux.
Cette commande règle tous les champs de lignes sur Aucun d’un coup, la région comme le vendeur. Le rapport ne montre plus que les montants des vendeurs, avec Est, Nord et Sud comme simples titres, puis le total général de 5 530 €.
Retirer le sous-total d’un seul champ
Pour garder les sous-totaux d’un niveau et retirer ceux d’un autre, ouvre les paramètres du champ concerné comme plus haut, sélectionne Aucun dans la rubrique Sous-totaux, puis valide. La partie sur les sous-totaux de plusieurs niveaux applique ce réglage à un rapport à trois champs.
Placer les sous-totaux en haut ou au bas du groupe
En forme compactée, Excel écrit le sous-total sur la ligne de la région, en haut du groupe, ce qui fait gagner une ligne par région mais oblige à remonter pour lire le total. Pour un rapport qui se lit de haut en bas, comme un état que l’on imprime, le sous-total trouve mieux sa place sous les vendeurs.
- 01Clique dans le rapport, puis ouvre le menu Sous-totaux de l’onglet Création.
- 02Choisis Afficher tous les sous-totaux au bas du groupe.
Chaque région reçoit une ligne Total Est, Total Nord ou Total Sud sous ses vendeurs, et la ligne de la région reste vide. Pour revenir en arrière, la commande voisine Afficher tous les sous-totaux en haut du groupe remonte les montants sur la ligne de chaque région. Le même réglage existe champ par champ, dans l’onglet « Disposition et impression » des paramètres du champ, avec la case « Afficher les sous-totaux au-dessus de chaque groupe ».
Passer le rapport sous forme tabulaire
La forme tabulaire range la région et le vendeur dans deux colonnes distinctes, comme un tableau ordinaire, et c’est elle qu’on choisit pour copier le rapport ailleurs. Ouvre le menu Disposition du rapport de l’onglet Création, puis choisis Afficher sous forme tabulaire.
Le même rapport sous forme tabulaire
| A | B | C | |
|---|---|---|---|
| 3 | Région | Vendeur | Somme de Montant |
| 4 | Est | Paul | 870 |
| 5 | Sarah | 1254 | |
| 6 | Total Est | 2124 | |
| 7 | Nord | Claire | 930 |
| 8 | Hugo | 726 | |
| 9 | Total Nord | 1656 | |
| 10 | Sud | Inès | 1018 |
| 11 | Marc | 732 | |
| 12 | Total Sud | 1750 | |
| 13 | Total général | 5530 |
Sous cette forme, les sous-totaux tombent toujours au bas du groupe, même quand le rapport les demande en haut, puisque la ligne de la région porte déjà son premier vendeur. La commande Afficher en mode Plan, dans le même menu, garde les deux colonnes mais remet le sous-total sur la ligne de la région.
Régler les sous-totaux de plusieurs niveaux
Chaque champ de la zone Lignes porte ses propres sous-totaux, sauf le dernier, qui n’en affiche aucun par défaut. Glisse Produit sous Vendeur, et le rapport montre un sous-total par région et un autre par vendeur, 870 € pour Paul et 1 254 € pour Sarah, mais aucun pour Écran ou Souris, qui sont les lignes de détail.
Comme ces réglages se font champ par champ, tu peux garder le total des régions et retirer celui des vendeurs. Ouvre les paramètres du champ Vendeur, sélectionne Aucun et valide, et la ligne de chaque vendeur ne sert plus que de titre au-dessus de ses produits. La région garde son sous-total, au bas du groupe ou en haut selon le réglage choisi. Ces niveaux peuvent aussi venir d’une seule colonne de dates, que notre article sur le regroupement des dates par année, trimestre et mois découpe en trois champs.
Les sous-totaux personnalisés réglés sur le dernier champ se comportent autrement. Avec Somme cochée pour Produit, Excel ajoute à la fin du rapport, juste avant le total général, une ligne par produit, toutes régions confondues, soit 1 046 € pour Somme Clavier, 3 780 € pour Somme Écran et 704 € pour Somme Souris. Si tu veux surtout comparer les produits, glisse plutôt Produit dans la zone Colonnes, où le rapport se lit mieux.
Afficher ou enlever le total général
Le total général se règle dans le menu Totaux généraux de l’onglet Création, qui propose quatre choix. Le choix Désactivé pour les lignes et les colonnes retire tous les totaux généraux, et le choix Activé pour les lignes et les colonnes les remet.
Les deux autres choix trompent souvent, parce que « lignes » désigne le total de chaque ligne, écrit dans une colonne à droite. Sur notre rapport, qui n’a que des champs en lignes, Activé pour les lignes uniquement fait donc disparaître la ligne Total général du bas, alors que le choix Activé pour les colonnes uniquement la garde.
La différence se voit mieux avec Produit dans la zone Colonnes. Le choix Activé pour les lignes uniquement garde la colonne Total général à droite, qui additionne Clavier, Écran et Souris pour chaque vendeur, et retire la ligne du bas. Le choix Activé pour les colonnes uniquement fait l’inverse, et la ligne du bas affiche 1 046 € sous Clavier, 3 780 € sous Écran et 704 € sous Souris.
Quelle différence entre un sous-total et un champ calculé
Un sous-total résume un groupe avec une fonction, alors qu’un champ calculé ajoute une colonne de valeurs dont la formule combine d’autres champs. Pour obtenir le prix moyen payé par article, aucun sous-total ne suffit, et un champ calculé Prix moyen, écrit =Montant/Quantité, affiche ce prix dans la zone Valeurs. Notre article sur le champ calculé d’un tableau croisé dynamique détaille sa création.
Sur la ligne d’une région, le champ calculé applique sa formule aux sommes du groupe, pas à chaque vente. Le Nord affiche ainsi environ 63,69, soit 1 656 € divisés par les 26 articles vendus, alors que la moyenne des six prix unitaires du Nord vaudrait environ 87,67. Pour un prix moyen pondéré par les quantités, c’est le bon calcul.
Les sous-totaux personnalisés ne s’appliquent pas aux champs calculés. Avec Somme et Moyenne cochées pour la région, les lignes Somme Nord et Moyenne Nord affichent bien 1 656 € et 276 € dans la colonne Somme de Montant, mais restent vides dans la colonne du prix moyen.
Pour réunir plusieurs rapports et leurs graphiques sur une même page, notre article sur le tableau de bord Excel montre comment les relier à des segments. Si tu débutes avec les tableaux croisés dynamiques, notre guide en PDF reprend tout depuis le début, de la préparation des données au graphique croisé dynamique, sur un même petit exemple.
Le tableau croisé dynamique pour débutant (PDF)
Pars de zéro avec le guide en PDF, qui se termine par un exercice corrigé. Il est gratuit !
Questions fréquentes
Les sous-totaux d’un tableau croisé dynamique se règlent dans le rapport et se recalculent à chaque actualisation, sans aucune formule. La fonction SOUS.TOTAL s’écrit au contraire dans une cellule d’une feuille ordinaire, et la commande Sous-total de l’onglet Données insère cette fonction dans une liste triée. Dans un tableau croisé dynamique, tu n’as besoin ni de l’une ni de l’autre.
Les sous-totaux et le total général ne comptent que les éléments affichés. Si tu masques Hugo avec le filtre du champ Vendeur, le Nord passe de 1 656 € à 930 €, soit les seules ventes de Claire, et le total général descend à 4 804 €.
Le pourcentage se règle dans le champ de valeurs, car le sous-total n’a pas de réglage pour ça. Fais un clic droit sur un montant, choisis Afficher les valeurs, puis % du total de la ligne parente, et chaque vendeur affiche sa part dans sa région, comme Claire avec 56,16 % du Nord. Notre article sur le pourcentage dans un tableau croisé dynamique passe en revue les autres choix.
Les fonctions cochées restent en place après une actualisation, et leurs résultats tiennent compte des nouvelles ventes. Avec Somme et Nombre cochées pour la région, tape Est, Paul, Clavier, 2 et 116 sur la ligne jaune de l’onglet « Ventes », sous le tableau, qui s’étend alors jusqu’à la ligne 17. Fais ensuite un clic droit dans le rapport et choisis Actualiser. Somme Est passe à 2 240 €, Nombre Est à 5, et le total général à 5 646 €. Notre article sur l’actualisation d’un tableau croisé dynamique montre comment l’automatiser.
Le tri d’un champ de lignes se fait sur les montants de son niveau. Fais un clic droit sur le montant d’une région, choisis Trier, puis Trier du plus grand au plus petit. Les régions passent dans l’ordre Est, Sud et Nord, chacune avec ses vendeurs, qui gardent leur propre ordre.