Formule générique
=SUMPRODUCT(SMALL(rng,(1,2,n)))
Sommaire
Pour additionner les n valeurs les plus faibles d'une plage, vous pouvez utiliser une formule basée sur la fonction SMALL et 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 le nombre de valeurs les plus faibles à additionner. Dans l'exemple illustré, E5 contient cette formule:
=SUMPRODUCT(SMALL(B4:B14,(1,2,3)))
qui renvoie la somme des trois plus petites valeurs de B5: B14, 60
Explication
Dans sa forme la plus simple, SMALL renverra la "nième plus petite" valeur d'une plage. Par exemple:
=SMALL(range,1) // smallest =SMALL(range,2) // 2nd smallest =SMALL(range,3) // 3rd smallest
Cependant, si vous fournissez une constante de tableau (par exemple une constante de la forme (1, 2, 3)) à SMALL comme deuxième argument, SMALL renverra un tableau de résultats au lieu d'un seul résultat. Par exemple:
=SMALL(A1:A10,(1,2,3))
renverra les 1ère, 2ème et 3ème plus petites valeurs dans la plage A1: A10.
En travaillant de l'intérieur vers l'extérieur dans l'exemple illustré, SMALL renvoie les 3 plus petites valeurs de la plage B5: B14:
=SMALL(B4:B14,(1,2,3))
Le résultat est un tableau comme celui-ci:
(10,20,30)
Ce tableau est renvoyé directement à la fonction SUMPRODUCT, qui additionne les nombres et renvoie le total:
SUMPRODUCT((10,20,30)) // returns 60
Formule matricielle avec SUM
Il est courant d'utiliser SUMPRODUCT comme ci-dessus car il peut gérer les tableaux de manière native sans entrer en tant que formule matricielle. Cependant, vous pouvez également écrire une formule matricielle avec la fonction SOMME comme ceci:
(=SUM(SMALL(B4:B13,(1,2,3))))
Il s'agit d'une formule matricielle et doit être entrée avec Ctrl + Maj + Entrée, sauf dans Excel 365.
Quand n devient grand
Lorsque n devient grand, il devient fastidieux de créer la constante de tableau à la main - taper 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, pour faire la somme des 20 dernières valeurs d'une plage appelée "rng", vous pouvez écrire une formule comme celle-ci:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:20"))))
Ici, INDIRECT convertit la chaîne "1:20" dans la plage 1:20, qui est renvoyée directement à SMALL.
Variable n
Pour configurer la formule a où n est une variable dans une autre cellule, vous pouvez concaténer dans INDIRECT. Par exemple, si A1 contient N, vous pouvez utiliser:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:"&A1))))
Cela permet à un utilisateur de modifier la valeur de n directement sur la feuille de calcul.
