AGREGAT (AGGREGATE en anglais) est une fonction ultra-puissante qui te permet d'effectuer 19 types de calculs différents (somme, moyenne, max, min, comptage...) tout en ignorant automatiquement les erreurs, les lignes masquées ou les sous-totaux. C'est la solution idéale quand tes données contiennent des #N/A, des #DIV/0! ou d'autres erreurs que tu veux ignorer sans nettoyer manuellement.
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 de la fonction AGREGAT
=AGREGAT(no_fonction; option; ref1; [ref2]; ...)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.
Astuce : Retiens les 5 incontournables : 1=MOYENNE, 2=NB, 4=MAX, 5=MIN, 9=SOMME. Pour les autres, tape =AGREGAT( dans Excel : la saisie assistée affiche la liste complète.
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).
Astuce : L'option 6 couvre 90 % des cas d'usage : elle ignore à la fois les erreurs et les lignes masquées par un filtre, ce qui rend tes tableaux de bord ultra-robustes.
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).
Pas envie d'écrire la formule AGREGAT à la main ?
Génère-la avec notre IA
Exemples pratiques pas à pas
Contrôleur de gestion : somme avec gestion des erreurs dans les données financières
Tu es contrôleur de gestion et tu consolides les revenus de plusieurs filiales. Certaines filiales ont des #N/A dans leurs données car elles n'ont pas encore transmis leurs chiffres. Avec SOMME classique, tu obtiendrais une erreur, là où AGREGAT calcule sans broncher.
| A | B | |
|---|---|---|
| 1 | Filiale | Revenu mensuel |
| 2 | Paris | 150 000 € |
| 3 | Lyon | #N/A |
| 4 | Marseille | 98 000 € |
| 5 | Bordeaux | 112 000 € |
| 6 | TOTAL | 360 000 € |
=AGREGAT(9; 6; B1:B4)Le code 9 demande une somme et l'option 6 fait ignorer les erreurs : la fonction additionne donc Paris, Marseille et Bordeaux (360 000 €) en sautant le #N/A de Lyon. Quand Lyon transmettra ses chiffres, le total s'ajustera tout seul.
Analyste : calculs sur données filtrées
Tu es analyste de données et tu dois calculer la moyenne des ventes uniquement sur les lignes visibles après avoir appliqué un filtre. MOYENNE classique inclurait toutes les lignes, même masquées, alors qu'AGREGAT respecte ton filtre.
| A | B | C | |
|---|---|---|---|
| 1 | Produit | Ventes | État |
| 2 | Produit A | 5 200 € | Visible |
| 3 | Produit B | 2 100 € | Masqué (filtré) |
| 4 | Produit C | 6 800 € | Visible |
| 5 | Moyenne visible | 6 000 € |
=AGREGAT(1; 6; B1:B3)Le code 1 demande une moyenne et l'option 6 fait ignorer les lignes masquées par le filtre : seules les ventes visibles (5 200 € et 6 800 €) entrent dans le calcul, soit 6 000 €. Change les filtres et le résultat s'adapte en temps réel.
Astuce de pro : SOUS.TOTAL(101; B1:B3) donnerait le même résultat ici, mais AGREGAT est plus polyvalente : 19 fonctions contre 11 pour SOUS.TOTAL, et elle gère les erreurs en prime.
Gestionnaire de stock : maximum avec valeurs manquantes
Tu es gestionnaire de stock et tu dois trouver le stock maximum parmi plusieurs entrepôts. Certains entrepôts ont des #N/A car l'inventaire n'est pas terminé. Tu veux quand même connaître le maximum actuel sans attendre.
| A | B | |
|---|---|---|
| 1 | Entrepôt | Stock unitaire |
| 2 | Nord | 450 |
| 3 | Sud | #N/A |
| 4 | Est | 820 |
| 5 | Ouest | 615 |
| 6 | Maximum | 820 |
=AGREGAT(4; 6; B1:B4)Le code 4 demande le maximum et l'option 6 fait ignorer les erreurs : la fonction retient le plus gros stock parmi Nord, Est et Ouest (820) en sautant le #N/A du Sud. Quand le Sud mettra son inventaire à jour, le résultat s'ajustera si sa valeur dépasse 820.
RH : moyenne sur employés actifs uniquement
Tu travailles aux ressources humaines et tu dois calculer le salaire moyen de l'équipe actuelle. Les employés partis sont masqués manuellement dans le tableau mais pas supprimés, pour conserver l'historique. Tu veux calculer uniquement sur les employés actifs visibles.
| A | B | C | |
|---|---|---|---|
| 1 | Employé | Salaire | Statut |
| 2 | Marie | 3 200 € | Actif |
| 3 | Pierre | 2 800 € | Parti (ligne masquée) |
| 4 | Sophie | 3 600 € | Actif |
| 5 | Lucas | 2 900 € | Actif |
| 6 | Moyenne actifs | 3 233 € |
=AGREGAT(1; 5; B1:B4)Le code 1 demande une moyenne et l'option 5 fait ignorer les lignes masquées manuellement (et non celles filtrées) : seuls les actifs visibles (3 200 €, 3 600 € et 2 900 €) sont comptés, soit 3 233 €. Tu gardes ainsi l'historique des partis sans qu'il pèse sur tes statistiques actuelles.
Astuce de pro : Combine AGREGAT avec une mise en forme conditionnelle pour visualiser automatiquement les écarts de salaire par rapport à la moyenne, tout en excluant les anciens employés de tes statistiques.
Envie de t'entraîner sur de vrais exercices Excel ?
M'entraîner
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 sur la fonction AGREGAT
Quelle est la différence entre AGREGAT et SOUS.TOTAL ?
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.
Comment mémoriser les codes de fonction d'AGREGAT ?
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.
AGREGAT peut-elle ignorer les erreurs ET les lignes masquées en même temps ?
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.
Pourquoi AGREGAT renvoie #VALEUR! alors que mes données semblent correctes ?
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!.
Peut-on utiliser AGREGAT avec plusieurs plages comme SOMME ?
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
Les fonctions similaires : SOUS.TOTAL, SIERREUR, SOMME, MOYENNE, GRANDE.VALEUR
Bloqué sur une formule Excel ?
Pose ta question à notre assistant Excel IA, il te sort la bonne formule en quelques secondes.
Essayer l'assistant IAGratuit · 10 questions par mois