XL 2016 Signaler des bilocations du fait d'horaires identiques ou en chevauchement [selon le contenu de la colonne L du fichier]

[Webperegrino]

Bonjour Le Forum,

Après une journée de recherches infructueuses pour trouver la bonne formulation par codes Vba ou Formule, je viens vers vous avec l’exemple ci-joint.

Un tableau n° 4 me donne une synthèse de recherche de compagnons pour une personne, dans un tableau bien plus important (jours, lieux) : cela, j’arrive parfaitement à le résoudre avec du Vba.

Ce qui est présenté ici est un résultat de recherche pour le prénom SOLENN.
Mon problème :
Signaler en ROUGE ou en VIOLET les plages horaires en colonne (extraites de la colonne J-LIEUX) pour lesquelles, ici, SOLENN est en bilocation (sur deux lieux en même temps) ou plus hasardeux, - dans la recherche d’une solution vba ou autre formule - quand elle va se trouver avec une partie d’horaires en chevauchement sur deux lieux, ou plusieurs lieux.

Deux exemples de ma requête sont développés en colonne O), la coloration ayant été mise manuellement en colonne L.

Ainsi, par une invention de génie de votre part, des cellules « horaire » de la colonne L seraient :

• en rouge pour « Même horaire sur deux ou plusieurs endroits »
• en violet pour « Une partie de temps d’horaires, sur deux ou plusieurs endroits ».

Merci à l’avance pour votre contribution,

Webperegrino

 

[JHA]

Bonjour à tous,

A tout hasard, un début avec power query

JHA

 

[Lolote83]

Bonjour à tous.
@JHA, je ne comprends pas la formule en colonne H.
Quand on ouvre le fichier, on constate bien des VRAI et des FAUX et c'est juste.

A l'ouverture

Par contre, si on rentre dans la formule (ne serais-ce que pour voir comment elle est construite), à la validation on obtient que des vides (dernier argument de la formule)

Si on remplace "" par JHA, on obtient que des JHA

Je ne saisie pas pourquoi

@+ Lolote83

 

[JHA]

Bonjour @Lolote83

J'ai modifié les formules des colonnes "G" et "H" pour une meilleure compréhension.
En colonne "H"; j'ai ajouté "NB.SI([Valeur];[@Valeur])>1;....." pour ne prendre que les noms >1

JHA

 

[Lolote83]

Re bonjour
Par contre, si par exemple sur la cellule C11 (donc ligne11 - Joëlle F) à la place de 18:30 on inscrit 18:25, cela ne devrait-il pas générer un VRAI avec la ligne 2
Ligne 02 : 16:00 -> 18:30
Ligne 11 : 18:25 -> 21:00, il y a bien chevauchement (5 min)
Je cherche
@+ Lolote83

 

[Webperegrino]

Bonjour Le Forum,
Bonjour JHA et Lolote83,
Merci pour cette proposition "power query", un procédure que je n'ai jamais utilisée jusqu'ici avec ma version Excel2016.
Je vais étudier cela dans le détail pour un usage ultérieur.

Toutefois, et pour le moment, j'ai comme de contrainte de trouver les résultats dans la feuille1, dans le pavé [$L$19:$L$34].

De plus, j'ai corrigé la colonne K, car étant une recherche pour ... SOLENN, j'avais oublié de la placer dans toutes les cellules K19 à K24 (c'est ce que m'affiche la macro vba dans ma vraie application, trop lourde pour venir en pièce jointe).

Voici donc le fichier de base corrigé dans ce sens.
Merci pour l'intérêt que vous avez porté à mon souci.
Webperegrino

 

[Webperegrino]

Le Forum,
Bonjour Lolote83,
La situation du #5 que vous soulevez ne sera pas utilisée dans mon cas mais est intéressante à appliquer.
En effet, dans ce cas on considère qu'il y a chevauchement. les cellules doivent alors être colorées en violet.
Ce cas sera en effet pratique dans l'usage.
Peut-on utiliser votre approche directement dans la feuille de mon fichier #6, sans avoir à utiliser le Power Query que je ne connais pas encore ?
Merci
Webperegrino

 

[Lolote83]

