Comment calculer des sous-totaux dans un tableau Excel : la fonction SOUS.TOTAL

Dans ce petit tutoriel, nous allons découvrir une fonction très pratique d’Excel qui va permettre d’effectuer plusieurs types de calculs tels que des additions, des dénombrements, des calculs de moyennes, etc… à l’intérieur d’un tableau Excel, tout en conservant la possibilité de calculer des totaux généraux.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer cette présentation de la fonction SOUS.TOTAL(), nous allons partir du tableau suivant dans lequel nous retrouvons les ventes réalisées par des commerciaux d’une entreprise au cours de l’année 2020 :

Comme vous pouvez le constater, dans ce tableau, nous retrouvons les noms et prénoms de chacun des vendeurs, ainsi que leur sexe et le magasin dans lequel elles travaillent.

Nous retrouvons également le montant des ventes réalisées au cours de l’année 2020, le nombre de clients servis, et enfin le CA moyen par client.

Nous allons souhaiter partir de cette base de données afin d’en extraire des données statistiques par magasin.

Mais avant de pouvoir l’utiliser correctement, nous allons devoir nous assurer que la base est triée de manière à ce que les éléments sur lesquels nous allons souhaiter faire apparaitre les sous-totaux soient réunis les uns avec les autres.

Or si nous regardons la base, ce n’est pas le cas ici, étant donné que celle-ci est triée par ordre alphabétique, à partir des prénoms.

Pour classer la base, il suffit de sélectionner l’une des cellules de la colonne Magasin, puis de cliquer sur Trier de A à Z depuis le menu Données du ruban :

Cela étant fait, nous allons devoir insérer de nouvelles lignes aux endroits où les sous-totaux vont être insérés.

Pour insérer une nouvelle ligne entre Bordeaux et Lille, nous commençons par sélectionner la ligne 12, puis appuyons sur les touches [Ctrl]+[+]

2. Utiliser la fonction SOMME

À présent, la solution la plus simple pour calculer le montant des ventes réalisées par le magasin de Bordeaux est d’utiliser la fonction SOMME() que nous avons déjà eu l’occasion de découvrir dans un tutoriel précédent, que vous pourrez retrouver en suivant ce lien.

 =SOMME(E9:E11) 

Nous repérons maintenant la même opération pour chaque magasin :

Maintenant, comme vous l’avez peut-être déjà remarqué, le problème de cette méthode, c’est que le total général est maintenant erroné, car les sommes que nous venons d’insérer sont comptées dans celui-ci.

Il est donc nécessaire de modifier la formule pour exclure ces cellules :

 =SOMME(E9:E11;E13:E16;E18:E21;E23:E26;E28:E30) 

Pour obtenir la somme de plusieurs plages de cellules, il suffit de saisir un point-virgule entre chacune d’entre elles.

3. Utiliser la fonction SOUS.TOTAL

La solution que nous venons de découvrir ici permet effectivement de calculer des sous-totaux, mais comme vous pouvez le voir, le fait de devoir modifier la formule du total général est assez contraignant.

D’autant plus que si ici le tableau n’est constitué que de quelques lignes, il ne sera pas envisageable d'utiliser cette technique sur un grand tableau avec des dizaines de sous-totaux.

Heureusement, Excel dispose d’une fonction spécialement dédiée et pour cela, nous allons utiliser la fonction SOUS.TOTAL() d’Excel.

Celle-ci permet, comme son nom l’indique de récupérer un sous-total à partir d’une liste ou d’une base de données.

Cette fonction attend au moins deux arguments :

 =SOUS.TOTAL(no_fonction;réf1;…) 

Où :

• No_fonction : correspond au type d’opération à mettre en place dans le sous-total :

• Réf1, réf2,… il s’agit ici de venir saisir les références à inclure dans le calcul du sous-total. Il est possible de renseigner entre 1 et 254 références.

Les fonctions sont réunies dans deux groupes :

• De 1 à 11
• Et de 101 à 111, la différence avec le premier groupe c’est qu’ici le calcul ignore les cellules masquées (attention, les cellules filtrées par un filtre classique seront toujours exclues)

Dans notre exemple, pour récupérer le montant des ventes réalisées par magasin, nous allons vouloir utiliser la fonction SOMME.

Étant donné qu’aucune cellule n’est masquée, nous pouvons utiliser la fonction du premier groupe : 9

 =SOUS.TOTAL(9;E9:E11) 

Ensuite, nous pouvons utiliser la fonction sur toutes les cellules de la colonne.

Nous pouvons ensuite utiliser à nouveau la fonction SOUS.TOTAL pour déterminer le montant des ventes, en appelant toutes les cellules de la colonne en tant que référence de calcul :

À présent, comme vous pouvez le constater, le montant du total général est maintenant correct, les sous-totaux sont en effet ignorés :

Nous pouvons maintenant utiliser la même méthode pour calculer le nombre de clients par magasin (en utilisant la poignée de recopie pour dupliquer la cellule) :

4. Utiliser la fonction SOUS.TOTAL automatiquement

L’un des autres avantages de la fonction SOUS.TOTAL, c’est qu’il est possible d’automatiser totalement son insertion au sein du tableau !

Pour illustrer cet exemple, commençons par supprimer toutes les lignes de sous-totaux que nous venons d’insérer :

Pour insérer des totaux automatiques, il suffit de cliquer sur l’une des cellules de ce tableau, puis de cliquer sur le bouton Sous-total du menu Données :

Excel ouvre alors une fenêtre dans laquelle nous sommes invités à paramétrer les sous-totaux à mettre en place :

Depuis cette fenêtre :

• Nous commençons par sélectionner la colonne pour laquelle nous souhaitons calculer les sous-totaux : il s’agit ici de la colonne magasin,
• Le second menu permet de choisir la fonction à utiliser pour les sous-totaux : Somme,
• Ensuite, nous choisissons les champs sur lesquels nous souhaitons mettre en place le calcul, ici les colonnes Ventes et Nb clients

Lorsque nous validons, Excel ajoute alors automatiquement les lignes pour y insérer les calculs demandés :

En plus de cela, Excel a mis en place un plan qui permet d’afficher et de masquer rapidement les données :

Pour en savoir plus sur le mode plan d’Excel, vous pouvez consulter le tutoriel dédié en cliquant ici.

Pour modifier les paramètres des sous-totaux, il suffit de répéter l’opération (sélectionner une cellule du tableau > Sous-total), et de cocher l’option « Remplacer les sous-totaux existants » :

5. Différence entre les fonctions SOUS.TOTAL et AGREGAT

La fonction SOUS.TOTAL que nous découvrons ici ressemble fortement une fonction que nous avons découverte récemment : la fonction AGREGAT.

En effet, ces deux fonctions sont très proches l’une de l’autre, même s’il existe évidemment quelques différences entre elles :

• Tout d’abord, la fonction AGREGAT n’est apparue qu’à partir d’Excel 2010. Si vous disposez d’une version plus ancienne, seule la fonction SOUS.TOTAL sera disponible.
• Les possibilités offertes par la fonction AGREGAT sont bien plus grandes, avec 19 fonctions disponibles, au lieu de 11 pour la fonction SOUS.TOTAL (nous y retrouvons notamment les fonctions PETITE.VALEUR et GRANDE.VALEUR) :

• La fonction AGREGAT est capable d’ignorer les cellules vides ou contenant des erreurs, contrairement à la fonction SOUS.TOTAL :

• L’avantage non négligeable de la fonction SOUS.TOTAL réside dans la fonctionnalité d’insertion automatique que nous avons vue dans la partie précédente !