Exceller

Fonction Excel AGGREGATE

Excel Aggregate Function

Fonction Excel AGGREGATERésumé

La fonction Excel AGGREGATE renvoie un calcul agrégé 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 Renvoyer le calcul agrégé Valeur de retour Dépend de la fonction spécifiée Syntaxe = AGGREGATE (no_fonction, options, ref1, ref2) Arguments
  • no_fonction - Opération à effectuer (1-19).
  • options - Valeurs à ignorer (0-7).
  • ref1 - Premier argument.
  • réf2 - Deuxième argument (k).
Version Excel 2010 Notes d'utilisation

La fonction AGGREGATE renvoie le résultat d'un calcul agrégé tel que 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 manière 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 deux erreurs et lignes caché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:

comment faire un plan budgétaire dans Excel
 
= AGGREGATE (5,7,A1:A10) // min value

Exemple # 2

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



 
= AGGREGATE (4,6,values)

où 'values' est le 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 dans D8: D10 montrent comment renvoyer les «nième 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 fonction ici est 14, ce qui exécute le GRANDE fonction . É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 MOYEN
deux COMPTER
3 COUNTA
4 MAX
5 MIN
6 PRODUIT
7 STDEV.S
8 STDEV.P
9 SOMME
dix À QUI
Onze VAR.P
12 MÉDIAN
13 MODE.SNGL
14 GRANDE à
quinze PETIT à
16 PERCENTILE.INC à
17 QUARTILE.INC quart
18 PERCENTILE.EXC à
19 QUARTILE.EXC quart

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
deux Ignorer les valeurs d'erreur, les fonctions SUBTOTAL et AGGREGATE
3 Ignorer les lignes cachées, les valeurs d'erreur, les fonctions SUBTOTAL et AGGREGATE
4 Ne rien ignorer
5 Ignorer les lignes masquées
6 Ignorer les valeurs d'erreur
7 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.


^