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.