Introduction : SOUS.TOTAL, la fonction Excel qui s’adapte à vos filtres
La fonction SOUS.TOTAL sur Excel permet d’effectuer des calculs (somme, moyenne, Max, Min…) sur une plage de données en tenant compte des filtres appliqués. Contrairement aux fonctions classiques comme SOMME ou MOYENNE, elle s’adapte automatiquement aux lignes visibles du tableau.
C’est précisément ce comportement qui en fait une fonction incontournable dès que l’on travaille avec des listes filtrées.
Comprendre à quoi sert la fonction SOUS.TOTAL
La fonction SOUS.TOTAL permet de calculer un résultat dynamique, qui se met à jour automatiquement lorsque tu filtres un tableau Excel.
Concrètement :
- si tu filtres des lignes, le résultat change
- si tu masques certaines données, elles peuvent être ignorées
- si tu ajoutes des lignes, le calcul s’actualise
C’est une différence majeure avec la fonction SOMME, qui additionne toutes les cellules, visibles ou non.
Structure de la fonction SOUS.TOTAL dans Excel
La syntaxe de la fonction est la suivante :
=SOUS.TOTAL(num_fonction ; plage)
- num_fonction : indique le type de calcul à effectuer
- plage : correspond aux cellules à analyser
Le comportement de la formule dépend entièrement du numéro de fonction choisi.
Différence entre les numéros 1–11 et 101–111
C’est un point fondamental, souvent mal compris.
La fonction SOUS.TOTAL propose deux séries de numéros :
- 1 à 11
- 101 à 111
Les numéros de 1 à 11 permettent d’ignorer uniquement les lignes masquées par un filtre. Les numéros de 101 à 111 vont plus loin en excluant à la fois les lignes filtrées et celles masquées manuellement. Dans la majorité des cas, lorsque l’on travaille avec des filtres Excel classiques, l’utilisation des numéros de 1 à 11 est largement suffisante et plus lisible.
Utiliser SOUS.TOTAL avec un tableau filtré
Application concrète sur un tableau de données
La fonction SOUS.TOTAL révèle tout son intérêt lorsqu’elle est utilisée avec un tableau filtré. Dans un tableau de ventes, par exemple, il est possible d’appliquer un filtre par commercial ou par période afin d’afficher uniquement certaines lignes. La cellule contenant la fonction SOUS.TOTAL se met alors à jour automatiquement en fonction des données visibles. À chaque modification du filtre, Excel recalcule instantanément le résultat, sans intervention supplémentaire. C’est cette capacité d’adaptation qui fait de la fonction SOUS.TOTAL un outil très apprécié dans les tableaux de suivi et les reportings simples.
Pourquoi utiliser SOUS.TOTAL plutôt que SOMME
Les limites de la fonction SOMME avec les filtres
La fonction SOMME additionne toutes les valeurs d’une plage de cellules, qu’elles soient visibles ou non. Cela peut poser problème lorsque des filtres sont appliqués, car le résultat affiché ne correspond alors pas aux données réellement visibles à l’écran. La fonction SOUS.TOTAL, à l’inverse, s’adapte automatiquement aux filtres et calcule uniquement à partir des lignes affichées. Elle permet ainsi d’obtenir un total cohérent avec le contenu du tableau, ce qui est essentiel pour les tableaux de contrôle, de suivi ou de synthèse.
Quelles sont les limites de cette fonction SOUS.TOTAL ?
Cas où SOUS.TOTAL n’est pas la meilleure solution
Même si la fonction SOUS.TOTAL est particulièrement pratique, elle présente certaines limites. Elle fonctionne uniquement sur des plages de données continues et ne permet pas de gérer des calculs complexes basés sur plusieurs critères. De plus, elle n’est pas adaptée aux analyses multidimensionnelles ou aux comparaisons croisées. Dans ces situations, d’autres outils Excel, comme les Tableaux Croisés Dynamiques ou des fonctions conditionnelles plus avancées, sont généralement plus appropriés.
Tableau des numéros de fonction SOUS.TOTAL
Voici la liste complète des numéros utilisables avec SOUS.TOTAL. Chaque numéro correspond à une fonction de calcul spécifique :
| Numéro (filtre) | Numéro (filtre + masqué) | Fonction équivalente | Description |
|---|---|---|---|
| 1 | 101 | MOYENNE | Moyenne des valeurs visibles |
| 2 | 102 | NB | Nombre de cellules contenant des valeurs numériques |
| 3 | 103 | NBVAL | Nombre de cellules non vides |
| 4 | 104 | MAX | Valeur maximale visible |
| 5 | 105 | MIN | Valeur minimale visible |
| 6 | 106 | PRODUIT | Produit des valeurs visibles |
| 7 | 107 | ECARTYPE | Écart-type (échantillon) |
| 8 | 108 | ECARTYPEP | Écart-type (population) |
| 9 | 109 | SOMME | Somme des valeurs visibles |
| 10 | 110 | VAR | Variance (échantillon) |
| 11 | 111 | VAR.P | Variance (population) |
Les numéros les plus utilisés au quotidien sont 9 (SOMME), 1 (MOYENNE), 2 (NB), 4 (MAX) et 5 (MIN). Les numéros 101-111 sont utiles quand vous masquez manuellement des lignes (clic droit > Masquer) en plus d’utiliser des filtres.
Exemples concrets d’utilisation de SOUS.TOTAL
Suivi commercial avec filtre par vendeur
Un responsable commercial dispose d’un tableau avec les colonnes : Vendeur, Client, Montant, Date. En bas du tableau, la formule =SOUS.TOTAL(9;C2:C500) affiche la somme des montants. Quand il filtre sur un vendeur spécifique, le total se met à jour automatiquement pour n’afficher que le chiffre d’affaires de ce vendeur. Plus besoin de créer des formules SOMME.SI complexes pour chaque commercial.
Comptage de lignes visibles après filtrage
Pour savoir combien de lignes sont affichées après un filtre, utilisez =SOUS.TOTAL(3;A2:A500) (équivalent NBVAL). Ce comptage est très utile pour vérifier rapidement le nombre de clients dans une région, le nombre de factures impayées, ou le nombre de produits en stock dans une catégorie.
Moyenne dynamique sur un reporting mensuel
Dans un tableau de suivi de production, la formule =SOUS.TOTAL(1;D2:D1000) calcule la moyenne de la colonne D uniquement sur les lignes visibles. En filtrant par mois, par équipe ou par machine, la moyenne se recalcule instantanément. C’est bien plus fiable qu’une formule MOYENNE classique qui inclurait les données masquées.
Questions fréquentes sur SOUS.TOTAL Excel
SOUS.TOTAL fonctionne-t-elle avec les tableaux structurés ?
Oui, et c’est même son usage le plus courant. Quand vous créez un tableau structuré (Ctrl + T), Excel ajoute automatiquement une ligne de totaux en bas. Cette ligne utilise justement la fonction SOUS.TOTAL pour que les calculs s’adaptent aux filtres du tableau. Vous pouvez changer le type de calcul (somme, moyenne, max…) via le menu déroulant de chaque cellule de la ligne de totaux.
Peut-on imbriquer plusieurs SOUS.TOTAL ?
La fonction SOUS.TOTAL ignore automatiquement les autres cellules contenant une formule SOUS.TOTAL dans la plage de calcul. Cela signifie que si vous avez des sous-totaux intermédiaires dans votre tableau, le sous-total général en bas ne les comptera pas deux fois. C’est un avantage majeur par rapport à SOMME, qui additionnerait les sous-totaux intermédiaires au total.
Quelle est la différence entre SOUS.TOTAL et AGREGAT ?
La fonction AGREGAT (disponible depuis Excel 2010) est une version plus puissante de SOUS.TOTAL. Elle propose 19 fonctions de calcul au lieu de 11, et permet d’ignorer les erreurs (#N/A, #DIV/0!) dans la plage de calcul. Si votre tableau contient des cellules en erreur, AGREGAT est préférable. Pour un usage standard sans erreurs, SOUS.TOTAL reste plus simple et plus lisible.
SOUS.TOTAL fonctionne-t-elle dans Google Sheets ?
Oui, Google Sheets supporte la fonction SOUS.TOTAL avec la même syntaxe et les mêmes numéros de fonction (1-11 et 101-111). Le comportement est identique : les calculs s’adaptent aux filtres appliqués. La version anglaise s’appelle SUBTOTAL.
Conclusion : Quand utiliser la fonction SOUS.TOTAL sur Excel ?
La fonction SOUS.TOTAL est idéale lorsque l’on travaille sur des listes filtrables et que l’on souhaite obtenir des résultats qui s’ajustent automatiquement aux données visibles. Elle est particulièrement utile pour construire des tableaux simples, lisibles et évolutifs, sans multiplier les formules. Elle constitue également une excellente introduction aux notions de calcul dynamique dans Excel, avant d’aborder des outils d’analyse plus avancés.