Accueil › Fonctions Excel › SOUS.TOTAL
Fonction SOUS.TOTAL dans Excel : additionner les cellules filtrées et visibles
SOUS.TOTAL calcule une somme, un nombre, une moyenne ou une autre statistique sur une plage, mais ignore les lignes masquées par un filtre. Filtrez votre liste sur une région, et le total se met à jour pour n'afficher que cette région. Dans l'Excel en anglais, la fonction s'appelle SUBTOTAL.
SOUS.TOTAL · Dans ce guide
Syntaxe de SOUS.TOTAL
=SOUS.TOTAL(no_fonction; réf1; [réf2]; …)| Argument | Signification |
|---|---|
| no_fonction | Un nombre qui indique à SOUS.TOTAL quel calcul faire, comme 9 pour SOMME ou 109 pour SOMME sans les lignes masquées. Voir le tableau ci-dessous. |
| réf1; réf2; … | La ou les plages sur lesquelles porte le calcul. |
Numéros de fonction
| Calcul | Inclut les lignes masquées à la main | Ignore les lignes masquées à la main |
|---|---|---|
| MOYENNE | 1 | 101 |
| NB (nombres) | 2 | 102 |
| NBVAL (non vides) | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUIT | 6 | 106 |
| ECARTYPE | 7 | 107 |
| ECARTYPEP | 8 | 108 |
| SOMME | 9 | 109 |
| VAR | 10 | 110 |
| VAR.P | 11 | 111 |
1–11 ou 101–111 : les deux versions ignorent les lignes masquées par un filtre. La différence porte sur les lignes que vous masquez vous-même (clic droit › Masquer) : 1–11 les incluent toujours, 101–111 les excluent. En cas de doute, utilisez 101–111 : elles ne calculent que ce que vous voyez.
S'exercer avec SOUS.TOTAL : essayez vous-même
Lire est une chose, taper la formule en est une autre. Construisez la formule ci-dessous pas à pas : les indications vous disent quoi taper, et vous pouvez cliquer sur les cellules ou faire glisser dessus pour les ajouter, comme dans Excel.
Exercice: Additionnez les montants avec SOUS.TOTAL, en B7: =SOUS.TOTAL(9;B2:B6)
Exemple : additionner une liste filtrée
| A | B | C | |
|---|---|---|---|
| 1 | Région | Vendeur | Ventes |
| 2 | Nord | Anne | 1 200 |
| 3 | Sud | Bruno | 800 |
| 4 | Nord | Charles | 950 |
| 5 | Est | Diane | 450 |
| 6 | Sud | Anne | 700 |
| 7 | Total | 4 100 |
Sans filtre, C7 affiche 4 100, comme SOMME. Filtrez maintenant la colonne A sur « Nord » (Données › Filtrer). Les lignes 3, 5 et 6 sont masquées et le total passe à 2 150 : seules les lignes visibles sont additionnées. Une =SOMME(C2:C6) normale afficherait toujours 4 100.
Compter ou faire la moyenne des lignes visibles
- Compter les lignes visibles :
=SOUS.TOTAL(103; A2:A6)compte les cellules visibles non vides. Après un filtre sur Nord, elle renvoie 2. - Moyenne de ce qui est visible :
=SOUS.TOTAL(101; C2:C6)renvoie 1 075 pour le Nord. - Plus grande valeur visible :
=SOUS.TOTAL(104; C2:C6)renvoie 1 200 pour le Nord.
Astuce : placez les formules SOUS.TOTAL au-dessus de la liste plutôt qu'en dessous. Elles restent ainsi visibles quand la liste s'allonge.
SOUS.TOTAL ignore les autres sous-totaux
Si une plage contient des cellules qui utilisent elles-mêmes SOUS.TOTAL, ces cellules sont ignorées. Vous pouvez donc mettre des sous-totaux par groupe et un total général sur toute la colonne sans rien compter deux fois. C'est exactement ce que fait la commande Données › Sous-total d'Excel : elle insère des formules SOUS.TOTAL pour chaque groupe et une pour le total général. La ligne de total d'un tableau Excel (Ctrl+L, puis Création de tableau › Ligne Total) utilise aussi SOUS.TOTAL.
Erreurs fréquentes
- Le total ne change pas quand vous masquez des lignes à la main. Vous avez utilisé 1–11. Passez à 101–111.
- Les colonnes masquées sont toujours incluses. SOUS.TOTAL n'ignore que les lignes masquées. Elle ne saute pas les colonnes masquées dans une plage horizontale.
- #VALEUR! Le numéro de fonction n'est pas compris entre 1–11 ou 101–111.
- Vous devez aussi ignorer des erreurs. Utilisez AGREGAT, qui fonctionne comme SOUS.TOTAL mais peut en plus ignorer les valeurs d'erreur :
=AGREGAT(9; 7; C2:C6)
Questions fréquentes
Quelle est la différence entre SOUS.TOTAL 9 et 109 ?
Les deux n'additionnent que les lignes visibles après un filtre. 109 ignore aussi les lignes que vous avez masquées à la main ; 9 les inclut toujours.
Comment additionner seulement les cellules visibles dans Excel ?
Utilisez =SOUS.TOTAL(109; plage). Elle ignore les lignes masquées par un filtre et celles masquées à la main.
Pourquoi utiliser SOUS.TOTAL plutôt que SOMME ?
SOMME additionne toujours toutes les cellules de la plage. SOUS.TOTAL respecte les filtres et ignore les autres sous-totaux : elle donne donc le bon total de ce qui est visible à tout moment.
Qu'est-ce qu'AGREGAT ?
AGREGAT est une version plus puissante de SOUS.TOTAL, disponible depuis Excel 2010. Elle propose plus de calculs et peut aussi ignorer les valeurs d'erreur.