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