Calcul de plages horaires à partir de valeurs booléennes (0 - 1) - Résolu

[Al1C21]

Bonjour,

Je cherche une solution, sans recours à des macros, pour déterminer des heures d'ouverture et fermeture de service, sur une période de 24 heures, en ayant pour chaque service l'information si le service est ouvert ou non dans chaque créneau d'une heure.

Pour chaque service j'ai donc une succession de 24 valeurs 0 ou 1, et je veux obtenir en bout de ligne les heures de début (ouverture) et de fin (fermeture) du service. Il peut y avoir 2 ou 3 plages d'ouverture.

J'ai "bricolé" quelque chose qui me donne bien la première ouverture, mais ça ne fonctionne pas pour la suite.

Notez bien qu'il y a des services qui n'on aucune ouverture, ou au contraire qui sont toujours ouverts.

Merci d'avance de votre aide, et même si la solution complète n'émerge pas je pense que vous m'aurez permis d'avancer.

Cordialement.

 

[eriiic]

Re : Calcul de plages horaires à partir de valeurs booléennes (0 - 1)

Re,

Que dois-je en conclure ?
Que tu ne veux pas d'une solution par macro ?
Ou que tu n'as pas lu le post 10, ou testé ?

eric

 

[mapomme]

Re : Calcul de plages horaires à partir de valeurs booléennes (0 - 1)

Bonsoir Al1C21, le forum,

Dans les formules alambiquées et avec un peu de retard, une proposition avec un tableau intermédiaire.

Pour apporter une petite différence aux remarquables propositions déjà faites, j'ai essayé de ne pas disjoindre des plages contigües (plage s'étendant autour de 4h00)

Les plages sont sur 1/2 Heure.

 

[ROGER2327]

Re : Calcul de plages horaires à partir de valeurs booléennes (0 - 1)

Bonjour à tous

Une autre solution :​

Sub tata()
Dim i&, j&, k&, l&, n&, h#, c&, v, u() As Boolean, o()

    n = 3 'Nb. max. de périodes d'ouverture.
    With Range("D2")
        With Range(.Cells, .End(xlToRight)) 'Plage horaire.
            v = Range(.Offset(2), .Offset(2).End(xlDown)).Value 'Données

            l = UBound(v, 1) - 1: c = UBound(v, 2) - 1: n = n + n
            ReDim u(l, c)
            ReDim o(l, n)
            For i = 0 To l: For j = 0 To c
                u(i, j) = CBool(v(i + 1, j + 1))
            Next j, i
            v = .Value
        End With
    End With
    For i = 0 To l
        k = 0
        For j = 0 To c
            If u(i, j) <> u(i, IIf(j = 0, c, j - 1)) Then
                If u(i, j) Then
                    If k = n Then o(i, k) = "...": Exit For
                    o(i, k) = j: k = k + 2
                End If
            End If
        Next
    Next

    For i = 0 To l
        k = 0
        Do Until IsEmpty(o(i, k)) Or k = n
            For j = o(i, k) To o(i, k) + c
                If Not u(i, j Mod (c + 1)) Then
                    h = v(1, o(i, k) + 1): o(i, k) = h - Int(h)
                    h = v(1, j Mod (c + 1) + 1): o(i, k + 1) = h - Int(h) - (h = 1)
                    k = k + 2
                    Exit For
                End If
            Next
        Loop
        If k = 0 And u(i, 0) Then o(i, k) = 0: o(i, k + 1) = 1
    Next

    With Range("AC4").Resize(l + 1, n + 1): .NumberFormat = "[h]:mm": .Value = o: End With 'Résultats.
End Sub

Assez rapide.

Facilement adaptable à d'autres plages horaires, même à pas variable (voir feuilles Feuil2 et Feuil3).​

ROGER2327
#5923

Lundi 16 Merdre 139 (Nativité de Saint Donatien A François - fête Suprême Quarte)
14 Prairial An CCXX, 0,5522h - acacia
2012-W22-6T01:19:31Z

 

[Al1C21]

Re : Calcul de plages horaires à partir de valeurs booléennes (0 - 1)

Bonjour,

Je cherche effectivement plutôt une solution par formules, mais n'exclue pas une solution par macro.
J'ai bien lu le post 10, mais oui je n'ai pas encore pu tester ta proposition (ni toutes les suivantes...)

L'avancée que j'ai réalisée est basée sur la proposition de Rachid : j'ai pu l'adapter facilement à 48 plages d'une demie-heure.

Voir ce que ça donne dans le fichier joint.

Je vai maintenant regarder un peu les solutions par macro, mais il m'y faudra un peu de temps (je n'ai que de vagues notions sur les macros).

En tout cas je remercie tous les contributeurs à ce fil, et si je ne réponds pas à chacun cela ne signifie pas que je regarde pas leurs propositions.

Alain

 

[eriiic]

Re : Calcul de plages horaires à partir de valeurs booléennes (0 - 1)

Bonjour,

