Numeriblog Google Workspace Google Sheets, le tableur surpuissant Google Sheets : comment faire une somme sur des valeurs filtrées avec la fonction magique « sous.total » ?

Google Sheets : comment faire une somme sur des valeurs filtrées avec la fonction magique « sous.total » ?

Amis du Sheets, bonjour. Aujourd’hui, on parle de la fonction =subtotal ou =sous.total. Dans la famille Mathématiques, je demande sous.total ! Bonne pioche ! La fonction est largement sous utilisée et méconnue et je vais […]

5(1)
personnes ont consulté cet article

3 minutes

Rédigé par Thierry Vanoffe - il y a 2 ans

Ce que vous allez découvrir

  • Démonstration qu'un filtre ou un masquage de lignes ne modifie pas le total
  • Syntaxe de la fonction sous.total

Google Sheets : comment faire une somme sur des valeurs filtrées avec la fonction magique « sous.total » ?

Amis du Sheets, bonjour. Aujourd’hui, on parle de la fonction =subtotal ou =sous.total. Dans la famille Mathématiques, je demande sous.total ! Bonne pioche ! La fonction est largement sous utilisée et méconnue et je vais tenter avec ce nouvel article de la rendre plus publique. Lors des formations que je dispense, je vois souvent des yeux écarquillés quand je montre que la somme d’un tableau filtré n’est plus bonne.

Démonstration qu’un filtre ou un masquage de lignes ne modifie pas le total

Explications sur ce qui pourrait s’apparenter à un bug à première vue de noob du tableur. Considérons ici un mini tableau des chiffres d’affaires par client et par région. Un total des CA par région est disponible en ligne 8. Au passage, le bouton « explorer » en bas à droite reste le moyen le plus rapide de faire un total.

Dès lors que je vais masquer certaines lignes ou appliquer un filtre et retirer de la vue quelques lignes, le total restera inchangé !

En effet, en cellule B8, le résultat est « 22 651 » car il retourne la somme de B2 à B7, que les cellules soient masquées, filtrées ou affichées. Google Sheets est discipliné (et non bête), il respecte la formule demandée « =somme(B2:B7) ». Je vous laisse constater par vous-même si vous aviez un doute ou si mon propos n’était pas assez clair.

sous.total Sheets

Ok super, ça marche et c’est logique. Mais comment faire pour que le total corresponde à ce qui est présent à l’écran ? Autrement dit, je veux le total de ce que je vois. C’est là que la super formule magique =sous.total fait son apparition.

Syntaxe de la fonction sous.total

En regardant de plus près le centre d’aide de la firme de Mountain view, on peut y lire la définition suivante : affiche un sous-total d’une plage verticale de cellules en utilisant une fonction d’agrégation spécifiée. Pour faire simple, le sous.total a un intérêt que s’il est positionné sur une plage verticale et non horizontale (en gros, vous mettez votre somme en haut ou en bas mais pas à droite de votre base de données).

La syntaxe est :

SOUS.TOTAL(code_fonction; plage1; [plage2; ...])

Il existe des codes fonctions selon si vous avez besoin de faire une somme, une moyenne ou d’autres fonctions mathématiques ou statistiques comme un écart type. Voici les codes fonctions à utiliser dans l’agrégation de « sous.total ».

  • 1 est MOYENNE
  • 2 est NB
  • 3 est NBVAL
  • 4 est MAX
  • 5 est MIN
  • 6 est PRODUIT
  • 7 est ECARTYPE
  • 8 est ECARTYPEP
  • 9 est SOMME
  • 10 est VAR
  • 11 est VAR.P

Voici quelques explications complémentaires sur ces codes fonctions :

  • VAR.P : calcule la variance basée sur toute une population.
  • VAR : calcule la variance basée sur un échantillon.
  • SOMME : affiche la somme d’une série de nombres et/ou de cellules.
  • ECARTYPEP : calcule l’écart type basé sur toute une population.
  • ECARTYPE : calcule l’écart type basé sur un échantillon.
  • PRODUIT : affiche le résultat de la multiplication d’une série de nombres.
  • MIN : affiche la valeur minimale d’un ensemble de données numériques.
  • MAX : affiche la valeur maximale d’un ensemble de données numériques.
  • NBVAL : affiche le décompte du nombre de valeurs d’un ensemble de données.
  • NB : affiche le nombre de valeurs numériques d’un ensemble de données.
  • MOYENNE : affiche la valeur numérique moyenne d’un ensemble de données, sans tenir compte du texte.

