Planning avec alerte sur la disponibilités horaire

Article à la suite d'une question sur un forum.

Objectif

Créer un tableau dont chaque ligne correspond à un créneau horaire sur une journée et affectée à une personne.

Avoir une colonne d'alerte permettant de savoir si le créneau de la ligne est disponible ou non pour la personne concernée.

Solutions proposées

La recherche de disponibilité ne se fera que dans les lignes précédentes. Par exemple, si les lignes 2 et 5 sont en conflit, la ligne 3 sera marquée "Disponible" et la ligne 5 sera "Indisponible".

Solution Disponibilité1 : Créneaux indisponibles verrouillés

=LET(m;FILTRE($A$1:$D1;($E$1:$E1=E2)*($B$1:$B1=B2));
n;NB(INDEX(m;0;1));
c;SI(n;
     REDUCE(0;SEQUENCE(n);LAMBDA(c;lg;c+ET(C2<INDEX(m;lg;4);D2>INDEX(m;lg;3))));
     0);
SI(c;"Indisponible";"Disponible"))

• m : Matrice des lignes correspondantes à la même date et à la même personne que la ligne évaluée.
• n : Nombre de lignes de m.
• c : Nombre de conflits
  • Pour chaque ligne de m, on détermine si les horaires de la ligne évaluée sont en conflit avec cette ligne.
  • Analyse des situations possible (Plage P1 : de début D1 à fin F1 et Plage P2 de D2 à F2):
    • 4 cas de conflit : "P1 à cheval sur le début P2", "P1 à cheval sur la fin P2", "P1 englobe P2", "P1 est englobé par P2".
    • 2 cas de non-conflits : "P1 avant P2" (F1<D2) OU "P1 après P2" (D1>F2).
  • Il est plus simple de tester 2 cas que 4 cas donc on part de cette 2^ème déclaration
    • On inverse la déclaration précédente "2 cas de non-conflits" en "2 cas de conflits" :
      "P1 pas avant P2" (F1>D2) ET "P1 pas après P2" (D1>F2).
• Résultat : Ici une forme abrégée du test de la fonction SI.
  • c égale à 0, c'est pour Excel la même chose que c = FAUX.
  • c égale à autre chose que 0, c'est pour Excel la même chose que c = VRAI.

Remarque : Cette solution peut facilement être transposée en mise en forme conditionnelle.

Solution Disponibilité2 : Créneaux indisponibles non verrouillés

Avec cette version, les lignes marquées "Indisponible" ne sont plus pris en compte pour déterminer la disponibilité.
Exemple : La plage de la ligne 2 marquée "Indisponible" ne rend plus indisponible la ligne 5.

=LET(m;FILTRE($A$1:$D1;($E$1:$E1=E2)*($B$1:$B1=B2)*(H$1:H1="Disponible"));
n;NB(INDEX(m;0;1));
c;SI(n;
     REDUCE(0;SEQUENCE(n);LAMBDA(c;lg;c+ET(C2<INDEX(m;lg;4);D2>INDEX(m;lg;3))));
     0);
SI(c;"Indisponible";"Disponible"))

• On a ajouté un critère au filtre de m.

Remarque : Cette solution peut facilement être transposée en mise en forme conditionnelle.

Solution Disponibilité3 : Identification des lignes en conflits

Cette version indique les lignes générant l'indisponibilité de la ligne. Elle nécessite la présence de la colonne N°.

=LET(m;FILTRE($A$1:$D1;($E$1:$E1=E2)*($B$1:$B1=B2));
n;NB(INDEX(m;0;1));
c;SI(n;
     REDUCE("";SEQUENCE(n);LAMBDA(c;lg;c&SI(ET(C2<INDEX(m;lg;4);D2>INDEX(m;lg;3));INDEX(m;lg;1)&", ";"")));
     "");
SI(c="";"Disponible";"Conflit ligne : "&TEXTE.AVANT(c;", ";-1)))

• Utilisation non conventionnelle de la fonction REDUCE (retourne une chaîne et non du numérique).
  • Retourne la liste des valeurs de la colonne N° correspondant aux lignes en conflits séparé par une virgule et un espace.
• La fonction TEXTE.AVANT sert à supprimer le dernier ", " gênant en fin de chaîne c.
• Une autre solution pour c ce problème de texte à supprimer : Ajouter ", " au début dans c : c&", "&SI(ET(C2<...
  • Dans ce cas on aurait dû, là aussi, supprimer le 1^er ", " de c avec une fonction TEXTE.APRES ou une fonction STXT.

Merci pour votre attention bienveillante.