Fonction Excel SUBTOTAL (Table des matières)
- SOUS-TOTAL dans Excel
- Formule de sous-total dans Excel
- Comment utiliser la fonction SUBTOTAL dans Excel?
SOUS-TOTAL dans Excel
- Une fonction de sous-total est une fonction intégrée dans Excel qui est classée sous la fonction math / trig
- Le sous-total ne totalisera pas seulement les nombres dans une plage définie de cellules, il aidera en effectuant différentes opérations arithmétiques
- SUBTOTAL est polyvalent. Vous pouvez l'utiliser pour faire la moyenne, résumer, compter un tas de cellules et d'autres calculs
Définition
Renvoie un sous-total dans une base de données ou une liste, c'est-à-dire pour connaître le sous-total d'une plage de cellules donnée. Il effectue un calcul spécifié pour un ensemble de valeurs donné.
Formule de sous-total dans Excel
La formule de la fonction SOUS-TOTAL est la suivante:
SOUS-TOTAL (numéro_fonction, ref1, (ref2), …) Où
Function_num ou code d'opération: il indique quelle fonction doit être utilisée pour les calculs des sous-totaux dans une liste donnée ou c'est un type d'opération mathématique que vous aimez effectuer
Un argument function_num est appliqué sur la base des deux critères ou méthodes mentionnés ci-dessous
- Inclure les valeurs masquées Lorsque l'argument function_num est compris entre 1 et 11, la fonction SUBTOTAL inclut les valeurs masquées.
- Ignorer les valeurs masquées Lorsque l'argument function_num est compris entre 101-111, la fonction SUBTOTAL exclut ou ignore les valeurs masquées. Il trouvera des valeurs que seules les lignes invisibles ignorent les valeurs cachées dans le calcul.
| Fun_num Comprend des valeurs cachées | Fun_num Exclut les valeurs cachées | Une fonction | La description | |||
| 1 | 101 | MOYENNE | Il calcule la moyenne d'une plage spécifiée. | |||
| 2 | 102 | COMPTER | Il comptera le nombre de cellules contenant des valeurs numériques. | |||
| 3 | 103 | COUNTA | Il compte les cellules non vides dans la plage spécifiée. | |||
| 4 | 104 | MAX | Il trouvera la plus grande valeur dans la plage spécifiée. | |||
| 5 | 105 | MIN | Il trouvera la plus petite valeur dans la plage spécifiée. | |||
| 6 | 106 | PRODUIT | Il calcule le produit des cellules dans la plage spécifiée. | |||
| sept | 107 | STDEV | Il estime l'écart type dans la plage spécifiée. | |||
| 8 | 108 | STDEVP | Il calcule l'écart type sur la base de l'ensemble de la population. | |||
| 9 | 109 | SOMME | Il calcule la somme de la plage spécifiée. | |||
| dix | 110 | VAR | Il estime la variance dans la plage spécifiée. | |||
| 11 | 111 | VARP | Il estime la variance en fonction de l'ensemble de la population. |
ref1, (ref2): Une ou plusieurs références à des cellules contenant les valeurs pour lesquelles le calcul doit être effectué pour le sous-total.
Comment utiliser la fonction SUBTOTAL dans Excel?
Voyons comment fonctionne la fonction SUBTOTAL dans Excel.
Vous pouvez télécharger ce modèle Excel de fonction SUBTOTAL ici - Modèle Excel de fonction SUBTOTALExemple # 1 - Formule SUBTOTAL pour les valeurs masquées à l'aide du filtre automatique Excel ou des lignes masquées par un filtre
Dans l'exemple mentionné ci-dessous, le tableau contient les données de ventes trimestrielles, où les trimestres individuels dans la colonne E et les données de ventes dans la colonne F.
La fonction somme est appliquée dans la cellule «F16», c'est-à-dire = SUM (F4: F15).
Il renvoie ou donne la valeur 1184 .
La fonction SUM renvoie la même valeur. c'est-à-dire 1184, au lieu de 194 pour les données du T4, si les lignes sont masquées par un filtre (capture d'écran ci-dessous).
Si je veux uniquement la valeur SUM correcte des données du quatrième trimestre, je dois utiliser la fonction SUBTOTAL.
Avant d'appliquer la fonction SUBTOTAL, sous la barre d'outils de données, appliquez un filtre pour la ligne d'en-tête.
Maintenant, calculons spécifiquement les données de ventes Q1 ou Q2 ou Q3 ou Q4 individuellement pour les données filtrées.
Utilisez 109 comme argument function_num dans la fonction SUBTOTAL pour résumer les données. soit = SOUS-TOTAL (109, C17: C28)
La fonction SUBTOTAL ignore les lignes masquées par un filtre et calcule le résultat correct.
Ici, la valeur du sous-total change dynamiquement en fonction du filtre, elle trouvera une somme de valeurs dans les lignes visibles uniquement; les lignes cachées sont négligées par la fonction SUBTOTAL. (Ci-dessous mentionné par exemple)
Q4
Q3
Q2
Q1
Exemple # 2 - Fonction SUBTOTAL pour les lignes masquées manuellement
Dans la feuille Excel, les lignes n ° 15, 16, 19 et 20 ont été masquées à l'aide de l'option de formatage des lignes, en mettant ces lignes en surbrillance, en cliquant avec le bouton droit de la souris et en sélectionnant Masquer.
Une fonction de sous-total est utilisée dans la cellule C23 du tableur, pour calculer la somme des cellules visibles.
Nous devons utiliser 109 comme argument function_num dans la fonction SUBTOTAL pour résumer les données.
soit = SOUS-TOTAL (109, C14: C21)
Ici, la fonction SUBTOTAL ignore manuellement les lignes masquées et calcule le résultat correct.
Il trouvera une somme de valeurs uniquement dans les lignes visibles; les lignes cachées sont négligées par la fonction SUBTOTAL
Exemple # 3 - Fonction SUBTOTAL pour la valeur normale
Comme toutes les cellules de la fourchette des chiffres des ventes trimestrielles sont visibles, aucune d'entre elles n'est filtrée ou les lignes sont masquées, elle inclut toutes les valeurs.
Dans l'exemple mentionné ci-dessous, plusieurs fonctions de sous-total sont appliquées.
Dans la cellule, la fonction SUBTOTAL C14, C15 & C16 est appliquée.
Pour calculer la valeur SUM, la fonction numéro 9 ou 109 est utilisée dans une fonction de sous-total où elle calcule la somme de la plage spécifiée, c'est-à-dire = SOUS-TOTAL (9, C4: C11)
Il nous donnera la somme de toutes les valeurs en C4: C11, à condition qu'aucune ne soit filtrée et que les lignes ne soient cachées dans cette plage spécifiée.
Pour calculer la valeur MIN, la fonction numéro 5 ou 105 est utilisée dans une fonction de sous-total où elle trouvera la plus petite valeur dans la plage spécifiée. Dans une cellule, C15, une fonction SOUS-TOTAL est appliquée, c'est-à-dire = SOUS-TOTAL (5, C4: C11)
Il trouvera la plus petite valeur dans la plage spécifiée, c'est-à-dire C4: C11
Pour calculer la valeur MAX, la fonction numéro 4 ou 104 est utilisée dans une fonction de sous-total où elle trouvera la plus grande valeur dans la plage spécifiée. Dans une cellule, C16, une fonction SUBTOTAL est appliquée, c'est-à-dire = SUBTOTAL (4, C4: C11)
Il trouvera la plus grande valeur dans la plage spécifiée, à savoir C4: C11.
Choses à retenir sur la fonction SOUS-TOTAL
- Une fonction SUBTOTAL peut être appliquée pour trouver une somme de valeurs filtrées.
- Une fonction SUBTOTAL est utilisée pour ignorer les valeurs des lignes masquées.
- La valeur de l'argument fonction _ num ne doit pas être supérieure à 11 ou inférieure à 1, de même ne doit pas être supérieure à 111 ou inférieure à 101, sinon, elle donnera #VALUE! Erreur.
- Une fonction SOUS-TOTAL est applicable uniquement pour la valeur numérique.
- Si des sous-totaux sont présents dans la plage SUBTOTAL, ils seront négligés, c.-à-d. Si des formules SUBTOTAL déjà existantes sont présentes dans la plage d'entrée d'une autre formule SUBTOTAL, alors ces valeurs sont négligées par la fonction de sous-total pendant le calcul, afin d'éviter un double comptage & il empêche l'erreur de double comptage.
- Une fonction SUBTOTAL peut être utilisée pour résumer dynamiquement les données.
- Les cellules vides et les cellules contenant des valeurs non numériques sont ignorées par la fonction de sous-total lors du calcul.
Articles recommandés
Cela a été un guide pour SUBTOTAL. Nous discutons ici de la formule SUBTOTAL et de la façon d'utiliser la fonction SUBTOTAL avec un exemple Excel et des modèles Excel téléchargeables. Vous pouvez également consulter ces fonctions utiles dans Excel -
- Fonction RECHERCHE dans Excel
- Fonction FIND dans Excel
- PAS dans Excel
- TRANSPOSE dans Excel