Comment utiliser la fonction de sous-total d’Excel

Lorsque votre feuille de calcul Excel contient des lignes cachées, des données filtrées ou des données groupées, utilisez la fonction SOUS-TOTAL d’Excel. La fonction SOUS-TOTAL peut inclure ou exclure les valeurs cachées dans les calculs. En plus de trouver le total d’un groupe de données, Excel peut calculer la moyenne, le maximum, le minimum, l’écart type et la variance de vos données.

Voici comment insérer des sous-totaux dans Excel.

Les instructions de cet article s’appliquent à Excel pour Microsoft 365, Excel 2019 et Excel 2016.
Syntaxe de la fonction SOUS-TOTAL

Utilisez la fonction SOUS-TOTAL dans Excel pour résumer les valeurs d’une feuille de calcul de différentes manières. Elle est particulièrement utile lorsque votre feuille de calcul contient des lignes cachées que vous souhaitez inclure dans le calcul.

La syntaxe de la fonction SUBTOTAL est la suivante : SUBTOTAL(function_num,ref1,ref2,…)

7 FONCTIONS PRATIQUES DE MICROSOFT EXCEL

L’argument function_num est obligatoire et spécifie le type d’opération mathématique à utiliser pour le sous-total. La fonction SUBTOTAL permet d’additionner des nombres, de calculer la valeur moyenne de nombres sélectionnés, de trouver les valeurs maximale et minimale d’une plage, de compter le nombre de valeurs dans une plage sélectionnée, etc.

La fonction SOUS-TOTAL ignore les cellules qui ne contiennent pas de données et les cellules dont les valeurs ne sont pas numériques.

Cet argument est un nombre et dépend du fait que vous souhaitiez inclure les lignes cachées dans le résultat ou exclure les lignes cachées du résultat. Ces lignes peuvent être masquées manuellement ou par un filtre.

L’argument ref1 est obligatoire. Il s’agit des cellules utilisées pour calculer les résultats de l’argument function_num sélectionné. Cet argument peut être une valeur, une seule cellule ou une plage de cellules.

Les arguments ref2,… sont facultatifs. Il s’agit de cellules supplémentaires qui sont incluses dans le calcul.

Utiliser la fonction SOUS-TOTAL avec des lignes cachées

Les fonctions Excel peuvent être saisies manuellement ou à l’aide de la boîte de dialogue Function Arguments. Pour illustrer la façon de saisir la fonction manuellement à l’aide de la barre de formule, l’exemple suivant utilise l’argument COUNT function_num pour compter le nombre de valeurs dans les rangées visibles et dans les rangées visibles et cachées.

Pour utiliser la fonction SUBTOTAL afin de compter le nombre de lignes dans une feuille de calcul :

Commencez par une feuille de calcul qui contient plusieurs lignes de données.

Sélectionnez la cellule qui contiendra le compte des lignes visibles.

Dans la barre de fonctions, saisissez =SUBTOTAL. À mesure que vous tapez, Excel suggère une fonction. Double-cliquez sur la fonction SUBTOTAL.

Pour utiliser la boîte de dialogue Arguments de fonction afin de saisir la fonction SOUS-TOTAL, allez dans Formules et sélectionnez Math & Trig > SOUS-TOTAL.

Dans le menu déroulant qui apparaît, double-cliquez sur l'argument 102 - COUNT function_num.

Tapez une virgule (,).

Dans la feuille de calcul, sélectionnez les cellules à inclure dans la formule.

Appuyez sur la touche Entrée pour voir le résultat dans la cellule que vous avez sélectionnée à l'étape 2.

Sélectionnez la cellule qui contiendra le compte des lignes visibles et cachées.

Dans la barre de fonctions, entrez =SUBTOTAL. Au fur et à mesure que vous tapez, Excel vous suggère une fonction. Double-cliquez sur la fonction SUBTOTAL.

Dans le menu déroulant qui apparaît, double-cliquez sur l'argument 2 - COUNT function_num, puis tapez une virgule (,).

Dans la feuille de calcul, sélectionnez les cellules à inclure dans la formule, puis appuyez sur Entrée.

Masquez plusieurs lignes de données. Dans cet exemple, les lignes contenant uniquement des ventes inférieures à 100 000 $ ont été masquées.

Utilisation de la fonction SOUS-TOTAL avec des données filtrées

L’utilisation de la fonction SUBTOTAL sur des données filtrées ignore les données des lignes qui ont été supprimées par le filtre. Chaque fois que les critères de filtrage changent, la fonction recalcule pour afficher le sous-total des lignes visibles.

Pour utiliser la fonction SUBTOTAL afin de voir les différences dans les résultats de calcul tout en filtrant les données :

Créez des formules SUBTOTAL. Par exemple, créez des formules pour déterminer le sous-total et les valeurs moyennes des données filtrées.

Il importe peu que vous utilisiez l'argument function_num pour les lignes visibles ou cachées. Les deux arguments fournissent le même résultat dans les données filtrées.

Sélectionnez n'importe quelle cellule dans l'ensemble de données.

Accédez à Accueil, puis sélectionnez Trier et filtrer > Filtrer.

Utilisez les flèches déroulantes pour filtrer les données de la feuille de calcul.

Remarquez comment les valeurs changent chaque fois que vous sélectionnez différents critères de filtrage.

Utiliser la fonction SOUS-TOTAL avec des données groupées

Lorsque les données sont groupées, il est possible d’appliquer la fonction SOUS-TOTAL à chaque groupe individuel, puis de calculer le total général de l’ensemble des données.

Sélectionnez n'importe quelle cellule dans l'ensemble de données.

Sélectionnez Données > Sous-total pour ouvrir la boîte de dialogue Sous-total.

Sélectionnez la flèche déroulante At each change in et choisissez le groupe pour lequel chaque sous-total sera calculé.

Sélectionnez la flèche déroulante Utiliser la fonction et choisissez un numéro de fonction.

Dans la liste Ajouter le sous-total à, sélectionnez la colonne à laquelle la formule sera appliquée.

Sélectionnez OK.

Des sous-totaux sont insérés pour chaque groupe de données, et un total général est inséré au bas de l'ensemble de données.

Pour modifier le numéro de fonction, mettez en surbrillance n'importe quelle cellule de l'ensemble de données et sélectionnez Données > Sous-total. Ensuite, faites vos choix dans la boîte de dialogue Sous-total.