Petite formule à trouver

[May]

Bonjour à tous,

Je travaille sur des plannings et il y a une formule qu'en faite je n'arrive pas à trouver ni à imaginer

Dans mon planning ce que j'aimerai c'est de pouvoir avoir à côté du total d'heure hebdomadaire une alerte qui dit "Repos Hebdo!" lorsqu'il n'y a pas 35h consécutives de repos par semaine (on les retrouve généralement le week-end).

Du coup je n'arrive pas à voir comment je pourrai trouver cette formule.

Je vous remercie d'avance de votre aide

May

 

[CISCO]

Re : Petite formule à trouver

Bonjour

Au niveau des mises en forme conditionnelles, que te faut-il :
* la couleur orange si la durée de travail dépasse 6 h sur tous les tableaux. Par ex si on a début à 05:00 et arrêt à 12:00, cette dernière cellule doit être en orange.
* la couleur verte si la durée de repos d'un jour au suivant est inférieure à 11 h sur tous les tableaux ?

C'est bien ça ?

@ plus

P.S : Est-ce une bonne idée de mettre en vert quelque chose qui ne convient pas ?

 

[May]

Re : Petite formule à trouver

Re,

Pour les MFC peu importe la couleur en faite. Pourvu que ça tape à l'oeil lorsqu'il y a une erreur.
- pour l'exemple à 5:00, si l'arrêt est à 12:00, c'est alerte. Mais si l'arrêt est à 11h pile c'est OK.
- et oui si inférieur à 11h de repos, alerte

Merci :')

 

[CISCO]

Re : Petite formule à trouver

Bonsoir

Et comment est-ce qu'on fait pour vérifier la condition "temps de repos >=11 h" en ligne 8, puisqu'on ne connait pas l'heure de débauche du dimanche soir précédent ?

@ plus

 

[May]

Re : Petite formule à trouver

Bonsoir,

Pour l'instant on ne peut pas vérifier cette ligne puisque l'on a pas les plannings précédents mais celui -ci devra s'étaler jusqu'a la fin de l'année. Il faudra que je mette une MFC à chaque debut de semaine pour cette vérification de repos de 11h...

May.

 

[CISCO]

Re : Petite formule à trouver

Bonjour

En pièce jointe, le même fichier que dans mon post #30, après nettoyage (il y avait des centaines de MFC, et maintenant, il ni en a plus que trois ! J'ai dû faire un double du fichier, supprimer toutes les MFC et réimplanter les 3 MFC qui m'intéressaient, avec les bonnes plages d'action). La première MFC ne travaille pas sur la ligne 8 puisque qu'on ne connait pas les h de débauche du dimanche précédent, et donc que le test (11 h de repos au moins) n'aurait pas de sens, mais en lignes 18, 28 et 38.

Quelques informations :
* JOURSEM(date;type de retour) renvoie un n° correspondant au jour de la sem, dimanche, lundi, mardi, mercredi...
Ex : JOURSEM(date;1) renvoie 1 si date tombe un dimanche, 2 pour un lundi et ainsi de suite
JOURSEM(date;2) renvoie 1 pour le lundi, 2 pour le mardi... et 7 pour le dimanche
Pour comprendre ce que fait une fonction, sélectionner son nom dans la barre de formule, en haut de l'écran, puis cliquer sur fx devant cette même barre de formule.

* Pour ce qui est des formules matricielles, en W15, on a {=MAX(SI(E8:E14="";B9:B15+2-E7:E13))}, ce qui signifie qu'Excel calcule MAX(SI(E8="";B9+2-E7);SI(E9="";B10+2-E8);SI(E10="";B11+2-E9);SI(E11="";B12+2-E10);SI(E12="";B13+2-E11);SI(E13="";B14+2-E12);SI(E14="";B15+2-E13)). Cela ne fonctionne que si on fait appel à une ou des fonctions nécessitant plusieurs valeurs (ici MAX) et si on valide la formule avec les trois touches Ctrl, maj tempo et entrer.

* Tu peux copier-coller les formules que j'ai mise en C15, J15 et ainsi de suite, à condition de les coller au bon endroit.

Si tu veux étendre ce planning plus vers le bas, il ni a pas de problème du coté des formules, par contre, du coté des MFC, cela risque de donner un truc tordu. Je regarde si je ne peux pas modifier la définition de ces MFC pour que cela soit plus facile.

@ plus

 

[May]

Re : Petite formule à trouver

Bonjour,

Je ne sais même pas quoi dire.. Merci!

Je suis en train de comprendre et vérifier aléatoirement les cellules et les mises en formes conditionnelles mais ça fonctionne

Merciii Cisco!

May.

 

[CISCO]

Re : Petite formule à trouver

Re

Ci-joint, un fichier semblable au précédent. J'ai juste changé les définitions des mises en forme conditionnelles pour avoir des plages d'action beaucoup plus faciles à rédiger. En simplifiant un peu le raisonnement, j'ai pris comme plage d'action presque tout le planning, et j'ai rajouté dans la formule définissant le test, à gauche, des conditions sur les lignes et sur les colonnes, car toutes les cellules de la plage d'action ne sont pas forcément à colorier.

Autrement dit, j'ai mis un test plus compliqué, et des plages plus faciles à écrire.

