Microsoft 365 Compter les WE libres

[Aloha]

Bonsoir,
Dans le document ci-attaché se trouvent les données (heures) en rapport avec le contrat de travail de DUPONT Marie et DURAND Germaine.
Dans la ligne en haut les 24 heures de la journée + nuit
Colonne A: Date
Colonne B: Jour semaine
Colonne C: Nom et prénom de l'agent
Colonne D: le service dans lequel l'agent travaille
Colonnes E à AB: les heures en rapport avec le contrat de travail

Signification des symboles rencontrés:
X = 1 heure de travail
C = 1 heure de congé
RS = 1 heure réunion de service comptée comme heure de travail
T = Tutorat
HS = heure supplémentaire
...et il y en a d'autres

Double problème:

* le premier:

La règle: le repos ininterrompu hebdomadaire de 44 heures doit coïncider avec le week-end au moins 20 fois par an. Le week-end s’étend sur une période de deux jours consécutifs situés entre 6 heures le samedi matin et 6 heures le mardi matin.

Les périodes samedi 6h à mardi 6h sont en jaune.

Il s'agit donc de vérifier quand la personne avait un WE de libre suivant les règles ci-dessus
et de les mettre dans un compteur pour faire le bilan à la fin de l'année.

* le second:

Chaque salarié a droit à un repos ininterrompu de 44 heures par semaine. Dès la fin d’un repos hebdomadaire, le prochain repos hebdomadaire doit intervenir endéans les prochains sept jours. Les salariés qui n’ont pas pu jouir dudit repos auront droit à un jour de congé supplémentaire pour chaque période entière de 8 semaines, successives ou non, pendant laquelle le repos ininterrompu de 44 heures par semaine n’est pas accordé.

Il faut donc compter les périodes de 7 jours où la condition est remplie (donc pas 44 heures libres consécutives)

