Additionner les cellules pour arriver à un montant donné
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
Bonjour,
S'il s'agit de réaliser la somme au plus proche sur des cellules contiguës, c'est réalisable en formule. Une formule à placer en C5 et étendre à toutes les lignes :
=ABS($B$2-SOMME($B$5:$B5))Pour déterminer la dernière ligne de la somme :
=EQUIV(MIN(C:C);C:C;0)Si la somme est à réaliser sur la meilleures combinaisons de cellules non contiguës, on peut utiliser le Solveur Excel.
Merci infiniment Pedro !!!
Oui je pense qu'il va falloir utiliser le.solver.
Merci beaucoup pour votre réponse
Bonne journée
Navrée Pedro je n'avais pas compris qu'il s'agissait du fichier retravaillé!
Non il ne s'agit pas de cellules contiguës.
Pour vous préciser le travail à réaliser, nous devons équilibrer les budgets de deux départements au sein de l'entreprise. L'une à un budget de 100 000 euros contre 37000 euros pour l'autre , nous devons donc transférer 50% de cette différence (63k/2) afin d'obtenir un budget similaire de 68 500 euros.
Nous avons donc plusieurs lignes et il faut sélectionner celles nous permettant d'atteindre ce montant (31,5k) au plus proche.
J'espère que ces précisions vous donneront une idée de ma recherche.
Navrée si j'ai manqué de clarté.
Merci beaucoup pour votre recherche et vos conseils.
Nbita
Bonjour,
Solution utilisant le Solveur :
- Formule en B3 : =ABS($B$2-SOMME.SI($C$5:$C$82;1;$B$5:$B$82))
Dans le Solveur :
- Cellule objectif :
$B$3 - Valeur cible :
min - Plage variable :
$C$5:$C$82 - Contrainte(s) :
$C$5:$C$82=binaire - Moteur de résolution :
Evolutionnaire
Fichier modifié :
Mais c'est top Pedro cela a fonctionné
J'ai essayé de mettre 10 lignes supplémentaires pour faire un autre test.
Malheureusement je n'arrive pas à mettre a jour les paramètres du solver en consequence.
En effet, s'il y a plus de lignes, il faut bien mettre les formules a jour dans le solver, notamment les cellules variables et les contraintes non?
Car j'ai bien réussi à modifier les cellules variables mais impossible de modifier la contrainte, quand je change la référence de cellule, ca m'indique l'erreur suivante :
"une contrainte doit être un nombre, une référence simple ou une formule contenant une valeur numérique"
Est ce que ce message vous dit quelque chose, j'ai peut être loupé une étape ?
Non ça y est Pedro j'ai réussi !!!!!!
Je ne sais pas comment vous remercier !!!!!
Merci infiniment !!!!!!!!!
Vous avez trouvé la solution
Milles mercis
Il faut aussi penser à changer la plage utilisée dans la formule en B3.
Le message d'erreur provient d'un défaut connu. Lors de l'ajout de la contrainte, j'ai modifié le menu déroulant central pour mettre bin, ce qui inscrit automatiquement binaire dans le 3ème encart (voir image ci dessous) :
Une fois validé, la contrainte apparaît sous la forme $C$5:$C$82 = binaire. A chaque modification, il faut rétablir la valeur central à bin avant de valider. Sinon le Solveur n'apprécie pas de trouver du texte après le symbole =...
Je pensais qu'en cliquant sur le symbole approuver en haut à droite cela validait le sujet et la solution mais je vois apparemment que non.
J'ai essayé de consulter les conseils sur le site mais je ne parviens pas à savoir comment valider une discussion, une dernière aide
Merci
J'ai réessayé j'espère que cela marchera
Merci encore Pedro!
J'ai réessayé j'espère que cela marchera
Merci encore Pedro!
C'est parfait ! Et plus utile pour les internautes qui font des recherches sur le forum de consulter des sujets qui indiquent clairement si une solution à été trouvée ou non.
Merci et au plaisir de te croiser à nouveau sur le forum !
Bonjour,
Je suis parti de la demande de nbita et du fichier de Pedro22 pour ajouter de nouvelles contraintes au solveur sans y parvenir.
Je cherche toujours à faire en sorte d'additionner des cellules pour m'approcher d'un montant donné.
Mais j'aimerais qu'en plus le calcul tienne compte d'une proportion donnée entre ces cellules (réparties en 3 types).
Par exemple, la somme totale doit sélectionner des valeurs de ces 3 types selon une proportion définie en amont : au maximum 20% du type 1, 20% du type 2, et 60% du type 3.
Débutant pour l'occasion sur Calc/Excel, je n'arrive pas à comprendre comment intégrer celà au Solver. Ce dernier ne prenant pas en compte mes contraintes (cellules du type 1 <= cellule du pourcentage]). C'est assez abstrait pour moi pour le moment. :/
Merci de votre aide.
Bonjour,
Ce ne sera pas très compliqué, mais je vous invite à créer votre propre sujet.
Je posterai un fichier modifié avec un scénario fonctionnel.
Bonjour Pedro,
Le sujet crée se trouve ici :
https://forum.excel-pratique.com/viewtopic.php?f=19&t=143657
Merci pour votre aide !