Je vai maintenant regarder un peu les solutions par macro, mais il m'y faudra un peu de temps (je n'ai que de vagues notions sur les macros).
Pour tester il suffit d'ouvrir le fichier de faire ctrl+t.
eric

 

[R@chid]

Re : Calcul de plages horaires à partir de valeurs booléennes (0 - 1)

Bonjour,

L'avancée que j'ai réalisée est basée sur la proposition de Rachid : j'ai pu l'adapter facilement à 48 plages d'une demie-heure.

J'ai comparé le résultat renvoyé par ma formule avec celui de la formule de Hoerwind, et j'ai trouvé une petite différence,
donc pour l'ajout de la dernière colonne c'est bon, mais ça va aussi nous aider a encore simplifier la formule,
en BB3 :

=SIERREUR(INDEX($D$2:$AZ$2;TROUVE("*";SUBSTITUE($BA3;CHOISIR(MOD(COLONNES($BB:BB)-1;2)+1;"01";"10");"*";ENT((COLONNES($BB:BB)-1)/2)+1)));"")

Comme on n'a pas besoin du dernier 0 dans la concaténation puisque c'est déjà fait sur la colonne AZ...

Voir PJ
Amicalement

 

[Al1C21]

Re : Calcul de plages horaires à partir de valeurs booléennes (0 - 1)

Re...

Eriiiic, j'ai fait tourner ta macro, et ça fonctionne bien.
Pour passer à des plages à la demie-heure je suppose qu'il ne suffit pas de modifier le tableau de données en insérant les colonnes manquantes ?

Roger 2327, ton code VBA marche bien lui aussi. Pour faire une macro adaptée à des plages par demie-heures (48 colonnes pour 24 heures), faut-il modifier quelque chose dans le code ?

Franchement, vous m'épatez tous, et je vous remercie vivement de vos apports.
Je sens que j'approche du but, après il ne me restera qu'à voir comment mettre en oeuvre compte tenu de la taille du fichier origine.

Cordialement.
Alain

 

[ROGER2327]

Re : Calcul de plages horaires à partir de valeurs booléennes (0 - 1)

Re...

(...)

Roger 2327, ton code VBA marche bien lui aussi. Pour faire une macro adaptée à des plages par demie-heures (48 colonnes pour 24 heures), faut-il modifier quelque chose dans le code ?

(...)

Je pensais qu'avec les trois exemples donnés et quelques annotations dans le code, le paramétrage du code était assez évident. Comme je me trompais, allons plus avant.

Nouvelle version du code :​

Private Sub CommandButton1_Click()
    tata HR:=Range("M2"), DC:=2, OF:=Range("E4"), NB:=3
End Sub

Sub tata(HR As Range, DC&, OF As Range, NB&)
Dim i&, j&, k&, l&, h#, c&, v, u() As Boolean, o()

    With HR
        With Range(.Cells, .End(xlToRight)) 'Plage horaire.
'       La cellule à droite de la dernière donnée utile doit être vide.

            v = Range(.Offset(DC), .Offset(DC).End(xlDown)).Value 'Données

            l = UBound(v, 1) - 1: c = UBound(v, 2) - 1: NB = NB + NB
            ReDim u(l, c)
            ReDim o(l, NB)
            For i = 0 To l: For j = 0 To c
                u(i, j) = CBool(v(i + 1, j + 1))
            Next j, i
            v = .Value
        End With
    End With

    For i = 0 To l
        k = 0
        For j = 0 To c
            If u(i, j) <> u(i, IIf(j = 0, c, j - 1)) Then
                If u(i, j) Then
                    If k = NB Then o(i, k) = "...": Exit For
                    o(i, k) = j: k = k + 2
                End If
            End If
        Next
    Next

    For i = 0 To l
        k = 0
        Do Until IsEmpty(o(i, k)) Or k = NB
            For j = o(i, k) To o(i, k) + c
                If Not u(i, j Mod (c + 1)) Then
                    h = v(1, o(i, k) + 1): o(i, k) = h - Int(h)
                    h = v(1, j Mod (c + 1) + 1): o(i, k + 1) = h - Int(h) - (h = 1)
                    k = k + 2
                    Exit For
                End If
            Next
        Loop
        If k = 0 And u(i, 0) Then o(i, k) = 0: o(i, k + 1) = 1
    Next

    With OF.Resize(l + 1, NB + 1): .NumberFormat = "[h]:mm": .Value = o: End With 'Résultats.
End Sub

Le code comporte quatre paramètres :

1. HR = Cellule de la première donnée d'horaire.
2. DC = Décalage entre la ligne de l'horaire et la première ligne de données.
3. OF = Première cellule de la plage de résultats.
4. NB = Nombre maximum de couples Ouverture/Fermeture souhaités.

Dans l'exemple joint, on appelle la procédure par :

tata HR:=Range("M2"), DC:=2, OF:=Range("E4"), NB:=3

ou simplement par

tata Range("M2"), 2, Range("E4"), 3

HR:=Range("M2") parce que la première donnée d'horaire (04:00) en en M2 ;
DC:=2 parce que la première ligne de données est deux lignes plus bas ;
OF:=Range("E4") parce que le premier résultat doit apparaître en E4 ;
NB:=3 parce qu'on veut au plus trois couples de résultats.

