[RECH] Optimisation d'un tableau excel à l'aide du solveur.

... ne prend pas en compte les n_Max ni les spécifiques ...

... définir une contraintes qui permet de mettre en avant une matière spécifique ...

... dans la réalité, dans une journée sur les 5 conteneurs ils ne sont pas figés pour une seule et même matière ...

... cette étape sera prise en compte par la suite quand tu dis que l'on ne prend pas encore les spécifiques et les n_Max non ...

J'ai regardé le code VBA des deux fonctions, sans surprise je comprend bien celui pour effacer vu qu'il est relativement simple.

... regrouper puis de lancer à la suite les différents BPP ...

... "contrôle bonne juxtaposition des convois" ...

... matrice globale des températures tout à droite ... 

Edit : Je pense à ça pour le 1* car la il est question que de 2 pièces la mais en général il y a plutôt 8 - 16 ou 32 pièces à faire, soit 2 pièces par conteneurs pour mini 16 conteneur si 32 pièces, etc... Donc en général elles sont étalées sur toute la semaine.

Boucle sur les classes de températures
    traitement des cas spécifiques et n_MAX
    Lancement Modèle BPP sur références résiduelles de la classe de températures
    nbConteneurs <--- solution Modèle BPP 
    Solution <-- convoyage Modèle BPP
    Création puis lancement modèle Minimisation conteneur n° nbConteneurs
    Si convoyage Modèle Minimisation conteneur n° nbConteneurs meilleur que Solution
    Alors  Solution <-- convoyage Modèle Minimisation conteneur n°nbConteneurs
    Sinon
        Lancement modèle max remplissage
        Si convoyage Modèle max remplissage meilleur que Solution
        Alors Solution = convoyage Modèle max remplissage
        Sinon
            dichotomie sur la minimisation des restes à remplir des conteneurs pleins
            Si convoyage minimisation des restes à remplir meilleure que Solution
            Alors Solution <--  convoyage minimisation des restes à remplir
Fin Boucle

... ne prend pas en compte les n_Max ni les spécifiques ...

C'est parce j'estime qu'il est préférable que tu prennes connaissance de l'automatisation de l'optimisation le plus tôt possible et que tu ne perdes pas de temps à trop chercher dans des zones non "rentables" pour ton planning serré.

Oui je sais bien qu’il ne me reste vraiment pas beaucoup de temps dans ce planning . Après normalement on doit rendre le rapport mercredi soir mais je pense pouvoir pousser à vendredi prochain. C’est juste que pour écrire le rapport, expliquer comment je compte faire (grâce à ton aide avec l’explication d’opensolveur + de l’algorithme de VBA) pour obtenir le fichier demandé c’est cette deadline.

Et puis, ça te permet d'avoir quelque chose à présenter, même si ça n'est pas complet.

Le problème, c'est que tu rajoutes des fonctionnalités à tour de bras et que tu ne proposes pas de façons de les gérer (que ce soit au niveau des données et de leurs structures ou au niveau de l'algo pour les traiter).

