Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

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

W

Webperegrino

XLDnaute Impliqué
Supporter XLD
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
 

Pièces jointes

  • Coloration des horaires si bilocation.xlsm
    21 KB · Affichages: 17
Trier par date Trier par votes
W

Webperegrino

XLDnaute Impliqué
Supporter XLD
Voici où j'en suis en restant avec ma version de 2016 sans Power Query inconnu pour moi en utilisation.
Bons résultats d'approche avec colonnes O, P, Q, R, (TooFatBoy #18).

Ma formule en colonne S est mauvaise.
Les cas de Lolote83 en colonne U-V (j'ai colorié manuellement, malheureusement).

Fichier 1 : Colonne I non triée
Fichier 2 : Colonne I triée
 

Pièces jointes

  • Coloration des horaires si bilocation_approcheWebperegrino.xlsm
    24.1 KB · Affichages: 3
  • Coloration des horaires triés _approcheWebperegrino.xlsm
    24.2 KB · Affichages: 3
Dernière édition:
Vote positif 0 Vote négatif
L

Lolote83

XLDnaute Barbatruc
Re,
Dans ton dernier fichier transmis tu inscrit des recouvrement ou non, mais tu ne fais jamais appel à la personne concernée.
C'est la raison pour laquelle @JHA avec son PowerQuery avait de ton tableau initial établi un tableau structuré pour tester chaque personne.
Tu testes uniquement les horaires mais sans différencier qui est impacté.
Dans une même cellule tu peux avoir SOLENN qui sera effectivement en chevauchement mais pas NICOLLE pour la même cellule testée (Comment compte tu faire cette différence ?????)
Il faut que tu travailles en BDD comme le tableau fourni par @JHA
@+ Lolote83
 
Vote positif 0 Vote négatif
W

Webperegrino

XLDnaute Impliqué
Supporter XLD
Lolote83,

J'avance un peu ! voir colonne [M] imparfaite.

Je viens te donner une peu d'explications sur le fonctionnement de mon application de construction du planning individuel de chaque bénévole.

En réalité, elle sert à mon épouse qui est responsable bénévole d'une équipe de 80 bénévoles à "répartir" sur plusieurs jours et plusieurs plages changeantes selon le jour.
Actuellement elle s'arrache les neurones à le faire sur papier...

Le très petit morceau de fichier placé ici est une simulation de situations imaginées. Tu as d'ailleurs bien trouvé tous les cas d'anomalies horaires.

Ici on n'est que dans le cas, par exemple de SOLENN pour la journée de demain et sur tous les sites sur lesquels on lui demandera d'opérer.

Les personnes qui accompagnent SOLENN sur ses postes, suite à une recherche pour elle seulement, se trouveraient en colonne [K] pour ce genre de résultat ; les autres prénoms sont donc là seulement pour indiquer à SOLENN avec qui elle travaillera.

Donc ce qui apparaît en colonnes I et L sont les POSTES DE SOLENN, sur une journée (il y a plusieurs journées étudiées).

L'objectif est donc de savoir quand SOLENN est à deux endroits en même temps et quels sont les postes pour lesquels elle va avoir une période de recouvrement, pour que je lui attribue des postes plus équilibrés en présence horaire ; ainsi le planning général sera modifié, SOLENN déplacée.

Une nouvelle recherche sur SOLENN aura corrigé les bilocations.
A mon épouse de savoir si on accepte des recouvrements ou pas : sur certains lieux, SOLENN peut quitter son binôme à l'approche de fin de prestation pour aller assurer l'autre poste avec un autre binôme, trinôme, etc.

Ensuite chaque autre personne de l'équipe, dont celle qui a l'occasion d'être avec SOLENN, aura aussi cette étude de "Bilocation-recouvrement".

Tu cites NICOLLE :
Ainsi NICOLLE aura son propre planning qui apparaîtra après une extraction macro-vba du planning général en construction ; comme j'aurai précédemment réglé le problème de bilocation au niveau du planning de SOLENN, NICOLLE sera en conflit horaire ou pas pour d'autres plages dans son propre planning individuel et pas nécessairement avec SOLENN, ce cas aura été corrigé au niveau de celui de SOLENN.

D'où ces indications d'horaires en rouge et en violet sur la première recherche chez Solenn.

Ainsi le planning général des horaires, qui sera attribué à 80 personnes sur 51 plages horaires (vaste programme !), aura moins d'anomalies de "bilocations-recouvrements" au fur et à mesure que je fais mes extractions, bénévole par bénévole, avec le petit tableau du pavé [I19:L34] comme résultat de "moulinette VBA".
 

Pièces jointes

  • 2_Coloration des horaires triés _approcheWebperegrino.xlsm
    24.7 KB · Affichages: 1
Dernière édition:
Vote positif 0 Vote négatif

piga25

XLDnaute Barbatruc
Bonjour,
Peut être en VBA
VB: 
Sub ColorierNomsEtHoraires()
    Dim ws As Worksheet
    Dim derniereLigne As Long
    Dim i As Long, j As Long
    Dim noms1() As String, noms2() As String
    Dim k As Long, l As Long
    Dim horaire1 As String, horaire2 As String
    Dim debutHoraire1 As Date, finHoraire1 As Date
    Dim debutHoraire2 As Date, finHoraire2 As Date
    Dim nomTrouve As Boolean

    ' Définir la feuille de calcul
    Set ws = ThisWorkbook.Sheets("Feuil1") ' Ajustez le nom de la feuille si nécessaire
    
    ' Trouver la dernière ligne avec des données dans la colonne K
    derniereLigne = ws.Cells(ws.Rows.Count, "K").End(xlUp).Row

    ' Boucler à travers chaque paire de lignes pour vérifier les noms dupliqués et les horaires qui se chevauchent
    For i = 2 To derniereLigne - 1
        For j = i + 1 To derniereLigne
            ' Extraire les noms de chaque cellule dans la colonne K
            noms1 = Split(ws.Cells(i, 11).Value, "*") ' Colonne K
            noms2 = Split(ws.Cells(j, 11).Value, "*") ' Colonne K

            nomTrouve = False
            ' Comparer chaque mot de noms1 avec chaque mot de noms2
            For k = LBound(noms1) To UBound(noms1)
                For l = LBound(noms2) To UBound(noms2)
                    If Trim(noms1(k)) = Trim(noms2(l)) And Trim(noms1(k)) <> "" Then
                        nomTrouve = True
                        Exit For
                    End If
                Next l
                If nomTrouve Then Exit For
            Next k

            If nomTrouve Then
                horaire1 = ws.Cells(i, 12).Value ' Colonne L
                horaire2 = ws.Cells(j, 12).Value ' Colonne L

                ' Extraire les horaires de début et de fin
                debutHoraire1 = CDate(Left(horaire1, InStr(1, horaire1, " - ") - 1))
                finHoraire1 = CDate(Mid(horaire1, InStr(1, horaire1, " - ") + 3))
                debutHoraire2 = CDate(Left(horaire2, InStr(1, horaire2, " - ") - 1))
                finHoraire2 = CDate(Mid(horaire2, InStr(1, horaire2, " - ") + 3))

                ' Gérer les horaires se prolongeant jusqu'au lendemain
                If finHoraire1 < debutHoraire1 Then finHoraire1 = finHoraire1 + 1
                If finHoraire2 < debutHoraire2 Then finHoraire2 = finHoraire2 + 1

                ' Si les horaires sont identiques
                If horaire1 = horaire2 Then
                    ws.Cells(i, 11).Interior.Color = RGB(255, 0, 0) ' Rouge
                    ws.Cells(i, 12).Interior.Color = RGB(255, 0, 0) ' Rouge
                    ws.Cells(j, 11).Interior.Color = RGB(255, 0, 0) ' Rouge
                    ws.Cells(j, 12).Interior.Color = RGB(255, 0, 0) ' Rouge

                ' Si les horaires se chevauchent
                ElseIf (debutHoraire1 < finHoraire2 And debutHoraire2 < finHoraire1) Then
                    ws.Cells(i, 11).Interior.Color = RGB(148, 0, 211) ' Violet
                    ws.Cells(i, 12).Interior.Color = RGB(148, 0, 211) ' Violet
                    ws.Cells(j, 11).Interior.Color = RGB(148, 0, 211) ' Violet
                    ws.Cells(j, 12).Interior.Color = RGB(148, 0, 211) ' Violet
                End If
            End If
        Next j
    Next i
End Sub
 

Pièces jointes

  • Coloration des horaires si bilocation.xlsm
    35.7 KB · Affichages: 3
Vote positif 0 Vote négatif
W

Webperegrino

XLDnaute Impliqué
Supporter XLD
... et encore plus près avec les propositions précédentes rassemblées dans la formule de la colonne [M], sans utiliser le "brouillon d'approche colonnes [O] à [V]...
 

Pièces jointes

  • 3_Coloration des horaires triés _approcheWebperegrino.xlsm
    24.8 KB · Affichages: 1
Vote positif 0 Vote négatif
W

Webperegrino

XLDnaute Impliqué
Supporter XLD
Bonsoir Piga25

(de B'sançon ? ma fille y ayant séjourné un moment)

Ah !
Ça a l'air de fonctionner ! Wouahh !
Je viens de l'appliquer sur le fichier #26, reproduit ici en pièce jointe.
J'ai l'impression que vous venez de trouver "LA" solution, mais mon épouse demande de venir manger ... alors je vais lever le verre à votre santé car ça s'arrose !
Je reviens vers vous après analyse de l'effet de la macro.
Merci, merci, merci.
Une épine en moins !
Bonne soirée
Webperegrino
 

Pièces jointes

  • PIGA25_Coloration des horaires triés.xlsm
    30.3 KB · Affichages: 2
Vote positif 0 Vote négatif
W

Webperegrino

XLDnaute Impliqué
Supporter XLD
Piga25,
Je viens de placer les codes dans mon grand fichier source.

En réalité il est inutile de travailler sur la colonne des noms:
dans mon "fichier-source", Solenn est placée en [C2], la macro tourne et les prénoms des compagnons se placent, sans "Solenn" en fin de compte, dans la colonne [K].

Votre macro a juste besoin de travailler sur la colonne [L] pour l'étude de "bilocation-chevauchement" pour coloration ou pas.
J'ai d'ailleurs mis en sommeil la coloration sur la colonne [K]

Toutefois :
J'ai une erreur d'exécution 5 sur la ligne
debutHoraire1 = CDate(Left(horaire1, InStr(1, horaire1, " - ") - 1))
Savez-vous pourquoi ?
Meric
 
Dernière édition:
Vote positif 0 Vote négatif
W

Webperegrino

XLDnaute Impliqué
Supporter XLD
Merci TooFatBoy,
Je ne serai pas libre avant 14 h 00 demain, j'étudie la proposition par formules #28.
Je viens de voir rapidement, ça a l'air d'être correct pour des analyses de lignes voisines.
Piga25 semble avec les codes VBA résoudre également les lignes non adjacentes pour déceler les horaires identiques ou à chevauchement.
Mais je n'ai pas regardé à fond, et surtout expérimenté dans le détail.
A demain et encore merci beaucoup pour vos aides à tous ici, ça fait vraiment plaisir.
Bonne soirée.
 
Dernière édition:
Vote positif 0 Vote négatif
Identifiez-vous ou inscrivez-vous pour participer.

Discussions similaires

T
  • Question
Microsoft 365 Afficher des résultats en colonne sous forme de ligne
Réponses
6
Affichages
111
JHA
J
B
  • Résolu(e)
XL 2019 Concours / MFC de mise en surbrillance de numéro selon résultats 13 ou <13
Réponses
4
Affichages
174
berru76
B
  • Résolu(e)
XL 2021 affectation et classement de mes appels selon contenu des cellules en colonnes J ou L ou M
2 3
Réponses
30
Affichages
2 K
Usine à gaz
M
  • Résolu(e)
XL 2013 Macross supprimant les données de cellule en dehors d'une période définie dans deux autres cellules
Réponses
9
Affichages
540
MarineU
M
W
  • Question
XL 2016 Transfert de 2 colonnes sans formules et duplication des cellules selon le nbre de fois indiqué en colonne L
Réponses
9
Affichages
610
Webperegrino
W
W
  • Question
XL 2016 Extraction de la partie centrale de texte alphanumérique de cellules placées en colonne B
2
Réponses
15
Affichages
1 K
Webperegrino
W
F
  • Résolu(e)
Microsoft 365 copier coller des colonnes en fonction de la valeur d'une cellule
Réponses
8
Affichages
625
loloboc
L
N
  • Question
Microsoft 365 afficher/masquer des lignes en fonction de la valeur d'une cellule qui aura été renseignée par une autre feuille (via un copier/collé automatique)
Réponses
5
Affichages
444
Nel'
N
L
  • Question
XL 2010 Filtrer et mise en couleur de cellules sur une colonne
Réponses
8
Affichages
586
sylvanu
W
  • Question
XL 2016 Coloration ou pas des cellules voisines de la colonne B
Réponses
2
Affichages
331
Webperegrino
W
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…