Re bonjour,
La structure du fichier fourni est assez compliquée à gérer c'est la raison pour laquelle @JHA est passé par PowerQuery pour en sortir un tableau structuré et correspondant à tes souhaits.
De mon coté, j'ai aussi fait un tableau structuré (à l'image de celui de JHA) qui donne actuellement les mêmes résultats mais ou le problème évoqué au post#5 n'est pas pris en compte ni suite à la modification que vous avez apportez au post#6.
Voici une copie du fichier mais je butte encore sur le chevauchement

@+ Lolote83

 

[Webperegrino]

Lolot83,
Merci pour la réponse précédente.
Je vais analyser tout cela.

Je proposer de simplifier mon approche, à partir du fichier en #6.
Il ne s'agit que de travailler à partir des cellules de la colonne L (Horaires).
En réalité, on peut faire abstraction des autres colonnes du pavé [I:K];
Ce sont les horaires, ici qui sont importants à travailler.

De mon côté, j'essaie de rester sur la FEUILLE1.
J'y éclate actuellement en colonne G et H :
- la gauche (5 caractères)
- la droite avec 5 caractères également
... pour DEBUT et FIN, soit 16:00, soit 18:30
- que je vais mettre en 10,00 et 18,30 plus tard s'il le faut
- et essayer en colonne I vos formules pour trouver un résultat incitant la coloration de la cellule L en rouge ou en violet.
Webperegrino

 

[TooFatBoy]

Bonjour,

Je proposer de simplifier mon approche, à partir du fichier en #6.
Il ne s'agit que de travailler à partir des cellules de la colonne L (Horaires).
En réalité, on peut faire abstraction des autres colonnes du pavé [I:K];
Ce sont les horaires, ici qui sont importants à travailler.

Le problème c'est qu'il n'y a, au sens Excel, aucun horaire, il n'y a que des textes...

Peut-être serait-il plus simple de travailler sur deux colonnes contenant respectivement l'horaire de début et l'horaire de fin, plutôt que d'avoir une seule colonne contenant du texte représentant la tranche horaire ?



[edit]

J'y éclate actuellement en colonne G et H

Au temps pour moi, je n'avais pas lu jusqu'au bout. Je suis désol !

que je vais mettre en 10,00 et 18,30 plus tard s'il le faut

Vaudrait mieux utiliser un truc du genre TEMPSVAL, histoire que ça devienne réellement des horaires.

[/edit]

 

[Webperegrino]

Bonjour TooFatBoy,
Oui dans la colonne L, on affiche en sortie d'extraction l'horaire sous présentation de texte.
Cette partie est sortie de mon application source (85 Feuilles) de près de 6 000 ko et va servir ensuite à composer des "fiches planning individuel" exportées en format .PDF, entre autre, à chaque bénévole. Ces horaires en texte y sont reportés pour la période qui les concerne.

C'est pour cela que dans la composition générale des postes sur 80 personnes, j'ai recours à cette information de Bilocation ou chevauchement, pour m'aider dans la distribution des personnes sur les lieux.

A ce stade de mon travail, je ne peux revenir en arrière pour transformer ces "horaires-texte" en horaire.
Je pense que la solution conjuguée de Lolote83-JHA est judicieuse et que pour aller en avant (avec ce que j'ai déjà dans mon application sans trop de transformation) on approche du résultat face à cette étape que je n'arrive pas à résoudre.

Je vais étudier le "truc" TEMPSVAL, merci pour l'information ; avec vous aussi, le débit de 'l'eau dans mon moulin" devient plus efficace. Avec une prochaine solution mon application ne sera plus un moulin, ça deviendra une cathédrale !
Webperegrino.

 

[Webperegrino]

=TEMPSVAL(GAUCHE(L19;5))+DROITE(L19;5) au format "13:30", c'est cela ?
Webperegrino

 

[Webperegrino]

=SIERREUR(TEMPSVAL(GAUCHE(L19;5))+DROITE(L19;5);"") pour éviter #VALEUR !
... semble me donner un résultat correct.

 

[Lolote83]

Re bonjour à tous
Concernant le recouvrement, voici les possibilités que j'entrevoie (Je ne pense pas en avoir oublié)

C'est bien ça ?????
@+ Lolote83

 

[Webperegrino]

oui,
Il y a aussi un dernier cas 7 :
- avec le segment de temps jaune plus long que le vert : NONOK Recouvrement TOTAL