Répartition de tâches sur un planning

**VincentCA** · 15/02/2021, 12h24

Bonjour à tous,

Chargé de la planification d’une équipe d’une 50aine de salariés, je souhaiterais pouvoir faire évoluer le système existant par un planning automatisé.
Malheureusement mes connaissances et mes compétences sur Excel rendent la tâche énormément complexe.
C’est pourquoi, j’aimerais pouvoir bénéficier de l’aide de ce forum pour me permettre de mieux travailler mon document.

En PJ, vous trouverez un classeur excel.
Les feuilles aa/bb et cc/dd sont des trames de planning comportant les horaires des salariés. Ces feuilles-là me sont envoyées par la secrétaire du service. Elles sont liées 2 par 2 pour permettre un affichage A3 dans le service mais concernent toujours une même semaine.
aa et bb = semaine6
cc/dd = semaine7

Chaque semaine, le classeur excel s’incrémentera de 2 feuilles supplémentaires.
1ere question : est il possible de concaténer les feuilles de telle sorte qu’elles s’affichent toutes sur une même feuille (en augmentant seulement le nombre de colonne et de ligne) ?

Sur ces trames, il existe un code couleur pour différencier les tranches horaires. Cela permet aux salariés de mieux reperer leurs horaires de travail.

La complexité du planning relève par les horaires qui peuvent varier en fonction des plages horaires / semaine, les absences dans la semaine ou la présence de RTT.
C’est pourquoi il existe du 6h00-13h00, du 6H00-14h00, 7h30-15h15, 7h30-16h15 ... Peut importe, ils agissent tous à travers le code couleur car le plus importante reste l’heure d’arrivée au poste (soit 6h, soit 7h30, soit 8h, soit 12h15 ou 13h15).
La deuxième complexité du planning relève d’horaire continu (une seule ligne/salarié) d’horaire non continu (deux lignes/salariés).

A ce jour, une de mes tâches en tant que manager :
- Inscrire manuellement sur une trame vierge telle cc/dd les postes où seront affectés les salariés en tenant compte de mes impondérables (tant de pers par poste sur telle plage horaire). Ce travail est fastidieux et répétitif.
Est-il possible de rendre cette tâche automatique en tenant compte des tranches horaires et d’une notion de répétitivité de la tache sur la semaine (certaines tâches sont plus contraignantes et pénibles).

- Afin de gérer une certaine équité sur le roulement des postes, je suivrai également le nombre de poste par semaine et par salarié (colonne V à AC).
Est-il possible de réaliser un cumulé au cours des semaines ?

Exemple attendu en feuille « exemple attendu » au mardi 16

En espérant avoir été clair.
Merci d’avance pour tout ce que le forum pourra m’apporter.
Bonne journée.
Vincent

**EricDgn** · 15/02/2021, 17h48

Bonjour,

C'est très bien d'avoir mis la feuille "résultat attendu". Il faudrait aussi avoir un exemple des feuilles telles que reçues "du secrétariat".

Il est certainement possible de fusionner les feuilles pour n'en avoir que 1 par semaine.

Pour ce qui est des couleurs, le plus simple est d'utiliser la "mise en forme conditionnelle".
Ruban / Accueil / Styles / Mise en forme conditionnelle / Gérer les règles / Nouvelle règle / Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué ...
Exemple de formule pour poste commençant à 6h: =(GAUCHE(B6;2)="6."), pour poste commençant à 12h: =(GAUCHE(B6;3)="12.")Procédure à répéter/adapter pour toutes les conditions possibles.

Pour ce qui est de l'encodage, il faudrait regarder s'il n'est pas possible d'utiliser la fonction "validation de données".

Il y a un tas de tutoriels qui expliquent ces fonctions "mise en forme conditionnelle" et "validation de données".

Il faut absolument supprimer toutes les cellules fusionnées.

Ce n'est pas une bonne idée d'utiliser le point pour indiquer les heures. Il est vraiment préférable d'utiliser des : qui est le marqueur conventionnel, donc 12:00 , 12:15 , 12:30.

De ce que je vois, il serait préférable d'avoir 2 lignes par personne, la 2e étant laissée vide la plupart du temps. Cela simplifierait les comptages nécessaires pour la "répartition des tâches" sur la semaine.

