XL 2010 Couverture plage horaire sur un planning

[sheraf]

Bonjour,

J'aurais besoin de votre aide car je ne vois pas comment réaliser ce que je souhaite.
J'ai un planning horaire de travail hebdomadaire pour un service composé de plusieurs personnes.
Je souhaiterais avoir pour chaque jour une cellule (G11 dans le fichier ci-joint pour le lundi) qui me confirme (OK ou PAS OK) que la plage horaire totale (8h -20h) est bien couverte par la présence d'au moins une personne.

Par exemple, si j'ai deux personnes présentes:
Personne 1 travaille de 8h à 14h
Personne 2 travaille de 14h à 20h
Alors OK

OU
Personne 1 travaille de 8h à 13h
Personne 2 travaille de 14h à 20h
Alors PAS OK (il y a un trou de 13h à 14h)

Le fichier ci-joint vous montrera comment tout est présenté.

Je vous remercie par avance pour votre aide !

 

[CISCO]

Bonjour

Cf. en pièce jointe. Comme il s'agit d'une formule matricielle, il faut la valider avec les 3 touches Ctrl+maj+entrer.

Si besoin est, je t'expliquerai comment fonctionne cette formule un peu plus tard.

@ plus

P.S : Je n'ai fait le travail que dans G11.

 

[sheraf]

Rolala, merci beaucoup ! 🙂

Je crois avoir compris l'idée générale de ta formule. Mais je suis preneur d'une petite explication si tu as le temps.

Merci mille fois en tous les cas !

 

[CISCO]

Bonjour

Je vais essayer de faire court et compréhensible... Pas évident !

LIGNE(INDIRECT(8*60+1&":"&20*60)), en matriciel, liste tous les nombres de 481 à 1200 (8 h = 480 minutes, 20 h = 1200 minutes) (Si tu veux travailler avec d'autres heures d'ouverture, il faut donc remplacer le 8 et le 20 en conséquence).
Excel a pour unité de temps le jour. Donc 1:00 = 1/24. Donc, si tu veux avoir le nombre de minutes correspondant à une heure donnée sous la forme hh:mm, il faut multiplier cette valeur par 24 puis par 60.
E12*24*60 donne donc le nombre de minutes correspondant à la valeur dans E12.
(E12*24*60<LIGNE(INDIRECT(8*60+1&":"&20*60))) renvoie des VRAI pour toutes les minutes de 481 à 1200 strictement supérieures à E12 en minutes, des FAUX dans les autres cas.
(LIGNE(INDIRECT(8*60+1&":"&20*60))<=F12*24*60) renvoie des VRAI pour toutes les minutes de 481 à 1200 inférieures ou égales à F12 en minutes, des FAUX dans les autres cas.
VRAI*VRAI = 1. Tous les autres produits comprenant un FAUX donnent 0.
Nous nous retrouvons donc avec une série de 1 et de 0.
La partie décrite ci-dessus ne concernent que les minutes de présence de la 1ère personne le matin, le responsable hébergement. Il faut faire de même avec les heures correspondant à l'après midi (la ligne 14) puis pour la 2ème personne, la 3ème personne et ainsi de suite (2 lignes pour chaque personne).
Comme nous faisons la somme de tous ces 0 et ces 1, nous nous retrouvons avec une série de nombres >=1 (plusieurs personnes travaillent pendant ces minutes là) et peut être un ou des 0 (Personne ne travaille pendant ce(s) minute(s) là).

Si à la fin, on n'a que des nombres >= 1, c'est que toutes les minutes de 481 à 1200 sont comprises dans les périodes de travail considérées. EQUIV(0;... va renvoyer #NA (Excel ne trouve pas de 0 dans la liste précitée). Et ESTNA(EQUIV(0;... va renvoyer VRAI, donc "OK".
S'il y a au moins un 0, c'est qu'au moins une des minutes de 481 à 1200 n'est pas comprise dans ces périodes, et donc qu'il ni y a personne à ce moment là. Dans ce dernier cas, EQUIV(0;.... va renvoyer un nombre (la position du premier 0 dans la liste) et ESTNA(EQUIV(0;... va renvoyer FAUX, donc "ncp".

Il faut peut être vérifier ce qu'il faut faire avec les bornes des intervalles contrôlés en travaillant avec des < ou des <=. A voir en faisant des tests, en mettant des cas où il ni a personne à 8:00, mais avec quelqu'un à 8:01. Idem avec 19:59 et 20:00.

@ plus

P.S1 : La formule est longue ( 2 lignes par personne * nombre de personnes), et donc peu pratique s'il y a beaucoup d'employés (mais je ne vois pas de solution plus concise pour le moment). Le mieux serait de donner un nom à la partie LIGNE(INDIRECT(8*60+1&":"&20*60)) pour en simplifier l'écriture. En allant à la ligne avec Alt+entrer dans la barre des formules, cela facilite le travail. Reste plus qu'à faire pas mal de copier-coller...
P.S2: Il y a bien sûr d'autres possibilités. Par ex, tu peux mettre,
SI(MIN((E12 à la place de SI(ESTNA(EQUIV(0;(E12 à condition de mettre à la fin <=F34*24*60))<>0;"OK";"ncp") à la place de <=F34*24*60);0));"OK";"ncp")

 

[CISCO]

Bonsoir

Je supprime ce post, car la formule proposée ne fonctionnait pas correctement dans tous les cas. Dommage.

@ plus

 

[sheraf]

Merci bcp pour l'explication CISCO ! C'est bien plus clair ! 🙂

 

[CISCO]

Bonjour à tous

Bien que ma proposition fonctionne, est-ce que quelqu'un aurait une formule plus facile à utiliser lorsqu'il y a beaucoup d'employés, dans laquelle on peut travailler directement avec des plages (E12:E32, F12:F32 par ex) ? Perso, je ne vois pas.

@ plus