Je ne sais pas si ces poblèmes sont résolubles par seules formules ou s'il faut du VBA. La base de données retraçant chaque heure de chaque jour de l'année pour quelque 130 salariés (qui n'existe pas encore; les données sont disponibles dans des fichiers mensuels par service regroupant tous les salariés travaillant dans ce service; il faudra copier les données par VBA) sera assez importante: 130 * 365 = beaucoup de lignes

Quelqu'un parmi vous entrevoit-il/elle comment résoudre ce double problème?
Merci d'avance pour tout tuyau et toute aide!

Bien à vous
Aloha

 

[Aloha]

Bonjour,
La macro servant à transférer les données bloque à l'instruction

    dernièrelignsource = Workbooks(nomfichier).Worksheets(nomfeuil).Cells(Rows.Count, 1).End(xlUp).Row - 1

avec le message d'erreur qu'en annexe.

Il serait utile d'intégrer ta macro dans la macro qui ouvre tous les fichiers un par un et qui remplit la feuille Database.

13:23
J'ai trouvé la cause: les fiches sont protégées. J'y reviens un peu plus tard.
A+
Aloha

 

[CISCO]

Bonjour

1) Effectivement, les fichiers Service sont protégés. Je m'en suis rendu compte, car il me faut travailler directement dedans avec la méthode proposée au post 135, celle permettant de faire le transfert sans les lignes vides. Il suffit de déprotéger la feuille au début, puis de la reprotéger à la fin, avant de passer à la feuille suivante...
2) Depuis le début de ce fil, j'ai essayé de résoudre ton besoin dans diverses directions :
a) Uniquement avec des formules, en mettant toutes les données de chaque personne sur une seule ligne (ou une seule colonne). Défaut : temps de calcul
b) Une macro, en mettant toutes les données de chaque personne sur une seule ligne (ou une seule colonne). Défaut : Comme la macro n'est pas optimisée (vu mon niveau en VBA), elle n'est pas très rapide
c) Une macro transfert, une macro tri, en gardant un tableau (365 lignes x 24 colonnes par personne) disposé presque comme le tableau initial, et des formules (que l'on remplace ensuite par les valeurs obtenues) dans 3 tableaux intermédiaires placés à droite. Défaut : Avec 130 personnes, cela risque d'être long, là aussi

En fait, la meilleure solution est la proposition de Chris ou une macro bien faite qui ferait le tout.

Pour faire mieux que la solution b ou c, je pense qu'il est possible de faire avec :
* une macro transfert
* une macro tri (365 lignes x 24 colonnes par personne)
* une ou des macro qui donnent les valeurs des 3 tableaux de droite, sans passer par des formules dans les cellules...
C'est la solution c "optimisée". Cela devrait tourner moins vite que la proposition de Chris, mais plus vite que les solutions b ou c.

@ plus

 

[Aloha]

Re,

Je n'ai pas le temps pour l'instant de m'en occuper; je le ferai ce soir.

Seulement ceci pour le moment:
Du moment que ta macro est intégrée dans la macro existante le problème des feuilles protégées ne se pose plus: elles le sont déjà; pas besoin de les reprotéger: la macro existante les ferme sans enregistrer.

Ce matin j'étais étonné de retrouver dans un fichier original une macro Private Sub dans "ThisWorkbook" mais pas dans mon dernier fichier que j'ai fabriqué sur base d'un vrai fichier; je ne comprends pas pourquoi.
Je l'ai remarqué lorsque j'ai exécuté ta macro par F8 et qu'elle a dévié sur cette macro-là.

A ce soir
Aloha

 

[CISCO]

Rebonjour

Dans mon dernier fichier (post #135), la macro Transfer prend, dans le fichier Service mensuel, feuille par feuille, toutes les lignes jusqu'au dernier jour du mois, y compris les lignes vides (une sur deux).
Ensuite, avec un filtre utilisé dans le fichier destination, cette macro élimine ces lignes vides.
Chez moi, cela prend environ 40 s pour 7 personnes.

Avec une seconde méthode, en filtrant directement dans le fichier Service mensuel, j'obtiens le même résultat en... 3 s, toujours pour 7 personnes...

Cela complique la macro car le filtre ne semble pas fonctionner, ni sur les colonnes masquées (A, B et C), ni sur les cellules fusionnées (plage F5:AC65). Il me faut donc mettre des lignes de code en plus pour afficher ces colonnes et défusionner cette plage. Il ne faut donc pas sauver le fichier Service mensuel après ce transfert, lorsqu'on le ferme, car il a été modifié, si on veut garder sa version originale.
En plus, on ne peut pas tout transférer d'un coup car le fichier Service mensuel a des colonnes en trop (B, C, D et E) dans la plage allant de A à AC.

Le transfert est passé de 5 minutes (avec une boucle), à 40 s (avec un filtre dans le fichier destination), et maintenant à 3 s (avec un filtre sur chaque feuille du fichier Service mensuel). Comme tu as une dizaine de fichiers Service mensuel à traiter chaque mois, ce gain de temps est toujours bon a prendre...

J'essaye de vérifier tout cela ce soir. Je vais aussi faire calculer les valeurs des tableaux de droite par une macro. Avec ces tableaux intermédiaires, la macro ne devrait pas être trop compliquée à mettre au point (moins que la macro de la solution b de mon précédent post).

Ensuite, il faudra voir comment inclure cela dans ta macro, si celle-ci est moins rapide.

@ plus

 

[Aloha]

Cela complique la macro car le filtre ne semble pas fonctionner, ni sur les colonnes masquées (A, B et C), ni sur les cellules fusionnées (plage F5:AC65). Il me faut donc mettre des lignes de code en plus pour afficher ces colonnes et défusionner cette plage. En plus, on ne peut pas tout transférer d'un coup car le fichier Service mensuel a des colonnes en trop (B, C, D et E) dans la plage allant de A à AC.

J'avais réussi à défusionner et à enlever les lignes vides en les triant, le tout dans le fichier de destination.
Normalement la macro devrait se trouver dans le fichier "Base" que j'avais posté

J'ai une réunion maintenant à 17:00 et je m'en occuperai après.
A+
Aloha

 

[CISCO]

Rebonjour

Je regarderai ta macro dans le fichier Base ce soir, histoire de voir comment combiner tout cela.

@ plus

 

[Aloha]

Re,
J'ai vérifié: dans le fichier base que j'avais posté il y a bien la macro originale et à partir de Copier toutes les saisies jusque Next n celle que j'ai fabriquée pour importer les saisies.
Et dans ThisWorkbook du fichier Septembre... il y a aussi la Private Sub qui ne devrait pas nous intéresser ici si le fait n'était que lorsque j'ai exécuté ta macro avec F8 elle n'ait dévié dans cette Private Sub.

Une chose que je n'ai pas su réaliser d'une façon plus élégante que la destruction (provisoire puisque le fichier n'est pas enregistré à sa cloture), c'est empêcher que les 3 autres fiches (Tableau, Résumé, Codes) présentes dans chaque fichier mensuel à part les fiches individuelles, ne soient pas parcourues et copiées aussi.

Ce qui me rend confus à l'instant, c'est que cette nécessité s'imposait dans ma macro, étant donné que la macro originale qui remplit Database ne copiait bien pas ces 3 fiches. Je me demande si j'ai effacé l'instruction qui empêchait cela. Je vais analyser cela demain.
Par contre, avec la méthode de la destruction provisoire cela fonctionne ici.

En fait, la meilleure solution est la proposition de Chris ou une macro bien faite qui ferait le tout.

Je suis d'accord avec cette méthode, celle de Chris, -comment pourrais-je ne pas l'être- si elle fonctionne, mais je ne comprends pas son fonctionnement, ce qui n'est pas bien grave, du moment qu'elle donne des résultats exacts, mais seulement pour l'un des deux problèmes.

Il me semble qu'avec "ma" macro, qui peut sûrement être améliorée (e.a. et surtout mes définitions des aires à traiter dans lesquelles il y a de la confusion, me semble-t-il) une bonne partie du boulot est faite: la copie des données, le placement des formules et leur remplacement. Si, en plus (ce n'est pas sans importance pour utiliser un understatement), elles donnent des résultats fiables et corrects, le tour est joué, même si toute l'action prend un certain temps, p.e. celui de la pause de midi.

Ce qui me semble important pour gagner en vitesse, je me répète une fois de plus, c'est l'intégration dans la macro qui remplit Database, puisqu'elle parcourt exactement les mêmes fiches et que cela empêche de devoir les ouvrir et parcourir une deuxième fois.

A+
Aloha

 

[CISCO]

Bonsoir

En pièce jointe, une autre version, où presque tout est fait avec des macros :

1) Une macro pour transférer les données du fichier mensuel Service
2) Une macro pour trier les données précédentes une fois sur la feuille destination (tri par personne, date et service)
3) Une macro créant les 3 tableaux (Tableau WE, Tableau repos et Tableau repos ncp (celui-ci affiche des durées de fin de repos d'au moins 44 h au repos suivant d'au moins 44 h, celui-ci inclus)).

