Fonction SOUS.TOTAL Excel

La fonction SOUS.TOTAL Excel est très pratique lorsque vous travaillez avec des tableaux contenant des filtres.
Elle permet de faire des calculs comme une somme, une moyenne ou un comptage, tout en s’adaptant automatiquement aux données affichées.

C’est une fonction souvent utilisée dans les tableaux de suivi, les reportings ou les tableaux de bord simples.
Elle aide à obtenir des résultats fiables, même lorsque le tableau est filtré.La fonction SOUS.TOTAL Excel permet de renvoyer un agrégat dans une base de données.

---

---

La fonction SOUS.TOTAL dans Excel est moins pertinente aujourd'hui depuis l'apparition de la fonction AGREGAT. En effet, AGREGAT pallie les limites que tu peux rencontrer avec SOUS.TOTAL.

Avis de notre formateur : Nicolas Parent

La fonction SOUS.TOTAL Excel fonctionne avec deux éléments principaux. Vous indiquez d’abord le type de calcul à effectuer. Puis vous précisez la plage de données concernée.

Selon le numéro de fonction utilisé, Excel peut ignorer ou non les lignes masquées manuellement. Ce point est important à connaître pour éviter les surprises dans vos résultats.

• Si Réf1, [Réf1], [...] contiennent des sous-totaux, alors ceux-ci sont ignorés pour éviter tout comptage en double.
• SOUS.TOTAL ignore les lignes masquées après un filtre, peu importe le No_fonction. Elle prend en compte uniquement les lignes masquées manuellement.
• SOUS.TOTAL est surtout adaptéepour les colonnes, elle offrira moins de possibilité si elle est utilisée avec des lignes de données.

Ce tableau présente les ventes et le nombre de clients de deux magasins.

D’abord, tu veux calculer la somme des ventes (cellule B2) dont le calcul est dynamique en fonction du filtre appliqué (voir segment en haut à gauche).

Puis, tu veux renvoyer la moyenne des ventes (cellule C2) dont le calcul est dynamique en fonction du filtre appliqué.

• Tu peux insérer un segment uniquement si tes données sont mises sous forme de tableau.
• (1) 9correspond à la fonction SOMME.
• (2) 1correspond à la fonction MOYENNE.
• SOUS.TOTAL ignore les lignes masquées après un filtre, peu importe le No_fonction. Elle prend en compte uniquement les lignes masquées manuellement. AGREGATest plus complète, car tu peux aller plus loin dans les options à prendre en compte dans le calcul.
• Pour calculer des sous-totauxà l’intérieur du tableau, tu peux aller dans « Données», puis cliquer sur « Sous-total».

Le fichier d’exercice ci-dessous vous permet de tester SOUS.TOTAL sur un tableau filtré.
Vous verrez comment le résultat change automatiquement en fonction des filtres appliqués.

C’est le meilleur moyen de comprendre rapidement le comportement de la fonction.

Pratique	Explication
Utilisation appropriée des types d'opération	Choisissez judicieusement le type d'opération approprié en fonction de ce que vous souhaitez accomplir. Les types d'opération vont de 1 à 11 et représentent différentes fonctions telles que la somme, la moyenne, le maximum, le minimum, etc. Assurez-vous de sélectionner le type d'opération correspondant à votre objectif pour obtenir des résultats précis.
Sélection de la plage de référence adéquate	Assurez-vous de sélectionner la plage de référence correcte pour la fonction SOUS.TOTAL. Cette plage peut inclure des cellules individuelles, des plages de cellules ou des références de feuille de calcul. Veillez à ce que la plage de référence contienne les données pertinentes pour le calcul que vous souhaitez effectuer, et excluez toute donnée non pertinente qui pourrait fausser les résultats.
Utilisation des options d'ignorance des valeurs masquées	Lorsque vous utilisez la fonction SOUS.TOTAL sur une plage de données filtrées, utilisez les options d'ignorance des valeurs masquées pour exclure les valeurs filtrées de votre calcul. Cela garantit que seules les valeurs visibles sont prises en compte dans le calcul, ce qui peut être essentiel pour obtenir des résultats précis lorsque vous travaillez avec des données filtrées.
Préserver les sous-totaux lors de l'insertion ou de la suppression de lignes	Lorsque vous utilisez la fonction SOUS.TOTAL pour calculer des totaux dans des listes de données, assurez-vous de préserver les sous-totaux en cas d'insertion ou de suppression de lignes. Vous pouvez le faire en utilisant les fonctions de tableau structuré ou en veillant à ce que vos références de plage incluent des en-têtes de colonnes pour que les sous-totaux soient automatiquement mis à jour lorsque vous modifiez la structure de vos données.
Validation des résultats	Après avoir appliqué la fonction SOUS.TOTAL, assurez-vous de valider les résultats pour garantir leur exactitude. Comparez les totaux obtenus avec d'autres méthodes de calcul ou vérifiez visuellement les données incluses dans la plage de référence pour vous assurer que le total correspond à vos attentes. En cas de doute, n'hésitez pas à vérifier et à corriger toute erreur éventuelle.

