Planning. MFC si horaires inférieur à 11h

[vincentbzh]

Bonjour à vous tous,
J'ai une question sur l'un de mes problèmes.
Je voudrais créer une mise en forme conditionnelle si entre la fin de prise de service et la prise de service il y a moins de 11h suivant les différents cas proposés dans le fichier joint (une journée se compose de deux tranches horaire et entre la fin de semaine et le début de la semaine suivante).
Merci pour votre aide car je ne vois pas du tout comment faire.
Bon dimanche.

 

[CISCO]

Bonjour

Tu es bien matinal, Vincent, à Quiberon...

La nouvelle version utilise la même MFC que précédemment, par contre, c'est le contenu des colonnes supplémentaires qui a changé :
* Il ni a plus que 5 colonnes supplémentaires, au lieu de 8.
* Les colonnes B et C ne servent à "rien", sauf que grâce à elles, je n'ai pas besoin de modifier la définition de jourhfin1 qui ne fonctionne bien dans tous les cas que si chaque jour comporte 4 colonnes.
* La colonne D contient une formule donnant le dernier hdébut1 de l'employé de la semaine précédente (Pour D16, c'est le dernier hdébut1 de la ligne 4)
* Même principe pour la colonne E qui donne le dernier hfin1 de l'employé de la semaine précédente.
* La colonne AH donne le premier hdébut2 de l'employé de la semaine suivante (Normalement, si cette semaine ne comprend par exemple que des jours de repos, cela devrait donner le premier hdébut2 non pas de la semaine suivante, mais de celle qui suit ensuite. Et ainsi de suite de proche en proche s'il y a plusieurs semaines de repos. A toi de tester davantage).

Dans B13, B25 etc, tu peux mettre n'importe quelle date, ou même rien (L'information dans ces cellules n'est pas utilisée dans les calculs et les données en dessous ne correspondent pas aux dates dimanche 11, dimanche 18. La MFC va toujours utiliser les dates de la ligne 1. Même si c'est faux (sauf pour la première semaine), ce qui compte c'est que jourhfin1 soit <> de jourhdébut2 ou pas).

Pour bien comprendre, une solution :
* Tu sélectionnes une cellule, par ex P5.
* Clique sur Formules puis Gestionnaire de noms
* Cliques sur hdébut1 puis sur sa définition.
Excel encadre H5 avec des pointillés. Cette cellule donne hdébut1 correspondant à P5.
Si tu fait pareil avec hfin1, cela mettra en valeur I5.
Pour hdébut2, il faut supprimer le +1 à la fin de la définition avant de faire la manip décrite ci-dessus (Ne pas oublier de remettre le +1 après avoir fait cette vérification) (Excel utilise comme unité de temps le jour, donc +1 = + 24 h). Excel mettra R5 en valeur.

En simplifiant un peu, la MFC met en rouge les cellules de I5 à R5 si elles ne contiennent pas le mot "Repos" et si hdébut2 - hfin1 < 11/24 donc si R5+1 - I5 <11/24


@ plus

 

[vincentbzh]

Bonjour

En relisant mon dernier post, cela me fait penser qu'il y a certainement un problème si le lundi suivant (détaillé dans les 4 colonnes de droite) est de repos. En effet, la formule définissant hdébut2 devrait alors aller chercher les informations dans la plage du mardi (ou du mercredi si le mardi est aussi un jour de repos), ce qu'elle ne fait pas.... Grrr. Il faut donc mettre dans les 8 colonnes supplémentaires les informations correspondants au dernier jour et au premier jour travaillés, et pas forcément celles du dernier dimanche et du premier lundi. Je regarderai ça plus tard.

@plus

 

[vincentbzh]

Et bien bonsoir,
J'esai de t'envoyer le fichier complet (si il passe ?)
Car là, je bloque ? il me met une phrase quand je veux copier dans la colonne AH.
Peux tu regarder ?
Petite question : Ça va se recopier pour les autres mois ?
Encore merci pour le temps que tu passes pour moi....
PS : et oui je commence tôt le matin
Bonne soirée

 

[CISCO]

Bonsoir

Cf. en pièce jointe, uniquement pour le mois de novembre.

Il faudra compléter la partie AH104:AH123 à la main, ou avec des formules. Pourquoi as-tu mis juste au dessus vendredi 1er janvier ?

Et bien bonsoir,
Petite question : Ça va se recopier pour les autres mois ?
Bonne soirée

