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,

 

[CISCO]

Bonjour

Les résultats pour le salarié 3, dans AA17 et AB17, ne sont pas corrects. On devrait avoir 20 et 9. Les formules actuelles mettent l'heure de début et celle de fin sur la ligne comportant le début en question, même pour les périodes à cheval sur deux jours, ici sur la ligne 16. Mais comme il y a déjà quelque chose dans AA16 et AB16, il y a un problème.

@ plus

 

[Aloha]

Bonjour,

Entrevois-tu une solution pour ce nouveau problème que tu viens de soulever?
En ce qui concerne le précédent, j'ai corrigé les formules.
Je me suis demandé si elle calculera correctement le 31.12. puisqu'elle renvoie à la cellule de la prochaine ligne, qui est vide. Mais cela ne m'inquiète pas pour l'instant, on en est loin.
Dans cet ordre d'idées, il me semble que, pour terminer l'année correctement il faudra les saisies de début janvier 2019, non?

Bonne journée
Aloha

 

[CISCO]

Bonjour

Bonjour

Dans le dernier cas que tu signales, c'est plutôt le test initial qui est incomplet : ...

SI((AF75=AF76)+(AF76="");"";(ARRONDI(MAX(DECALER(AG75;-6;;7));10)<ARRONDI((44/24);10))*1)

@ plus

J'ai l'impression que ce n'est pas correct dans tous les cas, par ex si tu as bien une dernière période comprenant 7 jours. A contrôler donc.

@ plus

 

[Aloha]

Re,
Veux-tu dire par là que la formule modifiée ne fonctionne pas correctement non plus?
A+
Aloha

 

[CISCO]

Bonsoir

Re,
Veux-tu dire par là que la formule modifiée ne fonctionne pas correctement non plus?
A+
Aloha

Oui. Elle ne donne pas le bon résultat sur la dernière ligne lorsque la dernière période comprend 7 lignes. Ce n'est qu'un détail... à mon avis, assez facile à corriger. Je regarderai cela plus tard.

@ plus

 

[CISCO]

Bonjour

Tout compte fait :
1) Comme j'ai trop de difficultés pour trouver des formules donnant l'heure de début et celle de fin (colonnes AA et AB) en prenant en compte tous les cas possibles, et en écrivant le résultat sur la bonne ligne
2) et comme j'ai remarqué que cela commençait à ramer avec juste 4 onglets
j'ai essayé de rédiger une macro dans la module 1 donnant directement le temps de repos (colonne AE). Cf. en pièce jointe. Pour le moment, il faut effacer la colonne AE à la main entre chaque essai.

Bien sûr, c'est à améliorer (neutralisation de l'affichage pendant les calculs, nettoyage de cette colonne, calcul de la colonne AG, lancement automatique de cette macro, nettoyage du gestionnaire de noms, suppression des colonnes AA et AB peut être...). Jette un œil là-dessus et dis moi si cela te semble une voie possible, si les résultats de la colonne AE sont corrects.

@ plus

 

[Aloha]

Bonjour,
J'ai testé ta macro; elle fonctionne, mais seulement pour une feuille, comme j'ai cvompris, toujours pour la feuille active. Il faut donc aussi l'étendre aux autres feuilles. Je vais essayer si j'y arrive en prenant le code requis dans une autre macro.
Les résultats de la colonne AE sont corrects!
Par contre, dans la cellule AB75 de la feuille Salarié 3 il y a "FAUX" comme résultat, ce qui, si j'ai bien analysé, n'a aucun impact sur le comptage des heures et des cycles. Si je comprends bien ta remarque concernant l'éventuelle suppression des colonnes AA et AB, ce faux résultat ne semble pas être utilisé dans les formules en aval?
Les points que tu soulèves qui restent:
* neutralisation de l'affichage: pas de problème
* calcul de la colonne AG: les formules sont toujours en place!
* lancement automatique: je devrais trouver cela dans un forum resp. dans l'aide
* nettoyage du gestionnaire des noms: donc aussi à intégrer dans la macro? Problème: je ne sais pas exactement en quoi existe ce nettoage ni comment l'intégrer dans la macro
* suppression des colonnes AA et AB peut être: comme je ne comprends pas tes formules je ne sais pas y porter un jugement
A+
Aloha

 

