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]

Bonsoir

Oui, il faut que tu fasses des essais avec cette dernière pièce jointe.
Je ne suis pas certain que cela fonctionne correctement, puisque certains noms sont définis uniquement par rapport à la feuille Ag X. Si cela ne fonctionne pas, il faudra utiliser les mêmes définitions, mais en mettant comme étendue le classeur. On ne peut pas le faire directement dans la fenêtre "gestionnaire de noms". Il faut redéfinir chaque nom, sans rien changer à sa définition, mais en faisant bien attention à l'étendue. Avec plusieurs copier-coller, c'est faisable. Si tu as un problème, je le ferai.

@ plus

 

[CISCO]

Bonjour

Où est-ce que tu en es, Aloha, avec ce travail ? Tu t'en sors ?

@ plus

 

[Aloha]

Bonjour,
Merci pour ton message!
Je dois avouer avoir fait une pause. Il faut que je m'y mette puisque la prochaine fin de mois approche et si je veux avoir la moindre chance pour être prêt je dois m'y mettre.
A+
Aloha

 

[Aloha]

Bonjour,
Enfin, je me suis remis à l'affaire.

Comme mon oisiveté passagère m'a permis de gagner un peu de distance par rapport à cette tâche bien difficile, j'ai réfléchi à nouveau sur la solution manuelle, mais, après réflexion, je me suis rendu compte qu'elle est et reste aussi difficile à réaliser.

1. Méthode par fiche annuelle
Pour éviter les problèmes avec les noms j'ai pris ton dernier fichier comme base, j'ai ajouté les quelques formules qui y manquent, et j'ai changé le nom de la feuille en "Modèle", le nom utilisé par la macro.
Cela devrait fonctionner ainsi. Puis il y a le problème des noms.
Je dois avouer avoir des difficultés pour comprendre la façon dont Excel gère les noms. Lorsque j'ouvre le gestionnaire des noms dans un fichier mensuel rempli, il y a plein de noms avec d'abord le nom de la feuille.
Si je comprends bien, avec la méthode que tu préconises dans ton post #241 les noms sont rattachés au classeur entier et non pas à chaque feuille.
Si j'ouvre le gestionnaire de noms je constate que certains sont rattachés au classeur, les autres à la feuille unique et je ne vois pas comment changer cela.

2. Méthode par fiche mensuelle - simple réflexion
Vu les nombreux problèmes et la procédure plus complexe et nettement ralentie avec la méthode des fiches annuelles, je me suis rappelé qu'on avait, dans une première étape, basé tous les calculs sur les fiches mensuelles existantes, mais on s'est rendus compte que pour calculer correctement les 44 heures et les permanences il fallait disposer des données des premiers jours du mois suivant et on s'est orientés vers le détour de fiches annuelles.
En contemplant la méthode manuelle povisoire que notre administration a adoptée, je me suis rendu compte qu'en fait ils ont le même problème: à la fin du mois ils ne savent pas non plus fournir des chiffres définitifs aux salariés en ce qui concerne ces deux paramètres. Seulement, au besoin, ils corrigent les chiffres manuellement le mois d'après. Pour en faire autant par formules et VBA, sans intervention humaine, il faudrait donc arriver à garder des données du mois précédent auxquelles seraient rajoutées les données des premiers jours du mois actuel, et là je me rends compte que ce serait difficile à réaliser

Conclusion de cette réflexion: solution annuelle, si un jour j'y arriverai, ou solution manuelle.

Bonne journée
Aloha

 

[CISCO]

Bonjour

De mon coté, j'y perd un peu mon latin... Mais bon, en m'y replongeant, cela devrait revenir .

En général, j'évite de travailler avec des noms attribués à une feuille uniquement. Je ne sais d'ailleurs pas pourquoi présentement il y a des noms attribués à la feuille Modèle ou Ag x, et presque le même nom valable sur tout le classeur, par exemple codes_comptés (valable sur la feuille) et code_comptésfeuille (valable sur le classeur).

Dans le fichier joint, j'ai supprimé tous les noms valables uniquement sur la feuille en les rendant utilisables sur tout le classeur (et en supprimant ceux en double bien sûr) et cela semble fonctionner correctement. Pourrais-tu vérifier la validité des résultats dans divers cas, et faire un ou deux tests avec ta macro, juste pour voir ce que cela donne dans le gestionnaire de noms ?

@ plus

 

[Aloha]

Bonjour,
Merci!
J'ai pris ton fichier pour base, y ai ajouté les formules manquantes et je suis en train d'y injecter les données des fiches mensuelles de janvier. Bientôt j'aurai aussi celles de février.
Je vais voir ce que cela donnera.
A bientôt
Aloha

 

[Aloha]

Bonsoir,