Cordialement.

**VincentCA** · 15/02/2021, 21h19

Bonjour,

C'est très bien d'avoir mis la feuille "résultat attendu". Il faudrait aussi avoir un exemple des feuilles telles que reçues "du secrétariat". les feuilles fournies par le secrétariat sont identiques à cc/dd sans l'exemple attendu du mardi 16. En fonction de l'aide que vous tous m'apporterez, je verrai avec le secrétariat comme améliorer la base de travail pour qu'elle satisfasse davantage au système de codage ou autre...

Il est certainement possible de fusionner les feuilles pour n'en avoir que 1 par semaine ça serait déjà super.

Pour ce qui est des couleurs, le plus simple est d'utiliser la "mise en forme conditionnelle".
Ruban / Accueil / Styles / Mise en forme conditionnelle / Gérer les règles / Nouvelle règle / Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué ...
Exemple de formule pour poste commençant à 6h: =(GAUCHE(B6;2)="6."), pour poste commençant à 12h: =(GAUCHE(B6;3)="12.")Procédure à répéter/adapter pour toutes les conditions possibles.c'est déjà ce qui est fait par la secrétaire. Quand je parlais de couleur, je souhaitais que le code couleur de la tranche puisse être un indicateur pour le positionnement automatique des postes

Pour ce qui est de l'encodage, il faudrait regarder s'il n'est pas possible d'utiliser la fonction "validation de données". avez vous une idée de ce que cela apporterait ?

Il y a un tas de tutoriels qui expliquent ces fonctions "mise en forme conditionnelle" et "validation de données".

Il faut absolument supprimer toutes les cellules fusionnées j'en ai déjà parlé a la secrétaire, en journée non continu code orange, il y aura 2 lignes. Puis pour les tranches horaires, actuellement fusion de 2 cellules, une premiere cellule avec la tranche horaire et le fond de couleur et une deuxième cellule avec le poste.

Ce n'est pas une bonne idée d'utiliser le point pour indiquer les heures. Il est vraiment préférable d'utiliser des : qui est le marqueur conventionnel, donc 12:00 , 12:15 , 12:30.Ok

De ce que je vois, il serait préférable d'avoir 2 lignes par personne, la 2e étant laissée vide la plupart du temps. Cela simplifierait les comptages nécessaires pour la "répartition des tâches" sur la semaine.ok

Cordialement. MERCI

**VincentCA** · 16/02/2021, 10h48

Sur la nouvelle PJ, j'ai repris en planning 1, une version telle que la secrétaire me la transmet actuellement.
En planning2, j'ai apporté manuellement quelques modifications visant à rendre les horaires plus conventionnels par le remplacement d'un . par un :
J'ai également défusionné toutes les cellules ; cellule 1 de chaque colonne pour l'horaire, la cellule 2 pour l'affectation automatique du poste. Si horaire non continu, couleur orange, alors deux lignes pour un salarié (une ligne "matin" et une ligne après midi".

Merci d'avance,
Cordialement.

**EricDgn** · 16/02/2021, 12h19

Bonjour,

Je proposerais de commencer par améliorer ce que fait le secrétariat.

Il pourrait déjà mettre tous les noms sur une seule feuille et utiliser pour la "mise en page" la fonction "Lignes à répéter en haut" pour que les 5 premières lignes soient automatiquement reprises à l'impression.

Par ailleurs, pourquoi 3 colonnes pour le samedi ? et même pourquoi 2 colonnes pour chaque jour ? Théoriquement cela devrait être plus simple pour lui s'il n'y avait qu'une colonne par journée, mais c'est peut-être obtenu via une autre application.

Enfin pourquoi une si grande taille de caractères ... qui oblige ensuite à mettre le zoom à 30%.

Je n'ai pas l'impression que la mise en forme conditionnelle soit utilisée, aucune règle n'étant présente.

En attaché, une proposition de mise en page pour ce qui concerne la taille des caractères et la mise en page (et pas les colonnes et la mise ne forme conditionnelle).

Pour la suite, pour vous permettre d'ajouter les postes de travail, le plus simple sera sans doute d'utiliser une macro pour passer de la présentation secrétariat à votre présentation.