C'est très très long : Pour 7 personnes et 2 mois (plus de 400 lignes), cela prend ... une dizaine de secondes. Comme quoi, travailler avec des filtres et des arrays, cela vaut le cout.

Ne fais pas trop d'essais avec cette macro car je risque d'y apporter d'autres modifications.

Je vérifie cette macro, puis regarde comment faire un mixte avec ta macro à partir de ce soir.

@ plus

P.S1 : L'ancien tableau de droite affichait un 1, à la fin du premier repos, lorsqu'on ne trouvait pas un repos d'au moins 44 h dans les 7 jours suivants.
Le nouveau affiche la durée à partir de la fin de ce premier repos, comme cela on peut vérifier où commence la période de 7 jours après un repos d'au moins 44 h, si l'enchainement des heures est logique, si un arrêt de cet enchainement est logique... Et lorsqu'on a un 168 (=7*24), c'est qu'il ni y a pas eu de nouveau repos de 44 h depuis le dernier. La formule dans la colonne AE faisait un test par rapport aux 1 présents dans ce tableau. Maintenant, elle est installée par la macro et elle fait un test par rapport à ces 168.

P.S2 : Il faudra contrôler ce qui se passe en début et en fin d'année...

 

[Aloha]

Bonsoir,
J'ai testé avec un fichier. Les deux premières macros tournent comme il faut, la troisième bloque à l'instruction

joursem = Weekday(Tablotrié(lig, 1), 7) 'le 7 défini le samedi comme le 1er jour de la semaine

avec le message Erreur d'exécution 13 - Incompatibilité de type.
Ce qui m'étonne puisque joursem a bien une valeur!