Super, ça avance ! Mais l’autre jour, le super formateur de Numericoach m’a parlé de code 109 pour faire une somme, c’est quoi la différence entre 9 et 109 du coup ? Tiens, cela me fait penser à la blagounette, quelle est la différence entre un oeuf et un peigne… (Google est ton ami), sinon les commentaires sont tout en bas 😂

Le code 9, qui est aussi une série télévisée britannique d’espionnage, permet d’inclure les valeurs masquées pour une somme tandis que le code 109 les ignore, donc cela répond à notre besoin de faire une somme des valeurs visibles sur l’écran.

FonctionCode (inclut les valeurs cachées)Code (ignore les valeurs cachées)
AVERAGE1101
COUNT2102
COUNTA3103
MAX4104
MIN5105
PRODUCT6106
STDEV7107
STDEVP8108
SUM9109
VAR10110
VARP11111

Démonstration avec un nouveau gif animé sur la base de données initiale (animation réalisée avec Snag-it si vous vous posiez la question) :

La formule est :

=SUBTOTAL(109;B2:B7)

CQFD ! Cela fonctionne !

Comme vous le savez, vous ne pouvez pas exclure les lignes filtrées ou masquées dans les fonctions conditionnelles telles que Countif, Sumif, Minifs, Maxifs et Averageif dans Google Sheets. La seule fonction qui fonctionne sur les données filtrées est « sous.total ». Cela fonctionne aussi bien sur les données cachées. Vous devez donc en faire usage le plus possible.

Pour terminer, sachez également que :

  • Cette fonction sous.total peut être utilisée pour créer des tableaux de bord dynamiques. Pour ce faire, l’argument de code de fonction doit faire référence à une autre cellule. Lorsqu’elle est combinée avec une validation de données basée sur une liste, cette cellule peut être convertie en liste déroulante mettant instantanément à jour le tableau de bord complet.
  • Cette fonction sous.total peut être utilisée pour effectuer une analyse rapide des différents sous-ensembles de données. Pour ce faire, un tableau de bord de sous-totaux doit être créé au-dessus de la zone filtrée. Chaque fois que les critères de filtre changent, le tableau de bord se met automatiquement à jour avec les nouveaux totaux.
  • L’utilisation de sous.total empêche les calculs en double associés aux formules SOMME simples.

Voici un autre article qui pourrait te plaire sur Google Sheets.

Besoin de rapidement monter en compétences avec une vraie méthode de formation certifiante sur Google Sheets, éligible au CPF ? Cela se passez chez Numericoach et ici.

Besoin d'un peu plus d'aide sur Sheets ?

Des formateurs sont disponibles toute l'année pour vous accompagner et optimiser votre utilisation de Sheets, que ce soit pour votre entreprise ou pour vos besoins personnels !

Découvrir nos formations Sheets

Rédacteur

Photo de profil de l'auteur
Thierry Vanoffe

Thierry VANOFFE, consultant, formateur, coach Google Workspace CEO de Numericoach, leader de la formation Google Workspace en France. Passionné par Google, ce blog me permet de partager cette passion et distiller tutos, trucs, astuces, guides sur les outils Google. N'hésitez pas à me solliciter pour vos projets de formation.

S’abonner
Notification pour
guest

5

Note du cours

(1)

(0)

(0)

(0)

(0)

1 Commentaire
Le plus ancien
Le plus récent Le plus populaire
Commentaires en ligne
Afficher tous les commentaires
gaultier
gaultier
Votre note :
     

Bonjour, merci pour tous vos tutos.
J’aimerai savoir si il est possible de faire la somme de plage qui sont le résultat d’une formule.

Je m’explique, j’ai actuellement un tableau avec des cases a cocher.
Lorsque ma case L6 est coché, une valeur apparaît dans la case K6 grace a la formule =SI(L6=VRAI; »-10,00″; » ») que j’ai pris de l’un de vos tutos. J’ai fait cela de K6 à K20

J’aimerai ensuite pouvoir faire l’addition des valeurs qui apparaissent de K6 a K20 mais en faisant le =SOMME(K6:K20) je n’obtiens rien.

Est ce que ce que je souhaite faire est possible ou non ?

Merci d’avance pour votre réponse

Continuez votre super taf, et merci encore.

Gaultier

Cet avis vous a été utile ?