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

 

[CISCO]

Bonsoir

1) Sur le fichier du post 24, il faut remplacer dans la colonne AG le (4+6/24)<JOURSEM(... par (4+6/24)<=JOURSEM(....
En faisant cela, on obtient un 1 dans AG1593 le 7/6/05, ce qui donne un nombre de W.E convenant de 6.

2) Théoriquement, on doit trouver 9 pour DUPONT Marie, mais ce n'est pas le cas, car pour le moment, le fichier s'arrête ligne 3000 le 04/08/05 colonne AC, alors que la colonne A va jusqu'au 16/10/05. J'ai prolongé le tableau jusqu'au 30/10/05 dans le fichier en pièce jointe, et cela donne bien 9.

3) Dans les colonnes AH, AL, AP, il y avait encore une ancienne version ne prenant pas en compte automatiquement les dates jusqu'en bas de la colonne A. Il ne faut pas faire dans ces formules avec A4:A155 mais avec le nom Date. J'ai corrigé dans le fichier en pièce jointe jusqu'à US4.

4) J'ai changé le fichier du post 26 traitant le problème 2 car il fallait un maxintéressant>43 au lieu d'un maxintéressant>44

@ plus

 

[CISCO]

Bonsoir

Mais là il faudrait saisir chaque formule!?

Il faut faire avec des boucles balayant tout le tableau de gauche, sans les formules des tableaux de droite.

@ plus

 

[CISCO]

Bonsoir

Avec la première méthode, il faut environ 365*24*130*4, soit plus de 4 millions de cellules !
Avec la seconde (posts 23 et 26), il faut 2 * (24*365+130*365), soit un peu plus de 112 000 cellules. C'est mieux.

@ plus

 

[Aloha]

Bonsoir

Avec la première méthode, il faut environ 365*24*130*4, soit plus de 4 millions de cellules !
Avec la seconde (posts 23 et 26), il faut 2 * (24*365+130*365), soit un peu plus de 112 000 cellules. C'est mieux.

@ plus

En effet!
Il faut absolument limiter les formules à un minimum et, si possible, les saisir/remplacer par leurs valeurs moyennant VBA, puisqu'il y a déjà énormément de formules dans le fichier en question (il y a p.ex. une feuille avec quelque 1500 formules avec des références compliquées vers d'autres feuilles, donc plus gourmandes en ressources (?), et ce n'est qu'un exemple.

Ces calculs se feront dans des feuilles supplémentaires. En ce qui concerne la méthode, je me demande s'il ne serait pas mieux que je répartisse le tout sur 3 feuilles: une feuille où j'importe toutes les saisies de tous les jours et de tous les salariés pour les 24 heures, une feuille où le problème 1 est traité, et une troisième pour le problème 2. Qu'en penses-tu?
Ceci implique que les formules doivent recueillir les données dans une autre feuille que celle où elles résident.

 

[CISCO]

Bonsoir

Oui, mais j'ai oublié un petit détail (Il y a 130 personnes, donc le tableau intermédiaire est 130 fois plus grand)

Avec la seconde (posts 23 et 26), il faut 2 * (24*365+130*365)

En réalité, c'est :
Avec la seconde méthode, il faut 2 * ((24+1)*365*130), soit un peu plus de 2 372 000 cellules. C'est mieux que la première méthode... mais ce n'est pas super.

Je crois que du VBA est indispensable... Il va falloir que je révise...

@ plus
24 * 365 par personne pour le tableau intermédiaire
1 * 365 par personne pour la colonne supplémentaire
((24+1)*365*130) pour le tableau intermédiaire et la colonne supplémentaires pour les 130 personnes pour un seul problème
2 * ((24+1)*365*130) pour les deux problèmes

 

[Aloha]

Bonsoir,

Je pense aussi qu'une solution par VBA s'impose.
J'avais écrit que j'ai un début de solution, mais il faut encore y travailler et l'auteur du code n'est pas/plus accessible. Si tu veux je peux poster le code.
A+
Aloha

 

[CISCO]

Bonjour

Une possibilité avec macro en pièce jointe.

