XL 2010 Affectation de personnels sur planning

[newheel]

Bonjour et merci à tous les développeurs pour leur contribution à faire évoluer nos projets.

Je suis en limite de compétence sur un projet dont je vous soumets mon fichier: il s'agit d'un planning d'affectation de personnels à des postes de travail qui doivent apparaître dans un planning qui leur est remis toutes des semaines.

Le dossier comprend :

1-le fichier planningSem : 2 tableaux constitués de la semaine actuelle et des postes à pourvoir par jour ( les mêmes pour les 2 HUITS) dans lesquels les prénoms des personnes doivent apparaître dans le huit auquel ils appartiennent (HUIT 1 ou HUIT 2)


2- le planning : il y a la liste des personnels, l'équipe du huit auquel ils appartiennent pour a semaine ( A PREPA ou M PREPA et le calendrier de l'année en cours. Les personnels sont affectés à un poste ( A, G, T, CL etc...) par jour.

3 - un fichier listes pour gérer les paramètres et relations des tableaux permettant les rechercheV()

4- un fichier rythme huit1-huit2 pour lier les 2 huits à leur plages horaires ( M PREPA ou A PREPA)

Je voudrais que lorsque j'affecte pour par exemple le jeudi 16 juin prémonop1 à A dans planning, prénomop1 apparaisse dans planningSem sous le libellé correspondant à A ( ici A = Animateur), si prénomop2 affecté à G, le prénomop2 apparaisse sous GPAO dans le tableau à imprimer correspond à leur HUIT affecté à la page horaire paramétrée.

En vous remerciant d'avance pour votre contribution sur ce projet!

Newheel

 

[vgendron]

Re : Affectation de personnels sur planning

Hello

Voir test en PJ

en meme temps que je poste, je viens de voir que il y a des erreurs #Ref, provoquées par le fait que sur une meme journée, (le vendredi 17) tu as DEUX personnes occupant le meme poste (CUB)

1) si c'est quelque chose de possible alors ma solution ne fonctionnera pas
2) s'il s'agit d'une erreur de saisie et qu'un poste ne peut etre occupé qu'une seule fois. alors c'est bon
3) j'ai modifié la syntaxe des cellules B3 et L3: j'ai supprimé les espaces entre HUIT et 1 (ou2)

note: j'ai créé quelques zones nommées: plus facile pour les formules
j'ai modifié également tes formules (ligne4) pour avoir la date
en fait, à partir de ton premier jour (D1) je fais juste un +1 pour avoir les autres jours de la semaine
ET j'ai appliqué un format personalisé pour garder l'affichage d'origine
idem dans le planning

colonne A, j'ai ajouté l'info de correspondance entre la fonction et l'affectation:
cette info, est utilisée dans la GRosse formule

le principe
SI(ET(SOMMEPROD((TabData=$A5)*(AnneeComplete=C$4)*(LIGNE(TabData)-3))<>0;INDEX(ListeEquipes;SOMMEPROD((TabData=$A5)*(AnneeComplete=C$4)*(LIGNE(TabData)-3)))=$B$3);INDEX(ListeNoms;SOMMEPROD((TabData=$A5)*(AnneeComplete=C$4)*(LIGNE(TabData)-3)));"")

dans le tableau TabData, je recherche la case qui répond aux critères suivants;
contient l'affectation cherchée (colonneA)
le jour de cette affectation correspond au jour du semainier
je récupère le numéro de ligne de cette case

je vais voir si le huit correspond au Huit du semainier

et si toutes ces conditions sont ok, je vais chercher le nom de l'operateur situé à la même ligne


j'ai été contraint de rajouter une condition sur "si numéro de ligne<>0" car lorsqu'aucune case ne correspond, Sommeprod renvoie 0.
et hélas. index(,0) retourne une valeur.. alors qu'on en veut pas justement..


je suis quasi sur qu'il y a plus "simple";. mais la. comme ca. je vois pas ;-)

 

[Lolote83]

Re : Affectation de personnels sur planning

Salut NEWHEEL,
Un partie du contrat seulement est remplie.
Dans ton tableau SEMPLANNING2 (copie de SemPlanning), je n'arrive pas pour le moment a faire le distinguo entre M PREPA1 et A PREPA1 (chose que tu demandes par ailleurs).
Par contre pour que cette formule fonctionne, il a fallu reprendre toutes tes dates car elles n'étaient pas au format date.
Donc dans l'onglet PLANNING2 (copie de planning) , j'ai rajouté en cellule G1 (2016) soit l'année en cours et ainsi, toutes les dates de ton fichier seront actualisée automatiquement si tu changes cette valeur
Idem pour l'onglet SEMPLANNING2 pour les cellules C4 à I4.
Voici donc un début de piste, d'autres formulistes plus aguerris que moi trouveront certainement l'astuce pour justement différencier M PREPA1 et A PREPA1.

