Formule sous-total d’Excel : comment ça marche ?

excel formule sous-total

Les tableaux Excel contiennent parfois de grandes quantités de données avec des calculs complexes. Concernant la mise en forme de votre tableau, vous avez masqué certaines plages de cellules de votre aperçu ? Pas de soucis : utilisez la fonction sous-total Excel pour comptabiliser les valeurs filtrées ! Pour que vos calculs réagissent aux filtres et cellules masquées, c’est la fonctionnalité idéale. Alors, comment utiliser la fonction SOUS.TOTAL Excel ? On vous explique tout dans cet article !

Pourquoi utiliser la formule sous-total ?

Si vous êtes amené à travailler sur de gros volumes de données, vous n’avez pas toujours besoin d’en afficher toutes les plages. Masquer certaines lignes ou filtrer certaines valeurs permet souvent de faciliter la compréhension de tableaux complets et simplifier la manipulation de données essentielles. Les valeurs masquées ne sont toutefois pas perdues : elles font toujours partie de votre classeur et peuvent être utilisées pour calculer le sous-total de vos cellules.

Avec la formule sous-total Excel, pas besoin de créer un nouveau tableau et de transférer vos données. C’est une solution alternative que vous pouvez configurer pour prendre en compte ou non les cellules masquées.

SOUS.TOTAL regroupe onze fonctions : vous devez sélectionner le mode de calcul à appliquer à la fonction et décider d’intégrer ou non les cellules masquées. À savoir que toutes les valeurs temporairement masquées par un filtre ne seront pas prises en compte dans les calculs de la fonction. Obtenir le sous-total peut être fait sous forme de résultat supplémentaire en plus du résultat final (pour comparer les deux valeurs obtenues si besoin).

Syntaxe de la fonction sous-total Excel

La formule sous-total Excel s’écrit de la manière suivante : =SOUS.TOTAL(no_fonction,réf1,[réf2],…).

Les arguments de la fonction SOUS.TOTAL sont les suivants :

  • no_fonction : argument obligatoire. Il s’agit du nombre 1 à 11 ou 101 à 111 qui spécifie la fonction à utiliser pour calculer le sous-total. Les chiffres 1 à 11 intègrent les lignes masquées manuellement, tandis que les nombres 101 à 111 les excluent (comme précisé, les cellules filtrées sont toujours exclues).
  • réf1 : argument obligatoire. C’est la première référence ou plage nommée dont vous souhaitez calculer le sous-total.
  • réf2;… : arguments facultatifs. Ce sont les plages ou références nommées 2 à 254 dont vous souhaitez calculer le sous-total.

 

Function_num (inclus les lignes masquées) | Function_num (ignore les lignes masquées) |Fonction
1 |  101 | MOYENNE
2 | 102 | NB
3| 103 | NBVAL
4| 104| MAX
5| 105 | MIN
6| 106 | PRODUIT
7| 107 | ECARTYPE
8| 108 | ECARTYPEP
9| 109| SOMME
10| 110 | VAR
11| 111 | VAR.P

Formule sous-total Excel : les règles à respecter

Pour utiliser la commande sous-total correctement, votre base de données doit respecter certaines règles :

  • Pas ligne vide : il ne faut pas qu’il y ait de ligne vide dans votre base. Sinon, vous allez automatiquement scinder votre tableau en plusieurs parties et toutes les données ne seront pas analysées. Le résultat de la formule sous-total Excel sera alors incorrect.
  • Faire une copie de la base de données : avant de l’utiliser pour calculer vos sous-totaux, faites une copie de votre base. En effet, une fois que le sous-total est obtenu, vous ne pourrez pas trier vos données (sinon vous perdriez vos calculs).
  • Un seul titre par colonne et une seule ligne d’en-tête : Excel considérera la première ligne du tableau comme celle qui contient les en-têtes des colonnes. Vous devez donc faire en sorte que chaque colonne possède un titre unique.
  • Éviter d’avoir plusieurs types de données dans la même colonne : ne conservez que des chiffres dans des colonnes qui devraient contenir des chiffres pour éviter les calculs erronés.
  • Pas de cellules fusionnées : évitez les cellules fusionnées si vous désirez exploiter toutes les données de votre base. Dans le cas contraire, la fusion de cellules bloque la lecture de vos données par Microsoft Excel.

Processus du formule SOUS.TOTAL Excel

Prenons un exemple : vous avez un tableau avec des produits, les régions où ils ont été vendus, le prix unitaire des produits, le nombre d’unités vendues et les chiffres d’affaires. Vous souhaitez calculer le total de CA et des unités vendues par produit :

  • triez les données de la colonne « Produits » de A à Z pour regrouper les éléments de la même catégorie ;
  • cliquez sur une cellule à l’intérieur de votre tableau ;
  • allez sous l’onglet “Données” et dans le groupe “Plan” ;
  • choisissez “Sous-total” ;
  • dans la zone “À chaque changement de”, choisissez “Produits” dans le menu déroulant ;
  • optez pour la fonction “Somme” ;
  • dans le champ “Ajouter un sous-total à”, cochez les cases “Unité” et “CA” ;
  • vous obtenez votre résultat final.

Notre formation en ligne pour maîtriser Excel comme un pro

La formule Excel sous-total avec filtre est très utile au quotidien, et ce n’est pas la seule fonctionnalité d’Excel qui vous permet de gagner du temps et d’être plus productif ! Le tableau contient de nombreuses fonctions pour mieux exploiter ses données. Et grâce à notre formation Excel éligible au CPF, vous maîtriserez bientôt ces fonctionnalités pour maximiser votre employabilité ou décrocher une promotion !

Découvrez notre programme de formation en e-learning. Il est accessible à tous, et ce peu importe votre niveau initial. Nous vous proposons un apprentissage sur-mesure pour que vous puissiez progresser à votre rythme. Vous choisissez vos horaires, et nous pouvons également travailler sur vos tableaux professionnels pour une mise en application immédiate de vos nouvelles compétences.

Notre formation Excel à distance est éligible au CPF : utilisez votre droit à la formation et profitez d’un reste à charge de 0 €. Notre formation alterne entre cours particuliers avec nos professeurs experts en bureautique et modules d’apprentissages autonomes. Formule sous-total Excel, filtre automatique Excel, tableaux croisés dynamiques, listes déroulantes de choix : développez des compétences 100 % opérationnelles pour concrétiser vos objectifs professionnels comme personnels. Passez l’une des certifications internationales (ENI, TOSA ou ICDL) en fin de formation pour obtenir une attestation reconnue dans le monde entier.

 

Ne ratez aucune de nos actualités. Inscrivez-vous à notre newsletter.

En cliquant sur “Envoyer”, j’accepte et je comprend comment Proformation traite mes données personnelles comme indiqué dans la Politique de Confidentialité.

Proformation Proformation
Merci pour votre visite, malheureusement ce site n'est pas supporté par ce navigateur.

Rendez-vous sur un autre navigateur pour découvrir Proformation (Edge, Chrome, Firefox...)