Exceller

Somme des n premières valeurs

Sum Top N Values

Formule Excel : additionner les n valeurs supérieuresFormule générique |_+_| Sommaire

Pour additionner les valeurs supérieures d'une plage, vous pouvez utiliser une formule basée sur la fonction LARGE, enveloppée à l'intérieur de la fonction SOMMEPROD. Sous la forme générique de la formule (ci-dessus), rng représente une plage de cellules contenant des valeurs numériques et N représente l'idée de la Nième valeur.





Dans l'exemple, la cellule active contient cette formule :

formule pour ajouter plusieurs cellules dans Excel
= SUMPRODUCT ( LARGE (rng,{1,2, N }))
Explication

Dans sa forme la plus simple, LARGE renverra la 'Nième plus grande' valeur dans une plage. Par exemple, la formule :





 
= SUMPRODUCT ( LARGE (B4:B13,{1,2,3}))

renverra la 2ème plus grande valeur dans la plage B4:B13 qui, dans l'exemple ci-dessus, est le nombre 9.

Cependant, si vous fournissez une « constante de tableau » (par exemple, une constante sous la forme {1,2,3}) à LARGE comme deuxième argument, LARGE renverra un tableau de résultats au lieu d'un seul résultat. Donc la formule :



 
= LARGE (B4:B13, 2)

renverra la 1ère, la 2ème et la 3ème plus grande valeur dans la plage B4:B13. Dans l'exemple ci-dessus, où B4:B13 contient les nombres 1-10, le résultat de LARGE sera le tableau {8,9,10}. SUMPRODUCT additionne ensuite les nombres de ce tableau et renvoie un total de 27.

SOMME au lieu de SOMMEPROD

SUMPRODUCT est une fonction flexible qui vous permet d'utiliser des références de cellule pour k à l'intérieur de la fonction LARGE.

Cependant, si vous utilisez un simple constante matricielle comme {1,2,3}, vous pouvez simplement utiliser la fonction SOMME :

 
= LARGE (B4:B13,{1,2,3})

Notez que vous devez entrez cette formule sous forme de formule matricielle si vous utilisez des références de cellules et non une constante matricielle pour k à l'intérieur de LARGE.

Quand N devient grand

Lorsque N devient grand, il devient fastidieux de créer la constante matricielle à la main. Si vous souhaitez additionner les 20 ou 30 premières valeurs d'une grande liste, la saisie d'une constante matricielle avec 20 ou 30 éléments prendra beaucoup de temps. Dans ce cas, vous pouvez utiliser un raccourci pour créer la constante matricielle qui utilise les fonctions LIGNE et INDIRECT.

Par exemple, si vous souhaitez additionner les 20 premières valeurs d'une plage appelée « rng », vous pouvez écrire une formule comme celle-ci :

 
= SUM ( LARGE (B4:B13,{1,2,3}))

Variable N

Avec des données insuffisantes, un N fixe peut provoquer des erreurs. Dans ce cas, vous pouvez essayer une formule comme celle-ci :

comment créer une ligne sparkline dans Excel
 
= SUMPRODUCT ( LARGE (rng, ROW ( INDIRECT ('1:20'))))

Ici, nous utilisons MIN avec COUNT pour additionner les 3 premières valeurs, ou le nombre de valeurs, s'il est inférieur à 3.

Auteur Dave Bruns


^