Remarque : si le nombre de couples possibles est supérieur au nombre NB de couples souhaités, la procédure ne donne que les NB premiers couples. Pour montrer qu'elle a vu qu'il y avait plus que NB couples possibles, elle complète le résultat par ... (trois points) dans une colonne supplémentaire. (Ca peut aider à détecter une erreur dans les données...)

Voilà.

Je m'absente une huitaine de jours à partir de dimanche : s'il y a des précisions supplémentaires à donner, je verrai cela la semaine suivante.
​

ROGER2327
#5925

Mardi 17 Merdre 139 (Saint Woland, professeur - fête Suprême Quarte)
15 Prairial An CCXX, 0,4002h - caille
2012-W22-7T00:57:37Z

 

[Al1C21]

Re : Calcul de plages horaires à partir de valeurs booléennes (0 - 1)

Bonjour,

Merci Roger pour cette nouvelle contribution.
Comme je l'ai déjà dit, je n'y connais rien aux macros (sauf à savoir que ça existe et peut faire du bon boulot).

D'ailleurs j'ai un problème avec ta dernière proposition : je ne sais pas comment se nomme la macro, et lorsque j'ouvre le fichier je ne sais pas la lancer (pas de nom proposé dans le menu Macros).

Pardonnez mon amateurisme intégral !

Cordialement.
Alain

 

[eriiic]

Re : Calcul de plages horaires à partir de valeurs booléennes (0 - 1)

Bonjour,

Roger t'as mis un bouton en A2, clique dessus.
eric

 

[Al1C21]

Re : Calcul de plages horaires à partir de valeurs booléennes (0 - 1)

Re...

Ok, vu le bouton. Merci Eric

Sur ta macro, comment faire pour passer à 48 plage d'une demie-heure ? Plage de données : $D$2:$AY$26 au lieu de $D$2:$AA$26.

Pour le VBA de Roger, il y aurait une amélioration à apporter : par exemple, pour le service Gardien (ID 102, ligne 5) il faudrait en fait trouver 2 plages : 4:00 h - 10:00 h et 22:00 h - 4:00 h (le premier 4:00 est le début de période, le deuxième est en fait le lendemain).

Cordialement.

Alain

 

[eriiic]

Re : Calcul de plages horaires à partir de valeurs booléennes (0 - 1)

Re,

tu as vraiment besoin de 2 solutions en vba ?
Celle de roger est très bien. Et comme il a pris le temps de l'adapter...
eric

 

[Al1C21]

Re : Calcul de plages horaires à partir de valeurs booléennes (0 - 1)

Re...

Bien sûr, Eric, une seule solution me suffirait :
- soit la tienne adaptée à 48 plages d'une demie-heure
- soit celle de Roger si elle fournit par exemple deux plages 4h-10h et 22h-4h au lieu d'une seule 22h-10h (ou 4h-5h et 21h-4h au lieu de 21h-5h) comme actuellement.

Et vu ma très grande maîtrise de VBA (!!!), je ne suis capable de faire ni l'une ni l'autre modification !
Je suis désolé d'être aussi peu compétent en VBA, mais c'est bien un peu pour ça que j'ai lancé ma demande...

Mais je peux attendre le retour de Roger pour peaufiner sa macro.

Quoi qu'il en soit, merci beaucoup de ton aide et de tes propositions.
Cordialement.

Alain

 

[Fo_rum]

Re : Calcul de plages horaires à partir de valeurs booléennes (0 - 1)

Bonsoir,

j'ai suivi l'évolution du fil depuis son début.
Je présente 2 autres idées (VBA oblige pour éviter des formules qui ralentissent) :
- si le tableau des 0 et 1 n'est pas obligatoire : un UsF servant à saisir les plages.
- avec le tableau mais sans saisie : effets immédiats aux clics (mise en évidence de la plage et inscription sous forme horaire).
Tout cela peut se peaufiner avec des gardes-fous et des nouvelles colonnes formatées par modification des macros.

 

[Al1C21]

Re : Calcul de plages horaires à partir de valeurs booléennes (0 - 1)

Bonjour,

Le tableau des 0 et 1 est obligatoire : c'est LA donnée d'origine à partir de laquelle doivent être calculées les plages. Je ne maîtrise pas ces données d'origine, représentant plus de 70000 enregistrements issus de relevés de terrain. Il n'est donc pas envisageable pour de ressaisir toutes les données d'origine !

Merci d'avoir suivi et regardé, et de ta proposition.

Au final je fais le bilan suivant :
- pour une solution par formules, la proposition de Rachid a ma préférence (mais je ne suis pas sûr qu'Excel la supporte vue la taille du fichier de données)
- pour une solution par macro, voir mon post 28 : une des deux propositions (Eric ou Roger), après avoir résolu les manques identifiés soit sur les 48 plages, soit sur l'affichage des résultats.

Merci encore à tous les contributeurs.

Alain