AGGREGATE: La meilleure fonction Excel

Dans Excel 2010, Microsoft a introduit une nouvelle fonction appelée AGGREGATE. C’est l’une des fonctions les plus puissantes d’Excel, mais je l’ai rarement vue utilisée dans un scénario réel. Mon objectif avec cet article est de vous montrer à quel point AGGREGATE est bon, afin que vous puissiez utiliser sa puissance.

Que fait AGGREGATE?

Son nom est trompeur. Que signifie le mot agrégat? Pour former un tout à partir de parties séparées. Cela ressemble à SUM, n’est-ce pas? D’où le problème avec son nom, la fonction AGGREGATE fait bien plus que SUM.

AGGREGATE peut COMPTER, MOYENNE, MAX, PETIT et SOMME, pour n’en nommer que quelques-uns. Mais c’est mieux que ces fonctions car il exécute tout cela tout en ignorant les erreurs, les cellules cachées et d’autres cellules contenant des fonctions AGGREGATE et SUBTOTAL.

Pour vous aider à voir la puissance, considérons quelques scénarios.

Scénario 1

Supposons que vous souhaitiez trouver le deuxième plus grand résultat sur une plage de cellules où ces cellules contiennent une erreur # N / A. Nous nous tournons généralement vers la fonction LARGE pour ce type de calcul, mais dans ce cas, LARGE renverra une erreur.

Nous pouvons dire à la fonction AGGREGATE d’ignorer l’erreur, ce qui lui permet de calculer le résultat correct.

La capture d’écran ci-dessous montre le résultat des fonctions LARGE et AGGREGATE.

Scénario 2

Comme deuxième exemple, que faire si vous souhaitez trouver la moyenne des cellules visibles. La fonction MOYENNE calculera en utilisant toutes les cellules de la plage; peu importe si ces cellules sont visibles ou non.

Nous pouvons dire à la fonction AGGREGATE d’ignorer les cellules cachées, ce qui lui permet de calculer le résultat correct.

La capture d’écran ci-dessous, dans laquelle la ligne 5 est masquée, montre que la moyenne des cellules visibles doit être de 38 750 (comme calculé par AGGREGATE), et non de 71 000 (comme calculé par MOYENNE).

Fonctionnalité bonus

En prime, AGGREGATE peut également traiter des tableaux pour des fonctions spécifiques sans avoir besoin de Ctrl + Maj + Entrée. Si vous ne savez pas ce que cela signifie, ne vous inquiétez pas. Mais si vous le faites, vous saurez que c’est un gros problème.

Comment utiliser AGGREGATE

La fonction AGGREGATE peut avoir deux formes de syntaxe:

Formulaire de référence:

=AGGREGATE(function_num,options,ref1,[ref2],...)

Forme de tableau:

=AGGREGATE(function_num,options,array,[k]) 

Vous n'avez pas à vous soucier du formulaire que vous utilisez. Excel adoptera la forme nécessaire en fonction du numéro_fonction sélectionné.

Considérons chacun des arguments de la fonction.

no_fonction

Une valeur de 1 à 19 qui indique le type d’action à effectuer.

no_fonction	Calcul effectué	La description	Forme de fonction
1	MOYENNE	Renvoie la moyenne (moyenne)	Référence
2	COMPTER	Compte le nombre de valeurs ou de cellules contenant des nombres	Référence
3	COUNTA	Compte le nombre de valeurs ou de cellules qui ne sont pas vides	Référence
4	MAX	Renvoie la plus grande valeur	Référence
5	MIN	Renvoie la plus petite valeur	Référence
6	PRODUIT	Multiplie tous les nombres	Référence
sept	STDEV.S	Estime l’écart type sur la base d’un échantillon	Référence
8	STDEV.P	Calcule l’écart type en fonction d’une population	Référence
9	SOMME	Ajoute toutes les valeurs ou cellules d’une plage	Référence
dix	VAR.S	Estime la variance sur la base d’un échantillon	Référence
11	VAR.P	Calcule la variance en fonction d’une population	Référence
12	MÉDIAN	Renvoie la médiane (le nombre au milieu)	Référence
13	MODE.SNGL	Renvoie le nombre le plus fréquent	Référence
14	GRAND	Renvoie la nième valeur la plus grande	Tableau
15	PETIT	Renvoie la nième plus petite valeur	Tableau
16	PERCENTILE.INC	Renvoie le nième centile des valeurs dans une plage qui inclut la valeur	Tableau
17	QUARTILE.INC	Renvoie le quartile basé sur un centile comprenant la valeur	Tableau
18	PERCENTILE.EXC	Renvoie le nième centile des valeurs dans une plage qui exclut la valeur	Tableau
19	QUARTILE.EXC	Renvoie le quartile basé sur un centile excluant la valeur	Tableau

 

