Sommer les valeurs d'une liste pour atteindre une valeur cible

Bonjour,

Tout d'abord, félicitations pour ton travail, la macro proposée est très bien construite. De plus elle réponds pleinement (ou presque) aux exigences de FaNy. Cela mérite des encouragements, le +2 est mérité.

Il y a toutefois quelques points à revoir.

Le bug du 0 au lieu de 6000.
Il se situe ici regarde les commentaires :

Do While NB_decoupes <> NB_decoupes_OK
    Do While Ligne_decoupes <> NB_decoupes + 2 ' test l'égalité entre la valeur actuelle et 6000
        'SI ON ATTEIND LA LONGUEUR MAX
        If Long_ACT + Sheets(Feuil).Cells(Ligne_decoupes, 3) = Long_tube_MAX Then
        'BLABLABLA
            If Sheets(Feuil).Cells(Ligne_decoupes, 4).Value = "" Then
                Sheets(Feuil).Cells(Ligne_decoupes_OK, 6) = Sheets(Feuil).Cells(Ligne_decoupes, 2).Value
                Sheets(Feuil).Cells(Ligne_decoupes_OK, 7) = Sheets(Feuil).Cells(Ligne_decoupes, 3).Value
                Sheets(Feuil).Cells(Ligne_decoupes, 4) = "X"
                NB_decoupes_OK = NB_decoupes_OK + 1
                NB_tubes = NB_tubes + 1
'ON INSCRIT 6000 FANS LA FEUILLE
                Sheets(Feuil).Cells(Ligne_decoupes_OK, 8) = Long_tube_MAX
                Sheets(Feuil).Cells(Ligne_decoupes_OK + 1, 5) = "Tube " & NB_tubes
                Test1 = True
                Ligne_decoupes_OK = Ligne_decoupes_OK + 1
'ON REINITIALISE Long_Act
                Long_ACT = 0
                Exit Do
                End If
'BLA BLA BLA 

'MAIS!!!!! SI TEST2 EST FALSE ALORS ON RE-INSCRIT DANS LA FEUILLE Long_ACT
'OR, ICI Long_ACT = 0....
    If Test2 = False Then
        NB_tubes = NB_tubes + 1
        Sheets(Feuil).Cells(Ligne_decoupes_OK - 1, 8) = Long_ACT
        Sheets(Feuil).Cells(Ligne_decoupes_OK, 5) = "Tube " & NB_tubes
        Long_ACT = 0
    End If

Pour pallier à cela, il suffit, lorsque tu testes l'égalité entre la valeur actuelle et 6000, si Test2 = False alors on le remet à True :

 If Test2 = False Then Test2 = True

à insérer dans ta macro, après Test1 = True par exemple :

    Do While Ligne_decoupes <> NB_decoupes + 2 ' test l'égalité entre la valeur actuelle et 6000
        If Long_ACT + Sheets(Feuil).Cells(Ligne_decoupes, 3) = Long_tube_MAX Then
            If Sheets(Feuil).Cells(Ligne_decoupes, 4).Value = "" Then
                Sheets(Feuil).Cells(Ligne_decoupes_OK, 6) = Sheets(Feuil).Cells(Ligne_decoupes, 2).Value
                Sheets(Feuil).Cells(Ligne_decoupes_OK, 7) = Sheets(Feuil).Cells(Ligne_decoupes, 3).Value
                Sheets(Feuil).Cells(Ligne_decoupes, 4) = "X"
                NB_decoupes_OK = NB_decoupes_OK + 1
                NB_tubes = NB_tubes + 1
                Sheets(Feuil).Cells(Ligne_decoupes_OK, 8) = Long_tube_MAX
                Sheets(Feuil).Cells(Ligne_decoupes_OK + 1, 5) = "Tube " & NB_tubes
                Test1 = True
                If Test2 = False Then Test2 = True
                Ligne_decoupes_OK = Ligne_decoupes_OK + 1
                Long_ACT = 0
                Exit Do
                End If
        Else: Ligne_decoupes = Ligne_decoupes + 1
        End If
    Loop

Petits détails :
- Il faut, si tu tiens à la protection de la feuille, déprotéger en début de macro et protéger à la fin. La syntaxe :

Sheets("machin").Unprotect
'... Ici tout ton code
Sheets("machin").Protect

- Comment as tu traité : pas de chiffre après la virgule on arrondi au supérieur (précision donnée par FaNy ICI?
Pour un bien, il faudrait que cela soit traité dans le code. En effet, FaNy ne le fera peut être pas systématiquement et cela risque de poser souci.

- Également prévoir, en début de code, l'effacement des données éventuellement contenues dans les colonnes de D à H.

Il y a encore pas mal d'autres choses à voir, mais commence déjà par ces points.

Merci pour tes commentaires et pour tes corrections :)

