Excel Fonction subtotale avec des exemples de formule

Le tutoriel explique les spécificités de la fonction sous-totale dans Excel et montre comment utiliser des formules sous-totales pour résumer les données dans les cellules visibles.

Dans l'article précédent, nous avons discuté d'un moyen automatique d'insérer des sous-totaux dans Excel en utilisant la fonctionnalité sous-totale. Aujourd'hui, vous apprendrez à écrire par vous-même des formules subtotales et quels avantages cela vous donne.

• Fonction subtotale dans Excel - Syntaxe et utilisations
• Top 3 des raisons d'utiliser la fonction subtotale dans Excel
• Exemples de formule sous-totale
• Excel subtotal ne fonctionne pas

Fonction sous-totale Excel - Syntaxe et utilisations

Microsoft définit le sous-total Excel comme la fonction qui renvoie un sous-total dans une liste ou une base de données. Dans ce contexte, le "subtotal" ne totalise pas seulement les nombres dans une gamme définie de cellules. Contrairement à d'autres fonctions Excel qui sont conçues pour ne faire qu'une seule chose spécifique, le sous-total est incroyablement polyvalent - il peut effectuer des opérations arithmétiques et logiques différentes telles que le comptage des cellules, le calcul de la moyenne, la recherche de la valeur minimale ou maximale, et plus encore.

La fonction subtotale est disponible dans toutes les versions d'Excel 2016, Excel 2013, Excel 2010, Excel 2007 et Lower.

La syntaxe de la fonction sous-totale Excel est la suivante:

Subtotal (function_num, ref1, [Ref2],…)

Où:

• Function_num - un nombre qui spécifie la fonction à utiliser pour le sous-total.
• REF1, REF2,… - une ou plusieurs cellules ou gammes au sous-total. Le premier argument REF est requis, d'autres (jusqu'à 254) sont facultatifs.

L'argument fonction_num peut appartenir à l'un des ensembles suivants:

• 1 - 11 Ignorez les cellules filtrées, mais incluent des rangées cachées manuellement.
• 101 - 111 Ignorez toutes les cellules cachées - filtrées et cachées manuellement.

Function_num		Fonction	Description
1	101	MOYENNE	Renvoie la moyenne des nombres.
2	102	COMPTER	Compte les cellules qui contiennent des valeurs numériques.
3	103	Comte	Compte les cellules non vides.
4	104	Max	Renvoie la valeur la plus importante.
5	105	Min	Renvoie la plus petite valeur.
6	106	PRODUIT	Calcule le produit des cellules.
7	107	Stdev	Renvoie l'écart type d'une population en fonction d'un échantillon de nombres.
8	108	Stdevp	Renvoie l'écart-type basé sur une population entière de nombres.
9	109	SOMME	Additionne les nombres.
10	110	Var	Estime la variance d'une population basée sur un échantillon de nombres.
11	111	Varp	Estime la variance d'une population basée sur une population entière de nombres.

En fait, il n'est pas nécessaire de mémoriser tous les numéros de fonction. Dès que vous commencez à taper une formule sous-totale dans une cellule ou dans la barre de formule, Microsoft Excel affichera une liste des numéros de fonction disponibles pour vous.

Par exemple, c'est ainsi que vous pouvez faire une formule sous-totale 9 pour résumer les valeurs dans les cellules C2 à C8:

Pour ajouter un numéro de fonction à la formule, double-cliquez dessus, puis tapez une virgule, spécifiez une plage, tapez la parenthèse de fermeture et appuyez sur Entrée. La formule terminée ressemblera à ceci:

=SUBTOTAL(9,C2:C8)

De la même manière, vous pouvez écrire une formule sous-totale 1 pour obtenir une moyenne, sous-totale 2 pour compter les cellules avec des nombres, le sous-total 3 pour compter les non-blanks, etc. La capture d'écran suivante montre quelques autres formules en action:

Note. Lorsque vous utilisez une formule sous-totale avec une fonction récapitulative comme la somme ou la moyenne, il calcule uniquement des cellules avec des nombres ignorant les blancs et les cellules contenant des valeurs non nucères.

Maintenant que vous savez comment créer une formule sous-totale dans Excel, la question principale est - pourquoi voudrait-on prendre la peine de l'apprendre? Pourquoi ne pas simplement utiliser une fonction régulière comme la somme, le compte, le maximum, etc.? Vous trouverez la réponse juste ci-dessous.

Les 3 principales raisons d'utiliser le sous-total dans Excel

Par rapport aux fonctions Excel traditionnelles, Subtotal vous donne les avantages importants suivants.

1. Calculer les valeurs dans les lignes filtrées

Étant donné que la fonction sous-totale Excel ignore les valeurs dans les lignes filtrées, vous pouvez l'utiliser pour créer un résumé de données dynamique où les valeurs sous-totales sont ré-calculées automatiquement en fonction du filtre.

Par exemple, si nous filtrons le tableau pour montrer les ventes uniquement pour la région de l'Est, la formule sous-totale s'ajustera automatiquement afin que toutes les autres régions soient supprimées du total:

Note. Étant donné que les deux ensembles de numéros de fonction (1-11 et 101-111) ignorent les cellules filtrées, vous pouvez utiliser la formule sous-totale 9 ou sous-totale 109 dans ce cas.

2. Calculer uniquement les cellules visibles

