XL 2010 Détecter les périodes de <44 h. libres pendant 7 jours

[Aloha]

Bonjour,
Le titre est un peu compliqué; je l'explique.
Le contexte: des salariés ont droit à 1 jour de congé supplémentaire lorsque pendant 7 semaines consécutives ou non ils ne sont pas libres pendant 44 h. de suite par semaine.

Ils remplissent chacun une fiche mensuelle où ils saisissent toutes les heures en relation avec le contrat de travail, heure par heure, en inscrivant dans les cellules le type d'heure : travail ("X"), congé ("C"), maladie ("M"), heures supplémentaires (6 sortes, de "S1" à "S6"), réunions ("R"), etc. Ces fiches sont regroupées dans un classeur dont le nom se compose du nom du service, du mois et de l'année ([Mois] [Année] [SERVICEX].XLSX)

Admettons que je sois arrivé à regrouper ces données mois par mois et salarié par salarié (pour l'instant je le simule dans le fichier ci-joint; je dois m'occuper de ce problème par après) dans une base de données, en y copiant (de préf. par VBA, sinon par formule), les données requises de toutes les fiches de tous les mois et de tous les services.

Ce qui donne, en gros, un fichier comme celui qui est ci-joint.

J'ai donc compté jour par jour dans les fichiers remplis par les salariés les cases horaires (00:00 à 24:00, puisque le service fonctionne nuit et jour tous les jours de l'année) où il y a une inscription (colonne D) et j'ai retranché ce chiffre de 24, ce qui me donne donc les heures libres par période de 24 heures (colonne E). La colonne F tient compte de la date d'engagement et ne reprend les valeurs de la colonne E que pour la période du contrat de travail, sinon "HC" (hors contrat) est inscrit dans la case correspondante.

Il s'agit donc à présent de détecter, 7 jours par 7 jours, les périodes où dans la colonne F l'addition de 2 cellules superposées ne donne pas au moins 44, en commençant, pour déterminer les périodes de 7 jours, par le 1er janvier, resp. par la date d'engagement si le salarié a été engagé pendant l'années en cours,

 

[Aloha]

Bonjour,
Je n‘ai pas encore réussi à faire fonctionner les formules en FN.
A part ce problème, les choses se compliquent: dans mon fichier exemple j‘ai mis une bonne partie de l‘année dans un seul tableau.
En réalité il s‘agit de tableaux mensuels, ce qui signifie que je dois garder de janvier les infos nécessaires pour commencer les calculs en février; ces données sont stockées par VBA dans une base de données dans un autre fichier et pour faire la fiche de février elles sont récupérées dans la base de données moyennant des formules.
A cet effet j‘ai interrompu le tableau exemple à la fin de janvier et j‘ai prévu fin janvier des cellules pour afficher les données importantes pour commencer février et viceversa.
J‘aurais dû construire le tableau dès le début de cette sorte.
Bonne journée
Aloha

 

[CISCO]

Bonjour

Ca se complique. Je regarderai ça plus tard...

@ plus

 

[Aloha]

Bonjour,
Maintenant que j‘ai étudié l‘affaire je me rends compte que le fait de partager l‘année en mois a un impact bien plus grand sur certaines formules que je n‘avais pensé:
FH80 a besoin des données de AC72 et FI72
FI80: AC72 et FI72
FL80: là les choses commencent à se compliquer puisqu‘il ya une référence non pas à une cellule du mois précédent maisà une aire: FK$5:FK72
FM80:
*plus compliqué encore: une référence allant du dernier jour du mois précédent jusqu‘à la fin de la (à remplacer par la fin du mois): F72:AC$243
* FM72
FN80: FK$5:FK72, FM72, FM$5:FM72
FP80: FP$5:FP72
A+
Aloha

 

[CISCO]

Bonjour

Bonjour,
Maintenant que j‘ai étudié l‘affaire je me rends compte que le fait de partager l‘année en mois a un impact...

FL80: là les choses commencent à se compliquer puisqu‘il ya une référence non pas à une cellule du mois précédent mais à une aire: FK$5:FK72...

Aloha

Et oui. INDEX(FK$5:FK72;EQUIV(9^9;FK$5:FK72;1)) va chercher la dernière date+heure de fin du travail dans la colonne FK, au dessus de la ligne en cours (80).

