INFORMATIQUE

Sous-total Excel : calculer le bon total avec ou sans filtre

Vous filtrez un tableau Excel, mais votre total ne change pas ? Utilisez =SOUS.TOTAL(109;C2:C5) pour additionner uniquement les lignes visibles. Contrairement à SOMME, cette formule adapte le résultat aux filtres et ignore les lignes masquées manuellement.

La fonction SOUS.TOTAL Excel sert aussi à calculer des moyennes, compter des cellules et éviter les doubles comptages. Voici comment l’utiliser, avec des exemples vérifiables dans Excel pour Microsoft 365, 2024, 2021, 2019 et 2016.

L’essentiel

  • =SOUS.TOTAL(109;C2:C5) additionne les lignes visibles.
  • Le code 9 compte les lignes masquées à la main ; le code 109 les ignore.
  • Les lignes exclues par un filtre sont toujours ignorées.
  • Données > Sous-total crée des totaux automatiques par groupe.

Un exemple concret : pourquoi SOMME donne le mauvais total

Un responsable des ventes prépare son reporting mensuel. Il filtre son export pour afficher uniquement les écrans, puis présente en réunion un chiffre de 570 €. Problème : les ventes d’écrans représentent seulement 400 €. Sa formule SOMME additionnait toujours toutes les catégories.

Reproduisons son tableau :

CatégorieProduitMontant (€)
ÉcransÉcran 24 pouces150
ÉcransÉcran 27 pouces250
ClaviersClavier sans fil60
CasquesCasque sans fil110

Les en-têtes occupent la ligne 1, les montants la colonne C, de C2 à C5.

Sans filtre, =SOMME(C2:C5) et =SOUS.TOTAL(109;C2:C5) affichent toutes les deux 570 €.

Activez maintenant Données > Filtrer, puis sélectionnez uniquement la catégorie Écrans. SOMME affiche encore 570 €, tandis que SOUS.TOTAL renvoie 400 €. Le résultat suit les données réellement affichées.

Comprendre la formule SOUS.TOTAL Excel

La syntaxe française, détaillée dans l’aide Microsoft de la fonction SOUS.TOTAL, est =SOUS.TOTAL(no_fonction;réf1;[réf2];…).

Le premier argument indique le calcul souhaité : somme, moyenne, nombre de cellules ou autre opération. Il accepte un code de 1 à 11 ou de 101 à 111. Le deuxième désigne la plage à traiter. Les références supplémentaires sont facultatives. Dans Excel en français, les arguments sont séparés par des points-virgules.

9 ou 109 : la différence qui change vos résultats

Les deux codes additionnent les données, mais ne traitent pas les lignes masquées manuellement de la même manière.

Reprenons notre tableau filtré sur Écrans. Faites un clic droit sur la ligne 3, puis choisissez Masquer.

=SOUS.TOTAL(9;C2:C5) affiche 400 € : la ligne 3 reste comptée malgré son masquage manuel. Avec =SOUS.TOTAL(109;C2:C5), le résultat descend à 150 €.

Sans aucun filtre, mais avec cette même ligne masquée, le code 9 donne 570 € et le code 109 donne 320 €.

À retenir : les filtres excluent toujours les lignes du calcul SOUS.TOTAL. Seules les lignes masquées manuellement distinguent les deux familles de codes.

Les 11 fonctions et leurs numéros

SOUS.TOTAL ne sert pas uniquement à additionner. Voici tous les codes disponibles :

Code comptant les lignes masquées à la mainCode les ignorantFonction équivalente
1101MOYENNE
2102NB
3103NBVAL
4104MAX
5105MIN
6106PRODUIT
7107ECARTYPE
8108ECARTYPEP
9109SOMME
10110VAR
11111VAR.P

Par exemple, =SOUS.TOTAL(101;C2:C5) calcule la moyenne des montants visibles.

Créer des sous-totaux automatiques par catégorie

Inutile de saisir une formule après chaque groupe : Excel dispose d’une commande dédiée.

Calculatrice posée à côté de rapports imprimés avec des graphiques de ventes

Préparez une liste avec des en-têtes, des colonnes homogènes et aucune ligne ou colonne vide. Sélectionnez la liste, puis utilisez Données > Trier pour regrouper les catégories de A à Z ou de Z à A.

