Formule dans Excel 2010 [Résolu/Fermé]

Signaler
-
Messages postés
23895
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
24 février 2021
-
Bonjour,

Là, je pousse ma "luck" pas mal. Mais qui risque rien n'a rien.

Existe-t-il une petite formule magique pour faire ceci:
J'ai une liste de chèques et la personne qui fait les dépôts ne m'en a pas donné le détail ou oublie parfois de me fournir son bordereau de dépôt.
Je dois essayer de trouver à quoi correspond son total.
Exemple. Mettons que j'ai ces montants:
16.58
17.80
21.55
28.05
31.77
35.21
35.83
40.63
44.59
59.06
63.86
218.82
514.50

et je sais que la personne a déposé pour $236.88. A part d'avoir à taponner pour essayer toutes les combinaisons possibles, Excel peut-il me trouver ma solution ?
Cette fois-ci, c'est une petite liste. Mais des fois, c'est plus compliqué.
Et oui, j'ai déjà demandé à la personne en question de me fournir plus de détail mais c'est peine perdue.

On ne sait jamais, peut-être qu'il existe une petite formule qui me fera sauver du temps ?

merci pour votre aide.


11 réponses

Messages postés
1941
Date d'inscription
lundi 3 mai 2010
Statut
Membre
Dernière intervention
23 août 2020
144
Bonjour,

Tu veux dire qu'il faut que la somme de plusieurs (tu ne sais pas combien) des nombre listés doit atteindre 236.88 ?
Je partirais sur le principe de tester toutes les combinaisons impliquant les 11 premiers nombres soit 2048 possibilités, comme là : https://www.cjoint.com/?CJyjNhP7uM6 .
Ici, ça tombe bien, car une seule combinaison est possible.

A+
???????????????
là faut que tu m'expliques comment t'as fait ça !
je suis impressionnée.
Je suis très heureuse que tu m'aies trouvé la réponse mais je ne comprends pas comment t'as fait.
Explique-moi ton fichier svp.
ça t'as pris combien de temps pour monter ça ?
Messages postés
1941
Date d'inscription
lundi 3 mai 2010
Statut
Membre
Dernière intervention
23 août 2020
144
Mon idée était de balayer toutes les possibilités. Pour chaque montant, soit il contribue au total, soit il n'y contribue pas (=> binaire). D'où un total de 2^11 (=2048) possibilités dont certaines absurdes (aucun chèque, tous les chèques,...).
En convertissant en binaire, tu as un nombre sur 11 bits et tu as toutes les possibilités.
Là où j'ai perdu du temps, c'est que je ne savais pas que la fonction DEC2BIN ne pouvait pas renvoyer un résultat sur plus de 10 bits, mais grâce à Internet c'est allé vite.
Je ne comprends pas dans quelles conditions tu as besoin de ça.
Je fais la comptabilité pour plusieurs entreprises.
J'en ai une en particulier qui m'envoie que des brides de documents. Il n'est pas ordonné du tout. Il ne détaille pas son bordereau de dépôt et il ne joint pas les pièces justificatives avec. Faut que je devine ce qu'il a déposé en fonction des documents que j'ai acculumés. Car des fois, il m'envoie des talons de chèque un mois, et le dépôt l'autre mois. C'est toujours le bordel dans ses affaires.

Mais dans le cas présent, c'est une autre entreprise. Le rapport de caisse mensuel m'indiquait un certain total de chèque déposé dans le mois mais en additionnant tous les dépôts du mois, j'avais un écart de $236.88. Au moins, cette entreprise détaille ses dépôts. Chaque dépôt correspond à environ 3 jours de ventes. En comparant, j'ai trouvé le dépôt qui ne balançait pas avec sa période de 3 jours. Il me restait à trouver quels chèques de sa liste totalisait $236.88 pour ensuite dire à mon client que ces chèques n'ont pas été poinçonnés dans sa caisse et n'ont pas affecté non plus ses comptes à recevoir.

ton fichier me sera très utile mais en autant que je comprenne comment le monter. Ce sont des formules que je connais pas.