Tu ne décris pas non plus l'organisation dans laquelle s'inscrit cette fonctionnalité (la priorisation matière est faite par quelqu'un? suivant quel processus décisionnel? il utilisera la feuille de calcul? il doit pouvoir prioriser en temps réel ou tranquillement le soir pour le lendemain? quelle interface utilisateur prévoir?)

... définir une contraintes qui permet de mettre en avant une matière spécifique ...
La priorisation actuelle est Atelier/Matière/Classe de température.

Il est possible d'inverser, en amont de l'optimisation, la priorisation pour les 2 derniers termes et avoir Atelier/Classe de température/Matière.
Ce sera alors le module d'extraction des données qui se chargera de ça.

Je préconiserais plutôt de le traiter post optimisation en offrant une possibilité de modifier l'ordre des convois (dans le respect, je suppose, des contraintes de classes de températures)
Dans ce cas, on peut considérer que c'est de l'excel "manuel" et le déléguer au module de réintégration du convoyage optimisé.

Tu vois que dans les 2 cas le module d'optimisation n'est que très peu touché.
Il travaille sur les données que tu lui présentes et restitue le convoyage que tu te charges de réintégrer dans ton classeur Excel.

... dans la réalité, dans une journée sur les 5 conteneurs ils ne sont pas figés pour une seule et même matière ...

Dans l'exemple que tu donnes tu ne parles pas de classes de températures....

Et tu me retournes le classeur avec le tableau issu de l'optimisation et, à côté, le tableau "réarrangé". Et tu n'oublies pas les règles de gestion (description du processus décisionnel qui t'a amené à cette réorganisation du convoyage).

As-tu réellement une dead line à dimanche soir?
Si c'est le cas, oublie tous tes élargissements de fonctionnalités.

Le nombre de colonnes qui résultera de l'optimisation n'est pas connu à l'avance. C'est la cellule objectif qui le contiendra en fin d'optimisation.

Ces 2 données seront utiles pour décaler et redimensionner le prochain BPP (celui sur la classe suivante).
C'est pour ça que tu vois beaucoup de Offset(...) et de Resize(...) dans la macro.

Au fur et à mesure du déroulement de la macro, il faut cumuler toutes ces données (d'où l'incrémentation des pointeurs")
Pour être plus précis :
le premier BPP utilise G46:AE51 (parce qu'il y a 6 références dans la première classe d'où 6 lignes )comme variables entières et G71:AE71 comme variables binaires.
Le résultat me donne 2 conteneurs

Pour le BPP suivant, comme ne ne veux pas perdre mes résultats précédents, je décale G46 de 6 lignes et de 2 colonnes et je prends 4 lignes (c'est le tableau du nombre de réf qui me le dit) sur 25-2 colonnes, soit I52:AE55 et I71:AE71 pour les variables.
J'obtiens à nouveau 2 conteneurs.

Pour le BPP suivant, comme ne ne veux pas perdre mes résultats précédents, je décale G46 de 6+4 lignes et de 2+2 colonnes et je prends 7 lignes (c'est le tableau du nombre de réf qui me le dit) sur 25-2-2 colonnes, soit K56:AE62 et K71:AE71 pour les variables.
J'obtiens 3 conteneurs.

Pour le BPP suivant, comme ne ne veux pas perdre mes résultats précédents, je décale G46 de 6+4+7 lignes et de 2+2+3 colonnes et je prends 2 lignes (c'est le tableau du nombre de réf qui me le dit) sur 25-2-2-3 colonnes, soit N63:AE64 et N71:AE71 pour les variables.
J'obtiens 2 conteneurs.
etc...
Donc le nombre de colonnes est fourni par BPP.

... "contrôle bonne juxtaposition des convois" ...
BPP fournit parfois les résultats "éparpillés sur les colonnes.
Il faut donc les resserrer à gauche.

... matrice globale des températures tout à droite ... 
Oui, j'ai voulu chercher une façon de mettre les références produit en classe de températures pour minimiser le nombre (anticipé) de conteneurs.
D'où les tableaux 3_devant, 3_milieu et 3_derrière qui comptent le nombre de références qui peuvent être mises dans une même classe (+ ou - 5°) en partant du début pour devant, du milieu pour ... milieu etc...
Partant de là, il est possible de modéliser la mise en classes comme l'écriture de nombres de 19 positions (parce que matière_2 a 19 référence à produire en semaine 1) en multi-bases, celle des 3_devant(i).
Chaque nombre sera une mise en classe possible.
Le premier chiffre de gauche pourra aller de 1 à 7, le second de 0 à 6, le troisième de 1 à 6 et le dernier de 0 à 1.

Il y a une contrainte complémentaire qui est que si une position i d'un nombre contient un chiffre p alors les p-1 positions suivantes du même nombre sont à zéro.
Cette contrainte réduit considérablement le nombre de combinaisons et, comme on veut réduire le nombre de conteneurs, ce nombre de combinaisons sera réduit aux combinaisons ayant le moins de conteneurs (un conteneur est un chiffre différent de zéro dans le nombre à 19 chiffres).
On devrait atteindre une dizaine de mises en classe prometteuses.
Il suffirait alors de lance l'optimisation sur chaque mises en classes retenues pour ne garder que l'une des meilleures, c'est à dire celles qui nécessitent le moins de conteneurs.

C'est pour ça que je disais que la mise en classe est un problème à part entière.
J'ai commencé manuellement pour finir par abandonner faute de temps.
Je crois que cette mise en classes est un problème d'algo bien connu. Il suffit de trouver le bon site ou le bon bouquin qui le décrit (généralement je regarde d’abord le livre de Berstel, Pin et Pocchiola "mathématiques et informatiques")

Edit : Je pense à ça pour le 1* car la il est question que de 2 pièces la mais en général il y a plutôt 8 - 16 ou 32 pièces à faire, soit 2 pièces par conteneurs pour mini 16 conteneur si 32 pièces, etc... Donc en général elles sont étalées sur toute la semaine.
Là c'est le coup de grâce!!!
Faut-il revoir la façon d'aborder le problème et raisonner en semaine?
Quand je te demande d'envoyer des instances représentatives, tu n'en envoie qu'une (j'en demandais 10) et en plus il n'y a pas les cas généraux!!!!!

Tu n'aurais pas dû faire un Edit.


Cordialement

Mon problème n'est pas envisageable en utilisant la fonction d'Excel VALEURCIBLE ?

J'ai commencé à rédiger une "check-list" des différentes contraintes, à l'aide de ton fichier excel matière 6 (celui ou j'avais réussi a refaire le résultat de la matrice 4 x5 ) et cela donne :

- La cellule objectif = cellule à minimiser.

- Cellule variable = la plage de cellule que l'on veut calculer (jusque la on est d'accord)

- Contraintes :
1) Plage de cellules variables = entier (pour obtenir des résultats entier car on ne peut pas produire un nombre non entier de pièces)
2) Remplissage des conteneurs < = Capacité maximum admissible par le conteneur
3) Plage de cellule de la production prévisionnelle = plage de cellules de la production réalisé

Tu penses que l'opensolveur ne marche pas à cause du fait que ces nouvelles données d'entrées perturbe le calcul ?

Il faudra surement faire un tri avant de copier/coller le tableau dans l'onglet du planning correspondant.

1) Peut-on rajouter, en + des 2 lignes matière et classe de température, une ligne qui détaille les différentes références contenu dans le même conteneur ? 

Car si on ne visualise pas en détail les références d'une matière 1 à rassembler, on ne pourras pas passer le mot aux équipes.

2) J'ai vu que tu as mis les différentes classes de température en prenant en compte les matières. Ex : on a une matière 2 en classe de température 3 et la matière 3 en classe de température 3 car elles ont la même température.

