XL 2013 Additionner les cellules pour arriver à un montant donné

Nbita · 18 Juin 2020

Bonjour à tous,

J'espère que vous allez bien.

Je me.permets de vous solliciter à nouveau car vous m'aviez été d'une aide précieuse la première fois et je vous en remercie !!

J'ai une extraction Excel qui contient une colonne de divers montants sur plusieurs lignes.

On me demande d'aditionner les différents montants jusqu'à totaliser un certain montant.

Je dois m'y approcher le plus possible.

Alors je sais qu'en sélectionnant et en faisant défiler les cellules vers le bas, nous avons un sous total sur le bas droit de la feuille qui nous calcule automatiquement les cellules sélectionnées.

Mais là, il me faut le montant exact où sinon le plus proche possible du montant donné.

J'aurais donc voulu savoir s'il existait une fonction me permettant de dire a excel: trouve toutes les cellules XY se rapprochant ou exact à la cellule À

Je vous joins le fichier en question.

Merci infiniment d'avance pour vos conseils.

Portez vous bien.

Cordialement
Nbita

JHA · 18 Juin 2020

Bonjour à tous,

A essayer en "C5"

VB:

=SI(SOMME(B$5:B5)>$B$2;"";SOMME(B$5:B5))

copier vers le bas

JHA

Nbita · 18 Juin 2020

JHA à dit:
Bonjour à tous,

A essayer en "C5"

VB:

=SI(SOMME(B$5:B5)>$B$2;"";SOMME(B$5:B5))

copier vers le bas

JHA

Merci beaucoup JHA pour ta réponse et ta réactivité !

La subtilité est que je peux choisir les cellules que je veux , dans n'importe quel ordre mais j'aurais voulu qu'excel.m'aide.a les trouver au lieu d'appuyer sur CTRL et de recommencer juste pour essayer avec telle ou telle cellule....

eriiic · 19 Juin 2020

Bonjour,

Avec plusieurs dizaines de valeurs ça risque de durer un peu...
Ca ne peut être fiable que s'il ne trouve qu'une seule possibilité, et si tu le laisses tout explorer.
eric

mapomme · 19 Juin 2020

Bonsoir à @Nbita, @JHA , @eriiiic ,

En utilisant le complément solveur intégré dans Excel, on trouve des solutions après une à deux minutes de calcul.

Voir le fichier joint qui contient l'utilisation du solver avec :

Les coefficients à trouver par le solveur (C5:C82)
Les contraintes (C5:C82 entiers et compris entre 0 et 1)
La méthode utilisée (Evolutionary)
La cellule cible C2 avec la formule : =ABS(SOMMEPROD( B5:B82;C5:C82)-B2) que le solveur cherchera à minimiser

La colonne O montre une autre solution trouvée par le solver que celle de la colonne C.

eriiic · 19 Juin 2020

Bonjour,

le pb c'est que le solveur s'arrête à la 1ère solution trouvée.
Selon le problème il peut être important de savoir si elle est unique.
par exemple, il est difficilement pensable d'attribuer le paiement d'une facture au hasard.
eric

jmfmarques · 19 Juin 2020

Bonjour
Ce sujet a été traité plusieurs fois sur ce forum, avec toujours la même conclusion de non fiabilité comptable
je ne crois pas que le traiter une fois de plus soit de nature à y changer quoi que ce soit.

Nbita · 19 Juin 2020

Bonjour a tous et merci pour vos contributions.
mapomme a trouvé une solution qui semble fonctionner.
Étant donné que je ne cherche qu'une seule solution, cela n'est pas un problème qu'il ne me donne pas de.choix puisque mon calcul est finalement aléatoire pourvu simplement que je puisse sélectionner les lignes repondant à la somme recherchée.

Je suis en train d'essayer avec un autre cas pour valider définitivement cette solution.

Mais a première vue vous avez encore résolu mon problème.

Encore merci pour votre aide. Je reviendrai quand même vers vous pour confirmer que cela fonctionne et que j'ai réussi à reproduire !!

