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 des 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.
Contenu
Syntaxe de la fonction de sous-total
Utilisez la fonction SOUS-TOTAL dans Excel pour résumer les valeurs dans une feuille de calcul de différentes manières. Cette fonction 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 : SOUS-TOTAL(fonction_num,ref1,ref2,…)
Le fonction_num est nécessaire et précise le type d’opération mathématique à utiliser pour le sous-total. La fonction SUBTOTAL peut ajouter des nombres, calculer la valeur moyenne de nombres sélectionnés, trouver les valeurs maximales et minimales dans une plage, compter le nombre de valeurs dans une plage sélectionnée, et plus encore.
La fonction SUBTOTAL 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 de la question de savoir si vous voulez inclure des lignes cachées dans le résultat ou exclure des lignes cachées du résultat. Ces lignes peuvent être cachées manuellement ou par un filtre.
Les arguments function_num comprennent :
| Fonction Tâche | fonction_num | fonction_num |
|---|---|---|
| (y compris les valeurs cachées) | (exclut les valeurs cachées) | |
| MOYENNE | 1 | 101 |
| COMPTER | 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 cachées lorsqu’on utilise la commande Hide pour cacher des lignes. Lorsque la commande Filter est utilisée, les calculs SUBTOTAL n’incluent pas les résultats des filtres cachés.
Le ref1 l’argument est nécessaire. 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.
Le ref2,… Les arguments sont facultatifs. Il s’agit de cellules supplémentaires qui sont incluses dans le calcul.
Utilisez 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 Arguments de fonction. Pour illustrer la manière 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 lignes visibles et dans les lignes visibles et cachées.
Utiliser la fonction SOUS-TOTAL pour 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 nombre de lignes visibles.
- Dans la barre de fonctions, entrez =SUBTOTAL. Au fur et à mesure que vous tapez, Excel vous propose une fonction. Double-cliquez sur l’icône SOUS-TOTAL fonction.
Pour utiliser la boîte de dialogue Arguments de fonction pour entrer dans la fonction SOUS-TOTAL, allez à Formules et sélectionnez Math & Trig > SOUS-TOTAL.
- Dans le menu déroulant qui apparaît, double-cliquez sur le 102 – COMPTE argument function_num.
- Tapez une virgule (,).
- Dans la feuille de travail, sélectionnez les cellules à inclure dans la formule.
- Presse Saisissez pour voir le résultat dans la cellule que vous avez sélectionnée à l’étape 2.
- Sélectionnez la cellule qui contiendra le nombre de lignes visibles et cachées.
- Dans la barre de fonctions, entrez =SUBTOTAL. Au fur et à mesure que vous tapez, Excel vous propose une fonction. Double-cliquez sur l’icône SOUS-TOTAL fonction.
- Dans le menu déroulant qui apparaît, double-cliquez sur le 2 – COMPTE function_num, puis tapez une virgule (,).
- Dans la feuille de calcul, sélectionnez les cellules à inclure dans la formule, puis appuyez sur Saisissez.
- Cachez plusieurs rangées de données. Dans cet exemple, les rangées dont le chiffre d’affaires est inférieur à 100 000 $ ont été masquées.
Utiliser la fonction SOUS-TOTAL avec des données filtrées
L’utilisation de la fonction SUBTOTAL sur les données filtrées ignore les données des lignes qui ont été supprimées par le filtre. Chaque fois que le critère de filtrage change, la fonction recalcule pour afficher le sous-total des lignes visibles.
Utiliser la fonction SOUS-TOTAL pour voir les différences dans les résultats des calculs tout en filtrant les données :
- Créer des formules SOUS-TOTALES. Par exemple, créez des formules pour déterminer le sous-total et les valeurs moyennes des données filtrées.
Peu importe que vous utilisiez l’argument function_num pour les lignes visibles ou cachées. Les deux arguments donnent le même résultat pour les données filtrées.
- Sélectionnez n’importe quelle cellule de l’ensemble de données.
- Aller à Accueil puis sélectionnez Trier et filtrer > Filtrez.
- 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 regroupées, il est possible d’appliquer la fonction SOUS-TOTAL à chaque groupe individuel, puis de calculer le grand total pour l’ensemble des données.
- Sélectionnez n’importe quelle cellule de l’ensemble de données.
- Sélectionnez Données > Sous-total pour ouvrir le Sous-total boîte de dialogue.
- Sélectionnez le A chaque changement de et choisissez le groupe auquel chaque sous-total sera calculé.
- Sélectionnez le Fonction d’utilisation et choisissez une fonction_num.
- Dans le Ajouter le sous-total à sélectionnez la colonne dans laquelle la formule sera appliquée.
- Sélectionnez OK.
- 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.
- Pour modifier le function_num, mettez en évidence une cellule de l’ensemble de données et sélectionnez Données > Sous-total. Ensuite, faites vos choix dans le Sous-total boîte de dialogue.
