XL 2019 Formule pour calculer le nombre d'usagers simultanés sur un créneau

[Softmama]

Bonjour à tous,

Je cherche à partir d'une liste de créneaux d'utilisation d'installations sportives, à déterminer sur chaque créneau, combien d'usagers sont présents en moyenne dessus. (forcément 1 à minima, mais il peut y en avoir d'autres sur d'autres salles, ou en mutualisant l'équipement, sur des créneaux qui parfois se chevauchent)

Je bute depuis un bon moment sur une formule que je ne parviens pas à pondre. (Parti sur un SOMMEPROD ou une Matricielle sans succès jusque là)
J'ai réglé le problème par macro il y a bien longtemps, mais ça ne me comble pas, même si cela fonctionne néanmoins, et j'aimerais vraiment voir quelle formule pourrait faire l'équivalent, tellement je m'y suis cassé les dents. Si nécessité de placer des colonnes de calculs intermédiaires pour atteindre le résultat, cela me convient très bien. (Je convertis notamment des horaires notés sous la forme 1230 pour 12h30 en 12,5 pour les besoins des calculs, ce qui ne simplifie pas trop la compréhension globale).

La formule que je propose à titre d'exemple ne renvoie pas le résultat escompté, je fournis plus d'explications et des exemples de résultats souhaités dans le fichier joint.
A première vue, cela ne semble pourtant pas si compliqué, mais cela dépasse mes capacités manifestement.

Dans le fichier joint, j'ai tronqué mon tableau en supprimant quelques colonnes et beaucoup de lignes (30000 lignes à l'origine), mais le principe est là.

Désolé par avance pour celui qui se penchera dessus, je crains qu'il y ait un fossé entre la simplicité de l'énoncé du problème et la complexité de la formule finale.
Mais surtout merci à ceux qui seront en mesure de me proposer des pistes, voire une solution.

PS : Je peux fournir le code VBA qui effectue les calculs par macro si besoin. Le code tient en quelques lignes, c'est frustrant.

Softmama

 

[shinozak]

Salut,

Je me suis permet de changer le format des dates, si c'est pas possible dans le fichier officiel, il faudra changer la formule.

 

[Softmama]

Bonjour Shinozak,

Merci pour cette proposition.
Hélas, elle ne répond pas complétement à ma problématique.

L'approche par format d'heure ne me dérange pas.
L'idée de cumuler les plages horaires en commun plutôt que le nombre d'usagers en commun me convient également.


Ce qui bloque par rapport à ma demande initiale et que fait votre formule, c'est additionner les durées des créneaux qui ont un morceau en commun : ainsi si un créneau est 12-15h un autre 11h-13h et un dernier 10h-14h sur le même équipement, même jour, le résultat de la formule donnera 3h+2h+4h = 9h.
Ce que je recherche, pour reprendre cet exemple, serait d'additionner les durées des créneaux en commun par rapport au créneau étudié : (12hà15h = 3h) + (11hà13h = 1h en commun) + (10hà14h = 2h en commun) soient 6h au total et non 9h.

Je relance donc le sujet, qui n'est pas résolu à ce stade

 

[CISCO]

Bonsoir

1) Dans ton vrai fichier, combien de lignes as tu ? Parce que, par formule, il ne faut pas rêver, cela risque de ramer...
2) Est-ce que tu as compris pourquoi ta formule, ou celle de Shinozak, ne te donne pas la bonne réponse ?

@ plus

 

[CISCO]

Bonsoir

Est-ce que cela ne serait pas plus pratique de lister dans un tableau, par ex en lignes, toutes les salles, et en colonnes, le temps, 1/2 h par 1/2 h par exemple, et de donner à l'intersection le nombre d'occupants ?

@ plus

 

[Softmama]

Bonsoir Cisco, tous

1) Dans ton vrai fichier, combien de lignes as tu ? Parce que, par formule, il ne faut pas rêver, cela risque de ramer...
2) Est-ce que tu as compris pourquoi ta formule, ou celle de Shinozak, ne te donne pas la bonne réponse

Dans le vrai fichier, j'arrive à 30000 à 40000 lignes par an, un fichier par an.
La macro actuelle prend 3-4 min de calculs, activée une à deux fois par mois, c'était pas le bout du monde.
Depuis l'update des postes de travail au bureau, on m'a passé à Version 2019 et la macro antérieure prend près d'un quart d'heure désormais. J'irai faire une pause pendant les calculs au pire...