[CISCO]

Bonjour

Bonjour,
J'ai testé ta macro; elle fonctionne, mais seulement pour une feuille, comme j'ai compris, toujours pour la feuille active. Il faut donc aussi l'étendre aux autres feuilles. Je vais essayer si j'y arrive en prenant le code requis dans une autre macro.
Les résultats de la colonne AE sont corrects!
Par contre, dans la cellule AB75 de la feuille Salarié 3 il y a "FAUX" comme résultat, ce qui, si j'ai bien analysé, n'a aucun impact sur le comptage des heures et des cycles. Si je comprends bien ta remarque concernant l'éventuelle suppression des colonnes AA et AB, ce faux résultat ne semble pas être utilisé dans les formules en aval?t

Il faut que je regarde l'origine de ce FAUX.

* calcul de la colonne AG: les formules sont toujours en place!

Oui, mais on peut aussi le faire avec une macro, à voir si c'est plus intéressant que les formules

* nettoyage du gestionnaire des noms: donc aussi à intégrer dans la macro? Problème: je ne sais pas exactement en quoi existe ce nettoyage ni comment l'intégrer dans la macro

Non, c'est à faire à la main, une seule fois, histoire d'avoir un fichier plus propre. Mais avant de le faire, il faut que je "retrouve" les liens existant entre les divers noms utilisés, pour ne pas en supprimer un, qu'il faut en réalité, garder.

* suppression des colonnes AA et AB peut être: comme je ne comprends pas tes formules je ne sais pas y porter un jugement

Elles ne sont peut être plus indispensables, la colonne AE étant maintenant calculées sans utiliser les valeurs contenues dans ces colonnes AA et AB.

@ plus

P.S : Avant de te précipiter... Je vais encore un peu regarder la macro existante, histoire de voir si on ne peut pas faire plus concis ou plus rapide.

 

[CISCO]

Bonsoir

Comme d'habitude, tout n'est pas si simple...

Dans Salarié 4!AE28, donc sur la ligne 28, on a 13:00 qui correspond à une pause prise à cheval sur le 11 et le 12/01, sur les lignes 26 et 27. Donc, il arrive qu'une durée de repos soit indiquée sur une ligne située plus bas que le jour correspondant à cette pause. Si cela arrive à la fin d'une période de 7 jours, on aura peut être certaine fois une période de plus de 44 h signalée trop tard, sur la 1ère ligne de la période de 7 jours suivante. Cela risque de fausser les calculs. Rarement, mais cela peut arriver.

Conclusion : Puisque le but de cette partie des calculs, des colonnes AA à AG n'est pas de déterminer les heures de début, de fin de travail, les durées de toutes les pauses... mais de vérifier la présence ou l'absence de période de plus de 44 h de repos, il ne faut inscrire que ces durées de repos de plus de 44 h, sur la bonne ligne. Donc, je vais essayer de faire une macro pour obtenir les résultats de la colonne AG ou AH directement.

@ plus

 

[CISCO]

Bonjour

En pièce jointe, une autre version comprenant :
* une macro remplissant la colonne AE, nommée débutAE, dans le module 1, idem à la dernière macro déjà proposée.
* une autre formule dans la colonne AF. Puisqu'on travaille avec une fichier annuel, et plus mensuel, commençant forcément le 1/1 à 00:00, on peut utiliser des formules plus simples que celles que j'ai proposées jusque là. On doit certainement aussi pouvoir supprimer des cellules vertes. J'essayerai plus tard.
* une nouvelle macro, nommée débutAG, dans le module 2, indépendante des colonnes de AA à AF, remplissant directement la colonne AG.

Je n'ai fait des tests que sur les feuilles salarié 3 et salarié 4. A tester davantage donc.

@ plus

 

[Aloha]

Bonsoir,
Merci beaucoup pour tout ce travail. Je testerai demain.
Bonne nuit
Aloha

 

[Aloha]

Bonjour,

