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,

 

[Aloha]

Bonsoir,

Quand tu as XP tout seul, est-ce que cela donne aussi droit à 0:30 h de congé en plus ?

Théoriquement oui, puisqu'il n'y a qu'une limite supérieure (24 heures), mais c'est un cas tout à fait théorique qu'on peut négliger.

Ou est-ce qu'en pratique, tu as plutôt des cas avec une série de P commençant et/ou finissant avec un XP ou un P2 ?

Oui, c'est exactement cela.
A+
Aloha

 

[Aloha]

PS : Les situations du style ...P;XP;P... ou P;P2;P ne sont pas prises en compte par les nouvelles formules des colonnes AL, AM et AN. Mais je pense qu'elles ne se présentent jamais dans la réalité... Je pense, du moins j'espère.

On peut négliger de telles situations qui, en effet ne se présenteront pas.
De toute façon il y a toujours moyen de corriger les résultats manuellement s'il faut.
Je vais tester demain.

D'ailleurs, autant dans mon fichier original que dans celui que tu viens de renvoyer, Excel se plaint de références circulaires. Les cellules suivantes ont été indiquées, mais je n'y trouve pas de référence circulaire: AL48, AL161, AM307.
A+
Aloha

 

[CISCO]

Bonjour

...
De toute façon il y a toujours moyen de corriger les résultats manuellement s'il faut.

Oui, mais cela force à être précis, et cela prend du temps... Et, la "routine" aidant, il y a des risques d'erreur. Au besoin, tu peux mettre une MFC pour mettre en évidence les lignes contenant des passages en P;XP;P... ou P;P2;P

...D'ailleurs, autant dans mon fichier original que dans celui que tu viens de renvoyer, Excel se plaint de références circulaires... de référence circulaire: AL48, AL161, AM307

Étrange... Je n'ai pas ce message à l'ouverture du fichier chez moi. Je vais regarder...

Et comment est-ce qu'il faut compter les successions du style P2;X;X;X;X;X;X;X;X;P2 ? Cela donne aussi droit à 2 * 0:30 de congé supplémentaire ? Et est-ce que ce cas peut se présenter ?

@ plus

 

[CISCO]

Bonjour

J'ai essayé de voir s'il y avait un problème dans les cellules AL48, AL161 et AM307, mais je ne vois pas.

Ce pb/ref circulaires est embêtant car parfois cela entraine des résultats "aberrants" qu'on ne voit pas tout de suite.

En conséquence, j'ai un petit peu changé les formules dans les colonnes AL, AM et AN en mettant devant un test éliminant les calculs lorsque la plage Bx:Yx, ou que la cellule à gauche est vide.

@ plus

 

[Aloha]

Bonjour,

Et comment est-ce qu'il faut compter les successions du style P2;X;X;X;X;X;X;X;X;P2 ? Cela donne aussi droit à 2 * 0:30 de congé supplémentaire ? Et est-ce que ce cas peut se présenter ?

Théoriquement oui, mais je pense que dans la pratique cela n'arrivera pas sans des P avant le premier P2 et des P après le 2ème.

A+

 

[Aloha]

Re,
Si je mets la nouvelle formule dans AL16, je reçois #NOM.
A+
Aloha

 

[CISCO]

Bonjour

J'ai défini un nouveau nom, groupedePXPP2, dans le gestionnaire de noms, dans le fichier du post #225.

@ plus

 

[Aloha]

Re,
Lorsque je mets les nouvelles formules, après avoir défini le nouveau nom, il reste toujours #NOM dans la colonne AO et à chaque fois que j'avais mis l'une des 3 formules matricielles, une fenêtre s'est ouverte avec l'intitulé "Mettre à jour les valeurs" et invitant à sélectionner un fichier.
A+
Aloha

 

[CISCO]

Bonsoir

Est-ce que la feuille que tu utilises porte le même nom que la feuille de ma pièce jointe ?

@ plus

 

[Aloha]

Re,

Non, mon fichier modèle s'appelle "Fiches annuelles" et les copies que j'en fais prennent le nom des services & Espace & l'année.
Celui du service "XY" pour 2018 s'appellera donc "XY 2018".
A+
Aloha

 

[CISCO]

Bonjour

Il faut que la définition du nom groupedePXPP2 utilise le nom de la feuille où il est utilisé.
Dans ma dernière pièce jointe, cette définition commence par
=FREQUENCE(SI(SUBSTITUE(SUBSTITUE('Ag X'!$B16:$Y16;"XP";"P");"P2";"P")...
car ce nom est utilisé sur la feuille 'Ag X'.
Il faut que tu écrives dans le gestionnaire de noms cette définition avec le nom de la feuille que tu utilises à la place des 'Ag X' contenus dans cette formule.
Le plus simple, c'est encore de sélectionner la cellule AL16, et de supprimer dans cette définition dans la fenêtre du gestionnaire tous les 'Ag X'!. Excel écrira automatiquement le noms de la feuille en cours où il le faut.
Cela donne, pour ce qui est du début cité ci-dessus
=FREQUENCE(SI(SUBSTITUE(SUBSTITUE($B16:$Y16;"XP";"P");"P2";"P")... qui se transformera automatiquement en =FREQUENCE(SI(SUBSTITUE(SUBSTITUE(nomdelafeuille!$B16:$Y16;"XP";"P");"P2";"P")...

@ plus

 

[Aloha]

Bonsoir,
Merci beaucoup, ...avec un peu de retard. Je vais suivre tes conseils.
A+
Aloha

 

[Aloha]

Bonjour,

Le problème avec cette méthode est que je duplique par macro la seule feuille qui s'appelle "Modèle" en réalité et que les copies prennent le nom des salariés, mais que dans la formule restera "Modèle". Comment contourner ce problème?
Au pire, je dois persuader Excel de mettre les bonnes formules par macro.

Bon dimanche
Aloha

 

[CISCO]

Bonjour

J'avais pensé à cette possibilité, mais j’espérai ne pas y être "confronté".
On peut effectivement contourner ce problème avec du VBA. J'ai testé une autre possibilité en modifiant tous les noms dans le gestionnaire de noms, avec du INDIRECT dans tous les coins, pour qu'Excel n'insère pas automatiquement le nom de l'onglet initial, Ag X, dans les formules. Cela semble fonctionner. A toi de tester.

@ plus

P.S. : Dans le cas présent, c'est plus pratique de faire avec des noms, dans le gestionnaire de noms, définis sur tout le classeur, et pas sur la feuille uniquement. Dans ce cas, Excel ne rajoute pas le nom de la feuille devant le nom utilisé, uniquement devant l'adresse des cellules ou des plages utilisées.
P.S.: En passant par INDIRECT dans le gestionnaire de noms, Excel voit d'abord les adresses ou les plages comme du texte, et ne rajoute donc pas le nom de l'onglet devant. Mais cela rallonge les formules et ce n'est pas facilement applicable lorsqu'on utilise des références relatives ou semi-relatives.

 

[Aloha]

Bonsoir,
Je n'ai rien contre les formules avec INDIRECT() du moment qu'elles ne renvoient pas à un autre classeur.
Comme tu dis avoir modifié des noms il vaut sûrement mieux que je retienne le fichier que tu as joint au lieu de remplacer les formules dans mon fichier réel.
A+
Aloha