Lorsque votre feuille de calcul Excel contient des lignes masqué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 des valeurs masqué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. Cela est particulièrement utile lorsque votre feuille de calcul contient des lignes masquées que vous souhaitez inclure dans le calcul.
La syntaxe de la fonction SUBTOTAL est: SUBTOTAL(function_num, ref1, ref2, …)
L'argument function_num est obligatoire et spécifie le type d'opération mathématique à utiliser pour le sous-total. La fonction SOUS-TOTAL peut additionner des nombres, calculer la valeur moyenne des nombres sélectionnés, trouver les valeurs maximales et minimales dans une plage, 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 avec des valeurs non numériques.
Cet argument est un nombre et dépend si vous souhaitez inclure des lignes masquées dans le résultat ou exclure des lignes masquées du résultat. Ces lignes peuvent être masquées manuellement ou masquées par un filtre.
Les arguments function_num incluent:
| Tâche de fonction | function_num | function_num |
|---|---|---|
| (inclut les valeurs masquées) | (exclut les valeurs masquées) | |
| MOYENNE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUIT | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SOMME | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
Les arguments de référence function_num 1 à 11 n'incluent que les valeurs des lignes masquées lors de l'utilisation de la commande Masquer pour masquer les lignes. Lors de l'utilisation de la commande Filtrer, les calculs SOUS-TOTAL n'incluent pas les résultats de filtre masqués.
L'argument ref1 est obligatoire. Ce sont les 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. Ce sont des cellules supplémentaires qui sont incluses dans le calcul.
Utiliser la fonction SOUS-TOTAL avec des lignes masquées
Les fonctions Excel peuvent être saisies manuellement ou à l'aide de la boîte de dialogue Arguments de la fonction. Pour illustrer comment saisir manuellement la fonction à l'aide de la barre de formule, l'exemple suivant utilise l'argument COUNT function_num pour compter le nombre de valeurs dans les lignes visibles et dans les lignes visibles et masquées.
Pour utiliser la fonction SOUS-TOTAL pour compter le nombre de lignes dans une feuille de calcul:
-
Commencez avec une feuille de calcul contenant plusieurs lignes de données.
- Sélectionnez la cellule qui contiendra le nombre de lignes visibles.
-
Dans la barre de fonctions, saisissez =SUBTOTAL. Au fur et à mesure que vous tapez, Excel suggère une fonction. Double-cliquez sur la fonction SUBTOTAL.
Pour utiliser la boîte de dialogue Arguments de la fonction pour entrer dans la fonction SOUS-TOTAL, accédez à Formules et sélectionnez Math & Trig >SOUS-TOTAL.
Image -
Dans le menu déroulant qui apparaît, double-cliquez sur l'argument 102 – COUNT function_num.
Image -
Tapez une virgule (,).
Image -
Dans la feuille de calcul, sélectionnez les cellules à inclure dans la formule.
Image -
Appuyez sur Entrée pour voir le résultat dans la cellule que vous avez sélectionnée à l'étape 2.
Image - Sélectionnez la cellule qui contiendra le nombre de lignes visibles et masquées.
- Dans la barre de fonctions, saisissez =SUBTOTAL. Au fur et à mesure que vous tapez, Excel 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.
Image -
Masquer plusieurs lignes de données. Dans cet exemple, les lignes contenant uniquement des ventes inférieures à 100 000 $ ont été masquées.
Image
Utiliser la fonction SOUS-TOTAL avec des données filtrées
L'utilisation de la fonction SOUS-TOTAL 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 filtre changent, la fonction recalcule pour afficher le sous-total des lignes visibles.
Pour utiliser la fonction SOUS-TOTAL pour voir les différences dans les résultats de calcul lors du filtrage des données:
-
Créer des formules SUBTOTAL. Par exemple, créez des formules pour déterminer les valeurs de sous-total et moyennes des données filtrées.
Peu importe si vous utilisez l'argument function_num pour les lignes visibles ou masquées. Les deux arguments fournissent le même résultat dans les données filtrées.
Image - Sélectionnez n'importe quelle cellule dans l'ensemble de données.
-
Allez à Home, puis sélectionnez Sort & Filter > Filter.
Image -
Utilisez les flèches déroulantes pour filtrer les données de la feuille de calcul.
Image -
Remarquez comment les valeurs changent chaque fois que vous sélectionnez différents critères de filtre.
Image
Utiliser la fonction SOUS-TOTAL avec des données groupées
Lorsque les données sont regroupées, il existe un moyen d'appliquer la fonction SOUS-TOTAL à chaque groupe individuel, puis de calculer le total général pour l'ensemble de données.
- Sélectionnez n'importe quelle cellule dans l'ensemble de données.
-
Sélectionnez Data > Subtotal pour ouvrir la boîte de dialogue Subtotal.
Image - Sélectionnez la flèche déroulante À chaque modification de et choisissez le groupe auquel chaque sous-total sera calculé.
- Sélectionnez la flèche déroulante Utiliser la fonction et choisissez une fonction_num.
- Dans la liste Add subtotal to, sélectionnez la colonne dans laquelle la formule sera appliquée.
-
Sélectionner OK.
Image -
Des sous-totaux sont insérés pour chaque groupe de données, et un grand total est inséré au bas de l'ensemble de données.
Image - Pour modifier le function_num, mettez en surbrillance n'importe quelle cellule de l'ensemble de données et sélectionnez Data > Subtotal. Ensuite, faites vos choix dans la boîte de dialogue Sous-total.
