Fonction AGREGAT

La fonction AGREGATE est une fonction Excel permettant de faire un calcul agrégé. Elle réalise des opérations telles que MAX, MIN, NB, SOMME tout en permettant d’ignorer les lignes et colonnes masquées ainsi que les erreurs.
Utilisation de la fonction AGREGAT sur Excel

Syntaxe

La fonction AGREGAT s’écrit de la façon suivante :

= AGREGAT( no_fonction ; options ; matrice ; [k] )

Cette fonction possède au moins trois arguments.

  • no_fonction – Opération à réaliser (nombre attendu compris entre 1 et 19).
  • options – Valeurs à ignorer (nombre attendu compris entre 0 et 7.
  • matrice – Place sur laquelle appliquer le calcul
  • [k] – Dépends de l’opération sélectionnée. Pour les opérations GRANDE.VALEUR et PETITE.VALEUR, il faudra indiquer un nombre.

La fonction AGREGAT renvoie le résultat d’une opération telle que SOMME, MOYENNE, MIN, MAX, PETITE.VALEUR, GRANDE.VALEUR,… Par rapport aux fonctions de base, elle possède d’autres options. Parmi celles-ci il y a la possibilité d’ignorer les cellules masquées et les cellules renvoyant une erreur.

= AGREGAT( 9 ;           --> opération 9 => SOMME
           6 ;           --> option 6 => ignorer les erreurs
           $C$3:$C$14    --> plage sur laquelle réaliser l'opération
           )

Elle peut effectuer 19 calculs différents. Pour spécifier le type de calcul que l’on veut, il faut indiquer son numéro en tant que 1er argument de la fonction. Le second argument sera la spécification des options. Le 3e argument sera la plage de cellules sur laquelle effectuer le calcul choisi. Le dernier argument n’est utile que pour certains calculs. Par exemple, les calculs du type PETITE.VALEUR et GRANDE.VALEUR auront besoin que l’on spécifie la position que l’on souhaite récupérer. Voici la liste des opérations sur lesquelles il faudra ce dernier argument :

  • GRANDE.VALEUR ;
  • PETITE.VALEUR ;
  • CENTILE.INCLURE ;
  • QUARTILE.INCLURE ;
  • CENTILE.EXCLURE ;
  • QUARTILE.EXCLURE.

Comment utiliser la fonction AGREGAT sur Excel ?

Il faut utiliser 3 ou 4 arguments avec cette fonction : le numéro de la fonction, le choix des options de calcul, la plage de cellules sur laquelle appliquer le calcul et l’argument optionnel attaché à certains calculs.

Argument 1 – Numéro de la fonction

Le tableau ci-dessous indique la liste des fonctions pouvant être opérés par la fonction AGREGAT. Dans la dernière colonne sont indiquées les fonctions qui nécessitent le 4e argument.

no_fonctionFonctionk
1MOYENNE
2NB
3NBVAL
4MAX
5MIN
6PRODUIT
7ECARTYPE.STANDARD
8ECARTYPE.PEARSON
9SOMME
10VAR.S
11VAR.P.N
12MEDIANE
13MODE.SIMPLE
14GRANDE.VALEURk
15PETITE.VALEURk
16CENTILE.INCLURE
17QUARTILE.INCLURE
18CENTILE.EXCLURE
19QUARTILE.EXCLURE

Argument 2 – Les options

La fonction AGREGAT a plusieurs options disponibles. Elle peut :

  • Ignorer les erreurs ;
  • Ignorer les cellules masquées ;
  • Ignorer les sous-totaux ;
  • Ignorer les autres calculs AGREGAT.

Le tableau ci-dessous indique toutes les options disponibles.

Num optionCommentaire
0Ignorer les fonctions SOUS.TOTAL et AGREGAT imbriquées
1Ignorer les lignes masquées, ainsi que les fonctions SOUS.TOTAL et AGREGAT imbriquées
2Ignorer les valeurs d’erreur, ainsi que les fonctions SOUS.TOTAL et AGREGAT imbriquées
3Ignorer les lignes masquées, les valeurs d’erreur, ainsi que les fonctions SOUS.TOTAL et AGREGAT imbriquées
4Ne rien ignorer
5Ignorer les lignes masquées
6Ignorer les valeurs d’erreur
7Ignorer les lignes masquées et les valeurs d’erreur

Quelques utilisations de la fonction AGREGAT

Calculer la somme d’une plage de cellules en ignorant les erreurs

Pour récupérer la somme d’une plage de cellules en ignorant les erreurs, il faut utiliser la fonction 9 et l’option 6.

= AGREGAT( 9 ; 6 ; $C$3:$C$14 )

Calculer le maximum et le minimum en ignorant les lignes masquées

Pour générer un calcul du type MAX, il faut utiliser la fonction 4. Pour ignorer les lignes masquées, il faut utiliser l’option 5.

= AGREGAT( 4 ; 5 ; $C$3:$C$14 )

Pour générer un calcul du type MIN, il faut utiliser la fonction 5. Pour ignorer les lignes masquées, il faut utiliser l’option 5.

= AGREGAT( 5 ; 5 ; $C$3:$C$14 )

Récupérer la seconde plus grande valeur en ignorant les autres fonctions AGREGAT et les sous-totaux

Il faut utiliser la fonction GRANDE.VALEUR (nombre 14). Cette fonction permet de récupérer la énième plus grande valeur dans une plage de cellules. Il faudra donc ici préciser l’option 0 et indiquer 2 en tant que dernier argument

= AGREGAT( 14 ; 0 ; $C$3:$C$14 ; 2 )

Caractéristique de la fonction AGREGAT

  • La fonction AGREGAT ne fonctionne qu’avec les plages de cellules verticales ;
  • Elle permet d’ignorer les erreurs, les cellules masquées, les sous-totaux du calcul ;
  • Elle ressemble beaucoup à la fonction SOUS.TOTAL dans son fonctionnement tout en permettant davantage de possibilités grâce aux options.

Formules en lien

Fonctions en lien

4.8/5 - (19 votes)

Vous avez apprécié cet article ?

Pour me soutenir, cliquez sur cette publicité. Pas besoin d’acheter, un clic suffit.

Merci. 🤩

Annonces

Modèles Excel

+

Livret 

7 méthodes pour gagner 2H par semaine sur Excel

C’est gratuit 🤩

Les formations

Formations Excel

Parce que chacun devrait avoir les moyens de faire son travail sur Excel

Découvrez des formations accessibles, rapides et directement applicables.