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

[Paradjanov]

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'

 

[chris]

Bonjour

il y a des choses qui m'échappent

La Base de données Articles semblent correspondre à des commandes et non une base articles

Les intitulés des Références associées au client et au chantier ne correspondent pas entre les 3 onglets

Si je comprends bien vous faites du sur mesure.

Il faudrait donc une notion de date de livraison pour planifier la production de la demande entre aujourd'hui et cette date

Il faudrait également connaître les contraintes

• des chantiers différents peuvent être coulés ensemble ou pas
• les limites de production hebdo ou journalières
• ...

Là je ne visualise pas trop la logique, je ne vois pas de quantité à part le poids...

 

[Paradjanov]

Bonjour Chris et merci de votre réponse.

Je vais tenter de faire ça plus clair ^^ :







Je vais me servir, toute les semaines, d'un nouveau fichier (planning de production) édité par une autre personne détaillant la quantité de pièces à produire à la semaine. Ce fichier aura le format de la première feuille du classeur nommée "Planning production-1" et contient :

• Un code article pour ladite pièce
• Son client associé
• La référence (= désignation pièce) correspondant ici au secteur d'activité de cette pièce
• Le numéro du chantier sur lequel cette pièce peut être fabriquée
• S26, S27, S28, S29 & S30 étant le nombre de pièce à produire pour cette référence répartie en fonction du carnet de commande sur chaque semaine (de la S26 à la S30). Ex pour le code article n°1 : 1 seule pièce à réaliser en S30.

Les chantiers son indépendant les uns des autres :

• Chantier 1 et 2 peuvent utiliser les poches A et D (petites pièces)
• Chantier 3 e 4 peuvent utiliser les poches D et E (grosses pièces)

Et dernière question, qui est une des plus importante, les limites de productions hebdomadaires sont les suivantes :



Sur les 8 heures de travail par jour, on prend 7 heures "utiles" (après avoir enlevé les pauses et les différentes contraintes du poste).
Sur ces 7 heures, on essayera de couler le maximum de pièces en utilisant le minimum de poches.



Sachant qu'on a un "stock tampon" de 3 poches de chaque contenance A, D et E avec un temps moyen de préparation par type de poche égal à :

• 1 heure et 57 min pour la poche A
• 2 heures et 49 min pour la poche D
• 2 heures et 27 min pour la poche E

Nota : Soit un stock total de 9 poches (3 poches A, 3 poches D et 3 poches E) mais seulement 6 poches (A, D ou E) sont préparées le matin pendant la première heure de travail (de 7 h à 8 h). Une fois les poches prêtes, elles ne sont utilisables que l'une après l'autre avec leurs temps respectifs suivant :

• Utilisation poche A : 12 min 30 secondes
• Utilisation poche D : 15 min
• Utilisation poche E : 21 min

Et pour obtenir la quantité totale à produire sur la semaine, j'ai rajouté une cellule sur la première feuille du classeur qui fait la somme des pièces de la semaine toutes références confondues.

En espérant avoir répondu à toutes tes questions et t'avoir assez éclairé pour que la compréhension soit aisée,

Cordialement,

Paradjanov'

 

[Paradjanov]

Question complémentaire après une recherche sur le forum :

Peut-on obtenir le même résultat que dans la vidéo sans faire de liste déroulante mais en modifiant directement les valeurs dans l'onglet "base" ?! (ce qui s'apparenterai à ma modification du planning avec les nouvelles références de la semaine suivante)

Source vidéo : https://www.excel-downloads.com/med...ableau-selon-une-recherche-avec-rechechev.28/


Merci d'avance

 

[chris]

Bonjour

J'ai toujours du mal à cibler ce que tu attends

J'ai mis sous forme de tableaux les données dans l'onglet Base de données Articles
Tu remarqueras en colonne I une anomalie entre le chantier et la poche indiquée par rapport à ton post #3

Sur l'onglet Planning production-1 j'ai complété avec les données de la Base de données Articles et aussi réalisé un TCD qui synthétise par Chantier, Matière, T° : les poids et nombre de pièces sur les 4 semaines (ce pourrait être sur une semaine)
Cela peut t'aider à voir les optimisations éventuelles...

 

[Paradjanov]