options

Une valeur de 0 à 7 indiquant les cellules à inclure dans la fonction

options	La description
0 ou [blank]	Ignorer les fonctions SUBTOTAL et AGGREGATE imbriquées
1	Ignorer les lignes masquées, les fonctions SUBTOTAL et AGGREGATE imbriquées
2	Ignorer les valeurs d’erreur, les fonctions SUBTOTAL et AGGREGATE imbriquées
3	Ignorer les lignes cachées, les valeurs d’erreur, les fonctions SUBTOTAL et AGGREGATE imbriquées
4	Ne rien ignorer
5	Ignorer les lignes cachées
6	Ignorer les valeurs d’erreur
sept	Ignorer les lignes masquées et les valeurs d’erreur

 

ref1 / [ref2]

ref1: une référence à la plage de cellules sur laquelle la fonction doit être appliquée [ref2]: une plage supplémentaire facultative de cellules. Il peut y avoir jusqu’à 252 plages distinctes (y compris ref1), chacune séparée par une virgule (,).

tableau

Un tableau de valeurs ou une formule matricielle sur laquelle la fonction doit être appliquée
[k]

Un critère de sélection qui s’applique à des numéros de fonction spécifiques

calcul	La description
GRAND	La nième plus grande valeur trouvée
PETIT	La nième plus petite valeur trouvée
PERCENTILE.INC	La valeur en pourcentage doit être comprise entre 0 et 1
QUARTILE.INC	Le quartile doit être 0, 1, 2, 3, 4
PERCENTILE.EXC	La valeur en pourcentage doit être comprise entre 0 et 1
QUARTILE.EXC	Le pourcentage doit être 0, 1, 2, 3, 4

 

Trop de choses à retenir?

Si vous pensez qu’AGGREGATE est compliqué parce qu’il y a trop de choses à retenir, ne vous inquiétez pas. Lorsque vous commencez à taper la fonction dans une cellule ou dans la zone de formule, une liste déroulante apparaîtra avec les différentes options. La liste déroulante function_num La liste déroulante des options Si vous décidez d’appliquer la fonction via le fx bouton, c’est beaucoup moins utile. Il vous oblige à sélectionner le format de la fonction (bien que si vous sélectionnez le mauvais formulaire et appliquez les arguments aux bonnes positions, il calculera toujours correctement). le Arguments de fonction Window ne fournit aucune indication sur le numéro_fonction ou les options. Par conséquent, pour utiliser la fonction AGGREGATE, il est préférable de taper directement dans la cellule ou la zone de formule, plutôt que d’utiliser le fx bouton.

Exemples

Maintenant que vous comprenez un peu la fonction, nous pouvons commencer à regarder quelques exemples. Notez que la ligne 4 est masquée et que la cellule D6 contient une erreur.

Exemple 1 – Références multiples

La formule de la cellule B12 est:

=AGGREGATE(1,7,C2:C9,D2:D9,E2:E9)

Cette formule calcule la moyenne (no_fonction = 1) des cellules C2-C9, D2-D9 et E2-E9 tout en ignorant les lignes cachées et les erreurs (options = 7).

Bien qu’il soit possible d’inclure une seule référence de C2: E9, cet exemple montre chaque colonne de valeurs individuellement.

Exemple 2 – GRAND

La formule de la cellule B14 est:

=AGGREGATE(14,5,C2:C9,1)

Cette formule calcule la 1ère plus grande valeur (function_num = 14) dans la plage C2-C9 tout en ignorant les lignes cachées (options = 5).

Exemple 3 – Formule matricielle LARGE évitant Ctrl + Maj + Entrée

Cet exemple est beaucoup plus complexe. Si vous ne comprenez pas ce que cela signifie, ne vous inquiétez pas. Vous pouvez toujours utiliser AGGREGATE sans comprendre cet exemple.