Cordialement.

**VincentCA** · 17/02/2021, 10h29

Bonjour, une nouvelle version qui prend en compte la défusion des cellules et 2 colonnes par jour : 1 pour l'horaire, la seconde pour l'affectation du poste en auto; MERCI

Bonjour,

Je proposerais de commencer par améliorer ce que fait le secrétariat. En attaché, une nouvelle version qui est la trame d'extraction de la secrétaire sur laquelle j'ai juste ajouté des règles conditionnelles.

Il pourrait déjà mettre tous les noms sur une seule feuille et utiliser pour la "mise en page" la fonction "Lignes à répéter en haut" pour que les 5 premières lignes soient automatiquement reprises à l'impression.

Par ailleurs, pourquoi 3 colonnes pour le samedi ? et même pourquoi 2 colonnes pour chaque jour ? Théoriquement cela devrait être plus simple pour lui s'il n'y avait qu'une colonne par journée, mais c'est peut-être obtenu via une autre application Oui il s'agit d'une extraction réalisée à partir d'un logiciel de gestion du temps et de planification.

Enfin pourquoi une si grande taille de caractères ... qui oblige ensuite à mettre le zoom à 30%. je suis passé à 100% en taille 11

Je n'ai pas l'impression que la mise en forme conditionnelle soit utilisée, aucune règle n'étant présente.

En attaché, une proposition de mise en page pour ce qui concerne la taille des caractères et la mise en page (et pas les colonnes et la mise ne forme conditionnelle).

Pour la suite, pour vous permettre d'ajouter les postes de travail, le plus simple sera sans doute d'utiliser une macro pour passer de la présentation secrétariat à votre présentation.

Cordialement.

**EricDgn** · 17/02/2021, 15h46

Bonjour,

Donc si je comprends bien, ce que vous recevez du secrétariat contient déjà tous les horaires, cela sur 1 ou 2 lignes selon les cas, et avec toujours avec 2 colonnes par jour, le tout sans coloration ni titre (lignes 1 à 6 vides).

Voici toujours un début. La macro Formatage() permet de préparer l'affichage et la mise en page pour l'impression sur des feuilles A3. Pour ce qui est des formats conditionnels, il faudrait voir sur la feuille Fmt si pour 12:15-20:15 et 12:45-15:30 c'est vraiment cela qui est demandé.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
Option Explicit
 
Sub Formatage()
    '--- présentation générale feuille
    Cells.RowHeight = 33                        '--- hauteurs lignes
    Columns("A:A").ColumnWidth = 14             '--- largeur colonne A
    Columns("B:AF").ColumnWidth = 10            '--- largeurs colonnes B à AF
    Cells.VerticalAlignment = xlCenter          '--- centrage vertical
    Cells.HorizontalAlignment = xlCenter        '--- centrage horizontal
    Columns("A:A").HorizontalAlignment = xlLeft '--- colonne A alignée à gauche
    Range("A1:AF8").Font.Bold = True            '--- plage des titres en gras
    ActiveWindow.Zoom = 80                      '--- zoom 80%
    '--- Mise en page feuille A3
    ActiveSheet.Cells.PageBreak = xlPageBreakNone
    ActiveSheet.HPageBreaks.Add Before:=Range("A" & kR_Nom26)   '--- saut de page au 26e nom
    With ActiveSheet.PageSetup
        .PaperSize = xlPaperA3      '--- feuille A3
        .PrintTitleRows = "$1:$8"   '--- lignes de titre répétées
        .LeftMargin = Application.CentimetersToPoints(1)
        .RightMargin = Application.CentimetersToPoints(1)
        .TopMargin = Application.CentimetersToPoints(1)
        .BottomMargin = Application.CentimetersToPoints(1)
        .HeaderMargin = Application.CentimetersToPoints(1)
        .FooterMargin = Application.CentimetersToPoints(1)
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlPortrait
        .Zoom = 65                  '--- pour avoir semaine entière sur largeur 1 page
    End With
End Sub
 
