XL 2016 Ma formule de détection de chevauchements et bilocations en étudiant les plages horaires dysfonctionne

[Webperegrino]

Bonjour Le Forum,

L’extrait de ce qui est présenté en pièce annexé et le résultat d’une longue recherche par codification VBA.
Le résultat s’affiche après un tri ascendant sur la colonne F [Prénoms] , puis ascendant sur la colonne L [Début de la plage horaire] et ascendant sur colonne M (Fin de plage horaire].

Une première formule spécifique est placée en cellule [N3] : elle semble bien fonctionner.
C’est la deuxième formule en cellule [N4], glissée jusqu’à la cellule [N100] qui me porte des complications ?
Plus j’essaie de la transformer plus je me noie…
Rien ne presse mais j’aimerai trouver mon erreur dans la formulation ; ça fait un jour de recherche mais plus je modifie plus je me noie…

Fonctionnement souhaité de la formule de la colonne M :
Lorsqu’un prénom placé en colonne F, figurant aussi, ou pas, dans la cellule au-dessus et/ou dans la cellule au-dessous, la formule devrait travailler sur ce prénom, pour détecter (colonne N) sur une ou trois de ces cellules (colonne F) , selon le début horaire et fin horaire (colonnes L et M) :

• du chevauchement sur le début de plage ou la fin de plage horaire
• de la bilocation (sur deux sites différents) avec une plage horaire identique

Par exemple, avec la formule placée en colonne N :

• Lignes 3 et 4 : Alexandra est en chevauchement sur les deux premières lignes : elle terminerait en Site n° 1 à 22h30 mais doit être en Site n° 7 à partir de 21h00 : CHEVAUCHEMENT de plage horaire,
• Lignes 19 et 20 : il s’agit bien d’une BILOCATION sur Site 4 et Site 5 car Dominique est à la même plage horaire aux deux endroits,
• Mais, la formule ne fonctionne pas en lignes 21 et 22 : en effet, Elisabeth·P a deux plages horaires qui ne se chevauchent pas. Elle finit à 17h00 sur un site et ne reprend sur l’autre site qu’à 22h30 pour finir au matin à 02h00. Quand bien même elle aurait repris sur le deuxième site à 17h00 précises, il ne doit pas y avoir là, ni CHEVAUCHEMENT ni BILOCATION.
• Idem pour mes lignes suivantes :
  • ligne 52 avec le cas de Maryse
  • ligne 56 pour Michel·G
  • ligne 59 pour Muriel

Qu’est-ce que j’ai mal réalisé dans la formule et qui empêche de donner un bon résultat dans tous ces cas ?
Cette formule en colle N devrait pouvoir fonctionner pour tout nouvel affichage ultérieur de valeurs différentes en colonnes F-K-L-M.
J’ai espoir que vous pourrez m’aiguiller.

Dans deuxième dernier temps, une fois le bon résultat trouvé sur toutes les cellules de la colonne N, et pour ne garder à l’écran que les « anomalies de plages horaires » pour chaque personne, je cacherai toutes les lignes sans message pour n’afficher que les bilocations et les chevauchements.
Merci à l’avance pour toute aide apporté sur cette formule.
Webperegrino

 

[TooFatBoy]

Hello,

Une première formule spécifique est placée en cellule [M3] : elle semble bien fonctionner.

A moins que ce soit N3 ???

Lorsqu’un prénom placé en colonne F, figurant aussi, ou pas, dans la cellule au-dessus et/ou dans la cellule au-dessous, la formule devrait travailler sur ce prénom

Donc on n'a pas besoin de tester le prénom, et du coup la formule retourne n'importe quoi. Non ?

Qu’est-ce que j’ai mal réalisé dans la formule et qui empêche de donner un bon résultat dans tous ces cas ?

J'ai l'impression que tu as oublié de prendre en compte le changement de jour au-delà de minuit, donc tes tests sont faussés.
Soit tu ajoutes 1 dans ta colonne M quand l'heure de fin n'est pas le même jour que l'heure de début, soit tu ajoutes le 1 dans ta formule.

Mais pourquoi ta formule ne fait-elle le test que sur la ligne du dessus et du dessous ?
Si le tableau est trié différemment, ça ne fonctionne plus ?


Le lieu n'intervient pas dans ta formule ?


Le plus simple serait que tu expliques clairement ce que tu veux faire, plutôt que de donner une formule qui ne fonctionne pas. 😉


Toujours pas de TS ?!?

 

[sylvanu]

