Formule générique
=SUMPRODUCT(LARGE(rng,(1,2,N)))
Sommaire
Pour additionner les valeurs supérieures d'une plage, vous pouvez utiliser une formule basée sur la fonction LARGE, encapsulée dans la fonction SUMPRODUCT. Dans 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:
=SUMPRODUCT(LARGE(B4:B13,(1,2,3)))
Explication
Dans sa forme la plus simple, LARGE renverra la valeur «Nième plus grande» d'une plage. Par exemple, la formule:
=LARGE(B4:B13, 2)
renverra la deuxième plus grande valeur de 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,(1,2,3))
renverra la 1ère, la 2ème et la 3ème valeur la plus élevée 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, qui est de 27.
SUM au lieu de SUMPRODUCT
SUMPRODUCT est une fonction flexible qui vous permet d'utiliser des références de cellule pour k dans la fonction LARGE.
Cependant, si vous utilisez une simple constante de tableau codée en dur comme (1, 2, 3), vous pouvez simplement utiliser la fonction SOMME:
=SUM(LARGE(B4:B13,(1,2,3)))
Notez que vous devez entrer cette formule sous forme de formule matricielle si vous utilisez des références de cellule et non une constante matricielle pour k dans LARGE.
Quand N devient grand
Lorsque N devient grand, il devient fastidieux de créer la constante de tableau à la main - Si vous souhaitez additionner les 20 ou 30 premières valeurs d'une grande liste, la saisie d'une constante de tableau avec 20 ou 30 éléments prendra beaucoup de temps. Dans ce cas, vous pouvez utiliser un raccourci pour créer la constante de tableau qui utilise les fonctions ROW 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:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:20"))))
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:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:"&MIN(3,COUNT(rng))))))
Ici, nous utilisons MIN avec COUNT pour additionner les 3 premières valeurs, ou le nombre de valeurs, s'il est inférieur à 3.
