SOUS-TOTAL Formule dans Excel (Table des matières)

  • Formule de sous-total dans Excel
  • Comment utiliser la formule SUBTOTAL dans Excel?

Formule de sous-total dans Excel

Parfois, nous devons trouver le sous-total d'une catégorie à partir d'un grand ensemble de données qui a plusieurs catégories. Dans ce genre de situation, la fonction de sous-total nous aidera à trouver la catégorie totale sage. Non seulement le sous-total, nous pouvons calculer la moyenne, le nombre et le max et bien d'autres encore.

Définition

La fonction qui renvoie le sous-total d'une liste ou d'une base de données peut être définie comme fonction de sous-total. Le sous-total est une fonction spéciale par rapport à d'autres fonctions Excel car les autres fonctions Excel n'effectueront qu'une seule opération spécifique, mais la spécialité de la fonction de sous-total est qu'elle effectue non seulement le calcul du sous-total, mais également que plusieurs opérations arithmétiques et logiques dépendent du numéro de la fonction.

Syntaxe de la formule SUBTOTAL

Arguments:

Numéro de fonction : il fait référence au type d'opération mathématique que nous allons effectuer sur une plage de données spécifiée.

Ref1, Ref 2: Il s'agit de la plage de cellules.

Il existe plusieurs numéros de fonction disponibles, mais pas de problème, nous n'avons pas besoin de nous souvenir de tous ces numéros de fonction car, lors de l'utilisation de la formule SUBTOTAL, Excel vous montrera automatiquement la liste des numéros de fonction disponibles comme ci-dessous.

Vous devez vous demander pourquoi il existe deux numéros de fonction pour la même fonction.

  • 1 - 11 numéros de fonction doivent être utilisés lorsque nous voulons ignorer les cellules filtrées et considérer les cellules masquées manuellement.
  • Les numéros de fonction 101 à 111 doivent être utilisés lorsque nous voulons ignorer toutes les cellules masquées qui sont filtrées et les cellules masquées manuellement.

Nous verrons quelques exemples pour comprendre comment la fonction de sous-total fonctionnera pour différents numéros de fonction.

Comment utiliser la formule SUBTOTAL dans Excel?

La formule SUBTOTAL dans Excel est très simple et facile. Voyons comment utiliser la formule SUBTOTAL dans Excel avec quelques exemples.

Vous pouvez télécharger ce modèle de formule Excel de sous-total ici - modèle de formule Excel de sous-total

Exemple 1

Considérons un petit tableau qui contient des données de différentes catégories comme ci-dessous.

Si vous observez le tableau ci-dessus, nous avons la catégorie de produit, la catégorie de couleur et la quantité.

Appliquez la formule SOUS-TOTAL comme ci-dessous avec le numéro de fonction 9.

Après avoir appliqué cette formule, le résultat est le suivant.

Appliquez la formule SOUS-TOTAL comme ci-dessous avec le numéro de fonction 109.

Après avoir appliqué la formule SUBTOTAL, le résultat est indiqué ci-dessous.

Dans la formule, nous avons utilisé à la fois les fonctions numéro 9 et 109 pour effectuer SUM dans deux colonnes différentes. C2: C9 est la plage de données que nous effectuons des calculs.

Maintenant, la somme totale est de 385 pour les deux formules. Masquez quelques lignes et observez les résultats pour les deux formules.

Après avoir appliqué la formule SUBTOTAL, le résultat est indiqué ci-dessous.

Les lignes de 50 à 60 sont masquées, donc les résultats de la fonction numéro 109 sont passés à 255 car elle ne prend pas en compte les données masquées manuellement tandis que la fonction numéro 9 au total reste la même, ce qui signifie qu'elle prendra en compte les données même si nous masquons les données manuellement .

Appliquer un filtre aux données et filtrer une seule couleur, ici nous sommes sélectionnés la couleur noire.

Après avoir appliqué la formule SUBTOTAL, le résultat est le suivant.

Appliquez la formule SOUS-TOTAL comme ci-dessous avec la fonction numéro 109.

Après avoir appliqué la formule SUBTOTAL, le résultat est le suivant.

Si nous observons la capture d'écran ci-dessus, les numéros de fonction n'ont pas pris en compte la quantité de données filtrées. Après avoir filtré les données et masqué les lignes, les deux formules donneront les mêmes résultats que les deux formules ne prendront pas en compte les données non visibles.

Après avoir appliqué la formule SUBTOTAL, le résultat est le suivant.

Appliquez la formule SOUS-TOTAL comme ci-dessous avec la fonction numéro 109.

Après avoir appliqué cette formule, le résultat est le suivant.

Exemple # 2