Et non, il faut réécrire les 9 noms pour chaque feuille. Ce sont exactement les mêmes formules, mais à la place de nov!, il faut Déc!, Janvier! (en faisant attention à l'orthographe, à l'accent près) et ainsi de suite (Sauf si je trouve une astuce !!! Ne te précipite pas trop, je vais essayer de trouver plus simple).

Dans la pratique, tu n'as pas besoin d'écrire la définition des nouveaux noms avec le nom de l'onglet correct. Au contraire, il faut se placer dans la bonne cellule sur la nouvelle feuille (Déc par exemple), et écrire, ou mieux, coller la définition dans le gestionnaire de noms sans le nom de l'onglet en cours. Excel rajoutera automatiquement le nom de la feuille dans cette définition.

Ainsi, en ayant sélectionné la cellule Déc!D35 avant et en collant dans le gestionnaire

=DECALER($B12;;MAX(SI(ESTNUM($B12:AG12)*EST.IMPAIR(COLONNE($B:AG));COLONNE($B:AG)-3)))

, Excel écrira automatiquement

=DECALER(Déc!$B12;;MAX(SI(ESTNUM(Déc!$B12:AG12)*EST.IMPAIR(COLONNE(Déc!$B:AG));COLONNE(Déc!$B:AG)-3)))

Mais cela, il te faut le faire 98 fois !!!!! en mettant un nouveau nom à chaque fois.

@ plus

P.S : 30/03/2017. Modification de la pièce jointe.

 

[CISCO]

Bonjour

Comme tu as 12 mois, cela fait 12 x 9 = 98 noms à rentrer. Grrrr. Pas pratique du tout.

J'essaye de simplifier tout cela, pour que tu n'ai pas 98 noms à rentrer, dans la journée ou ce soir... Pas évident que je puisse. Demain peut être.

@ plus

P.S : De plus, comme j'ai utilisé la fonction DECALER, qui est une fonction volatile (c-à-d qui est recalculée dès qu'on modifie une cellule de la feuille, même s'il ni a pas de lien entre la cellule modifiée et la cellule contenant la fonction DECALER), c'est certainement mieux faire avec la fonction INDEX.

 

[vincentbzh]

Bonsoir

Cf. en pièce jointe, uniquement pour le mois de novembre.

Il faudra compléter la partie AH104:AH123 à la main, ou avec des formules. Pourquoi as-tu mis juste au dessus vendredi 1er janvier ?



Et non, il faut réécrire les 9 noms pour chaque feuille. Ce sont exactement les mêmes formules, mais à la place de nov!, il faut Déc!, Janvier! (en faisant attention à l'orthographe, à l'accent près) et ainsi de suite (Sauf si je trouve une astuce !!! Ne te précipite pas trop, je vais essayer de trouver plus simple).

@ plus

Bonjour

 

[vincentbzh]

Bonjour Cisco,
Ok j'attends de voir si tu trouve une solution...
Bonne soirée à toi

 

[CISCO]

Bonjour

Pourrais-tu me dire ce que l'on devrait avoir pour B. Thomas si on met dans F39 par ex 3:00 dans ma dernière pièce jointe ?

@ plus

 

[CISCO]

Bonjour

