XL 2013 calcul de charge + planification

[jasmine]

Salut Forum,
je bosse sur un projet de panification de l'activité du "contrôle qualité" mais je bloque sur un truc. Donc j'ai décidé de me diriger vers vous en espérant que vous m’aiderez par ce que c’est super important dans mon stage.
J’ai mis un fichier joint pour mieux décrire la situation.
J’ai pour mission de calculer la charge à long terme de l'activité.
Contraintes:
-différents types de tests,
-les opérateurs n'ont pas tous les mêmes compétences,
-les tests peuvent se faire sur plusieurs jours
-les temps de main d'œuvre ne sont pas successifs, avec des temps d'arrêts/d'attentes
J’ai commencé à travailler sur Excel en essayant de faire la charge à la semaine:
-j'ai les temps de main d'œuvre pour les tests et la ressource disponible (les opérateurs présents)

*** j’essaie de savoir comment faire (une formule) pour savoir si on à la ressource suffisante pour réaliser les tests en tenant en compte les contraintes citées ci-dessus? Peut-être pas toutes les contraintes.
Regarde la pièce jointe planif2.xls
Sachant que pour la semaine 5, 33 produits sont à tester qui « fait 191 heures de temps main d’œuvre ». Sachant que l’on dispose 390h de ressource (nombre d’opérateur*nombre de jours présents*7:30).
Cela peut paraitre simple vue que nous avons plus de temps’ressource’ que de temps ‘MO testing’ mais Les tests se fond sur plusieurs jours, ce qui complique la situation, car ici les 191 heures sont concentrés (somme de tous les temps MO de testing).

Mervi d'avance

 

[jasmine]

Re : calcul de charge + planification

Bonsoir
Merci cisco, ceci dit, la derniére formule dans ton post 54 donne les mêmes résultat que celle dans le 51, juste que celle du 54 ne fait pas de calcul inutile, s'arrete une fois que le total de test à faire est atteint.

**************************************************
Par ou je dois commencer pour la VBA:


une question: la macro ne change pas si la furmule dans les cellules change??

Merci d'avance

 

[CISCO]

Re : calcul de charge + planification

Bonjour

Il ni a rien à faire dans le code. Il suffit de la lancer : --> Développeur --> Macro --> Sélection de la macro désirée --> Exécuter.

Dans le cas présent, la macro essai F9:
* efface les colonnes de droite (les anciens résultats, au cas où il y en aurait),
* relance le calcul aussi longtemps que nécessaire pour obtenir un OK dans O15,
* affiche les résultats obtenus dans les colonnes de droite,
* prend les conditions à respecter pour une autre semaine,
* relance les calculs jusqu'à avoir OK dans O15
* affiche les résultats obtenus dans les colonnes de droite pour cette nouvelle semaine
* prend les conditions à respecter pour une autre semaine...
etc

Normalement, cela fonctionne pour un nombre quelconque de semaines. Il suffit de remplir les colonnes I:N à partir de la ligne 17, 2 lignes par semaines.

Pour cette macro, le fait de changer les formules dans le tableau I4:N14 ne change rien dans la macro, puisque celle-ci ne fait que relancer le calcul des formules dans ce tableau, mais ne modifie pas ces formules.

@ plus

 

[jasmine]

Re : calcul de charge + planification

Bonjour cisco,
merci de ta promptitude, enfaite c'est le code que veux comprendre. ou il repend les conditions de la formule "SI..."

A+

 

[CISCO]

Re : calcul de charge + planification

Bonjour

La macro ne reprend pas les formules de la plage I4:O15, mais relance le calcul de cette plage grâce à cette ligne de code :

Range("I4:O15").Calculate

et fait cela jusqu'à avoir

Cells(15, 15) = "OK"

autrement dit O15 = "OK".

