Subtotal vs agrégat dans Microsoft Excel

Pendant de nombreuses années, j'ai utilisé la fonction subtotale dans Microsoft Excel pour créer des sous-totaux facilement visibles en haut de mes feuilles de calcul. Cependant, lorsque j'ai rencontré un agrégat, cela est devenu ma fonction de référence pour obtenir le même résultat mais avec plus de flexibilité.

Avant d'expliquer davantage, rappelons-nous comment fonctionne la fonction subtotale.

La fonction subtotale

La fonction sous-totale de Microsoft Excel est assez simple à utiliser. Il vous permet d'effectuer des calculs sous-totaux pour les plages de cellules, et vous pouvez décider si vous souhaitez inclure des lignes cachées manuellement dans le résultat.

Cependant, les lignes filtrées sont toujours exclues, et la fonction sous-totale ne fonctionne pas s'il y a des erreurs dans les données, sauf si vous la nichez dans la fonction IFERROR. De plus, Subtotal ne prend en charge que 11 fonctions, limitant son utilisation si vous souhaitez effectuer des calculs statistiques plus complexes.

Voici la syntaxe:

=SUBTOTAL(a,b,c)

où

• un (requis) est un nombre qui représente la fonction que vous souhaitez utiliser dans le calcul,
• b (requis) est la première gamme de cellules à sous-totale, et
• c (Facultatif) représente le premier de 252 gammes supplémentaires au sous-total, chacun séparé par une virgule.

Pour argument un1 ou 101 = moyenne, 2 ou 102 = comptage, 3 ou 103 = counta, 4 ou 104 = max, 5 ou 105 = min, 6 ou 106 = produit, 7 ou 107 = STDEV, 8 ou 108 = STDEV.P, 9 ou 109 = somme, 10 ou 110 = var, et 11 ou 111 = VARP.

Nombres 1 à 11 pour l'argument un Forcez le résultat à inclure des lignes cachées manuellement, tandis que 101 à 111 excluent les lignes cachées manuellement.

Dans cet exemple, la fonction sous-totale a été utilisée pour calculer la moyenne des valeurs de la colonne B, avec la ligne 6 (équipe E) cachée. Le sous-total de la cellule E1 comprend la ligne cachée (argument un = 1), tandis que le sous-total de la cellule E2 ne le fait pas (argument un = 101).

Cependant, lorsque le filtre est utilisé pour masquer le score de l'équipe E à la place, les deux sous-totaux sont les mêmes, car il n'y a aucun moyen d'obtenir le sous-total pour inclure des valeurs filtrées.

Dans cet dernier exemple, vous pouvez voir que la fonction sous-totale renvoie une erreur en cas d'erreur dans la plage.

Pour éviter ces problèmes, vous pouvez utiliser la fonction agrégée à la place.

La fonction agrégée

La fonction agrégée est similaire à la fonction sous-totale, mais il existe plus d'options pour le type d'agrégation que vous souhaitez créer. En outre, vous avez un choix plus large de choses que vous pouvez exclure du résultat, et vous pouvez faire en sorte que Excel ignore les erreurs dans vos données.

En d'autres termes, par rapport à la fonction subtotale, l'agrégat est plus puissant et flexible.

La fonction agrégée a deux syntaxes, une pour les références et une pour les tableaux, bien que vous n'ayez pas à vous soucier de celui que vous utilisez, car Excel sélectionne celui pertinent en fonction des arguments que vous entrez.

La syntaxe pour la forme de référence de la fonction agrégée est:

=AGGREGATE(a,b,c,d)

où

• un (requis) est un nombre qui représente la fonction que vous souhaitez utiliser dans le calcul,
• b (requis) est un nombre qui définit ce que vous voulez que le calcul ignore,
• c (requis) est la plage de cellules sur lesquelles la fonction sera appliquée, et
• d (Facultatif) est le premier de 252 arguments supplémentaires qui spécifient d'autres gammes.

Si vous travaillez avec des tableaux, la fonction agrégée suit une syntaxe légèrement différente:

=AGGREGATE(a,b,c,d)

où

• un (requis) est un nombre qui représente la fonction que vous souhaitez utiliser dans le calcul,
• b (requis) est un nombre qui définit ce que vous voulez que le calcul ignore,
• c (requis) est le tableau de valeurs sur lesquelles la fonction sera appliquée, et
• d est le deuxième argument requis par les fonctions de tableau comme les grands, petits, centiles, et autres.

La première différence à noter entre le sous-total et l'agrégat est que ce dernier a plus d'options d'argument un: 1 = moyenne, 2 = count, 3 = counta, 4 = max, 5 = min, 6 = produit, 7 = stdev.s, 8 = stdev.p, 9 = sum, 10 = var.s, 11 = var.p, 12 = médian, 13 = mode.sngl, 14 = grand, 15 = petit, 16 = centile.inc, 17 = quartilec, 18 = percentile.excc, et 19 = quartile.

Cependant, lorsque l'agrégat l'emporte vraiment sur le subtotal est dans sa capacité à exclure certaines valeurs du résultat (argument b):

Alors, voyons-le en action.

Dans cet exemple, taper:

=AGGREGATE(1,7,Team_Scores(Score))

dans la cellule E2 renvoie la moyenne (argument un = 1) des valeurs visibles de la colonne B, ignorant la valeur cachée dans la ligne 8 et l'erreur dans la ligne 7 (argument b = 7).

Vous ne pourriez pas obtenir le même résultat en utilisant la fonction sous-totale seule dans ce scénario, car elle ne peut pas négliger les erreurs sans être imbriqué dans une formule IFERROR.

Même si le contournement des erreurs est un bon moyen de rendre votre feuille de calcul en plus rangée, ne prenez pas l'habitude de les ignorer complètement! Ils sont là pour une raison et pourraient aider à le dépannage.

D'un autre côté, tapant:

=AGGREGATE(1,6,Team_Scores(Score))

dans la cellule E3 renvoie la moyenne (argument un = 1) de toutes les valeurs de la colonne B, y compris la valeur de la ligne 8 qui est filtrée et excluant l'erreur (argument b = 6).

Encore une fois, cela ne serait pas possible avec le sous-total, car il inclut toujours les lignes filtrées dans le résultat, et il n'y a aucun moyen de contourner l'erreur dans les données.

Cette fois, dans la cellule E3, la fonction agrégée est utilisée pour générer la médiane (argument un = 12), l'un des nombreux calculs de la fonction sous-totale ne permet pas:

=AGGREGATE(12,6,Team_Scores(Score))

Dans un dernier exemple, argument d a été utilisé pour générer la deuxième valeur la plus grande:

=AGGREGATE(14,6,Team_Scores(Score),2)

Résumé: sous-total vs agrégat dans Excel

Voici un résumé des fonctions subtotales et agrégées de Microsoft Excel:

---

Même si vous pensez que vous vous êtes réglé sur une fonction qui fonctionne pour vous, vérifiez toujours qu'il n'y a pas une autre fonction qui fait encore mieux le travail! Microsoft introduit souvent de nouvelles fonctions qui sont des mises à niveau des plus anciennes – par exemple, j'utilise maintenant xlookup au lieu de VLookup, et XMatch est beaucoup plus convivial que Match.