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.

AGGREGATE - LARGE - Exemple 1

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).

AGGREGATE - MOYENNE Exemple 2

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 AGGREGATE - liste déroulante no_fonction La liste déroulante des options AGGREGATE - menu déroulant des options Si vous décidez d’appliquer la fonction via le fx bouton, c’est beaucoup moins utile. AGGREGATE - Bouton FX 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). AGGREGATE - Fenêtre Arguments de sélection le Arguments de fonction Window ne fournit aucune indication sur le numéro_fonction ou les options. AGGREGATE - Arguments de fonction 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. 1604505808 258 AGGREGATE La meilleure fonction Excel que vous nutilisez pas 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.

 

Avez-vous besoin d’aide pour l’adapter à vos besoins?

Je suppose que les exemples de cet article ne correspondaient pas exactement à votre situation. Nous utilisons tous Excel différemment, il est donc impossible d’écrire un article qui répondra aux besoins de chacun. En prenant le temps de comprendre les techniques et principes de cet article (et ailleurs sur ce site) vous devriez pouvoir l’adapter à vos besoins.

N’y allez pas encore, il y a beaucoup plus à apprendre sur comment-supprimer. Consultez les derniers articles:

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *