Discussion :

[Tekiila_67]

Bonjour à tous,

Je ne suis pas un pro du VBA, mais je sais plus ou moins faire ce qu'il faut pour que ça fonctionne en recherchant sur différents forums. Par contre, là, je sèche.
Pour commencer, c'est sur un PC du boulot, avec une version de Office 365, Excel 2002. Déjà là, je ne peux vous dire, c'est un Excel 2002 ou Excel 365 ? 2002 me parait bien vieux... Désolé de ma mauvaise culture.

Ma demande principale est la suivante :
Je gère une partie de maintenance préventive, qui est composée d'environ 2000 BT/an (bons de travail). Chaque BT est une tâche à faire. Ce tableau est issu de la GMAO (Gestion de maintenance assistée par ordinateur) qui extrait les données en csv, que je retranscris dans mon Excel pour jongler avec les données.

J'ai donc environ 5200 lignes actuellement, car il y a les années précédentes, qui ne s'affichent pas car j'ai un filtre sur les BT terminés, soldés etc.

J'ai rajouté une colonne "Planif" dans laquelle je mets une date (et heure) lorsque c'est planifié. J'aimerais pourvoir en extraire un calendrier automatique par semaine ou par mois, et imprimable, qui bouge en fonction des dates que j'entre dans cette colonne, et qui n'affichera que la semaine que je lui demande : Planning S47 pour exemple.

J'ai pensé faire un calendrier automatique dans une autre feuille, mais le souci est que si je change de semaine ou de mois, les données doivent changer, mais la cellule E30 par exemple, restera E30 dans la feuille. Donc je ne peux pas dire que "la date de tel BT = E30" car ça risque de changer. Pas facile à expliquer, désolé.

J'ai pensé aussi à créer toute une feuille avec tous les calendriers par semaine, comme ça les cellules ne changeront pas. Mais il est vrai que ça ne va pas être très dynamique, surtout que si j'ai 3 BT le même jour, on ne pourra pas superposer des données dans une seule cellule. Je m'explique : dans un autre planning manuel, j'ai juste à mettre une lettre en fonction de l'équipe qui travaille pour coloriser la cellule, et visuellement c'est propre. Là je voudrais le faire de façon automatique, pour que je n'ai qu'à entrer une date, et Excel me fait tout le reste : coloriser la bonne cellule, dans la bonne semaine etc. De plus, trouver une solution pour gérer le fait d'avoir plusieurs travaux dans une même journée.
Si j'ai plusieurs BT dans la même journée, je voudrais qu'il me mettre dans chaque ligne, le numéro du BT, et qu'il colorise le planning à la bonne date. CE qui veut dire que le planning peut s'agrandir en nombre de lignes.

En général, je suis imaginatif, et en manipulant j'arrive à me créer un code, pas forcément très propre par rapport à ce que vous savez faire, mais fonctionnel. Là, le souci est que je n'ai même pas mon point de départ, je ne sais pas comment imaginer la chose...

Merci d'avance pour vos avis/aide, sur une solution.

[Pierre Fauconnier]

Salut.

Tu parles de Office 365 ou XL 365, mais tu parles aussi de Excel 2002... Ca ne me semble pas très compatible. Excel 2002, c'est antédiluvien.

Si tu as Office 365, tu as normalement Excel 365 => Donc tu as Power Query, donc tu es potentiellement sauvé, car Power Query extrait très facilement des données selon une plage de dates dans un tableau structuré.


Si tu as besoin d'un planning visuellement plus élaboré, il y a moyen de faire simple... ou plus compliqué... Ici, je te montre une technique sans VBA.

Sur base d'un tableau des bons de travail (t_BT)




Tu peux créer un calendrier mensuel qui reprend les bons de travail pour chaque jour grâce à JOINDRE.TEXTE et FILTRE, deux fonctions XL365 qui font des miracles lorsqu'on les utilise ensemble... Dans le calendrier, la cellule A1 est formatée avec "mmmm aaaa" et les cellules reprenant les jours sont formatées avec "jjj jj". On peut bien entendu édulcorer le tout avec une mise en forme conditionnelle pour griser les jours "hors mois" et adapter les formules pour ne remplir que les cases du mois choisi.




