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

bonjour cisco,
pour répondre à ta question comment se présente mon tableau?

Mon tableau se présente comme suit : cf fichier EXEMPLE3
Un tableau avec tous les produits qui doivent être testé la semaine, le temps de MO nécessaire, la famille … et sur une deuxième feuille les horaires du personnel, présenté sous forme de (A, B, C ,J : ceux qui sont toujours de jours, et ceux qui sont postés) voir tableau 1.
Ce que j’ai regroupé dans le tableau 2 (nombre de jour présent* 7heures).
Pour simplifier le problème j’ai tout regroupé dans le tableau3, feuille 2 : ressource nécessaire et temps MO disponible, nombre de lots et temps de test des lots.

 

[CISCO]

Re : calcul de charge + planification

Bonjour

Feuille 2, que représentent exactement C3, C4, C5, C6.... J'ai l'impression que :

C3 = temps nécessaire pour effectuer les 4 tests Base 2X semaine 10
C5 = temps nécessaire pour effectuer les 8 tests Base 2X semaine 11
...
mais si c'était le cas, on devrait avoir C3/4 = C5/8 = C7/8 =... ce qui n'est pas le cas.

@ plus

 

[jasmine]

Re : calcul de charge + planification

hello,
OUI c'est bien:
C3 = temps nécessaire pour effectuer les 4 tests Base 2X semaine 10
C5 = temps nécessaire pour effectuer les 8 tests Base 2X semaine 11
donc pour les bases 2X il faut 16:30 pour les 4 LOTS
le calcul C3/4 c'est pour avoir le temps moyen pour faire 1 lot bases 2X.
comme c'est des temps differents d'une famille de produit à une autre, donc on ne peut pas avoir l'égalité: C3/4 = C5/8 = C7/8

?a+

 

[jasmine]

Re : calcul de charge + planification

autre chose:
dans une même familles, tu peux avoir des temps MO (produit) differents , c'est ce qui fait qu'on a pas C3/4 = C5/8 = C7/8.
c'est pas les mêmes produits chaque semaine et les temps MO moyen varient aussi.

 

[CISCO]

Re : calcul de charge + planification

Bonjour

OK, maintenant que j'ai compris, je vais pouvoir te faire ça assez rapidement.

En fait, pour que cela soit assez facile, il faut que je transpose les lignes et les colonnes dans mes dernières propositions, mais en prenant une durée de test variant chaque semaine.

@ plus

 

[CISCO]

Re : calcul de charge + planification

Bonsoir

Teste la macro en pièce jointe (Développeur --> Macro-->EssaiF9 -->Exécuter). Là, cela ne fonctionne que pour 4 semaines, mais on peut améliorer tout cela pour que cela fonctionne pour un nombre quelconque de semaines, et rajouter une ou deux sécurités pour qu'il ni ait pas de boucle infinie.

Dis moi si cela va dans le bon sens, et si cela te semble facilement adaptable à tes fichiers.

@ plus

 

[CISCO]

Re : calcul de charge + planification

Bonjour

Normalement, si un test dure par ex 3:00, toutes les valeurs dans la colonne correspondant à ce test devraient être un multiple, ou égales à cette valeur, ou nulles. Comme ce n'est pas toujours le cas dans le fichier précédent, c'est qu'il y a une ou des erreurs dans les formules du tableau K1424. J'essaye de corriger cela.

@ plus

 

[CISCO]

Re : calcul de charge + planification

Bonjour

Ce coup ci, je pense que c'est bon. Teste la macro, tu verras.
Dans S4, S17... sont donnés le nombre de tableaux tirés "au hasard" (mais en respectant certaines conditions telles que le nbre d'heures hebdomadaires) utilisés pour obtenir les tableaux avec OK présentés à droite. Si Excel fait 1000 tirages sans trouver de solution convenant, il affiche le dernier tableau obtenu avec un NON et passe à la semaine suivante.
Pour le moment, c'est peut être un peu long, mais c'est parce que j'ai laissé l'affichage des calculs à l'écran. Dans la version définitive, on n'affichera tout qu'à la fin.

@ plus

 

[jasmine]

Re : calcul de charge + planification

Bonjour,
je viens de télécharger les deux fichiers, jen prends connaissance puis je reviens vers toi... sinon j'avais une question sur le fichier:
EXEMPLE2ter.xlsx‎ s'il est toujour d'actualité,
1) si je peux avoir une explication de la fonction INDEX de la cellule Q15.
2)quand tu limites les chois possible( dans le tableau Q3:V13), peux-tu stp m'expliqué l'équation de la cellule O10.
3) la formule en W14, les 24 signifie 24HEURES???
suis pas trop forte en excel, et je veux vraiment comprendre les formules avec les quelles je dois travailller

merci

 

[CISCO]

Re : calcul de charge + planification

Bonjour

Bonjour,
je viens de télécharger les deux fichiers, jen prends connaissance puis je reviens vers toi... sinon j'avais une question sur le fichier:
EXEMPLE2ter.xlsx‎ s'il est toujour d'actualité,

Non, il n'est plus vraiment d'actualité vu les erreurs signalées précédemment

