Quand tu travailles avec un gros tableau Excel, tu as souvent besoin de calculer des totaux par groupe, de sommer uniquement les lignes visibles après un filtre, ou d’afficher des sous-totaux par région, par produit, par commercial. Deux outils existent pour ça : la commande Données > Sous-totaux (qui insère des lignes de total automatiquement dans ton tableau) et la fonction SOUS.TOTAL (que tu écris toi-même dans une cellule). Ce ne sont pas la même chose, et dans cet article tu vas voir comment chacune fonctionne, quand les utiliser, et pourquoi elles surpassent une simple SOMME.
- Écrire la fonction SOUS.TOTAL avec le bon code de calcul
- Calculer une somme, une moyenne ou un nombre de valeurs sur un tableau filtré
- Comprendre la différence entre les codes 1-11 et 101-111
- Insérer des sous-totaux automatiques via le menu Données
- Choisir entre SOUS.TOTAL et AGREGAT selon ton besoin
La fonction SOUS.TOTAL : syntaxe et codes
La syntaxe de SOUS.TOTAL est simple :
=SOUS.TOTAL(n_fonction; plage)n_fonction est un entier qui indique le type de calcul à effectuer. plage est la plage de cellules sur laquelle tu veux l’appliquer. Exemples concrets :
=SOUS.TOTAL(9;C2:C200) ' Somme des cellules visibles de C2 à C200
=SOUS.TOTAL(1;D2:D200) ' Moyenne des cellules visibles
=SOUS.TOTAL(3;A2:A200) ' Nombre de cellules non vides visiblesVoici le tableau complet des codes disponibles :
| Code (filtre) | Code (masquage manuel) | Fonction |
|---|---|---|
| 1 | 101 | MOYENNE |
| 2 | 102 | NB |
| 3 | 103 | NBVAL |
| 4 | 104 | MAX |
| 5 | 105 | MIN |
| 6 | 106 | PRODUIT |
| 7 | 107 | ECARTYPE |
| 8 | 108 | ECARTYPEP |
| 9 | 109 | SOMME |
| 10 | 110 | VAR |
| 11 | 111 | VAR.P |
Le code 9 (SOMME) est de loin le plus utilisé. Retiens aussi le 3 (NBVAL) pour compter des enregistrements et le 1 (MOYENNE) pour des indicateurs de performance.
La grande force de SOUS.TOTAL : ignorer les lignes filtrées
Voilà le point crucial. Si tu poses un filtre sur ton tableau et que tu calcules =SOMME(C2:C200), Excel additionne toutes les valeurs, visibles ou non. Résultat : ton total ne change pas quand tu filtres, ce qui ne sert à rien.
Avec =SOUS.TOTAL(9;C2:C200), le total se recalcule à chaque changement de filtre. Il ne tient compte que des lignes visibles à l’écran. C’est exactement ce dont tu as besoin pour un tableau de bord Excel interactif ou pour un reporting dynamique.
Deuxième avantage : si ta plage contient elle-même d’autres cellules SOUS.TOTAL (des lignes de sous-totaux intermédiaires par exemple), Excel les ignore automatiquement dans le calcul final. Aucun double comptage.
Codes 1-11 vs 101-111 : filtres ou masquage manuel ?
Il existe en fait deux séries de codes pour chaque fonction :
- Codes 1 à 11 : ignorent les lignes masquées par un filtre automatique, mais incluent les lignes masquées manuellement (clic droit sur un numéro de ligne, puis Masquer).
- Codes 101 à 111 : ignorent à la fois les lignes filtrées ET les lignes masquées manuellement. Seules les cellules réellement visibles entrent dans le calcul.
Dans la pratique, tu utiliseras presque toujours les codes 1-11 si tu travailles avec des filtres automatiques. Les codes 101-111 sont utiles quand tu masques des lignes à la main pour présenter un tableau épuré et que tu veux que tes totaux s’adaptent.
=SOUS.TOTAL(9;C2:C200) ' Somme filtrée uniquement
=SOUS.TOTAL(109;C2:C200) ' Somme filtrée + lignes masquées manuellement excluesLa méthode Données > Sous-totaux : insertion automatique
Si tu veux insérer des lignes de sous-totaux directement dans ton tableau, sans écrire de formule toi-même, la commande Données > Sous-totaux fait le travail en quelques clics.
Étapes à suivre
- Trie d’abord ton tableau par la colonne de regroupement (ex. : Région). Ce tri est indispensable, sinon Excel créera un sous-total à chaque changement de valeur, même non consécutif.
- Clique sur une cellule du tableau, puis va dans Données > Plan > Sous-totaux.
- Dans la boîte de dialogue : choisis À chaque changement de (ta colonne de groupe, ex. Région), la Fonction (Somme, Moyenne, Nombre…) et les Colonnes à additionner.
- Clique sur OK. Excel insère automatiquement des lignes de sous-totaux et un grand total en bas, avec des boutons de niveau (1, 2, 3) sur la gauche pour afficher ou masquer les détails.
Les boutons 1 / 2 / 3 à gauche de la feuille permettent de naviguer entre les niveaux : 1 = grand total uniquement, 2 = sous-totaux par groupe, 3 = toutes les lignes. Pour supprimer les sous-totaux, retourne dans Données > Sous-totaux > Supprimer tout.
Cas pratiques
1. Total des ventes par région avec filtre
Tu as un tableau de ventes avec les colonnes Région, Commercial, Montant. Tu veux voir le total des ventes pour la région que tu sélectionnes dans le filtre.
=SOUS.TOTAL(9;C2:C500)Pose cette formule dans une cellule en dehors du tableau (par exemple C1 ou une ligne de résumé au-dessus). Dès que tu changes le filtre sur la colonne Région, le total se met à jour.
2. Compter les clients visibles dans un tableau filtré
Tu filtes sur une catégorie de produit et tu veux savoir combien de clients distincts sont concernés. En supposant que chaque ligne correspond à un client :
=SOUS.TOTAL(3;A2:A500)Le code 3 (NBVAL) compte les cellules non vides parmi les lignes visibles. Si tu veux compter uniquement des valeurs numériques, utilise le code 2 (NB).
3. Moyenne des scores sur un tableau filtré
Pour un tableau d’évaluations ou de performances, tu veux la moyenne uniquement sur les lignes affichées :
=SOUS.TOTAL(1;D2:D500)La moyenne se recalcule automatiquement à chaque filtrage. Parfait pour un tableau de bord où le manager sélectionne l’équipe ou la période qui l’intéresse.
SOUS.TOTAL vs AGREGAT : quand passer à l’étape suivante ?
SOUS.TOTAL couvre les 11 fonctions classiques (SOMME, MOYENNE, MIN, MAX…). Mais depuis Excel 2010, la fonction AGREGAT est disponible et va plus loin.
AGREGAT propose 19 fonctions (dont PETITE.VALEUR, GRANDE.VALEUR, MEDIANE, PERCENTILE, MODE) et offre plusieurs options pour ignorer les erreurs, les lignes masquées, ou les deux à la fois. Sa syntaxe est légèrement différente :
=AGREGAT(n_fonction; options; plage)
Exemple : 2e plus grande valeur en ignorant les erreurs et les filtrées :
=AGREGAT(14; 5; C2:C500; 2)Le deuxième argument options contrôle ce qu’on ignore :
- 0 : rien d’ignoré (comme SOUS.TOTAL codes 1-11)
- 5 : ignore les lignes masquées (filtres + manuel)
- 6 : ignore les erreurs
- 7 : ignore les erreurs ET les lignes masquées
Pour la majorité des usages courants (sommer ou compter sur un tableau filtré), SOUS.TOTAL est suffisante et plus simple à écrire. Passe à AGREGAT quand tu as besoin d’un rang, d’un percentile, ou que ta plage contient des erreurs que tu veux ignorer.
Questions fréquentes sur SOUS.TOTAL
SOUS.TOTAL est-elle différente de SOMME ?
Quel code utiliser pour compter des cellules non vides dans un tableau filtré ?
=SOUS.TOTAL(3;B2:B100). NBVAL (code 3) compte les cellules non vides parmi les lignes visibles. Si tu veux compter uniquement les nombres, utilise le code 2 (NB).
