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

Non, le module 1 peut être supprimé.

La macro ne crée que le tableau trié. Les autres à droite sont remplis grâce à des formules. Il faut donc tirer ces dernières vers le bas aussi longtemps que nécessaire.

@ plus

 

[Aloha]

Bonsoir

Non, le module 1 peut être supprimé.

@ plus

Re,
OK, mais cela ne change rien en réalité pour la vitesse. Ce n'est que du texte du moment qu'elles ne sont pas exécutées.
Il me semble que les calculs de la colonne AE ne sont pas corrects. Il suffit de tirer les données de DUPONT Marie vers le bas, de sorte qu'elle n'a aucun temps libre par 7 jours pour s'en rendre compte.
Ceci est évidemment une situation extrême qui ne se produira pas dans la réalité.
Bonne nuit
Aloha

 

[CISCO]

Bonsoir

Re,
... Il me semble que les calculs de la colonne AE ne sont pas corrects. Il suffit de tirer les données de DUPONT Marie vers le bas, de sorte qu'elle n'a aucun temps libre par 7 jours pour s'en rendre compte.

Bonne nuit
Aloha

J'ai mis 02/01/2005 dans A4 et tiré ensuite la cellule A307 vers le bas jusque la ligne 397. Ensuite, j'ai tiré toute la dernière ligne du tableau initial vers le bas, ai rajouté une colonne de X pour n'avoir que des repos de 23 h max en bas du tableau, relancé la macro, prolongé les autres tableaux assez bas.

Ensuite, j'ai fait quelques tests en supprimant ou en rajoutant des X, et les résultats me semblent logiques.

@ plus

 

[Aloha]

Bonsoir,
J'ai fait un nouveau test en supprimant toutes les saisies de DUPONT Marie et en en rajoutant peu à peu et les résultats étaient corrects! Bravo!

Je vais injecter les données réelles d'un service pour l'année 2020 entière et je verrai ce que cela donnera.

Est-ce que la macro tient compte de modifications apportées après coup? Ce qui arrive dans la pratique: il se peut qu'on remarque seulement après la copie des données qu'une salariée a fait une mauvaise saisie tel jour à telle heure et la correction peut avoir un impact sur les calculs du temps libre.
A+
Aloha

 

[CISCO]

Bonsoir

Et non, il faut relancer la macro à chaque fois. Je vais réfléchir à ce problème, et modifier la macro pour lancer une MAJ automatique dès qu'il y a un changement.

Pour ce qui est du test avec un fichier réel, cela risque d'être long...

@ plus

 

[Aloha]

Pour ce qui est du test avec un fichier réel, cela risque d'être long...

Bonjour,
Pourquoi crois-tu cela?
De toute façon le système est dédié à fonctionner en situation réelle.

Es-tu sûr qu'en relançant la macro elle prend en compte des modifications? Il me semblait que non, mais je peux me tromper.

Bien à toi
Aloha

 

[CISCO]

Bonjour

Si on relance la macro, elle modifie le tableau trié, et les formules corrigent le "tir" automatiquement. De ce coté là, il ni y a pas de problème, mais ce n'est pas très ergonomique : il ne faut pas oublier de le faire.

@ plus

 

[Aloha]

Re,

il ne faut pas oublier de le faire

Cela c'est la moindre des choses!
Je n'ai pas encore reçu les fichiers pour essayer "grandeur nature".

Une question: trois des quatre tableaux que tu as créés contiennent un grand nombre de formules. Vois-tu une possibilité de les "mettre au repos" lorsqu'elles on fait leur travail, qui consiste à afficher un résultat, c.-à-d. de les coller avec la macro, juste dans autant de lignes qu'il y a de données nouvellement injectées et de les remplacer ensuite par leurs valeurs; ce qui impliquerait la nécessité de remettre les formules pour des saisies modifiées après coup. Quoique, la nécessité dépend de l'aire que la macro calcule en la lançant. Si elle recalcule p.ex. toutes les données d'une personne, alors la modification sera de toute façon prise en compte.

Remplacer les formules par leurs valeurs devrait alléger le tout considérablement, puisqu'il n'y aurait pratiquement pas de formules actives du tout.

A+
Aloha

 

[Aloha]

Re,
J'ai enregistré une macro inscrivant les formules et les remplaçant ensuite par leurs valeurs.