Problème	Explication
#VALEUR!	Cette erreur se produit lorsque la référence fournie en argument n'est pas valide ou lorsque la fonction SOUS.TOTAL est utilisée avec un type d'opération incorrect. Par exemple, si le type d'opération spécifié n'est pas compris entre 1 et 11, ou si la plage de référence contient des cellules vides ou des données non numériques, Excel renverra l'erreur #VALEUR!.
#NOMBRE!	L'erreur #NOMBRE! survient généralement lorsque la fonction SOUS.TOTAL est utilisée avec une plage de référence contenant des valeurs non numériques. Par exemple, si des cellules de texte ou des cellules vides sont incluses dans la plage de référence, Excel ne peut pas effectuer le calcul et renverra cette erreur. Assurez-vous donc que la plage de référence utilisée avec la fonction SOUS.TOTAL contient uniquement des valeurs numériques valides.
Référence incorrecte	Lorsque la référence utilisée avec la fonction SOUS.TOTAL est incorrecte, Excel renverra l'erreur #REF!. Cela peut se produire si la référence pointe vers une plage de cellules qui a été supprimée ou déplacée, ou si la référence contient une erreur de saisie, comme une faute de frappe dans le nom de la feuille ou de la cellule.
Opération non valide	Si le type d'opération spécifié avec la fonction SOUS.TOTAL n'est pas valide, Excel renverra l'erreur #VALEUR!. Les types d'opération valides pour la fonction SOUS.TOTAL vont de 1 à 11, chaque numéro représentant une opération spécifique (par exemple, la somme, la moyenne, le maximum, le minimum, etc.). Assurez-vous de spécifier un type d'opération valide pour obtenir le résultat souhaité.

Ces problèmes courants peuvent survenir lors de l'utilisation de la fonction SOUS.TOTAL dans Excel. Il est essentiel de comprendre ces erreurs et de les éviter en veillant à utiliser correctement les arguments de la fonction et à fournir des données valides en entrée.

Quelle est la différence entre SOUS.TOTAL et AGREGAT ?

Contrairement à AGREGAT, la fonction SOUS.TOTAL prend en compte uniquement les lignes masquées manuellement. AGREGAT offre plus de flexibilité dans les options à prendre en compte dans le calcul.

Quel est l'équivalent de SOUS.TOTAL en anglais ?

L'équivalent de AGREGAT en anglais est la fonction SUBTOTAL.

Quels sont les types d'opération disponibles avec la fonction SOUS.TOTAL ?

Les types d'opération disponibles avec la fonction SOUS.TOTAL vont de 1 à 11, chacun représentant une opération spécifique. Par exemple, le type d'opération 1 représente la somme, le type d'opération 2 représente la moyenne, le type d'opération 3 représente le nombre de valeurs, etc. Référez-vous à la documentation d'Excel pour obtenir la liste complète des types d'opération disponibles.

La fonction SOUS.TOTAL prend-elle en compte les valeurs filtrées ou cachées ?

Oui, la fonction SOUS.TOTAL peut prendre en compte ou ignorer les valeurs filtrées ou cachées, en fonction de l'option sélectionnée. En utilisant les options d'ignorance des valeurs masquées (en spécifiant un nombre entre 1 et 11 avec un "10" ou un "100" ajouté), vous pouvez exclure les valeurs filtrées ou cachées de votre calcul, garantissant ainsi des résultats précis lorsque vous travaillez avec des données filtrées.

Puis-je utiliser la fonction SOUS.TOTAL avec des données structurées ou des tableaux Excel ?

Oui, la fonction SOUS.TOTAL peut être utilisée avec des données structurées ou des tableaux Excel. En utilisant des références de tableau structuré, vous pouvez garantir que les sous-totaux sont automatiquement mis à jour lorsque vous modifiez la structure de vos données, notamment lors de l'insertion ou de la suppression de lignes.

Comment puis-je vérifier les résultats de la fonction SOUS.TOTAL ?

Vous pouvez vérifier les résultats de la fonction SOUS.TOTAL en comparant les totaux obtenus avec d'autres méthodes de calcul ou en vérifiant visuellement les données incluses dans la plage de référence. Assurez-vous que les résultats correspondent à vos attentes et que toutes les valeurs pertinentes sont prises en compte dans le calcul.

Aller plus loin

Découvrez toutes les fonctions sur Excel

Ou optez pour notre livre Excel complet à ce sujet

Progressez sur Excel avec Morpheus Formation

La fonction SOUS.TOTAL fait partie des bases à maîtriser pour travailler efficacement avec des tableaux filtrés.
Elle est souvent l’une des premières fonctions utilisées dans des fichiers professionnels.

Dans notre formation Excel, nous vous aidons à comprendre quand utiliser SOUS.TOTAL et quand passer à AGREGAT.
La formation est sur mesure, adaptée à votre niveau, à vos besoins et à vos fichiers, avec un accompagnement humain et concret.