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

 

[CISCO]

Re : calcul de charge + planification

Bonsoir

Non, le 6 représente le nombre de chiffres après la virgule pris en compte.

Ex : 1,1212452 est arrondi à 1,121245

@ plus

P.S : Le fichier avec les semaines posant problème n'est pas avec ton dernier post... Pour que je puisse voir ce que cela donne avec la seconde méthode, il me faut bien sûr les habilitations de tes employés (mets des noms bidons).

 

[jasmine]

Re : calcul de charge + planification

bonjour
je n'arrive plus à joindre des fichiers, merci de voir ces semaines en image


A+

 

[CISCO]

Re : calcul de charge + planification

Bonjour

Ton fichier est peut être devenu tros gros, hors, comme je n'ai pas besoin de tout... Dans un nouveau fichier

1) ne mets que le petit tableau, présentant les données des 7 semaines à pb,
2) et un autre petit tableau avec les habilitations des "tes" employés, une fois les noms anonymés.

@ plus

 

[jasmine]

Re : calcul de charge + planification

RE,
fichier joint

A+

 

[CISCO]

Re : calcul de charge + planification

Bonsoir

Il est évident que ton entreprise ne pourra pas faire les tests de la semaine 8 avec cette équipe. Les 4 tests "Flexibles" nécessitent 74:00 h de travail au total, soit 18:30 h par test. Et comme tu n'as que l'opérateur G pour faire ce type de test, il ne pourra en faire qu'un seul (au mieux, 2 tests en faisant 2:00 h sup).

@ plus

P.S : Même type de problème pour la semaine 14

 

[CISCO]

Re : calcul de charge + planification

Bonsoir

En pièce jointe, quelques répartitions possibles pour les semaines 11, 15 et 23. Les autres semaines posent pb à cause des tests "Flexibles" à faire, qui nécessitent plus de 35 h, hors, comme il ni y a qu'un employé pour faire ce type de test...

Ces répartitions ont été obtenues très rapidement, mais cela m'a montré qu'il y avait une erreur dans ma macro dans les colonnes sans test à faire. J'ai arrêté la macro en route, le but ici n'étant pas de trouver toutes les répartitions possibles, mais de montrer qu'il en existe.

Je regarde davantage cette macro, et je la poste.

@ plus

P.S : Ces tableaux ne donnent pas le nombre d'heures attribuées à chaque employé dans la colonne, mais le nombre de tests que doit effectuer cet employé.

 

[jasmine]

Re : calcul de charge + planification

Bonsoir

Il est évident que ton entreprise ne pourra pas faire les tests de la semaine 8 avec cette équipe. Les 4 tests "Flexibles" nécessitent 74:00 h de travail au total, soit 18:30 h par test. Et comme tu n'as que l'opérateur G pour faire ce type de test, il ne pourra en faire qu'un seul (au mieux, 2 tests en faisant 2:00 h sup).

@ plus

P.S : Même type de problème pour la semaine 14