J'ai aussi séparé la plupart des cellules fusionnées (celles-ci sont à éviter de préférence, dès qu'on les utilise dans des calculs ou dans des plages). Pour "Prévision", j'ai ensuite sélectionné 4 cellules contiguës, puis "Accueil" --> "Format" --> "Format de cellule" --> dans la fenêtre déroulante en dessous de "Horizontal", choisir "Centré sur plusieurs colonnes" (Ainsi, les 4 cellules en dessous son indépendantes, mais le texte est à cheval sur ces 4 cellules).

@ plus

 

[CISCO]

Re : Petite formule à trouver

Bonjour

Si tu veux agrandir ton planning vers le bas, tu peux faire comme suit avec le fichier du post #38 juste au dessus :

* Tu supprimes toutes les lignes cachées que tu as actuellement en dessous de ton tableau.
* Il me semble que tu as aussi des cellules fusionnées en dessous. Sépare les.
* Puis "Accueil" --> "Mise en forme" --> "Gérer les règles" --> "Cette feuille de calcul"
* Tu remplaces tous les 45 à droite dans les plages d'action par un nombre beaucoup plus grand, supérieur ou égal au n° de la dernière ligne de ton futur planning.
* Tu fermes la fenêtre de gestion des MFC.
* Tu copies toute la plage correspondant à ton dernier jour, A36:U45, et tu colles cela en A46.

Normalement, c'est OK ainsi, ne reste plus qu'à remplir.

Tu peux certainement faire de même en copiant-collant plusieurs jours d'un coup.

@ plus

 

[May]

Re : Petite formule à trouver

Re,

Merci pour la notice

J'ai une interrogation: le petit 1 qui se trouve en haut de chaque semaine, c'est le 1 qui renvoie au jour de la semaine? Est-ce que je peux le supprimer ou non?

May.

 

[Victor21]

Re : Petite formule à trouver

May, il faut suivre ...

J'ai une interrogation: le petit 1 qui se trouve en haut de chaque semaine, c'est le 1 qui renvoie au jour de la semaine? Est-ce que je peux le supprimer ou non?

CISCO vous a précisé ici :

Pour que cela fonctionne, il faut mettre en E7, L7, S7, E17, L17 etc un 1, utile lorsque le lundi est un jour de repos.

Pour savoir si une cellule est utilisée dans une formule, utilisez "Rechercher et sélectionner", puis "Sélectionner les cellules, puis cocher "Dépendants"

 

[May]

Re : Petite formule à trouver

Bonjour Victor21,

Oui, en cliquant sur la formule j'ai vu qu'il était intégré dedans

Merciiii et j'arrête de vous embêter! ^^)

May.

 

[CISCO]

Re : Petite formule à trouver

Bonjour à tous, bonjour Victor21

Re,
Merci pour la notice
J'ai une interrogation: le petit 1 qui se trouve en haut de chaque semaine, c'est le 1 qui renvoie au jour de la semaine? Est-ce que je peux le supprimer ou non?
May.

Comme l'a écrit Victor21...

Ceci dit, dans la formule en C15 ou en W8, si par exemple E8 est vide, la formule va calculer B9+2-E7. Si on ne met rien dans E7, cela va donner le nombre d'heures le matin avant l'embauche (B9) plus 48 h (2 fois 24 h) moins 0, donc plus de 35 h, or cela n'est pas bon.
En fait, la formule, c'est B9 + 1 +( 1 - E7), avec B9 pour les h du matin, 1 pour la ligne vide, et 1 - E7 pour les heures entre la débauche le jour précédent et minuit.
Pour le mardi, le mercredi... cela ne pose pas de problème car la cellule E correspondante existe, contient quelque chose ou pas.
Pour E7, si on laisse cette cellule vide, si E8 est vide, on obtient systématiquement pour le lundi plus de 48 h, hors, puisque tu commences le comptage des heures de repos le lundi matin, c'est au maximum 24 h, c'est comme si la débauche avait eu lieu le dimanche soir à minuit.
Donc, en mettant E7 = 1 (=minuit) on obtient B9+2-1 et le problème est résolu.

Si ces 1 te gênent, pour l'impression, il te suffit de les écrire avec une police blanche, au besoin en laissant un petit commentaire du style "ne pas effacer cette cellule".

@ plus

P.S : A tout hasard, pour les autres utilisateurs potentiels, cette méthode, travailler par rapport aux lignes vides, ne fonctionnera pas dans tous les cas. Ici cela va tel que parce qu'il ni a pas de 1/2 journée de travail, parce que le criètère, c'est <35 h, et pas quelque chose du genre < 24 h par ex. De plus, 2 lignes vides ne donnent pas le vrai MAX, mais un résultat supérieur à 48 h, ce qui suffit ici.

 

[CISCO]

Re : Petite formule à trouver

Re

Si ces 1 te gênent vraiment, je peux transformer la formule avec un test par rapport au lundi ... mais je trouvai tellement "sympa" d'avoir une formule courte... ?

@ plus

 

[May]

Re : Petite formule à trouver

Hello,

Non non, le 1 ne gene pas. Je l'ai mis en blanc et ça va puis j'ai l'impression de comprendre un peu

J'ai fait un test avec les heures à vérifier justement mais on dirait que ça ne fonctionne pas, peux-tu jeter un oeil s'il te plait?

Mercii.

May.

 

[May]

Re : Petite formule à trouver

Re,

Je pense avoir compris comment ça fonctionne.

Finalement la formule ne voit pas si les 35h de repos sont consécutives ou pas, n'est-ce pas?

May.