3) Les cellules sommes par classe qui réponde à ta formule =SI SOMME PROD & DECALER peuvent-elles se mettre à jour entre 2 semaines de prod différentes avec des références différentes

4) Je comprend mieux l'histoire de la variable binaire du conteneur : après calcul via modèle BPP si l'on rempli un conteneur celui-ci passe d'une valeur de 0 à 1. La valeur 1 est ensuite sommé pour obtenir le nombre total de conteneur que l'on cherche à minimiser c'est ça ?

5) Dans les paramètre à ne pas modifier, je ne comprend pas ce que représente "nb ref atelier", je vois 9 qui correspond au nombre de ligne des classes de température mais non au référence du coup. Est-ce le nombre total de classe de combo classe de température/matière différent ?

6) Une fois l'optimisation effectué grâce à ta macro de dingue (oui oui :D), le planning est généré sur cette feuille. Penses-tu qu'il est possible de reporter automatiquement ces données sur la feuille de planning (ayant son format spécifique mais donnée d'entrées identique) pour obtenir le contenu des différentes références ?

J'ai testé en modifiant la fameuse valeur 1206006 et en passant à 8 (2 anciennement), on passe de 16 à 19 poches, ce qui est logique car plus de prod à faire avec un nombre MAX par conteneur.

Dans un souci de lisibilité du prototype on pourrait rappeler les références à gauche (en AL27:AL50 par exemple) mais pour un module d'optimisation ça n'est pas utile.

Je te rappelle la décomposition modulaire de ton projet (voir post <14>) pour que tu ne fasses pas de confusion entre chacun.

Jusqu'à présent, le résultat de l'optimisation réintégrait ta feuille de prod. Pas plus.
Cette information aux équipes doit se décrire en terme de support et de format de restitution.

La démarche est coûteuse en temps mais à la fin, tu as une maquette qui fonctionne comme tu le souhaites et ainsi ton cahier des charges est finalisé.

Au contraire, cf l'edit en fin de mon post <27>, il n'y a pas de continuité dans les classes de températures lorsque l'on change de matière.
Pour être plus précis : la matière 2 se termine avec le convoi 9 et une classe 5 (en AV24:AV26) et le convoi 10 contient la matière 3 en classe 3 puis le convoi 11 contient la matière 6 en classe 1.
C'est pour ça que je suis un peu surpris que tu me confirmes dans ton post <25> que "La priorisation actuelle est Atelier/Matière/Classe de température".

Oui.
Je pense que je vais ajouter un paramètre semaine que j'utiliserai dans tous mes DECALER(). A voir.... Vu qu'il y a des références à zéro selon les semaines....

Oui, mon libellé n'est pas très heureux et porte à confusion.
Je viens de le changer en nb combo pour reprendre ton expression.

Oui. C'est le module de réintégration qui doit s'en charger et pour info ce sera ton boulot.

Surtout que j'ai mis 2mn à écrire le code pour les N_MAX et que je n'ai même pas eu besoin de le déboguer!!! Ça ne m'arrive jamais!

7) Je ne sais toujours pas si tu veux des combos (matière/classe°) ou (classe°/matière)
".....erreur.....Mais ces temps sont pris en compte ....... Mais en effet, cela pourra être un point de conclusion/amélioration"
C'est un "oui, mais, mais"