Private Function kR_Nom26() As Long
    '--- cherche la ligne où se trouve le 26e nom
    '--- (utilisé pour y mettre un saut de page)
    Dim kR As Long, n As Integer, sNom As String
    kR = 9
    sNom = Range("A" & kR)
    n = 1
    While n < 26 And sNom <> ""
        kR = kR + 1
        If Range("A" & kR) <> sNom Then
            n = n + 1
            sNom = Range("A" & kR)
        End If
    Wend
    kR_Nom26 = kR
End Function

Cordialement.

**VincentCA** · 18/02/2021, 12h25

Envoyé par EricDgn

Bonjour, Bonjour

Donc si je comprends bien, ce que vous recevez du secrétariat contient déjà tous les horaires, cela sur 1 ou 2 lignes selon les cas, et avec toujours avec 2 colonnes par jour, le tout sans coloration ni titre (lignes 1 à 6 vides). J'ai ajouté dans le classeur, une feuille intitulée trame de base secrétariat telle que je la reçois.

Voici toujours un début. La macro Formatage() permet de préparer l'affichage et la mise en page pour l'impression sur des feuilles A3. Pour ce qui est des formats conditionnels, il faudrait voir sur la feuille Fmt si pour 12:15-20:15 et 12:45-15:30 c'est vraiment cela qui est demandé j'ai corrigé car il manquait 2 conditionnelles. Je pense que nous avons fais le tour des multiples horaires du service... .
Je vais tester pour l'impression pour voir si cela fonctionne mais c'est déjà génial de votre part d'avoir pris le temps de réaliser cette macro. MERCI

La colonne G de la feuille Fmt va vous servir pour la répartition des tâches ? En feuille fut, j'i refais un tableau, j'espère plus clair pour l'affectation des tâches. En première colonne l'intitulé, en deuxième les plages pouvant faire la tâche, en troisième le nombre de personne/j devant réaliser la tâche et en 4ième colonne la fréquence que peut occuper une personne / semaine sur le même poste

UN GRAND MERCI POUR VOTRE AIDE

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
Option Explicit
 
Sub Formatage()
    '--- présentation générale feuille
    Cells.RowHeight = 33                        '--- hauteurs lignes
    Columns("A:A").ColumnWidth = 14             '--- largeur colonne A
    Columns("B:AF").ColumnWidth = 10            '--- largeurs colonnes B à AF
    Cells.VerticalAlignment = xlCenter          '--- centrage vertical
    Cells.HorizontalAlignment = xlCenter        '--- centrage horizontal
    Columns("A:A").HorizontalAlignment = xlLeft '--- colonne A alignée à gauche
    Range("A1:AF8").Font.Bold = True            '--- plage des titres en gras
    ActiveWindow.Zoom = 80                      '--- zoom 80%
    '--- Mise en page feuille A3
    ActiveSheet.Cells.PageBreak = xlPageBreakNone
    ActiveSheet.HPageBreaks.Add Before:=Range("A" & kR_Nom26)   '--- saut de page au 26e nom
    With ActiveSheet.PageSetup
        .PaperSize = xlPaperA3      '--- feuille A3
        .PrintTitleRows = "$1:$8"   '--- lignes de titre répétées
        .LeftMargin = Application.CentimetersToPoints(1)
        .RightMargin = Application.CentimetersToPoints(1)
        .TopMargin = Application.CentimetersToPoints(1)
        .BottomMargin = Application.CentimetersToPoints(1)
        .HeaderMargin = Application.CentimetersToPoints(1)
        .FooterMargin = Application.CentimetersToPoints(1)
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlPortrait
        .Zoom = 65                  '--- pour avoir semaine entière sur largeur 1 page
    End With
End Sub
 
Private Function kR_Nom26() As Long
    '--- cherche la ligne où se trouve le 26e nom
    '--- (utilisé pour y mettre un saut de page)
    Dim kR As Long, n As Integer, sNom As String
    kR = 9
    sNom = Range("A" & kR)
    n = 1
    While n < 26 And sNom <> ""
        kR = kR + 1
        If Range("A" & kR) <> sNom Then
            n = n + 1
            sNom = Range("A" & kR)
        End If
    Wend
    kR_Nom26 = kR
End Function

Cordialement.

**EricDgn** · 18/02/2021, 14h05

Bonjour,

