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]

Bonsoir,
J'ai recherché les liaisons externes povoquant à chaque ouverture d'un fichier mensuel le message concernant la mise à jour.
J'ai trouvé un seul lien, mais sans "=", donc inactif:
"INDIRECT("[XXXXX.xls]"&BA1&"!"&"$D$3:v285")"
Et je ne comprends pas pourquoi Excel veut mettre à jour un lien inactif.
Un mystère de plus!
A+
Aloha

 

[Aloha]

Bonjour,
Ce que j'ai écrit dans mon dernier message n'est pas exact: j'avais oublié la feuille masquée "Résumé" où il y a des références actives vers d'autres fichiers.

J'ai eu un DVD avec tous les fichiers 2020, seulement, il est illisible.
A+
Aloha

 

[Aloha]

Bonjour,

Je viens de recevoir un autre DVD avec tous les fichiers de janvier 2020 à mars 2021.
Il est lisible, mais copier les fichiers était malgré tout problématique.
J'ai commencé à importer les fichiers.
La macro importe les fiches de janvier de plusieurs services puis elle se bloque au 31.1. auprès d'une personne déterminée du service X; un deuxième essai: même résultat.

J'enlève ce fichier mensuel du service X et j'importe les autres.
Nouveau blocage, au même endroit dans le code, avec le même message d'erreur dans le fichier Y
Mon analyse révèle qu'en plus des fiches "normales" il y a une fiche vide "Sheet1" et il est évident que le code ne peut pas agir dans une fiche vide. Je ferme le fichier mensuel sans l'enregistrer et je l'ouvre à nouveu: il n'y a pas de fiche "Sheet1", donc elle doit être produite au cours de l'opération, mais comment?
Je reprends le fichier original X que j'avais enlevé: il y a 13 fiches cachées Sheet1 à Sheet13. Dieu sait d'oû elles proviennent.
La situation entre les deux fichiers diffère donc: lorsque je reçois le fichier X il y a déjà les fiches Sheet1 à Sheet12, tandis que la fiche Sheet1 n'existe pas encore dans le fichier Y lorsque je le reçois.

Une solution serait de ne pas exclure que les trois fiches n'apparetenant pas à une salariée (Code, Résumé, Tableau), mais aussi toutes les fiches éventuellement présentes dont le nom commence par "Sheet" ou par "Feuil".

Une autre solution, plus compliquée, que j'entrevois:
Lorsque les fichiers sont préparés avant d'être envoyés aux services la macro se base sur la liste des salariés dans le service concerné pour lui établir une fiche. On pourrait se baser sur la même liste pour définir quelles fiches doivent être importées, ce qui exclurait toutes les fiches dont le nom ne correspond pas à un nom dans la liste des salariés du service.

A bientôt
Aloha

 

[Aloha]

Re,
Je ne vois plus clair dans tout cela.
Des fois le fichier X passe, d'autres fois non, et le code bloque des fois à un nom, et non seulement dans un "Sheet..".
J'ai ajouté dans la macro:

Fichierouvert.Sheets("Sheet*").Delete
Fichierouvert.Sheets("Feuil*").Delete

et j'ajoute la Base modifiée.
Cela semble marcher mais maintenant le code bloque à la 7ème fiche (AYMAIN Leonor) du fichier anonymisé ci-joint.
A+
Aloha

 

[CISCO]

Bonjour

Je regarderai cela ce soir, si je peux...

@ plus

 

[Aloha]

Re,
OK
J'ai exécuté le code avec F8 et il n'arrive déjà pas à sélectionner les colonnes A à D de la fiche en question, sans que je comprenne pourquoi.
En effet, avec F8 il bloque déjà ici:

    .Range("A:D").Select

A+
Aloha

 

[CISCO]

Bonsoir

Effectivement, cela active puis bloque sur la feuille AYMAIN Léonor. Mais en réalité, la macro essaye de travailler sur Sheet1 qui n'existe théoriquement plus !!!

