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

 

[Softmama]

Ha ben non finalement ça donne pas toujours le même résultat. Je regarde ce qu'il se passe.

 

[CISCO]

Bonsoir

Ne reste plus qu'à repérer dans quels cas cela ne donne pas les bons résultats... Mes formules ne fonctionnent certainement pas correctement s'il y a des périodes à cheval sur minuit (Compétition ?)... Est-ce à cause de cela ?

@ plus

 

[Softmama]

Bonsoir CISCO, Shinozak, le forum,

Je reviens pour donner des nouvelles de cette formule que j'ai donc intégrée à mon fichier de 30.000 lignes.
Deux soucis majeurs identifiés, dont l'un est rédhibitoire je pense :

• Une fois la formule entrée, je valide par Ctrl+maj+entrée et je ne reprends la main qu'après 12 minutes de calculs, là où le résultat par macro s'affiche en 48s. Le suis surpris de constater que le résultat d'une formule matricielle prend plus de temps à s'afficher que celui réalisé par macro, surtout avec autant d'écart.

1) Dans ton vrai fichier, combien de lignes as tu ? Parce que, par formule, il ne faut pas rêver, cela risque de ramer...

Cette remarque du post #4 prend tout son sens, j'étais prévenu...

• L'autre problème, est comme je le signalais, qu'il existe des différences dans les résultats, comme en témoigne cette capture d'écran partielle du fichier complet (Avant dernière colonne = macro, dernière colonne = formule), alors que dans pas mal de cas, les résultats coïncident. Cela ne provient pas des créneaux qui dépassent minuit, assez exceptionnels en pratique :

Du coup si je décortique un de ces écarts, par exemple le premier écart avec un résultat de 6.375 pour la macro contre 1 pour la formule, en filtrant sur l'installation et la date, et en enlevant les créneaux qui ne sont pas dans la plage horaire 08h-12h, j'obtiens ces 7 créneaux qui se chevauchent :

Le calcul, effectué à la main, pour chaque créneau donne :
1ère ligne = créneau de référence (8h-12h) = 1 usager (4/4h)
2ème à 6ème ligne (8h-13h à 14h) = 4h en commun = 1 usager (4/4h) * 5 lignes concernées
7ème ligne (10h-11h30) = 1h30 en commun = 0.375 usager (1.5/4h)
Le total attendu est bien 6.375 ce que me retourne par la macro.

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

est donc erronée. Il se peut que cela provienne de mon adaptation par rapport à celle que CISCO m'a proposée, mais je ne le crois pas. En gros, j'ai modifié dans sa formule initiale les valeurs du type $K$2:$K$29563 en ENT($K$2:$K$29563/100)+($K$2:$K$29563/100-ENT($K$2:$K$29563/100))*100/60, pour s'ajuster au format initial que j'utilise dans cette feuille de type HHMM.

Quoi qu'il en soit, étant donné le temps de calculs de cette formule, je pense que ça ne vaut plus le coup de creuser cette question.

Je remercie une nouvelle fois CISCO et Shinozak pour le temps passé à étudier mon problème et je passe en Résolu.

Bonne soirée.

 

[Softmama]

Oops, je viens de comprendre :

dans la formule, c'est la partie =SOMMEPROD((C2&D2&I2=...
qui pêche, le filtre dans la formule est réalisé également sur la salle (Colonne D), en plus de l'installation sportive (Colonne C) et la date (Colonne I).
En retirant cette partie, les résultats sont bien identiques (mais toujours un temps de calcul énorme) :

Pour les créneaux au delà de minuit, j'en ai une poignée et en effet, la formule ne le gère pas.
La macro non plus d'ailleurs, ce à quoi je n'avais pas prêté attention et que j'ai corrigé dans la foulée.

Voilà, c'était juste pour clore proprement le sujet et rendre à CISCO les honneurs d'être parvenu à trouver la bonne formule.

 

[CISCO]

Bonjour Softmama

Merci pour cette analyse. On voit que tu es un habitué du forum et d'Excel : On aime bien savoir pourquoi cela tourne correctement, ou pas, ce qu'à fait ou compris le demandeur...

Pour ce qui est de la durée, cela ne m'étonne pas. En fait, si les dates ne sont pas éparpillées n'importe où dans le fichier, on peut peut-être travailler sur une plage glissante, par exemple ne comprenant que 500 lignes. Ainsi, cela tournerait certainement plus vite.