Tes macros semblent fonctionner sans faille! Chapeau! Et toi qui prétendais ne pas être très familier avec VBA!
Cependant, sans vouloir paraître difficile ou prétentieux -comment pourrais-je?- il y a 2 bémols que j'ai remarqués:
* la lenteur: l'opération de copie est déjà très lente (80 min.); s'y ajoute le temps que mettent les macros. Je crains que les utilisateurs ne voudront pas utiliser ces fichiers annuels mais préféreront continuer leur méthode provisoire actuelle: le manuel
* toute la feuille est calculée à chaque fois. Au mois de décembre toute l'année est recalculée; il suffirait de calculer les données nouvellement copiées; désavantage: s'il y avait une erreur dans une saisie déjà présente, il n'en serait pas tenu compte.

Il faudrait trouver un moyen pour accélérer le tout, la copie et le calcul de ces 2 colonnes.

A+
Aloha

 

[CISCO]

Bonsoir

Bonjour,
- il y a 2 bémols que j'ai remarqués:
* la lenteur: l'opération de copie est déjà très lente (80 min.); s'y ajoute le temps que mettent les macros.

J'avais remarqué cette lenteur. Je vais essayé de trouver plus rapide.

Je crains que les utilisateurs ne voudront pas utiliser ces fichiers annuels mais préféreront continuer leur méthode provisoire actuelle: le manuel.

Combien de temps leur faut-il pour traiter tous les fichiers, à la main ?

* toute la feuille est calculée à chaque fois. Au mois de décembre toute l'année est recalculée; il suffirait de calculer les données nouvellement copiées; désavantage: s'il y avait une erreur dans une saisie déjà présente, il n'en serait pas tenu compte.
...
A+
Aloha

J'y avais pensé. Là aussi j'essayerai de trouver une astuce pour que la macro ne recommence pas les calculs à la ligne 16 à chaque fois.

@ plus

P.S : En fait, les colonnes AE et AF ne sont pas indispensables. Donc, on peut gagner un peu de temps en les supprimant.

 

[Aloha]

Bonjour,

une astuce pour que la macro ne recommence pas les calculs à la ligne 16 à chaque fois

Comme j'ai dit auparavant la situation peut se présenter -et se présentera- oùune modification est faite à la saisie existant. Il faut donc garder la possibilité de tout recalculer, ou alors d'indiquer deux dates entre lesquelles il faut recalculer.

Combien de temps leur faut-il pour traiter tous les fichiers, à la main ?

Aucune idée pour les 44 heures. Je leur demanderai lorsque je les verrai (le jeudi après les vacances). Pour les P cela doit être rapide: dans la base de données où les résultats mensuels sont collectés (les totaux) on peut ispoler les personnes qui ont des P (pas très nombreux) et regarder leurs fiches mensuelles.

A+
Aloha

 

[CISCO]

Bonjour

J'ai apporté quelques modifications à la macro, trouvé quelques simplifications (On n'a plus besoin d'aller lire le contenu de la dernière cellule de la ligne précédente dans la colonne 24), évité quelques tests avec un If.... Then .....Else..... End If (Dans la version précédente, je n'avais pas pensé au Else..., donc, on faisait des If test1 then...., If test2 then..., If test3 then... alors que seul le premier If doit être utilisé, alors que seul le test1 est intéressant quand les cellules ne correspondent pas à du travail).
Cela tourne plus vite (2 ou 3 secondes), mais cela n'est pas encore très rapide pour le peu qu'il y a à faire. Je ne comprend pas trop pourquoi c'est si "lent". On doit certainement pouvoir procéder autrement, sans aller lire dans chaque cellule, les unes après les autres, autrement qu'avec deux boucles imbriquées l'une dans l'autre, For lig = 1 To UBound(tablo), For col = 1 to 24...

Mais vu mon niveau en VBA, je ne sais comment faire. Cette petite macro ressemble plus à du basic qu'à un code rédigé en VBA.

Tu peux faire quelques essais en cliquant sur le bouton GoAG.

En l'état actuel, la macro recommence les calculs à la fin de la dernière période de 7 jours déjà renseignée en colonne AG. Donc, si tu rajoutes des données, cela ne reprend les calculs qu'un peu au-dessus de ces nouvelles dates.
Si on veut recommencer sur une ligne particulière, suite à une modification, il faudra rajouter un userform à faire renseigner par l'utilisateur, avec une question du style : "A partir de quelle date faut-il faire les calculs ?".

@ plus

P.S : Deux "/" ou "XP" ou plus successifs ne sont pas correctement pris en compte par la macro.