XL 2016 Générer et ordonner un planning de production au jour à partir de données à la semaine

Paradjanov · 22 Juin 2020

Bonjour à tous,

Je suis en train de réfléchir à faire un fichier Excel assez particulier/spécifique et j'aurai besoin de votre aide/vos avis .

Ce fichier Excel doit assurer l’optimisation et l’ordonnancement d’un planning de production.

Pour se faire, il faut :

Dans un premier temps, analyser et organiser le planning de production pour connaître la totalité des pièces à produire sur la semaine en fonction de leurs nuances respectives, températures de coulées, chantier et poids
Dans un second temps, organiser/trier les différentes références de pièces pour réaliser le plus de pièces possibles en utilisant le moins de poches possibles (cf. exemple)

Ces pièces à produire sont toutes réalisées en aluminium dans 3 nuances différentes nommées :

Aluminium-1
Aluminium-2
Aluminium-3

Il existe 3 types de poches différentes utilisable :

Poche A contenance métal liquide 180 Kg
Poche D contenance métal liquide 250 Kg
Poche E contenance métal liquide 500 Kg

Température de coulée allant de 730 ° à 760 ° C.

J’ai réalisé une version épuré d’un planning de production pour l’exemple.
Ci-joint 1 fichier Excel nommé « Fichier A » détaillant le planning de production sur les 6 prochaines semaines pour plusieurs références de pièces, selon plusieurs clients et selon leurs nuances d’aluminium respectives.

Exemple :

2 pièces que l’on doit couler initialement le lundi et mercredi à 740 °C de type aluminium-2 pèse 170 Kg et 20 Kg.

Si on les coule 1 par 1, il faudra 2 poches : 1 première de 180 Kg puis une seconde de 180 Kg que l’on lingotera de 160 Kg (perte de temps et d’argent).

Solution optimisation : Se servir d’une seule et même poche de 250 Kg pour couler les 2 pièces en même temps le même jour.

J’ai maintenant plusieurs questions à vous poser pour réaliser ce fichier Excel de manière robuste :

Faut-il obligatoirement 2 fichiers Excel ou puis-je me servir d’un seul fichier dans lequel je change juste les premières colonnes du planning de production ? Type Fichier A (en copiant/collant les nouvelles valeurs des nouveaux plannings de production)
Comment faire suivre ces valeurs avec les nouvelles du planning de production ?
Je pensais à des fonctions SI, ET, OU mais je ne sais pas si cela est robuste pour des références qui changent…
Comment faire pour « générer » le planning optimisé obtenu ? Faut-il que je me lance dans des macros VBA complexes, ou est-ce possible avec des fonctions SI, ET, OU directement après la mise à jour des données initiales du planning de production ?
Une fois l’optimisation de production à la semaine effectuée, peut-on générer une production journalière ? Si oui, à l’aide de quelles fonctions ?!

Lexique :

Moule : Emplacement (généralement en sable) dans lequel on vient couler les pièces en métal liquide.
Chantier : Emplacement désigné où la production est faite (exemple : un garage pour un mécanicien)

Je vous remercie pour votre temps de lecture… et j’espère que vous pourrez me transmettre un point de vue « Excel » sur comment répondre à mes différentes questions.

Cordialement,

Paradjanov'

Paradjanov · 25 Juin 2020

Re,

Je copie les cellules variables du fichier "de base" (exemple fichier 1 : 70 premières lignes et fichier 2 : 63 premières lignes) mais toujours le même nombre de colonnes (A à I).

Est-ce possible d'avoir un TCD qui se mette a jour automatiquement lors du changement de ces valeurs ? (Comme lorsque l'on utilise la fonction rechercheV sur les nouvelles références par rapport à la base de donnée globale)

En espérant être un minimum clair ^^'

chris · 25 Juin 2020

RE

On avait un seul fichier et 3 onglets donc là ne je vois pas de quoi tu parles

De façon générale si tu copies des colonnes entières en bête copier/coller tu casses probablement la cible

Non un TCD doit être actualisé
Il peut se référer à 1 voir n millions de lignes donc risquerait de toute bloquer s'il réagissait en temps réel

Paradjanov · 25 Juin 2020

Oui pardon c'est vrai que j'ai toujours upload qu'un seul fichier. Mais dans l'idée le format du planning "de base" des colonnes A à I sont, chaque semaine, sur un nouveau fichier.

J'ai vu effectivement que cela casse les liens.
Quand tu dis qu'on peut actualiser un TCD, il faut forcément le refaire où avec un copier/coller dans le bon format (tableau référencé) l'on peut "mettre à jour" le TCD rapidement ?!
Si oui, via quel commande ? Merci ^^

chris · 25 Juin 2020

RE

Comme je l'avais évoqué au #11, supprime les lignes du Tableau structuré (sélection des cellules d'une colonne hors titre, clic droit supprimer lignes de tableau
puis copier les cellules hors titres de ton ficher 2, collage spécial, valeurs en A2

Il te faudra ensuite copier coller (toujours en collage spécial valeurs), les titres des semaines
Une solution quand on a des titres fluctuants, liés au temps notamment, est de mettre un titre générique genre S1 période, S2 période... et d'écrire au-dessus en dehors du tableau les semaines par formule : ainsi on change la 1ère les autres suivent : c'est lisible et n'impacte pas le tableau