Mais c'est tout à fait génial et ça répond exactement à mes besoins. Je ne pensais pas que ça existait.
Je suis en train d'étudier ton fichier. J'ai ouvert toutes les lignes et j'ai vu les formules. J'essaie de les reproduire en utilisant que les 3 premiers montants, soit 16.58 17.80 21.55
Explique-moi dans ta formule suivante
=DECBIN((MOD(A2;4096)/512);3) & DECBIN(MOD(A2;512);9)
ce que veut dire le 4096 le 512 le 3 et le 9

et mettons que je voudrais chercher le total de 34.38, comment dois-je bâtir la formule svp ?
merci
Messages postés
1941
Date d'inscription
lundi 3 mai 2010
Statut
Membre
Dernière intervention
23 août 2020
144
Si tu n'as que 3 (idem pour 4, 5, etc.) montants, tu peux les lister sur la ligne 1.
Tu complètes avec des montants supérieurs à la somme.
J'ai encore de la difficulté à décortiquer ta formule, mais en taponnant, j'ai quand même réussi à faire des tests avec 4, 5 ou 6 données.
Et ça fonctionne.

Juste m'expliquer ce que veut dire 4096/512
d'où viennent ces chiffres ?

merci
Messages postés
1941
Date d'inscription
lundi 3 mai 2010
Statut
Membre
Dernière intervention
23 août 2020
144
J'ai rédigé un commentaire qui a été mangé (qui devait être lien 6) !
4096 = 2^12 => pour écrire le nombre sur 12bits
512=2^9 => pour traiter les 9 derniers bits du nombre

En comparant avec du décimal, c'est comme si on t'interdisait d'écrire des nombres de plus de trois chiffres. tu assembles donc 12 et 345 pour dire que ça fait 12345.
ok mais pourquoi sur 12 bits alors que j'avais 11 données ?

et ta formule en C2 est =STXT($B2;2;1)
Pourquoi n'est-elle pas =STXT($B2;1;1)
Doit-elle obligatoirement commencer à 2 ?

Voir mon fichier, je l'ai débuté à 1 et mes données sont sur 4 bits et ça fonctionne.
Est-ce que j'ai bien compris le principe ?
http://cjoint.com/?CJzqRCDG7B2

et j'ai pas compris ton histoire de 12345.
quand tu parles de nombres de plus de 3 chiffres, est-ce que tu parles des nombres sur la ligne 1 ?

merci.
Messages postés
1941
Date d'inscription
lundi 3 mai 2010
Statut
Membre
Dernière intervention
23 août 2020
144
ok mais pourquoi sur 12 bits alors que j'avais 11 données ?
Parce que j'ai utilisé la formule que j'ai trouvée sur le net. En remplaçant 4096 par 2048 (2^11) et 9 par 8, on doit tomber sur un nombre écrit sur 11 bits.

Est-ce que j'ai bien compris le principe ?
Oui, même si tu peux te passer de l'intermédiaire MOD (cf. ci-dessous)

et j'ai pas compris ton histoire de 12345
Je faisais une analogie avec le décimal. Parce que d'emblée je voulais écrire DECBIN(A2;11) mais 11 est trop grand pour être géré par cette fonction, d'où la ruse de séparer les bits de poids fort et ceux de poids faible.
d'accord,
un gros merci pour tes explications et pour ta patience.

Cette solution est vraiment génial.
Messages postés
23895
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
24 février 2021
6 664
Bonsoir à tous,

Si ça t'interesse, une appli que j'avais réalisé dans ce but là :
https://www.cjoint.com/c/CJzr20z51HX
Lire tous les commentaires pour voir le rôle des options.
Si plusieurs combinaisons sont possibles elles sont toutes listées.

eric

1) En plus du merci (et oui, ça se fait !!!), penser à mettre en résolu (en haut vers votre titre) lorsque c'est le cas. 2) Jamais tu ne répondras à un mp non sollicité...
Bon, ça c'est fait.
AYOYE !!!!
Je suis impressionnée.
Si je comprends bien, j'entre mes données dans la colonne E, j'entre des valeurs dans la colonne H, selon le besoin du moment, et je lance la recherche.
Donc, le nombre de données n'importe plus. Je pourrais en avoir une centaine et ça marcherait quand même ?
Mais à quoi sert la Feuil3 ?
Messages postés
23895
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
24 février 2021
6 664
Voilà, tu colles tes montants en E, ton total en H2, et tu cliques sur le bouton.