La formule de la cellule B16 est:

=AGGREGATE(14,5,C2:C9*(B2:B9="St Johns"),1)

Cet exemple est une extension de l’exemple 2 ci-dessus. Il inclut désormais l’exigence selon laquelle le premier plus grand ne doit être calculé que sur les élèves de l’école St Johns.

Si nous essayions d’obtenir le même résultat avec la fonction standard LARGE, nous aurions besoin d’appuyer sur Ctrl + Maj + Entrée, car il s’agit d’une formule matricielle. Un exemple peut être vu ci-dessous, n’entrez pas les accolades, Excel les ajoutera lui-même.

{=LARGE(C2:C9*(B2:B9="St Johns"),1)}

AGGREGATE n’a pas besoin de Ctrl + Maj + Entrée pour être utilisé lors de l’utilisation des numéros_fonction 14 à 19.

Avant que vous ne soyez trop excité, il y a un problème ici. Comment est-il possible que la valeur la plus élevée pour St Johns soit de 72 alors que dans l’exemple 2 la valeur la plus élevée pour toutes les écoles était de 68?

C’est une bizarrerie de formules matricielles. Le 72 retourné pour St Johns est contenu dans la ligne cachée. Nous avons sélectionné l’option 5 pour ignorer les lignes masquées, mais ce paramètre a été ignoré.

Le problème est la chaîne de calcul. Excel évaluera la formule dans l’ordre suivant:

1. B2: B9 = “St Johns” = {TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE}
2. C2: C9 * {TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE} = {55; 0; 72; 0; 58; 60; 0; 0}
3. = AGRÉGÉ (14,5, {55; 0; 72; 0; 58; 60; 0; 0}, 2)
4. = 72

La partie tableau de la fonction est calculée en premier, transformant les références de cellule en valeurs. Par conséquent, il n’y a aucune référence de cellule à ignorer. Ce n’est pas nécessairement une erreur de calcul; nous avons juste besoin de comprendre comment fonctionne la formule.

Exemple 4 – Sans ignorer les erreurs

La formule de la cellule B18 est:

=AGGREGATE(9,5,D2:D9) Cette formule essaie de faire la somme (function_num = 9) les valeurs, dans les cellules D2: D9 tout en ignorant les lignes cachées (options = 5). Le résultat de la formule est une erreur # N / A car les cellules incluses incluent également une erreur.

Erreurs / problèmes courants

Comme pour toutes les fonctions, il existe des pièges et des problèmes courants.

• Si un [k] L’argument est requis, mais pas à condition qu’une erreur #VALUE apparaisse. Par exemple, lors de l’application du numéro_fonction LARGE, il doit y avoir un argument pour trouver le nième élément le plus grand.
• Bien qu’il existe une option pour ignorer les lignes cachées, il n’y a pas d’option pour ignorer les colonnes cachées.
• Lors de l’utilisation d’un tableau qui implique un calcul, l’option Ignorer les lignes cachées est ignorée (comme indiqué dans l’exemple 3 ci-dessus).

Si c’est bon, pourquoi ne pas utiliser AGGREGATE tout le temps?

Si je vous ai convaincu que AGGREGATE est une formule fantastique, alors pourquoi ne pas l’utiliser tout le temps?

Il y a quelques inconvénients:

• C’est plus compliqué à utiliser que son homologue standard (c’est-à-dire que SUM est facile à utiliser, mais la sommation avec AGGREGATE est un peu plus compliquée)
• Prend plus de temps pour saisir tous les arguments
• Trop d’options à retenir
• La plupart des utilisateurs ne comprennent pas la fonction.
• Ne fonctionnera pas pour les utilisateurs avec Excel 2007 ou avant

Mais il y a autant de bonnes raisons de l’utiliser aussi.

• Peut faire 152 choses différentes (19 fonctions x 8 options), plutôt qu’une seule (c’est une formule de couteau suisse).
• Pas besoin de corriger les erreurs de formule dans la plage utilisée dans la fonction
• Les calculs peuvent être effectués sur les cellules visibles des listes filtrées
• Peut gérer des formules matricielles pour des numéros_fonction spécifiques
• C’est une formule très rapide

Voilà, c’est AGGREGATE, la meilleure fonction que vous n’utilisez pas. . . jusqu’à aujourd’hui.