Oui, pour celle de Shinozak, le calcul donne la durée totale des créneaux qui se juxtaposent. Ce dont j'ai besoin, c'est presque ça, mais uniquement sur la plage horaire considérée.
Si ma plage de base est par exemple de 10hà12h, et qu'il y a une autre plage 9hà11h, je souhaite que le résultat ne soit pas 4h (2 plages de 2h) mais 3h (2h de la plage de référence + 1h en commun avec l'autre plage).
Il manque donc une étape à la formule de Shinozak, qui par ailleurs est équivalente à la mienne (sauf que je me complique avec les formats horaires, mais c'est un détail), et dans la mienne je tente de ne pas prendre en compte la totalité de la plage horaire, mais juste la partie en doublon. C'est cette partie qui ne fonctionne pas, le SOMMEPROD isole bien l'équipement, la date, les horaires, mais la SOMME générée n'est pas bonne.

Est-ce que cela ne serait pas plus pratique de lister dans un tableau, par ex en lignes, toutes les salles, et en colonnes, le temps, 1/2 h par 1/2 h par exemple, et de donner à l'intersection le nombre d'occupants ?

Ton analyse de mon problème est juste.
En gros, c'est ce que je fais par macro, en checkant à la date choisie, l'équipement, et en regardant les quelques créneaux qui ressortent. La BDD étant triée par date, dès que je change de date ou que l'heure de début est supérieure à l'heure de fin du créneau étudié, je passe au créneau suivant. Le tout dans une variable en mémoire que je ponds à la fin.
Depuis qu'on est passés en 2019, les macros sont ralenties, je vais voir si je peux optimiser le code pour l'accélérer un chouille, car j'ai l'impression que je ne vais pas trouver par formule.

Je poste ici le code correspondant, issu du fichier original (les noms de feuilles et les plages sont donc en lien avec le vrai fichier), voir si quelqu'un y voit une possibilité d'amélioration sur la vitesse d'exécution. (J'y ajoute quelques commentaires pour plus de clarté) :

Sub ajuste_nbUsagers()
Dim R()
Dim n As Double, DATA(), ERP, Us, J, HDéb As Integer, Hfin As Integer, dDurée As Double, durCrén As Double
Dim c As Range

Application.ScreenUpdating = False
DATA = Feuil6.Range("A1:N" & Feuil6.Range("A100000").End(xlUp).Row).Value

ttt = Timer
For u = 2 To UBound(DATA, 1)
  n = 0

    'Récupération des infos sur le créneau étudié : Installation, Usager, Date, Heure de début et de fin, durée du créneau
    ERP = Feuil6.Range("C" & u)
    Us = Feuil6.Range("F" & u)
    J = Feuil6.Range("I" & u)
    HDéb = Feuil6.Range("J" & u) 'String(4 - Len(Feuil8.Range("J" & ligne)), "0") & Feuil8.Range("J" & ligne)
    Hfin = Feuil6.Range("K" & u) 'String(4 - Len(Feuil8.Range("K" & ligne)), "0") & Feuil8.Range("K" & ligne)
    durCrén = Feuil6.Range("M" & u)
 
  'Le tableau étant trié par date, je commence à la bonne date, pour pas regarder toute les lignes
  Set c = Feuil6.Range("I:I").Find(what:=J, LookIn:=xlFormulas, lookat:=xlWhole)
  '***************************************************************'
  '(j'aurais pu avantageusement je pense mettre à la place, déjà :
  'Set c = Feuil6.Range("I" & Application.match(J, feuil6.range("I1:I100000"),0))
  '***************************************************************'
 
  décal = 0
 
 '******************************************************************
 'ensuite je me balade dans le tableau, j'aurais certainement dû utiliser le Tableau DATA plutôt que des cellules...
