Comment utiliser la fonction Excel AGGREGATE -

Sommaire

La fonction Excel AGGREGATE renvoie un calcul global tel que MOYENNE, NOMBRE, MAX, etc., en ignorant éventuellement les lignes masquées et les erreurs. Un total de 19 opérations sont disponibles, spécifiées par le numéro de fonction dans le premier argument (voir le tableau pour les options).

Objectif

Calcul de l'agrégat de retour

Valeur de retour

Dépend de la fonction spécifiée

Syntaxe

= AGGREGATE (no_fonction, options, ref1, ref2)

Arguments

  • function_num - Opération à effectuer (1-19).
  • options - Valeurs à ignorer (0-7).
  • ref1 - Premier argument.
  • ref2 - Deuxième argument (k).

Version

Excel 2010

Notes d'utilisation

La fonction AGGREGATE renvoie le résultat d'un calcul agrégé comme MOYENNE, NOMBRE, MAX, MIN, etc. Un total de 19 opérations sont disponibles et l'opération à effectuer est spécifiée sous forme de nombre, qui apparaît comme premier argument de la fonction. Le deuxième argument, options, contrôle la façon dont AGGREGATE gère les erreurs et les valeurs dans les lignes masquées. Voir les tableaux ci-dessous pour toutes les options disponibles.

Exemple 1

Pour renvoyer la valeur MAX dans la plage A1: A10, en ignorant les erreurs et les lignes masquées, indiquez 4 pour le numéro de fonction et 7 pour les options:

=AGGREGATE(4,7,A1:A10) // max value

Pour renvoyer la valeur MIN avec les mêmes options, changez le numéro de fonction en 5:

=AGGREGATE(5,7,A1:A10) // min value

Exemple # 2

Dans l'exemple ci-dessus, la formule en D5 est:

=AGGREGATE(4,6,values)

où "valeurs" est la plage nommée B5: B14. Le numéro de la fonction est 4, ce qui spécifie MAX. Les options sont fournies sous forme de 6, pour ignorer uniquement les erreurs.

Exemple # 3 - nième plus grand

Les formules de D8: D10 montrent comment renvoyer les "nèmes plus grandes" valeurs:

=AGGREGATE(14,6,values,1) // 1st largest =AGGREGATE(14,6,values,2) // 2nd largest =AGGREGATE(14,6,values,3) // 3rd largest

Le numéro de la fonction ici est 14, qui exécute la fonction LARGE. Étant donné que la fonction LARGE nécessite un argument k, elle apparaît comme le dernier argument dans les trois formules ci-dessus.

Exemple # 4 - opération de tableau

Ce qui rend AGGREGATE particulièrement utile pour les formules plus complexes, c'est qu'il peut gérer les tableaux de manière native lorsque le numéro de fonction est 14-19. Par exemple, pour trouver la valeur MAX le lundi, avec des données comprenant des dates et des valeurs, vous pouvez utiliser AGGREGATE comme ceci:

=AGGREGATE(14,6,values/(TEXT(dates,"ddd")="Mon"),1)

Ici, nous spécifions 14 pour la fonction (LARGE) et 6 pour l'option (ignorer les erreurs). Ensuite, nous construisons une expression logique en utilisant la fonction TEXT pour vérifier toutes les dates du lundi. Le résultat de cette opération est un tableau de valeurs TRUE / FALSE, qui deviennent le dénominateur des valeurs d'origine. FALSE est évalué à zéro et lance un # DIV / 0! Erreur. TRUE prend la valeur 1 et renvoie la valeur d'origine. Le tableau final de valeurs et d'erreurs agit comme un filtre. AGGREGATE ignore toutes les erreurs et renvoie la plus grande (maximum) des valeurs restantes. Exemple plus complet ici.

Numéros de fonction

Le tableau ci-dessous répertorie les numéros de fonction disponibles pour la fonction AGGREGATE, ainsi que le nom de la fonction associée. La troisième colonne, Ref2, indique la deuxième valeur d'argument attendue lorsqu'elle est requise.

Fonction Fonction Réf2
1 MOYENNE
2 COMPTER
3 COUNTA
4 MAX
5 MIN
6 PRODUIT
sept STDEV.S
8 STDEV.P
9 SOMME
dix VAR.S
11 VAR.P
12 MÉDIAN
13 MODE.SNGL
14 GRAND k
15 PETIT k
16 PERCENTILE.INC k
17 QUARTILE.INC litre
18 PERCENTILE.EXC k
19 QUARTILE.EXC litre

Options de comportement

La fonction AGGREGATE a de nombreuses options pour ignorer les erreurs, les lignes masquées et d'autres fonctions. Les valeurs possibles sont 0-7, comme indiqué dans le tableau ci-dessous.

Option Comportement
0 Ignorer les fonctions SUBTOTAL et AGGREGATE
1 Ignorer les lignes cachées, les fonctions SUBTOTAL et AGGREGATE
2 Ignorer les valeurs d'erreur, les fonctions SUBTOTAL et AGGREGATE
3 Ignorer les lignes masquées, les valeurs d'erreur, les fonctions SUBTOTAL et AGGREGATE
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

Remarques

  • AGGREGATE renvoie une #VALUE! erreur si un deuxième argument de fonction est requis, mais non fourni.
  • Les références 3D ne fonctionnent pas avec AGGREGATE.
  • La fonction AGGREGATE est conçue des plages verticales, pas des plages horizontales.

Articles intéressants...