Fonction Excel SOUS-TOTAL pour des totalisations partielles

Cours et tutoriel excel / cours excel gratuit / tutoriel excel gratuit

Bienvenue d ans ce cours Excel qui vous montre comment utiliser la fonction excel sous-total pour effectuer des totalisations partielles.

La fonction Excel SOUS-TOTAL

Cette fonction SOUS-TOTAL vous permet d’effectuer des totalisations partielles pour des groupes spécifiques de données homogènes en renvoyant un résultat de sous-total partiel pour un groupe de valeurs faisant partie d’un ensemble plus vaste (par exemple une liste ou une base de données).

Le nom de la fonction exprime en fait partiellement le potentiel de l’expression de calcul car de nombreuses opérations peuvent être effectuées en plus d’une simple somme partielle.
:: La fonction SOUS-TOTAL est intéressante lorsque dans un tableau, seule une partie des données reste visible du fait de l’application d’un filtre. La Fonction SOUS-TOTAL permet donc de prendre en charge l’application de certaines Fonctions qui sous certaines conditions ne pourraient pas être appliquées.
:: À partir de la version 2010 d’Excel, la fonction AGREGATE a été introduite, qui peut être considérée comme une évolution de la fonction SOUS-TOTAL par rapport à laquelle le nombre de fonctions et d’options applicables est étendu. Les deux fonctions partagent de nombreux codes de réglage d’option. Pour plus d’informations sur cette fonction, voir : Appliquer la fonction AGGREGATE

Filtrer le tableau et calculer un SOUS-TOTAL des données

Comme le montre l’image ci-dessous, l’application d’un filtre au champ Secteur masque une partie des informations. La fonction SUM qui était précédemment définie pour toutes les valeurs ne modifie pas le résultat et compte également les salaires des lignes invisibles. En appliquant la fonction SOUS-TOTAL, il sera possible d’additionner uniquement les données affichées et d’ignorer les valeurs masquées par le filtre.

SOUS-TOTAL (Num_fonction ; Réf1 ; Réf2 ; Réf3 ……)
où : Function_Num est une valeur qui spécifie la fonction SUBTOTAL à utiliser. Le nombre peut être compris entre 1 et 11, (inclut les valeurs masquées dans le calcul) ou entre 101 et 111, (hors valeurs masquées).
Réf1 ; Réf2 ; Réf3 …. sont de 1 à 254 intervalles ou références de zone sur lesquels le SOUS-TOTAL doit être appliqué.

Dans l’image ci-dessus, la fonction SOUS-TOTAL utilise la valeur 9 qui correspond à l’opérateur SOMME.

Tableau des valeurs des fonctions utilisées par SUBTOTAL

Les fonctions utilisées par SUBTOTAL sont 11.
La valeur à attribuer au sein de la fonction va de 1 à 11 pour inclure les valeurs masquées et de 101 à 111 pour exclure les valeurs masquées.
:: NE confondez PAS la définition d’un FILTRE avec le masquage d’une partie de la feuille de calcul.
Les valeurs de 101 à 111 s’appliquent uniquement aux parties de la feuille qui ont été masquées (pas aux filtres).

Appliquer la fonction de sous-total aux données masquées

Dans l’exemple de l’image ci-dessous, les lignes 7 à 15 ont été masquées.
La définition de la fonction SOUS-TOTAL avec le code 9 produirait le même résultat que l’application de la fonction SOMME, car les valeurs contenues dans les lignes masquées seraient également comptées.
En utilisant le code 109 à la place, la valeur des cellules masquées n’est pas calculée (voir l’image ci-dessous).