Plan, consolidation et sous-totaux dans Excel
Un long tableau devient lisible quand on peut le replier : le plan masque le détail et ne garde que les totaux. La consolidation additionne plusieurs feuilles en une synthèse, et l’outil Sous-total calcule automatiquement un total à chaque changement de catégorie, sur un ou plusieurs niveaux.
GrouperMaj + Alt + →
DissocierMaj + Alt + ←
ConsoliderDonnées › Consolider
Sous-totauxDonnées › Sous-total
1
Le plan : un ou plusieurs niveaux
Un plan regroupe des lignes (ou des colonnes) de détail sous une ligne de synthèse. Dans la marge de gauche apparaissent les boutons de niveau 1 2 3 et les boutons − / + qui replient ou déplient chaque groupe.
- Plan automatique : cliquez dans le tableau, Données › Plan › Grouper › Plan automatique. Excel repère les formules de synthèse (
=SOMME(B2:B4)) et groupe les lignes qu’elles additionnent. - Plan manuel : sélectionnez les lignes de détail (sans la ligne de total), Données › Plan › Grouper ou Maj + Alt + →.
- Plusieurs niveaux : groupez ensuite un bloc plus large qui contient déjà des groupes. Chaque groupement ajoute un niveau (8 au maximum).
- Pour défaire : Données › Plan › Dissocier (Maj + Alt + ←) sur une sélection, ou Dissocier › Effacer le plan pour tout supprimer.
Simulateur : grouper un budget en plusieurs niveaux
Copier seulement les totauxRepliez au niveau 2, sélectionnez, puis Alt + ; (sélectionner les cellules visibles) avant de copier : les lignes masquées ne sont pas collées.
Totaux au-dessus ?Par défaut, les lignes de synthèse sont sous le détail. Le lanceur du groupe Données › Plan permet de décocher « Lignes de synthèse sous les lignes de détail ».
2
La consolidation
La consolidation regroupe dans une feuille de synthèse des tableaux de même nature répartis sur plusieurs feuilles ou classeurs (une feuille par agence, par mois…). Les tableaux n’ont pas besoin d’être dans le même ordre si l’on consolide par étiquettes.
- Placez-vous dans la feuille de synthèse, sur la cellule de départ (A1).
- Données › Outils de données › Consolider, choisissez la fonction (Somme, Moyenne, Nombre…).
- Dans Référence, sélectionnez le premier tableau avec ses titres (
Nord!$A$1:$C$4) puis Ajouter. Recommencez pour chaque feuille. - Cochez Ligne du haut et Colonne de gauche pour consolider par étiquettes. Cochez Lier aux données sources pour obtenir des formules et un plan.
Simulateur : consolider trois agences
Toutes les références :
Utiliser les étiquettes dans :
Sans étiquettesExcel additionne par position : la 1re ligne de Nord avec la 1re ligne de Sud, même si ce ne sont pas les mêmes formations. À réserver aux tableaux strictement identiques.
Mise à jourSans liaison, le résultat est une copie figée : relancez Consolider (les références sont mémorisées). Avec liaison, les formules se recalculent seules.
Simulateur : l’outil Sous-total
Sous-total
Ajouter un sous-total à :
3
Créer des sous-totaux
- Triez d’abord la colonne de regroupement (Données › Trier). Excel crée un sous-total à chaque changement de valeur : sans tri, la même région apparaît plusieurs fois.
- Cliquez dans la liste (pas de tableau de données : l’outil est grisé dans un tableau, convertissez-le en plage).
- Données › Plan › Sous-total : choisissez la colonne À chaque changement de, la fonction et les colonnes à totaliser.
- OK : Excel insère les lignes « Total Nord », « Total général » et construit le plan à 3 niveaux.
| Fonction choisie | Formule insérée | Libellé |
|---|---|---|
| Somme | =SOUS.TOTAL(9;D2:D6) | Total Nord |
| Nombre | =SOUS.TOTAL(3;D2:D6) | Nombre Nord |
| Moyenne | =SOUS.TOTAL(1;D2:D6) | Moyenne Nord |
| Max / Min | =SOUS.TOTAL(4;…) / =SOUS.TOTAL(5;…) | Max Nord / Min Nord |
Pourquoi SOUS.TOTAL ?SOUS.TOTAL ignore les autres SOUS.TOTAL de sa plage : le Total général n’additionne que les lignes de détail, jamais les totaux intermédiaires.
Supprimer les sous-totauxNe supprimez pas les lignes à la main : rouvrez Données › Sous-total et cliquez sur Supprimer tout. Les données d’origine sont restaurées.
4
Créer des sous-totaux à plusieurs niveaux
Pour obtenir un total par région et, à l’intérieur, par formation, on applique l’outil deux fois, du niveau le plus large au plus fin.
- Triez sur deux clés : Région puis Formation (Données › Trier › Ajouter un niveau).
- Premier sous-total : à chaque changement de Région, Somme, sur Stagiaires et Montant HT.
- Rouvrez Données › Sous-total : à chaque changement de Formation, même fonction, et décochez Remplacer les sous-totaux existants.
- Le plan passe à 4 niveaux : 1 = Total général, 2 = totaux par région, 3 = totaux par formation, 4 = détail.
Deux fonctions sur le même regroupementRelancez l’outil sur la même colonne avec une autre fonction (Moyenne) et Remplacer décoché : Excel ajoute « Moyenne Nord » au-dessus de « Total Nord ».
Dans le simulateurTriez, créez le sous-total par Région, puis par Formation sans remplacer. Cliquez sur les boutons 1 à 4, puis sur un
− pour replier un seul groupe.Exercice de synthèse : ventes de formations par agence
Télécharger le fichier d’exercice (Énoncé et Corrigé)
- Feuille Budget : groupez les lignes 2 à 4, puis 6 à 8 (Maj + Alt + →), puis 2 à 9 pour obtenir 3 niveaux. Affichez le niveau 1 : seul le Résultat (23 400 €) reste visible. Comparez avec le Plan automatique.
- Feuilles Nord, Sud, Est : dans Synthèse, consolidez les trois tableaux (Somme, Ligne du haut, Colonne de gauche). Excel T1 = 29, T2 = 35 ; Outlook T1 = 5.
- Feuille Ventes : triez par Région puis Formation, sous-total par Région (Somme sur Stagiaires et Montant HT) : Est 4 520 €, Nord 6 580 €, Sud 4 750 €, Total général 15 850 €.
- Ajoutez le sous-total par Formation sans remplacer : Nord Excel = 7 stagiaires et 2 730 €. Affichez le niveau 2 et copiez uniquement les cellules visibles vers une nouvelle feuille.