@ plus

 

[Softmama]

Bonjour CISCO, le forum

En fait, si les dates ne sont pas éparpillées n'importe où dans le fichier, on peut peut-être travailler sur une plage glissante, par exemple ne comprenant que 500 lignes. Ainsi, cela tournerait certainement plus vite.

A nouveau, je te rejoins, c'est ce que fait la macro d'ailleurs, les données étant triées par date, puis par heure de début de créneau puis par heure de fin. Je récupère facilement pour chaque créneau le bon numéro de ligne où démarrer les recherches avec un =EQUIV sur la date du créneau dans la colonne des dates, et regarde les créneaux un à un jusqu'à ce que l'heure de début de créneau soit supérieure à l'heure de fin du créneau étudié. Cela optimise au mieux les recherches et ainsi la macro ne parcourt que quelques dizaines voire jusqu'à 150 créneaux environ sur les 30.000 pour chaque créneau étudié. C'est ce qui explique qu'elle s'exécute beaucoup plus vite que le calcul par formule (45s contre 12min chez moi) . Intégrer une plage glissante dans la formule me semble du coup jouable, en suivant le même principe. C'est une bonne piste me semble-t-il. Il y a chaque jour entre 0 et environ 200 créneaux, donc l'idée de tester sur une plage glissante de disons 400 créneaux, démarrant sur la ligne renvoyée par le même =EQUIV que la macro doit pouvoir renvoyer des résultats justes dans 100% des cas.

Je me penche là-dessus et te tiens informé de mes avancées.

Merci pour cette piste intéressante qui aurait dû m'effleurer à un moment.

 

[Softmama]

Re CISCO, le forum,

Je viens de m'y coller et ça a fonctionné du 1er coup.
Le résultat par formule apparaît désormais en 16s, contre 48s pour la macro.
Je suis passé par une colonne intermédiaire, pour récupérer le numéro de ligne où commencer l'analyse :
=EQUIV([@Date];[Date];0)+1

ce qui me permet de ne pas trop alourdir la formule matricielle finale qui devient (attention, ça pique, les T2 étant l'appel de la formule de la colonne intermédiaire) :
{=SOMMEPROD((C2&I2=INDIRECT("C$"&T2&":C$"&T2+400)&INDIRECT("I$"&T2&":I$"&T2+400))*(INDIRECT("J$"&T2&":J$"&T2+400)<=K2)*(J2<=INDIRECT("K$"&T2&":K$"&T2+400))*((SI(INDIRECT("K$"&T2&":K$"&T2+400)<=K2;ENT(INDIRECT("K$"&T2&":K$"&T2+400)/100)+(INDIRECT("K$"&T2&":K$"&T2+400)/100-ENT(INDIRECT("K$"&T2&":K$"&T2+400)/100))*100/60;ENT(K2/100)+(K2/100-ENT(K2/100))*100/60)-SI(J2<=INDIRECT("J$"&T2&":J$"&T2+400);ENT(INDIRECT("J$"&T2&":J$"&T2+400)/100)+(INDIRECT("J$"&T2&":J$"&T2+400)/100-ENT(INDIRECT("J$"&T2&":J$"&T2+400)/100))*100/60;ENT(J2/100)+(J2/100-ENT(J2/100))*100/60))/M2))}

En gros, j'ai juste eu à remplacer partout les $K$2:$K$29563 en INDIRECT("K$"&T2&":K$"&T2+400) où T2 est en fait =EQUIV([@Date];[Date];0)+1

ça a donné les mêmes résultats que par macro, et du 1er coup, chose rare chez moi (Colonne S par macro, Colonne U par formule) :

Je ne pense pas qu'on pourra beaucoup aller plus loin, à part réduire le nombre de créneaux glissants qui doit avoisiner les 200 ou 250 maxi en réalité, mais il est préférable que j'ai une petite marge de sécurité à ce niveau.

Merci encore.

 

[CISCO]

Bonjour

J’allais m’y mettre, mais comme c’est déjà fait... Et comme tu dis, ça ... pique...

Et en plus, si cela tourne plus vite que la macro, c'est super... Ca fait plaisir...

@ plus