Pour actualiser un TCD, clic droit dedans, Actualiser

Paradjanov · 3 Juillet 2020

Bonjour/Re,

Alors yep merci beaucoup, j'ai bien testé avec le copier/collage spécial avec valeurs et les listes se mettent à jour même si changement de référence il y a.

Pour l'histoire des titres fluctuants, je pense comprendre à peut près ce que tu veux dire mais je ne vois pas comment le réaliser ^^'.

Pour actualiser le TCD, yes merci je n'avais pas trouvé mais impeccable avec tes conseils :].

Ensuite, désolé du temps de réponse car je devais revoir certaines choses.

Donc j'ai pu avancer sur le fichier, je te le mettrai en PJ pour que tu comprennes bien.

Etat des lieux :

J'ai bien rempli la base de donnée (onglet base de donnée) comme prévu.

Le 1er onglet PDP de base correspond au fichier que j'obtient (pour simplifier ici et n'avoir qu'un fichier j'ai copier/coller son contenu dans le premier classeur mais il est bien question d'un deuxième fichier différent).

Le 2ème onglet PDP entrée correspond au copier/collage spécial du 1er onglet + a des fonctions recherchesV pour obtenir directement les informations de la base de donnée même si les références du "PDP de base" changent.

La j'ai une question : Il arrive qu'entre deux semaines il y a des lignes cachées sur le prochain fichier "de base", en faisant un copier/collage spécial comme convenu j'obtiens une ligne vide entre 2 "lots" de références (ex : 20 lignes complètes puis la ligne vide puis 20 lignes complètes).

- Comment faire pour, malgré des lignes vides, obtenir les formules rechercheV mise à jour en fonction du nombre de lignes changeant chaque semaine ?
Ex : semaine 1 : 45 références soit 45 lignes. mis à jour ok, le tableau référencé contient maintenant 45 lignes.
Semaine 2 : 65 références soit 65 lignes. Le tableau de 45 lignes prendra en compte les 20 références supplémentaire en ajoutant les lignes dans le tableau référencé mais ne feras pas suivre les formules rechercheV des lignes 45 à 65.

J'ai peut être une idée mais je pense que l'on doit pouvoir y arriver plus simplement :

1. Macro copier/coller formules de la ligne en dessous du titre jusqu'à un "grand nombre" de références --> Pas dit que cela marche tous le temps car si l'on obtient une cellule vide entre deux paquets de références (ex : 20 lignes complètes puis une ligne vide puis 20 lignes complètes), la formule s'arrêtera au 20 premières lignes sans prendre en compte les lignes après la ligne vide.

Deuxième question : Une fois ce tableau référencé + les formules mises à jour (en fonction des nouvelles références de la nouvelle semaine), comment faire pour obtenir un tableau (autre que TCD ?!) qui génèrera, en 1 clic, un "planning" ?

Ce planning étant un tableau synthèse du précédent en triant les références par chantier, nuances, T° Coulée et poids.

Et ensuite 3ème question : une fois ce tableau la obtenu, il y a une feuille nommée "informations poches" qui contient les différentes configurations possibles pour la production.
Cas idéal on obtient :

Chantier I1 : 1 180 Kg par jour

Chantier I3 : 2 180 Kg par jour

De ce poids maximum que l'on peut produire par jour, il faudrait réussir à faire une synthèse/organisation des références selon chantier (I1 ou I3) et par la suite une répartition par poids.

