Comparer des plages de temps...

[kopto]

Bonjour ! Je dois réaliser l'encodage des durées de productions et d'arrêts d'une usine de friandises.

J'ai 2 tableaux :

1. Les produits fabriqués, de telle heure à telle heure, la durée que ça comprends.
   ( ex : Chocolats de 06:00 à 14:00 (total 8h) et Caramèles de 14:00 à 22:00 (total 8h) )
   
2. Les arrêts survenus sur la ligne de fabrication, de telle heure à telle heure, et la durée.
   ( ex : arrêt de 07:00 à 07:30 (total 30 min) et de 13:50 à 14:25 à (total 35 min) )

Je dois dans le tableau des produits fabriqués, trouver le temps total des arrêts, et la durée effective de production (c'est à dire en déduisant les arrêts de la durée de fabrication).

J'ai essayer avec SOMME.SI.ENS, mais impossible de comparer les plages d'heures... Et puis si il y a un arrêt qui s'étale sur 2 produits (comme dans mon exemple), il faut rajouter des calcules...

Je me demandais donc si il existe une formule capable de donner la durée commune entre une plage horaire d'arrêts et une plage horaire de production ?
Du style SOMME de la colonne des arrêts selon si l'heure de début et de fin d'arrêt se trouve (ou combien s'y trouve) entre l'heure de début et de fin de production ...

Un grand merci d'avance, car je suis bloqué dessus depuis une semaine... J'ai bien essayer d'intégrer la référence du produit concerné dans le tableau des arrêts, mais déjà la machine ne connait pas cette variable

Edit : Ci-joint un fichier de à quoi ressemble le travail...

 

[Victor21]

Bonjour, kopto. Et bienvenue sur XLD.

[...]Je me demandais donc si il existe une formule capable de donner la durée commune entre une plage horaire d'arrêts et une plage horaire de production ?[...]

Et que vous-êtes vous répondu ?
Blague à part, les fonctions natives d'Excel permettent de soustraire une durée d'une plage.
Pour vous aider, un court extrait de votre fichier (sans donnée confidentielle) nous aiderait à intégrer vos paramètres (disposition, format des données, ...) et à tester nos éventuelles propositions avant de vous les soumettre.

 

[Dranreb]

Bonjour.
On devrait pouvoir y arriver à coups de SOMME(MIN(MAX(…Je ferais bien des essais de formules sur votre classeur mais vous ne l'avez pas joint, et je n'ai pas envie de saisir vos chiffres d'exemples dans un nouveau classeur.

 

[kopto]

Désolé, voila j'ai joint le classeur Merci à tous !

J'ai mis en jaune où je dois entrer le nombre d'arrêts, et la somme des arrêts...
Ce qui permet de calculer la durée de production net en orange (durée brut - arrêts)

 

[Victor21]

Re,

Concernant le nombre des arrêts, essayez en F4 : =NB.SI.ENS(Tableau2[de];">="&[de];Tableau2[à];"<="&[@à])
L'organisation des données n'est pas très pratique : il eût été préférable d'affecter les arrêts à chaque production.

Pour les durées, je soupçonne la nécessité de l'utilisation d'une formule matricielle, ou de quelques lignes de code VBA (hors de ma portée)
Quant à la gestion des 2 références produites simultanément, je ne vois pas

 

[Dranreb]

Pas réussi à le faire avec des formules…
Dans un module standard :

Option Explicit
Function NombreCoupures(ByVal HDébProd As Double, ByVal HFinProd As Double, ByVal RCoupures As Range) As Double
   Dim TC(), L&, HDébC#, HFinC#
   TC = RCoupures.Value
   For L = 1 To UBound(TC, 1)
      HDébC = TC(L, 1): If HDébC < HDébProd Then HDébC = HDébProd
      HFinC = TC(L, 2): If HFinC > HFinProd Then HFinC = HFinProd
      If HFinC > HDébC Then NombreCoupures = NombreCoupures + 1
      Next L
   End Function
Function SommeCoupures(ByVal HDébProd As Double, ByVal HFinProd As Double, ByVal RCoupures As Range) As Double
   Dim TC(), L&, HDébC#, HFinC#
   TC = RCoupures.Value
   For L = 1 To UBound(TC, 1)
      HDébC = TC(L, 1): If HDébC < HDébProd Then HDébC = HDébProd
      HFinC = TC(L, 2): If HFinC > HFinProd Then HFinC = HFinProd
      If HFinC > HDébC Then SommeCoupures = SommeCoupures + HFinC - HDébC
      Next L
   End Function

En F4, à propager sur 4 lignes :

=NombreCoupures([@de];[@à];Tableau2[[de]:[à]])

En G4, à propager sur 4 lignes :

=SommeCoupures([@de];[@à];Tableau2[[de]:[à]])

 

[Victor21]

Re, bonjour Dranreb.
Peut-être :
- en F4 : =NB.SI.ENS(Tableau2[de];">="&C4;Tableau2[à];"<="&D4)
- en G4 : =SOMME.SI.ENS(Tableau2[Durée];Tableau2[de];">="&C4;Tableau2[à];"<="&D4)
A vérifier avec d'autres données

 

[job75]

Bonsoir kopto, Patrick, Bernard,

Formule en F4 :

=NB(Tableau2[Durée])-SOMMEPROD(([@de]>=Tableau2[à])+([@à]<=Tableau2[de]))

Formule en G4 :

=SOMME(Tableau2[Durée])-SOMMEPROD(([@de]>=Tableau2[à])+([@à]<=Tableau2[de]);Tableau2[Durée])

Fichier joint.

J'ai vérifié : les résultats sont les mêmes que ceux de Patrick et de Bernard.

A+

 

[Victor21]

Re,

Et pour le calcul automatique du prorata (en L4) :
=K4/SOMMEPROD((Tableau1[produite])*(GAUCHE(Tableau1[Ref. Article];4)=GAUCHE(A4;4)))

 

[Dranreb]

Je pense que mes fonctions perso pourraient donner des résultats différents si, un peu indépendants des périodes de production, certains arrêts, tel que des pannes de courant, je ne sais pas, pouvaient être à cheval sur deux productions différentes. C'est surtout parce que je tenais à retirer de celles ci les arrêt mordant sur elles, partiellement ou totalement, que je ne voyais pas comment le faire par formules.