SOUS.TOTAL : La formule Excel pour des calculs intelligents qui ignorent les lignes masquées
=SOUS.TOTAL(no_fonction; réf1; [réf2]; ...)La formule SOUS.TOTAL est l'une des fonctions les plus puissantes d'Excel pour effectuer des calculs statistiques sophistiqués. Contrairement aux formules classiques comme SOMME ou MOYENNE, SOUS.TOTAL possède une caractéristique unique : elle ignore automatiquement les lignes cachées, filtrées ou supprimées, ce qui en fait l'outil idéal pour travailler avec des données dynamiques. Cette fonction est particulièrement appréciée des analystes financiers, des gestionnaires de projets et des professionnels du contrôle de gestion qui doivent manipuler régulièrement des ensembles de données volumineux et complexes. La flexibilité de SOUS.TOTAL réside dans son système de numérotation de fonction, qui permet de sélectionner entre 11 opérations mathématiques différentes (de la somme à l'écart-type). Avec ses variantes 1-11 et 101-111, vous pouvez choisir d'ignorer ou d'inclure les valeurs d'autres fonctions SOUS.TOTAL imbriquées, offrant ainsi un contrôle granulaire sur vos calculs. Comprendre cette formule transformera votre manière de gérer les rapports Excel et les tableaux de bord interactifs.
Syntaxe et paramètres
La syntaxe de SOUS.TOTAL s'articule autour de deux éléments fondamentaux : le numéro de fonction et les références à additionner. Le paramètre no_fonction est crucial et détermine le type de calcul à effectuer. Les numéros 1 à 11 incluent les valeurs des autres fonctions SOUS.TOTAL imbriquées, tandis que les numéros 101 à 111 les excluent. Par exemple, 9 correspond à SOMME, 1 à MOYENNE, 3 à COUNTA, 11 à VAR (variance). Le paramètre réf1 est obligatoire et représente la première plage de cellules à analyser. Vous pouvez ajouter jusqu'à 254 références supplémentaires (réf2, réf3, etc.) pour étendre votre calcul. SOUS.TOTAL fonctionne intelligemment en ignorant automatiquement les lignes masquées par filtrage ou par masquage manuel, mais elle inclut les lignes supprimées avec les numéros 1-11. Avec les numéros 101-111, même les lignes supprimées sont exclues. Cette fonction ne fonctionne pas avec les lignes masquées par le groupage (outline), qui restent toujours incluses. Conseil pratique : utilisez les variantes 101-111 dans les rapports automatisés pour garantir la cohérence même si quelqu'un modifie manuellement la visibilité des lignes.
function_numref1Exemples pratiques
Calcul de total de ventes avec filtrage
=SOUS.TOTAL(9;C2:C50)Cette formule utilise le numéro 9 qui correspond à SOMME. Elle calcule la somme des cellules C2 à C50, en ignorant automatiquement les lignes cachées par le filtre. Si le filtre affiche uniquement 15 lignes sur 49, seules ces 15 lignes seront additionnées.
Moyenne des notes d'étudiants avec exclusion des doublons
=SOUS.TOTAL(1;D2:D100)Le numéro 1 correspond à MOYENNE. La formule calcule la moyenne des notes dans la plage D2:D100, en ignorant les lignes masquées manuellement ou par filtrage. Les lignes 'absent' sont préalablement masquées, donc elles ne sont pas incluses dans le calcul.
Comptage intelligent avec variante pour éviter les imbrications
=SOUS.TOTAL(103;E2:E200)Le numéro 103 (variante de COUNTA) compte les cellules non vides dans E2:E200 en ignorant les lignes cachées ET les autres fonctions SOUS.TOTAL imbriquées. C'est essentiel quand on combine plusieurs SOUS.TOTAL dans un même rapport pour éviter les calculs en cascade.
Points clés à retenir
- SOUS.TOTAL est la fonction idéale pour calculer des totaux qui ignorent les lignes masquées par filtrage, offrant une flexibilité que SOMME n'a pas.
- Le système de numérotation (1-11 vs 101-111) est crucial : utilisez 101-111 pour exclure les autres SOUS.TOTAL imbriquées et éviter les calculs en cascade.
- SOUS.TOTAL ne fonctionne pas avec les lignes masquées par le groupage (outline) - elles sont toujours incluses, contrairement aux lignes masquées manuellement ou par filtrage.
- Combinez SOUS.TOTAL avec d'autres fonctions comme DÉCALER, SI ou IFERROR pour créer des rapports dynamiques et robustes.
- Testez toujours vos formules SOUS.TOTAL en appliquant des filtres pour vérifier qu'elles se comportent correctement dans différents scénarios.
Astuces de pro
Utilisez toujours les variantes 101-111 dans les rapports avec plusieurs niveaux de SOUS.TOTAL pour éviter les calculs en cascade qui doublent les valeurs.
Impact : Garantit l'exactitude des totaux généraux et des sous-totaux dans les rapports hiérarchisés complexes, économisant des heures de débogage.
Créez une colonne d'aide avec des numéros de fonction (9, 1, 3, etc.) pour rendre vos formules SOUS.TOTAL plus lisibles et faciles à maintenir. Référencez cette colonne plutôt que d'encoder les numéros en dur.
Impact : Améliore la maintenabilité et permet à d'autres utilisateurs de comprendre rapidement votre logique sans consulter la documentation.
Testez toujours vos formules SOUS.TOTAL en masquant quelques lignes avec le filtrage automatique pour vérifier qu'elles se comportent correctement. Ne vous fiez pas uniquement à la valeur affichée sur toutes les lignes visibles.
Impact : Prévient les erreurs subtiles qui pourraient passer inaperçues et causer des inexactitudes dans les rapports finaux.
Préférez SOUS.TOTAL à SOMME quand vous travaillez avec des données que les utilisateurs vont filtrer, même si actuellement toutes les lignes sont visibles.
Impact : Rend vos modèles Excel plus robustes et évite les surprises quand d'autres utilisateurs appliquent des filtres sans réaliser que cela affecte les totaux.
Combinaisons utiles
SOUS.TOTAL + DÉCALER pour une plage dynamique
=SOUS.TOTAL(9;DÉCALER($A$1;1;0;LIGNES($A$2:$A$100);1))Cette combinaison crée une plage dynamique qui s'ajuste automatiquement au nombre de lignes de données. DÉCALER définit une plage qui commence à A2 et s'étend jusqu'à la dernière ligne avec données. SOUS.TOTAL calcule ensuite la somme en ignorant les lignes cachées. Utile pour les rapports qui reçoivent régulièrement de nouvelles données.
SOUS.TOTAL + SI pour les calculs conditionnels filtrés
=SOUS.TOTAL(9;SI(C2:C100>1000;C2:C100;0))Combinez SOUS.TOTAL avec SI pour créer une colonne auxiliaire qui applique une condition avant le calcul du sous-total. Saisissez avec Ctrl+Maj+Entrée pour créer une formule de tableau. Cela permet de filtrer les données selon des critères spécifiques tout en bénéficiant de l'ignoration des lignes masquées.
SOUS.TOTAL + IFERROR pour gérer les erreurs
=IFERROR(SOUS.TOTAL(9;A2:A100);"Erreur de calcul")Enrobez SOUS.TOTAL dans IFERROR pour gérer les cas où le calcul pourrait échouer (plage vide, références invalides, etc.). Affiche un message personnalisé au lieu d'un code d'erreur, améliorant la lisibilité des rapports et évitant les interruptions visuelles.
Erreurs courantes
Cause : Le numéro de fonction spécifié n'existe pas ou est hors de la plage 1-11 ou 101-111. Par exemple : =SOUS.TOTAL(15;A1:A10) où 15 n'est pas un numéro valide.
Solution : Vérifiez que le premier paramètre est un nombre entier entre 1 et 11 ou entre 101 et 111. Consultez la documentation pour connaître la correspondance exacte (1=MOYENNE, 9=SOMME, 11=VAR, etc.).
Cause : La plage de référence contient des cellules supprimées ou la référence pointe vers une plage invalide. Par exemple : =SOUS.TOTAL(9;C:C) si la colonne C a été partiellement supprimée.
Solution : Vérifiez que toutes les colonnes et lignes référencées existent toujours. Utilisez des plages explicites (A1:A100) plutôt que des colonnes entières (A:A) pour plus de clarté et de stabilité.
Cause : Utilisation de SOUS.TOTAL avec un numéro correspondant à une opération qui peut diviser par zéro (comme la variance ou l'écart-type) sur une plage vide ou contenant une seule valeur.
Solution : Vérifiez que votre plage contient au moins 2 valeurs pour les calculs de variance (numéros 10, 110) ou d'écart-type (numéros 11, 111). Utilisez une formule conditionnelle pour gérer les cas limites.
Checklist de dépannage
- 1.Vérifiez que le numéro de fonction est entre 1-11 ou 101-111 et correspond bien à l'opération souhaitée (1=MOYENNE, 9=SOMME, 10=VAR.P, 11=VAR.S, etc.)
- 2.Confirmez que les références de plage existent et ne contiennent pas d'erreurs (#REF!, #N/A). Testez chaque plage individuellement avec une formule SOMME simple.
- 3.Vérifiez si vous utilisez les bonnes variantes : utilisez 101-111 si vous avez d'autres SOUS.TOTAL imbriquées dans votre rapport pour éviter les doublons.
- 4.Testez le comportement en masquant/affichant des lignes avec le filtrage automatique. SOUS.TOTAL doit ignorer les lignes masquées. Si ce n'est pas le cas, vérifiez si les lignes sont masquées par le groupage (outline) plutôt que par filtrage.
- 5.Assurez-vous que la plage ne contient pas de formules qui retournent des erreurs. SOUS.TOTAL inclut les erreurs dans son calcul, ce qui peut causer #VALUE!. Utilisez AGGREGATE ou IFERROR pour contourner ce problème.
- 6.Vérifiez la cohérence des formats de données : si vous additionnez des cellules contenant du texte, SOUS.TOTAL les ignore, ce qui peut donner des résultats inattendus. Assurez-vous que votre plage contient uniquement des nombres.
Cas particuliers
Plage vide ou contenant uniquement des cellules masquées
Comportement : SOUS.TOTAL retourne 0 pour les fonctions de somme (9) et retourne une erreur #DIV/0! pour les fonctions de variance (10, 11) car il n'y a pas assez de données.
Solution : Enrobez SOUS.TOTAL dans IFERROR pour gérer ces cas : =IFERROR(SOUS.TOTAL(11;A1:A10);0)
C'est un comportement attendu qui reflète l'absence de données à calculer.
Formules SOUS.TOTAL imbriquées sans utiliser les variantes 101-111
Comportement : Les calculs se font en cascade. Par exemple, si A1 contient =SOUS.TOTAL(9;B1:B10) et A2 contient =SOUS.TOTAL(9;A1:A10), alors A2 inclut A1 deux fois dans le calcul final.
Solution : Utilisez les variantes 101-111 pour le niveau supérieur : =SOUS.TOTAL(109;A1:A10) pour exclure les SOUS.TOTAL imbriquées.
Ceci est une source courante d'erreurs dans les rapports hiérarchisés mal conçus.
Lignes masquées par le groupage (outline) vs filtrage automatique
Comportement : SOUS.TOTAL ignore les lignes masquées par filtrage ou masquage manuel, MAIS elle INCLUT toujours les lignes masquées par le groupage (outline). Les lignes masquées par outline ne sont jamais exclues du calcul.
Solution : Si vous avez besoin d'ignorer les lignes du groupage, utilisez AGGREGATE à la place, qui offre cette option. Ou restructurez votre approche pour utiliser le filtrage plutôt que le groupage.
Cette distinction est souvent oubliée et cause des résultats inattendus dans les rapports avec groupage.
Limitations
- •SOUS.TOTAL n'ignore pas les lignes masquées par le groupage (outline), contrairement aux lignes masquées manuellement ou par filtrage. Pour ignorer les lignes de groupage, utilisez AGGREGATE.
- •SOUS.TOTAL inclut les erreurs (#N/A, #DIV/0!, etc.) dans son calcul, ce qui peut retourner une erreur au lieu d'un nombre. Utilisez AGGREGATE ou IFERROR pour contourner cette limitation.
- •Les numéros de fonction 1-11 et 101-111 doivent être saisis comme nombres constants, pas comme références de cellules. Vous ne pouvez pas écrire =SOUS.TOTAL(A1;B2:B10) où A1 contient 9. Cette limitation rend les formules moins flexibles.
- •SOUS.TOTAL ne fonctionne que sur des plages 2D simples. Elle ne fonctionne pas avec les tableaux multidimensionnels ou les données structurées complexes sans préparation préalable.
Alternatives
Offre encore plus d'options (19 fonctions vs 11 pour SOUS.TOTAL) et peut ignorer les erreurs, les lignes masquées ET les lignes supprimées simultanément. Plus flexible et moderne.
Quand : Préférez AGGREGATE pour les calculs complexes sur des données bruitées contenant des erreurs (#N/A, #DIV/0!, etc.). Disponible à partir d'Excel 2010.
Compatibilité
✓ Excel
Depuis Excel 2007
=SOUS.TOTAL(no_fonction;réf1;[réf2];...) - Identique dans toutes les versions (2007, 2010, 2013, 2016, 2019, 365)✓Google Sheets
=SUBTOTAL(function_number, range1, [range2], ...) - Syntaxe anglaise avec SUBTOTAL au lieu de SOUS.TOTALFonctionne de manière identique dans Google Sheets. Les numéros de fonction (1-11, 101-111) sont les mêmes. Attention : les noms de fonctions sont en anglais par défaut.
✓LibreOffice
=SOUS.TOTAL(no_fonction;réf1;[réf2];...) - Syntaxe identique à Excel avec traduction française