'******************************************************************
  Do While c.Offset(décal, 0) = J 'Si bonne date'
    If Cells(c.Offset(décal, 0).Row, 3) = ERP And Cells(c.Offset(décal, 0).Row, 9) = J Then
      dDurée = 0
      If (Format(Cells(c.Offset(décal, 0).Row, 10), "0000") <= Format(HDéb, "0000") And Format(Cells(c.Offset(décal, 0).Row, 11), "0000") > Format(HDéb, "0000")) Or (Format(Cells(c.Offset(décal, 0).Row, 10), "0000") < Format(Hfin, "0000") And Format(Cells(c.Offset(décal, 0).Row, 11), "0000") >= Format(Hfin, "0000")) Or (Format(Cells(c.Offset(décal, 0).Row, 10), "0000") <= Format(Hfin, "0000") And Format(Cells(c.Offset(décal, 0).Row, 11), "0000") >= Format(HDéb, "0000")) Then
        dDurée = Val(Left(Application.Min(Cells(c.Offset(décal, 0).Row, 11), Hfin), Len(Application.Min(Cells(c.Offset(décal, 0).Row, 11), Hfin)) - 2)) - Val(Left(Application.Max(Cells(c.Offset(décal, 0).Row, 10), HDéb), Len(Application.Max(Cells(c.Offset(décal, 0).Row, 10), HDéb)) - 2)) + (Val(Right(Application.Min(Cells(c.Offset(décal, 0).Row, 11), Hfin), 2)) - Val(Right(Application.Max(Cells(c.Offset(décal, 0).Row, 10), HDéb), 2))) / 60
        n = n + dDurée / durCrén  'calcul du nbre d'usagers qui se cumule
      End If
      If c.Offset(décal, 1) > Hfin Then Exit Do 'sortie anticipée de la boucle si l'horaire est dépassé
    End If
    décal = décal + 1
  Loop
  ReDim Preserve R(1 To u - 1)
  R(u - 1) = n
Next

Debug.Print "nbusagers ", Timer - ttt
Application.ScreenUpdating = True
'Affichage du résultat'
Feuil6.Range("S2").Resize(UBound(R, 1), 1) = Application.Transpose(R)

End Sub

Bon, déjà en commentant le code (qui fonctionne pour le coup mais que je trouve long), je me rends compte que je charge le Tableau de 30000 lignes en mémoire, mais qu'ensuite une partie de l'analyse se fait avec une variable range c. Cela me donne déjà une piste d'amélioration en ne travaillant que sur la variable Tableau, j'ajusterai le code demain, c'est quand même pas terrible comme façon de faire ce que j'avais codé là...

Après, si un formuleux a une solution autre, je reste toujours très intéressé, par curiosité plus qu'autre chose.

Merci encore pour le temps passé et vos contributions.

 

[shinozak]

Bonjour Shinozak,

Merci pour cette proposition.
Hélas, elle ne répond pas complétement à ma problématique.

L'approche par format d'heure ne me dérange pas.
L'idée de cumuler les plages horaires en commun plutôt que le nombre d'usagers en commun me convient également.


Ce qui bloque par rapport à ma demande initiale et que fait votre formule, c'est additionner les durées des créneaux qui ont un morceau en commun : ainsi si un créneau est 12-15h un autre 11h-13h et un dernier 10h-14h sur le même équipement, même jour, le résultat de la formule donnera 3h+2h+4h = 9h.
Ce que je recherche, pour reprendre cet exemple, serait d'additionner les durées des créneaux en commun par rapport au créneau étudié : (12hà15h = 3h) + (11hà13h = 1h en commun) + (10hà14h = 2h en commun) soient 6h au total et non 9h.

Je relance donc le sujet, qui n'est pas résolu à ce stade

Bonjour,

As tu la fonction FILTRE dans ta version d'XL ?

 

[Softmama]

Bonjour,

Oui bien sûr, le filtre est bien présent.
Créer une macro en filtrant 30000 fois pour obtenir le résultat est envisageable, je l'ai codé pour voir, mais c'est encore plus long (je suis passé à 20 minutes).
Par ailleurs, j'ai réalisé le test annoncé hier en me passant de la variable Range et ne gardant que la variable Tableau et le temps de traitement est divisé par 10, ce qui redevient acceptable à mes yeux.

Donc si personne d'autre n'a de propositions par formule, je considère que mon problème a évolué de façon satisfaisante.

Pour ceux que ça intéresse, le code équivalent snas la variable Range c est le suivant :

Sub ajuste_nbUsagers()
Dim R()
Dim n As Double, DATA(), DADATES(), ERP, Us, J, HDéb As Integer, Hfin As Integer, dDurée As Double, durCrén As Double
Dim lLig As Long

Application.ScreenUpdating = False
DATA = Feuil6.Range("A1:N" & Feuil6.Range("A100000").End(xlUp).Row).Value
DADATES = Feuil6.Range("I1:I" & Feuil6.Range("A100000").End(xlUp).Row).Value