Accessoirement si tu es sûre que c'est entre 3 et 12 chèques, tu le mets dans le nombre de termes mini et maxi (ça peut accélérer considérablement la recherche).

Tu peux mettre autant de montants que tu veux mais le temps de recherche est exponentiel par rapport au nombre de données. Bien qu'optimisé au maximum, le nombre de branches à explorer augmente vite.
Si tu en mets 1000 avec un micro peu puissant tu peux aller faire tes courses du we pendant ce temps...Il n'y a malheureusement pas algorithme de résolution sur ce type de problème. Il faut explorer toutes les branches en essayant d'en élaguer le maximum.
D'où l'intérêt de saisir nbTermeMax. Si tu es sûre que ça ne peut pas être plus que 20 chèques il faut le mettre, inutile d'explorer les solutions à 21, 22, ... chèques.

Ne te sers pas de l'option 'Supprimer les doublons'. Ce n'est que pour des cas très particuliers.
Et laisse la précision à 0 puisque tu recherches un total exact.
Rester uniquement sur la feuille 'Somme' (la 3 et un résidu pour des tests).

Par curiosité fais qcq test avec tes valeurs et dis-nous le temps mis pour x valeurs (et le processeur si tu le connais).

eric
Messages postés
1941
Date d'inscription
lundi 3 mai 2010
Statut
Membre
Dernière intervention
23 août 2020
144
Oh ben moi qui allais proposer d'utiliser le principe de mon tableau pour l'appliquer sous forme de macro... je ne serais jamais allé aussi loin !
Eric, je n'ai accès au code que via OpenOffice (donc pas exécutable), mais c'est impressionnant !
Je comprends le principe de ton élagage, mais je ne comprends pas comment toutes les possibilités sont parcourues.

Chapeau l'artiste !
Messages postés
23895
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
24 février 2021
6 664
Re,

ce fut qcq jours de bonne prise de tête sponsorisées par doliprane pour l'optimisation.
Et c'est encore plus difficile à expliquer :-s

Tu trouves le moteur qui génère les combinaisons à partir de ' incrémenter pointeurs ptr, avec un tableau de pointeur de la taille du nombre de termes pris en compte sur cette passe.
Plus de l'élagage dans Calcul(). Par exemple inutile de continuer à ajouter des montants si on a déjà atteint le total, ou le nom maxi de termes autorisés atteint. Ca fait un paquet de branches de supprimées.

eric
Bonjour,

eriiic, j'utilise souvent ton appli que tu m'as fourni et c'est vraiment très pratique.
Par contre, j'ai remarqué qu'à chaque fois que je l'utilise, ma barre d'état en bas de mon écran disparaît (excel 2010).
La seule façon dont j'ai trouvé pour la réactiver, est de fermer excel et de le rouvrir. J'ai fouillé sur internet et j'ai pas trouvé grand chose à ce sujet. Certains parlent d'aller dans Affichage, et de cocher. mais dans Excel 2010, les seules cases à cocher à cet endroit est: règle, barre de formule, quadrillage, titres.

N'y a-t-il pas une autre façon de la réactiver sans avoir à tout fermer ?
du genre ALT ou CTRL quelque chose ?

merci.
Messages postés
23895
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
24 février 2021
6 664
Bonjour,

Effectivement, je viens de contrôler, je ne restaurais pas la barre d'état. Désolé.

J'en profite pour te livrer la dernière version qui ne l'utilise plus.
A la place j'utilise une boite non modale.
Ca coûte un peu en temps (ne baisse pas trop le rafraichissement si tu es occupée à autre chose, autant qu'il ait le maximum de ressources pour la recherche) mais ça permet d'avoir un suivi plus précis de la progression.
Et surtout ça permet de suspendre la recherche (et de mettre le micro en veille pour reprendre le lendemain par exemple), de la reprendre ou de l'arrêter.
Si tu pouvais faire une remontée de tes impressions ou anomalies éventuelles dans qcq utilisations stp. v4.3.2 : https://www.cjoint.com/?DEDaNrugUPv

eric