@ plus

 

[CISCO]

Bonsoir

Cela ne fonctionne pas avec
Fichierouvert.Sheets("Sheet*").Delete
ni avec
Fichierouvert.Sheets("Feuil*").Delete



Tu peux supprimer les deux lignes
Fichierouvert.Sheets("Sheet*").Delete
Fichierouvert.Sheets("Feuil*").Delete

et rajouter à la place
For Each Wsh In Fichierouvert.Worksheets
If Wsh.Name Like "Sheet*" Then Wsh.Delete
Next Wsh

Je ne dis pas que cela suffira, mais essaye déjà avec ça.

@ plus

 

[Aloha]

Re,
J'ai remplacé les 2 lignes exactement au même endroit. Est-ce correct?

Dans ce fichier j'ai ajouté la feuille contenant la fiche du service FM avec les noms qui correspondent aux fiches de ce service, et à partir de cette fiche on pourrait déterminer quelles sont les fiches (donc celles qui correspondent à un nom dans la fiche FM) qu'il faut importer. J'ai juste gardé les noms et effacé tout le reste qui est sans importance pour la tâche; toutes les fiches des services ont la même structure, c.-à-d. que les noms se trouvent toujours dans la même plage de cellules qui s'étendent plus ou moins vers la droite suivant le nombre de salariés.

Même si la macro travaille sur Sheet1, elle devrait pouvoir sélectionner les colonnes A à D!
A+
Aloha

 

[Aloha]

Bonjour,

La macro tourne après ta dernière modification!
Je viens d'importer les fichiers réels de janvier et février.
Pour février j'ai mis le chronomètre: 30" pour importer et 6" pour calculer.

J'ai importé le reste de l'année 2020 et mis le chronomètre pour chaque mois:

Mars: 46"
Avril: 51"
Mai: 57"
Juin: 1' 17"
Juillet: 1' 14"
Aout: 1' 24"
Septembre: 1' 32"
Octobre: 1' 41"
Novembre 1' 51"
Décembre: 1' 57.

Il met 30' pour impoter tous les fichiers d'un mois, le reste est du temps de calcul.

Donc tout à fait acceptable, pour 46.084 lignes!

Ce que j'ai remarqué: j'ai analysé les données d'une salariée qui n'avait que 6 WE de libre et je me suis rendu compte qu'elle a seulement été engagée au mois d'octobre.
Donc, pour être tout à fait correct, et pour éviter de devoir calculer manuellement le pro rata des 20 WE suivant la durée de l'engagement dans l'année, il faudrait prendre en considération la date d'engagement et de fin de contrat (vide si le contrat continue au-delà du 31.12.) de la personne.

Je vois 2 possibilités:
* la macro utilise la fiche des services dans Base (que j'ai ajoutée dans la dernière version postée ci-dessous) et j'importe juste les fiches du fichier mensuel auxquelles correspond un nom dans la fiche du service dans Base et elle y prend aussi la date d'engagement
* j'essaye d'intégrer la date d'engagement et de fin de contrat dans les fichiers mensuels
* par contre, j'y pense, on peut faire ce calcul seulement à la fin de l'année, puisqu'on connaît d'avance la date d'engagement, mais pas celle de la fin du contrat, ce qui exclut déjà la deuxième solution
Nouveau fichier Base avec la fiche FM contenant les noms et les dates d'engagement et de fin du contrat

"Petit" problème: la Base contenant toutes les fiches de l'année a été gonflée de 128 Kb dans la version vide à la taille impressionnante de 16 654 Kb avec la conséquence qu'elle met une éternité (1' 25") à s'ouvrir. Il faut donc encore une autre solution, p.ex. exporter dans un autre fichier à créer à cet effet et ne contenant que les calculs des 44h.
Je ne comprends pas que de simples données, avec peu de formules, occupent autant de place.

Autre idée: remplacer les formules dans Saisies AE:AF par leurs valeurs permettrait d'effacer toutes les données des tableaux.
Je pense que ça c'est la solution! J'ai fait l'exercice suivant:
J'ai copié la feuille Saisies dans un nouveau classeur. Il fait toujours 16.161 Kb.
Puis j'ai remplacé les formules en AE.AF par leurs valeurs, j'ai effacé les données des tableaux, j'ai enregistré le fichier et il fait une taille acceptable de 3.450 Kb, ce qui devrait permettre de garder les calculs des 44h dans le fichier principal; pour vérifier cela j'ai pris le fichier Base avec les données importées, j'ai remplacé les formules en AE:AF par leurs valeurs, effacé les données des tableaux, et enregistré le fichier: il fait 3.900 Kb et son ouverture est quasi-instantanée.
A+
Aloha

 

[CISCO]

Bonjour

1) Effectivement, ça doit être faisable de remplacer les formules des colonnes AE et AF par leur valeur. Je l'ai déjà fait dans une autre macro n'affichant pas les tableaux de droite. J'essaye de te faire ça ce soir.

2) Pour ce qui est des personnes n'ayant pas travaillé toute l'année, il faut que j'y réfléchisse davantage...

