Monsieur Excel
Pour tout savoir faire sur Excel !

22 octobre 2009

Somme des cinq grandes valeurs

Dans l’exemple ci-dessous, nous voulons calculer en E1 la somme des cinq plus grandes valeurs de la colonne B.

Pour plus de clarté, nous avons mis en relief dans notre modèle toutes les cellules avec les cinq plus grandes valeurs. Nous constatons un problème sur cet exemple : il y a en fait deux valeurs à 15, la cinquième plus grande valeur. Si notre formule fait la somme de toutes les valeurs supérieures ou égales à la cinquième plus grande valeur, le total sera trop grand de 15 unités.

La formule à utiliser en E1, pour éviter ce problème, est la suivante :
=somme(si(B2:B17+ligne(B2:B17)/10^12>=grande.valeur(B2:B17+ligne(B2:B17)/ 10^12;5);B2:B17;0))

C’est une formule matricielle, à valider donc avec [Ctrl]-[Maj]-[Entrée].

L'astuce fondamentale de cette formule, c'est qu’en ajoutant le numéro de ligne divisé par 10^12, nous évitons tout ex æquo possible.

Une autre formule possible, non matricielle, que nous avons saisie en E2, fait intervenir la fonction SommeProd :
=sommeprod(--(B2:B17+ligne(B2:B17)/10^12>=grande.valeur(B2:B17+ligne(B2:B17)/ 10^12;H19));(B2:B17))

Remarque – Comme nous l’avons déjà montré avec d’autres exemples, le "--" peut aisément être remplacé par "1*". De même, le dernier ";" peut lui aussi être remplacé par un "*".

7 Commentaire(s):

  • Voilà le genre de formules que je me refuse à installer chez mes utilisateurs : ils ne seront jamais capables de les comprendre !
    Alors tant qu'à ne pas comprendre, je préfère de loin leur donner la "Function" VBA suivante npgv (n plus grandes valeurs) :

    Function npgv(r As Range, n As Integer)
    Dim i As Integer
    For i = 1 To n
    npgv = npgv + Application.WorksheetFunction.Large(r, i)
    Next i
    End Function

    L'utilisation dans Excel s'écrit donc très simplement : =npgv(B2:B17;5)

    J'ajoute que la formule "tout Excel" reproduite à 10000 exemplaires génère un classeur de 1,72 Mo mais descend à 552Ko si on utilise la "Function".

    By Anonymous cduigou, sur 10:33 PM  

  • JHA,
    Pourquoi tant de complication:

    =SOMME(GRANDE.VALEUR(B2:B17;{1;2;3;4;5}))
    donne le même résultat

    By Anonymous Anonyme, sur 10:50 AM  

  • Merci pour vos contributions !

    Je n'avais pas pensé en particulier à mettre un argument matriciel à Grande.Valeur.

    Ce qui est original, c'est que cette formule n'exige pourtant pas une validation matricielle :)

    By Blogger Hervé Thiriez, sur 10:58 PM  

  • Inconvénient de la solution d'Anonyme : tout va bien tant qu'il s'agit des 5 premières valeurs, mais supposons qu'on ait besoin des 50 premières...

    By Anonymous cduigou, sur 8:57 AM  

  • Je suis sûr que, dans ce cas, on pourra vous faire confiance pour écrire la macro qui créera la formule :)

    Au fait, qu'est-ce que cette solution vous donne en place mémoire ?

    By Blogger Hervé Thiriez, sur 10:33 AM  

  • Pourquoi tant de complications quand il suffit de faire un sumif avec comme critere d etre plus grand que le n i em max

    =sumif(A1:A50,">="&large(A1:A50,C2),A1:A50))

    mettre en C2 le nombre de max desire comme un input ou rentrer une valeur a la place de C2 dans la formule

    By Anonymous Anonyme, sur 5:35 PM  

  • Votre solution est certes plus simple, mais elle ne résout pas du tout le problèmes des ex aequo en dernière position. Ma formule résout ce problème.

    Exemple : les plus grandes valeurs sont 9, 9, 6 et 6. Vous demandez la somme des 3 plus grandes : votre formule donne 30 comme résultat au lieu de 24 !

    By Blogger Hervé Thiriez, sur 6:58 PM  

Enregistrer un commentaire

<< Accueil