XL 2016 Somme des plus grandes valeurs

[Zanku]

Bonjour,

Pour les besoins d'un fichier, j'ai mis au point une formule pour récupérer la somme des x plus grandes valeurs d'une ligne.

Ce qui nous donne ceci :

=SI(NB(B2:AZ2)<=25;SOMME(B2:AZ2);SOMMEPROD((B2:AZ2)*(B2:AZ2>=GRANDE.VALEUR(B2:AZ2;25))))

x étant égal à 25 dans ce cas.

La formule fonctionne très bien à une exception près.
Si la x+1ème valeur est égale à la dernière valeur à additionner, elle est également additionnée.

Dans le cas de notre exemple, si la 25e valeur est égale à 40 et que les 26e et 27e valeurs le sont aussi,
nous n'aurons pas la somme des 25 plus grandes valeurs mais des 27 plus grandes.


Si quelqu'un a une idée pour résoudre ce problème, je suis preneur.

Merci d'avance.

 

[Denis132]

Bonjour,

Peut-être en ajoutant à votre formule quelque chose de semblable:

-(NB.SI(B2:AZ2;PETITE.VALEUR(B2:AZ2;25))-1)*PETITE.VALEUR(B2:AZ2;25)

@+

Denis

 

[Zanku]

Bonjour Denis,

Merci pour cette piste.
ça n'a pas abouti directement mais ça m'a permis de mettre sur la voie.

Dans un premier temps, je me suis attelé à résoudre mon problème initial et j'ai abouti à cette formule.

=SI(NB(A2:AZ2)<=25;SOMME(A2:AZ2);SOMMEPROD((A2:AZ2)*(A2:AZ2>=GRANDE.VALEUR(A2:AZ2;25))))-(SI(GRANDE.VALEUR(A2:AZ2;26)=GRANDE.VALEUR(A2:AZ2;25);GRANDE.VALEUR(A2:AZ2;25)*(NB.SI(A2:AZ2;GRANDE.VALEUR(A2:AZ2;25))-1)))


Elle fonctionne pour le problème rencontré mais m'en a amené un autre.
Si on veut sommer les 25 meilleurs résultats et que les valeurs 24 à 26 sont égales (donc des deux côtés de la limite), alors les valeurs 24 et 25 ne sont pas additionner aux autres.

Voilà donc la formule finale :

=SI(NB(A2:AZ2)<=25;SOMME(A2:AZ2);SOMMEPROD((A2:AZ2)*(A2:AZ2>=GRANDE.VALEUR(A2:AZ2;25)))-SI(GRANDE.VALEUR(A2:AZ2;26)=GRANDE.VALEUR(A2:AZ2;25);GRANDE.VALEUR(A2:AZ2;25)*(NB.SI(A2:AZ2;GRANDE.VALEUR(A2:AZ2;25))-1))+SI(GRANDE.VALEUR(A2:AZ2;26)=GRANDE.VALEUR(A2:AZ2;25);(25-EQUIV(GRANDE.VALEUR(A2:AZ2;25);A2:AZ2;0))*GRANDE.VALEUR(A2:AZ2;25)))


Comme la formule est longue un petit test vaut mieux qu'un grand discours, je vous joins un fichier 🙂

Si quelqu'un trouve un moyen de la raccourcir pour le même résultat je suis preneur ^^

 

[eriiic]

Bonjour,

Je dirais :

=SOMMEPROD(GRANDE.VALEUR(A2:E2;LIGNE($1:$4)))

eric

 

[Chris401]

Bonjour

Une petite variante à la formule d'eriiic pour le cas où il y aurait moins de valeurs que les x plus grandes demandées (exemple, 3 cellules de renseignées et on veut les 4 plus grandes valeurs)

=SOMMEPROD(GRANDE.VALEUR(A2:G2+0;LIGNE($1:$4)))

 

[JHA]

Bonjour à tous,
Bonjour Chris401🙂

Avec ta version d'excel, tu peux également utiliser la fonction agregat()

JHA

 

[david84]

Bonjour,
si les données sont sur la même ligne comme dans ton fichier test :

=SOMMEPROD(GRANDE.VALEUR($A2:$G2*1;COLONNE(A2:D2)))

A+

 

[Chris401]

Re

Effectivement, on peut décliner la façon d'écrire :

A2:G2+0 = A2:G2-0 = $A2:$G2*1 = $A2:$G2/1 Le but étant d'obtenir 0 dans les cellules vides

LIGNE($1:$4) = COLONNE($A$2 : $D$2 = COLONNE(A : D) Le but étant d'obtenir {1\2\3\4}

 

[Zanku]

Bonjour à tous.

Je n'ai pas eu beaucoup de temps pour passer dernièrement.
Merci pour vos réponses.

ça fonctionne exactement comme je le voulais et avec une formule nettement moins longue 🙂