La fonction SOUS.TOTAL d’Excel est une formule utile pour générer des tableaux concis et sans risque d’erreur. Elle vous permet de regrouper et de totaliser des valeurs sur des lignes ou des colonnes, offrant ainsi un moyen rapide d’analyser et de résumer de grandes quantités de données. Si aujourd’hui, vous multipliez les sommes et que vous avez peur de compter plusieurs fois la même valeur ou d’en oublier d’autres, je vous explique pas-à-pas comment utiliser la formule SOUS.TOTAL. Elle m’est très pratique dans mon quotidien de Contrôleur de Gestion et elle est essentielle pour gagner du temps dans Excel. Vous êtes prêt(e) à maitriser cette fonction ? Suivez le guide !
Pourquoi utiliser la fonction SOUS.TOTAL dans vos tableaux Excel ?
Chaque jour vous construisez vos tableaux dans vos feuilles de calcul. Vous savez parfaitement créer des totaux en bout de ligne et de colonne et vous avez voulu créer des sous-parties pour avoir des sous-totaux et vous rendre compte du poids de chaque section dans le total.
Vous faites peut être face à un volume de données important ou tableau très grand et vous souhaitez à terme masquer ou grouper certaines sous parties tout en laissant visible la ligne sous total. C’est très intéressant lorsqu’on analyse un compte de résultat par exemple. On peut passer d’une version condensée à une version détaillée très rapidement tout en gardant le niveau de détail requis pour une analyse complète.
Cependant, il arrive parfois que la somme des totaux des lignes ne soit pas égale à la somme des totaux des colonnes. Oups ! Dans ce cas, vous êtes parti(e) pour de longues minutes de recherche (ou même des heures). Vous dépouillez votre classeur et chercher dans chaque cellule de total ou sous-total où l’erreur a été commise.
En fonction de la taille de votre tableau et de sa complexité, identifier cette erreur peut être plus ou moins compliquée. On risque de passer à côté.
C’est pour ça qu’existe la formule SOUS.TOTAL. Elle vous permet d’éviter ce type de problème. Il n’y aura plus de risque de compter plusieurs fois une cellule ou d’en oublier une autre.
Les arguments de la fonction SOUS.TOTAL.
La formule SOUS.TOTAL a besoin de 2 arguments pour fonctionner correctement. La synthaxe de la fonction SOUS.TOTAL se présente sous la forme suivante :
=SOUS.TOTAL(numéro de la fonction;plage de cellules)
Voyons en détail ces arguments :
Numéro de la fonction
La fonction SOUS.TOTAL ne se contente pas uniquement de faire des sous totaux, elle peut calculer les moyennes des sous parties de votre tableau, le nombre d’éléments. Elle peut trouver le maximum et le minimum de chaque sous partie du tableau. Il faut donc préciser à Excel ce que nous souhaitons calculer avec la formule SOUS.TOTAL. Généralement ce sera bien évidemment la somme des valeurs.
Pour indiquer à Excel ce que nous souhaitons calculer avec la fonction SOUS.TOTAL, il faudra utiliser un nombre.
- 9 – somme ;
- 1 – moyenne ;
- 2 – nombre ;
- 3 – nombre de valeurs ;
- 4 – maximum ;
- 5 – minimum ;
Et si vous souhaitez faire la somme en enlevant les lignes masquées. Il faudra indiquer à Excel comme numéro de fonciton le numéro 109 (101 pour la moyenne,…).
Plage de cellules
Le second argument de notre fonction SOUS.TOTAL sera la sélection des cellules sur lesquelles il faut faire la somme (ou une autre opération). Il vous suffit de cliquer sur vos cellules. Je vous montre la synthaxe par un exemple.
Comment utiliser la fonction SOUS.TOTAL dans Excel ?
Prenons notre exemple du tableau des ventes pour comprendre et mettre en oeuvre la fonction SOUS.TOTAL dans Excel. Naturellement, vous auriez eu tendance à faire ça :
Le problème est alors que vous risqueriez d’oublier des valeurs comme la ligne Autres ventes. Si vos sous totaux se déplacent ou que de nouvelles lignes sont intégrées, l’intégrité de votre tableau risque d’être remise en question.
Nous allons simplement remplacer toutes les formules dans les cellules de sous-totaux et de totaux par la fonction SOUS.TOTAL. Comme ceci :
A présent, Excel est parfaitement capable de comprendre l’organisation de votre tableau. Il n’intégrera pas dans une somme de niveau supérieur, une valeur déjà intégrée dans une somme de niveau inférieur. Fini les risques de compter plusieurs fois les mêmes cellules dans vos formules.
Utiliser l’outil d’Excel pour les sous-totaux
Excel a voulu vous simplifier la tâche pour calculer les sous-totaux sur des classeurs clairement ordonnés. Je vais vous présenter cette méthode même si dans la réalité je la trouve peu applicable. Il est peu fréquent que nos données soient ordonnées de cette façon. Ce sera généralement le cas d’exports de logiciels. De plus, il faudra se contenter de la mise en forme d’Excel. Je vous la montre tout de même.
Vos données doivent avoir la forme d’un tableau simple avec une ou plusieurs colonnes permettant d’identifier clairemement les sous-parties sur lesquelles il faudra faire des sous-totaux. Dans le tableau, j’ai intégré la ligne type pour simuler ce cas.
Ensuitez cliquez sur l’une des cellules de votre tableau et rendez vous dans la section Données de votre ruban pour y trouver le bouton Sous-total. Cliquez dessus.
Une fenêtre va apparaitre. Dans le premier encart, indiquez la colonne qui contient vos catégories. Ensuite, indiquez la fonction que vous souhaitez utiliser. Dans notre cas, nous voulons faire des sous totaux, nous allons donc demander à faire la somme des valeurs. Il faut cocher les colonnes concernées par les sous totaux. Dans notre cas ce sont les colonnes 2020 et 2021. On peut personnaliser la mise en forme grâce aux 3 boutons à cocher.
Et voilà, nos sous totaux ont été produits en un rien de temps. Cette fonctionnalité est vraiment sympa mais dans la réalité, c’est surtout la fonction SOUS.TOTAL que nous utiliserons directement pour faire nos sous-totaux dans Excel.
Astuce de pro : le tableau au carré
Si vous êtes arrivé(e), jusque là, je souhaite vous communiquer une petite astuce. Je la tiens de mon premier chef (big up à toi Guillaume !). Il s’agit d’une pratique appliquée dans beaucoup de grands groupes pour s’assurer que nous ne sommes pas passés à côté d’une cellule. On l’appelle le “tableau au carré”. C’est à dire que nous nous assurons régulièrement que nos totaux en ligne et en colonne soient égaux.
A chaque mise à jour du tableau, un contrôle visuel de quelques secondes sur les VRAI / FAUX permet de se rendre compte s’il y a un problème ou non. Dans notre quotidien bien chargé, gagner de précieuses minutes dans la production du reporting, c’est appréciable non ?
Conclusion
Les sous-totaux sont très importants dans Excel. Ils nous permettent d’effectuer des calculs sur de grands volumes de données. Dans cette leçon, vous devriez avoir appris à ajouter des sous-totaux manuellement et avec la fonction SOUS.TOTAL.
Bien qu’il s’agisse d’une fonction extrêmement intéressante pour les grands classeurs, je ne pense pas qu’elle soit assez utilisée par les utilisateurs. C’est souvent par méconnaissance.
J’espère que vous avez apprécié ces explications. Dites le moi en commentaire. Votre retour est important pour l’amélioration des articles. A bientôt !
