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_fonction | Fonction | k |
|---|---|---|
| 1 | MOYENNE | |
| 2 | NB | |
| 3 | NBVAL | |
| 4 | MAX | |
| 5 | MIN | |
| 6 | PRODUIT | |
| 7 | ECARTYPE.STANDARD | |
| 8 | ECARTYPE.PEARSON | |
| 9 | SOMME | |
| 10 | VAR.S | |
| 11 | VAR.P.N | |
| 12 | MEDIANE | |
| 13 | MODE.SIMPLE | |
| 14 | GRANDE.VALEUR | k |
| 15 | PETITE.VALEUR | k |
| 16 | CENTILE.INCLURE | |
| 17 | QUARTILE.INCLURE | |
| 18 | CENTILE.EXCLURE | |
| 19 | QUARTILE.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 option | Commentaire |
|---|---|
| 0 | Ignorer les fonctions SOUS.TOTAL et AGREGAT imbriquées |
| 1 | Ignorer les lignes masquées, ainsi que les fonctions SOUS.TOTAL et AGREGAT imbriquées |
| 2 | Ignorer les valeurs d’erreur, ainsi que les fonctions SOUS.TOTAL et AGREGAT imbriquées |
| 3 | Ignorer les lignes masquées, les valeurs d’erreur, ainsi que les fonctions SOUS.TOTAL et AGREGAT imbriquées |
| 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 |
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
- SOMME.SI plusieurs critères
- Compter nombre de valeurs texte différentes
- Compter le nombre de cellules égales à un texte
- Compter le nombre de cellules contenant du texte
- Compter le nombre de cellules non vides
- NB.SI avec plusieurs critères