Actuellement le code est pas fini. C'est provisoir, je dois encore définir une zone d'impression aussi.

Je compte crée une barre d'outils à l'ouverture du classeur. Un bouton "Calculer" qui lance le sub "chercher". // Un bouton effacer, qui effacera au choix : Les mesures inscrites || Les mesures ordonées || les deux. //

Pour le problème des nombre à virgule il est règler hors du code, par la validation de donnée qui obliges l'utilisateur à inscrire un entier plus petit que Longeur_Max + 1 // se qui règle aussi le problème si des valeur au dessu de la valeur maximal sont entrée (sauf si on change la valeur maximal après que les données soient entrée)

Et les macro peuvent, dans certain cas, ultrepasser la protection de la feuille. Je ne pense pas que ça soit nécessaire ici, mais je recontrôlerai.

Et actuellement je pense plutôt mettre que si test1 n'est pas faux alors test2 n'est pas faux dans le Bloc IF qui vérifie si test1 est faux.

Pour le problème des nombre à virgule il est règler hors du code, par la validation de donnée qui obliges l'utilisateur à inscrire un entier plus petit que Longeur_Max + 1 // se qui règle aussi le problème si des valeur au dessu de la valeur maximal sont entrée (sauf si on change la valeur maximal après que les données soient entrée)

Pour faciliter la tâche de FaNy, je penses qu'elle ne souhaitera pas saisir ses données une par une, mais faire un copié/collé. Par conséquent, je penses que ces deux points doivent être traités par le code.

Et les macro peuvent, dans certain cas, ultrepasser la protection de la feuille

Chez moi, lors du lancement de ton code, le débugage s'est lancé parce que la feuille était protégée. Par conséquent, l'ajout des 2 lignes que je te propose est nécessaire...

Bonjour,

Ben dis donc!!! Quel travail!
Oui en effet je ferais des copiés/collé des tableaux de valeurs.

Fanny

Dac aLors je rajoutrait les lignes de protection :)

Et j'utiliserai une macro pour les convertire les valeurs copiée en valeur entière.

Dim Ligne as Integer
Ligne = 2

Do While Ligne < NB_decoupes + 2
Cells(Ligne, 3) = cInt(Cells(Ligne, 3).Value))
Ligne = Ligne +1 
Loop

Je compte aussi couper l'actualisation de l'écran pour que la vistesse de calcule soit optimisée, le resultat sera quasi instantané.

C'est sur qu'il imposible d'obtenir une lsite de valeur qui donnerons exactement X tubes de 6 mètres.

Mais dans ce cas je vois 3 méthodes qui permettent d'arriver a un résultat qui essaye de limiter les déchet pour chaque barre.

Actuellement théoriquement, je pense que j'arriverait à faire en sorte d'avoir toujours le meilleur rendement, mais il me faudrait des heures de claculs, et des heures de codage. En plus d'un ordinateur très puissant si on veut que les calcules soit pas trop long. Et dans la pratique je suis pas sûr d'arriver a qqchose de concluant :)

Donc je préfère me limiter aux premières méthode qui donnerons un bon résultat, même si c'est pas un résultat parfais.

Bonjour,

Par curiosité, je suis ce sujet.

Le but est bien d'optimiser mais également d'obtenir le meilleur résultat. J'imagine que FaNy ne fait pas cette démarche pour que son entreprise ne gagne au final qu'une centaine d'euros annuellement, mais plus de l'ordre de dizaine de milliers d'euros...

Excel-VBA, bien qu'assez complet, me semble (mais ce n'est que mon avis) un peu limite dans cette situation. Le but ici n'est pas de développer une application (dans ce cas, il conviendrait de s'assurer le secours de développeurs "payants"), mais de créer une macro qui permet cela sous Excel.

Il y a, en effet, plusieurs méthodes pour y parvenir.

La première, et certainement la plus simple, est la mise en oeuvre de l'algorithme glouton du sac à dos.
Non non ce n'est pas une blague.

La seconde utiliserai une fonction récursive pour trouver les solutions et listerai tout cela, en ôtant à chaque "tour de boucle" les références déjà utilisées.

La troisième, et certainement la meilleure que j'ai imaginé, consiste à utiliser la fonction récursive du 2 en cherchant, d'abord, les solutions exactes, puis les autres solutions en augmentant la "marge" progressivement.

J'en suis là de mes réflexions, mais j'arrête aujourd'hui sous risque de claquage du cervelet, j'ai les idées qui se croisent...

D'autres pistes éventuellement sous réserve :
- un arbre magique (avec une jolie classe Noeud) de tous les résultats approchant 6000,
- utiliser le solver Excel (=> je ne connais absolument pas).
- etc...

