Fonction SOUS-TOTAL : Formule et comment utiliser la fonction SOUS-TOTAL dans Excel

Qu'est-ce que la fonction SOUS-TOTAL dans Excel ? Qu'est-ce que la formule SOUS-TOTAL dans Excel ? Découvrons-le avec LuckyTemplates.com !

Microsoft Excel possède de nombreuses fonctions utilisées pour calculer des totaux, et SUBTOTAL en fait partie. Cependant, Subtotal est différent de la fonction SUM. Ce n'est pas une somme normale. Au lieu de cela, il est utilisé pour calculer des sous-totaux dans des listes ou des bases de données, calculer des moyennes, etc.

Contenu principal sur la fonction SOUS-TOTAL

• Qu'est-ce que la fonction SOUS-TOTAL et à quoi sert-elle ?
• Formule SOUS-TOTAL dans Excel
• Comment utiliser la fonction SOUS-TOTAL dans Excel
  • 1. Calculez le total des lignes filtrées 
  • 2. Comptez les cellules filtrées qui ne sont pas vides
  • 3. Ignorer les valeurs dans les formules de sous-total imbriquées
• Points à considérer avant d'utiliser la fonction SOUS-TOTAL dans Excel
• Comment se tromper lors de l'exécution de la fonction SOUS-TOTAL dans Excel

Qu'est-ce que la fonction SOUS-TOTAL et à quoi sert-elle ?

À première vue, vous vous demanderez peut-être : à quoi sert la fonction SOUS-TOTAL ? Après tout, il est facile de trouver un sous-total à partir d'une plage de cellules à l'aide de fonctions existantes telles que SUMIF ou AVERAGE .

S'il est vrai que vous n'avez pas nécessairement besoin d'utiliser SUBTOTAL, cela facilite grandement l'analyse des données ciblées. Vous pouvez insérer une formule utilisant SOUS-TOTAL dans une plage de cellules sans affecter le total global, car SOUS-TOTAL ignore les autres cellules contenant une formule SOUS-TOTAL.

SUBTOTAL fonctionne avec d'autres fonctionnalités d'Excel, telles que le filtrage des cellules. Si vous filtrez un tableau par valeur, la formule SOUS-TOTAL se met à jour, reconnaît le filtre et exclut les cellules correspondantes. SUBTOTAL est également utile pour ignorer les valeurs cachées, ce que d'autres fonctions (comme SUM) ne peuvent pas faire.

Heureusement, vous pouvez utiliser d'autres fonctions dans le cadre d'une formule SOUS-TOTAL. Il existe 11 méthodes de sous-total différentes disponibles, chacune adaptée à une fonction Excel existante. Si vous souhaitez une formule SOMME qui fonctionne avec des cellules filtrées et ignore les autres formules SOUS-TOTAL, il est préférable d'utiliser SOUS-TOTAL, bien qu'un tableau croisé dynamique puisse fonctionner de la même manière.

Formule SOUS-TOTAL dans Excel