Je suis désolé, mais j'ai constaté des problèmes.
Les colonnes AE et AG ne calculent plus correctement (p.ex.: AE20: 104 au lieu de 20); on dirait que les chiffres son additionnés puisqu'ils vont en croissant; par contre, les colonnes AB et AD n'affichent aucun résultat .

J'ai pris le dernier fichier (post 245) et l'avant-dernier (post 236) et j'y ai collé des données réelles.

En ce qui concerne les noms, il y en a toujours qui se rapportent à la feuille et non pas au classeur,
D'ailleurs il y en a un qui s'appelle "Janvier 2004"!

A+
Aloha

 

[CISCO]

Bonsoir

Bonsoir,
...
J'ai pris le dernier fichier (post 245) et l'avant-dernier (post 236) et j'y ai collé des données réelles.
En ce qui concerne les noms, il y en a toujours qui se rapportent à la feuille et non pas au classeur,

A+
Aloha

Pour ce qui est du fichier fait à partir de celui du post 236, c'est normal. Dans ce dernier, j'avais placé des INDIRECT pour ne plus avoir le problème des noms d'onglets mis automatiquement par Excel, mais je n'avais pas éliminé les noms valables uniquement sur la feuille.

Pour le moment, il ne faut se servir que du fichier du post 245, ou des fichiers provenant de celui-ci.

Pour ce qui est du nom janvier 2004, je ne sais d'où il vient. Il y en avait un dans le fichier source m'ayant servi à créer le fichier du post 245, donc je l'ai gardé.

Je vais essayer de trouver d'où viennent les erreurs.

@ plus

 

[CISCO]

Rebonsoir

La formule dans la colonne AB utilisait le nom Codes_comptés, non défini dans le gestionnaire de noms, donc cela ne fonctionnait pas. Dans le gestionnaire de noms, il y avait à la place, Codes_comptésclasseur, valable sur tout le fichier, que j'ai raccourci en codes_comptésclas, et mis dans la formule de cette colonne AB.

Regardes si cela fonctionne mieux.

@ plus

P.S : Le nom Janvier 2004 me semble inutile (Il désigne la plage A13:Y78). Je l'ai désactivé.

 

[Aloha]

Re,
Merci beaucoup.
Je suis navré de te faire autant de travail.
Il faut dire que l'histoire des noms dans Excel est bien compliquée.
J'essayerai demain.
Bonne nuit
Aloha

 

[Aloha]

Bonjour,

J'ai donc injecté les données dans la dernière mouture du fichier.
Il y a malheureusement un problème dans la colonne AO, pour les dates 18.-21.1.

J'ai alors ajouté manuellement, pour tester, d'autres P du 6.-8.1. et là il y a un résultat.
Bizarre!

A+
Aloha

 

[CISCO]

Bonjour

Je vais essayer de comprendre d'où vient ce problème. La formule dans la colonne AO est vraiment tordue.

Dans le gestionnaire de noms, il y a encore des noms valables que sur la feuille. Cela vient peut être de là.

@ plus

 

[CISCO]

Bonjour

Dans le fichier de mon post #249, plusieurs noms comportaient encore le nom de la feuille dans leur définition, Ag X! dans le fichier XP et P2 post245. En transformant le premier de la liste ayant ce défaut, groupedePmoinsunclasseur, en utilisant des INDIRECT dans cette définition, tous les autres Ag X! des autres noms me semblent avoir disparu.

Pourrais-tu faire un autre test avec cette nouvelle mouture ?

@ plus

P.S : Au passage, j'ai raccourci les noms en xxxxclasseur en xxxxclas, histoire de rendre tout cela un tout petit peu plus lisible.

 

[Aloha]

Re,

Dans le gestionnaire de noms, il y a encore des noms valables que sur la feuille. Cela vient peut être de là.

J'avais remarqué cela, mais oublié de le mentionner.
Je vais tester.
D'ailleurs, l'opération de copie des données des fiches mensuelles de janvier de quelque 120 salariés dans les fichiers mensuels par service par macro dure 80 minutes!
Je peux m'imaginer que pour les données de février cela pourrait aller plus vite puisque plus de 95% des fiches existent déjà, alors que pour le mois de janvier, toutes les fiches doivent être d'abord créées.
Je viens de recevoir les fiches de février et peux tester.

A+
Aloha

 

[Aloha]

Bonjour,
J'ai donc testé et les affichages d'erreurs ont disparu.
Par contre, j'ai découvert des calculs incorrects concernant les 44 heures:
Dans le fichier joint j'ai marqué en jaune 2 périodes de 44 resp. 44,5 h. de libre à l'intérieur d'un ensemble de 7 jours, alors que les formules affichent un ensemble de 7 jours sans 44 h. de libre. La règle est qu'il faut au moins 44 heures de libre, ce qui est donc même 2 fois le cas.
A+
Aloha