Calcul d'un coût journalier basé sur des plages horaires

Demande de l'utilisateur : Calculer le coût journalier avec des temps de travail pouvant être à cheval des plages horaires aux taux différents.

>La période de travail pouvant être dans une plage ou à cheval sur 2 voire 3 plages horaires et à cheval sur 2 jours (exemple : Débuter à 21h et Finir à 7h) !

Plage et taux correspondants :

• 06:00 à 20:00 à 40 €
• 20:00 à 22:00 à 50 €
• 22:00 à 06:00 à 60 €

Question récurrente, traditionnellement résolue pas de monstrueuses imbrications de fonction SI.

Je vous propose une solution via les tableaux dynamique (matrice).

Principes

• On travaille sur une durée de 48h allant de 0h à 48h.
  • Quand l'heure de Fin est inférieure à l'heure de Début (à cheval sur 2 jours), on va ajouter 24h à l'heure de Fin.
    Exemple : Débuter à 21h et Finir à 7h donnera Débuter à 21h et Finir à 31h (31-21 est bien égale aux 10h de travail).
• On considère donc 2x4 plages horaires et non 3 plages.
  • Jour1 : 00:00-06:00, 06:00-20:00, 20:00-22:00, 22:00-24:00,
  • Jour2 : 24:00-30:00, 30:00-44:00, 44:00-46:00, 46:00-48:00 (Plage Jour1 + 24h ).
• Dans tous les cas, on va calculer le coût sur les 8 plages horaires (s'il n'y a pas de travail dans une des plages, on aura simplement un coût à 0 €).
• La difficulté est de déterminer la durée du travail à prendre en compte pour chaque plage horaire.

Formule servant de base logique pour la formule finale (ici pour le créneau 06:00>20:00)

=MAX(0;MIN(HeureFin;"20:00"*1)-MAX("6:00"*1;HeureDebut))

• "20:00"*1 permet d'obtenir la valeur correspondant à 20h pour Excel (0,833333).
• Avec MIN( HeureFin ; "20:00"*1 ), on prend l'heure de fin la moins tardive mais limité à 20:00.
• Avec MAX( "6:00"*1 ; HeureDebut ), on prend l'heure de début la plus tardive mais limité à 06:00.
• Le 1er MAX sert à mettre à zéro la valeur négative obtenue quand on est complètement hors créneau.

Résultat selon les cas (rappel, la plage utilisé ici est 06:00>20:00) :

• Début : 07:00, Fin 10:00 => MAX( 0 ; 10:00 - 07:00) = MAX( 0 ; 03:00) = 03:00
• Début : 05:00, Fin 10:00 => MAX( 0 ; 10:00 - 07:00) = MAX( 0 ; 03:00) = 03:00
• Début : 07:00, Fin 21:00 => MAX( 0 ; 20:00 - 07:00) = MAX( 0 ; 13:00) = 13:00
• Début : 04:00, Fin 05:00 => MAX( 0 ; 05:00 - 06:00) = MAX( 0 ; - 01:00) = 00:00 (hors créneau 06:00>20:00)
• Début : 22:00, Fin 23:00 => MAX( 0 ; 20:00 - 22:00) = MAX( 0 ; - 02:00) = 00:00 (hors créneau 06:00>20:00)

• Utilisant une formule matricielle, les fonctions MAX et MIN ne sont pas utilisables.
  • Elles aplatissent les matrices, c'est à dire elles ne renvoient qu'une valeur et non une matrice de MIN/MAX.
• On va les remplacer par des fonctions Si.
  • Exemple : MIN( HeureFin ; "20:00"*1 ) <=> SI( HeureFin < "20:00"*1 ; HeureFin ; "20:00"*1)

Formule finale proposée

=LET(d;A3;
     f;B3;
     fC;f+(f<=d)*1;
     mPlH;{6.20.22.24}/24;
     mPlHc;ASSEMB.H(0;mPlH;mPlH+"24:00"*1);
     mTx;{60.40.50.60};
     mTxC;ASSEMB.H(mTx;mTx);
     mSeq;SEQUENCE(;NBVAL(mTxC));
     md;INDEX(mPlHc;;mSeq);
     mf;INDEX(mPlHc;;mSeq+1);
     m;SI(fC<mf;fC;mf)-SI(d>md;d;md);
     SOMME(SI(m<0;0;m)*24*mTxC)
)

• d : Heure de départ.
• f : Heure de fin.
• mPlH : Matrice des plages horaires.
  • /24 pour convertir les valeurs en durée Excel.
• mPlHc : Matrice des créneaux horaires corrigée (ajout du 0 et des créneaux du Jour2).
• mTx : Matrice des taux correspondant aux créneaux horaires.
  • On a répété 60 € pour correspondre aux plages 00:00-06:00 et 22:00-24:00.
• mTx : Matrice des taux corrigée (ajout d'une 2eme série de taux).
• mSeq : Matrice allant de 1 au nombre de créneaux horaires ici { 1 . 2 . 3 . 4 . 5 . 6 . 7 . 8 } (8 créneaux).
• md : Matrice des heures de début à prendre en compte (bornée à un minimum dépendant du début du créneau).
  • INDEX permet de renvoyer les valeurs de mPlHc sans prendre la dernière valeur, c'est à dire les bornes gauches des créneaux (les débuts).
• mf : Matrice des heures de fin à prendre en compte (bornée à un maximum dépendant de la fin du créneau).
  • INDEX permet de renvoyer les valeurs de mPlHc sans prendre la 1ère valeur, c'est à dire les bornes droites des créneaux (les fins).
• m : Matrice des durées calculés.
• Résultat :
  • On remplace les valeurs négative de m par 0.
  • On multiplie par 24 les durées pour les passer en 100eme d'heure.
  • On multiplie ces durées par les taux horaires correspondants pour avoir le coût de chaque créneau.
  • On additionne ces coûts pour avoir le coût global.

Exemple : Plage 4h-2H

On obtient : 4h * 60€ + 14h * 40 + 2h* 50€ + 2h * 60€ = 1020 € (22h).

Merci pour votre attention bienveillante.