Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

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

Nbita

XLDnaute Nouveau
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
 

Pièces jointes

  • Tuto.xlsx
    9 KB · Affichages: 15

Nbita

XLDnaute Nouveau
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

XLDnaute Barbatruc
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
 

Pièces jointes

  • Accès Factures avec Somme Totale v4.3.4.xls
    107 KB · Affichages: 10

mapomme

XLDnaute Barbatruc
Supporter XLD
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.
 

Pièces jointes

  • Nbita- Somme- v1.xlsx
    33.5 KB · Affichages: 17

eriiic

XLDnaute Barbatruc
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

XLDnaute Accro
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

XLDnaute Nouveau
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!!!!!
 

mapomme

XLDnaute Barbatruc
Supporter XLD
Re, @jmfmarques ,

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.
 
Dernière édition:

mapomme

XLDnaute Barbatruc
Supporter XLD
Bonjour @eriiiic ,

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é.
 
Dernière édition:

Nbita

XLDnaute Nouveau
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.
 

Pièces jointes

  • Copie de Nbita- Somme- v1.xlsx
    35.8 KB · Affichages: 6
  • Numérisation 19 juin 2020.pdf
    723.6 KB · Affichages: 4
  • Numérisation 19 juin 2020 (1).pdf
    775.8 KB · Affichages: 5

Discussions similaires

Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…