Dans la pratique, on peut peut être réduire cette plage, ne pas la faire commencer en ligne 5. Pour autant, on ne peut pas prendre en compte une seule cellule, FK72, justement parce qu'on ne sait pas à l'avance le dernier jour (la dernière ligne) de travail de cette personne.

Il y a du travail sur la planche pour trouver le truc vraiment polyvalent dans tous les cas...

@ plus

 

[Aloha]

Il faudrait arriver à inscrire les données requises pour continuer les calculs le mois prochain dans les cellules colorées en-dessous de la feuille mensuelle. Mais comme je ne comprends pas vraiment toutes tes formules, je ne sais pas juger si c'est possible pour toutes les informations requises.

Théoriquement, théoriquement, il y aurait moyen de copier par VBA toutes les saisies mensuelles par personne dans une banque de données à part (ce qui donnerait donc un tableau comme mon tableau annuel initial), respectivement les résultats des formules. Seulement, il s'agit de plus de 100 salariés!

A+
Aloha

 

[CISCO]

Bonjour

Deux questions :

1) Pourquoi avoir mis les lignes 6 et 8 vides ?
2) Est-ce qu'on ne pourrait pas tout mettre bout à bout, sans introduire les lignes avec les cellules vertes ? Que t'impose exactement la macro à ta disposition ?

@ plus

 

[Aloha]

Re,
Je dois m‘orienter d‘après le fichier réel (je me répète en regrettant de ne pas l‘avoir fait de suite; mea culpa), et ce fichier est établi par service et il contient une feuille pour chaque salarié. A la fin du mois il est expédié à l‘administration qui lance une macro qui copie toutes les données importantes du mois (heures de travail, de congé, de réunion, de maladie, de formation etc.) dans une banque de données. Une autre macro fait une feuille comme celle de mon exemple modifié, tenant compte des données personnelles (heures de travail que le salarié doit effectue toute l‘année, les heures de congé auxquelles il a droit, etc.) dont sont retranchées les heures déjà effectuées qui sont extraites par formules de la base de données pour pouvoir afficher les heures restantes. Avant de passer au salarié suivant la macro fige les chiffres extraits par les formules en remplaçant les formules par les valeurs.
Je suis donc tenu à l‘architecture de ces fiches.
Le fait que les lignes 6 et 8 sont vides est dû à une erreur.
A+
Aloha

 

[Aloha]