Je regarde ça cette après midi après manger et je te fais un retour.
Mais je pense que mon besoin va être plus clair une fois que j'aurai réussi à faire un "prototype" type à l'aide du tuto suivant :

 

[chris]

RE

Les RECHERCHEV ne manquent pas dans le fichier que je t'ai fourni

 

[Paradjanov]

Re,


Pour l’histoire du TCD, j’en ai entendu parlé mais je n’ai pas encore pris le temps de me pencher dessus pour comprendre comment ça marche et comment on le configure. Mais on m’a dit que c’était vraiment puissant et je pense que la deuxième partie de ce fichier en aura besoin.


J’ai mis dans le fichier joint des annotations pour aider à la compréhension des différentes étapes que je cherche à reproduire.


Ce que j’ai réussi à faire cet après-midi :

• RechercheV pour les colonnes Matière, Poids & T° coulée

Cela permet, si changement de référence (dans le tableau (1)) de pièce à produire il y a, de modifier automatiquement et en accord avec la base de données les informations suivantes :

• Matière / Poids / T° Coulée

La partie (2) correspond, à l’aide des fonctions RechercheV et de la base de données pièces, à un extrait des références à produire pour la semaine. Il ne faut pas organiser le planning des 4 semaines en 1.

Ce que je cherche à faire dans le tableau (3), est d’organiser au mieux la production issue du tableau journalière, avant ou après avoir défini un planning sur la semaine.


J’ai mis des annotations dans le fichier Excel pour expliquer les différentes


Synthèse étape fichier excel :

• On récupère les 5 colonnes d’un nouveau fichier planning mensuel prévisionnel
• On obtient, automatiquement avec les fonctions rechercheV, les informations Nuance, Poids & T°Coulée
• A l’aide des informations obtenues, du nombre de poches maximums par jour utilisables (et surement à l’aide d’un TCD ?) à On génère un planning journalier optimisé
• Puis on génère de toutes ces références un planning à la semaine optimisé

Et ensuite on répète les opérations 1 à 4 la semaine suivante avec le nouveau planning de production fournis.


En gros je pense que pour passer du fichier de base au tableau (2) avec les fonctions RechercheV cela sera suffisant (le peu que j’ai réussi cet après-midi le confirme…).

Par contre pour la suite pour le passage du (2) au (3) à aucune idée pour le moment, je vais me renseigner sur le TCD.

J’espère qu’avec ces modifications et ces annotations tu comprendras mieux ce que je cherche .



Merci par avance,


Cordialement,

Paradjanov’

 

[chris]

RE

J'avais pris la peine

• d'utiliser des tableaux structurés. Ce n'est pas pour faire joli mais parce que cela a un réel intérêt et notamment aurais évité ta colonne d'erreurs en M
• de donner les bonnes formules qui évitent de changer la formule pour chaque colonne : une seule formule suffisait pour toutes en la tirant vers la droite

"Il faut copier/coller les colonnes en rouge" : je ne vois pas de colonnes en rouge dans ton retour

Quoi qu'il en soit si tu modifies les colonnes A à I de Planning1 ou d'une copie, les formules continueront à lire le contenu de BDD articles, initial ou modifié selon la norme des tableaux structurés

A noter qu'un tableau structué doit avoir la même formule sur toutes le slignes cequi n'est plus le cas dans BDD articles : si ce 10 a un sens pour toi il ne devrait pas être dans cette colonne
De façon générale, même indépendamment des tableaux structurés on évitre de mélanger choux et carottes dans une même colonne

Tableaux structurés Excel

Tableaux structurés Excel

fauconnier.developpez.com

 

[Paradjanov]

RE

J'avais pris la peine

• d'utiliser des tableaux structurés. Ce n'est pas pour faire joli mais parce que cela a un réel intérêt et notamment aurais évité ta colonne d'erreurs en M
• de donner les bonnes formules qui évitent de changer la formule pour chaque colonne : une seule formule suffisait pour toutes en la tirant vers la droite

"Il faut copier/coller les colonnes en rouge" : je ne vois pas de colonnes en rouge dans ton retour

Quoi qu'il en soit si tu modifies les colonnes A à I de Planning1 ou d'une copie, les formules continueront à lire le contenu de BDD articles, initial ou modifié selon la norme des tableaux structurés

