=SOUS.TOTAL(9;C2:C8) additionne les nombres de C2:C8, comme SOMME, avec deux différences : elle ignore les autres formules SOUS.TOTAL de la plage, et elle ignore les lignes masquées par un filtre. Le premier argument, 9, indique le calcul à faire. SOUS.TOTAL s'appelle SUBTOTAL dans un Excel anglais ; le tableau affiche les formules sous cette forme, avec des virgules, mais vous pouvez aussi les taper comme dans un Excel français, avec des points-virgules : =SOUS.TOTAL(9;C2:C8).
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Item | Sales | Check | Result | |
| 2 | North | Apple | 120 | SUM of C2:C7 | 890 | |
| 3 | North | Pear | 80 | |||
| 4 | North total | 200 | ||||
| 5 | South | Apple | 200 | |||
| 6 | South | Pear | 45 | |||
| 7 | South total | 245 | ||||
| 8 | Grand total | 445 |
=SOUS.TOTAL(9;C2:C7)Le total général en C8 porte sur toute la colonne, lignes de sous-total comprises, et affiche pourtant 445 : SOUS.TOTAL laisse de côté C4 et C7 parce qu'elles contiennent des formules SOUS.TOTAL. F2 fait la même chose avec SOMME et affiche 890, chaque vente comptée deux fois. Avec SOUS.TOTAL sur chaque ligne de total, vous pouvez ajouter ou déplacer des groupes sans réécrire le total général.
Les numéros de fonction de SOUS.TOTAL
=SUBTOTAL(function_num, ref1, [ref2], ...)
| Calcul | Ignore les lignes filtrées | Ignore aussi 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.STANDARD | 7 | 107 |
| ECARTYPE.PEARSON | 8 | 108 |
| SOMME | 9 | 109 |
| VAR.S | 10 | 110 |
| VAR.P.N | 11 | 111 |
Quand vous tapez =SOUS.TOTAL(, Excel affiche cette liste : inutile de l'apprendre par cœur. 9 et 109 (SOMME), 1 (MOYENNE) et 103 (compter les lignes visibles) sont les plus utilisés.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Item | Sales | Calculation | Result | |
| 2 | North | Apple | 120 | AVERAGE (1) | 88.33 | |
| 3 | North | Pear | 80 | COUNTA (3) | 6 | |
| 4 | South | Apple | 200 | MAX (4) | 200 | |
| 5 | South | Pear | 45 | MIN (5) | 30 | |
| 6 | East | Apple | 55 | Visible rows (103) | 6 | |
| 7 | East | Plum | 30 | SUM (109) | 530 |
=SOUS.TOTAL(1;C2:C7)Rien n'est masqué ici, donc chaque ligne est égale à la fonction ordinaire : une moyenne de 88.33, 6 lignes, un MAX de 200, un MIN de 30 et une SOMME de 530. La différence n'apparaît que lorsque des lignes sont masquées, ce dont parle la section suivante.
SOUS.TOTAL 9 ou 109, et les lignes filtrées
Activez un filtre avec Données > Filtrer (Ctrl+Maj+L, Cmd+Maj+F sur Mac), puis choisissez North dans la liste déroulante Region. Les lignes des autres régions sont masquées :
=SOMME(C2:C7)additionne toujours les six lignes.=SOUS.TOTAL(9;C2:C7)et=SOUS.TOTAL(109;C2:C7)n'additionnent que les lignes North visibles.=SOUS.TOTAL(103;A2:A7)compte les lignes restées à l'écran : 2, le même nombre que "2 enregistrements sur 6 trouvés" dans la barre d'état.
Les deux familles ne diffèrent que pour les lignes masquées à la main (sélectionnez des lignes, clic droit > Masquer). 9 les additionne encore ; 109 non. Si le total doit toujours correspondre à ce qui est à l'écran, utilisez 109. Si vous masquez des lignes uniquement pour alléger l'affichage et voulez les garder dans le total, utilisez 9.
SOUS.TOTAL ne travaille que sur les lignes. Les colonnes masquées sont toujours incluses : =SOUS.TOTAL(109;B2:G2) sur une ligne additionne aussi les colonnes masquées.
Le moyen le plus rapide d'obtenir un SOUS.TOTAL est le bouton Somme automatique avec un filtre actif : Excel écrit =SOUS.TOTAL(9;...) au lieu de SOMME. Données > Sous-total va plus loin : sur une liste triée selon une colonne, il insère une ligne de total sous chaque groupe et un total général, tous avec SOUS.TOTAL, plus des boutons de plan pour réduire les groupes.
AGREGAT : un SOUS.TOTAL qui peut ignorer les erreurs
Si une cellule de la plage contient une erreur, SOMME et SOUS.TOTAL renvoient cette erreur. AGREGAT (AGGREGATE en anglais, Excel 2010 et versions ultérieures) est un SOUS.TOTAL doté d'un argument d'options supplémentaire ; l'option 6 ignore les valeurs d'erreur.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Item | Sales | Formula | Result | |
| 2 | North | Apple | 120 | SUBTOTAL | #N/A | |
| 3 | North | Pear | #N/A | AGGREGATE, ignore errors | 450 | |
| 4 | South | Apple | 200 | AGGREGATE, MAX | 200 | |
| 5 | South | Pear | 45 | |||
| 6 | East | Apple | 55 | |||
| 7 | East | Plum | 30 |
=AGREGAT(9;6;C2:C7)C3 contient #N/A (même nom en français et en anglais), donc F2 affiche aussi #N/A. F3 l'ignore et additionne les cinq autres : 450. Son premier argument utilise les mêmes numéros que SOUS.TOTAL (9 pour SOMME, 4 pour MAX). Autres options : 5 ignore les lignes masquées, 7 ignore les lignes masquées et les erreurs, 3 ignore les lignes masquées, les erreurs et les formules SOUS.TOTAL et AGREGAT imbriquées. Remplacez C3 par un nombre et F2 affiche le même total que F3.
Exercice : un total général sur des sous-totaux
| A | B | C | |
|---|---|---|---|
| 1 | Region | Item | Sales |
| 2 | North | Apple | 120 |
| 3 | North | Pear | 80 |
| 4 | North total | 200 | |
| 5 | South | Apple | 200 |
| 6 | South | Pear | 45 |
| 7 | South | Plum | 60 |
| 8 | South total | 305 | |
| 9 | Grand total |
À vous : La liste a un sous-total sous chaque région. Placez en C9 un total général qui couvre C2:C8 sans compter deux fois les lignes de sous-total.
Pourquoi un total SOUS.TOTAL reste faux
- Les totaux de groupe utilisent SOMME. SOUS.TOTAL ignore les autres formules SOUS.TOTAL de sa plage, pas les formules SOMME. Un total de groupe écrit
=SOMME(C2:C3)est compté une seconde fois. Passez chaque ligne de total en SOUS.TOTAL. - Les lignes ont été masquées à la main et le numéro de fonction est 9. Utilisez 109.
- Les données sont en colonnes, pas en lignes. Les colonnes masquées ne sont jamais ignorées.
- Il vous faut une condition, pas un filtre. SOUS.TOTAL suit ce que le filtre masque. Pour totaliser North sans filtrer, utilisez SOMME.SI. Pour une synthèse de tous les groupes à la fois, un tableau croisé dynamique le fait sans lignes de total dans les données.
Questions fréquentes
Que signifie SOUS.TOTAL 9 dans Excel ?
Le premier argument choisit le calcul, et 9 correspond à SOMME. =SOUS.TOTAL(9;C2:C8) additionne C2:C8 en ignorant les lignes masquées par un filtre et les autres formules SOUS.TOTAL de la plage. 1 correspond à MOYENNE, 2 à NB, 3 à NBVAL, 4 à MAX, 5 à MIN.
Quelle est la différence entre SOUS.TOTAL 9 et 109 ?
Les deux ignorent les lignes masquées par un filtre. 109 ignore aussi les lignes que vous avez masquées à la main (clic droit > Masquer), alors que 9 les additionne encore. Utilisez 109 quand le total doit correspondre exactement à ce qui est à l'écran.
Comment additionner seulement les cellules visibles après un filtre ?
Utilisez =SOUS.TOTAL(9;C2:C100) ou =SOUS.TOTAL(109;C2:C100) sous les données. Quand vous filtrez la liste, le total ne porte plus que sur les lignes visibles. Une simple SOMME continue d'additionner les lignes masquées.
Comment compter les lignes visibles d'une liste filtrée ?
Utilisez =SOUS.TOTAL(103;A2:A100). 103 correspond à NBVAL en ignorant les lignes masquées : la formule compte les cellules remplies encore affichées.
Comment additionner une plage qui contient des erreurs ?
Utilisez AGREGAT avec l'option 6, qui ignore les erreurs : =AGREGAT(9;6;C2:C8). SOMME et SOUS.TOTAL renvoient toutes deux l'erreur si une cellule de la plage contient #N/A.
