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

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

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 :

Excel formation - fonction SOUS.TOTAL - 01

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 :

Excel formation - fonction SOUS.TOTAL - 02

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]+[+]

Excel formation - fonction SOUS.TOTAL - 03

 

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) 

Excel formation - fonction SOUS.TOTAL - 04

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

Excel formation - fonction SOUS.TOTAL - 05

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) 

Excel formation - fonction SOUS.TOTAL - 06

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 :

Excel formation - fonction SOUS.TOTAL - 07

  • 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)

no_fonction

Fonction

1 ou 101

MOYENNE

2 ou 102

NB

3 ou 103

NBVAL

4 ou 104

MAX

5 ou 105

MIN

6 ou 106

PRODUIT

7 ou 107

ECARTYPE

8 ou 108

ECARTYPEP

9 ou 109

SOMME

10 ou 110

VAR

11 ou 111

VAR.P

 

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) 

Excel formation - fonction SOUS.TOTAL - 08

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 :

Excel formation - fonction SOUS.TOTAL - 09

À 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 :

Excel formation - fonction SOUS.TOTAL - 10

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) :

Excel formation - fonction SOUS.TOTAL - 11

 

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 :

Excel formation - fonction SOUS.TOTAL - 12

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 formation - fonction SOUS.TOTAL - 13

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

Excel formation - fonction SOUS.TOTAL - 14

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

Excel formation - fonction SOUS.TOTAL - 15

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

Excel formation - fonction SOUS.TOTAL - 16

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

Excel formation - fonction SOUS.TOTAL - 17

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 » :

Excel formation - fonction SOUS.TOTAL - 18

 

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) :

Excel formation - fonction SOUS.TOTAL - 19

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

Excel formation - fonction SOUS.TOTAL - 20

  • 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 !

 



 



Articles qui pourraient vous intéresser

Comment identifier les cellules identiques en 1 clic sur Excel (couleur selon valeur) ?
Comment créer un tableau de bord qui s’actualise et identifie automatiquement les meilleures performances sur Excel !
L’erreur cachée qui plombe vos tableaux Excel (et comment l’éviter)
Comment entourer automatiquement cellules non conformes Excel sans MF conditionnelle ni formule ?
Comment créer un tableau de bord commercial intelligent avec Excel ?
Comment créer un graphique dessiné à la main avec Excel ?
Comment créer un graphique de distribution en 5 minutes chrono ?
L’astuce Excel pour imprimer le tableau que vous voulez en un clic !
Comment gérer ses comptes sur Excel avec le « Suivi du budget familial »
Comment créer facilement des QR codes sans VBA avec Excel ?
Transformez vos données en graphique tornade interactif facilement sur Excel !
Comment créer un graphique Iceberg dans Excel ?

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2024 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.