1) si je peux avoir une explication de la fonction INDEX de la cellule Q15.

La syntaxe de la fonction INDEX est de la forme INDEX(plage;ligne;colonne), par exemple INDEX(plage;3;4) qui renvoie le contenu de la cellule de la 3ème ligne de la 4ème colonne de la plage utilisée. Si la plage est verticale, on n'indique que la ligne, ex INDEX(A10:A20;3) renverra la valeur contenu dans A12. Si la plage est horizontale, on n'indique que la colonne directement. Ex : INDEX(A10:G10;3) donnera le contenu de C10.

2)quand tu limites les choix possibles (dans le tableau Q3:V13), peux-tu stp m'expliquer l'équation de la cellule O10.

Vu qu'elle n'est plus d'actualité... Je t'expliquerai plus tard les formules du dernier fichier, si besoin.

3) la formule en W14, les 24 signifie 24HEURES???

Oui. Excel compte le temps en jour, donc 1 heure = 1/24, 1 min = 1/(24*60), 1 s = 1/(24*60*60). Tapes dans une cellule 12:00, puis met cela au format nombre, avec 2 chiffres après la virgule. Tu verras le résultat.
On pourrait faire dans les équations sans les /24 en mettant le nombre d'heures hebdomadaires sous la forme 35:00 au format [h]:mm au lieu d'écrire comme je l'ai fait 35. Cela serait d'ailleurs plus "beau", plus cohérent dans les formules. Pour Excel, ce dernier 35 représente 35 j et pas 35 h.

@ plus

 

[CISCO]

Re : calcul de charge + planification

Bonsoir

Alors, pour t'aider, une "petite" explication de la formule dans K14.

Imaginons qu'il faille faire 4 (= K4) tests Bases2X en 22:00 (= K3), donc que chaque test prenne 5:30 (durée d'un test = K3/K4). Il faut tirer au hasard dans cette colonne des multiples de 5:30, ou mettre des 0, ou rien, tout en faisant le nécessaire pour que le total par colonne (= par type de test) soit égal à 22:00 et que le total par ligne ( = par employé) ne dépasse pas les 35 h hebdomadaires (= D1/24).