A noter qu'un tableau structué doit avoir la même formule sur toutes le slignes cequi n'est plus le cas dans BDD articles : si ce 10 a un sens pour toi il ne devrait pas être dans cette colonne
De façon générale, même indépendamment des tableaux structurés on évitre de mélanger choux et carottes dans une même colonne

Tableaux structurés Excel

Tableaux structurés Excel

fauconnier.developpez.com

Bonjour Chris,

Site très complet que je viens finir de lire (pour cet article sur les tableaux référencés).

Je m'excuse car n'en n'ayant pas connaissance, je n'avais pas compris grand chose sur ton fichier précédent.

Je vais, à partir de maintenant et dans le futur, utiliser au maximum ce type de tableau car le fait que l'on puisse l'alimenter en données/formules et qu'il se mette à jour sans "casser" comme détaillé dans l'article est vraiment un plus !


J'ai du coup une question (même si j'imagine que je donnerai la réponse).

Ma 1ère étape consistant à copier/coller les nouvelles colonnes (A à I) du nouveau planning de production, puis-je obtenir le même résultat (générer nuance, poids, T°Coulée & Chantier) quelque soit le format du tableau de base ? (plage classique ou tableau référencé).

Je pense que oui mais à confirmer d'où ma question...


Je remodif/refait le fichier ce matin (avec tes remarques de ton ancien fichier et en utilisant les tableaux référencés) puis je me renseignerai sur le TCD, pour savoir s'il sera indispensable à l'étape suivante pour obtenir le planning de production optimisé...

 

[chris]

Bonjour

Si tu supprimes les lignes d'un tableau structurés (la 1ère sous le titre resta affichée vide)
dès que tu copies de nouvelle lignes (pas des colonnes) les formules vont automatiquement réapparaitre

Ce n'est pas du tout le cas dans une plage

 

[Paradjanov]

Re bonjour,

Ok pour ces précisions et merci ^^.

J'ai lu un article sur les TCD et un autre sur les GCD, qui je pensent vont grandement me servir pour exploiter au maximum mes données.

Néanmoins, je vais devoir bien réfléchir au format de mes données pour pouvoir les utiliser pleinement !

Pour faire simple :
1) Fichier/feuille Base de données qui contient toutes les informations de toutes les références confondues
2) 1er tableau (directement TCD ou pas, a définir) pour connaitre le détails des références à produires sur la semaine
3) Tableau TCD pour organiser et trier toutes les données du 1er tableau (étape 2)
4) GCD d'après le ou les TCD pour obtenir des graphiques explicatif/démonstratifs du/des TCD

Voila je pense que cela donneras un truc dans cette idée la.

A confirmer demain matin ^^

Mais en tout cas merci de m'avoir parlé des tableaux référencés qui m'ont eu même emmener sur les tableaux croisées dynamiques qui eux même m'ont envoyé sur les graphiques croisées dynamiques !!!


Bonne fin de journée,

Paradjanov'

 

[chris]

RE

2) 1er tableau (directement TCD ou pas, a définir)

Le mot directement m'interpelle : un TCD est toujours basé sur une source de données, de préfréence un tableau structuré

C'est généralement un bon outil de restitution mais aussi une aide à l'analyse d'hypothèses : j'ai fait travailler des journalistes sur ce type de de schéma et cela leur était utile

 

[Paradjanov]

Alors oui, je vais faire un tableau référencé avec toutes les informations concernant les pièces.
Une fois le tableau copié/collé, je met en place à côté les colonnes chantier, matière, Poids, T°Coulée pour avoir un visuel + ton tableau pour répartir par matière, poids, T°coulée et chantier.

Par contre j'ai une question, dans ton fichier production_Alu :

• Peut-on, en prenant le même format de tableau référencé en entrée lors de la copie des colonnes A à I, faire en sorte que le tableau d'accueil s'adapte sur le même nombre de lignes ? (Ex : mon tableau d'accueil contient 10 références mais le nouveau tableau en contient 12. En faisant un copier/coller à la main, cela ne prendra pas les 2 dernières lignes. Ou alors faut-il utiliser une autre méthode de copier/coller ?

Merci par avance et je continue de travailler dessus

 

[chris]

RE

Peux-tu repréciser : tu copies quelles cellules de quel onglet vers quel onglet ?