Colonne AH : Une formule à tirer vers le bas (Actuellement, il peut y avoir jusqu'à environ 150 noms).
Les colonnes AI et AJ sont remplies automatiquement par la macro en fonction du contenu de la colonne AH, macro à lancer avec le smiley "Macro repos".

Il faut bien sûr tester tout cela.
Je ne suis pas certain d'avoir prévu tous les cas particuliers et d'avoir bien traité les cas limites (repos de 44 h finissant le mardi matin à 6 h, repos de 44 h suivant un autre repos juste à la limite des 7 jours, repos à la fin de l'année...).
Je n'ai pas fait de vérification par rapport au second problème (La macro donne un résultat, mais je n'ai pas fait d'exemple particulier pour voir si ce résultat est correct).
Je regarderai tout cela plus tard.

@ plus

P.S : D'ailleurs, comment est-ce qu'on fait pour le second problème, pour les 7 jours suivants la dernière période de repos de 44 h ? Si par ex, tu as une période de 44 h finissant le 25/12 à 12 h, puis plus de période de repos de 44 h ou plus après. On n'a pas 7 jours d'ici au 31/12 à minuit. Donc, est-ce qu'on tient compte de cette période ou pas ?

 

[Aloha]

D'ailleurs, comment est-ce qu'on fait pour le second problème, pour les 7 jours suivants la dernière période de repos de 44 h ? Si par ex, tu as une période de 44 h finissant le 25/12 à 12 h, puis plus de période de repos de 44 h ou plus après. On n'a pas 7 jours d'ici au 31/12 à minuit. Donc, est-ce qu'on tient compte de cette période ou pas ?

Bonjour et merci!

Je vois que tu n'es pas seulement fort en formules mais aussi fort en VBA! J'en suis bien jaloux.
Le début et la fin de l'année sont vraiment un problème.

Pour ce qui est du premier problème: la règle dit qu'il faut 20 coïncidences dans l'année, donc du 1.1. au 31.12., des dates bien définies.

Pour ce qui est du deuxième problème, il faudrait peut-être garder des données de l'année précédente; la dernière semaine devrait suffir. Lorsque la base de données est vidée à la fin de l'année, il suffit de trier le tout sur la date et d'effacer seulement jusqu'à une certaine date.
Pour la fin de l'année je ne vois que la possibilité d'importer les données de janvier non seulement dans le nouveau fichier, mais aussi dans le fichier de l'année précédente. On pourrait demander aux services de renvoyer vers les 10 janvier les fichiers provisoires avec les saisies remplis jusqu'au 7 janvier seulement par exemple, pour ne pas avoir à attendre le mois de février pour finir le tout. Là encore on peut trier le tout sur la date et éliminer ce qui n'est pas nécessaire.

 

[CISCO]

Bonsoir

Si on prend des jours en plus, avant et après l'année, il faudra changer deux ou trois choses dans la macro. Ce n'est pas très difficile à faire.
Pour le moment, elle tourne sur 365 ou 366 jours. On peut même faire une macro travaillant avec un nombre quelconque de jours. Mais est-ce intéressant ?

@ plus

 

[Aloha]

Bonsoir,

Pour pouvoir vraiment faire correctement les calculs par entités de 7 jours on ne peut pas se limiter à l'année proprement dite, voilà pourquoi il serait utile d'intégrer un minimum de jours de l'année précédente et de l'année suivante. Les données de l'année précédente sont de toute façon disponibles; pour celles de l'année suivant c'est un peu plus compliqué.

Bonne soirée
Aloha

 

[CISCO]

Bonjour

Pour le moment, la macro :
* fait une boucle sur chaque nom
* dans cette boucle, lit tout le tableau sur la feuille (Environ 130 * 365 * 24 cellules)
* ne garde que les cellules correspondant à la personne en cours dans la boucle (série nommée tableau dans la macro)
* dans cette série, transforme les cellules vides en 1, les autres en 0
On obtient donc une série du style 0000000011111110000001111111000000011..... (série nommée dans la macro tableauUn)
* Pour résoudre le 1er problème, ne garde que les 1 correspondant aux "W.E." (tableauWE) ce qui donne une nouvelle série du style 0000000000000000000001111111000000011....
* à partir de tableauWE, somme les uns sous la forme 0000000000000000000000000007000000000.... (tableauWEfinal)
* Pour résoudre le 2nd problème, à partir de tableauUn, somme les uns sous la forme 0000000012345670000001234567000000012.... (tableauUnfinal)
Après il suffit ;
* pour résoudre le problème 1, de faire un test sur tableauWEfinal et de compter le nombre de valeurs >43,
* et pour le second problème, dans tableauUnfinal,de calculer le nombre d'heures entre chaque valeur successive >43, et de compter le nombre de cas où ces écarts sont > 7 *24
Tous les tableaux ci-dessus sont des tableaux à une dimension.
La macro passe ensuite au nom suivant.

1) Telle que, la macro lit tout le tableau (Environ 130 * 365 * 24 cellules) sur la feuille pour chaque nom, donc, dans ton fichier réel final, environ 130 fois (dans le fichier test, que 2 fois !)... Cela n'est pas la meilleure méthode et cela risque d'être long en pratique.
Le plus simple, pour n'avoir à lire qu'une seule fois le tableau sur la feuille, serait de faire un tableau à deux dimensions. Ce n'est pas un problème mais il faut un peu réorganiser la macro en conséquence.

2) Dans ton fichier réel, comment procédez vous :
* Est-ce que vous avez 130 * 12 fichiers mensuels, un par mois par personne
* ou est-ce que vous avez 130 fichiers annuels,
tous dans le même dossier, ou dans plusieurs dossiers
ou un seul fichier, avec toutes les données dans le même fichier,
* avec une feuille par personne par mois
* ou une feuille par personne pour l'année
*...
Est-ce que vous créez le tableau de la feuille SAISIES à la main (avec des copier-coller), ou est-ce que c'est fait par une macro ?
Tout cela pour te dire, qu'en fonction de l'origine de toutes ces données, on peut très bien demander à la macro d'aller chercher ces données directement dans les fichiers (ouverts ou pas), ou sur les feuilles source... Cela serait qu'en même plus simple...
Autrement dit, si tu me précises votre méthode de travail, je peux modifier la macro pour que cela soit fait automatiquement un peu plus vite....