SI(K$4=0;"";--> s'il ni a pas de test à faire, on ne fait rien
-------------------------------------
puis une combinaison de trois tests SI(ET(test 1;test 2;test 3) réalisés sous la forme SI( test 1 * test 2 * test 3;

SI((D14="X") : --> Il faut que l'employé ait le droit de faire ce test

*(SOMME(K$13:K13)+K$3/K$4<=K$3) : --> et que la somme des données de la colonne+durée d'un test soit <= à K3 (la somme doit être <= à 22:00 et il faut encore pouvoir y ajouter au moins 5:30 tout en restant <= à 22:00) (rem : le * remplace un ET. Ex ET(1;1)=1*1=1, ET(1;0)= 1*0=0)

*(SOMME($J14:J14)+K$3/K$4<=$D$1/24) : --> et la somme des données de la ligne + durée d'un test soit <= à D1/24 pour ne pas dépasser les 35 h hebdomadaires
--------------------------------------
;(K$3/K$4)*ALEA.ENTRE.BORNES(0;MIN(ENT((K$3-SOMME(K$13:K13))/(K$3/K$4));ENT((($D$1/24)-SOMME($J14:J14))/(K$3/K$4))))
--> Si les trois tests précédents sont vrais, on multiplie la durée d'un test par un nombre entier pris au hasard entre 0 et la plus petite des deux valeurs suivantes :
ENT((K$3-SOMME(K$13:K13))/(K$3/K$4)) --> nombre de tests encore nécessaire pour s'approcher au max de la valeur K3 puisque (K$3-SOMME(K$13:K13)) = durée encore à faire dans la colonne, (K$3/K$4) = durée d'un test
ENT((($D$1/24)-SOMME($J14:J14))/(K$3/K$4)) --> nombre de tests nécessaires pour s'approcher au max de D1/24 puisque (($D$1/24)-SOMME($J14:J14)) = durée pas encore faite par l'employé de la ligne en cours pour atteindre les 35 h hebdomadaires, (K$3/K$4) = durée d'un test
(la plus petite des deux valeurs, parce qu'avec la plus grande, l'autre critère, =K3 ou <=D1/24, ne serait pas forcément respecter)
--------------------------------------
;"")) --> Si les trois tests précédents ne sont pas tous vrais (ou l'employé n'a pas le droit de faire ce test, ou on ne peut pas rajouter la durée d'un test dans la colonne sans dépasser le total nécessaire en K3, ou on ne peut pas rajouter la durée d'un test sans dépasser le total de 35 h sur cet ligne pour cet employé), on ne fait rien.

Il faut faire attention aux $ utilisés, puisque certains calculs se font par rapport à la ligne en cours, d'autres par rapport à la colonne en cours.

Comme on tire les valeurs au hasard, on obtient bien un total par ligne (par employé) inférieur à 35:00, ce qui convient, mais pas forcément un total par colonne (par type de test) égal à la durée totale nécessaire pour le type de test en cours, Base2X, Base1X..., ce qui ne convient pas. On refait donc, avec F9, un tirage pour obtenir les bons totaux par colonne, permettant la réalisation de tous les tests données ligne 4. Comme on n'obtient pas forcément les bons résultats au bout de 3 ou 4 tirages, c'est plus simple de faire un grand nombre de tirages dans une boucle dans une macro, boucle qui s'arrête lorsque tous les totaux sont corrects, ou lorsque le nombre max de tirage autorisé est dépassé.

@ plus

 

[jasmine]

Re : calcul de charge + planification

Bonsoir
J'avais commencé a me documenter sur macro pour comprendre le principe...pour pas poser de question bête et a vrai dire je ne comprends pas grand chose.
J'essaie d'abord de comprendre comment est construite la macro? Les cellules s4,17..
Ps: ton dernier post me fera surement dormir 0_0 oulaaa
Merci pour l'explication, faut vraiment que je me couche là dessus.
A+

 

[CISCO]

Re : calcul de charge + planification

Bonsoir

Comme on ne peut mettre dans une colonne qu'un multiple de la durée du test, ou cette durée, ou 0, on peut un peu simplifier la formule utilisée dans le tableau K1424.
Par exemple, dans K14, on peut remplacer
*(SOMME(K$13:K13)+K$3/K$4<=K$3) par *(SOMME(K$13:K13)<K$3).
De même, on peut remplacer ENT((K$3-SOMME(K$13:K13))/(K$3/K$4)) par (K$3-SOMME(K$13:K13))/(K$3/K$4).

J'ai fait la première modification dans la pièce jointe précédente, pas la seconde car j'ai l'impression qu'il faut alors plus de tirages pour obtenir des cas convenant.

@ plus

 

[CISCO]

Re : calcul de charge + planification

Rebonsoir

Bonsoir
J'avais commencé a me documenter sur macro pour comprendre le principe...
A+

Essaye d'abord de comprendre les formules qu'il y a dans le tableau K1424. La macro ne fait que répéter des tirages les uns après les autres jusqu'à avoir :
K2525 contenant les durées totales nécessaires, aussi données dans K33
et Q14:Q24 ne contenant que des valeurs inférieures ou égales à 35 h.

Je t'expliquerais plus tard comment cette macro fonctionne.

@ plus

 

[CISCO]

Re : calcul de charge + planification

Bonsoir

En pièce jointe, une "amélioration" de la formule dans le tableau K1424.
Dans le fichier précédent, on utilisait une formule du style, en colonne K :

SI(K$4=0;"";
SI(condition1*condition2*condition3;
(K$3/K$4)*ALEA.ENTRE.BORNES(0;MIN(ENT(nbre de tests encore possible par rapport à la colonne pour obtenir K3; nbre de tests encore possible par rapport à la ligne pour obtenir 35 h hebdomadaires))
;""))

Maintenant, on fait avec

SI(K$4=0;"";
SI(condition1*condition2*condition3;
(K$3/K$4)
*SI(NBVAL(D15:D$25)=0;
MIN(nbre de tests encore possible par rapport à la colonne pour obtenir K3; nbre de tests encore possible par rapport à la ligne pour obtenir 35 h hebdomadaire);
ALEA.ENTRE.BORNES(0;MIN(nbre de tests encore possible par rapport à la colonne pour obtenir K3; nbre de tests encore possible par rapport à la ligne pour obtenir 35 h hebdomadaire)))
;""))

ce que je peux encore écrire sous la forme, pour rendre la modification encore plus évidente

SI(K$4=0;"";
SI(condition1*condition2*condition3;
(K$3/K$4)*SI(NBVAL(D15:D$25)=0;MIN(X;Y);ALEA.ENTRE.BORNES(0;MIN(X; Y));""))

et en donnant un nom à la partie MIN(X;Y), qui se trouve deux fois dans la formule, on obtient par exemple :

SI(K$4=0;"";SI((D14="X")*(SOMME(K$13:K13)<K$3)*(SOMME($J14:J14)+K$3/K$4<=$D$1/24);(K$3/K$4)*SI(NBVAL(D15:D$25)=0;MINnbredetests;ALEA.ENTRE.BORNES(0;MINnbredetests));""))

Autrement dit, si le total de la colonne en cours n'est toujours pas égal à K3, et si la cellule en cours correspond au dernier employé pouvant faire ce type de test (dernier X dans la colonne D), on ne fait plus de tirage au sort, on prend le MIN de...

Autrement dit, dans les "dernières" lignes du tableau, on force le système pour se rapprocher le plus possible du total désiré donné en K3.

Avec l'ancienne méthode, on obtenait les bons résultats certaines fois après 150 ou 200 tirages. Maintenant, dans les cas traités, la solution est trouvée après 7 ou 8 tirages, et souvent avec 2 ou 3. Ca va beaucoup plus vite.

@ plus

P.S : Attention, dans la formule, c'est bien NBVAL(D15: D$25)=0 et non NBVAL(D$15: D25)=0 car c'est la fin de la colonne D qui nous intéresse.