Par contre, je ne suis pas d'accord avec : Avec une macro c'est tres simple

En tout cas, à demain...

Bonjour,

Tout d'abord merci à vous tous pour votre réactivité.
Je pensais au départ que mon problème pourrait facilement être résolu sous excel...mais mes faibles connaissances ne m'ont par permis d'aboutir...je m'en remet donc à vous!
J'ai bien hâte de voir ce qu'il est possible de faire.

"La troisième, et certainement la meilleure que j'ai imaginé, consiste à utiliser la fonction récursive du 2 en cherchant, d'abord, les solutions exactes, puis les autres solutions en augmentant la "marge" progressivement."

C'est en effet ce que je souhaitais faire mais je n'ai pas réussi...

En espérant que des bonnes nouvelles arrivent!

Merci encore,

Fanny

Dison que le "Simple" est une manière de parler :)

Alors que souhaitez vous finalement ?

Vous visez plutôt l'optimisation à 100%, qui risque d'être extrement compliquée.

Ou une solution qui limite les perte autant que possible avec Excel, mais qui est faisable gratuitement ?

///
Il exsite bien d'autres manières qui sont plus ou moins bonne en fonction du nombre de découpe à faire.

J'attend votre réponse pour continué, si vous cherchez qqchose d'optimal il va faloir casser sa tirelire.

Salut le fil, skk201,

Disons que le "Simple" est une manière de parler :)

Je préfères. Je ne voyais pas ce qui pourrait être simple dans ce sujet.
D'autant plus que je m'aperçois d'une contrainte supplémentaire non énoncée au début : deux longueurs de tubes, 5500 et 6000 mm???

@FaNy : c'est tout concernant les contraintes? Je te suggère, aimablement, de nous donner EXACTEMENT l'énoncé de ce que tu cherches à faire, avec toutes les conditions, contraintes et exigences...
Il est clair que sans cela, le travail que nous effectuerons, pour toi, ne sera pas utile.

EDIT : faut il tenir compte des chiffres après la virgule dans tes longueurs de découpe? exemple : G5 196 = 236,193475815523
Si oui, de combien est la précision?
Faut il également tenir compte de ta "marge" de 0,005? Si oui qu'elle est elle EXACTEMENT?

haha faute de frape je voulais dire : Est-ce que ça change qqchose ?

Actuelement j'ai déjà réfléchi à une macro encore plus effiace.

Et pour la marge de découpe ça serait plus précis avec une marge par découpe que une marge générale pour la barre de 6m.

Est-ce que tu veux une macro vite fait qui marchera uniquement pour ce document-là ou est-ce que tu veux que je te fasse un document expret pour cela, qui sera réutilisable ?

Bonjour,
Merci de ton implication.
Un fichier réutilisable serait l'idéal puisque j'aurai par la suite d'autres listing de valeur. De plus me sera t-il possible de rentrer dans ta macro pour modifier la longueur de 6m? En fait j'ai des longueurs de 6m et des longueurs de 5,5m.
En ce qui concerne la marge, elle n'a que peut d'intéret pour moi finalement puisque mon but est d'avoir le moins de pertes possible et que la macro ne me fasse pas de combinaison supérieur à 6m (physiquement non réalisable).
En relisant votre réponse je me demande si ce que vous appelez la "marge" n'est pas plutot la tolérance sur la découpe: c'est à dire que je dois découper une longueur de 1,20m par exemple avec une précision de +/-5mm soit une longueur entre 1195mm et 1205mm, est-ce cela? dans ce cas partir sur une précision de 0,005m car nous sommes très proche du 0 en réalité.

Oui c'est tout à fait fesable et oui je parait bien de la tolérance sur la découpe.

Dac c'est assez simple je vous ferai un tru tout bête sur l'exemple que vous m'avez donner.

Est-ce que la liste des découpes dans l'ordre doit être imprimable ?

Oui, la liste des découpes dans l'ordre doit être imprimable.

Bonjour,

C'est l'exemple 1 qui correspond à ma réalité:
J'ai des commandes de 350 à X éléments que je souhaite optimiser et par exemple:
J'ai une commande de 350 à X éléments que je réalise dans des tubes de 6000mm
J'ai une autre commande de 350 à X éléments que je réalise dans des tubes de 5500mm.

X correspond à ce qu'il est possible de faire via excel pour que je ne sois pas limité si une commande dépasse les 357 comme le fichiers joints. Sinon je scinderai mes commandes en éléments de 357 - j'aurai moins optimisé ma perte mais ce sera déjà mieux que rien!

Je ne souhaite pas mixer mes commandes.

Dac, alors pas de soucis.