Salut VGendron, télescopage, mais certainement formule différente.
@+ Lolote83

 

[vgendron]

Re : Affectation de personnels sur planning

Hello Lolotte

je crois qu'on a eu la meme démarche. mais moi avec des sommeprod

je me permet d'apporter une modif à ta formule pour justement prendre en compte les MPrepa1 (ou Huit 1 2)
en C5
SIERREUR(SI(INDEX(planning2!$D$4:$D$31;EQUIV($A5;INDEX(planning2!$H$4:$NJ$31;;EQUIV(C$4;planning2!$H$3:$NJ$3;0));0))=Semplanning2!$B$3;SIERREUR(INDEX(planning2!$B$4:$B$31;EQUIV($A5;INDEX(planning2!$H$4:$NJ$31;;EQUIV(C$4;planning2!$H$3:$NJ$3;0));0));0);"");"")

ce fameux test alourdi considérablement la formule.. index ou sommeprod. meme combat ;-)

 

[newheel]

Re : Affectation de personnels sur planning

Hello Lolotte

en meme temps que je poste, je viens de voir que il y a des erreurs #Ref, provoquées par le fait que sur une meme journée, (le vendredi 17) tu as DEUX personnes occupant le meme poste (CUB)

1) si c'est quelque chose de possible alors ma solution ne fonctionnera pas
2) s'il s'agit d'une erreur de saisie et qu'un poste ne peut etre occupé qu'une seule fois. alors c'est bon

Bonjour Vgendron et Lolotte,

merci de vos contributions !!

Non effectivement 1 seule affectation possible par 8 sur chaque poste dans chaque HUIT - chaque opérateur étant positionné sur 1 seul poste dans le planning par HUIT; par contre il y a forcément des doublons si pas de distinguo entre M PREPA et A PREPA - votre écueil pour l'instant.

Merci en tout cas de faire avancer mon projet!

 

[newheel]

Re : Affectation de personnels sur planning

Et est-ce que pour contourner le problème il serait plus facile de créer 2 boutons macros GENERER PLANNING HUIT 1 et GENERER PLANNING HUIT 2 en fonction du numéro de semaine type

entrer N° de sem : 24
GENERER HUIT1 renverrait à un onglet HUIT1 - M PREPA dans un onglet nommé S24 HUIT1 MPREPA
GENERER HUIT2 renverrait à HUIT2 - A PREPA dans un onglet nommé S24 HUIT2 A PREPA

ect pour S25, S26 ..


possible et faisable?

merci.

 

[Lolote83]

Re : Affectation de personnels sur planning

Salut Newheel, VGendron,

Voici donc une nouvelle version, basée sur la précédente, mais prenant en compte (contrairement à la version 1) les APREPA1 et MPREPA1.
VGendron, je n'ai pas encore pris la peine de regarder ta formule, je le ferrais prochainement

Concernant cette nouvelle version, elle se base sur une concaténation avec un petit tableau intermédiaire situé en fin de tableau (colonne NL). Utilisation de la fonction decaler

Concernant ta nouvelle demande, je regarde

Et si pour il était créer 2 boutons macros GENERER PLANNING HUIT 1 et GENERER PLANNING HUIT 2 en fonction du numéro de semaine type

entrer N° de sem : 24
GENERER HUIT1 renverrait à un onglet HUIT1 est M PREPA dans un onglet nommé S24 HUIT1 MPREPA
GENERER HUIT2 renverrait à HUIT2 est A PREPA dans un ongmet nommé S24 HUIT2 A PREPA

ect pour S25, S26 ..

@+ Lolote83

 

[newheel]

Re : Affectation de personnels sur planning

Lolote, Vgendron,

merci pour le travail réalisé, je peux faire ma plannif pour cette semaine en gagant déjà pas mal de temps. Formidable!!

@Lolote; merci d'avoir cogité pendant le WE !!! L'idée des 2 macros était là pour voir à faire autrement, en même temps s'il devient possible de créer un onglet nommé par semaine type S24, S25, etc.., le problème de A PREPA et M PREPA étant résolu (encore merci) un onglet pour les 2 HUITS suffirait du coup.