La fonction SOUS-TOTAL fonctionne en calculant la valeur totale d'une plage de cellules, basée sur une autre fonction mathématique. SUBTOTAL utilise jusqu'à 11 fonctions mathématiques différentes (de AVERAGE à VAR.P) pour trouver la valeur totale appropriée, tout en travaillant dans les paramètres de la fonction (c'est-à-dire en ignorant les autres formules SUBTOTAL).

La fonction SUBTOTAL a la syntaxe =SUBTOTAL(function_num, ref1, [ref2],…) . Là-dedans :

• Function_num : Les nombres de 1 à 11 et de 101 à 111 précisent quelle fonction sera utilisée pour calculer en SOUS-TOTAL.
• Ref1, Ref2, ... : 1 ou plusieurs cellules, ou plage de cellules pour calculer les sous-totaux, maximum 254.

Note:

• La fonction SOUS-TOTAL est conçue pour calculer des colonnes verticales de données.
• Si les arguments ref1, ref2,... contiennent la fonction SOUS-TOTAL, ils seront ignorés pour éviter une double comptabilisation.
• Si function_num est compris entre 1 et 11, la fonction SOUS-TOTAL calcule en incluant les valeurs masquées dans l'ensemble de données car la ligne contenant cette plage est masquée. Si function_num est compris entre 101 et 111, la fonction SOUS-TOTAL calcule uniquement les valeurs non masquées dans l'ensemble de données (sans compter les valeurs dans les lignes masquées).
• Pour les zones de données masquées par Filtre, SOUS-TOTAL sera ignoré.

Comment utiliser la fonction SOUS-TOTAL dans Excel

1. Calculez le total des lignes filtrées 

Pour mieux comprendre ce type de somme, veuillez vous référer à l'article Calculer la somme des valeurs de liste filtrées dans Excel . Fondamentalement, la fonction SOUS-TOTAL dans ce cas sera :

=SOUS-TOTAL(9,_plage)

pham_range voici la zone que vous souhaitez additionner après avoir filtré les données.

2. Comptez les cellules filtrées qui ne sont pas vides

Nous utiliserons SOUS-TOTAL 3 ou SOUS-TOTAL 103. Cependant, s'il y a des lignes masquées, vous devez utiliser SOUS-TOTAL 103 pour compter avec précision les cellules visibles non vides.

Par exemple, le tableau de données ci-dessus masquera 2 lignes 4 et 5. Lorsque vous utilisez SOUS-TOTAL 3 ou SOUS-TOTAL 103, cela produira 2 résultats différents.

Nous entrons la formule, puis Excel affichera automatiquement l'ensemble de fonctions parmi lequel vous pourrez choisir sans avoir à vous en souvenir.

Le résultat lors de l'utilisation de SOUS-TOTAL 3 sera 3, y compris les cellules masquées dans la ligne.

Quant au SOUS-TOTAL 103, il affichera uniquement les cellules non vides que nous voyons, en ignorant les lignes masquées.

3. Ignorer les valeurs dans les formules de sous-total imbriquées

Par exemple, nous calculerons la moyenne du total des kilogrammes de tissu dans les entrepôts A1 et A2.

La formule moyenne pour l'entrepôt A2= SUBTOTAL(1,C2:C4) et donne le résultat 19.

La formule moyenne pour l'entrepôt A1 = SUBTOTAL(1,C5:C7) donne le résultat 38.

Cependant, lors du calcul de la moyenne du nombre total de tissus dans 2 entrepôts, les résultats moyens dans les 2 entrepôts seront ignorés. Nous avons la formule =SOUS-TOTAL(1,C2:C9) et le résultat supprimera automatiquement le résultat moyen précédemment calculé.

Points à considérer avant d'utiliser la fonction SOUS-TOTAL dans Excel

Bien que SOUS-TOTAL présente des avantages, vous devez prendre en compte certains éléments avant de commencer à utiliser cette fonction, notamment les suivants :

• Comme mentionné, l'utilisation de 1-11 pour l'argument function_num garantira que SUBTOTAL inclut les valeurs masquées, tandis que 101-111 les ignore.
• L’utilisation d’une valeur autre que 1-11 ou 101-111 entraînera le renvoi par Excel de l’erreur #VALEUR !. Cela se produira également pour les références de cellules 3D (où la même cellule, sur plusieurs feuilles, est référencée dans une plage).
• Lorsque des plages de cellules horizontales telles que A1:D1 sont utilisées, les valeurs masquées sont automatiquement incluses (quelle que soit la valeur de l'argument function_num). Il s'agit d'une limitation de la fonction SOUS-TOTAL et ne peut être surmontée.
• L'utilisation de SUBTOTAL sur des données filtrées garantira que les valeurs cachées seront toujours ignorées, quel que soit l'argument function_num utilisé.
• SUBTOTAL peut être utilisé comme fonction imbriquée dans le cadre d'autres fonctions, y compris des formules contenant une fonction IF.
• Si une autre formule SOUS-TOTAL se trouve dans la plage de données ref1, SOUS-TOTAL l'ignore et l'exclut du calcul global.
• Excel permet d'utiliser jusqu'à 254 plages de cellules dans une formule SOUS-TOTAL, bien qu'une seule plage de cellules soit nécessaire pour que la formule fonctionne.

Comment se tromper lors de l'exécution de la fonction SOUS-TOTAL dans Excel

Lorsque vous calculez la fonction SOUS-TOTAL dans Excel, vous rencontrerez souvent des erreurs basiques :

• #VALEUR ! : L'identifiant de la fonction n'est pas compris entre 1 et 11 ou entre 101 et 111 ou a une référence 3D.
• #DIV/0! : se produit lorsqu'une somme spécifique doit être divisée par 0 (par exemple, calcul de la moyenne ou de l'écart type d'une plage de cellules qui ne contient pas de valeurs numériques).
• #NOM? Le nom de la fonction SOUS-TOTAL est mal orthographié.

Quelques conseils sur la façon d'utiliser la fonction SOUS-TOTAL dans Microsoft Excel que vous ne connaissez peut-être pas

Astuce 1 : Disons que vous souhaitez vous assurer que toutes les couleurs de t-shirts sont disponibles dans toutes les tailles dans l'un ou l'autre entrepôt. Vous pouvez suivre ces étapes :

Étape 1 : Cliquez sur Sous-total . N'oubliez pas que cette étape consiste à ajouter davantage de critères aux données de sous-total existantes.

Étape 2 : Sélectionnez COUNT dans le menu déroulant et Taille dans le champ « Ajouter un champ de sous-total à ». Ensuite, décochez Remplacer les sous-totaux actuels . Lorsque vous cliquez sur OK , vous recevrez le tableau de données suivant :

Cela permet de garantir que vous disposez d'un nombre suffisant de dimensions différentes et que vous pouvez classer les données de manière à ne pas nécessiter d'opérations répétées.

Astuce 2 : Il est toujours utile de trier les données par colonne.

Astuce 3 : vous aide à inclure une étiquette dans la première ligne.

Astuce 4 : Si vous souhaitez résumer les données, décochez la case « Résumé sous les données lors de l'insertion du sous-total ». - Résumé ci-dessous les données lors de l'insertion de la fonction SOUS-TOTAL.

Bref, qu'est-ce que la fonction Sous-total dans Excel ?

Comme son nom l'indique, la fonction SOUS-TOTAL d'Excel est utilisée pour calculer des sous-totaux. La fonction Excel SUBTOTAL renvoie des résultats agrégés pour les valeurs fournies. SUBTOTAL peut calculer la somme, la moyenne, le nombre, le maximum… Cet Excel peut également inclure ou exclure des valeurs dans des lignes masquées.

• Objectif : Calculer le sous-total dans une liste ou une base de données.
• Valeur de retour : un nombre représentant un type de sous-total.
• Argument:
  • function_num- Un nombre qui spécifie la fonction à utiliser pour calculer les sous-totaux dans une liste.
  • ref1- Une plage nommée ou une référence à un sous-total.
  • ref2- [facultatif] Une plage nommée ou une référence à un sous-total.

L'utilisation de la fonction SOUS-TOTAL peut vous aider à analyser rapidement un ensemble de données sans recourir à des tableaux croisés dynamiques plus complexes. Si vous êtes bloqué, vous pouvez également utiliser la fonction Sous-total, trouvée dans l'onglet Données du ruban, pour créer automatiquement une formule SOUS-TOTAL que vous pouvez ensuite modifier ou copier ailleurs.

En savoir plus :

• Fonction SUMPRODUCT dans Excel : calculer le produit total des valeurs correspondantes
• Comment combiner 2 colonnes Nom complet et Prénom dans Excel sans perdre le contenu