Un sous-total dans Excel permet d’agréger des données par groupe sans perturber le reste du tableau. Deux approches coexistent : l’outil Sous-total du menu Données, qui structure automatiquement un tableau en niveaux, et la fonction SOUS.TOTAL, qui offre un contrôle précis sur les lignes filtrées ou masquées. Les deux méthodes évitent le double-comptage et s’adaptent à des structures de données variées.
Créer des sous-totaux via le menu Données
L’outil intégré génère des sous-totaux automatiques en regroupant les lignes selon une colonne de catégories. Avant de l’utiliser, une étape préalable est indispensable : trier la colonne de catégories. Sans ce tri, Excel crée plusieurs sous-totaux distincts pour une même catégorie dispersée dans le tableau.
- Sélectionner une cellule à l’intérieur du tableau de données.
- Aller dans l’onglet Données, puis cliquer sur Sous-total.
- Choisir la colonne de regroupement dans A chaque changement de.
- Sélectionner la fonction de calcul souhaitée : Somme, Moyenne, Nombre, etc.
- Cocher les colonnes numériques à totaliser, puis valider.
Excel insère alors des lignes de sous-total et affiche un plan hiérarchique sur la gauche. Trois niveaux d’affichage : niveau 1 (total général), niveau 2 (sous-totaux par groupe), niveau 3 (toutes les lignes de détail).
La fonction SOUS.TOTAL : syntaxe et fonctionnement
La syntaxe est : =SOUS.TOTAL(no_fonction ; réf1 ; [réf2] ; …)
- no_fonction : un nombre entre 1 et 11 ou entre 101 et 111 qui détermine le type de calcul.
- réf1 : la plage principale sur laquelle appliquer le calcul.
- réf2, … : jusqu’à 254 plages supplémentaires optionnelles.
Comportement fondamental : les cellules filtrées sont toujours exclues du calcul, quel que soit le numéro de fonction choisi.
Les numéros de fonction : 1-11 et 101-111
SOUS.TOTAL accepte deux séries de numéros. Les numéros 1 à 11 incluent les lignes masquées manuellement dans le calcul. Les numéros 101 à 111 les excluent. Dans les deux cas, les lignes exclues par un filtre ne sont jamais comptabilisées.
- 1 / 101 : MOYENNE
- 2 / 102 : NB
- 3 / 103 : NBVAL
- 4 / 104 : MAX
- 5 / 105 : MIN
- 6 / 106 : PRODUIT
- 9 / 109 : SOMME
- 10 / 110 : VAR
- 11 / 111 : VAR.P
Exemples pratiques
Pour additionner les valeurs de la plage A2:A100 en ignorant les lignes filtrées : =SOUS.TOTAL(9;A2:A100)
Pour calculer la moyenne en excluant également les lignes masquées manuellement : =SOUS.TOTAL(101;A2:A100)
Un SOUS.TOTAL imbriqué dans une autre plage SOUS.TOTAL est automatiquement ignoré pour éviter tout double-comptage. C’est l’un des avantages décisifs par rapport à SOMME.
SOUS.TOTAL vs SOMME : quelle différence concrète ?
La différence principale tient à la prise en charge des filtres. Une formule =SOMME(A2:A100) additionne toujours toutes les valeurs de la plage, qu’elles soient visibles ou non.
=SOUS.TOTAL(9;A2:A100) recalcule en temps réel dès qu’un filtre est activé : seules les lignes affichées entrent dans le calcul. C’est l’avantage décisif pour analyser un sous-ensemble de données sans modifier les formules.
Questions fréquentes
SOUS.TOTAL(9;…) exclut automatiquement les lignes masquées par un filtre actif, tandis que SOMME additionne toutes les cellules de la plage sans distinction. Avec un filtre appliqué, SOUS.TOTAL recalcule en temps réel sur les seules lignes visibles.
Le numéro 9 inclut les lignes masquées manuellement dans la somme. Le numéro 109 les exclut. Dans les deux cas, les lignes filtrées sont toujours exclues.
Oui, le tri préalable est indispensable. Si la colonne de catégories n’est pas triée, Excel génère un sous-total distinct pour chaque occurrence d’une même catégorie, ce qui fragmente les résultats.
Oui. Dans un tableau structuré, activer la ligne de totaux utilise automatiquement SOUS.TOTAL en arrière-plan, garantissant que les totaux correspondent toujours aux données filtrées visibles.
Les fonctions SOUS.TOTAL imbriquées dans la plage de calcul sont automatiquement ignorées pour éviter le double-comptage. Ce comportement est natif et ne nécessite aucun paramètre supplémentaire.