Bonjour Webperegrino, TooFatBoy,
Votre formule va devenir une usine à gaz avec des heures de fin le lendemain et des sites qui peuvent changer ou non.
Puisque votre fichier est en xlsm, peut on utiliser une fonction perso en VBA ?

 

[Webperegrino]

Le Forum,

Bonjour TooFatBoy, Bonjour Sylvanu,
Oui c'est corrigé pour la cellule N3. J'avais mal référencé dans mon texte.
Le prénom peut se reproduire jusqu'à trois fois (pas plus, en adjacent) en colonne F.
Le prénom absent en cellule supérieure ou/et inférieure, la formule intégrerait tous ces cas pour chaque prénom qui reste nécessaire à consulter par elle... pour éviter justement n'importe quoi au résultat.
Oui, je ne sais pas faire les calculs avec ces comparaisons d’horaires entre « avant minuit » et « après minuit » ; ça va faire partie de mes recherches annexées au sujet en cours.

J’ai pensé qu’en regroupant les lignes comme présentées (tous les prénoms rassemblés dans l’ordre croissant) le traitement de cette formule serait plus simple. C’est un plus aussi, pour la lecture d’ensemble du tableau, d’avoir le prénom ainsi regroupé sur une à trois lignes dans la colonne des prénoms.
Le lieu n’intervient pas dans la formule.

Waou ! les TS sont inconnus de conception pour moi. Il faut que je trouve un site où je trouverai un tuto. Leur usage me semble bien sympathique pourtant.

Sylvanu : Justement, je pensais aussi que m’orienter vers une conception VBA de remplissage de la colonne N serait plus efficace. Je m'y attelle cette semaine, après mardi, car mon week-end est chargé.
Merci à vous,
Webperegrino

 

[TooFatBoy]

Oui, je ne sais pas faire les calculs avec ces comparaisons d’horaires entre « avant minuit » et « après minuit » ; ça va faire partie de mes recherches annexées au sujet en cours.

Comme dit précédemment, il suffit d'ajouter 1.


En colonne M tu peux saisir 26:30 pour 2:30 du matin, comme ça normalement tu es tranquille pour les calculs dans tes formules.
Ou alors tu utilises une colonne masquée avec une formule qui reprend les heures de la colonne M en y ajoutant 1 si besoin, et dans tes formules au lieu d'utiliser la colonne M tu utilises cette colonne masquée.

Mais si en colonne M tu saisis 2:30, dans tes formules il te faut ajouter 1 au contenu de la cellule de la colonne M.
Par exemple, au lieu de simplement mettre M4 dans ta formule, il faudra mettre un truc du genre M4+(M4>=L4).

 

[Webperegrino]

Le Forum,
TooFatBoy et Sylvanu,
C'est noté, TooFatBoy, pour les remarques #5, merci.

Quant à moi, j'ai eu un moment pour moi cet après-midi pour travailler un peu.
J'ai essayé ceci ; je n'en suis pas mécontent (voir fichier ci-joint).

Avez-vous mieux à proposer, les lignes VBA semblent bien fonctionner ?
Je vous mets l'application en phase d'attente : il suffit de cliquer sur le bouton Oranger pour voir l'effet des codes.
Webperegrino

 

[TooFatBoy]

Donc ta question initiale demandait une formule, et maintenant on est partis sur du VBA.
Est-ce définitif, ou cela peut-il encore changer et revenir à une formule ?

As-tu essayé d'inverser les horaires des lignes 3 et 4 juste pour voir ce que ça donnerait ?


ps : il vaut mieux, logiquement, mettre le Application.ScreenUpdating = False au tout début de la macro, ou au moins avant de faire es modifications dans la feuille. Ca éviterait de voir clignoter le tableau de gauche.
Ceci dit, peut-être est-ce ici fait exprès pour que ledit clignotement montre que le tableau a bien été mis à jour ?

 

[Webperegrino]

Le Forum,
Merci pour les derniers conseils, TooFatBoy.
Sur les recommandations de Sylvanu j'ai adopté le vba pour résoudre mon affaire.
Ça fonctionne apparemment maintenant, je vais donc garder cette dernière solution.
Je vais placer Application.ScreenUpdating=False au début. En effet, ce sera mieux.
Merci à vous deux,
Webperegrino

 

[TooFatBoy]

Sur les recommandations de Sylvanu j'ai adopté le vba pour résoudre mon affaire.
Ça fonctionne apparemment maintenant, je vais donc garder cette dernière solution.