ttt = Timer
For u = 2 To UBound(DATA, 1)
  n = 0

    ERP = Feuil6.Range("C" & u)
    Us = Feuil6.Range("F" & u)
    J = Feuil6.Range("I" & u)
    HDéb = Feuil6.Range("J" & u)
    Hfin = Feuil6.Range("K" & u)
    durCrén = Feuil6.Range("M" & u)
 
  lLig = Application.Match(J, DADATES, 0)
 
  décal = 0
 
  Do While DATA(lLig + décal, 9) = J
    If DATA(lLig + décal, 3) = ERP Then
      dDurée = 0
      If (Format(DATA(lLig + décal, 10), "0000") <= Format(HDéb, "0000") And Format(DATA(lLig + décal, 11), "0000") > Format(HDéb, "0000")) Or (Format(DATA(lLig + décal, 10), "0000") < Format(Hfin, "0000") And Format(DATA(lLig + décal, 11), "0000") >= Format(Hfin, "0000")) Or (Format(DATA(lLig + décal, 10), "0000") <= Format(Hfin, "0000") And Format(DATA(lLig + décal, 11), "0000") >= Format(HDéb, "0000")) Then
        dDurée = Val(Left(Application.Min(DATA(lLig + décal, 9), Hfin), Len(Application.Min(DATA(lLig + décal, 11), Hfin)) - 2)) - Val(Left(Application.Max(DATA(lLig + décal, 10), HDéb), Len(Application.Max(DATA(lLig + décal, 10), HDéb)) - 2)) + (Val(Right(Application.Min(DATA(lLig + décal, 11), Hfin), 2)) - Val(Right(Application.Max(DATA(lLig + décal, 10), HDéb), 2))) / 60
        n = n + dDurée / durCrén
      End If
      If DATA(lLig + décal, 11) > Hfin Then Exit Do
    End If
    décal = décal + 1
    If lLig + décal > UBound(DADATES, 1) Then Exit Do
  Loop
  ReDim Preserve R(1 To u - 1)
  R(u - 1) = n
Next

Debug.Print "nbusagers ", Timer - ttt
Application.ScreenUpdating = True
Feuil6.Range("S2").Resize(UBound(R, 1), 1) = Application.Transpose(R)

End Sub

Merci encore pour vos contributions.

 

[CISCO]

Bonsoir

Je ne comprend pas bien ton exemple :
(12hà15h = 3h) + (11hà13h = 1h en commun) + (10hà14h = 2h en commun) --> 6 h
Pour moi, il ni a qu'une heure en commun entre ces trois périodes, de 12 h à 13 h.

Est-ce que tu pourrais expliquer ta logique dans ce cas ?

@ plus

 

[Softmama]

Bonsoir CISCO,

Je me suis mal fait comprendre en effet.
Je ne cherche pas quelle période est en commun avec tous les créneaux.
Je cherche le cumul de créneaux qui sont en commun avec le 1er, qui sert de référence, puis je fais le même calcul avec le suivant, jusqu'au dernier créneau...

C'est pourquoi, dans l'exemple que tu reprends :
(12hà15h = 3h) + (11hà13h = 1h en commun) + (10hà14h = 2h en commun) --> 6 h

Le créneau pris en compte est 12h à 15h, je garde sa durée de 3h. Puis j'ajoute les durées des autres créneaux qui "mordent" sur ce créneau.
Ainsi le créneau 11h à 13h a bien 1h en commun de 12h à 13h,
le créneau 10h à 14h a bien 2h en commun de 12h à 14h

Pour un total de 6h

Si j'avais un créneau de 12h15 à 12h45, j'ajouterais 0,5h
Si j'avais un créneau de 12h30 à 18h j'ajouterais 2h30 (12h30 à 14h en commun), et ainsi de suite...

Mais à la base, je ne cherchais pas la durée totale en commun, mais le nombre d'usagers simultanés. Le lien entre les deux est assez simple, il suffit de diviser la durée de créneau retenue par la durée du créneau de référence, ainsi pour l'exemple précédent :

Le premier créneau est 12h à 15h, je garde sa durée de 3h, soit 3/3= 1 usager (logique, s'il n'y a pas d'autres créneaux, il y a bien 1 usager). Puis j'ajoute les durées des autres créneaux qui mordent sur ce créneau.
Ainsi le créneau 11h à 13h a bien 1h en commun de 12h à 13h, soit 1/3 d'usager
le créneau 10h à 14h a bien 2h en commun de 12h à 14h soit 2/3 d'usager

Pour un total de 2 usagers en moyenne sur le créneau. (3 de 12h à 13h, 2 de 13 à 14h , et 1 de 14h à 15h)

et ainsi de suite pour chacun des 30 à 40000 créneaux que j'ai par an...

Cette formule me permet de ventiler le coût de fonctionnement de l'installation sportive au plus juste.
Ainsi toujours pour l'exemple précédent :
Si par exemple le coût de fonctionnement horaire (calculé par ailleurs 1 fois tous les 2 ans en prenant en compte les charges, les fluides, la masse salariale, les travaux lissés sur 10 ans...) et de 60€/h pour un gymnase, le créneau de l'exemple est valorisé à 60€/h * 3h (durée du créneau étudié) / 2 (nb d'usagers moyen sur ce créneau) = 90€ pour le créneau.