je m'explique, en 1 clic d'après le tableau synthèse qui a précédemment trié les références par chantier, nuances, T° coulée et poids --> obtenir un tri (type via l'outil filtre dans un tableau référencé) des références en fonction de la capacité jour possible : ici 1 180 Kg pour le chantier I1 et 2 180 Kg pour le chantier I3 (cf. plus haut). Ce tri sera situé dans la feuille Planning tri.

J'espère que ce n'est pas trop brut, dans le cas contraire tu me diras s'il y a des choses que tu n'as pas compris et je ferai une synthèse de ces informations ^^

Merci par avance pour tes conseils et tes futurs réponses

Paradjanov'

chris · 3 Juillet 2020

Paradjanov à dit:
Pour l'histoire des titres fluctuants, je pense comprendre à peut près ce que tu veux dire mais je ne vois pas comment le réaliser

Voir ci-joint

Paradjanov à dit:
La j'ai une question : Il arrive qu'entre deux semaines il y a des lignes cachées sur le prochain fichier "de base", en faisant un copier/collage spécial comme convenu j'obtiens une ligne vide entre 2 "lots" de références (ex : 20 lignes complètes puis la ligne vide puis 20 lignes complètes).

- Comment faire pour, malgré des lignes vides, obtenir les formules rechercheV mise à jour en fonction du nombre de lignes changeant chaque semaine ?

Une fois les lignes collées dans le tableau, tu filtres pour ne voir que les lignes vides puis tu sélectionnes ces lignes (une colonne de ces lignes suffit, clic droit, supprimer les lignes du tableau puis défiltrer

Si la formule RECHERCHEV a été correctement faite elle s'adapte automatiquement au nombre de lignes du tableau comme je l'évouais au # : les tableaux struturés gèrent les formules

Surtout pas de macro qui va pourrir l'automatisme

Paradjanov à dit:
Deuxième question : Une fois ce tableau référencé ~~+ les formules mises à jour (en fonction des nouvelles références de la nouvelle semaine),~~ comment faire pour obtenir un tableau (autre que TCD ?!) qui génèrera, en 1 clic, un "planning" ?

Ce planning étant un tableau synthèse du précédent en triant les références par chantier, nuances, T° Coulée et poids.

Soit tu tries le tableau, soit il faut un duplicata par formules (assez compliqué) soit utiliser PowerQuery ou VBA
Mais comme tu débutes, garde le TCD : voir ci-joint

Paradjanov à dit:
Et ensuite 3ème question : une fois ce tableau la obtenu, il y a une feuille nommée "informations poches" qui contient les différentes configurations possibles pour la production.
Cas idéal on obtient :

Chantier I1 : 1 180 Kg par jour
Chantier I3 : 2 180 Kg par jour

De ce poids maximum que l'on peut produire par jour, il faudrait réussir à faire une synthèse/organisation des références selon chantier (I1 ou I3) et par la suite une répartition par poids.

je m'explique, en 1 clic d'après le tableau synthèse qui a précédemment trié les références par chantier, nuances, T° coulée et poids --> obtenir un tri (type via l'outil filtre dans un tableau référencé) des références en fonction de la capacité jour possible : ici 1 180 Kg pour le chantier I1 et 2 180 Kg pour le chantier I3 (cf. plus haut). Ce tri sera situé dans la feuille Planning tri.

Il manque des informations de référence : le lien entre chantier et capacité poches.
J'ai aléatoirement modifié un tableau de l'onglet Informations poches et ajouté une colonne à PDP entrée
Ce n'est sans doute pas bon mais cela donne une idée de ce qu'il faire en adaptant et il faut adapter aussi le TCD

A noter que la formule de ta colonne Poids total me parait bizarre

J'ai renommé correctement les tableaux principaux (au lieu d'ajouter des noms pointant sur les tableaux), et fait le ménage dans les noms.
Il te reste à renommer correctement tous tes tableaux de Informations poches

J'ai activé la ligne des totaux : on ne totalise pas dans une ligne normale
A désactiver avant de coller les nouvelles données puis réactiver (je l'ai intégré au code de vidage)

J'ai adapté la macro de RAZ filtres mais j'ai placé un segment bien, plus pratique que du code
J'ai adapté le code de réinitialisation du Tableau (qui utilise le RAZ filtres) : il y avait bien un problème sur les formules, que j'ai corrigé : sans doute ton code formules qui avait tout cassé

Paradjanov · 6 Juillet 2020

Bonjour,

Merci beaucoup pour ta réponse.

Alors je vais reprendre point par point sur ce que j'ai compris, fait, et reste à faire.

Pour les titres fluctuants, merci c'est plus clair (je devais surement penser à quelque chose de trop compliqué que juste un A1+1 ^^). Il faudra néanmoins ajouter a la main le numéro de semaine correspondant à chaque fois mais je ne pense pas que l'on puisse faire plus simple.

Une fois les lignes collées dans le tableau, tu filtres pour ne voir que les lignes vides puis tu sélectionnes ces lignes (une colonne de ces lignes suffit, clic droit, supprimer les lignes du tableau puis défiltrer

Si la formule RECHERCHEV a été correctement faite elle s'adapte automatiquement au nombre de lignes du tableau comme je l'évouais au # : les tableaux struturés gèrent les formules

Surtout pas de macro qui va pourrir l'automatisme

Yep j'ai regardé, essayé et niquel !

Soit tu tries le tableau, soit il faut un duplicata par formules (assez compliqué) soit utiliser PowerQuery ou VBA
Mais comme tu débutes, garde le TCD : voir ci-joint

Oui le TCD ira très bien d'ailleurs cela m'amène une autre question :

Comment faire pour, après filtres utilisés (chantier, nuance et T°coulée), obtenir un regroupement des références à produire en fonction du poids max par chantier ?
Ex : après les filtres on obtient 6 références différentes à couler qui font au total 1 450 Kg.
Or, en fonction du chantier I1 ou I3 la capacité Max par jour est de 1 180 / 2 280 Kg. Voire même être plus précis en terme de quantité car exemple si une pièce fait 450 Kg et une autre 120 Kg, on ne pourras pas les "rassembler/fabriquer" dans une seule et même poche de 500 Kg.
Il faudrai réussir à "trier" le tableau synthétique pour après organiser les poches, en fonction de leurs contenance pour mettre les différentes références au mieux. J'espère avoir été un peu clair ^^.
-------------------------------

Il manque des informations de référence : le lien entre chantier et capacité poches.

Yep c'est surement la ou il faut que je re-travaille, la pour le moment j'ai deux poids total de production journalière. 1 poids par chantier représentant un cas de figure "type" dans le meilleur des mondes si l'on peut préparer et couler toutes les poches.

J'ai aléatoirement modifié un tableau de l'onglet Informations poches et ajouté une colonne à PDP entrée.
Ce n'est sans doute pas bon mais cela donne une idée de ce qu'il faire en adaptant et il faut adapter aussi le TCD

J'ai bien vu que tu as rajouter les chantiers I1 et I3 mais je ne les voient pas affichés sur le TCD. Pour la colonne rajouter à PDP entrée n'apportera rien ici (même si c'est pour l'exemple que je n'ai pas vu sur le TCD je me doute ^^), car cette donnée de Poids total max par chantier doit rester une limite MAX de quantités que l'on peut produire par jour.

A noter que la formule de ta colonne Poids total me parait bizarre

Alors elle prend le nombre de pièces à produire sur la semaine (nombre présent dans la colonne de la semaine) multiplié par le poids d'une pièce. Comme ça on obtient le poids total à produire pour une même référence.

J'ai renommé correctement les tableaux principaux (au lieu d'ajouter des noms pointant sur les tableaux), et fait le ménage dans les noms.
Il te reste à renommer correctement tous tes tableaux de Informations poches

Merci d'avoir renommer en effet, je vais prendre l'habitude de le faire. Peux-tu me confirmer qu'il faut utiliser le gestionnaire de nom dans l'onglet Formules ?

J'ai activé la ligne des totaux : on ne totalise pas dans une ligne normale
A désactiver avant de coller les nouvelles données puis réactiver (je l'ai intégré au code de vidage)

Via quel fonction peut-on activer et désactiver cette ligne stp ? Que je puisse expliquer à mon collègue pour ne pas se retrouver avec une ligne "batarde" en bas de planning nommée total général qui impact la mise à jour des formules comme détaillé plus haut.

J'ai adapté la macro de RAZ filtres mais j'ai placé un segment bien, plus pratique que du code
J'ai adapté le code de réinitialisation du Tableau (qui utilise le RAZ filtres) : il y avait bien un problème sur les formules, que j'ai corrigé : sans doute ton code formules qui avait tout cassé
[/QUOTE]

Alors pour le côté macro, je suis vraiment débutant. Le peu que j'ai réussi à faire pour les filtres ici, je l'ai fait avec l'outil "enregistrer une macro". Je vais regarder dans le détail les modifs que tu as faites même si je ne suis pas sur de tout comprendre tout de suite ^^.

Merci pour tout et je te tiens au courant de la suite ou si j'ai des questions supplémentaires à celles citées dans cette réponse .

chris · 6 Juillet 2020

Paradjanov à dit:
J'ai bien vu que tu as rajouter les chantiers I1 et I3 mais je ne les voient pas affichés sur le TCD. Pour la colonne rajouter à PDP entrée n'apportera rien ici (même si c'est pour l'exemple que je n'ai pas vu sur le TCD je me doute ^^), car cette donnée de Poids total max par chantier doit rester une limite MAX de quantités que l'on peut produire par jour.
Oui le TCD ira très bien d'ailleurs cela m'amène une autre question :
....
Comment faire pour, après filtres utilisés (chantier, nuance et T°coulée), obtenir un regroupement des références à produire en fonction du poids max par chantier ?
Ex : après les filtres on obtient 6 références différentes à couler qui font au total 1 450 Kg.
Or, en fonction du chantier I1 ou I3 la capacité Max par jour est de 1 180 / 2 280 Kg. Voire même être plus précis en terme de quantité car exemple si une pièce fait 450 Kg et une autre 120 Kg, on ne pourras pas les "rassembler/fabriquer" dans une seule et même poche de 500 Kg.
Il faudrai réussir à "trier" le tableau synthétique pour après organiser les poches, en fonction de leurs contenance pour mettre les différentes références au mieux. J'espère avoir été un peu clair ^^.

l'info est Colonne D du TCD

Comme je l'ai dit cela n'est qu'un exemple pour montrer qu'il faut avoir dans un tableau de référence ce maxi par chantier/nuance et T°coulée pour pouvoir faire quelque chose

Paradjanov à dit:
A noter que la formule de ta colonne Poids total me parait bizarre

Alors elle prend le nombre de pièces à produire sur la semaine (nombre présent dans la colonne de la semaine) multiplié par le poids d'une pièce. Comme ça on obtient le poids total à produire pour une même référence.

Oui mais pourquoi la 1ère semaine seulement ?

Paradjanov à dit:
J'ai renommé correctement les tableaux principaux (au lieu d'ajouter des noms pointant sur les tableaux), et fait le ménage dans les noms.
Il te reste à renommer correctement tous tes tableaux de Informations poches

Merci d'avoir renommer en effet, je vais prendre l'habitude de le faire. Peux-tu me confirmer qu'il faut utiliser le gestionnaire de nom dans l'onglet Formules ?

Ou bien à gauche de l'onglet Outils de Tableau quand la cellule active est dans le tableau
Idem pour la ligne du Total : c'est dans l'onglet Outils de tableau

Paradjanov à dit:
Alors pour le côté macro, je suis vraiment débutant. Le peu que j'ai réussi à faire pour les filtres ici, je l'ai fait avec l'outil "enregistrer une macro". Je vais regarder dans le détail les modifs que tu as faites même si je ne suis pas sur de tout comprendre tout de suite ^^.

Comme indiqué pour les filtres, utilise les segments pas du code
Seul nouveauPDP qui défiltre avant avec razfiltres est utile
Tout ce que j'ai mise ne commentaire peut être supprimé

Paradjanov · 6 Juillet 2020

chris à dit:
l'info est Colonne D du TCD

Comme je l'ai dit cela n'est qu'un exemple pour montrer qu'il faut avoir dans un tableau de référence ce maxi par chantier/nuance et T°coulée pour pouvoir faire quelque chose

Oui mais pourquoi la 1ère semaine seulement ?

Seulement parce que dans un premier temps je dois obtenir un planning sur 1 semaine puis ,par la suite, 4 semaine.
Mais il ne faut pas oublier qu'un planning de production est généralement mis à jour hebdomadairement. Donc il faut que j'obtienne, dans un premier temps, un premier fichier fonctionnel générant 1 semaine de production optimisé. Il pourra être améliorer par la suite par mes successeurs.

Ou bien à gauche de l'onglet Outils de Tableau quand la cellule active est dans le tableau
Idem pour la ligne du Total : c'est dans l'onglet Outils de tableau

Je suis sur Excel 2016, j'ai trouvé pour la ligne des totaux merci .

Comme indiqué pour les filtres, utilise les segments pas du code
Seul nouveauPDP qui défiltre avant avec razfiltres est utile
Tout ce que j'ai mise ne commentaire peut être supprimé

Yes bien d'accord pour le moins de macro possible.
Je garde comme convenu seulement : NouveauPDP, RazFiltres.

J'ai ajouté des segments sur le TCD afin d'obtenir mon "tri" recherché en fonction de :
- Chantier
- Nuance
- T° coulée

Maintenant, il faut que je regarde dans le détail les différentes hypothèses à poser et je reviendrai vers toi pour mettre ça en forme ^^'.

Sachant que j'aimerais obtenir un fichier/tableau synthèse à partir du dernier tri effectué détaillant quel est la meilleure combinaison/organisation de fabrication des pièces.

Dans l'idée, il faudrait dans un premier temps, obtenir après avoir mis le contenu du nouveau PDP (en copier/collage spécial) une "automatisation" via Excel qui nous dise directement si le planning est faisable (par rapport à la capacité maximal journalière des différents chantiers) et si oui --> Dans quel ordre de fab et comment doit-on procéder.
Si non --> Comment faire pour y arriver. Avec un levier supplémentaire (si gros écart entre demandé et réalisable) qu'on pourrait ajouter un couleur supplémentaire pour augmenter la capacité maximal par jour. Mais cela sera complexe a déterminer car même s'il y a une personne en plus, il y a des éléments qui sont utilisés par tous le personnel à tour de rôle (je pense notamment au pont roulant pour la manipulation des poches).

Par contre, cela pourra être très apprécié d'avoir une idée de ce que l'on gagnerai, ou non, à prendre cette personne en plus comme moyen de négociation/conclusion sur un bilan économique.

Paradjanov · 8 Juillet 2020

Bonjour,

Je viens avec de nouvelles informations et un objectif global plus détaillé .

Alors le début du fichier est très bien et je t'en remercie.

Maintenant, après avoir fait le tri dans le TCD, il faudrait que j'obtienne (surement à l'aide d'un autre TDC) un tableau récapitulatif nommée TCD-2 avec :

Alliage
Quantité/Poids à la semaine
Equivalent poche (cette colonne sera obtenu via l'onglet information poche --> Poche A = 180 Kg, Poche D = 250 Kg, Poche E = 500 Kg)

De ce tableau, en faisant la "somme" du nombre de poches requises on obtient notre nombre de poches total sur la semaine à couler (Je ne peux pas mettre une formule dans un TCD qui se met à jour comme dans un tableau référencé, si ?).

De ce nombre de poches total sur la semaine, définir sur une nouvelle feuille excel (avec les jours de la semaine) la répartition de ces poches sur la semaine en fonction de l'alliage coulé (jour travaillés en général égal à 5 mais pouvant varier avec les congés, RTT et jours fériés).

Et enfin, d'après l'organisation défini, il faudrait obtenir la liste des pièces/références produites par jour.

Ce qui permettrai de faire le chemin en sens inverse et d'obtenir la production optimum des pièces selon les meilleurs combinaisons de références.

Pour l'automatisation du fichier, sachant que le fichier de base est en réseau local dans l'entreprise, je me suis renseigné et ai vu qu'on pouvait (au lieu de copier/collage spécial) effectuer un "Données/Nouvelle requête/A partir d'un fichier excel" permettant de mettre à jour les quantités de pièces à produire directement dès l'ouverture.

Pensez-vous que je peux réussir à automatiser la répartition des poches sur la semaine (du coup il faut que je remplisse au préalable différentes configurations, mais très complexe voire impossible à avoir toutes les configurations possibles sachant que les plannings de productions changes chaque semaine)

Ou alors, remplir dans le tableau des jours de la semaine des chiffres (1,2 ou 3) représentant le nombre de poche à produire par alliage, égal au nombre de poche par semaine obtenu précédemment ?

J'ai fait quelques modifications sur le fichier joint et j'aimerai vos remarques/conseil pour réussir à obtenir cette démarche de planification.

PS : Je veux obtenir sur 4 semaines les colonnes Poids total. Dans un tableau référencé, je ne connais pas la manipulation à faire dans la formule pour "figer" la cellule poids en utilisant le symbole "$".
Pouvez-vous me dire s'il faut mettre autre chose que ce symbole pour figer une des deux cellules de cette formule ? Ou s'il faut faire une autre manipulation ?

Merci par avance et bonne après-midi,

Cordialement,
Paradjanov'

chris · 8 Juillet 2020

Bonjour

Paradjanov à dit:
Maintenant, après avoir fait le tri dans le TCD, il faudrait que j'obtienne (surement à l'aide d'un autre TDC) un tableau récapitulatif nommée TCD-2 avec :

Alliage

Quantité/Poids à la semaine

Equivalent poche (cette colonne sera obtenu via l'onglet information poche --> Poche A = 180 Kg, Poche D = 250 Kg, Poche E = 500 Kg)

Je vois que les tableaux de l'onglet Informations poches ont toujours n'importe quoi comme noms...
Si tu veux faire des calculs il faut se raccrocher à un référentiel et là je ne vois toujours pas comment croiser les données de synthèse du TCD avec ce référentiel.
Ta formule SI sur TCD-2 ne doit pas contenir des seuils mais les déduire du référentiel qui ne contient pas de poche E à 500 kg si je lis bien.

Paradjanov à dit:
De ce tableau, en faisant la "somme" du nombre de poches requises on obtient notre nombre de poches total sur la semaine à couler (Je ne peux pas mettre une formule dans un TCD qui se met à jour comme dans un tableau référencé, si ?)....

Avec l'exemple que j'avais donné (Maxi) l'idée était déjà de disposer d'information dans le TCD
On est limité en calcul mais tant que je ne vois comment établir le lien entre le chantier de PDP entrée, et sa capacité et autres dans le référentiel, je ne vois pas comment on peut avancer d'autant que le poids total pourra se ventiler sur des poches de plusieurs tailles
C'est ton job donc tu y vois peut-être clair, mais moi je ne vois pas

Il est probable que tes TCD ne suffisent pas car le calcul est complexe : il faut donc bien mettre à plat ce référentiel.

Si tu importes par Données/Nouvelle requête/A partir d'un fichier excel, qui utilise PowerQuery, il faudra continuer sur PowerQuery : là on n'utilise plus RECHERCHEV mais on croise les tables.
Donc on retombe sur la nécessité du référentiel carré et de la mise à plat des règles

Paradjanov à dit:
PS : Je veux obtenir sur 4 semaines les colonnes Poids total. Dans un tableau référencé, je ne connais pas la manipulation à faire dans la formule pour "figer" la cellule poids en utilisant le symbole "$".

Pour info :

VB:

=PDP_Entree[@[Poids]:[Poids]]*[@[S1 période]]

Paradjanov · 16 Juillet 2020

chris à dit:
Bonjour

Je vois que les tableaux de l'onglet Informations poches ont toujours n'importe quoi comme noms...
Si tu veux faire des calculs il faut se raccrocher à un référentiel et là je ne vois toujours pas comment croiser les données de synthèse du TCD avec ce référentiel.
Ta formule SI sur TCD-2 ne doit pas contenir des seuils mais les déduire du référentiel qui ne contient pas de poche E à 500 kg si je lis bien.

Avec l'exemple que j'avais donné (Maxi) l'idée était déjà de disposer d'information dans le TCD
On est limité en calcul mais tant que je ne vois comment établir le lien entre le chantier de PDP entrée, et sa capacité et autres dans le référentiel, je ne vois pas comment on peut avancer d'autant que le poids total pourra se ventiler sur des poches de plusieurs tailles
C'est ton job donc tu y vois peut-être clair, mais moi je ne vois pas

Il est probable que tes TCD ne suffisent pas car le calcul est complexe : il faut donc bien mettre à plat ce référentiel.

Si tu importes par Données/Nouvelle requête/A partir d'un fichier excel, qui utilise PowerQuery, il faudra continuer sur PowerQuery : là on n'utilise plus RECHERCHEV mais on croise les tables.
Donc on retombe sur la nécessité du référentiel carré et de la mise à plat des règles

Pour info :

VB:

=PDP_Entree[@[Poids]:[Poids]]*[@[S1 période]]

Bonjour,

Merci pour tes remarques. J'ai bien renommé les différents tableaux et ai pris l'habitude de le faire dès la création des nouveaux.

Pour l'importation des données, je n'ai aucune connaissance sur PowerQuery même si j'ai vu comment "activer" ladite chose via l'option - à partir d'un fichier excel -.

J'ai remis à jour le fichier et je vais te faire un compte-rendu ci-dessous :

Le premier onglet "PDP_Pièces" correspond au planning sur le fichier de base (fichier indépendant de celui-ci). Le copier/collage spécial en tant que valeurs et/ou en utilisant PowerQuery nous permet de mettre à jour les informations Nuance, Chantier, T°Coulée, Poids, Poids total par semaine ainsi que "l’équivalent poche" (= % qu'un référence remplie la poche. Ex 180 Kg capacité poche --> Pièce 60 Kg = 33 %)
Une fois l'équivalent poche pondéré, on obtiens un nombre total de poche minimum requises par semaine pour couler les références si l'on utilise les poches à 100 % (il faudra prendre une marge d'erreur à + 20 %).
Ici j'aimerai modifier pour obtenir le nombre de poche par semaine de chaque type (A,D et E) et non un total global sans détail.
Le second onglet "PDP_Poches_semaine" représente le TCD du 1er onglet qui permet d'obtenir un tri avancé par chantier, nuance & T°Coulée mais surtout le poids total par semaine à couler que l'on compare avec la capacité MAX possible des chantiers (avec comme base 5 poches par jour travaillés. Ex : 3 jours --> 5 * 3 = 15 Poches par chantier (I1 et I3) soit 30 Poches au total sur la semaine.
Le troisième onglet "Combinaisons orga" est une copie du 1er onglet mais en effectuant le tri et l'optimisation à la main (chaque couleur pour une nuance différentes puis on regarde à la mains comment l'on peut optimiser la coulée de celles-ci).
Sachant aussi que nous sommes légèrement flexible par rapport à la T°Coulée --> Ex : Ref1 à couler à 560°C +-5 °C et Ref 2 - 565 +-5°C : on pourra couler les deux références à 560 ou 565°C.
Enfin en dessous de ce tableau, c'est la somme de toutes les poches requises pour couler le planning --> Dans un premier temps avec optimisation puis sans.
Ici on remarque que l'on peut gagner 4 poche A/D soit 780/1 000 Kg. J'ai rassemblé les références même si chantier différents (hypothèse à confirmer par mon supérieur hierarchique) mais tout en gardant bien la même nuance d'alliage et une température de coulée commune. Mais aussi en obtenant l somme des poids des différentes références inférieur ou égal à la capacité Maximum de la poche.
Ensuite le 4ème onglet "PDP_Poches_semaine orga" est un tableau synthèse où l'on vient placer les poches, en fonction de la nuance (chose qui devra être détailler dans l'onglet "Combinaison_orga" par la suite) et du jour de coulée sur la semaine.

Et dans le futur, il faudrait obtenir :

Le nombre de poche A, D et E requises pour assurer la production du PDP_Pièces sur la semaine
Le contenu des poches (= Tableau "PDP_Poches_semaine orga" rempli directement une fois le nombres de poches défini ou après) détaillant la nuance coulée et les différentes références "rassemblés" dans la même poche --> Ce qui permettra de sortir un "Planning" type au jour pour produire les différentes pièces en accord avec le planning de production.

J'espère avoir été assez clair et qu'il n'y ai pas trop d'infos "Brutes".

Pour obtenir le contenu des poches, pour ce coup-ci je l'ai fait à la main mais à priori si j'arrives à bien définir toutes les différentes variables (surement ce dont tu parles lorsque tu parles de table de référence ?!), je devrais pouvoir obtenir une "combinaisons" types de références repartis dans les poches avec l'outil Solveur.

Si tu confirmes que c'est possible, je vais regarder ça de plus près.
Si non, existe-il un autre moyen que le Solveur pour réaliser cette "organisation" des différentes références dans les poches ?

Merci par avance du temps que tu consacre à me répondre, on avance vraiment bien et je t'en remercie.

Ci-joint le fichier + un scan de ma feuille pour l'organisation "A la main".

Cordialement,
Paradjanov'

Paradjanov · 7 Août 2020

Message SYNTHESE PROJET :

Bonjour,

Je viens aux nouvelles avec un nouveau fichier édité.

Alors j'ai synthétisé ce que j'ai fait et je vais vous le détailler dans ce message.

J'ai en tête le procédé "brut - à la main" qui faudrait que je puisse automatiser.

Donc :
1) On prend les données qui correspondent aux colonnes C à K du PDP_Pièces (de Code article à S34 dans cet exemple).
De là, on obtient à l'aide du tableau situé dans l'onglet Base de données et de formules RechercheV dans le PDP_Pièces toutes les informations (Nuance, T°Coulée, Poids, Poche à utiliser, Nb pièces totales/poche, Equivalent poche ainsi que le nombre de poches requises pour produire les références demandées sur la semaine.

* La colonne Nb pièces totales/poche correspond aux nombres de pièces que l’on peut produire avec une poche.

* La colonne équivalent poche, complète la colonne Nb pièce total/poche pour pondérer le poids de chaque référence dans la poche. Ex : Ref 1 = 60 Kg que l’on coulera avec une poche A de capa 180 Kg à alors equivalent poche de la ref 1 = 33,3 % du remplissage de cette même poche.

* Nb poches requises sur la semaine : alors là j’ai fait des formules SI.CONDITIONS pour prendre en compte un type de poche par colonne et par chantier (Poche A – I1, Poche D - I1, Poche D-I3, Poche E I3, etc…).

Le plus compliqué étant de prendre une formule « standard » pour les références n’ayant qu’1 pièce totale/poche à Quantitée à produire/(nb total par poche * equivalent poche) permettant ainsi de prendre en compte la pondération expliquée au-dessus.

Pour les références ayant 2 pièces/poche, là la formule est différente à Quantitée à produire/nb total poche, car on ne peut pas produire + que 2 pièces/poche d’une même référence même si la capacité poche le permet (contrainte interne). Ex : Capacité poche 180 Kg à Ref 60 Kg : 3 ref/poche en remplissage « optimisé » mais vu qu’on a une contrainte de 2 pièces/poche alors ont rempli qu’a 66,6% la poche. Du coup, ont rempli avec une autre référence les 33% restant.

2) À l'aide du TCD créer dans l'onglet PDP_Poches_semaine et des segments l'on peut filtrer nuance et chantier et T°Coulée pour obtenir une liste "filtrée" des références à organiser dans les poches.

Alors pour les poches, il y a plusieurs type et capacité :
- Poche A = 180 Kg utilisable uniquement pour le chantier I1
- Poche D = 250 Kg utilisable pour les chantiers I1 et I3
- Poche E = 450, 560 ou 650 Kg utilisable uniquement pour le chantier I3. Sachant que les poches "standard" E ont 450 Kg, 560 Kg étant une poche réservée à une seule référence dans toute la liste.
- Poche H = 7 000 Kg réservée pour les pièces GP (= Grosses Pièces)

Et c’est en ayant filtré chaque chantier/nuance/T°Coulée correspondant que l’on obtient le tableau synthèse des références à optimiser dans les poches pour l’étape suivante.

Ensuite, et c'est cette étape qu'il faudrait automatiser ^^ :

3) Dans l'onglet PDP_Poche_semaine orga, j'ai "esquissé" le principe de remplissage manuel des poches avec les références qui seront prise en compte (via liste déroulante se référant à la base de données). Nous avons par jour, 5 poches MAX pour le chantier I1 et 7 poches MAX pour le chantier I3.
Ce nombre varie en fonction des jours de la semaine. Sur une semaine complète de 5 jours à 25 Poches I1 et 35 Poches I3.

4) Et pour l'étape finale, il faudrait arriver (avec Index/Equiv et/ou RechercheV ?!), à transposer le contenu hebdomadaire des poches I1 dans l'onglet Planning_fusion IMF1 et I3 dans l’onglet Planning_fusion IMF3. Cela permettra d'obtenir le fameux planning de production qui prend en compte les contraintes de la production (prise en compte précédemment pour bien organiser la répartition des références dans les poches).

Questions :

Il y a eu des modifications et je m'en excuse, car cela amène d'autres questions qui sont :

a) Pour l'onglet PDP_Pièces, je n'arrivais pas à figer plus d'une ligne pour ainsi obtenir les segments tout en haut + la ligne contenant code article, client, etc figé lorsque l’on descend dans les références à J’ai positionné les segments à gauche pour figer uniquement la première ligne du tableau. Comment faire pour figer plusieurs lignes dans Excel ? La fonction figer les volets ne marchait pas « bien » Elle figeait les segments et les 5 premières lignes du tableau en ayant seulement sélectionné la première ligne bleue contenant les noms des titres.

b) Les formules SI.CONDITIONS peuvent surement être synthétisées/raccourcis. Étant donné que ce sont mes premières formules SI.CONDITIONS, je n’ai peut-être pas pensé à bien les optimiser.

c) Pour l’histoire précédente des numéros de semaine en préfixe dans les cellules. On copie-colle en données d’entrée les numéros de semaines. Je cherche donc à obtenir, sur les cellules correspondantes (Cellule R1 à Y1 pour la S27 dans cet exemple), en préfixe, le numéro de la semaine (cellule G1).

d) Comment faire pour obtenir un arrondi entier supérieur pour toutes les cellules de la ligne totale correspondant au nb de poches (dans cet exemple R76 à Y76).

e) Pour réussir à automatiser l’étape 3, j’ai recherché et ai trouvé la fonction « solveur » d’Excel. Néanmoins, le solveur ne peut résoudre que des opérations mathématiques or ici je dois répertorier le contenu des poches en fonctions de « texte » (= nuance, chantier, T°Coulée). Y’aurai-t-il un autre moyen que le solveur pour réussir à faire ça ? Peut-être avec la fonction « Données/Analyse de scénario » ? Je ne connais pas du tout cette fonction mais peut-être qu’elle pourrait me permettre d’analyser différentes optimisations possibles ?