Une question: pourquoi la semaine est-elle précédée du samedi et suivie du dimanche? Comment faites vous pour raccorder les données déjà encodées lors du planning précédent? Exemple: les 20 et 21/2 ont déjà été encodés lors du planning de la semaine 07. Il faut les ré-encoder lors du planning de la semaine 08 ?

Cordialement.

**VincentCA** · 18/02/2021, 16h26

Très bonne question :
Il est nécessaire seulement d'encoder 20/21 et pas 27/28 pour l'exemple en cours soit le week end avant et pas celui après.
Nous imprimons la trame suivante le jeudi ou samedi de la semaine en cours.
Les salariés aiment bien savoir les horaires qu'ils ont le week end qui vient c'est pour cela que nous mettons les 2 week-ends en visuel.

Merci d'avance

**EricDgn** · 20/02/2021, 23h40

Bonjour,

Une façon de faire présentée en attaché. Elle ne règle pas tout, mais presque, et pourra toujours vous donner des idées.

Bonne continuation.

**VincentCA** · 21/02/2021, 11h17

Bonjour Monsieur,

Tout d'abord un grand merci pour le travail et le temps alloué à cette discussion.
Vous m'êtes d'une grande aide et je ne peux que vous remercier très chaleureusement.

Pourriez-vous m'expliquer s'il vous plait la fonction charger des données. Ce module là est sensé ouvrir mon explorateur windows pour que je sélectionne le planning secrétariat?
Ce planning secrétariat doit-il avoir un nom particulier? Doit il être intégré sur une feuille du classeur? Il doit être sous le format de la trame secrétariat du planning1 xlsm ?
En PJ, une capture d'écran (capture1) d'un code erreur lorsque je souhaite charger des données avec NON sur l'enregistrement.
En PJ, une capture d'écran (capture2) d'un code erreur lorsque je souhaite charger des données avec OUI sur l'enregistrement.

Merci de votre aide,
Cordialement.

**EricDgn** · 21/02/2021, 15h29

Bonjour,

L'erreur doit provenir du fait que vous êtes sur Mac, ce que je ne peux pas tester. Ci-dessous une version qui résout peut-être le problème. S'il y a encore une erreur, notez le n° de l'erreur, cliquez sur le bouton "Déboguer" et transmettez l'image de la ligne mise en surbrillance dans le code et le n° de l'erreur.

Le fichier du secrétariat est présumé être présenté comme dans l'image attachée.

Cordialement.

**VincentCA** · 21/02/2021, 16h32

Merci,

Voici deux nouvelles capture d'écran...

Merci d'avance,
Cordialement.

**EricDgn** · 21/02/2021, 17h26

Bonjour,

Ajouter cette ligne juste en-dessous de Function ...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Function BrowseMac(mypath As String) As String
    Dim sMacScript As String

Cordialement.

**VincentCA** · 21/02/2021, 18h08

On va y arriver, pas facile. Maintenant c'est l'accès au répertoire qui semble poser quelques problèmes.

MERCI

**EricDgn** · 21/02/2021, 18h37

Tapez ceci dans la fenêtre d'exécution

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

? BrowseMac(ThisWorkbook.Path)

Qu'est-ce que cela donne ?

**VincentCA** · 21/02/2021, 18h49

-43

**EricDgn** · 21/02/2021, 19h02

Bon, je n'ai plus vraiment le temps de regarder cela ce soir.
En attendant, il devrait être possible de passer outre et de mettre le chemin du fichier "à la main" dans cette ligne:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
    #If Mac Then
        Workbooks.Open "... chemin/nom du fichier "

Cordialement.

**VincentCA** · 21/02/2021, 19h20

Il m'arrive très souvent d'être connecté sur mon PC mais il est bien évident que lorsque je poste une discussion, je n'entends pas une réponse dans la minute.
L'aide qui m'est apportée est déjà largement précieuse et très satisfaisante.
Si je me suis montré oppressant, veuillez m'en excuser.
Demain je regarderai depuis le boulot où je travaille en excel sur Windows (le mac est à mon domicile mais parfois en télétravail)

En vous souhaitant une agréable soirée,
Merci encore

Répartition de tâches sur un planning

Macros et VBA Excel

Discussions similaires

Partager

Partager