Nombre de valeurs constituant une certaine somme

Tibo

XLDnaute Barbatruc

• 3 Novembre 2012

• #2

Re : Nombre de valeurs constituant une certaine somme

Bonsoir,

Un petit bout de fichier exemple permettrait d'illustrer ta demande.

Cela dit, sur la base de ce que j'ai deviné (ou cru), pas évident de trouver une solution formule.

Si elle existe, je suis curieux de la découvrir.

@+

 

Vote positif 0 Vote négatif

D

david84

XLDnaute Barbatruc

• 3 Novembre 2012

• #3

Re : Nombre de valeurs constituant une certaine somme

Bonsoir, salut Tibo,
peut être une combinaison =SOMME(DECALER mais sans fichier...
A+

 

Vote positif 0 Vote négatif

J

jbf

Guest

• 3 Novembre 2012

• #4

Re : Nombre de valeurs constituant une certaine somme

Merci pour vos deux réponses. Voici un bout de fichier...Regarde la pièce jointe Excel-Downloads_JBF.xlsx

 

Pièces jointes

• Excel-Downloads_JBF.xlsx

  10.8 KB · Affichages: 70
• Excel-Downloads_JBF.xlsx

  10.8 KB · Affichages: 70

Vote positif 0 Vote négatif

Jocelyn

XLDnaute Barbatruc

• 3 Novembre 2012

• #5

Re : Nombre de valeurs constituant une certaine somme

Bonjour le Forum,
Bonjour jbf, Tibo🙂, David84🙂,

Un formule a ralonge mais attention elle ne va pas chercher par exemple les données ligne 2 puis ligne 4 etc.. pour se rapprocher elle permet juste d'ettendre la plage a additionner jusqu'a ce que la somme soit trouvée ou dépassée.

Cordialement

 

Pièces jointes

• Excel-Downloads_JBF.xlsx

  11.4 KB · Affichages: 58
• Excel-Downloads_JBF.xlsx

  11.4 KB · Affichages: 64
• Excel-Downloads_JBF.xlsx

  11.4 KB · Affichages: 64

Vote positif 0 Vote négatif

D

david84

XLDnaute Barbatruc

• 3 Novembre 2012

• #6

Re : Nombre de valeurs constituant une certaine somme

Re

Code:

=SI(SOMME(DECALER(A2;;;6))<=SOMME(A2:A11);"pas atteint";"atteint")

au lieu de placer 6 en dur dans la fonction DECALER, tu peux te référer à une liste déroulante et tu pourrais choisir la valeur voulue.
A+

 

Vote positif 0 Vote négatif

R@chid

XLDnaute Barbatruc

Supporter XLD

• 3 Novembre 2012

• #7

Re : Nombre de valeurs constituant une certaine somme

Bonjour @ tous,
Salut Jocelyn,
une variante avec Colonne Intermédiaire,
Amicalement

Edit : Salut David, on a compris la question différemment

 

Pièces jointes

• Excel-Downloads_JBF.xlsx

  11.3 KB · Affichages: 75
• Excel-Downloads_JBF.xlsx

  11.3 KB · Affichages: 72
• Excel-Downloads_JBF.xlsx

  11.3 KB · Affichages: 66

Vote positif 0 Vote négatif

D

david84

XLDnaute Barbatruc

• 3 Novembre 2012

• #8

Re : Nombre de valeurs constituant une certaine somme

Re
sinon, via une fonction personnalisée, peut-être plus facile à mettre en oeuvre sur de grandes plages (à peaufiner le cas échéant mais c'est pour voir si c'est ce que tu atends) :

Code:

Function seuil(Plage As Range) As String
Dim Total As Double, Somme As Double, i As Long
Total = Plage.Cells(Plage.Rows.Count, Plage.Column)
For i = 1 To Plage.Rows.Count - 1
    Somme = Somme + Plage.Cells(i, Plage.Column)
    If Somme >= Total Then seuil = "Somme atteinte ou dépassée en additionnant les " _
    & i & " premières valeurs": Exit Function
Next i
End Function

Code:

=seuil(A2:A12)

A+

 

Vote positif 0 Vote négatif

mapomme

XLDnaute Barbatruc

Supporter XLD

• 3 Novembre 2012

• #9

Re : Nombre de valeurs constituant une certaine somme

Bonjour jbf, Tibo, Jocelyn, david84, R@chid, le forum,

Un essai avec une formule matricielle. Si les valeurs sont en A1:A10, alors la formule matricielle est:

Code:

=EQUIV(VRAI;PRODUITMAT(TRANSPOSE(CNUM(STXT(TRANSPOSE(STXT(REPT("1"; NBVAL(A1:A10));1;LIGNE(INDIRECT("1:" & NBVAL(A1:A10)))) & STXT(REPT("0"; NBVAL(A1:A10));1;NBVAL(A1:A10)-LIGNE(INDIRECT("1:" & NBVAL(A1:A10)))));LIGNE(INDIRECT("1:" & NBVAL(A1:A10)));1)));A1:A10) >=K4;0)

Formule matricielle: Elle doit être validée par la combinaison des touches Ctrl+Maj+Entrée au lieu de la seule touche Entrée comme une formule classique.
Si la validation matricielle est correcte, alors Excel entoure la formule d'accolades {=.......}.
Chaque fois que cette formule sera modifiée, la validation devra se faire par Ctrl+Maj+Entrée. Les accolades apparaissent à la validation et ne doivent pas être saisies au clavier.

Cliquez pour agrandir...

 

Pièces jointes

• Nombre de valeurs constituant une certaine somme V4.xlsx

  11.2 KB · Affichages: 49

Dernière édition: 3 Novembre 2012

Vote positif 0 Vote négatif

Modeste geedee

XLDnaute Barbatruc

• 3 Novembre 2012

• #10

Re : Nombre de valeurs constituant une certaine somme

Bonsour® à tous
aprés passage des illustres prédécesseurs
et au risque d'arriver à la fumée des cierges ...
Alors, juste pour le fun une représentation graphique 😎

 

Pièces jointes

• JBF.xlsx

  18.4 KB · Affichages: 64

Vote positif 0 Vote négatif

R@chid

XLDnaute Barbatruc

Supporter XLD

• 4 Novembre 2012

• #11

Re : Nombre de valeurs constituant une certaine somme

Bonsoir @ tous,
Tout d'abord Chapeau Bas mapomme, malheureusement je n'ai pas le droit qu'en un seul "Like" sinon je vais faire 10 "Like"
Plus simple,

Code:

=EQUIV(SOMME(A2:A11)/2;SOUS.TOTAL(9;DECALER(A2;;;LIGNE(INDIRECT("1:"&NB(A2:A11)))-1)))

@ valider par Ctrl+Maj+Entree
combien de "Like" pour sa petite...😕🙂

Amicalement

 

Dernière édition: 27 Août 2013

Vote positif 0 Vote négatif

mapomme

XLDnaute Barbatruc

Supporter XLD

• 4 Novembre 2012

• #12

Re : Nombre de valeurs constituant une certaine somme

Bonsoir à tous et à R@chid,

-> R@chid,
Alors le coup du sous.total, R@chid, tu nous l'avais pas encore fait !
J'en reste admiratif et sans voix et pourtant j'ai cherché longtemps quelque chose pour transformer une somme de matrices en une matrice de sommes...
Ce post là, je vais le mettre bien au chaud. Cela ouvre pas mal de perspectives.

 

Dernière édition: 4 Novembre 2012

Vote positif 0 Vote négatif

Tibo

XLDnaute Barbatruc

• 4 Novembre 2012

• #13

Re : Nombre de valeurs constituant une certaine somme

Bonjour,

Je suis également très admiratif.

Bravo mille fois Rachid, tu mérites plusieurs Likes comme tu l'avais envisagé pour Mapomme, dont la formule m'avait également scotché.

J'avais déjà eu besoin de solutionner ce problème à titre professionnel et n'avait su faire mieux que de passer par une colonne intermédiaire. Désormais, tu nous a ouvert une nouvelle voie.

Encore Merci.

@+

 

Vote positif 0 Vote négatif

D

david84

XLDnaute Barbatruc

• 4 Novembre 2012

• #14

Re : Nombre de valeurs constituant une certaine somme

Bonjour,
bravo Rachid !
J'avais cherché dans cette voie avec

Code:

=EQUIV(VRAI;SOMME(A2:A11)/2<=SOMME(DECALER(A2;;;LIGNE(INDIRECT("1:"&NB(A2:A11)))));0)

mais cela bloquait (étonnant d'ailleurs a priori car

Code:

=SOMME(A2:A11)/2<=SOMME(DECALER(A$2;;;LIGNE(INDIRECT("1:"&NB(A2:A11)))))

ramène la matrice correcte...), mais je n'avais pas pensé à passer par SOUS.TOTAL.
A+

 

Vote positif 0 Vote négatif

mapomme

XLDnaute Barbatruc

Supporter XLD

• 4 Novembre 2012

• #15

Re : Nombre de valeurs constituant une certaine somme

Bonjour à tous,

Une réduction sensible de la formule du post #9.

La formule matricielle devient (sans atteindre ni la clarté ni la concision de la formule de R@chid):

Code:

=EQUIV(VRAI;PRODUITMAT(--(TRANSPOSE(LIGNE(INDIRECT("1:"&LIGNES(A1:A10))))<=LIGNE(INDIRECT("1:" & LIGNES(A1:A10))));A1:A10)>=K4;0)

 

Pièces jointes

• Nombre de valeurs constituant une certaine somme V5.xlsx

  11.5 KB · Affichages: 45

Dernière édition: 4 Novembre 2012

Vote positif 0 Vote négatif