Cf. en pièce jointe.
J'ai modifié le contenu des 9 noms hdébut, hfin1... pour ne plus avoir à les définir dans chaque feuille. Puisqu'Excel écrit automatiquement le nom de la feuille utilisée si on écrit dans la définition de ces noms des plage du style A9:AH9, ce qui nous gêne dans le cas présent, j'ai contourné le problème en définissant tout une série de plages avec la fonction INDIRECT.
$A$9:$AH$9 devient INDIRECT("A9:AH9") (nommé PlagedateA9àAH9 dans le gestionnaire). Dans ce cas, comme Excel voit dans un premier temps "A9:AH9" comme du texte, il ne rajoute pas le nom de la feuille en cours devant. La définition est donc bonne sur toutes les feuilles du fichier.
Autre exemple, si on se place dans F12, F12:$AH12 devient, INDIRECT(ADRESSE(LIGNE();COLONNE())&":AH"&LIGNE()) ( (nommé PlagecolencoursàAH dans le gestionnaire)

Dans la feuille Déc, j'ai rajouté les 4 colonnes à gauche et la colonne à droite nécessaires. J'y ai collé les formules prises sur la feuille Nov. J'y ai ensuite rajouté la mise en forme =SI((F12<>"Repos")*(jourhfin1<>jourhdébut2);hdébut2-hfin1bis<11/24) et il semble que cela fonctionne.

Il te reste :
* à essayer de comprendre comment cela fonctionne
* faire des tests pour voir s'il ni y a pas d'erreurs
* faire de même sur les autres feuilles (Il ni y a pas de nouveaux noms à définir dans le gestionnaire, mais il reste à insérer les colonnes, à y coller les formules, à modifier certains formats, à rajouter la MFC...)

Il reste deux trucs à améliorer. Cette version ne transfère pas de données d'une feuille à la suivante, or il le faudrait :
* Nov!AH104:AH123 devrait contenir des informations provenant de Déc!F12:AH31 (les hdébut2 de la première semaine de décembre)
*Déc!E12:E31 devrait être remplie automatiquement avec des valeurs provenant de Nov D104:AG123 (les derniers hdébut1 et hfin1 de la dernière semaine de novembre).

On verra ça un peu plus tard... Pour le moment, ça fume assez dans ma petite tête...

@ plus


PS : J'ai aussi remplacé les DECALER par des INDEX.

 

[vincentbzh]

Bonjour Cisco.
Là je viens d'essayer de créer janvier et ça semble super bien marché... Félicitations à toi ! et encore un grand merci.
juste un petit soucie lors de l'ouverture du fichier (je l'ai mis en pièce jointe).
J'espère que ça fume pas trop dans ta tête en voyant ce message...
Bonne fin d'après midi

 

[vincentbzh]

Bonjour Cisco.
Là je viens d'essayer de créer janvier et ça semble super bien marché... Félicitations à toi ! et encore un grand merci.
juste un petit soucie lors de l'ouverture du fichier (je l'ai mis en pièce jointe).
J'espère que ça fume pas trop dans ta tête en voyant ce message...
Bonne fin d'après midi

 

[vincentbzh]

Re bonjour...
Ok pour le message, il n'y en a plus ...
Tu peux reposer ta tête...

 

[CISCO]

Bonjour

Effectivement, il y a un message indiquant qu'il y a une ou des références circulaires sur ma dernière pièce jointe. Même si le fichier semble fonctionner correctement, il faut se méfier de ce genre de situation, source de problème. J'essaye de trouver pourquoi. Par précaution, histoire que tu ne fasses pas du travail pour rien, arrête, STP, de modifier le fichier pour le moment.

@ plus

 

[CISCO]

Rebonjour

Apparemment, j'avais oublié d'effacer une formule dans Nov!F131, formule que j'avais utilisée pour vérifier des résultats. Tu peux l'enlever. Je n'ai plus de message m'indiquant la présence d'une référence circulaire après l'avoir fait.

@ plus

 

[CISCO]

Bonjour

Ci-joint, un fichier prenant en charge le passage des données utiles d'une feuille à la suivante.

Pour que cela fonctionne, j'ai défini les noms :
* Nomsfeuilles (qui donne la liste des noms des feuilles)

=STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")

(Merci J. Boisgontier)
* En ayant sélectionné Nov!AH104, PlageligsemsuivantedeFàAHfeuillesuivante et premierhdébut2semsuivantefeuillesuivante qui donne le hdébut2 de la semaine suivante sur la feuille suivante
Dans Nov!AH104, on écrit =SI(premierhdébut2semsuivantefeuillesuivante="";"";premierhdébut2semsuivantefeuillesuivante), formule que l'on tire vers le bas
* En ayant sélectionné Déc!D12, PlageligsemprécédeAàAHfeuilleprécé et derhdébut1semprécédfeuilleprécé qui donne le hdébut1 de la semaine précédente à la fin de la feuille précédente. Dans Déc!D12, on colle la formule =SI(derhdébut1semprécédfeuilleprécé="";"";derhdébut1semprécédfeuilleprécé), formule que l'on tire vers le bas, uniquement pour la 1ère semaine
* En ayant sélectionné Déc!E12, derhfin1semprécédfeuilleprécé qui donne le hfin1 de la semaine précédente en bas de la feuille précédente.
Dans Déc!E12, on écrit =SI(derhfin1semprécédfeuilleprécé="";"";derhfin1semprécédfeuilleprécé), formule que l'on tire vers le bas, uniquement pour la 1ère semaine

Ne reste plus qu'à tester et à mettre les mêmes formules sur toutes les feuilles.

@ plus