Re,
je me suis rendu compte que la raison que j'ai évoquée pour expliquer pourquoi les lignes 6 et 8 sont vides ne correspond pas à la réalité
Cela tient à une autre particularité (que j'aurais pu intégrer dans le fichier exemple mais qui ne doit pas poser problème) du fichier:
Pour afficher sur toutes les feuilles mensuelles toutes les données importantes 31 cellules ne me suffisaient pas. Voilà pourquoi j'utilise seulement une ligne sur 2 et j'ai reliées à chaque fois 2 cellules superposées.
Lorsque j'ai cherché la raison pour laquelle les formules en FN renvoient un FAUX, j'ai reproduit cette situation pour que les références de tes formules correspondent exactement à celles du fichier réel. D'ailleurs je n'ai pas trouvé d'erreurs de recopie.
Donc, tu peux ignorer ces lignes vides.
A toutes fins utiles j'ai modifié le fichier en y ajoutant une ligne entre 2 jours. Dans la première ligne j'ai relié certaines cellules pour montrer ce que cela donne. Le voici.

A+ Aloha

 

[CISCO]

Bonjour

Bonjour,
FL80: là les choses commencent à se compliquer puisqu‘il ya une référence non pas à une cellule du mois précédent maisà une aire: FK$5:FK72
...
A+
Aloha

Bonjour
Et oui. INDEX(FK$5:FK72;EQUIV(9^9;FK$5:FK72;1)) va chercher la dernière date+heure de fin du travail dans la colonne FK, au dessus de la ligne en cours (80).

@ plus

Tout compte fait, ce n'est pas gênant car on peut mettre l'information à conserver dans la cellule FK verte.

Mais il faut modifier la première formule dans la colonne FL pour que la première ligne de chaque mois (sauf janvier) aille chercher l'information dans la première ou la seconde ligne verte de cette colonne FK. Cf. les formules en rouge dans FK67 et FL73. Il faut faire de même pour chaque début de mois.
Il faut aussi modifier les autre formules de la colonne FL, car on ne va plus systématiquement aller chercher une information à partir de la ligne 5, mais à partir de la première ligne du mois considéré. Cf. les cellules avec des formules bleues. En janvier, c'est à partir de la ligne 5, ce qu'on obtient avec un $5. En février, c'est à partir de la ligne 73, donc on met $73 à la place de $5. Et ainsi de suite. Il faut faire de même pour les autres mois.


@ plus

P.S : Le fait de mettre deux lignes par date complique les choses, car, dans l'état actuel, on ne peut plus tirer (=copier-coller) les formules vers le bas. Le mieux serait de ne pas introduire ces lignes blanches supplémentaires, et ensuite seulement, une fois tout fini, les insérer (les formules sont modifiées automatiquement). Autre solution, je peux très bien modifier les formules pour avoir automatiquement une ligne sur deux blanches, tout en allant rechercher les informations au bon endroit. Toutefois, il faut choisir assez vite, et surtout ne pas utiliser les formules destinées à un modèle de feuille dans l'autre modèle.

 

[Aloha]

Bonjour,

En janvier, c'est à partir de la ligne 5, ce qu'on obtient avec un $5. En février, c'est à partir de la ligne 73, donc on met $73 à la place de $5. Et ainsi de suite. Il faut faire de même pour les autres mois.

Non: le mois de février commence aussi à la ligne 5!

Les feuilles mensuelles sont identiques et c'est donc toujours à la ligne 5 que sont saisis les symboles pour le 1er de chaque mois.

Je précise davantage le fonctionnement du tout:

Au début de l'année il y a 2 fichiers:

1. le premier, contenant tout le code VBA,

a) une feuille pour chaque service avec tous les salariès (Nom et prénom, date de naissance, date d'engagemnt, dates de changements éventuels de la tâche hebdomadaire, la ou les tâches hebdomadaires, le congé restant de l'année précédente etc; puis il y a aussi

b) la base de données: colonne 1: mois, 2: Nom et prénom, 3: Service, 4: heures travaillées, 5; congé pris au cours du mois en question..... et puis les données nécessaires pour les calculs du mois prochain (cellules bleues dans mon fichier exemple en-bas du mois)

2. le deuxième, qui a l'architecture de mon fichier exemple (mais donc seulement 1 mois) dans lequel sont insérées par formules, pour chaque salarié du service en question, les données personnelles nécessaires de 1.a) et celles de 1.b). Puis le code copie toutes les parties formules récupérant des données depuis 1.a) et 1.b) (bien évidemment pas celles calculant à l'intérieur du mois en question, comme p.ex. celle qui compte et additionne les heures travaillées) et les remplace par les valeurs (pour figer les chiffres, les fichiers étant envoyés aux différents services), le fichier est enregistré sous le nom du service et du mois et il est expédié au service.
Même procédure pour tous les services: le fichier est ouvert à nouveau, les fiches des salariés sont produites etc.

Ensuite, fin janvier, les services renvoient leurs fichiers remplis et les données sont copiées dans la base de données; les fichiers de janvier remplis sont ensuite classés. Fin février les données de février sont ajoutées à la base de données etc. etc.

Ce qui signifie pour la tâche actuelle: je dois récupérer du fichier du mois actuel toutes les valeurs nécessaires pour pouvoir continuer mes calculs le mois prochain. P.ex., lorsque au mois de janvier j'ai 2 cycles complets de 7 jours sans les 44 heures, et que pour le 3ème j'ai déjà compté 2 jours, le calcul pour le mois de février doit donc continuer là: si je travaille le 1er et le 2ème février le premier jour est donc le 3e jour du 3e cycle commencé, le 2.2. le 4e jour.

En ce qui concerne les 2 lignes par jour: tu fais comme tu préfères; il n'y a aucun problème, mais cela demande just un peu de temps et de copier-coller, pour transférer les formules d'un système à 1 ligne dans un système à 2 lignes.

Donc, une donnée fixe, à laquelle je ne peux rien changer: je dois intégrer les formules dans un modèle à 2 lignes.

Bonne journée
Aloha

 

[CISCO]

Bonjour

Bonjour,

En janvier, c'est à partir de la ligne 5, ce qu'on obtient avec un $5. En février, c'est à partir de la ligne 73, donc on met $73 à la place de $5. Et ainsi de suite. Il faut faire de même pour les autres mois.

