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.

 

[hoerwind]

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

Bonjour et bienvenue sur le forum,

Avec un tableau intermédiaire il ne devrait pas il y avoir de gros problèmes, mais sans je ne vois pas ... encore.

 

[eriiic]

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

Bonjour,

Ca ne serait pas plus simple si tu saisissais les heures et que le 0/1 se mettent tout seuls ???


Un début de réponse :

AC4: =SI(SOMME($D4:$AA4)=0;"";MOD(MIN(SI(($D4:$AA4)*TRANSPOSE(LIGNE($1:$24))*(($C4:$Z4)=0)=0;9^9;($D4:$AA4)*TRANSPOSE(LIGNE($1:$24))*(($C4:$Z4)=0)))+3;24)/24)

AD4: =SI(SOMME($D4:$AA4)=0;"";MOD(MIN(SI(($D4:$AA4)*TRANSPOSE(LIGNE($1:$24))*(($E4:$AB4)=0)=0;9^9;($D4:$AA4)*TRANSPOSE(LIGNE($1:$24))*(($E4:$AB4)=0)))+4;24)/24)

AE4: =SI(SOMME($D4:$AA4)=0;"";MOD(MAX(($D4:$AA4)*TRANSPOSE(LIGNE($1:$24))*(($C4:$Z4)=0))+3;24)/24)

AF4: =SI(SOMME($D4:$AA4)=0;"";MOD(MAX(($D4:$AA4)*TRANSPOSE(LIGNE($1:$24))*(($E4:$AB4)=0))+4;24)/24)

toutes sont des formules matricielles à valider avec shift+ctrl+entrée

Si 1 plage d'ouverture : la 2nde répète la 1ère. On peut le masquer avec une MFC
Si 2 plages d'ouverture : tout va bien
Si 3 plages d'ouverture : la 1ère et la dernière sont affichées. Pas d'idée pour celle du milieu pour l'instant. Petite.valeur() et grande.valeur() n'ont pas l'air d'aimer les matricielles...
en VBA ça serait plus facile.

eric

 

[Victor21]

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

Bonjour à tous.

Puisque c'est fait ...J'ai ajouté une colonne 3:00 et 4:00, la 3° plage n'est pas calculée.
1° Ouverture

=SI(SOMME($D4:$AC4);INDEX($D$2:$AC$2;0;EQUIV(1;$D4:$AC4;0));"")

2° Fermeture

=SI($AD4="";"";INDEX(DECALER($D$2:$AC$2;0;SI($AD4="";0;ARRONDI.SUP($AD4*24;0)-2));0;EQUIV(0;DECALER($D4:$AC4;0;SI($AD4="";0;ARRONDI.SUP($AD4*24;0)-2));0)))

3° Ouverture

=SI(AE4="";"";SI(SOMME($D4:$AC4)>ARRONDI.SUP(($AE4-$AD4)*24;0);INDEX(DECALER($D$2:$AC$2;0;SI(AE4="";0;ARRONDI.SUP(AE4*24;0)-2));1;EQUIV(1;DECALER($D4:$AC4;0;SI(AE4="";0;ARRONDI.SUP(AE4*24;0)-2));0));""))

4° Fermeture

=SI($AF4="";"";SI(SOMME($D4:$AC4)>ARRONDI.SUP(($AE4-$AD4)*24;0);INDEX(DECALER($D$2:$AC$2;0;SI(AF4="";0;ARRONDI.SUP(AF4*24;0)-2));1;EQUIV(0;DECALER($D4:$AC4;0;SI(AF4="";0;ARRONDI.SUP(AF4*24;0)-2));0));""))

 

[Al1C21]

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

Merci.

Merci bien d'avoir regardé.
Une solution avec tableau intermédiaire (sur une autre feuille) pourrait peut-être me convenir. La contrainte risquant alors d'être la taille du fichier d'origine (plus de 70000 lignes, quelque 37 Mo).
Mais si ça permet d'avancer...