@ plus

P.S : Comme tu t'en doutes, en plus d'un mois, j'ai un peu oublié les tenants et les aboutissants de ce fichier, ainsi que le fonctionnement de ces macros. Il me faut donc un peu de temps pour comprendre et améliorer tout cela.

 

[Aloha]

P.S : Comme tu t'en doutes, en plus d'un mois, j'ai un peu oublié les tenants et les aboutissants de ce fichier, ainsi que le fonctionnement de ces macros. Il me faut donc un peu de temps pour comprendre et améliorer tout cela.

Re,
Pas de problème.
Je vais voir si je peux faire quelque chose moi-même avec mes maigres connaissances
A+
Aloha

 

[CISCO]

Bonjour

Il pleut donc...

Pour mettre les valeurs dans les colonnes AE et AF, dans Sub Calculs(), juste en dessous de

Range("AE4:AE" & (3 + nbre_personnes)).FormulaR1C1 = "= SUMPRODUCT((Nometprénomtrié = RC[-2])*(TableauWE=44))"
'modifié le 9.4.2021:
'Range("AF4:AF" & (3 + nbre_personnes)).FormulaR1C1 = "= SUMPRODUCT((Nometprénomtrié = RC[-3])*(Tableaureposncp=7*24))"
Range("AF4:AF" & (3 + nbre_personnes)).FormulaR1C1 = "= SUMPRODUCT((Nometprénomtrié = RC[-3])*(Tableaurepos < 44)*(Tableaureposncp=7*24))"

rajoute

Range("AE4:AF" & (3 + nbre_personnes)).Copy
Range("AE4").PasteSpecial xlPasteValues

Dis moi, STP, ce que cela donne au niveau koctets.

@ plus

 

[Aloha]

Re,

Dis moi, STP, ce que cela donne au niveau koctets.

Pour le savoir je dois réimporter tous les fichiers. J'ai importé le mois de janvier (30').
Les formules sont bien remplacées par les valeurs; il faudrait juste encore vider les tableaux.

A+
Aloha

 

[CISCO]

Bonsoir

On peut effectivement enlever les tableaux de droite mais :
1) Dedans, il ni a que des valeurs. Est-ce qu'en les supprimant cela va vraiment beaucoup alléger le fichier ?
2) On ne pourra plus vérifier les cas litigieux. Il faudrait au moins garder une version avec les tableaux de droite, histoire de la faire tourner lorsqu'il y a un problème.
3) J'ai une version (cf. post 201) sans les tableaux de droite, et sans les formules dans les colonne AE et AF (Les calculs correspondant à ces colonnes se font au fur et à mesure, sans des SOMMEPROD). Il faut que je la retrouve et que je la vérifie...

@ plus