Jusqu'à présent, nous avons vu l'opération SUM, maintenant nous allons effectuer l'opération MOYENNE avec la même plage de données. 1 et 101 sont les numéros de fonction pour effectuer la moyenne. Parcourez le tableau ci-dessus pour les détails des numéros de fonction.

Après avoir appliqué la formule SUBTOTAL, le résultat est le suivant.

Pour la cellule suivante, appliquez à nouveau la formule SUBTOTAL avec le numéro de fonction 109.

Après avoir appliqué la formule, le résultat est le suivant.

Avec la même formule SUBTOTAL, nous pouvons également calculer la moyenne avec le seul changement de numéro de fonction.

Après avoir appliqué la formule SUBTOTAL, le résultat est le suivant.

Pour la cellule suivante, appliquez à nouveau la formule SUBTOTAL avec la fonction numéro 101.

Après avoir appliqué la formule SUBTOTAL, le résultat indiqué ci-dessous.

Cachez quelques lignes, observez maintenant les changements dans la moyenne. La moyenne avec la fonction numéro 1 reste la même que toujours, elle considère également la quantité cachée. La moyenne avec le numéro de fonction 101 a changé car elle ignore les données masquées manuellement. Comme cela, vous pouvez essayer le reste des numéros de fonction et vérifier la fonctionnalité de chacun car le concept de base est le même, ils fonctionneront également de la même manière.

Exemple # 3

Le sous-total présente un avantage supplémentaire qu'il ne prendra pas en compte si des sous-totaux sont disponibles dans la plage de données. Nous verrons un exemple pour mieux comprendre. Formules d'entrée pour effectuer l'opération SUM en fonction du produit comme A, B, C et D. Il n'est pas obligatoire que ce soit en fonction du produit, vous pouvez également le faire par couleur.

Après avoir appliqué la formule SUBTOTAL, le résultat est le suivant.

Utilisez à nouveau la formule SUBTOTAL pour vérifier la somme de B.

Après avoir appliqué la formule SUBTOTAL, le résultat est le suivant.

Utilisez à nouveau la formule SUBTOTAL pour vérifier la somme de C.

Après avoir appliqué la formule SUBTOTAL, le résultat est le suivant.

Utilisez à nouveau la formule SUBTOTAL pour vérifier la somme de D.

Après avoir appliqué la formule SUBTOTAL, le résultat est le suivant.

Utilisez à nouveau la formule SUBTOTAL dans la cellule suivante.

Après avoir appliqué la formule SUBTOTAL, le résultat est le suivant.

Utilisez à nouveau la formule SUBTOTAL dans la cellule suivante.

Après avoir appliqué la formule SUBTOTAL, le résultat indiqué ci-dessous.

Si nous observons les captures d'écran ci-dessus, il y a une somme des sous-produits et lors de l'exécution de la somme totale, nous avons inclus les totaux des catégories dans la somme totale (cellules de 36 à 48) mais il ne considérera pas que les sous-totaux (cellules de 44 à 47 ). Ainsi, nous pouvons calculer les sous-totaux entre la plage de données sans affecter la valeur de somme totale.

J'espère que vous comprenez comment utiliser le sous-total et son utilisation.

Choses à retenir sur la formule SUBTOTAL dans Excel

  • Bien que donner le numéro de fonction ne donne pas le numéro qui est hors de la plage de numéros de fonction car il n'est pas prédéfini par Excel, nous obtiendrons un message d'erreur. Nous devons toujours donner le nombre compris entre 1 et 11 et 101 et 111, sinon il affichera le message d'erreur #value.
  • Lors de l'utilisation de l'opération de division, n'oubliez pas qu'aucun nombre ne doit être divisible par zéro. Cela signifie que x / 0 est un format incorrect.
  • Si vous appliquez le sous-total pour les données horizontales provenant de A1: F1 et que le masquage de colonnes n'affectera pas le sous-total.
  • Lors de l'application du sous-total si des cellules ne contiennent pas de données ou des données non numériques, la fonction ignorera ces cellules.
  • Utilisez les numéros de fonction en comprenant la fonctionnalité et utilisez-les sinon vous risquez de ne pas obtenir les résultats corrects que vous attendez.

Articles recommandés

Cela a été un guide pour la formule SUBTOTAL dans Excel. Ici, nous discutons Comment utiliser la formule SUBTOTAL dans Excel avec des exemples pratiques et un modèle Excel téléchargeable. Vous pouvez également consulter nos autres articles suggérés-

  1. Comment utiliser la formule IRR Excel?
  2. TRIM gauche dans Excel (formule, exemples)
  3. Tutoriel RECHERCHEV dans Excel
  4. Formule ISNUMBER Excel

Catégorie:

Conseils Excel