merci

 

[vgendron]

Re : Affectation de personnels sur planning

Hello

comme j'avais commencé le code semaine dernière, je le poste. meme si plus besoin

Sub planning()
Set Huit1 = Sheets("Semplanning").Range("B4:I16")
Set Huit2 = Sheets("Semplanning").Range("L4:S16")


For Each jour In Huit1.Rows(1).Cells
    If Not jour Like ("SEM*") Then
        Set c = Range("AnneeComplete").Find(jour)
        If Not c Is Nothing Then
            colonne = c.Column - 7
            For Each fonction In Huit1.Columns(1).Cells
                If Not fonction Like ("SEM*") Then
                    'recherche de l'affectation correspondant à la fonction en première colonne
                    affectation = Range("ListAffectations").Rows(WorksheetFunction.Match(fonction, Range("ListFonctions"), 0))
                    Set ici = Range("TabData").Columns(colonne).Find(affectation)
                    If Not ici Is Nothing Then
                        If Range("ListeEquipes").Rows(ici.Row - 3) = "HUIT1" Then
                            Final = Range("ListeNoms").Rows(ici.Row - 3)
                            Huit1.Cells(fonction.Row - 3, jour.Column - 1) = Final
                        End If
                    End If
                End If
            Next fonction
        End If
    End If
Next jour

For Each jour In Huit2.Rows(1).Cells
    If Not jour Like ("SEM*") Then
        Set c = Range("AnneeComplete").Find(jour)
        If Not c Is Nothing Then
            colonne = c.Column - 7
            For Each fonction In Huit2.Columns(1).Cells
                If Not fonction Like ("SEM*") Then
                    'recherche de l'affectation correspondant à la fonction en première colonne
                    affectation = Range("ListAffectations").Rows(WorksheetFunction.Match(fonction, Range("ListFonctions"), 0))
                    Set ici = Range("TabData").Columns(colonne).Find(affectation, lookat:=xlWhole)
                    If Not ici Is Nothing Then
                        If Range("ListeEquipes").Rows(ici.Row - 3) = "HUIT2" Then
                            Final = Range("ListeNoms").Rows(ici.Row - 3)
                            Huit2.Cells(fonction.Row - 3, jour.Column - 11) = Final
                        End If
                    End If
                End If
            Next fonction
        End If
    End If
Next jour
End Sub

pour l'histoire des doublons..
si pour Huit1, il y a deux personnes affectées à la meme fonction, une seule personne (la première) sera remontée.
c'est pour ca que dans la PJ pour le 17 juin, il n'y a que 6 personnes remontées dans le Huit2 alors que 9sont renseignées
dans ces 9: un R, deux Sc2 et Deux Cub --> 9- 3 = 6

dans la PJ, j'ai forcé la date du jour au 13/06/2016 (cellule D1)

 

[newheel]

Re : Affectation de personnels sur planning

Vgrendron, salut,

Merci pour la macro pour remplir les Huits, les explications vulgarisées et la méthode proposée.
Merci pour cet effort pendant le we en plus !! C'est pratique sous forme de bouton ! Et la gestion des doublons permet d'éviter les erreurs, super pour éviter de faire des photocopies par contre il me semble que le problème d'affectation A PREPA M PREPA demeure ..arf après test de la macro.

 

[vgendron]

Re : Affectation de personnels sur planning

Hello

le problème d'affectation A PREPA M PREPA demeure

je viens de me rendre compte que oui. il demeure parce que pas traité..
je croyais que A Prepa, c'est forcément du Huit 1
et que M Prepa, c'est forcément du Huit2

donc du coup. la question:
nomop 27 en M Prepa - Huit2 va bien dans le tableau Huit 2
mais nomop28 en MPrepa - huit1: il va ou??

meme question posée différemment..
entre Huit et Prepa. lequel décide vers quel tableau envoyer..?

 

[newheel]

Re : Affectation de personnels sur planning

Hello



je viens de me rendre compte que oui. il demeure parce que pas traité..
je croyais que A Prepa, c'est forcément du Huit 1
et que M Prepa, c'est forcément du Huit2

donc du coup. la question:
nomop 27 en M Prepa - Huit2 va bien dans le tableau Huit 2
mais nomop28 en MPrepa - huit1: il va ou??

meme question posée différemment..
entre Huit et Prepa. lequel décide vers quel tableau envoyer..?