Vous pourrez choisir la longueur maximal des tubes entre 500 et 10 000 mm.

Et vous pourrez mettre un nombre très grand de mesure (autant que excel à de lignes)

En sachant que plus vous en mettez plus vous vous approcherez (je crois) d'un résultat optimal.

Mais que selon l'ôrdinateur que vous utilisez il risque d'avoir un temps de calculs plus ou moins long.

Sur quel version d'excel vous trouvez vous ? 2003 ? 2007 ? 2010 ? 2013 ?

Re,

Je suis sur excel 2013.
...hate de voir ce qu'il est possible d'obtenir :) !

Je penses que nous avons tous les éléments pour travailler...
Je reviens dessus plus tard, pas trop le temps...
A tantôt

Je pense que le meilleur tri est celui qui classe les mesure dans l'ordre décroissant.

Il existe encore une autre méthode, mais qui n'est pas forcément la plus efficasse. C'est d'asemblé les valeur dans des petits groupes de 1000 mm || 2000 mm || 3000mm et d'assemblé les groupes entre eux pour obtenir 6000 mm.

Mais cela dépend de la tailler et du nombre de découpes qu'on nous donne.

ça permet aussi de minimisé les pertes.

On fait des groupes entre 1860 et 2000 se qui fait qu'on limite théroriquement la perte par tube à max 420 mm par tube.

Mais bour pour que ça fonctionne bien, encore une fois, il faudrait beaucoup de valeurs de petites tailles.

Nous pouvons également en déduire une infinité d'autres selon le tri de la liste

.
Peut être pas une infinité, mais beaucoup en tout cas. D'ou l'utilisation d'algorithmes tels que le simplexe et autres pour explorer l'arbre des possibilités - ou en l'occurence les sommets du polygone des solutions.

On a i produits distribuées de façon normale autour d'une moyenne de 300 : i~N(300,sigma)
Ces i produits sont découpés dans j tubes

Donc on doit

Min Somme (Perte(j))=Min Somme (600-somme(i))
sous les contraintes que somme(i,j)<600
i>0

il ne doit pas y avoir bcp plus de contraines que ça.

Suis au bureau, mais le problème est excellent. Je regarde avec Lingo ce soir ^^

Je comprend juste pas le

sous les contraintes que somme(i,j)<600

Une petite explication :) ?

j'avoue que j'ai pas tous les caractères dont j'ai besoin pour l'écrire correctement ^^ Il faut que chaque la somme des i-éléments dans le j-ième tube découpé soit inférieure à la longueur du tube.

Salut Noctambule,

L'idée a été abordée ICI, mais n'a pas été développée par la suite. Dommage car les codes fournis travaillent sur de l'approximation. D'autant plus approximative que la liste des longueurs données comporte certaines valeurs avec décimales et que la règle, souhaitée par FaNy, est d'arrondir au supérieur.

Une réponse plus claire à ce sujet de la part de FaNy serait grandement utile...

0.005m ça fait 5 millimètres, ce n'est pas rien ou c'est une erreur de frappe et c'est du 0,005mm et une coupe au laser (sans doute) d'où ma question :attendons Fany.

Après quelques test de 6000mm :

Avec une marge de 0mm : 83 tubes
Avec une marge de 5mm: 83 tubes
Avec une marge de 10mm : 84 tubes
Avec une marge de 20mm : 85 tubes
Avec une marge de 50mm : 87 tubes

Comme quoi la marge n'a vraiment pas un grand impacte. (une marge de 10mm c'est déjà énorme)

avec les 360 découpes qu'on avait on arrive à moins d'un tube de perte : 5741mm de perte avec 5mm de tolérance

en revanche avec des tubes de 5500 et 5mm de marge :

9 tubes avec plus de 2600mm de perte.

soit une perte total plus de 30 000mm !!

Donc le choix des tubes peut avoir un grand impacte !

////////////

C'est amusant de voir qu'on aurait 7000mm de perte avec des tubes de 10 000 de long.

on pourrait même imaginé l'inverse, une macro qui dit la longueur idéales des tubes pour qu'il n'y ai pas de perte (ou le moins possible)

@skk201
Salut à toi
Je suis le sujet depuis un moment et je serai curieux de voir ta démarche, peux tu la mettre sur cjoint au format excel 2003
Merci

Voilà les dernières MAJ.

Disponibles en XLSM et XLS sur ce lien Onedrive

https://onedrive.live.com/redir?resid=834B81F80B205A7A!3636&ithint=folder,&lor=shortUrl

merci

il n'y a plus qu'à ....

Cordialement

Plus que à... ?

décrypter !

Salut,

juste en passant :
la moyenne des pertes est égale à la perte totale divisée par le nombre de tubes...