@ plus

 

[Aloha]

Dans ton fichier réel, comment procédez vous :
* Est-ce que vous avez 130 * 12 fichiers mensuels, un par mois par personne
* ou est-ce que vous avez 130 fichiers annuels,
tous dans le même dossier, ou dans plusieurs dossiers
ou un seul fichier, avec toutes les données dans le même fichier,
* avec une feuille par personne par mois
* ou une feuille par personne pour l'année

Il y a un fichier mensuel par service (au nombre de 11 si je ne me trompe) et il contient une feuille pour chaque personne travaillant dans le service (cf annexe; sur l'une des deux feuilles, contenant des données réelles telles que saisies par les salariées, on voit à droite une multitude de formules "digérant" les données saisies).

Est-ce que vous créez le tableau de la feuille SAISIES à la main (avec des copier-coller), ou est-ce que c'est fait par une macro ?

Je suis en train de me casser les dents sur une macro qui doit faire les copies. A cet effet, pour résoudre l'une des difficultés, j'ai créé un autre thème: "VBA: Trier les cellules".
En effet (cf fichier en annexe), pour gagner des lignes pour tous les calculs et tous les résultats, je n'avais pas assez avec 31 lignes pour les 31 jours et j'ai eu l'idée (en 2005) de doubler le nombre de lignes à disposition (et il en manque encore!) en reliant 2 cellules entre elles verticalement dans le tableau des saisies.

J'ai depuis le début une macro qui ouvre mensuellement les fichiers mensuels renvoyés remplis par les services et qui parcourt toutes les fiches et copie les résultats dans une banque de données, p.ex. le nombre d'heures travaillées, de congé, de maladie, supplémentaires etc et je veux en profiter pour copier alors toutes les saisies.

Une autre macro prépare mensuellement les nouveaux fiches vides pour le prochain mois; ces fiches reprennent toutes les valeurs pour la personne depuis le début de l'année (heures travaillées depuis le 1er janvier, heures restant à travailler, congé pris et restant etc, ce qui explique pourquoi il me faut autant de lignes.

Ma nouvelle macro doit prendre les dates en A, puis les saisies en F:AC, les copier dans une feuille nouvellement créée dans le dichier principal ("Base", contenant déjà une multitude de feuilles) et que j'ai nommée "Saisies" (fichier exemple en annexe contenant uniquement la feuille "Saisies"), puis trier les données fraîchement copiées (en copiant les données les cellules liées sont déliées de sorte qu'entre deux jours j'ai toujours une ligne vide), après le nom est pris en U1 et copié aussi, et en fin de compte encore le nom du service.

La fiche "Saisies" ci-jointe montre un essai échoué, échoué parce que j'ai copié les données de 3 personnes et que je les ai triées sur la seule date, de sorte que j'ai chaque date 3 fois.
J'aurais dû trier chaque bloc (personne) avant de copier les données de la prochaine personne, puis insérer nom et service.

Bien compliqué tout ça, et cela devient plus compliqué encore après chaque négociation couronnée de succès entre patrons et représentants du personnel.

En réponse à ton dernier message: génial ce système pratiquement binaire! Chapeau!

Bien à toi
Aloha

 

[CISCO]

Bonjour

J'ai repéré 2 petites erreurs dans le fichier Aloha Saisies VBA4. Je viens de les corriger, et de changer ce fichier dans le post 37.

@ plus

 

[CISCO]

Bonjour

J'ai fait quelques tests avec un tableau plus grand (sur la feuille, 300 lignes * 24 colonnes), et cela rame beaucoup, aussi bien avec, dans la macro, des tableaux à une dimension qu'avec des tableaux à deux dimensions.
Ton tableau réel a au moins 365 lignes * 130 personnes sur 24 colonnes. La macro pour le moment utiliserait dans ce cas des tableaux de 365 *24 lignes par 130 colonnes....
Conclusions :
* Soit les boucles que j'ai programmée ne sont pas les meilleures possibles
* Soit il va falloir passer par un autre outil VBA, les dictionnaires par exemple...

Je fouille pour trouver mieux

 

[Aloha]

Bonjour,

Merci encore pour tout ce que tu fais pour trouver une solution!
"Cela rame beaucoup" cela signifie quoi exprimé en unité de temps?