Autrement dit, les formules fonctionnent toutes seules (pour avoir une autre organisation possible des tests entre les divers employés, il suffit de faire F9, ce qui montrent bien qu'elles sont indépendantes de la macro), et la macro n'est là que pour :
* relancer ces calculs autant de fois que nécessaire pour obtenir OK dans O15 (appuyer sur F9 mille fois de suite, c'est plutôt rébarbatif, donc la macro le fait à notre place)
* faire ces calculs pour plusieurs semaines les unes après les autres
* afficher les résultats dans les colonnes de droite.

Si tu veux comprendre la macro, poses moi des questions par rapport aux lignes que tu ne comprends pas.

@plus

 

[jasmine]

Re : calcul de charge + planification

bonsoir
TU peux m'expliquer le:
if J=5000
ET

Range("I4:O15").Copy 'copie le tableau I4:O15 dans le presse papier (puisque o15="OK", et que les résultats conviennent
Range("R" & (4 + (i - 1) * 13)).PasteSpecial xlValues 'Colle les valeurs du presse papier à partir de la cellule dans la colonne R sur la ligne 4 + (i - 1) * 13
Range("R" & (4 + (i - 1) * 13)).PasteSpecial xlPasteFormats 'Colle le format de la plage I4:O15 contenu dans le presse papier à partie de la cellule dans la colonne R sur la ligne 4 + (1 -1)* 13
Range("Q" & (4 + (i - 1) * 13)) = "semaine " & i 'Colle le texte "semaine i" dans la colonne Q sur la ligne...
Range("Q" & (5 + (i - 1) * 13)) = j 'Colle j, le nombre de tirage ayant été utilisé pour obtenir un ensemble

Merci d'avance

 

[CISCO]

Re : calcul de charge + planification

Bonsoir

If j=5000

J'ai limité le nombre de tirage par semaine à 5000. Si O15 n'indique pas OK, la macro relance le calcul du tableau I4:O15, et cela jusqu'à obtenir soit O15 = "OK" soit J = 5000. S'il ni avait pas cette valeur limite, on peut imaginer des cas où la macro tournerai sans cesse... Si jamais tu viens à avoir certaines semaines posant problème (la macro ne trouve pas de solutions convenant), tu peux relancer la macro après avoir remplacé cette valeur par un nombre plus important...

Range("I4:O15").Copy

La macro copie dans le presse papier la plage I4:O15

Range("R" & (4 + (i - 1) * 13)).PasteSpecial xlValues

La macro colle uniquement les valeurs (donc pas les formules mais le résultat de ces formules) de la plage I4:O15 sur la cellule R(4 + (i - 1) * 13) et ses voisines, par exemple pour la 11ème semaine testée, en R134 (cette cellule est la cellule en haut à gauche de la plage collée). Cela permet de mettre les résultats de chaque semaine les uns en dessous de autres, sans écraser les résultats de la semaine précédente.

Range("R" & (4 + (i - 1) * 13)).PasteSpecial xlPasteFormats

Idem pour le format (pas indispensable puisqu'on n'a pas mis de format spécial dans le tableau I4:O15, mais, si tu veux modifier la présentation de la partie I4:O15, cette modification sera prise automatiquement en compte dans les colonnes à droite)

Range("Q" & (4 + (i - 1) * 13)) = "semaine " & i ...

La macro écrit dans la colonne Q semaine 1, semaine 2, semaine 3 ... pour que tu repères facilement à quelle semaine correspondent les valeurs dans les colonnes à droite

Range("Q" & (5 + (i - 1) * 13)) = j

La macro écrit dans la colonne Q, juste en dessous de semaine i, le nombre de tirages qui ont été nécessaires pour obtenir les valeurs présentées à droite (en réalité, c'est j - 1 et pas j). Cela donne une idée de la disponibilité de l'équipe : un petit nombre montre qu'il y a certainement moyen de faire faire davantage de tests, un grand nombre, qu'on approche les limites, le potentiel max de l'équipe. Pour mémoriser la signification de ce nombre, tu peux modifier cette ligne de code comme suit :

Range("Q" & (5 + (i - 1) * 13)) = j &" tirages au sort"

@ plus

 

[jasmine]

Re : calcul de charge + planification

ça commence à venir , je coprends mieux, en tout cas merci. donc je remplace:

Range("Q" & (5 + (i - 1) * 13)) = j 'Colle j, le nombre de tirage ayant été utilisé pour obtenir un ensemble de résultats convenant (nombre d'heures nécessaires pour les tests, ligne 15, corrects, et nbre d'heures hebdomadaires par employés <= 35 h)

PAR

Range("Q" & (5 + (i - 1) * 13)) = j " tirages au sort" Colle j, le nombre de tirage ayant été utilisé pour obtenir un ensemble de résultats convenant (nombre d'heures nécessaires pour les tests, ligne 15, corrects, et nbre d'heures hebdomadaires par employés <= 35 h)

 

[CISCO]

Re : calcul de charge + planification

Bonsoir

Attention, tous les caractères, et mêmes les espaces vides, peuvent compter dans un code. Ici, c'est

Range("Q" & (5 + (i - 1) * 13)) = j &" tirages au sort" 'Colle j, le nombre de tirage ayant été utilisé pour obtenir un ensemble de résultats convenant (nombre d'heures nécessaires pour les tests, ligne 15, corrects, et nbre d'heures hebdomadaires par employés <= 35 h)
qu'il faut écrire

Le & associe la valeur de j au texte " tirages au sort"
et l'apostrophe ' indique que la suite de la ligne est un commentaire, donc n'est pas du code.

@ plus

 

[jasmine]

Re : calcul de charge + planification

Bien noté merci,
je vais l'appliquer à mon fichier sur plusieur semaines pour voir ce que ça donne. je tiens au courant.
bonne journée
A+

 

[CISCO]

Re : calcul de charge + planification

Bonjour

Je suis en train de tester une autre méthode. Une petite question au passage : Est-ce que dans ton fichier réel tu as plus d'employés ou plus de types de test que sur le fichier exemple ?

@ plus

 

[jasmine]

Re : calcul de charge + planification

Bonjour
dans mon fichier, j'ai plus d'employés pour les mêmes types de tests.
c'est pour la macro?

merci et A+

 

[CISCO]

Re : calcul de charge + planification

Bonjour

J'essaye de trouver une méthode disant à coup sûr si il existe une répartition des horaires permettant d'effectuer tous les tests. La méthode précédente trouve des exemples, mais lorsqu'elle n'en trouve pas, cela ne veut pas dire qu'il ni y en a pas.

@ plus

 

[CISCO]

Re : calcul de charge + planification

Bonjour

Est-ce que tu as réussi à adapter le fichier et la macro à ton cas réel ? Si non, combien as-tu d'employés dans la réalité ?

@ plus

 

[jasmine]

Re : calcul de charge + planification

Bonjour cisco
je n'ai pas encore appliqué la macro à mon fichier. j'ai repris la tableau avec la formule revue ce week end.
Dans mon fichier, j'ai 14 Employés, dont:
3 interimaires (ne sont pas toujours present)
1 stagaire: present 1/2 MOIS
1 opérateur qui travail sur un autre projet (n'est pas toujours a son poste)
retenons 14 employés et en foction des 4 derniérs la ressource peut baisser ou augumenter.

 

[CISCO]

Re : calcul de charge + planification

Bonjour

J'ai adapté le fichier et la macro pour que cela fonctionne avec 16 employés au max. A toi de vérifier, un oubli est tellement vite arrivé.

@ plus