Sub TroisTableaux()
'
    Range("BJ4").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(@testWEcolAH*(RC[-25]=""""),IF(RC[-28]=R[-1]C[-28],R[-1]C[23],0)+1,0)"
    Range("BK4").Select
    ActiveCell.FormulaR1C1 = "=IF(@testWEcolAH*(RC[-25]=""""),RC[-1]+1,0)"
    Range("BK4").Select
    Selection.Copy
    Range("BL4:CG4").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("CI4").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-50]="""",IF(RC[-53]=R[-1]C[-53],R[-1]C[23],0)+1,0)"
    Range("CJ4").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-50]="""",RC[-1]+1,0)"
    Range("CJ4").Select
    Selection.Copy
    Range("CK4:DF4").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("DH4").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=(RC[-25]>43)*(RC[-24]=0)"
    Range("DH4").Select
    Selection.Copy
    Range("DI4:EE4").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("BJ4:EE4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BJ5:BJ36").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        Range("BJ4:EE36").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Seulement, j'ai commencé à la première ligne, la ligne 4 et j'ai tiré les formules jusqu'à la ligne 36, et il faudrait adapter la macro pour qu'elle traite juste les lignes contenant les données fraîchement injectées, et je ne sais pas comment m'y prendre pour garder l'information quelle était la dernière ligne avant l'exécution de la macro.

A+
Aloha

 

[CISCO]

Bonsoir

Re,
J'ai enregistré une macro inscrivant les formules et les remplaçant ensuite par leurs valeurs.
....quelle était la dernière ligne avant l'exécution de la macro.

A+
Aloha

Pour ce qui est de connaitre le n° de la dernière ligne utilisée, cela n'est pas très difficile.
On peut faire par ex avec :
dernièrelignom = Cells(Rows.Count, 34).End(xlUp).Row
pour connaitre le n° de la ligne contenant la dernière cellule de la colonne AH (= colonne 34) utilisée.

Pour ce qui est de stocker les formules, il suffit de les écrire ailleurs sur la feuille (Juste une de chaque sorte), et d'ensuite les coller dans les bonnes cellules, dans la ligne 4, puis de demander à la macro de les coller aussi longtemps que nécessaire vers le bas.

Pour ce qui est d'améliorer la macro pour qu'elle prenne en charge des modifications, si possible sans relancer tous les calculs, j'y réfléchi...

@ plus

 

[CISCO]

Bonjour

Ne serait-il pas plus simple de faire une sauvegarde des valeurs (toutes ou uniquement la liste des noms et les deux colonnes attenantes) sur une autre feuille ou dans un autre fichier, nommé automatiquement par la macro, nom comprenant l'année correspondante ?

La méthode de travail serait :
1)) Utilisation du fichier pour obtenir les résultats (avec la 1ère macro et les formules)
2) Sauvegarde avec une seconde macro

@ plus

 

[Aloha]

Ne serait-il pas plus simple de faire une sauvegarde des valeurs (toutes ou uniquement la liste des noms et les deux colonnes attenantes) sur une autre feuille ou dans un autre fichier

Bonjour,
(en réalité je voulais placer le "Bonjour" avant la citation, mais je ne sais pas comment le faire si je commence par la citation, un détail)

Quel avantage y vois-tu? Si dans le fichier unique et principal (à côté il y a encore un fichier modèle utilisé pour préparer les fichiers mensuels vides où les salarié/es saisissent leurs symboles; dans le post #42 j'avais joint un fichier mensuel rempli) j'ajoute la feuille "Saisies" où toutes les formules sont remplacées par leurs valeurs une fois leur travail fait, la présence de cette fiche ne devrait pas poser problème.

Utiliser une feuille supplémentaire dans le fichier unique, pourquoi pas, si tu vois de bons arguments pour procéder de la sorte.

Les résultats de ces calculs doivent être communiqués mensuellement aux salariés moyennant le même fichier mensuel et il me semble plus aisé de le faire si toutes les données à communiquer sont réunies dans un seul fichier, que j'avais baptisé provisoirement "Base" en 2005, et dont le nom est resté provisoire.

A+
Aloha

 

[CISCO]

Bonjour

J'ai pensé à cette possibilité surtout pour ne pas avoir à copier les formules: On garde une feuille qui sert aux calculs, et une ou d'autres sur lesquelles on n'envoie que des valeurs, qui ser(ven)t de sauvegarde. Au besoin, on peut avoir une feuille de sauvegarde par employé. Dans ce fichier, tu aurais une feuille avec les formules, laisser telle que (à garder pour pouvoir faire des modifications, si besoin) et les sauvegardes de l'année.

Chaque année, tu sauves le fichier et l'année suivante, la feuille contenant les formules sert à créer une nouvelle série.

@ plus

 

[Aloha]

Bonjour,

Entretemps j'ai trouvé une solution pour remplacer les formules par les valeurs, et pour recalculer la ligne sur laquelle on procède à une modification moyennant une Private Sub Worksheet_Change(ByVal Target As Range)
Il y a juste un petit problème: cette Private Sub se met aussi en action -et c'est logique- lorsque les données sont importées et que le contenu des cellules est donc changé!

Une question: la colonne BH est le double de la colonne BG (23-24 h.), mais toute la colonne est vide. L'as-tu mise intentionnellement?

En ce qui concerne ta proposition, veux-tu laisser le tout dans le même fichier "Base" ou dans un tout autre fichier?

Je ne comprends pas trop comment cela doit fonctionner. Il faudrait que je le voie fonctionner, ce qui impliquerait que tu construises le système, ce que je ne peux en aucun cas attendre de toi, après tout le temps et l'énergie que tu as déjà investis!

A+
Aloha

 

[CISCO]

Bonsoir

Une question: la colonne BH est le double de la colonne BG (23-24 h.), mais toute la colonne est vide. L'as-tu mise intentionnellement?

Non, c'est une erreur de ma part : Lorsque j'ai supprimé les deux colonnes ne servant pas (celles contenant le nom des jours, lundi, mardi...), j'ai fait certaines corrections, mais pas assez apparemment : Il y avait cette colonne en trop à supprimer, un nom à modifier dans le gestionnaire de noms, et toutes les formules allaient chercher une information dans une colonne trop à droite, par exemple, dans AK au lieu de AJ.

C'est corrigé dans le fichier du post 72.

@ plus

P.S : Par contre, il ne faut rien écrire dans la colonne DF à partir de la ligne 4 jusqu'en bas du tableau