Non: le mois de février commence aussi à la ligne 5!
...
Bonne journée
Aloha

Je suis bien d'accord, dans l'ancienne version, le mois de février utilise des données à partir de la ligne 5, dans la colonne FL, sous la forme, ligne 73 par ex,

FJ73-INDEX(FK5:FK65;EQUIV(9^9;FK5:FK65;1))

Mais, si c'est gênant (L'est-ce vraiment ?), on peut contourner ce problème en mettant dans une cellule verte, FK67 ou FK69, l'information INDEX(FK5:FK65;EQUIV(9^9;FK5:FK65;1)). Autrement dit, on mémorise dans une cellule verte la dernière date+heure de travail. Et dans FL73, utiliser la relation FJ73- FK67 ou FK69. Après, dans FL74, il faut utiliser une formule utilisant une plage commençant en ligne 73, sous la forme =SI(FJ74="";"";FJ74-INDEX(FK$73:FK73;EQUIV(9^9;FK$73:FK73;1))) au lieu de =SI(FJ74="";"";FJ74-INDEX(FK$5:FK73;EQUIV(9^9;FK$5:FK73;1))). Regarde dans mon dernier fichier, les formules rouges et celles en bleu. Cela devrait fonctionner.

Une question : Pourquoi as-tu besoin de deux lignes avec des cellules vertes entre chaque mois. Une seule ne suffit pas ?

@ plus

P.S : Mais il faut aussi changer la formule dans la colonne FO car elle fait appel au 6 dernières lignes à cause de la partie DECALER(...;-6;...). En début de mois, cela peut poser problème (pas pour janvier dans l'exemple traité, mais dans les suivants).

 

[Aloha]

Bonjour,
Pour répondre à ta question: dans la 2e ligne verte (dans le fichier réel ces cellules se trouveront autre part; je les mises à l'endroit actuel pour faire comprendre le fonctionnement) je collecte, pour le mois prochain, les données requises du mois précédent pour exécuter les calculs du mois prochain.

Pour mieux faire comprendre, voir le fichier joint pour février.

Pour expliquer autrement: les cellules vertes servent à collecter les données de janvier (en haut) et à stocker celles de février nécessaires pour continuer correctement en mars (en bas).
Elles servent donc pour remplacer le lien entre le 28.2. et le 1.3. cassé par le fait que les mois sont enregistrés dans des fichiers différents.
A+
Aloha

 

[CISCO]

Rebonjour

Est-ce qu'il y a les mêmes informations dans les deux lignes vertes, après export et réimport ?

@ plus

 

[Aloha]

Re,
non. dans celles en haut il y a les valeurs requises du mois précédent
dans celle en bas il y a les informations de la fin du mois actuel requises pour les calculs du mois prochain. Les lignes vertes doivent remplacer le lien cassé avec les jours avant le 1.2. resp. avec ceux après le 28.2..

J'ai d'ailleurs commencé à confectionner un exemple le plus simple possible pour mieux faire comprendre.

Si j'y arrive je le posterai lorsqu'il sera prêt.
A+

 

[Aloha]

Re,
Voici 2 fichiers simulant les fichiers réels: Fiches mensuelles et BD ainsi qu'un fichier contenant tous les mois comme mon fichier exemple initial.

Ma tâche fictive est de calculer pour chaque jour la moyenne des derniers 7 jours.
A partir du 7 du mois tous les chiffres requis sont situés dans le mois actuel, mais pour calculer la moyenne jusqu'au 6 il faut les chiffres du 6ème au 1er jour avant le 1er jour du mois actuel.

Voilà pourquoi je stocke à la fin du mois les chiffres des 6 derniers jours dans la BD et le mois prochain je les récupère.
Car, pour la moyenne du 1.2. moins 6 jours je dois stocker les chiffres du jour -6 au jour -1 (31.1.)

Où l'exemple boîte légèrement: dans cet exemple simple je n'aurais pas besoin des cellules vertes en bas parce que les chiffres sont directement disponibles dans la colonne, alors que dans le fichier réel il s'agit de références et même d'aires et non pas de simples chiffres.

Dans le fichier annuel je n'ai pas besoin de cette opération avec la BD puisque tous les chiffres sont directement disponibles.

J'espère me faire comprendre.
A+
Aloha