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

Que représentent les S3, des heures de travail, ou autre chose ?
Comment comptes-tu les 1/2 h, avec les / ?

Et pour ce qui est des cellules que tu as mis à raison en jaune, il y a un problème de calculs intermédiaires et/ou d'arrondi : Dans AG32 44:00 correspond à 1,833333333328480 et dans AH36, la formule compare à 44/24, c.à-d. à 1,83333333333333. Il faut que je trouve l'origine de cette différence. Dans le pire des cas, on doit aussi pouvoir faire en ne prenant en compte qu'un certain nombre de chiffres décimaux. A voir.

@ plus

 

[CISCO]

Rebonjour

J'ai repéré l'origine de l'erreur d'arrondi. Ne me reste plus qu'à trouver comment la contourner...

@ plus

 

[Aloha]

Re,
Tu peux ignorer les S3 (un de 6 types d'heures supplémentaires), puisque ce code ne se trouve pas dans la liste des codes dans AQ et n'est donc pas compté.
D'ailleurs, la liste des codes n'est pas complète. A la fin, quand tout sera prêt, les responsables devront la compléter.
Et les demi-heures sont saisies en mettant un "/".
A cette occasion je me rends compte qu'ils sont aussi dans la liste (ce qui est bien exact) mais jusqu'ici il sont c^omptés comme heure entière. Le mieux serait évidemment qu'il seraient comptés comme demi-heuire seulement, mais cela complique les choses davantage encore et je pensais ignorer qu'une demi-heure est comptée de trop. Je ne pense pas que cela change grand-chose dans le comptage des 44 heures.
A+
Aloha

 

[CISCO]

Bonjour

Il y a de nouveau des noms valables que sur une feuille dans ta dernière pièce jointe. Est-ce que cela provient de la macro, ou de noms mal définis, d'INDIRECT manquants ? Je ne sais. J'ai supprimé ces noms valables que sur une feuille. J'espère que tout cela ne posera pas de problème.

Pour résoudre le problème des arrondis, j'ai modifié les formules des colonnes AE et AG. Le résultat me semblant vraiment trop compliqué, j'ai fini par mettre uniquement des ARRONDI dans la formule de la colonne AH (Les nombres sont arrondis 10 chiffres après la virgule). Cela ne pose pas de problème puisqu'on travaille de 1/2 h en 1/2 h, et pas à la minute près.

Pour tenir compte des 1/2 h signalées par les /, j'ai modifié les formules et le format des colonnes AA et AB et rajouté quelques noms dans le gestionnaire de noms.

J'ai aussi raccourci plusieurs noms dans ce gestionnaire.

Tout cela me semble bien compliqué. J'ai eu du mal à comprendre les formules initiales dans les colonnes AA et AB, et pourtant, c'est moi qui les ai écrites !! Si j'ai le courage, j'essayerai de reprendre les formules de ces deux colonnes.

@ plus

 

[Aloha]

Re,

Est-ce que cela provient de la macro, ou de noms mal définis, d'INDIRECT manquants ?

Je pense que cela ne vient ni de l'un ni de l'autre.
Les noms dont tu parles sont H1 H2 et H3? Est-ce qu'il y a une procédure spéciale pour les rendre valables pour le classeur entier? Je dois avouer ne pas maîtriser à 100% la gestion des noms. Il n'y a d'ailleurs qu'une seule feuille dans le classeur dont sont générées toutes les feuilles.
Si ce sont ces noms-là qui posent problème je peux treès bien les effacer et utiliser l'aire qu'ils définissent.

Tout cela me semble bien compliqué. J'ai eu du mal à comprendre les formules initiales dans les colonnes AA et AB, et pourtant, c'est moi qui les ai écrites !!

Ca, je veux bien le croire! Je suis bouche-bée devant tes formules!
Ne te tracasse pas trop avec cela!

Si j'ai le courage, j'essayerai de reprendre les formules de ces deux colonnes.

Je vais attendre alors un peu avant de recommencer à 0 avec ton dernier fichier.

D'ailleurs, je constate que ce tas de formules rend l'ouverture des fichiers bien lente. Il faudrait peut-être que j'essaye de remplacer par macro les formules des lignes contenant les saisies copiées par leur valeur. Je vais essayer. Je peux m'imaginer que cela pourrait aider, puisque ce sont sûrement les formules "actives" (c.à.d. celles ayant des données à calculer) qui ralentissent.

Bonne nuit
Aloha

 

[CISCO]

Bonsoir

Que faut-il afficher sur les lignes 45 et 46 : On ne prend pas en cause la pause de 12 à 13 h ?

@ plus​

 

[Aloha]

Bonjour,
Tu veux dire pour compter les 44 heures?
Dans la pratique ces pauses de midi lors d'une formation (F) peuvent être ignorées. Seulement, techniquement, est-il possible de les ignorer puisque c'est un début de temps libre, même s'il ne dure qu'une heure. Si elles ne sont pas comptées, alors il faut définir à partir de combien d'heures entre 1 h et 44 h le temps libre est compté.
N'est-il pas plus facile de les compter pour éviter cette tâche supplémentaire?
Bon dimanche
Aloha

 

[CISCO]

Bonjour

Cf. en pièce jointe où j'en suis pour le moment, pour prendre correctement en compte les 1/2 h signalées par des / (ou des XP).
J'ai changé les formules dans les colonnes AA, AB et AD. Est-ce que tu pourrais regarder si cela semble te donner les bons résultats (uniquement dans les colonnes AA à AD), sans utiliser pour le moment la macro, mais en mettant d'autres situations, d'autres enchainements possibles ?

J'ai un peu modifié la définition de certains noms. Il y a certainement trop de noms dans ce gestionnaire de noms. J'essayerai d'y faire du nettoyage plus tard.

Pour ce qui est des "petites pauses", je ne sais... Je vais y réfléchir.

@ plus

 

[Aloha]

Bonsoir,
Pour l'instant je suis totalement bloqué par le refus de ma macro de copier les données des vieilles fiches mensuelles dans les nouvelles. Après je copie les données des nouvelles mensuelles dans les annuelles.
Je dois d'abord résoudre ce problème avant de pouvoir continuer avec les annuelles.
A+
Aloha

 

[Aloha]

Bonsoir,
Je suis en train de copier les saisies de janvier dans ton dernier fichier. Pour 120 salariés de 10 services cela dure 80 minutes.
Comme il me semble que cette lenteur est due pour une bonne partie au fait que dans les fichiers mensuels les cellules sont fusionnées verticalement deux par deux et que la macro doit d'abord lever cette fusion cellule par cellule, et trier les données pour éliminer les lignes vides avant de copier les saisies, je me suis demandé combien ce serait onéreux, côté formules, de reproduire les mêmes fusions de cellules dans le fichier annuel, ce qui signifierait qu'il y aurait des formules dans une ligne sur deux seulement.
Qu'en penses-tu?
Bonne nuit
Aloha

 

[Aloha]

Bonjour,
J'avais espéré que la copie des saisies mensuelles pour février serait plus rapide que celle de janvier, parce que les fiches sont déjà existantes, mais il n'en était rien.
Malheureusement il y a un petit problème dans la colonne AB.
Le problème apparaît lorsqu'il y a des saisies le 1.1.
Si, p.ex., je mets un X pour le salarié 1 le 1.1., le problème apparaît, non seulement pour le 1.1., mais aussi pour le 3.1.; sans ce X il disparaît dans les deux cellules.
Je suis vraiment désolé.
A+
Aloha

 

[CISCO]

Bonjour

Le dernier problème que tu signales, dans AB16, vient de la partie de la formule SI(AB14="";0;ARRONDI.SUP(AB14;0)))) sur la 4ème ligne dans la barre des formules :
1) Comme AB14 contient du texte, ARRONDI.SUP(AB14;0) renvoie #VALEUR si la ligne 16 contient un ou des X correspondant à un temps de travail à prendre en compte.
2) Je ne comprend pas pourquoi, étant en ligne 16, la formule va chercher une valeur en ligne 14 et pas en ligne 15, comme cela devrait le faire, il me semble. C'est le cas dans toute cette colonne : en ligne x, la formule utilise une valeur provenant de la ligne x - 2, alors que c'est celle de la ligne x - 1 qui nous intéresse, si mes souvenirs sont bons. C'est aussi le cas dans ma dernière pièce jointe. Cela vient peut-être du fait que les cellules AB14 et AB15 sont fusionnées... J'ai peut être fait un copier-coller malheureux qui a propagé cette erreur en dessous de la ligne 16.

J'essaye de comprendre s'il faut travailler avec la valeur de la ligne x - 1 ou de la ligne x - 2 avant de corriger tout cela.

@ plus

 

[CISCO]

Rebonjour

J'ai trouvé d'autres cas posant problème. Dans la foulée, j'essaye de simplifier les formules des colonnes AA et AB. Pas évident de bien prendre en charge tous les cas possibles.

@ plus

 

[Aloha]

Re,
Je suis vraiment navré, mais j'ai détecté une autre irrégularité: le 28 février, alors que les 7 jours ne sont pas encore complets (ils le seront seulement le 3 mars), la formule dans AH75 du salarié 4 met déjà un chiffre 1, alors que 4 jours se sont seulement écoulés et qu'il reste donc 3 jours pour avoir les 44 heures.
A+
Aloha

 

[CISCO]

Bonjour

Dans le dernier cas que tu signales, c'est plutôt le test initial qui est incomplet : Dans cette cellule, il ne faudrait pas de résultat. C'est assez facile à corriger avec un

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

toujours dans la ligne 75, à tirer vers le haut et vers le bas.

@ plus