Tu as donc modifié la macro de #6, je suppose.
Peut-on voir, juste par curiosité, la version qui fonctionne correctement, s'il te plait ?

 

[Webperegrino]

Le Forum,
Bonsoir TooFatBoy, Sylvanu
TooFatBoy, à mon avis la version #6 fonctionne parfaitement avec le vba : il suffit d'activer le bouton orange que j'ai ajouté en haut de feuille pour activer la macro.
Voici la version avec la ligne Application.ScreenUpdating = False placée au tout début de la macro (plus de clignotement du tableau comme vous me l'avez signalé en #7 : cette ligne est donc passée de la ligne 4 à la ligne n° 2).
Webperegrino

 

[TooFatBoy]

TooFatBoy, à mon avis la version #6 fonctionne parfaitement avec le vba : il suffit d'activer le bouton orange que j'ai ajouté en haut de feuille pour activer la macro.

Si tu le dis... je dois sûrement me tromper. Probablement une subtilité que je n'ai pas saisie, du même genre que "les prénoms identiques se suivent obligatoirement et ne peuvent apparaître que trois fois maximum", donc ici un truc du genre "il est impossible que les horaires...". 🙁

 

[Webperegrino]

En réponse au #11 :
Oui, car au début du #1, j'avais précisé qu'un travail vba préparatoire - non présentée en fichier #1, pour ne pas alourdir mes explications - se faisait pour que :
- la feuille se présente avec un résultat de tri et d'ordonnancement,
- les prénoms sont alors rassemblés (dans mon travail, un prénom ne dépasse pas trois affichages successifs dans la colonne)
- les horaires de début et de fin de plage, dans les deux colonnes voisines, sont aussi placés en ordre ascendant par un ordre VBA préalable à la recherche des bilocations/chevauchements.
Ce n'est qu'après que les codes du #6 ou du #10 entrent en action pour afficher le synoptique des Bilocations/Chevauchement.
Webperegrino

 

[bsalv]

avec 2 fonctions personalisées. Le nombre d'affichages n'est pas limité et la liste n'est pas nécessairement trié.
transformation vers une macro ordinaire n'est pas si difficile.

 

[Webperegrino]

Le Forum,
Bonjour Bsalv,
Merci pour le fichier rectifié. Votre proposition me semble très intéressante.
Très occupé aujourd'hui, je reviens vers vous demain en journée, ou mardi.
Merci beaucoup,
Webperegrino

 

[Webperegrino]

Le Forum,

Bonjour TooFatBoy et Sylvanu,

Bonjour Bsalv, je ne vous oublie pas !

Bsalv, votre proposition #13 m’a dans un premier temps intrigué : je suis complètement ignorant dans l’utilisation des tableaux structurés et des formules (fonctions vba rattachées).

Puis, dans un deuxième temps, j’ai pensé qu’il fallait que je me mette à ces tableaux structurés ! (selon également les recommandations de TooFatBoy depuis un bon moment).

Dans mon vrai fichier, j’ai déjà commencé à avancer dans ce domaine, ; j’y ai placé ce qui était expliqué en exemple #13, et ça se présente plutôt bien. Mais la présentation finale et la durée des calculs me semblent à améliorer.

Aussi, je retravaille pour plus de cohérence et de logique dans ma façon de « pomper » dans ma feuille PLANNING puis mieux analyser chaque bénévole sur une journée de ce planning.

Je suis en train de préparer un exemple de ce que je désirerais en final. Je ne veux pas vous le présenter ici tant que je n’aurai pas élaboré une partie du travail. Mais ça me prend beaucoup d’énergie et de cogitation, car, pour moi, ces TS n’ont rien à voir avec la gestion de mes tableaux non structurés avec du VBA « normal ».

Quand j’aurai créé un nouveau jet de la présentation désirée, je reviendrai vers vous et alors, le « Prof » que vous serez pour moi, pourra y mettre les rectifications-corrections nécessaires, si vous l’acceptez bien sûr.

Pour le moment, comme dans le feuilleton « Mission Impossible » des années d’autant, mes macros se mettent à griller sur certaines lignes… J’en suis à vérifier si je peux simplifier certaines étapes de fragmentation-éclatement-regroupement ordonné des cellules ramenées de la base pour analyse et détection de mes Chevauchements et Bilocations.

Ce que vous avez déjà proposé est déjà satisfaisant pour moi, une nouvelle approche permettra peut-être de donner un résultat plus rapide : quand je fais tourner l’analyse elle prend plus de 2 minutes avant l’affichage final…

Merci de votre patience et votre aide,

Webperegrino