Qu'est-ce que la fonction AGREGAT ?
AGREGAT (AGGREGATE en anglais) te sert le jour où une seule cellule en erreur menace de faire planter tout ton total : au lieu de traquer la ligne fautive ou d'empiler des SIERREUR, tu choisis directement ce qu'elle doit ignorer.
C'est l'outil de référence pour les tableaux de bord automatisés : elle s'adapte aux filtres en temps réel, survit aux données imparfaites et remplace d'un coup des combinaisons complexes de SIERREUR, SOMME ou MOYENNE.
Syntaxe
=AGREGAT(no_fonction; option; ref1; [ref2]; ...)Clique sur un argument pour aller à son explication.
Les fonctions 14 (GRANDE.VALEUR) et 15 (PETITE.VALEUR) nécessitent un argument k obligatoire en position ref2 pour indiquer le rang recherché.
Comprendre chaque paramètre de la fonction AGREGAT
Les deux premiers arguments décident de tout : no_fonction dit QUEL calcul tu veux (1 pour la moyenne, 9 pour la somme...), et option dit ce qu'AGREGAT doit ignorer au passage (erreurs, lignes masquées, filtres). Tu ne peux pas en sauter un : ils sont tous les deux obligatoires et l'ordre est verrouillé.
Vient ensuite ref1, ta plage de données. Les plages suivantes (ref2, ref3...) sont facultatives quand tu veux additionner des zones séparées, mais attention : pour les codes 14 et 15, cette deuxième position change de rôle et devient obligatoire.
no_fonction
: le type de calcul à effectuer, représenté par un nombre de 1 à 19Les codes les plus courants : 1 = MOYENNE, 2 = NB (comptage), 4 = MAX, 5 = MIN, 9 = SOMME.
Tu as aussi accès à des fonctions plus avancées comme 14 (GRANDE.VALEUR, qui retourne la n-ième plus grande valeur) ou 15 (PETITE.VALEUR). Chaque code correspond à une fonction Excel classique.
option
: ce paramètre indique ce qu'AGREGAT doit ignorer dans son calculLes options les plus utiles : 0 (ne rien ignorer), 1 (ignorer les lignes masquées manuellement), 2 (ignorer les erreurs), 3 (lignes masquées ET erreurs), 5 (lignes masquées, filtres exclus), 6 (erreurs et valeurs masquées par filtre), 7 (tout ignorer : erreurs, lignes masquées, sous-totaux imbriqués).
ref1
: la première plage de données sur laquelle effectuer le calculÇa peut être une plage comme A1:A100, une colonne entière A:A, ou même une référence à une seule cellule.
[ref2], [ref3], ...
: tu peux ajouter jusqu'à 252 plages supplémentaires pour calculer sur des zones non adjacentes(facultatif)Très pratique pour additionner des colonnes séparées ou faire des moyennes sur plusieurs tableaux.
Pour les fonctions 14 (GRANDE.VALEUR) et 15 (PETITE.VALEUR), ref2 devient obligatoire et représente k : le rang recherché (par exemple 3 pour la 3e plus grande valeur).
Exemples pratiques pas à pas
Comment additionner une colonne en ignorant les erreurs avec la fonction AGREGAT
Les dix-huit postes du budget sont saisis dans un tableau, chaque service ayant rempli ses lignes avec son montant, son trimestre et son statut. On te réclame le total consolidé pour la réunion, sauf qu'une ligne alimentée depuis un autre classeur peut basculer en #N/A du jour au lendemain : avec une somme classique, c'est tout le total qui part en erreur et le chiffre que tu annonces disparaît. La fonction AGREGAT fait le calcul et saute les cellules en erreur au passage, donc ton total reste affiché même quand une donnée manque à l'appel.
- 1Dans une cellule, écris
=AGREGAT(. - 2En 1er argument : saisis le code du calcul voulu,
9. C'est celui de la somme, et c'est ce premier chiffre qui fait d'AGREGAT une addition plutôt qu'une moyenne ou un maximum. - 3En 2ᵉ argument : saisis le code de ce que la fonction doit ignorer,
6. Cette option écarte à la fois les cellules en erreur et les lignes masquées par un filtre, ce qui est exactement la protection recherchée sur un budget alimenté depuis un autre classeur. - 4En 3ᵉ argument : sélectionne la colonne des montants à additionner,
B2:B19. La plage démarre en ligne 2 pour laisser l'en-tête de côté, et elle suffit à elle seule puisque toutes les valeurs tiennent dans la même colonne. - 5Ferme la parenthèse et appuie sur Entrée.
=AGREGAT(9; 6; B2:B19)9, c'est la somme) et le second dit ce qu'il faut laisser de côté (6, les erreurs et les lignes masquées par un filtre). La fonction additionne les dix-huit montants de la colonne et affiche 628 000 €, et le jour où l'un d'eux bascule en #N/A, le total continue de s'afficher au lieu de tomber en erreur à son tour.
Comment calculer une moyenne sur un tableau filtré avec la fonction AGREGAT
Tu passes les dépenses en revue service par service et tu viens de poser un filtre sur la colonne Service pour n'afficher que les lignes du Nord. La moyenne que tu annonces doit coller à ce que l'écran montre : si elle continue d'inclure les lignes masquées, ton chiffre ne veut plus rien dire et l'arbitrage part de travers. La fonction AGREGAT change d'opération selon le code que tu lui donnes et tient compte du filtre en place, donc la moyenne suit l'affichage à chaque changement de service.
- 1Dans une cellule, écris
=AGREGAT(. - 2En 1er argument : saisis le code du calcul voulu,
1. C'est celui de la moyenne, là où le code9demanderait une somme sur les mêmes données. - 3En 2ᵉ argument : saisis le code de ce que la fonction doit ignorer,
6. Cette option couvre les erreurs et les lignes masquées par un filtre, et c'est son volet filtre qui joue ici puisque les lignes du Sud sont cachées à l'écran. - 4En 3ᵉ argument : sélectionne la colonne des montants,
B2:B7. Tu la prends en entier, lignes masquées comprises, parce que la fonction se charge d'écarter ce que le filtre cache et qu'une plage rétrécie à la main serait fausse dès le prochain changement de service. - 5Ferme la parenthèse et appuie sur Entrée.
=AGREGAT(1; 6; B2:B7)1 demande une moyenne à la place de la somme, et l'option 6 écarte les lignes que le filtre a masquées. Seuls les trois montants du Nord (45 000 €, 28 000 € et 20 000 €) entrent donc dans le calcul, soit 31 000 € de moyenne. Bascule le filtre sur le Sud et le chiffre se recalcule tout seul, sans que tu touches à la formule.
Comment obtenir la 3e plus grande valeur avec la fonction AGREGAT
L'arbitrage budgétaire arrive et on ne te demande pas seulement le plus gros poste de dépense, mais le trio de tête. Tes montants sont dans une colonne où une ligne affiche #N/A parce que la facture n'est pas encore remontée, et tu veux sortir le troisième montant le plus élevé sans attendre qu'elle arrive. La fonction AGREGAT sait faire ce classement, accepte un rang en plus de ses arguments habituels et ignore l'erreur qui bloquerait un calcul classique.
- 1Dans une cellule, écris
=AGREGAT(. - 2En 1er argument : saisis le code qui classe les montants du plus grand au plus petit,
14. Il correspond à GRANDE.VALEUR et va chercher la n-ième valeur de ce classement, là où le code4te rendrait seulement le maximum. - 3En 2ᵉ argument : saisis le code de ce que la fonction doit ignorer,
6. Il écarte les cellules en erreur, donc le#N/Ade la ligne RH 3 est mis de côté avant le classement. Sans lui, cette erreur se propagerait au résultat et tu n'obtiendrais aucun montant. - 4En 3ᵉ argument : sélectionne la colonne des montants,
B2:B7. Tu la prends telle quelle, avec son#N/Adedans, puisque c'est l'option précédente qui s'occupe de l'écarter. - 5En 4ᵉ argument : saisis le rang à remonter dans ce classement,
3. Avec les codes14et15, cette quatrième position est obligatoire et l'oublier renvoie une erreur. Avec tous les autres codes, elle sert au contraire à ajouter une plage de données. - 6Ferme la parenthèse et appuie sur Entrée.
=AGREGAT(14; 6; B2:B7; 3)14 classe les montants du plus grand au plus petit, l'option 6 met le #N/A de côté et le rang 3 va chercher le troisième de ce classement. Il reste 51 000 €, puis 45 000 €, puis 37 000 € sur la marche du dessous. Attention à cette quatrième place dans la formule : elle porte le rang avec les codes 14 et 15, alors qu'elle sert à ajouter une plage avec tous les autres.
Les erreurs fréquentes avec la fonction AGREGAT
AGREGAT a beaucoup d'arguments numériques, et c'est presque toujours là que ça coince : un chiffre glissé d'une case dans le code de fonction, et tu récoltes un #VALEUR!. Garde en tête que no_fonction doit rester entre 1 et 19.
Les deux autres pièges sont plus sournois car ils ne plantent pas forcément : les codes 14 et 15 réclament un rang k qu'on oublie, et une option mal choisie laisse passer des lignes que tu croyais exclues, sans le moindre message d'alerte.
Code de fonction invalide (hors de la plage 1-19)
Si tu obtiens #VALEUR!, c'est souvent que le code de fonction (1er paramètre) est incorrect. Les codes valides vont de 1 à 19. Un code 0, 20 ou négatif génère une erreur.
Solution : Consulte la liste des codes : 1=MOYENNE, 2=NB, 3=NBVAL, 4=MAX, 5=MIN, 6=PRODUIT, 7=ECARTYPE, 9=SOMME, 14=GRANDE.VALEUR, 15=PETITE.VALEUR. Vérifie que tu n'as pas glissé d'un code à l'autre lors de la saisie.
Argument k manquant pour GRANDE.VALEUR ou PETITE.VALEUR
Les fonctions 14 (GRANDE.VALEUR) et 15 (PETITE.VALEUR) nécessitent un 4e argument k pour indiquer quel rang tu cherches. Sans cet argument, Excel renvoie une erreur.
Solution : Ajoute le paramètre k en 4e position : =AGREGAT(14; 6; A1:A100; 3) pour obtenir la 3e plus grande valeur. Pour toutes les autres fonctions, le 4e argument sert à ajouter des plages supplémentaires.
Option incorrecte : des données que tu voulais ignorer sont incluses
Chaque option a un comportement spécifique. L'option 1 ignore uniquement les lignes masquées manuellement, pas celles cachées par un filtre. Si tu appliques un filtre et que le calcul reste identique, c'est que l'option choisie ne couvre pas ce cas.
Solution : Utilise l'option 6 pour la plupart des cas : elle ignore à la fois les erreurs et les lignes masquées par un filtre. Si tu veux être plus sélectif, utilise 1 (lignes masquées uniquement), 2 (erreurs uniquement) ou 3 (lignes masquées ET erreurs, filtres exclus).
AGREGAT vs SOUS.TOTAL vs SOMME vs MOYENNE
Utilise les fonctions classiques (SOMME, MOYENNE, MAX...) quand tes données sont propres et que tu n'as pas besoin de gérer les filtres. SOUS.TOTAL est un bon compromis pour les données filtrées sans erreurs. AGREGAT prend le relais dès que tu dois ignorer des erreurs ou des lignes masquées.
| Critère | AGREGAT | SOUS.TOTAL | SOMME / MOYENNE / MAX |
|---|---|---|---|
| Ignore les erreurs | ✅ Oui (options 2, 3, 6, 7) | ❌ Non | ❌ Non |
| Respecte les filtres | ✅ Oui (options 6, 7) | ✅ Oui (codes 101-111) | ❌ Non |
| Ignore les lignes masquées | ✅ Oui (options 1, 3, 5, 6, 7) | ✅ Oui | ❌ Non |
| Nombre de fonctions disponibles | 19 fonctions | 11 fonctions | 1 fonction chacune |
| Complexité | ⭐⭐ | ⭐⭐ | ⭐ |
| Meilleur usage | Données avec erreurs ou filtrées | Données filtrées sans erreurs | Données propres, calculs simples |
Questions fréquentes
AGREGAT offre plus de flexibilité : elle propose 19 fonctions différentes (contre 11 pour SOUS.TOTAL), peut ignorer les erreurs en plus des lignes masquées, et gère mieux les sous-totaux imbriqués. Si tu dois ignorer des erreurs comme #N/A, AGREGAT est le bon choix.
Retiens les 5 plus courants : 1=MOYENNE, 2=NB, 4=MAX, 5=MIN, 9=SOMME. Pour les autres (ECARTYPE, GRANDE.VALEUR...), garde une feuille de référence ou tape =AGREGAT( dans Excel : la saisie assistée te montre la liste complète avec les libellés.
Oui. Utilise l'option 6 ou 7. L'option 6 ignore les erreurs et les valeurs masquées par un filtre. L'option 7 fait pareil mais ignore aussi les sous-totaux imbriqués. C'est très puissant pour nettoyer tes calculs dans un tableau de bord.
Vérifie que le code de fonction est valide (entre 1 et 19) et que tu fournis le bon nombre d'arguments. Les fonctions 14 (GRANDE.VALEUR) et 15 (PETITE.VALEUR) nécessitent un 4e argument k pour indiquer le rang. Sans lui, Excel renvoie #VALEUR!.
Oui, pour la plupart des fonctions. AGREGAT accepte jusqu'à 253 plages supplémentaires après ref1. C'est parfait pour additionner ou calculer des moyennes sur des zones non adjacentes tout en ignorant les erreurs.
Pour aller plus loin
Continue sur ta lancée après la fonction AGREGAT : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.
Tout voir
