Caculer une somme tant que le résultat < une valeur

Bonjour,

Je voudrais savoir s'il est possible de calculer la somme d'une colonne de valeur, en définissant la plage automatiquement pour que la somme arrive au plus prés d'une valeur sans la dépasser, et de reporter le résultat sur une autre colonne à la ligne correspondante ?

Merci beaucoup

AlexM

Oui c'est possible ... mets nous un bout de fichier, ce sera plus simple pour te donner la solution.

Comment peut-on joindre un fichier ?

En fait il faut une colonne A avec des valeurs par exemple comprises entre 2,1 et 2,2 et dans colonne B la valeur max de la somme prenant en compte X cases en partant de la ligne correspondante.

Par exemple en B1 on aurait =somme(A1:AX)<35, en B2 =somme(A2:AX)<35 et ainsi de suite.

Merci

AlexM


49classeur1.xlsx (27.82 Ko)

bonjour

un essai pour amorcer ; sinon il faut jouer du solveur ....................

56alex1111.xlsx (8.94 Ko)

cordialement

Bonjour Tulipe, Merci pour votre réponse, cependant je ne comprend pas bien ce que vous avez fait.

Le but étant que chaque case de la colonne B s'approche le plus prés de la valeur cible peu importe le nombre de case de la colonne A a sommer.

Cordialement

AlexM

re

puisque tu parlais de SOMME mon truc essaie de faire la somme de ce qu'il y a dans la colonne A jusquà cela corresponde apeu près a la valeur cible

c'est pas çà que tu voulais ??

sinon met une piece jointe

cordialement

Ah oui ok ! Alors oui c'est ca, mais il faudrait dans chaque case de la colonne B il y ait le résultat (valeur proche de la valeur cible) de la somme des valeurs de la colonne A en partant de la même ligne.

PS : J'ai mis une pièce jointe dans un de mes messages précedent.

Cordialement

AlexM

re

eh bien ça le fait ;pour tester modifie la valeur cilble (en bleu) met par exemple 17

52alex11112.xlsx (9.31 Ko)

je t'ai mis une colonne en orange qui indique les valeurs utilisées

coordialement

Re tulipe,

Merci de vous donner du mal pour mon problème^^ mais ce n'est pas ca qu'il me faut^^ Regardez le fichier que j'ai uploadé plus haut vous allez dessuite comprendre

Cordialement

AlexM

Bonjour,

Sur la base de ton fichier, avec la valeur 35 en I7 par exemple :

=SOMMEPROD(DECALER(I9;0;0;EQUIV($I$7;PRODUITMAT(LIGNE($I$9:$I$73)>=TRANSPOSE(LIGNE($I$9:$I$73));$I$9:$I$73);1)))

Cette formule ne fonctionne que si ta liste de données ne contient pas plus de valeurs qu'un fichier Excel est susceptible d'avoir de colonnes.

A+

Bonjour Ooiste,

Ca à l'air super complexe ta formule. je l'ai copié en colonne H et j'ai mis 35 en I7 et le résultat est "#VALEUR!" :/ Une idée ?

AlexM

J'utilise LibreOffice et j'avais oublié que Excel ne convertit pas automatiquement les VRAI/FAUX en 1/0...

En utilisant la fonction N() dans le premier argument de PRODUITMAT, cela devrait le faire :

=SOMMEPROD(DECALER(I9;0;0;EQUIV($I$7;PRODUITMAT(N(LIGNE($I$9:$I$73)>=TRANSPOSE(LIGNE($I$9:$I$73)));$I$9:$I$73);1)))

Humm non toujours pareil, ca ne change rien :/

AlexM

bonjour

je me demande si il ne faut pas selectionner la plage ; avant de valider en matriciel (Ctrl Maj Entée) les 3 touches en meme temps

parce que avec produit mat et transpose .........

cordialement

tulipe_4 a écrit :

bonjour