Cependant, comme tu as écrit que je ne fasse pas trop d'essais, j'arrête de tester.
A+
Aloha

 

[CISCO]

Bonsoir

Chez moi, cela tourne sans problème.

Vu le message d'erreur, il semble que cela soit un problème de déclaration des variables. Et en effet, au début, dans les 2 macros dans le module 3, il faut remplacer dans les premières lignes tous les Single et tous les Double par Integer et pour heure, Variant par Single. C'est mieux au niveau utilisation de la place mémoire.

Je ne peux pas juger de la validité de ce "détail", puisque, mathématiquement parlant, cela ne suffit pas pour justifier ce message d'erreur, et puisque chez moi, cela fonctionne aussi bien avec Single, Double qu'Integer.

@ plus

 

[Aloha]

Bonjour,

J'ai trouvé la raison du blocage de la macro: ce n'est pas une mauvaise déclaration des variables, mais le fait que les feuilles Codes, Tableau et Résumé sont copiées aussi, et comme les valeurs que la macro de transfert prend dans ces feuilles pour les mettre dans la colonne A ne sont pas des dates, cela ne peut pas fonctionner.
J'ai regardé dans la macro existante et je ne vois pas du tout comment elle empêche ces 3 fiches d'être copiées.

Lorsque je supprime ces feuilles avant l'importation, les macros fonctionnent, même sans changer la déclaration de ces variables, et elles fonctionnent à la merveille, c.-à-d. avec une grande rapidité! Chapeau!

Lorsque la dernière macro a fini son travail, elle met un message: cf en annexe.

Pour vérifier les résultats des formules, c'est autre chose, c'est assez difficile, en tout cas pour vérifier si tous les 7 jours il y a le temps libre requis.
Vérifier le nombre de WE libres, c'est plus aisé; je l'ai contrôlé pour deux salariés et le résultat est exact! Cf. document attaché.

La macro ne transfert pas le nom du service correctement depuis la cellule AY1 des fiches individuelles mensuelles. J'ai remplacé le nom du service de 2 personnes, l'un par XXX et l'autre par YYY (les vrais noms abrégés comportent entre 2 et 4 lettres); la macro les a transformés en SXX et SYY.
Les noms du service se retrouvent d'ailleurs toujours dans le nom du fichier, qui dans ces deux exemples seraient "[Nom du mois] [Année] [Service, donc 2, 3 ou 4 lettres].xls", p.ex. Mai 2020 XXX.xls, ou Octobre 2020 YYY.xls

A+
Aloha

 

[CISCO]

Bonjour

1) J'ai commencé à regarder comment était construite "ta" macro. Avec des abréviations, en allemand pour certaines, ce n'est pas évident. En général, j'essaye de mettre des noms de variables ayant un rapport avec ce à quoi elles correspondent. Et vu que j'étais nul en allemand au lycée... Mais bon, je vais essayer de combiner les deux macros ensemble
2)

Lorsque la dernière macro a fini son travail, elle met un message: cf en annexe.

C'est le temps de calcul en seconde ! Dans la macro, au début, il y a une ligne t = timer et à la fin Msgbox timer - t. Il suffit de mettre une apostrophe ' devant pour que ces 2 lignes ne soient pas prises en compte (Elles sont alors écrites en vert). Dans la version définitive, il suffira de les supprimer. J'avais mis ces lignes pour voir le gain de temps obtenu en utilisant telle ou telle méthode...
3)

Pour vérifier les résultats des formules, c'est autre chose, c'est assez difficile, en tout cas pour vérifier si tous les 7 jours il y a le temps libre requis.
Vérifier le nombre de WE libres, c'est plus aisé; je l'ai contrôlé pour deux salariés et le résultat est exact! Cf. document attaché.

A+
Aloha