Ouvrez ensuite Données > Sous-total, dans le groupe Plan. Choisissez la colonne Catégorie pour le regroupement, la fonction Somme et la colonne Montant à totaliser. Vous pouvez aussi régler le remplacement des sous-totaux existants, les sauts de page et la position de la synthèse. Pour des sous-totaux imbriqués, la procédure Microsoft des sous-totaux détaille chaque étape.

Sur notre exemple, Excel crée les sous-totaux suivants : Écrans 400 €, Claviers 60 €, Casques 110 €, puis un total général de 570 €.

Les boutons 1, 2 et 3 à gauche permettent de passer du total général aux sous-totaux, puis au détail complet. Les symboles + et − développent ou replient chaque groupe.

La suppression passe par le même menu : cliquez dans la plage, retournez dans Données > Sous-total, puis choisissez Supprimer tout.

Si la commande est grisée, votre liste est probablement un tableau Excel structuré. Faites un clic droit dans le tableau, puis Tableau > Convertir en plage et confirmez avec Oui. La mise en forme reste, mais les fonctionnalités propres au tableau disparaissent.

Ajouter une condition ou regrouper par date

SOUS.TOTAL ne possède aucun argument de critère. SOMME.SI et SOMME.SI.ENS permettent des calculs conditionnels, mais n’ont aucun argument pour exclure les lignes filtrées ou masquées. Pour tester chaque ligne, la fonction SI Excel reste la base, et RECHERCHEV avec plusieurs critères retrouve une valeur selon plusieurs conditions.

Pour additionner les montants visibles de la catégorie Écrans, même sans filtre, utilisez :

=SOMMEPROD((A2:A5="Écrans")*SOUS.TOTAL(103;DECALER(C2;LIGNE(C2:C5)-LIGNE(C2);0))*C2:C5)

DECALER transmet les cellules une par une à SOUS.TOTAL. Le code 103 renvoie 1 pour une ligne visible et 0 pour une ligne masquée ; SOMMEPROD combine cette visibilité, le critère Écrans et les montants. Avec la ligne 3 masquée, le résultat est 150 €.

Dans la plupart des cas, filtrer sur le critère puis utiliser SOUS.TOTAL(109;…) reste plus simple.

Pour des sous-totaux Excel par date, la commande crée un groupe à chaque changement de valeur. Si vous souhaitez des totaux mensuels, ajoutez une colonne Mois, triez dessus, puis appliquez la commande. Pour d’autres calculs calendaires, consultez notre guide sur le nombre de mois entre deux dates.

SOUS.TOTAL Excel ne fonctionne pas : trois vérifications

Le total ne change pas comme prévu

Vérifiez le code utilisé et la manière dont les lignes ont été masquées. Avec le code 9, un masquage manuel ne réduit pas la somme. Utilisez 109 si vous voulez exclure ces lignes.

Le total général est trop élevé

Un comptable pose un total général avec SOMME sous plusieurs sous-totaux. Son chiffre double, parce que les montants détaillés et les totaux intermédiaires sont additionnés ensemble.

SOUS.TOTAL ignore les autres SOUS.TOTAL présents dans sa plage. Si les sous-totaux et le total général utilisent tous cette fonction, le double comptage disparaît. Placez toutefois la formule sur une plage verticale : masquer une colonne dans une plage horizontale ne modifie pas son résultat.

Excel affiche une erreur

Une référence 3D couvrant plusieurs feuilles provoque #VALEUR! avec SOUS.TOTAL. Vérifiez également les références et les séparateurs de la formule.

Si votre colonne contient des cellules en erreur, la fonction AGREGAT propose une autre approche : =AGREGAT(9;6;C2:C5) additionne en ignorant les valeurs d’erreur.

FAQ sur les sous-totaux

Comment écrire SOUS.TOTAL en anglais ?

Le nom anglais est SUBTOTAL. Sa syntaxe est SUBTOTAL(function_num, ref1, [ref2], …).

La fonction existe-t-elle dans Google Sheets ?

Oui. Google Sheets propose SOUS.TOTAL en français et SUBTOTAL en anglais, avec les onze fonctions et les codes 101 à 111 pour ignorer les lignes masquées manuellement. Les lignes filtrées et les autres sous-totaux présents dans la plage sont également ignorés.

Quelle est la définition d’un sous-total ?

Un sous-total est un total partiel calculé sur une partie des données, par exemple les ventes d’une catégorie avant le total général. Dans Excel, il permet de lire les résultats par groupe sans perdre la vue d’ensemble.

Les commentaires sont fermés.

Article Suivant :

0 %