je me demande si il ne faut pas selectionner la plage ; avant de valider en matriciel (Ctrl Maj Entée) les 3 touches en meme temps

parce que avec produit mat et transpose .........

cordialement

Non il ne faut pas sélectionner la plage, la formule renvoie un résultat unique. On recopie après vers le bas.

Par contre, tu as peut-être raison pour la validation matricielle. Elle n'est pas utile avec Calc, mais il est possible qu'elle soit nécessaire avec Excel à cause de TRANSPOSE.

Dans ce cas, on peut utiliser aussi bien SOMME que SOMMEPROD.

Ok et donc je dois faire quoi du coup ?

AlexM

re

du coup ;

1) tu te prepares 3sdoigts

2)tu te mets sur la 1ere cellule ou tu a copier coller la formule de oooiste

3) tu vas a l'extremité droite de la dernière parenthese (comme si tu voulais continuer a saisir )

4) tu appuies sur les touches :Ctrl la fleche majuscule et Entrée :chacune avec un doigt et simultanement

normalement les { } se mettent ;preuve que tu as reussi l'exercice

5) la cell ainsi validée ,tu la tires vers le bas avec la petite croix (coin inf droit) ;jusqu'au bout de la colonne des valeurs

avec de telles explications tu n'as plus le droit à l'erreur

cordialement

Explication au top ! :p mais maintenant j'ai #N/A....... :'(

AlexM


Non c'est bon ca marche j'avais oublié de mettre 35 en I7^^

Cependant ! Je pense qu'il y a un bug car tout dépend de ce que je met dans les cases valeurs, le résultat peut etre soit au dela de 35 soit bien en dessous... :/ Est ce que vous pouvez m'expliquer cette formule ? Merci

AlexM

AlexM a écrit :

Explication au top ! :p mais maintenant j'ai #N/A....... :'(

AlexM


Non c'est bon ca marche j'avais oublié de mettre 35 en I7^^

Cependant ! Je pense qu'il y a un bug car tout dépend de ce que je met dans les cases valeurs, le résultat peut etre soit au dela de 35 soit bien en dessous... :/ Est ce que vous pouvez m'expliquer cette formule ? Merci

AlexM

Le résultat de la formule est soit égal à I7 soit inférieur, il ne peut en aucun cas être supérieur.

Donc je ne vois pas trop ce qui ne fonctionne pas.

Si tu joignais un fichier avec un résultat incorrect pour que je vois ?

La formule va être difficile à expliquer.

=SOMMEPROD(DECALER(I9;0;0;EQUIV($I$7;PRODUITMAT(N(LIGNE($I$9:$I$73)>=TRANSPOSE(LIGNE($I$9:$I$73)));$I$9:$I$73);1)))

PRODUITMAT(N(LIGNE($I$9:$I$73)>=TRANSPOSE(LIGNE($I$9:$I$73)));$I$9:$I$73) renvoie la matrice des cumuls de valeurs de la plage de données.

Pour simplifier, écris =SOMME($I$8:I9) en J9 et recopies vers le bas.

Ce que tu obtiens en colonne J est la même chose que le résultat de PRODUITMAT.

EQUIV recherche alors la position de 35 (ou I7) dans cette matrice ordonnée.

SOMME(DECALER(I9)) fait donc la somme de I9 à la ligne renvoyée par EQUIV.

Voilà.

Oups, en répondant je viens de me rendre compte que j'ai oublié de mettre I9 en relatif.

Correction :

=SOMMEPROD(DECALER(I9;0;0;EQUIV($I$7;PRODUITMAT(N(LIGNE(I9:$I$73)>=TRANSPOSE(LIGNE(I9:$I$73)));I9:$I$73);1)))

Je te met le fichier en pièce jointe !

AlexM

5classeur1.xlsx (28.50 Ko)
Rechercher des sujets similaires à "caculer somme tant que resultat valeur"