Bonjour Cisco
cela montre qu'il n'y pas assez de personne formé pour les flexibles. je trouve la macro parfaite. elle est un outil de discision dans la mesure ou on voit les semaine ou il manquerai de ressource et de prévoir des solutions comme (l'interim).

 

[jasmine]

Re : calcul de charge + planification

Bonsoir

En pièce jointe, quelques répartitions possibles pour les semaines 11, 15 et 23. Les autres semaines posent pb à cause des tests "Flexibles" à faire, qui nécessitent plus de 35 h, hors, comme il ni y a qu'un employé pour faire ce type de test...

Ces répartitions ont été obtenues très rapidement, mais cela m'a montré qu'il y avait une erreur dans ma macro dans les colonnes sans test à faire. J'ai arrêté la macro en route, le but ici n'étant pas de trouver toutes les répartitions possibles, mais de montrer qu'il en existe.

Je regarde davantage cette macro, et je la poste.

@ plus

re,
encore merci pour cette recherche, je continue de remplir le voir pour voir s'il ya d'autres semaines posant problème.

A+

 

[CISCO]

Re : calcul de charge + planification

Bonjour

Ci-joint deux macros permettant d'utiliser une autre méthode, listant toutes les répartitions possibles.

Le principe :
* Les formules dans le tableau I4:N16 donne le nombre max de tests que peut faire l'employé concerné s'il y consacre le maximum de temps possible, pour la colonne en cours. Dans I4, cela donne

SI((I$20=0)+(B4<>"X");0;MIN(ENT($O$1/(I$19/I$20));I$20))

Par ex, si l'employé concerné est habilité pour ce type de test, si l'équipe doit en faire cette semaine là 8 en 48:00 h (donc 6:00 h par test), il pourra en faire dans la semaine au max 5 en 30:00 h (puisqu'il ne peut pas faire plus de 35:00). Ce qui signifie que pour cette cellule, on a le choix entre toutes les valeurs entières allant de 0 à 5 incluses.

* Dans chaque colonne on obtient une série de nombres identiques , par ex 5, 5, 5 s'il y a trois employés habilités pour ce type de test. Ce qui signifie que le nombre de répartitions possibles pour cette colonne est 6 * 6 * 6 = 216 cas possibles.

* Mais on veut aussi que la somme des nombres de cette colonne soit égale au nombre de tests demandés, fixés par le cahier des charges, donné sur la ligne 20. Toujours avec l'exemple précédent, on veut faire 8 tests de ce type, et cela peut être fait avec
0 + 3 + 5 = 8
0 + 4 + 4 = 8
0 + 5 + 3 = 8
1 + 2 + 5 = 8
1 + 3 + 4 = 8
et ainsi de suite, chaque valeur étant forcément <= à 5, le total devant donner 8.

La première macro, nbredetestconvenanttableau, met en mémoire, toutes les répartitions possibles :
- correspondant à la première colonne (c-à-d au premier type de test), dans un tableau T1, en mémoire
- correspondant à la seconde colonne (c-à-d au second type de test), dans un tableau T2, en mémoire
et ainsi de suite jusque la colonne 6, dans T6.

Elle affiche dans I24:N24 le nombre total de répartitions possibles pour chaque colonne. Dans le fichier joint, cela donne 21 pour la première colonne, 12465 pour la seconde et ainsi de suite.

Les répartitions intéressantes pour tout le tableau I4:N16, convenant, sont une combinaison de ces diverses répartitions, colonne par colonne, stockées dans T1, T2.... et T6. Ce qui en fait 21*12465*....= 366 471 000 répartitions possibles pour l'exemple en pièce jointe.

* La seconde macro, résultatsconvenant, à l'aide de plusieurs boucles, liste toutes les répartitions possibles en piochant, dans l'ordre (et pas au hasard) dans T1, T2....
Plus exactement :
- elle prend dans T1 une première répartition pour la colonne 1 (I4:I16),
- prend dans T2 une répartition pour la colonne 2 (J4:J16),
- calcule le temps total nécessaire pour chaque employé pour réaliser les tests de ces deux colonnes,
- et si cela dépasse 35 h pour un des employés, prend une autre répartition dans T2, refait le calcul...

Lorsqu'elle a trouvé une répartition prise dans T1 et une autre prise dans T2 ne nécessitant pas plus de 35:00 h pour chacun des 13 employés, elle choisit une répartition dans T3 et recommence le calcul par rapport aux 35:00 h.

Lorsqu'elle a trouvé une répartition dans T1, une autre dans T2, une autre dans T3 ne nécessitant pas au total plus de 35:00 h pour chacun des 13 employés, elle choisit une répartition dans T4....

Lorsqu'elle a trouvé les 6 répartitions prises dans T1, T2, T3... et T6 donnant des totaux horaires inférieurs ou égaux à 35:00 h, elle affiche le tableau correspondant à droite et demande si l'utilisateur veut trouver une autre répartition.

En pratique, il faut forcément utiliser les deux macros l'une après l'autre, nbredetestconvenanttableau puis résultatsconvenant, à chaque fois. Chez moi, avec cet exemple, la première tourne 4 ou 5 minutes avant d'afficher les valeurs désirées dans I24:N24, la seconde 2 ou 3 secondes avant d'afficher la première répartition convenant. Pour traiter une nouvelle semaine, il faut remplir "à la main" la plage I19:N20 avec les valeurs correspondantes.

Il y a certainement plus simple, au niveau de l'écriture du code, vu qu'il y a plusieurs boucles qui se ressemblent beaucoup, mais bon, puisque cela semble fonctionner ainsi... Si je trouve un peu plus simple, je le posterais.

@ plus

P.S : Je n'arrive pas à faire afficher les totaux horaires sous la forme hh:mm dans la colonne X. Il y a presque toujours des 0 pour les secondes, alors que cela ne nous intéresse pas... Pour afficher des totaux horaires dépassant les 24 h, j'ai fait avec la ligne de code

Cells(compteur * 15 + 3 + i, 24) = Application.Text(IIf(totalemployé(i, 6) = 0, "", totalemployé(i, 6)), "[h]:mm")

Si quelqu'un a une solution, directement en VBA, pour ne pas avoir ces secondes... Merci d'avance.

 

[jasmine]

Re : calcul de charge + planification

Bonsoir,
merci , vu le nombre de cette macro, ça change change carrément de la premiére.
donc pour qu'on puisse avoir les solution, il faut impérativement les 2 macro...
j'ai bien avancé sur l'autre sur la premiére version, et pour toutes les semaines que j'ai fais jusque l'a sont OK.
j'essaie juste de voir comment alimenter ma base (automatique), que je n'ai pas à saisir. je termine LES 52 semaines pour voir ce que ça donne. s'il le faut pour les 7 semaines qui ne sont pas OK, je les adapterai à ta derniére solution.
merci
A+

 

[CISCO]

Re : calcul de charge + planification

Bonjour

Effectivement, l'idée, c'est :
* de faire tourner la première méthode (celle faisant des tests au hasard) avec toutes les semaines,
* et de n'utiliser la seconde méthode (celle testant toutes les répartitions "possibles" les unes après les autres) qu'avec les semaines posant problème.

La première risque en effet de passer à coté de la ou des solutions, s'il y en a peu, d'où l'utilité d'utiliser parfois la seconde, qui, elle, risque, de tourner parfois longtemps avant de les trouver.

Pour ce qui est de la seconde méthode, il y a certainement des améliorations possibles au niveau du code, et peut être des petites erreurs au niveau du nombre de répartitions possibles par colonne, à une ou deux unités près (je finis un peu par m'y perdre, entre tous les cas possibles...). J'essaye de vérifier tout cela.

@ plus

 

[CISCO]

Re : calcul de charge + planification

Bonjour

Dranreb a proposé vers la fin de ce fil un autre code permettant d'obtenir beaucoup plus rapidement les répartitions possibles. Une fois adapté à ton besoin, cela donne la macro test3 en pièce jointe. Teste la, tu verras, c'est beaucoup plus efficace.

@ plus