Oui, effectivement. Pour les WE, c'est assez facile. par contre, pour ce qui est des repos de 44 h forcément suivis dans les 7 jours d'un autre repos de 44 h, c'est beaucoup plus difficile.
C'est pour cela que j'ai changé le contenu du 3ème tableau, celui complètement à droite. Maintenant, elle affiche les nombres 1, 2, 3 dès qu'un repos de plus de 44 h fini, jusqu'à l'obtention d'un autre repos de 44 h, ou jusqu'à avoir 168. Dans ce dernier cas, cela signifie qu'il ni a pas eu de repos de 44 h dans les 7 jours considérés. Dans les quelques exemples que j'ai traités, certaines fois je me suis demandé pourquoi j'avais telle série à partir de tel endroit... Il faut comparer les deux tableaux, tableau repos et tableau repos ncp pour faire cette vérification.
Cela serait bien d'avoir des fichiers Service permettant de vérifier tous les cas litigieux (période en début d'année, en fin d'année, période de repos de 43 h ou de 44 h finissant en bout de ligne, ou sur le dernière ligne concernant la personne...) ou de comparer avec des cas déjà dépouillés à la main.

4)

La macro ne transfert pas le nom du service correctement depuis la cellule AY1 des fiches individuelles mensuelles. J'ai remplacé le nom du service de 2 personnes, l'un par XXX et l'autre par YYY (les vrais noms abrégés comportent entre 2 et 4 lettres); la macro les a transformés en SXX et SYY.
Les noms du service se retrouvent d'ailleurs toujours dans le nom du fichier, qui dans ces deux exemples seraient "[Nom du mois] [Année] [Service, donc 2, 3 ou 4 lettres].xls", p.ex. Mai 2020 XXX.xls, ou Octobre 2020 YYY.xls

Oui, effectivement, j'avais construit le nom du service avec "S" & une partie de l'information dans AW1 de la feuille concernée. Ce qui donnait forcément S 1, S 2..., S10, S11... Je mettrai directement tout le contenu de AW1 ou ferai avec la partie intéressante du nom du fichier.

@ plus

 

[Aloha]

Cela serait bien d'avoir des fichiers Service permettant de vérifier tous les cas litigieux (période en début d'année, en fin d'année,

Bonjour,

Je vais demander s'il y a eu des cas jusqu'ici où la règle des 44h endéans les 7 jours n'était pas respectée et qu'on m'envoie les fichiers correspondants. Normalement, si la personne qui établit le plan de travail fait attention, il ne devrait pas y avoir de tels cas, sauf cas de force majeure où p.ex. une personne doit remplacer un congé de maladie.

Je pense que la période de début d'année ne pose

Avec des abréviations en allemand pour certaines, ce n'est pas évident.

Si je ne me trompe il s'agit juste de noms de variables, non? Sinon, je peux traduire les noms allemands, ce n'est pas un problème.
Pfad = le chemin
SuchPfad = le chemin où il faut chercher
WbQuelle = le workbook source
WbZiel = le workbook de destination
Zelle = cellule

période de repos de 43 h ou de 44 h finissant en bout de ligne

Ca n'existe pas: personne n'arrête à travailler à minuit.

Je dirais qu'il y a de fortes chances que les résultats soient exacts.
Il faudra que j'envoie un fichier provisoire aux gestionnaires pour qu'ils testent avec leurs fichiers et voir ce qu'ils diront. Ils sont davantage à même de déceler les cas où les règles ne sont pas respectées que moi-même.

Un détail: il sera utile que près des deux colonnes affichant les résultats il y en ait aussi une avec le nom du service.

A+
Aloha

 

[CISCO]

Bonsoir

Est-ce que tu préfères dans la colonne AE le nombre X de repos ne convenant pas, ou ENT(X/8) (Même si, comme c'est rare, cela n'arrive pas souvent que X >7 ) ? Il y aura qu'en même 130 lignes dans cette colonne...

@ plus

 

[Aloha]

Re,
Je dirais qu'il serait intéressant d'avoir le nombre des repos ne convenant pas, pour être renseigné plus précisément et être averti à temps pour pouvoir empêcher que cela continue.

Il y aura qu'en même 130 lignes dans cette colonne...

Je ne comprends pas le pourquoi de ta remarque puisqu'il y a de toute façon autant de formules que de salariés dans cette colonne!

J'ai analysé une situation où ta formule affiche 1 période de 7 jours où la règle n'est pas respectée, et cela me semble exact.
Cette situation est d'autant plus intéressante, qu'il y a par hasard des C (congé) en jeu et j'ai posé la question à la directrice si les C sont traités comme les X (heures travaillées) ou non. Je dirais que oui, mais je dois me baser sur sa réponse.
A+
Aloha