Formule générique
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Sommaire
Pour additionner les n premières valeurs dans une plage de critères de correspondance, 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), la plage représente une plage de cellules comparées à des critères , les valeurs représentent les valeurs numériques à partir desquelles les valeurs supérieures sont extraites et N représente l'idée de la Nième valeur.
Dans l'exemple, la cellule active contient cette formule:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Où la couleur est la plage nommée B5: B12 et la valeur est la plage nommée C5: C12.
Explication
Dans sa forme la plus simple, LARGE renvoie la valeur "Nième plus grande" dans une plage avec cette construction:
=LARGE (range,N)
Ainsi, par exemple:
=LARGE (C5:C12,2)
renverra la deuxième plus grande valeur dans la plage C5: C12, qui est 12 dans l'exemple illustré.
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 (C5:C12, (1,2,3))
renverra la 1ère, 2ème et 3ème plus grande valeur C5: C12 dans un tableau comme celui-ci: (12,12,10)
L'astuce ici est donc de filtrer les valeurs en fonction de la couleur avant que LARGE ne s'exécute. Nous faisons cela avec l'expression:
(color=E5)
Ce qui donne un tableau de valeurs TRUE / FALSE. Lors de l'opération de multiplication, ces valeurs sont converties en uns et en zéros:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Le résultat final est donc que seules les valeurs associées à la couleur "rouge" survivent à l'opération:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
et les autres valeurs sont forcées à zéro.
Remarque: cette formule ne gère pas le texte dans la plage de valeurs. Voir ci-dessous.
Gestion du texte dans les valeurs
Si vous avez du texte n'importe où dans les plages de valeurs, la fonction LARGE générera une erreur #VALUE et arrêtera la formule de fonctionner.
Pour gérer le texte dans la plage de valeurs, vous pouvez ajouter la fonction IFERROR comme ceci:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Ici, nous interceptons les erreurs de LARGE causées par des valeurs de texte et les remplaçons par zéro. L'utilisation de IF à l'intérieur de LARGE nécessite que la formule soit entrée avec Ctrl + Maj + Entrée, nous passons donc à SUM au lieu de SUMPRODUCT.
Remarque: je suis tombé sur cette formule publiée par l'incroyable Barry Houdini sur stackoverflow.