Many thanks en tous cas!!!!!

jmfmarques · 19 Juin 2020

Étant donné que je ne cherche qu'une seule solution

Ce n'est pas l'unicité du montant recherché, qui est ici en cause, mais celle des "solutions" possibles pour parvenir à ce montant

mapomme · 19 Juin 2020

Re, @jmfmarques ,

jmfmarques à dit:
Ce n'est pas l'unicité du montant recherché, qui est ici en cause, mais celle des "solutions" possibles pour parvenir à ce montant

Je plussoie.

Le solveur peut trouver une solution "approchée" alors qu'une solution donnant le montant (et évidente au premier abord) existe.
Si on s'arrête à une solution approchée, rien n'indique que c'est la meilleure solution.
rien n'assure l'unicité d'un résultat

A moins de partir sur une exploration de toutes les possibilités existantes, il n'y a pas de solution présentant la garantie d'avoir un meilleur résultat. Il y a des système de codage à clefs publiques qui utilisent d'ailleurs ce fait.

mapomme · 19 Juin 2020

Bonjour @eriiiic ,

eriiiic à dit:
Selon le problème il peut être important de savoir si elle est unique.
par exemple, il est difficilement pensable d'attribuer le paiement d'une facture au hasard.

A moins d'essayer toutes les n-uplets de 0 et 1, c'est impossible de le savoir dès que n est un "petit grand".

Heureusement les lettrages se font souvent pour un seul client (retard, paiements ne respectant pas l'ordre des factures, fractionnement des paiements pour une même facture, paiement qui concerne plusieurs factures - même vu des clients qui payaient trop). Ce qui peut limiter le nombre de n-uplets.

Demander à un client d'indiquer les références des factures à rattacher à son paiement est fait. Le résultat est complètement aléatoire. Les clients s'en fichent. Souvent les clients qui le font sont aussi les clients bons payeurs. Donc on n'est pas plus avancé.

Nbita · 19 Juin 2020

Re!!!
Force est de constater que je suis loin d'avoir les compétences de notre ami mapomme et de vous autres.

J'ai essayé de reproduire la manip avec contraintes et formules mais je rencontre des difficultés erreur.

Voici le fichier avec les variables réelles sur lesquelles je travaille actuellement. Pour préciser le travail fourni, un département de l'entreprise a plus de fonds qu'un autre, nous souhaitons donc transférer des montants en euros afin que les deux départements soient à 50/50.

J'ai besoin de transférer 50985 euros , je cherche donc les lignes qui me feront atteindre ce montant.

Ci joint fichier et erreurs rencontrées

Merci infiniment pour votre retour, sincèrement même si je n'ai pas réussi, vous me faites progresser , encore merci d'avance.

mapomme · 19 Juin 2020

Re,

Premier point à vérifier :
Avez-vous installé le complément Solveur ? Si oui, il devrait se trouver dans votre menu Données, (chez moi à droite du menu) ?

Nbita · 19 Juin 2020

Oui je confirme il est bien là, chez moi aussi il est à droite

Nbita · 19 Juin 2020

Alors une idée de ce qui bloque dans mon fichier ...?

XL 2013 Additionner les cellules pour arriver à un montant donné

Nbita

XLDnaute Nouveau

Pièces jointes

JHA

XLDnaute Barbatruc

Pièces jointes

Nbita

XLDnaute Nouveau

eriiic

XLDnaute Barbatruc

Pièces jointes

mapomme

XLDnaute Barbatruc

Pièces jointes

eriiic

XLDnaute Barbatruc

jmfmarques

XLDnaute Accro

Nbita

XLDnaute Nouveau

jmfmarques

XLDnaute Accro

mapomme

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

Nbita

XLDnaute Nouveau

Pièces jointes

mapomme

XLDnaute Barbatruc

Nbita

XLDnaute Nouveau

Nbita

XLDnaute Nouveau

Discussions similaires