Discussion :

[tacleur]

Bonjour à tous,
Après avoir vu de nombreux tutos, passé des soirées entières sur le problème, je m'en remet à vous.
Je souhaite utiliser mon planning pour créer un tableau avec un menu déroulant en haut qui donnerait la date et les cases se remplirait automatiquement avec le nom de l'employé dans la case du bon poste.
Si l'employé est absent, il n'apparaît pas dans les cases. (voir tableau)

Voici mon tableau test :



Vous pouvez déjà remarqué que le résultat ne correspond pas à ma recherche.
A quoi est ce dû ?

Avez vous une solution plus simple pour faire rentrer les noms des employés dans les bonnes cases ?

Autre question (bonus ), je souhaite a terme mettre un menu déroulant pour le choix de la date et qu'après tout se remplisse automatiquement.
Est ce possible ? (car la fonction SI n'a pas le même format que les cases date standard)

Merci infiniment pour l'aide que vous pouvez m'apportez.
Je ne trouve en effet aucune solution,

Excellente soirée à vous,

[Philippe Tulliez]

Bonjour,
Tu travailles à l'envers
La recherche doit se faire là où tu places la grille présentée comme un agenda et la liste des données doit avoir une colonne avec un identifiant unique reprenant la date, éventuellement l'heure de de début, le nom de la personne, sa tâche, etc.
et pour l'agenda, on prévoit une liste déroulante avec une date de début et on crée des jours ou semaine ou mois glissant à l'aide de la fonction DATE

[ARTURO83]

Bonjour,

Vous pouvez toujours essayer ceci, pas sûr que ce soit plus simple à comprendre.
Formule utilisée en B3 (formule matricielle à valider avec CTRL + SHIFT + ENTREE) et à tirer vers le bas

Code :

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

=SIERREUR(INDIRECT("A"&EQUIV($A17;GAUCHE(INDIRECT(GAUCHE(ADRESSE(1;EQUIV($B$16;$3:$3;0);4);TROUVE(1;ADRESSE(1;EQUIV($B$16;$3:$3;0);4))-1)&"1:"&GAUCHE(ADRESSE(1;EQUIV($B$16;$3:$3;0);4);TROUVE(1;ADRESSE(1;EQUIV($B$16;$3:$3;0);4))-1)&"8");7);0));"")

image du résultat
Pièce jointe 540274

Le fichier en exemple
Pièce jointe 540278

Reste à créer une liste déroulante pour les dates

Cdlt

[Pierre Dumas]

Bonjour

Je vais vous proposer la solution que j'adopterai pour moi

Tout d'abord, par rapport à votre point de départ, j'ai (très) légèrement changé la saisie et adopté les règles suivantes :
Les dates commencent en B3 et se continuent sans arrêt jusqu'à la fin du mois sur la ligne 3
Dans la saisie à partir de la cellule B4, être très rigoureux. Toutes les cellules doivent commencer par "Poste " suivi de la lettre (et non "Poste 1" comme sur l'image)
En C16, il ne faut écrire QUE la date (et pas le mot "le" devant). De plus la date doit être au format date (et non "03/03/03" comme sur l'image)
De B17 à B20, il ne faut écrire que "Poste " suivi de la lettre (pas le ":" comme sur l'image)

Une fois ces règles bien suivies, on peut envisager la formule suivante en C17 :=INDEX($A$4:$A$8;EQUIV(B17&"*";DECALER($A$4;0;JOUR($C$16);5;1);0))
Je n'utilise pas la fonction RECHERCHE, mais plutôt le couple INDEX et EQUIV qui offrent plus de possibilités et de souplesse
En fait, on va afficher de la A4 à la A8, pour l'équivalent de la B17 suivie d'une étoile que l'on trouve dans la plage qui correspond à la date. Cette dernière partie est un peu plus délicate, car elle fait appel à la fonction DECALER. En effet, on ne va pas toujours chercher de D4 en D8 (pour le 3 mars). Mais parfois en B4:B8 (pour le 1er mars) ou en E4:E8 (pour le 4 mars). Il faut donc, en partant de la A4, se décaler de 0 lignes, d'un nombre de colonnes qui correspond au numéro de jour du mois (c'est la partie JOUR($C$16) ; donc 1 pour le 1er mars et 3 pour le 3 mars), sur une hauteur de 5 lignes et une largeur de 0 colonnes.

Le résultat renvoie la bonne info si un résultat est trouvé et renvoie "#N/A" s'il n'y a pas de résultat.
Et voici en image ce que je viens d'essayer d'expliquer en texte :


Enfin, concernant le menu pour les dates, il faut rester en C16, aller dans Données / Outils de données / Validation des données / Validation des données / dans le choix "Autoriser", sélectionner Liste et dans le choix "Source", sélectionner toutes les dates sur la ligne 3 (c'est à dire de B3 à E3 au moins) / OK

En espérant que cela convienne...

Bonne journée

Pierre Dumas

[tacleur]

Bonjour à tous,

Merci beaucoup pour votre aide.

La solution de Pierre Dumas semble tout à fait fonctionner sur mon test à petite échelle.
Je vais le déployer dans le week-end à très grande échelle (ou plutôt immense tableur).

Je vous tiens informé de la solution,

Excellente soirée à tous,

[tacleur]

Bonjour à tous,
Merci encore de votre précieuse aide.
J'y arrive presque complétement !

Il reste deux problèmes :
1) j'ai deux postes qui ont le même nom "Poste A". Comment puis-je lui demander de passer le premier résultat ?
La solution ultime serait de renommer mon poste mais je préfère voir avec vous si jamais vous avez une solution à laquelle je n'aurai pas pensé.

2)Bizarrement lorsque j'enchaîne deux mois sur la même ligne (février suivi de mars par exemple) il me donne des résultats abérants.
Quand j'enlève le mois "en plus" il me donne le bon résultat.
Avez vous une idée d'où cela provient ?

Merci encore pour votre aide,

[ARTURO83]

Bonjour,

1) j'ai deux postes qui ont le même nom "Poste A". Comment puis-je lui demander de passer le premier résultat ?
La solution ultime serait de renommer mon poste mais je préfère voir avec vous si jamais vous avez une solution à laquelle je n'aurai pas pensé.

Vous avez répondu à la question, il vous faut affecter un indice supplémentaire pour les distinguer, sinon comment voulez-vous qu'une formule prenne un plutôt que l'autre. Renommez votre poste "Poste A1" et "POSTE A2".

2)Bizarrement lorsque j'enchaîne deux mois sur la même ligne (février suivi de mars par exemple) il me donne des résultats abérants.
Quand j'enlève le mois "en plus" il me donne le bon résultat.

J'ai fait des tests avec ma formule, je n'ai rien trouvé d'anormal.

Pièce jointe 542577

Cdlt

[tacleur]

Bonjour Merci pour votre aide,

Je suis dessus depuis trois jours. Je ne m'en sors pas (novice investi mais novice quand même).

J'ai fait un test de tableau en dessous du planning :



Voici le fichier test :

Test Planning.xlsx

Merci encore pour votre aide et votre soutien

[ARTURO83]

Bonjour,

Pour simplifier, je suis passer par une fonction personnalisée, la formule devient en C61 jusqu'à C64, (à tirer vers le bas, s'il doit y en avoir plus)

Code :

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

=empl($C$60;$B61)

TRES IMPORTANT: Il vous faut reprendre l'ensemble de votre tableau car vous avez pour un même horaire un nombre d'espaces différents entre certaines valeurs. Exemple:sc2 8h avec 4 espaces et sc2 8h avec 5 espaces, il vous être plus rigoureux, sinon ça ne marchera pas correctement. Le nombre de caractères, espaces compris, doit être rigoureusement le même pour une même nomenclature.

le fichier
Pièce jointe 544053

Le code utilisé pour la fonction personnalisée

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
Function Empl(Date_j As String, Plage As Range) As String
    Application.ScreenUpdating = False
    If Plage <> "" Then
        Set d = Rows(3).Find(Date_j, LookIn:=xlFormulas, LookAt:=xlWhole)
        Lig = 61
        For i = 4 To 53
            If Cells(i, d.Column) = Plage Then
                Res = Res & "; " & Cells(i, "A")
                Lig = Lig + 1
            End If
        Next i
        If Res <> "" Then
            Empl = Right(Res, Len(Res) - 1)
        Else
            Empl = ""
        End If
    Else
        Empl = ""
    End If
End Function

Cdlt

[Pierre Fauconnier]

Salut.

J'ai écrit un tuto expliquant comment créer un calendrier perpétuel en Excel sans VBA... Si cela peut t'aider...

Règles de base pour ce genre de travail: On saisit les données dans un tableau structuré (voir mon tuto sur les tableaux structurés), et on récupère les données (par formules et/ou VBA) dans la feuille de calendrier. C'est uniquement dans ce sens-là que l'on peut utiliser au mieux Excel...

[tacleur]

Bonjour à tous,

Merci à tous pour votre aide,

Cela prends forme grâce à vous !

Il me reste quelques ajustements : en effet, je souhaite utiliser la fonction personnalisée que vous m'avez proposé dans un onglet différent que celui dans lequel s'effectue la recherche.

Comment puis-je imposer un onglet de recherche dans ma fonction personnalisée sur excel ?

Merci encore pour votre aide,

Nous sommes proche de la solution finale.

[ARTURO83]

Bonjour,

Tableau de résultat dans l'onglet: "Recherche". Saisissez dans la cellule C3 le nom de l'onglet où doit s'effectuer la recherche , la formule d'extraction des employés reste inchangée.
le fichier:
Pièce jointe 550841

le code utilisé:

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
Function Empl(Date_j As String, Plage As Range) As String
    Dim f1 As Worksheet, f2 As Worksheet
    Dim Lig As Long, i As Long
    Dim Res As String
 
    Application.ScreenUpdating = False
    Set f1 = Sheets("Recherche")
    Set f2 = Sheets(f1.Cells(3, "C").Value)
    If Plage <> "" Then
        Set d = f2.Rows(3).Find(Date_j, LookIn:=xlFormulas, LookAt:=xlWhole)
        Lig = 61
        For i = 4 To 53
            If f2.Cells(i, d.Column) = Plage Then
                Res = Res & "; " & f2.Cells(i, "A")
                Lig = Lig + 1
            End If
        Next i
        If Res <> "" Then
            Empl = Right(Res, Len(Res) - 1)
        Else
            Empl = ""
        End If
    Else
        Empl = ""
    End If
    Set f1 = Nothing
    Set f2 = Nothing
End Function

Cdlt

[Philippe Tulliez]

Bonjour,

On saisit les données dans un tableau structuré (voir mon tuto sur les tableaux structurés), et on récupère les données (par formules et/ou VBA) dans la feuille de calendrier. C'est uniquement dans ce sens-là que l'on peut utiliser au mieux Excel.

100% d'accord avec Pierre.

Comme d'ailleurs je l'avais déjà signalé dans mon intervention (Fil #2)
Il faut placer les données dans une feuille et le planning rempli grâce à des formules comme illustré ci-dessous (A gauche la base de données, à droite le planning)
Si l'on souhaite absolument, faire du VBA pour effectuer l'encodage, on utilise une procédure événementielle qui affiche un UserForm qui ira chercher l'information de la journée et de l'heure sélectionnée grâce à une clé construite au moment de l'événement (ici Date et Heure soit l'intersection Ligne/Colonne)
Construit comme cela c'est quelques lignes de code. C'est propre et la maintenance est aisée



Une procédure événementielle (Clic droit) affiche les informations provenant de la liste de données



Une procédure événementielle (Double Clic) affiche les informations provenant de la liste de données en permettant la modification et la suppression des données

[Pierre Fauconnier]

Pour compléter Philippe...

Dans la mesure où l'on sait saisir assez facilement dans un tableau structuré, un agenda perpétuel "de base" peut être réalisé sans aucune ligne de code, sur base de trois formules Excel. Il suffit de modifier la date en H3 pour afficher l'agenda d'une autre semaine. Cet agenda peut fonctionner pour plusieurs années. Sa seule limite est que l'on ne peut saisir que 1.048.575 réunions. En tranches horaires d'une demi-heure, vous en prenez pour 120 ans (en théorie).

On peut (à peine) complexifier en mettant une validation sur H3 pour n'accepter que des lundis comme saisie, et des listes déroulantes dans le tableau structuré pour les utilisateurs et les salles. L'actualisation est effectuée en fonction des options de calcul (automatique ou manuel). On peut agrémenter avec de la Mise en Forme Conditionnelle si on veut. Si JOINDRE.TEXTE n'est pas disponible (selon les versions), on peut adapter la formule. Ca m'a pris 7 minutes pour le créer et le mettre en place, et c'est un xlsx, donc sans vba.

[tacleur]

Bonjour à tous,

C'est la solution de ARTURO83 qui m'a été la plus utile.

Pour éviter d'avoir des erreurs dans la saisie, je souhaite faire des listes.
Deux problèmes que je n'ai pas encore résolu : je souhaite que les choix aient une couleur défini et je souhaite aussi pouvoir mettre un pop up ou trouver un moyen d'éviter d'avoir deux fois le même choix dans une même colonne.

Merci encore pour vos réponses,votre aide et votre patience pour un novice

[ARTURO83]

Bonjour,

Pouvez-vous citer des exemples de ce que vous attendez?

Cdlt

[tacleur]

Bonjour,

J'ai un tableau Excel de grande taille avec une vingtaine de poste différents (par colonne).
Chaque poste a une couleur en fonction de son lieu. (ex : poste A 8H et poste A 9H ont une couleur distincte du poste B ...) Tout cela dans le but d'avoir une harmonie visuellement.

Concernant l'idée de pouvoir avoir une liste de choix, cela permettrait de contrer les éventuelles erreurs de frappe qui pourraient être introduites dans le tableau si l'on écrit au clavier et rendre notre solution de recherche caduque.

Je souhaite donc avoir une liste déroulante qui une fois le choix sélectionné, donne la couleur de la cellule choisi en fonction de celui-ci.
Une fois la colonne complète, je souhaite enfin pouvoir visualiser les erreurs de doublons.

N'hésitez pas si vous avez des questions,


Merci encore à vous,
Bonne journée à vous,

[ARTURO83]

Bonjour,

D'après ce que j'ai compris, création d'une feuille nommée "Liste_des_postes" contenant en colonne "A" tous les postes, en colonne "B" le nom de la couleur affectée à chaque poste, et en colonne "C" la concaténation de 2 colonnes.
Dans la feuille des mois, en sélectionnant une des cellules du tableau, cela crée une validation de données (liste déroulante de tous les postes avec le nom de la couleur associée), sélectionnez le nom et la couleur de votre choix, mais comme cet évènement n'est pas déclencheur d'une macro, il vous faut cliquer dans une cellule d'à côté pour faire afficher le nom du poste avec celui de la couleur, et cliquer à nouveau sur la cellule pour afficher le nom du poste ainsi que la couleur de la cellule.

Ci-dessous une petite vidéo des actions à effectuer:
Pièce jointe 555888

le fichier
Pièce jointe 555892

le code dans le module "ThisWorkbook"

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
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim Couleur As String
    Application.EnableEvents = False
    On Error Resume Next
    If Sh.Name <> "a terme" And Sh.Name <> "Recherche" And Sh.Name <> "Liste_des_postes" Then
        If Selection.Count = 1 Then
            'ajout d'une validation de données dans la cellule sélectionnée
            If Target.Row > 3 And Target.Column > 1 And Cells(Target.Row, "A") <> "" Then
                With Target.Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Postes"
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .ShowInput = True
                    .ShowError = False
                End With
            End If
            'Application des couleurs
            DerLig = Range("A" & Rows.Count).End(xlUp).Row
            DerCol = Range("XFD3").End(xlToLeft).Column
            Set d = Sheets("Liste_des_postes").Columns(3).Find(Range(Target.Address).Value, LookIn:=xlValues)
            Couleur = Sheets("Liste_des_postes").Cells(d.Row, "A").Interior.Color
            Range(Target.Address).Interior.Color = Couleur
            ReDim Car(Len(Range(Target.Address).Value)) As String
            'suppression du texte excédentaire à partir du "$"
            Position$ = InStr(1, ActiveCell, "$", 1)
            Target = Left(Target, Position$ - 1)
        End If
    End If
    Application.EnableEvents = True
End Sub

Cdlt

[tacleur]

Bonjour,
Un grand merci à vous !
Je teste cela cette semaine.

Concernant le simulateur de planning, si je souhaite le mettre dans un autre fichier excel que le planning de base cela est-il possible ?

Merci pour votre précieuse aide,