Je pensais aussi à utiliser un message Box pour donner le nombre de jours dans la semaine de 1 à 5 pour ainsi générer dans l’onglet PDP_poches_semaine orga le nombre de poches I1/I3 correspondant. Ex : 3 jours à 3 * 5 = 15 poches I1 et 3 * 7 = 21 Poches I3.

Ou alors l’analyse de scénario pourrait être utilisée dans le futur, une fois l’optimisation faite, pour prendre en compte (via de nouvelles contraintes à définir) différents cas en termes de l’effectif du secteur ?

Exemple : Aujourd’hui 8 employés pour 100 % production. Demain on passe à 6 employées (car un Arrêt maladie + 1 démission) pour x % production qui serait définie par la fonction.

XL 2016 Générer et ordonner un planning de production au jour à partir de données à la semaine

Paradjanov

XLDnaute Nouveau

Pièces jointes

Paradjanov

XLDnaute Nouveau

chris

XLDnaute Barbatruc

Paradjanov

XLDnaute Nouveau

chris

XLDnaute Barbatruc

Paradjanov

XLDnaute Nouveau

Pièces jointes

chris

XLDnaute Barbatruc

Pièces jointes

Paradjanov

XLDnaute Nouveau

chris

XLDnaute Barbatruc

Paradjanov

XLDnaute Nouveau

Pièces jointes

Paradjanov

XLDnaute Nouveau

Pièces jointes

chris

XLDnaute Barbatruc

Paradjanov

XLDnaute Nouveau

Pièces jointes

Paradjanov

XLDnaute Nouveau

Pièces jointes

Discussions similaires