Merci encore.

 

[Al1C21]

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

Bonjour,

Ca ne serait pas plus simple si tu saisissais les heures et que le 0/1 se mettent tout seuls ???


Un début de réponse :
...
eric

Merci beaucoup, tes propositions m'intéressent !
Je ne peux pas modifier le fichier d'origine, il ne dépend pas de moi.

Je ne connais quasiment pas VBA, aussi je ne peux m'y lancer directement sur un fichier comme celui-là (plus de 70000 lignes, pour 37 Mo).

Je vai creuser un peu tes propositions.
Merci encore.

 

[Al1C21]

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

Merci Patrick.

Je vais comparer tes propositions avec celles d'eriiiic.

Est-ce qu'entre les deux façons de faire l'une serait plus lourde que l'autre (en terme de temps de calcul, pour plus de 70000 lignes) ?

Merci encore.

 

[Victor21]

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

Re,

Loin de moi l'idée de défendre ma solution* (qui peut sans aucun doute être affinée) mais les matricielles sont beaucoup plud gourmandes en ressources, et sur 70 000 lignes... Oufff...
L'idéal serait sans aucun doute une macro, ou au moins une fonction personnalisée (mais cela sort de mon domaine de compétences).


* Quoi que 😛

 

[Victor21]

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

Bonsoir à tous,

En PJ, le calcul des trois plages.
🙂

 

[eriiic]

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

Re,

Hé bé, pas mal victor, comme quoi c'est possible ;-)
C'est clair que des matricielles sur 60000 lignes ça ne va pas le faire....

J'ai abandonné la voie formule et fait par macro, que tu vois ce que ça donne al1C21.
Tu peux garder le fichier avec la macro ouvert, ouvrir un autre fichier de données, la macro traitera la plage que tu lui désignes sur cet autre classeur.
J'ai ajouté un raccourci clavier pour l'appeler : Ctrl+t

eric

 

[hoerwind]

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

Bonjour,

Pour 70 000 lignes je crains aussi qu'une solution par formules ne puisse convenir, même si elles sont non matricielles.
Mais faut-il nécessairement conserver ces 70 000 lignes dans un même fichier, pourquoi ne pas le tronçonner puisqu'il n'y a aucune relation entre les lignes ?

En pièce jointe un essai par tableau intermédiaire avec formules extra simples.
Il me semble aussi qu'une solution sans tableau intermédiaire soit possible, un peu comme Victor l'a proposé, mais cela fera toujours 420 000 formules !

 

[R@chid]

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

Bonsoir @ tous,
Une macro peut être plus rapide,
mais cette formule peut être plus jolie 😛🙂 (Juste Pour le Fun)
Colonne Intermédiaire, en AB4 :

=0&D4&E4&F4&G4&H4&I4&J4&K4&L4&M4&N4&O4&P4&Q4&R4&S4&T4&U4&V4&W4&X4&Y4&Z4&AA4&0

@ tirer vers le bas

Formule en AC4:

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

@ tirer vers le bas et vers la droite

Aucune validation Matricielle...

Voir PJ

Amicalement

 

[Al1C21]

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

Bonjour, et merci à tous !

J'avoue ne pas avoir encore eu le temps de regarder en détail chacune des propositions.
D'autant plus que le demande a un peu évolué : il s'agirait d'une période de 24 h, entre 4h du matin et 4 h du lendemain, par pas d'une demie-heure !

Je vous tiens au courant...
Merci encore.

Alain

 

[Victor21]

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

Bonjour à tous.

Supprimé, car n'apporte rien au fil.

 

[Al1C21]

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

Merci Patrick,

Ta version avec calcul des 3 plages me va bien. Je verrai pour une adaptation à des plages d'une demie-heure.

La proposition de Rachid est aussi bluffante, par le subterfuge intermédiaire utilisé !

Cordialement.