Fonction AGREGAT ExcelGuide Complet 2026 avec Exemples
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.
Dans ce guide, tu vas découvrir comment utiliser AGREGAT pour des calculs robustes et fiables, même sur des données imparfaites. Fini les formules qui plantent à cause d'une seule erreur !
Syntaxe de la fonction AGREGAT
=AGREGAT(no_fonction; option; ref1; [ref2]; ...)La fonction AGREGAT combine la puissance de 19 fonctions différentes en une seule, avec des options avancées pour gérer les cas complexes. Elle accepte jusqu'à 253 plages de données.
Astuce : AGREGAT est parfaite pour les tableaux de bord automatisés où tu ne peux pas nettoyer les données manuellement à chaque fois. Elle s'adapte automatiquement aux erreurs et aux filtres.
Comprendre chaque paramètre de la fonction AGREGAT
no_fonction
(obligatoire)C'est le type de calcul que tu veux effectuer, représenté par un nombre de 1 à 19. Les plus courants sont : 1 pour MOYENNE, 2 pour NB (comptage), 4 pour MAX, 5 pour MIN, et 9 pour SOMME.
Tu as aussi accès à des fonctions plus avancées comme 14 (GRANDE.VALEUR pour la n-ième plus grande valeur) ou 15 (PETITE.VALEUR). Chaque code correspond à une fonction Excel classique.
option
(obligatoire)C'est ici que la magie opère ! Ce paramètre indique ce qu'AGREGAT doit ignorer dans son calcul. Les options les plus utiles sont :
0: Ne rien ignorer (comportement standard)1: Ignorer les lignes masquées manuellement2: Ignorer les valeurs d'erreur (#N/A, #DIV/0!, etc.)3: Ignorer les lignes masquées ET les erreurs6: Ignorer les erreurs et les valeurs masquées (lignes filtrées comprises)7: Tout ignorer (erreurs, lignes masquées, sous-totaux imbriqués)
ref1
(obligatoire)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 A1.
[ref2], [ref3], ...
(optionnel)Tu peux ajouter jusqu'à 252 plages supplémentaires pour calculer sur des zones non adjacentes. Très pratique pour additionner des colonnes séparées ou faire des moyennes sur plusieurs tableaux.
Note : Pour les fonctions 14 (GRANDE.VALEUR) et 15 (PETITE.VALEUR), le paramètre ref2 devient obligatoire et représente k (le rang recherché, par exemple 3 pour la 3ème plus grande valeur).
Astuce pro : L'option 6 est la plus polyvalente pour 90% des cas d'usage. Elle ignore à la fois les erreurs et les données masquées par un filtre, ce qui rend tes tableaux de bord ultra-robustes.
Exemples pratiques pas à pas
Exemple 1 – 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. Avec AGREGAT, le calcul se fait automatiquement.
Code 9=SOMME, Option 6=ignorer les erreurs. Résultat : 150 000 + 98 000 + 112 000 = 360 000 €
| 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)Avec =SOMME(B1:B4), tu aurais obtenu #N/A. AGREGAT ignore l'erreur et calcule la somme des valeurs valides. Quand Lyon transmettra ses données, le total s'ajustera automatiquement.
Exemple 2 – 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. AGREGAT respecte ton filtre.
Code 1=MOYENNE, Option 6=ignorer les lignes masquées. Moyenne de 5 200 et 6 800 uniquement.
| 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)L'option 6 détecte automatiquement les lignes masquées par ton filtre Excel. Tu peux changer les filtres, AGREGAT s'adapte en temps réel. C'est parfait pour des tableaux de bord dynamiques.
Comparaison : SOUS.TOTAL(101; B1:B3) donnerait le même résultat, mais AGREGAT est plus polyvalente car elle offre 19 fonctions contre 11 pour SOUS.TOTAL.
Exemple 3 – Gestionnaire de stock : agrégation d'inventaire avec valeurs #N/A
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.
Code 4=MAX, Option 6=ignorer les erreurs. Le maximum des valeurs valides est 820.
| 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)Sans AGREGAT, tu devrais utiliser une combinaison complexe de SIERREUR et MAX. Ici, une seule formule suffit. Quand l'entrepôt Sud mettra à jour son inventaire, le calcul s'ajustera automatiquement si nécessaire.
Exemple 4 – RH : calculs de salaires en ignorant les employés partis (lignes masquées)
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 l'historique). Tu veux calculer uniquement sur les employés actifs visibles.
Code 1=MOYENNE, Option 5=ignorer les lignes masquées. Moyenne de 3 200, 3 600 et 2 900 uniquement.
| 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)L'option 5 ignore uniquement les lignes masquées manuellement (pas celles filtrées). Si tu veux ignorer à la fois les lignes masquées ET les filtres, utilise l'option 6 à la place. C'est parfait pour maintenir des données historiques sans qu'elles impactent les calculs actuels.
Astuce RH : 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.
Les erreurs fréquentes et comment les corriger
#VALEUR! : Code de fonction invalide
Si tu obtiens #VALEUR!, c'est souvent que le code de fonction (1er paramètre) est incorrect. Les codes valides vont de 1 à 19. Vérifie que tu n'as pas tapé 0, 20 ou un nombre négatif.
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. Les autres fonctions sont plus rares (médiane, quartiles, etc.).
Argument k manquant pour GRANDE.VALEUR ou PETITE.VALEUR
Les fonctions 14 (GRANDE.VALEUR) et 15 (PETITE.VALEUR) nécessitent un 4ème argument k pour indiquer quel rang tu cherches. Sans cet argument, Excel renvoie une erreur.
Solution : Ajoute le paramètre k : =AGREGAT(14; 6; A1:A100; 3) pour obtenir la 3ème plus grande valeur. Pour les autres fonctions, k est optionnel et sert à ajouter des plages supplémentaires.
Option incorrecte qui ne donne pas le résultat attendu
Si ton calcul inclut des données que tu voulais ignorer, c'est que l'option (2ème paramètre) n'est pas la bonne. Chaque option a un comportement spécifique.
Solution : Pour la plupart des cas, utilise l'option 6 qui ignore à la fois les erreurs et les lignes masquées (même par filtre). Si tu veux être plus sélectif, utilise 1 (lignes masquées uniquement), 2 (erreurs uniquement) ou 3 (les deux mais pas les filtres).
AGREGAT vs SOMME vs MOYENNE vs MAX vs MIN vs SOUS.TOTAL
| Critère | AGREGAT | SOMME | MOYENNE | MAX/MIN | SOUS.TOTAL |
|---|---|---|---|---|---|
| Ignore les erreurs | ✅ Oui (option 2, 3, 6, 7) | ❌ Non | ❌ Non | ❌ Non | ❌ Non |
| Respecte les filtres | ✅ Oui (option 6, 7) | ❌ Non | ❌ Non | ❌ Non | ✅ Oui (codes 101-111) |
| Ignore lignes masquées | ✅ Oui (option 1, 3, 5-7) | ❌ Non | ❌ Non | ❌ Non | ✅ Oui |
| Nombre de fonctions | 🔥 19 fonctions | 1 fonction | 1 fonction | 2 fonctions | 11 fonctions |
| Complexité | ⭐⭐ | ⭐ | ⭐ | ⭐ | ⭐⭐ |
| Meilleur usage | Données avec erreurs | Sommes simples | Moyennes simples | Min/Max simples | Données filtrées |
Utilise AGREGAT quand tes données contiennent des erreurs ou nécessitent des calculs avancés. Pour des calculs simples sur données propres, les fonctions classiques (SOMME, MOYENNE, MAX, MIN) suffisent. SOUS.TOTAL est un bon compromis pour les données filtrées sans erreurs.
Choix rapide : Données parfaites = fonctions classiques. Données avec erreurs = AGREGAT. Données filtrées sans erreurs = SOUS.TOTAL ou AGREGAT. Les deux dernières fonctionnent bien ensemble dans un même tableau.
Questions fréquentes
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 ton choix.
Comment mémoriser les codes de fonction d'AGREGAT ?
Retiens les plus courants : 1=MOYENNE, 2=NB, 4=MAX, 5=MIN, 9=SOMME. Pour les autres (ECARTYPE, GRANDE.VALEUR, etc.), garde une feuille de référence sous la main ou tape =AGREGAT( et Excel te montrera la liste complète.
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, les valeurs masquées manuellement et les lignes filtrées. L'option 7 fait pareil mais ignore aussi les sous-totaux imbriqués. C'est très puissant pour nettoyer tes calculs.
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 4ème argument k pour indiquer le rang.
Peut-on utiliser AGREGAT avec plusieurs plages comme SOMME ?
Oui, pour certaines 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.
Les fonctions similaires à AGREGAT
Deviens un pro d'Excel
Tu maîtrises maintenant AGREGAT ! Rejoins Le Dojo Club pour découvrir toutes les fonctions avancées et devenir ultra-efficace sur Excel, même avec des données complexes.
Essayer pendant 30 jours