Rapport de message :*
 

Re: Planning Horaire

Titre du sujet : Re: Planning Horaire
par JeanMarie le 20/12/2008 18:31:55

Bonjour Feutrine, le forum
Feutrine, je pense que la MFC reste pour ton problème la meilleure des solutions, je ne vois pas l'insertion d'un userform plus pratique par rapport ta gestion actuelle de tes chantiers.
J'ai pas mal modifié ton fichier exemple, tu y trouveras trois feuilles plannig, dont la gestion de la MFC est différente.
  • Dans la feuille "Planning", les cellules contiennent les formules de gestion de l'activité.
  • Dans la feuille "Planning (2), les cellules ne contiennent aucune formule, les formules étant intégrées à la MFC
  • Dans la feuille "Planning (3), qui est identique à la première feuille, la MFC utilise =mDF qui permet d'utiliser plus de 3 conditions.mDF MFCmultiples à la loupe

Des modifs mineures :
  • La formule permettant de calculer la première date du mois sélectionné est remplacée par la fonction DATEVAL
  • Le contrôle du mois pour les jours suivant a été supprimé, seul restant test les trois derniers.(29; 30 et 31)
  • Les soustractions (-1) des formules de la colonne (A) ont été supprimées et remplacées par une référence dans la fonction LIGNE().

Des modifs plus conséquentes :
  • Insertion en première ligne d'une constante, donnant la référence de la cellule ligne 4, permettra de calculer une plage variable.
  • Les certaines plages nommées supprimées et remplacées par une formule dans la colonne B à G
      En colonne B : Valeur numérique de la colonne de début de mise en Fabrication, décalée des heures non effectives de la 1ère journée.

      En colonne C : Nb d'heures déjà réalisées (en négatif sur la feuille "Planning")

      En colonne D : Nb d'heures planifiées en fabrication (en négatif sur la feuille "Planning")

      En colonne E : Référence numérique de la colonne de début de la Pose, décalée des heures non effectives de la 1ère journée.

      En colonne F : Nb d'heures déjà réalisées (en possitif sur la feuille "Planning")

      En colonne G : Nb d'heures planifiées en pose (en possitif sur la feuille "Planning")


Formule B :
=SI(A6="";"";EQUIV(INDIRECT("Chantiers!X"&LIGNE(B4));$3:$3;0)+EQUIV(INDIRECT("Chantiers!Y"&LIGNE(B4));{8;9;10;11;14;15;16;17};0)-1)
EQUIV(INDIRECT("Chantiers!X"&LIGNE(B4));$3:$3;0)
retourne le N° de la colonne contenant la date de mise en fabrication du chantier
EQUIV(INDIRECT("Chantiers!Y"&LIGNE(B4));{8;9;10;11;14;15;16;17};0)-1,
retourne la position de la variable heure début dans la matrice. -1 est nécessaire puisque 8h est sur la même colonne que la valeur Date de fabrication.

Formule C :
=SI(A6="";"";-INDIRECT("Chantiers!AA"&LIGNE(B4)))
INDIRECT("Chantiers!AA"&LIGNE(B4))), permet de s'affranchir d'un message d'erreur #REF! lors d'une suppression d'une ligne dans la feuille "Chantier", (ceci pour les compléter les explications).

Formule D:
Identique dans la forme à la formule de la colonne C, mais en retournant la valeur de la colonne Z
Nota : l'ordre de C:D sera importante pour les formules de la feuille "Planning (2)" et "Planning (3)"

Formule retournant le code qui servira pour la MFC. (en H6)
=SI(OU(H$5="";$A6="");" ";SI(COLONNE()>=$E6;NB(INDIRECT(H$1&":"&ADRESSE(5;$E6)));SI(COLONNE()>=$B6;-NB(INDIRECT(H$1&":"&ADRESSE(5;$B6)));"")))
OU(H$5="";$A6="")
 il est VRAI quand le jour n'est pas travaillé ou quand le nom de Chantier est inconnu, alors " " est retournée, quand les deux données sont fausses le calcul du code de couleur pour la MFC est effectué.
SI(COLONNE()>=$E6;NB(INDIRECT(H$1&":"&ADRESSE(5;$E6)))
COLONNE() retourne le N° de la colonne. Ici on est en H6, la valeur retournée sera 8, (en I6 donnera 9, etc)
Quand la valeur de la colonne sera supérieure ou égale à la position de la date de début de fabrication, on détermine le code couleur.
INDIRECT(H$1&":"&ADRESSE(5;$E6))
retourne une plage de la ligne 5, allant de la colonne (H$1) à la colonne ($E6) date de début de fabrication.
NB(....) comptabilise le nombre d'heures effectives de fabrication dans la plage calculée. C'est là qu'intervient une très très belle idée, (qui n'est pas de moi). En supprimant par ("") les heures d'une journée de samedi, dimanche ou de jours fériés, dans la ligne 5, on facilite la gestion des heures non travaillées dans le reste du tableau. (j'adore).

Les MFC de la feuille "Planning"
Les valeurs issues de NB évolues dans le temps, par la gestion de l'opérateur "est compris entre", et en indiquant les bonnes valeurs, on arrive à formater les bonnes couleurs. La différence les heures en fabrication et les heures en pose, est fait par le passage de la valeur négative ou positive.
A la différence des deux autres feuilles planning où les valeurs retournées par les formules sont des codes de couleur,
  • 1 = couleur "Bleu"
  • 2 = couleur "Rouge"
  • 3  et 6 = fond de couleur neutralisé 
  • 4 = couleur "Jaune"
  • 5 = couleur "Orange"
Dans les deux feuilles "Plannig (2)" et "Planning (3)", les formules sont basés sur la mëme base, mais j'ai intégré un codage de la valeur NB

EQUIV(1;FREQUENCE(...;$F6:$G6);0)

Formule pour la gestion de heures des samedis, dimanches et jours fériés. 
=SI(JOURSEM(H3;2)<6;SI(ESTNA(EQUIV(H3;Listes!$A:$A;0));8;"");"")
par JOURSEM on contrôle le n° du jour, si celui-ci est supérieur à 5 c'est un samedi ou un dimanche
par ESTNA(EQUIV(H3;Listes!$A:$A;0)) permet de déterminer si c'est la date H3 est présente dans la plage des jours fériés Listes!$A:$A. EQUIV retourne un message d'erreur quand la valeur n'est pas trouvée, on teste ce message d'erreur par la fonction ESTNA 

Attention, si une date prévision de Fabrication ou de Pose porte sur deux mois, la sélection du 2ième mois n'affichera pas l'encours. Une proposition pour résoudre ce problème simplement, serait de calculer cellule H3, la valeur mini de la plage Liste!X:X et Liste!AC:AC

Bonne soirée.

@+Jean-Marie