Il suffira de modifier la date en A1 du calendrier (Attention, c'est bien une date qui est saisie avec un format personnalisé de la cellule) pour que le calendrier s'adapte. Tu as ainsi un calendrier qui peut fonctionner durant des années, la seule limite étant de ne pas rentrer plus de 1 048 575 bons de travail


BONUS 1

En utilisant INDIRECT sur une plage rendue dynamique, tu pourrais numéroter tes tâches pour les repérer et les dénombrer plus facilement dans la case d'une journée






BONUS 2

Pour autant que tes cellules utilisent "Renvoyer à la ligne automatiquement", tu peux remplacer le séparateur " - " de JOINDRE.TEXTE par CAR(10) pour démarrer chaque tâche sur une nouvelle ligne

[Tekiila_67]

Je t'avoue que je suis aussi perdu... En général, je vais sur Excel et tout est indiqué directement avec la version.
Ici il n'y a rien, juste "Excel". Je vais donc dans "compte" et je vois ceci :

Microsoft Excel pour Office 365 MSO (....) 64 bits. Et sur la page informations produit, j'ai Microsoft Office 365 ProPlus, et plus bas il y a marqué "A propos d'Excel ... Version 2002"
J'espère que tu vois mieux pourquoi je suis perdu

Justement, j'ai suivi ton tuto du calendrier perpétuel. Je voulais le faire en entier pour bien comprendre les différentes formules, et pouvoir réadapter à mon besoin, qui serait d'avoir un affichage sur une semaine ou un mois, avec les bonnes données.
J'ai réussi à tout faire, sauf que quand j'arrive au moment d'intégrer les raisons des jours fériés, il me transforme la date en son code (44... par exemple), mais la suite s'affiche bien.
Et si je mets JOUR avant JourCalculé, tout disparait. Depuis le début j'ai eu un souci avec ça, mais ça n'a pas bloqué pour la suite. Je pensais qu'il y avait une erreur dans la saisie du tuto, mais au final je pense que non...

Ce que tu me présente là à l'air d'être un peu évolué, dans le sens où il y a à chaque fois une ligne avec jour et date qui sépare chaque cellule. A voir si ça change grand chose par rapport à ton tuto. Mais ça m'a l'air déjà pas trop mal de faire quelque chose du genre, même si, effectivement, ce ne sera pas très visuel. Le visuel recherché, correspondrait à une sorte de Gantt.

Voici le calendrier actuel suivant ton tuto. J'y ai juste modifié la façon de générer la date avec 2 listes déroulantes, et modifié quelques MFC pour n'afficher QUE le calendrier réel, pas ce qui est hors du mois. Tu pourras aussi y voir mon souci avec le 11 novembre 2020 justement.







Et voici mon Gantt, dans lequel tu peux voir les BT, avec toutes les infos nécessaires, et sur la droite j'ai le planning. Si je choisi une date en cellule F dans laquelle il y a les dates du début à la fin du BT, le filtre m'enlève tout ce qui est hors de cette date, et je me retrouve par exemple avec une seule semaine. Visuellement, c'est ce que je recherche, mais j'aimerais que tout ce que je rentre là manuellement (les cellules colorées) se fasse automatiquement lorsque je mets une date.




En tout cas, je te remercie pour ta réponse !

[Tekiila_67]

Ah oui, autre chose que j'oubliais d'évoquer, et qui peut éventuellement bien déranger quand même.

Pour avoir mon fichier de données avec tous les BT (fichier nommé LUT pour Liste Unique des Travaux) voici le principe de fonctionnement :
- Je fais une requête en GMAO
- Extraction des données sur un fichier Excel en CSV que je renomme "Extr 20201105" pour aujourd'hui par exemple et que je range dans un dossier précis.
- Je lance ma macro vba : MAJ (Mise à Jour du LUT).
- Le code crée un onglet "Traitement" dans le LUT.
- Copie des données du CSV dans l'onglet Traitement du LUT.
- RechercheV sur toutes les données de l'onglet DATA (qui est le tableau final avec les planifs etc) pour les mettre aux bons endroits en fonction du numéro de BT.
- Une fois tout fini, le code me supprime tout le DATA, et me remet le tableau neuf et à jour à la place de l'ancien.
- Plein de filtres, mises en forme etc suivant des besoins.

Pourquoi j'explique ça ? Car le fait d'utiliser un VBA, et de supprimer des choses pour les remettre, ne risque pas de faire planter des choses ? Par exemple, si j'ai une date issu du tableau DATA, le lendemain, lors de la mise à jour, ce tableau sera supprimé, et là je me retrouverai avec une erreur dans le planning qui est, lui, géré sans VBA. Je me trompe ?

Car ça m'était arrivé lorsque je voulais faire une vue simplifiée pour ceux en lecture seule, avec moins de données. Pour ça j'avais juste dit dans un autre onglet que la cellule A1 est égale à la cellule A1 du LUT par exemple. Mais vu que tout est supprimé, après une MAJ je me retrouve avec des #N/A partout...

[Tekiila_67]

EDIT2 :
J'ai farfouillé, et j'ai vu quelqu'un qui te posait déjà la meme problématique que moi avec le jour qui se transforme en code ou en --/--/1900. J'ai remis en format standard, et ça fonctionne.

Si on arrive à faire comprendre à Excel que telle ou telle date est un jour férié, et que ça évolue lorsque le calendrier évolue, il me semble logique que je puisse faire pareil avec les numéros de BT.
Je vais "juste" devoir trouver comment, après la MAJ, j'arrive à obtenir mon tableau t_BT avec juste le numéro et les dates, et ça devrait fonctionner.

J'étudie la chose

EDIT3 :
Le fait de remettre en "standard", me casse mes MFC, car désormais Excel ne sait plus prendre en compte la fonction MOIS d'une cellule en standard je pense. Pourtant, dans ton tuto tu as réussi... ?

[Pierre Fauconnier]

Manifestement, tu es en XL365

Même si on est fondamentalement dans la même logique au niveau des formules, un Gantt se présente effectivement différemment. De plus, mon calendrier perpétuel a un peu vieilli car il n'était pas optimisé pour XL365, or les nouvelles fonctions apparues, que j'utilise dans l'exemple que je t'ai fourni, simplifient tout de même un peu la donne...
Que tu choisisses un Gantt ou un calendrier "normal" par semaine ou par mois, montre où tu en es et ce qui bloque... (peut-être en anonymisant les données) , mais note qu'au départ Excel n'est pas fait pour cela et donc il faudra un peu finasser avec les formules, voire les découper en morceaux plus digestes, en utilisant les formules nommées.

Montre où tu en es et où tu bloques, et nous pourrons probablement t'aider (J'avoue que je n'ai pas tout suivi de des messages). Un petit récap serait le bienvenu

[Tekiila_67]

Merci pour la précision

Effectivement, j'ai fait un Gantt car dans l'année j'ai 2 arrêts techniques, qui font que j'ai sur peu de temps (2-3-4 semaines) une grande quantité de BT, d'entreprises extérieures, de choses en interne, etc qui viennent en même temps, sans compter que les autres services interviennent dans les mêmes locaux, ils faut donc que tout soit planifié, et je fais donc ça manuellement.

Je vais essayer d'être clair et simple, même si, tu le vois, ce n'est pas évident et je m'en excuse déjà.
Pour commencer, la différence avec ton planning, est la quantité d'infos. Dans ton planning, c'est bien lorsqu'on a une chose à faire de temps en temps, c'est suffisant et propre. Dans mon cas, les bonhommes doivent travailler TOUS les jours, donc forcément chaque cellule aura des infos, avec même une précision sur les heures. C'est pour ça qu'un visuel à la semaine est plus simple.

L'objectif serait d'avoir, sur un onglet de mon fichier LUT, que j'appellerai "Planning", un planning sur la semaine (lundi au vendredi ou lundi au dimanche, peu importe) ou au mois dans lesquels j'aurais les numéros de BT qui sont à faire. Au lieu d'avoir "élagage" j'aurais "109452" par exemple. Et si j'en ai plusieurs, qu'ils aillent tous dans cette même cellule, à la bonne date.
A savoir que :

• Ce planning doit être imprimable
• Je souhaiterais pouvoir choisir la semaine facilement, je ferai probablement une liste déroulante ou quelque chose du genre
• Une fois la semaine choisie, le planning se met à jour, avec le même format, et les BT sont directement dedans
• Les dates des BT sont dans un autre onglet du fichier LUT, l'onglet "DATA" dans lequel il y a tout. En revanche, les colonnes ne sont pas l'une à coté de l'autre.
• Une fois les dates saisies dans l'onglet DATA, le planning se met à jour
• Ce planning doit avoir le détail des heures, car effectivement, des opérations ne prennent qu'une heure, et je ne voudrais pas qu'on se pose la question de la durée si la tâche est sur toute la journée dans le planning.
• J'aurais une MFC qui mettrais en couleur les plages horaires sur la bonne journée, comme tu peux le voir dans mon Gantt

En gros, ça doit être visuel sur une semaine, à la journée, et à l'heure, avec juste les infos du numéro de BT pour savoir de quoi on parle quand même. Le Gantt est toujours l'objectif le plus proche je pense. Je rajouterai pas la suite, par exemple, le numéro du local, comme ça, si on me demande, je peux dire que je serai dans tel local, tel jour.
Je ne sais pas si je peux poster un lien YT ou pas. Si non, je supprimerai. Mais voici le type de chose qui serait vraiment le top du top. A la place des employés, avoir le numéro de BT, comme dans mon Gantt encore une fois. Ce qui impliquerait que le tableau ne peut pas bouger en largeur car les jours et heures restent fixes, par contre en longueur il peut s'incrémenter ou se décrémenter.

Sinon oui, je sais qu'Excel n'est pas faire pour ça, mais je sais aussi qu'on peut faire des choses absolument fonctionnelles en manipulant correctement les différentes formules. Et à savoir que s'il y a besoin de faire en VBA, ça ne me dérange pas non plus, tant que ça fonctionne au final.

Ensuite, par rapport à ton fichier, j'en suis à ce point :





Comme tu peux le voir, j'ai mes deux listes, qui génèrent (via plusieurs formules ou techniques pour arriver à mes fins) la date, automatiquement au 1er du mois et de l'année sélectionnés. Le planning se génère bien au niveau des dates. Les numéros de jours fonctionne depuis que je suis repassé en standard et en ajoutant JOUR avant JourCalculé, mais depuis, mes MFC qui faisaient disparaitre les cellules et leurs contours qui sont hors mois ne fonctionnent plus. J'ai tenté dans une autre cellule, et effectivement, quand je choisis un jour, Excel me dit que la donnée n'est pas une date. Je pense que, puisque je demande de comparer le mois, et que dans la fonction on a JOUR, le bug vient de là, mais tu as fait ainsi et ça fonctionne... Voila où je bloque.
D'autant plus que ça fonctionne bien pour identifier les jours fériés et les samedis dimanches, donc je ne comprends pas pourquoi ça ne fonctionne plus.
Je voudrais faire disparaitre les jours hors mois, et j'avais réussi avant, mais là, ça ne veut plus...

Pour la suite, j'ai crée un tableau test avec des numéros de BT, j'arrive à en mettre un dans la cellule, mais pas plus, puisque RechercheV s'arrête dès lors qu'il a un résultat. J'ai vu dans ton nouveau planning que tu m'as envoyé, que c'est une autre fonction qu'on utilise, je vais essayer.
Par contre, le tableau que j'ai crée en test avec les numéros de BT et dates, il faudra que j'arrive à le générer automatiquement après la MAJ du fichier LUT.

Voici le tableau test (à l'arrache évidemment)



Désolé pour la longueur de ma réponse, j'espère que tu as les infos qui t'éclaircissent la chose, sinon n'hésites pas à me demander ce qu'il te manque. J'étudie de mon côté les solutions aussi.
Merci !

[Tekiila_67]

Bonjour,

J'ai tenté d'avancer un petit peu.
J'essaye de faire les choses par étapes, et voici ce que ça donne avec un tableau test.

Mon tableau test, sur lequel je rajoute des heures pour avoir un peu plus de précision.



Voici ensuite ce que j'en ressors, en mixant les différentes solutions proposées par Pierre.
J'ai bien les N° de BT qui apparaissent avec les heures, ou juste le N° de BT lorsqu'aucune heure n'est inscrite.



C'est plutôt propre, et je pense que pour un visu mensuel ce serait suffisant. J'aimerais en revanche savoir, maintenant que la date et la cellule dans lesquelles sont les infos sont séparées, comment je peux faire comprendre à Excel que "Si le 11/11/20 est férié, il faut griser la cellule concernée" ?
J'ai sélectionné C6 et C7 pour tester, en créant une nouvelle règle : =NB.JOURS.OUVRES.INTL(C6;C6;1;t_XFériés[Date]) et aussi avec un =0 à la fin, mais ça ne fonctionne pas, il y a une erreur. Pourtant quand je teste dans une cellule à coté hors MFC, ça fonctionne. J'aimerais que juste la cellule C7 soit grisée. Si vous avez une idée. Pour les samedis et dimanches ce sera probablement la même technique.

J'ai ensuite la version hebdomadaire journée, qui reprend le même principe que le mois, mais sur la semaine, et j'essaierai de mettre plus d'informations étant donné que j'ai plus de place. Ce tableau n'est pas très compliqué à finaliser à mon avis.




Ensuite, j'ai la version hebdomadaire horaire, et ce serait ça qui m'intéresse. Il faudrait arriver à faire comprendre à Excel comment différencier les heures, et surtout comment les mettre dans telle ou telle cellule. Puis aussi de différencier les couleurs par tâches. D'ailleurs, je devrais regrouper des tâches : Si j'ai 10 purgeurs à vérifier, qu'il ne mette qu'une cellule avec les 10N° de BT par exemple ou je ne sais pas exactement.
J'ai déjà tenté de faire une recherche de la ligne en fonction de l'heure, puis passer par des étapes intermédiaires pour ensuite compléter le tableau. Lui faire comprendre que le Lundi XX/XX/XX à 8h30 c'est la cellule X, et 9h c'est X+1 etc. Les RechercheV n'ont pas fonctionné pour le moment, mais je pense qu'il doit y avoir moyen de combiner les RechercheV et H ?




Et voici une idée de ce qui est fait totalement manuellement actuellement.

[Philippe Tulliez]

Bonjour,

comment je peux faire comprendre à Excel que "Si le 11/11/20 est férié, il faut griser la cellule concernée" ?

La mise en forme conditionnelle avec un test logique sur le résultat renvoyé par la fonction EQUIV supérieur à 0. EQUIV ayant comme premier argument la référence à la cellule contenant la date, le deuxième argument fait référence à la plage contenant la liste des jours fériés et le troisième argument la constante 0
lstHolidays est la plage nommée contenant la liste des fériés

Code :

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

=EQUIV(B$1;lstHolidays;0)>0

[EDIT]
ou, si l'on veut être puriste

Code :

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

=NON(ESTERREUR(EQUIV(B$1;lstHolidays;0)>0))

[Tekiila_67]

Merci Philippe Tulliez,

J'ai étudié la fonction EQUIV que je n'utilisais pas jusqu'ici. J'ai fait un test dans une cellule pour comprendre, et je t'avoue qu'au départ je ne voyais pas le but. Mais c'est bon, j'ai compris, mis en place, et avec quelques ajustements (EQUIV ne prend pas de tableau même en choisissant la ligne, il faut renommer) ça fonctionne.

Le point sur cette MFC est résolu.

Concernant le reste, hier j'ai vu une vidéo sur YT d'une dame, qui présente en anglais pendant 40mn un dashboard complet, avec une sorte de Gantt, tout ça à partir d'un TCD remanié. Ca m'a l'air top. J'essaye de trouver les solutions à mon problème.