ça me permet pour chacun des 30 équipements sportifs que je gère d'optimiser les dépenses de fonctionnement sur une année, de m'aider à arbitrer l'attribution des créneaux et de reprendre de volée les usagers qui se plaignent que rien n'est fait pour eux : je leur affiche la valorisation de la mise à disposition gracieuse de l'installation sportive (souvent plusieurs dizaines de milliers d'euros par an pour une association), en général ça les calme.

En espérant m'être mieux fait comprendre. Désolé, pour ne pas avoir suffisamment bien détaillé mon problème à la base, j'ai cru pouvoir me passer de donner davantage de détails sur la finalité de la formule, mais je réalise que cela aide au final à se projeter vers une solution adaptée.
Bien à toi,

 

[CISCO]

Bonsoir

Si je ne me trompe, tu peux faire dans N2 avec

SOMME(SI(C2&D2&I2=C$2:C$402&D$2:D$402&I$2:I$402;SI(K$2:K$402<=K2;K$2:K$402;K2)-SI(J2<=J$2:J$402;J$2:J$402;J2)))

en matriciel, en mettant la bonne valeur à la place des 402, ou avec

SOMMEPROD((C2&D2&I2=C$2:C$402&D$2:D$402&I$2:I$402)*(SI(K$2:K$402<=K2;K$2:K$402;K2)-SI(J2<=J$2:J$402;J$2:J$402;J2)))

en matriciel elle aussi, à mettre au format hh:mm:ss, pour avoir le résultat en heures, minutes et secondes, toujours en mettant la bonne valeur à la place des 402..

@ plus

 

[Softmama]

Bonsoir CISCO,

Je viens de voir ta réponse et je t'en remercie.
La formule me semble pas mal coller à ce que je souhaite sans avoir encore testé.
Je regarde ça dans le détail demain et te tiens au courant.

Merci beaucoup.

 

[CISCO]

Bonjour

Je pense avoir oublié de prendre en compte les cas où il ni a pas recouvrement des périodes, et cela fausse certainement le résultat. On doit pouvoir corriger cela avec un ou des max (0,...). Je regarde ça cette après midi.

@ plus

 

[CISCO]

Bonjour

Nouvelles versions, ne posant pas problème il me semble quand certaines périodes du jour concerné ne se chevauchent pas :

SOMME(SI(C2&D2&I2=C$2:C$402&D$2:D$402&I$2:$I402;(J$2:J$402<=K2)*(J2<=K$2:K$402)*(SI(K$2:K$402<=K2;K$2:K$402;K2)-SI(J2<=J$2:J$402;J$2:J$402;J2))))

ou

SOMMEPROD((C2&D2&I2=C$2:C$402&D$2:D$402&I$2:I$402)*(J$2:J$402<=K2)*(J2<=K$2:K$402)*(SI(K$2:K$402<=K2;K$2:K$402;K2)-SI(J2<=J$2:J$402;J$2:J$402;J2)))

toutes les deux en matriciel

@ plus

 

[Softmama]

Bonjour Cisco, Shinozak, le forum,

Génial ! Je viens de modifier légèrement la formule pour qu'elle s'ajuste à mon format HHMM sur 4 digits, et calculé le nombre d'usagers moyens par créneau plutôt que la durée totale en commun que propose ta formule (il suffit de diviser pas la durée de référence à chaque fois) et injecté le tout dans le fichier exemple, ça donne les mêmes résultats que mon fichier source et les résultats par macro. C'est top.

Je vais maintenant remettre cette formule dans mon fichier initial pour évaluer le gain de temps, je vous tiens informés.
La formule finale retenue qui fonctionne et que j'intègre est (en matricielle) :

=SOMMEPROD((C2&D2&I2=C$2:C$402&D$2:D$402&I$2:I$402)*(J$2:J$402<=K2)*(J2<=K$2:K$402)*((SI(K$2:K$402<=K2;ENT($K$2:$K$402/100)+($K$2:$K$402/100-ENT($K$2:$K$402/100))*100/60;ENT(K2/100)+(K2/100-ENT(K2/100))*100/60)-SI(J2<=J$2:J$402;ENT($J$2:$J$402/100)+($J$2:$J$402/100-ENT($J$2:$J$402/100))*100/60;ENT(J2/100)+(J2/100-ENT(J2/100))*100/60))/M2))

D'ores et déjà un grand bravo et un gros merci.