Salut Vgendron,

la réponse est dans l'onglet rythme huit1_huit2 :

chaque HUIT alterne sur la matin (M PREPA) et l'après-midi (A PREPA) de façon hebdomadaire

S1 : HUIT1 M PREPA HUIT2 A PREPA
S2: HUIT1 A PREPA HUIT2 M PREPA etc...

 

[vgendron]

Re : Affectation de personnels sur planning

une question..

dans ton planning, tu as le numéro de semaine en cours (calculé selon la date d'aujourdhui en A1)
En admettant que tu remplisses à la main la colonne D: avec Huit1 ou Huit2, POURQUOI la colonne E ne correspond-t-elle pas forcément? la formule est =RECHERCHEV($A$2;rythme_huits;2;0) ou RECHERCHEV($A$2;rythme_huits;3;0)
le 2 ou 3 : comment est il défini?
ne devrait il pas suivre le contenu de la colonne D ?: si D =Huit 1, alors2 sinon 3

ce qui ferait automatiquement correspondre le bon huit au M ou A Prepa selon le rythme.
ex: ligne 31: nomop28 est affecté au huit 1, et pourtant. c'est du MPrepa1 en colonne E alors que le rythme dit pour la semaine 25, Huit1 = A Prepa

 

[newheel]

Re : Affectation de personnels sur planning

une question..

dans ton planning, tu as le numéro de semaine en cours (calculé selon la date d'aujourdhui en A1)
En admettant que tu remplisses à la main la colonne D: avec Huit1 ou Huit2, POURQUOI la colonne E ne correspond-t-elle pas forcément? la formule est =RECHERCHEV($A$2;rythme_huits;2;0) ou RECHERCHEV($A$2;rythme_huits;3;0)
le 2 ou 3 : comment est il défini?
ne devrait il pas suivre le contenu de la colonne D ?: si D =Huit 1, alors2 sinon 3

ce qui ferait automatiquement correspondre le bon huit au M ou A Prepa selon le rythme.
ex: ligne 31: nomop28 est affecté au huit 1, et pourtant. c'est du MPrepa1 en colonne E alors que le rythme dit pour la semaine 25, Huit1 = A Prepa

Bonjour Vgendron,
Merci de l'avoir vu, oui j'ai un risque d'erreur!!! Arf car j'affecte manuellement HUIT1 et HUIT2 alors que M PREPA et A PREPA sont liés à la date de la semaine, ce qui devrait être le cas entre cas entre HUIT1 et HUIT2 et A PREPA et M PREPA : si je change un opérateur de HUIT, ce qui pourrait arriver le rythme ne change pas.
Je veux bien un coup de main là-dessus stp.!
Du coup , serait-il aussi possible d'attribuer HUIT1 et HUIT2 à 2 listes d'opérateurs (aujourd'hui j'affecte les HUITS à chaud sur la liste avc le risque d'erreur que tu as constaté.

Merci

 

[vgendron]

Re : Affectation de personnels sur planning

Hello

voir PJ pour début de réponse:

1) dans le planning, j'ai mis une formule en colonne E qui va chercher le prepa A ou M selon le HUIT de la colonne D
--> le cas qui posait problème est réglé. enfin. je pense

2) en attendant de savoir ce que tu préfères:
soit affecter les Huit à chaque personne à la main dans la colonne D du planning.. auquel la formule en E suffit.
soit tu crées deux listes d'opérateurs A et B (onglet Rythme: tableau colonne G et H)
et chaque liste est affectée à un huit (tableau colonne J et K)

la formule coloone F pourra donc etre utilisée dans l'onglet planning pour affecter le Huit.

du coup la démarche complète dans l'ordre:
Colonne E: liste des opérateurs disponibles..
en colonne G, tu sélectionnes les opérateurs que tu veux affecter à la liste A
en colonne H; idem pour la liste B
Note: chaque fois qu'un opérateur est sélectionné, il apparait en rouge dans la liste colonne E (pour aider visuellement à savoir qui est déjà saisi)-->attention: n'empêche pas de sélectionner deux fois le meme opérateur.. mais bon. faut en faire exprès ;-)

ensuite: en K4, tu sélectionnes le Huit que tu veux affecter à la liste A--> la liste B s'affecte ensuite toute seule

ainsi.. la formule F attribue directement le bon huit à l'opérateur.. cette formule pourra etre recopiée dans le planning..


voila.. à toute à l'heure ;-)