Si vous vous en souvenez, les formules subtotales avec fonction_num 101 à 111 ignorent toutes les cellules cachées - filtrées et cachées manuellement. Ainsi, lorsque vous utilisez la fonction de masquer d'Excel pour supprimer les données non pertinentes de la vue, utilisez le numéro de fonction 101-111 pour exclure les valeurs dans les lignes cachées des sous-totaux.

L'exemple suivant vous aidera à mieux comprendre son fonctionnement: sous-total 9 vs sous-total 109.

3. Ignorer les valeurs dans les formules sous-totales imbriquées

Si la plage fournie à votre formule sous-totale Excel contient toutes les autres formules sous-totales, ces sous-totaux imbriqués seront ignorés, de sorte que les mêmes nombres ne seront pas calculés deux fois. Génial, n'est-ce pas?

Dans la capture d'écran ci-dessous, la grande formule moyenne SUBTOTAL(1, C2:C10) ignore les résultats des formules subtotales dans les cellules C3 et C10, comme si vous utilisiez une formule moyenne avec 2 plages distinctes moyennes AVERAGE(C2:C5, C7:C9) .

Utilisation d'exemples de sous-total dans Excel - Formule

Lorsque vous rencontrez le sous-total pour la première fois, cela peut sembler complexe, délicat et même inutile. Mais une fois que vous aurez atteint des punaises en laiton, vous vous rendrez compte que ce n'est pas si difficile à maîtriser. Les exemples suivants vous montreront quelques conseils utiles et des idées inspirantes.

Exemple 1. Sous-total 9 vs sous-total 109

Comme vous le savez déjà, Excel Subtotal accepte 2 ensembles de numéros de fonctions: 1-11 et 101-111. Les deux ensembles ignorent les lignes filtrées, mais les chiffres 1-11 incluent les lignes manuellement cachées tandis que 101-111 les excluent. Pour mieux comprendre la différence, considérons l'exemple suivant.

Pour totalement les lignes filtrées , vous pouvez utiliser une formule sous-totale 9 ou sous-totale 109 comme indiqué dans la capture d'écran ci-dessous:

Mais si vous avez caché des éléments non pertinents manuellement en utilisant la commande Hide Rows sur l'onglet Home > Group des cellules > Format > Masquer et un peu , ou en cliquant avec le bouton droit sur les lignes, puis en cliquant sur Masquer , et maintenant vous voulez totalement les valeurs uniquement dans les lignes visibles, le sous-Total 109 est la seule option:

D'autres numéros de fonction fonctionnent de la même manière. Par exemple, pour compter les cellules filtrées non verbales , la formule sous-totale 3 ou sous-totale 103 fera l'affaire. Mais seul le sous-total 103 peut compter correctement les non-blanks visibles s'il y a des lignes cachées dans la gamme:

Note. La fonction sous-totale Excel avec function_num 101-111 néglige les valeurs dans les lignes cachées, mais pas dans les colonnes cachées . Par exemple, si vous utilisez une formule comme SUBTOTAL(109, A1:E1) pour résumer les nombres dans une plage horizontale, la cachette d'une colonne n'affectera pas le sous-total.

Exemple 2. Si subtotal pour résumer dynamiquement les données

Si vous créez un rapport de résumé ou un tableau de bord où vous devez afficher divers résumé de données mais que vous n'avez pas d'espace pour tout, l'approche suivante pourrait être une solution:

• Dans une cellule, faites une liste déroulante contenant les noms de fonctions tels que Total, Max, Min, etc.
• Dans une cellule à côté de la liste déroulante, entrez une formule imbriquée avec les fonctions sous-totales intégrées correspondant aux noms de fonction de la liste déroulante.

Par exemple, en supposant que les valeurs sous-totales sont dans les cellules C2: C16, et la liste déroulante en A17 contient des éléments totaux , moyens , max et min , la formule sous-totale "dynamique" est la suivante:

=IF(A17="total", SUBTOTAL(9,C2:C16), IF(A17="average", SUBTOTAL(1,C2:C16), IF(A17="min", SUBTOTAL(5,C2:C16), IF(A17="max", SUBTOTAL(4,C2:C16),""))))

Et maintenant, selon la fonction que votre utilisateur sélectionne dans la liste déroulante, la fonction sous-totale correspondante calculera les valeurs dans les lignes filtrées:

Conseil. Si tout d'un coup, la liste déroulante et la cellule de formule disparaissent de votre feuille de calcul, assurez-vous de les sélectionner dans la liste des filtres.

Excel subtotal ne fonctionne pas - erreurs courantes

Si votre formule sous-totale renvoie une erreur, il est probable que ce soit pour l'une des raisons suivantes:

#VALUE! - L'argument Function_num est autre qu'un entier entre 1 - 11 ou 101 - 111; ou l'un des arguments de référence contient une référence 3D.

#DIV/0! - se produit si une fonction de résumé spécifiée doit effectuer une division par zéro (par exemple, calculant un écart moyen ou type pour une gamme de cellules qui ne contient pas une seule valeur numérique).

#NAME? - Le nom de la fonction sous-totale est mal orthographié - l'erreur plus facile à corriger :)

Conseil. Si vous ne vous sentez pas encore à l'aise avec la fonction sous-totale, vous pouvez utiliser la fonctionnalité sous-totale intégrée et faire insérer les formules pour vous automatiquement.

C'est comment utiliser les formules subtotales dans Excel pour calculer les données dans les cellules visibles. Pour rendre les exemples plus faciles à suivre, vous êtes invités à télécharger notre classeur d'échantillons ci-dessous. Merci d'avoir lu!

Classeur de pratique

Exemples de formule sous-totale Excel (fichier .xlsx)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!