8) Pour les NO.SEMAINE() vs {1, 2, 3, 4}
En fait, c'est un faux problème.
Le module d'optimisation ne gère que des 1, 2, 3 et 4 mais rien ne t'empêche d'avoir dans les modules d'entrée et de sortie du module d'optimisation une table de transcodage qui présentera les bons n° de semaine aux utilisateurs.

9) parlons en de ces modules satellites.
a) je me suis concentré sur le module d'optimisation pour éviter de me disperser.
b) j'ai cherché à toutes forces de ne pas rentrer dans ton fichier excel d'origine parce qu'il me faudrait une tonne d'explications pour savoir d'où tirer les données et comment les combiner pour présenter au module d'optimisation quelque chose d'acceptable.
Et pour la réintégration ça serait pareil.
c) il faut pourtant bien les développer ces modules, soit par des opérations manuelles, soit par une macro (évidemment la macro est à privilégier) que tu ne vas peut-être pas savoir écrire.

Ce que je te propose, c'est d'utiliser l'enregistreur de macro pour enregistrer tout ce que tu fais pour chaque module.
Après on verra comment le packager dans ton appli.

Et ça se modélise très bien dans OpenSolver (tu le verras dans le fichier matière_2 que je vais t'envoyer).
L'intérêt de ce modèle est qu'il fournit le nombre de conteneurs directement et est très rapide pour ces petites instances
Ça évitera les itérations de modélisations inutiles que j'avais prévues initialement pour connaître ce nombre !

Mais cette modélisation BPP se fiche pas mal du remplissage des conteneurs.
Il faudra ensuite passer à un autre modèle qui minimisera le contenu du dernier conteneur.

Soit précis :
Par référence par jour?
Par référence par semaine?
Est-ce que c'est un n_MAX par jour? (qui peut donc se combiner avec le n_MAX (celui par conteneur)

une optimisation journalière n'est qu'une optimisation hebdomadaire dont on prend le premier jour.

Il faudra donc prévoir un enregistrement des données de la semaine en cours pour les figer dans le marbre et pouvoir les recharger en cas de besoin.

Question : l'optimisation sur 4 semaines est-elle un besoin du client?

Je sais bien qu'on te demande un boulot pour lequel tu n'as pas été formé mais, en tant que futur ingénieur, tu dois savoir t'adapter, et vite.