ALL Google Sheets

Google Sheets : 4 façons de faire des sous totaux

Ecrit par Thierry

Dans cet article, issu d’une demande la semaine dernière d’une participant à une session de formation Google Tableur, je vous présente 4 façons de faire des sous totaux, c’est à dire des sommes par catégories ou codes analytiques.

 Vous verrez ainsi que deux techniques (=QUERY) et (=SOMME.Si) pourront vous faire gagner un temps précieux, tout comme le fameux TCD (tableau croisé dynamique).

Voici le tableau de l’énoncé (il s’agit d’un fichier de reporting pour du linge servant à établir la facturation, les données ont été anonymisées et modifiées) :

Vous pouvez utiliser ce fichier pour vous exercer… (une copie sera automatiquement générée).

Méthode 1 : faire le tableau récap’ à la main et additionner chaque cellule entre elle.

Vous l’avez compris la méthode est longue et sinueuse. Le risque d’erreur est important. C’est généralement la méthode utilisée quand on ne connaît pas les deux suivantes.

Les formules ressemblent donc pour chaque code à  :

On peut se rassurer avec des sous totaux intermédiaires ici :

Méthode 2 : la fonction =SOMME.SI

=SOMME.SI (=SUMIF en anglais) affiche une somme conditionnelle au sein d’une plage.

La syntaxe est : SOMME.SI(plage; critère; [somme_plage])

Ce qui donne pour notre tableau : =SOMME.SI(A:A;F5;G:G)

  • A:A est la plage dans lequel il va chercher le code analytique.
  • F5 est le critère, c’est à dire le code qu’il va chercher dans la plage A:A
  • G:G est la plage qu’il va sommer.

Nous avons les mêmes résultats dans le tableau de synthèse en moins de 30 secondes. Excellent, nous pourrions nous arrêter là. La formule SOMME.SI est relativement facile à mettre en place. Attention néanmoins à vérifier que chaque code soit présent et que les données soient au bon format (nombre).

Méthode 3 : =QUERY

Notre ami Jean-Paul JOURDAN nous délivre le secret de cette fonctionne qu’il affectionne particulièrement.

Quelques explications s’imposent, la fonction est loin d’être facile à dompter pour le commun des mortels :

La fonction de base à utiliser est =Query() qui exécute sur toutes les données une requête écrite dans le langage de requête de l’API Google Visualization.
Dans le tableau en question, les données intéressantes sont :
  • La colonne A, ( qui contient des codes analytiques à regrouper dans le total)
  • La Colonne F (qui contient les valeurs qu’il faut additionner)
On va donc demander à Query de créer un tableau à partir des données de la plage A16:F    =Query(A16:F
qui va faire le total des sommes contenues dans la colonne F « Select sum(F)
en regroupant par code analytique pivot A »
le ;1 permet de signaler à query la présence d’en-tête sur la premiere ligne de la plage de données
Ensuite, la fonction transpose() va mettre le résultat de la requête en colonnes et non en lignes
That’s all folks 🙂

Méthode 4 : faire un TCD (pivot table pour ceux qui ont laissé l’interface en anglais dans Google Sheets) :

Il faut avouer ici que la source de données n’est pas super bien formatée pour un TCD, les sous totaux réalisés à la main ne sont vraiment pas utiles. Il est préférable pour un TCD efficace de ne pas avoir de cellules fusionnées et de lignes vides et avoir toutes les infos sous forme de colonnes sans tout total.
Voici la démonstration (encore une fois, en moins de 30 secondes, on obtient le résultat escompté) :
NB : S’il y a des lignes blanches dans la source sélectionné, il est important d’utiliser dans le TCD le filtre pour retirer les éléments vides, sinon des 0 apparaissent dans le tableau.
Il est possible de copier coller le TCD dans la source de données pour éviter de l’avoir dans une autre feuille.
Il est important qu’il ne manque pas de code dans la colonne A ici les codes analytiques.
Conclusion :
Dans Google Sheets, il y a souvent différents moyens d’arriver à un même résultat. Ici les 3 dernières techniques permettent en moins de 30 secondes de générer ce petit tableau de synthèse qui prenait environ 40 minutes par semaine à la personne en charge des opérations. Le gain de temps sur l’année pour cette personne est de l’ordre de 35H, une semaine complète de gagner grâce à une formule. N’hésitez donc pas à poser vos questions, vos problématiques ou tout simplement demander comment gagner du temps avec l’utilisation un peu plus avancée du tableur. De nombreux Tutoriels existant également sur Youtube.com pour monter en compétences. L’équipe de Numericoach se fera également un plaisir de répondre à vos questions, un consultant formateur peut être missionné sur vos données avec votre budget formation si beoin pour optimiser vos tableurs.

 

Notez cette information
[Total: 0 Average: 0]

A propos de l'auteur

Thierry

Thierry VANOFFE, consultant, formateur, coach G Suite.
Passionné et fasciné 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 formations en ligne ou en présentiel.

Laisser un commentaire

Abonnez-vous à la newsletter hebdo du lundi.

Recevez chaque lundi les 7 news, trucs et astuces sur G Suite.

L'abonnement à la newsletter a été réalisé avec succès !

Share This

Share This

Share this post with your friends!