Comment utiliser la fonction Excel SOUS-TOTAL -

Sommaire

La fonction Excel SOUS-TOTAL renvoie un résultat agrégé pour les valeurs fournies. SUBTOTAL peut renvoyer une fonction SUM, AVERAGE, COUNT, MAX et autres (voir le tableau ci-dessous), et SUBTOTAL peut inclure ou exclure des valeurs dans les lignes masquées.

Objectif

Obtenir un sous-total dans une liste ou une base de données

Valeur de retour

Un nombre représentant un type spécifique de sous-total

Syntaxe

= SOUS-TOTAL (no_fonction, ref1, (ref2),…)

Arguments

  • function_num - Un nombre qui spécifie la fonction à utiliser pour calculer les sous-totaux dans une liste. Voir le tableau ci-dessous pour la liste complète.
  • ref1 - Une plage nommée ou une référence au sous-total.
  • ref2 - (facultatif) Une plage nommée ou une référence au sous-total.

Version

Excel 2003

Notes d'utilisation

Utilisez la fonction SOUS-TOTAL pour obtenir un sous-total dans une liste ou une base de données. Malgré son nom, SUBTOTAL a la capacité d'exécuter une variété de fonctions mathématiques, y compris MOYENNE, NOMBRE, MAX, et bien d'autres (voir le tableau ci-dessous pour la liste complète). Par défaut, SUBTOTAL exclut les valeurs des lignes masquées par un filtre, ce qui rend SUBTOTAL très utile dans les tableaux Excel.

La fonction SUBTOTAL ignore automatiquement les autres formules SOUS-TOTAL qui existent dans les références pour éviter le double comptage.

Exemples

Vous trouverez ci-dessous quelques exemples de SOUS-TOTAL configuré sur SOMME, NOMBRE et MOYEN des valeurs d'une plage. Notez que la seule différence est la valeur utilisée pour l' argument function_num :

=SUBTOTAL(109,range) // SUM =SUBTOTAL(103,range) // COUNT =SUBTOTAL(101,range) // AVERAGE

Calculs disponibles

SUBTOTAL comportement est contrôlé par le Function_Num argument, qui est fourni en une valeur numérique. Il y a 11 fonctions disponibles, chacune avec deux options, comme indiqué dans le tableau ci-dessous. Remarquez que les valeurs sont «appariées» (par exemple 1-101, 2-102, 3-103, etc.). Cela est lié à la façon dont SUBTOTAL traite les lignes masquées manuellement. Lorsque function_num est compris entre 1 et 11, SUBTOTAL inclut les cellules qui ont été masquées manuellement. Lorsque function_num est compris entre 101-111, SUBTOTAL exclut les valeurs des lignes qui ont été masquées manuellement.

Fonction Inclure caché Ignorer caché
MOYENNE 1 101
COMPTER 2 102
COUNTA 3 103
MAX 4 104
MIN 5 105
PRODUIT 6 106
STDEV sept 107
STDEVP 8 108
SOMME 9 109
VAR dix 110
VARP 11 111

Remarque: SUBTOTAL ignore toujours les valeurs des cellules masquées par un filtre. Les valeurs des lignes qui ont été "filtrées" ne sont jamais incluses, quel que soit function_num .

SOUS-TOTAL dans les tableaux Excel

La fonction SOUS-TOTAL est utilisée lorsque vous affichez une ligne Total dans un tableau Excel. Excel insère automatiquement la fonction SOUS-TOTAL et vous pouvez utiliser un menu déroulant pour changer de comportement et afficher max, min, moyenne, etc. La raison pour laquelle Excel utilise SUBTOTAL pour les calculs dans la ligne Total d'un tableau Excel est parce que SOUS-TOTAL exclut automatiquement les lignes masqué par les contrôles de filtre en haut du tableau. Autrement dit, lorsque vous filtrez les lignes dans une table avec une ligne Total, vous verrez les calculs se mettre à jour automatiquement pour respecter le filtre.

SOUS-TOTAL avec contours

Excel dispose d'une fonctionnalité de sous-total qui insère automatiquement des formules SOUS-TOTAL dans les données triées. Vous pouvez trouver cette fonctionnalité dans Données> Plan> Sous-total. Les formules SOUS-TOTAL insérées de cette manière utilisent les numéros de fonction standard 1-11. Cela permet aux résultats du sous-total de rester visibles même lorsque les lignes sont masquées et affichées lorsque le plan est réduit et développé.

Remarque: bien que la fonction Plan soit un moyen "simple" d'insérer des sous-totaux dans un ensemble de données, un tableau croisé dynamique est un moyen meilleur et plus flexible pour analyser les données. De plus, un tableau croisé dynamique séparera les données de la présentation des données, ce qui est une bonne pratique.

Remarques

  • Lorsque function_num est compris entre 1 et 11, SUBTOTAL inclut les valeurs masquées
  • Lorsque function_num est compris entre 101-111, SUBTOTAL exclut les valeurs masquées
  • Dans les listes filtrées, SUBTOTAL ignore toujours les valeurs des lignes masquées, indépendamment de function_num .
  • SUBTOTAL ignore les autres formules SUBTOTAL qui existent dans les références pour éviter le double comptage.
  • SUBTOTAL est conçu pour fonctionner avec des valeurs de données verticales disposées verticalement. Dans les plages horizontales, les valeurs des colonnes masquées sont toujours incluses.

Vidéos connexes

Comment ajouter une ligne de totaux à un tableau Dans cette vidéo, nous verrons comment ajouter et configurer une ligne totale dans un tableau Excel. La ligne totale peut afficher automatiquement les calculs récapitulatifs sous un tableau. Comment compter les éléments dans une liste filtrée Dans cette vidéo, nous vous montrons comment ajouter un message en haut d'une liste filtrée qui affiche le total et les éléments visibles dans la liste. Formules pour interroger une table Les tables prenant en charge les références structurées, vous pouvez en apprendre beaucoup sur une table avec des formules simples. Dans cette vidéo, nous examinerons quelques formules que vous pouvez utiliser pour interroger une table.

Articles intéressants...