Comment ignorer les erreurs dans une somme ?

Utilisez =AGGREGATE(9,6,range). L’option 6 exclut les cellules qui contiennent des erreurs.

Quelle différence existe-t-il entre AGREGAT et SOUS.TOTAL ?

AGGREGATE peut ignorer les erreurs et propose 19 fonctions, dont MEDIAN et LARGE. SUBTOTAL n’en compte que 11 et ne sait pas exclure les erreurs.

Que signifient les options ?

0, ou une valeur omise, ignore les sous-totaux imbriqués ; 1 ignore aussi les lignes masquées ; 2, les erreurs ; 3, les deux ; 4 n’ignore rien ; 5 ignore les lignes masquées ; 6, les erreurs ; 7, les lignes masquées et les erreurs.

Toutes les fonctions

Math

AGREGAT (AGGREGATE) dans Excel

AGREGAT (AGGREGATE) expliquée avec ses 19 fonctions, les options d’exclusion, les erreurs, l’argument k et trois exemples.

Syntaxe

AGGREGATE(function_num, options, ref, [k])

Exemple : =AGGREGATE(9,6,C2:C100)

Disponible dans : Excel 2010+

AGREGAT exécute une opération d’agrégation avec une option permettant d’ignorer certaines lignes ou erreurs. Sa syntaxe est =AGGREGATE(function_num, options, array, [k]).

Quand l’utiliser

Additionner une colonne contenant des cellules #N/A ou #DIV/0! sans les nettoyer au préalable : =AGGREGATE(9,6,C2:C100).
Trouver la k-ième plus grande ou plus petite valeur en ignorant les erreurs, par exemple =AGGREGATE(14,6,A2:A100,3).
Remplacer SUBTOTAL lorsque vous avez aussi besoin de fonctions telles que MEDIAN ou PERCENTILE uniquement sur les lignes visibles.

Arguments

function_num. Un code qui sélectionne l’opération d’agrégation.
options. Un code qui détermine quels totaux imbriqués, lignes masquées et erreurs sont ignorés.
array. La plage ou le tableau à calculer.
k. Argument requis par des opérations telles que LARGE, SMALL et les centiles.

Exemples expliqués

Additionner toutes les valeurs

	A
1	Value
2	2
3	4
4	6
5	8

Formule : C2: =AGGREGATE(9,4,A2:A5)

Résultat : 20

La fonction 9 correspond à SUM ; l’option 4 n’ignore rien.

Ignorer une erreur

	A
1	Amount
2	3
3	=1/0
4	4

Formule : C2: =AGGREGATE(9,6,A2:A4)

Résultat : 7

L’option 6 ignore la cellule #DIV/0!. SUM(A2:A4) renverrait #DIV/0!.

Deuxième plus grande valeur

	A
1	Value
2	2
3	4
4	6
5	8

Formule : C2: =AGGREGATE(14,4,A2:A5,2)

Résultat : 6

La fonction 14 correspond à LARGE et k=2 demande la deuxième plus grande valeur.

Formules dans Excel en français

Remarque pour Excel en français : les versions françaises d’Excel attendent des noms de fonctions français et, en général, le point-virgule comme séparateur d’arguments. Les formules anglaises affichées ici se saisissent donc ainsi chez vous, par exemple =RECHERCHEV(A2;D:E;2;FAUX) au lieu de =VLOOKUP(A2,D:E,2,FALSE). Les valeurs VRAI et FAUX remplacent TRUE et FALSE, et les erreurs s’affichent #VALEUR!, #NOMBRE! ou #NOM? au lieu de #VALUE!, #NUM! ou #NAME? ; #N/A et #REF! restent inchangées. Dans la feuille interactive de cette page, c’est la syntaxe anglaise qui s’applique.

Fonctions présentes sur cette page (anglais → français) :

AGGREGATE → AGREGAT

Une formule de cette page en syntaxe française : =AGGREGATE(9,4,A2:A5) devient =AGREGAT(9;4;A2:A5).

À vous de jouer

Modifiez une formule ou une valeur dans la feuille d’exemple et observez le résultat.

Formule en F2 : =AGGREGATE(9,6,C2:C7)

Feuille d’exemple : AGGREGATE
	A	B	C	D	E	F
1	Region	Product	Units	Price		Result
2	East	Pen	120	2.5		350
3	West	Pen	80	2.5
4	East	Book	45	12
5	North	Book	60	12
6	West	Lamp	30	25
7	East	Lamp	15	25

Résultat en F2 : 350

Erreurs et vérifications

Mauvais numéro de fonction. Les nombres 1 à 19 sélectionnent la fonction : 1 AVERAGE, 4 MAX, 9 SUM, 12 MEDIAN et 14 LARGE. Un nombre hors de cette liste, tel que 99, renvoie #VALUE!.
Argument k manquant. Les fonctions 14 à 19 (LARGE, SMALL, centiles et quartiles) exigent le quatrième argument ; Excel n’accepte pas la formule sans lui.
#NUM! lorsque k est trop grand. AGGREGATE(14,6,A2:A4,5) renvoie #NUM! si moins de cinq valeurs subsistent après l’exclusion des erreurs.
Des erreurs apparaissent encore. L’option 4 n’ignore rien. Utilisez 6 pour ignorer les valeurs d’erreur, 5 pour les lignes masquées et 7 pour les deux.

Fonctions associées

Questions fréquentes

Comment ignorer les erreurs dans une somme ?: Utilisez =AGGREGATE(9,6,range). L’option 6 exclut les cellules qui contiennent des erreurs.
Quelle différence existe-t-il entre AGREGAT et SOUS.TOTAL ?: AGGREGATE peut ignorer les erreurs et propose 19 fonctions, dont MEDIAN et LARGE. SUBTOTAL n’en compte que 11 et ne sait pas exclure les erreurs.
Que signifient les options ?: 0, ou une valeur omise, ignore les sous-totaux imbriqués ; 1 ignore aussi les lignes masquées ; 2, les erreurs ; 3, les deux ; 4 n’ignore rien ; 5 ignore les lignes masquées ; 6, les erreurs ; 7, les lignes masquées et les erreurs.