Christian a une plage de 18 cellules contenant des valeurs. Il aimerait trouver la somme des 12 valeurs les plus élevées de cette plage et se demande comment accomplir la tâche.

Il existe plusieurs façons d’aborder ce problème. Vous pouvez, par exemple, filtrer les valeurs pour ne disposer que des douze premières valeurs, puis les additionner. D’autres approches impliquent l’utilisation de colonnes supplémentaires pour stocker des valeurs intermédiaires, mais je suppose que vous préféreriez une approche qui n’utilise pas de colonnes supplémentaires.

Pour commencer, supposons que votre plage de 18 cellules soit A1: A18. Vous pouvez utiliser la fonction LARGE pour rechercher les valeurs les plus élevées. Par exemple, l’utilisation de cette formule trouverait la deuxième valeur la plus élevée de la plage:

=LARGE(A1:A18,2)

C’est le deuxième paramètre de la fonction qui spécifie la plus grande valeur, dans l’ordre, vous voulez. Ainsi, vous pouvez trouver la somme des 12 plus grandes valeurs en utilisant une formule comme celle-ci:

=LARGE(A1:A18,1)+LARGE(A1:A18,2)+LARGE(A1:A18,3)+LARGE(A1:A18,4)

+LARGE(A1:A18,5)+LARGE(A1:A18,6)+LARGE(A1:A18,7)+LARGE(A1:A18,8)

+LARGE(A1:A18,9)+LARGE(A1:A18,10)+LARGE(A1:A18,11)+LARGE(A1:A18,12)

Il existe cependant des formules plus courtes que vous pouvez utiliser pour accomplir la tâche.

Par exemple, vous pouvez simplement soustraire les six plus petites valeurs de la somme de la plage, de cette manière:

=SUM(A1:A18)-SMALL(A1:A18,1)-SMALL(A1:A18,2)-SMALL(A1:A18,3)

-SMALL(A1:A18,4)-SMALL(A1:A18,5)-SMALL(A1:A18,6)

Vous pouvez également, si vous le souhaitez, utiliser la fonction SUMIF pour effectuer une comparaison des valeurs et les additionner uniquement si le critère que vous spécifiez est satisfait. Par exemple, considérons ces deux formules:

=SUMIF(A1:A18,">="&LARGE(A1:A18,12))

=SUMIF(A1:A18,">"&SMALL(A1:A18,6))

La première formule additionnera toutes les valeurs supérieures ou égales à la douzième plus grande valeur de la plage. Le second est similaire en effet; il additionne toutes les valeurs supérieures à la sixième plus petite valeur.

Ces deux formules fonctionnent très bien s’il n’y a pas de valeurs en double à la «limite» établie. Cependant, si plusieurs valeurs sont considérées comme la douzième plus grande ou la sixième plus petite, les formules ne renverront pas les sommes attendues. Dans la première formule, la somme sera trop grande (puisque toutes les valeurs en double sont ajoutées à la somme) et la deuxième formule la somme sera trop petite (puisque toutes les valeurs en double sont exclues de la somme).

La solution consiste soit à revenir à l’une des formules précédentes (celles qui n’utilisent pas SUMIF), soit à modifier la formule SUMIF afin qu’elle prenne en compte la possibilité de doublons de valeurs:

=SUMIF(A1:A18,">"&LARGE(A1:A18,12))+LARGE(A1:A18,12)

* (12-COUNTIF(A1:A18,">"&LARGE(A1:A18,12)))

Peut-être que la meilleure formule pour obtenir le résultat souhaité remet en question l’utilisation antérieure de la fonction LARGE:

=SUM(LARGE(A1:A18,{1,2,3,4,5,6,7,8,9,10,11,12}))

Cette formule utilise un tableau (la partie entre accolades), mais ce n’est pas une formule matricielle. Il utilise le tableau comme deuxième paramètre de la fonction LARGE, renvoyant ainsi les douze valeurs les plus grandes. Celles-ci sont ensuite additionnées et une seule valeur est renvoyée.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (9422) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365. Vous pouvez trouver une version de cette astuce pour l’ancienne interface de menu d’Excel ici:

link: / excel-Summing_Only_the_Largest_Portion_of_a_Range [Additionner uniquement la plus grande partie d’une plage].