Comptage des heures : sup jour / sup nuit / nuit

[Mathieu18]

Bonjour,


Il y a quelques mois j'avais posté sur cette problématique dont Cisco (le magnifique) m'avait merveilleusement sortie.

Je reviens vers vous car mon tableau de planning ne fonctionne plus sur une ligne / 2 colonnes d'heure (exemple 6:00 13:30)

mais sur 2 lignes / 2 colonnes
(exemple 6:00 10:00
14:00 18:00)

Du coup je n'arrive pas à réajuster ces 3 formules (heure de nuit - heure sup de nuit - heure sup de jour) bien trop complexe pour mon niveau.

Je me tourne donc à nouveau vers vous en essayant de vous fournir en pièce jointe le meilleure exemple possible de mon cas.

Par ailleurs je pense que cette formule dans ces 2 déclinaisons peut être très très utile pour beaucoup.


Je vous remercie.

Mathieu.

 

[Mathieu18]

Cisco bonsoir,

je suis franchement désolé je ne vois ta réponse qu'à l'instant, j'ai loupé le mail de notification.
Je viens de tester sur une cellule à priori ça à l'air d'être bon, je te remercie.

J'essaie de finir dans la soirée la réadaption tout ceci sur l'ensemble des cas et la boucle sera quasi bouclé. Je te remercie vivement Cisco.

Je dis quasi car je me suis aperçu d'un petit souci avec la formule Indirect qui rend le tout inexploitable, mais à priori il s'agit vraiment d'un point de détail qui ne devrait poser aucun problème.
Néanmoins je vais me pencher dessus pour essayé de le résoudre par moi même afin de progresser et surtout ne pas trop te déranger.
J'attendais de pouvoir finir ce que tu viens de me solutionner pour attaquer ce dernier point.

Je reviens vers toi et vous autres pour poster le travail fini.

Merci encore.

 

[Mathieu18]

Cisco, vous autres, bonsoir;

Re-actualisation de mon dernier post, j'ai finalement réussi à résoudre encore quelques points mais il en demeure 1 qui est je pense juste un petit détail rapide mais qui malheureusement me dépasse.

Lorsque l'une ou les 2 lignes des plages horaires sont vides, les formules renvoient un #REF. Je pense que cela vient des plages nommées et notamment de la fonction Indirect.
Cela rend malheureusement le tableau inexploitable.

Merci d'avance et désolé pour ma sur-sollicitation, j'essaie de faire au mieux.

 

[CISCO]

Bonsoir

J'essaye de te faire ça ce soir ou demain....

@ plus

 

[CISCO]

Rebonsoir

C'est normal que cela ne fonctionne pas. Tu as dû remarquer que dans la formule du post #7, il y a trois noms :
* période en min 1ère ligne qui est utilisé lorsqu'il ni y a des données que dans la première ligne
* période en min 2nde ligne, lorsqu'il y a des données que dans la 2nde ligne
* et période en min deux lignes, lorsqu'il y a des données dans... les deux lignes.
Cette formule est construite sous la forme SI(test1; formule(période en min 1ère ligne );SI(test2;formule(période en min 2nde ligne);formule(période en min deux lignes))), donc, si test1 est vrai, seul période en min 1ère ligne est utilisé, si test1 est faux et que test2 est vrai, seul période en min 2nde ligne l'est. Et si test1 et test2 sont faux, seul période en min deux lignes sert.

Les formules que tu proposes n'utilisent que le dernier nom, donc, cela ne fonctionne que lorsque les deux lignes sont remplies. Etonnant, non !!!


J'essaye de te modifier tout cela... mais cela me semble bien compliqué.

@ plus

 

[CISCO]

Bonsoir

C'est galère à faire, il me semble, parce que la troisième partie de la formule, prend en charge les deux lignes en même temps, donc, ne peut pas simplement prendre en compte un "", un "R" ou un "H" sur la première ligne, et un "", un "R" ou un "H" sur la seconde ligne. Je crains qu'il ne faille changer de méthode pour prendre le cas avec les deux lignes renseignées. La nuit portant parfois conseil...

@ plus

 

[Mathieu18]

Oui, je pensais bêtement que si une des 2 lignes était vide, il ferait 0 + le résultat de l'autre ligne.


Plusieurs pistes de réflexions si ça peut aider :

1 - Démultiplier les SI(ET(OU, ce que je suis en capacité de faire, en démultipliant les 9 cas actuel dans 4 autres grand cas
SI(ET(Ligne 1 = ""; Ligne 2 = >0); avec tous les cas possible, formule2ndligne
SI(ET(Ligne 1 = >0; Ligne 2 = ""); avec les tous les cas possible sur formule1ereligne
SI(ET(Ligne 1 = >0; Ligne 2 = >0) avec les tous les cas possible sur formuleen2lignes
SI(ET(Ligne 1 = ""; Ligne 2 = "");""
Cela ferait un imbriquement de SI x 27 à priori.
Pas la méthode la plus élégante, dans l'espoir qu'elle génère pas trop de lag, mais si on a pas d'autres solutions je m'y mets dès demain.


2 - Pour revenir à l'idée initiale des "-" avec la formule N().
Dans un autre onglet de mon tableau , je calcule simplement la totalité des heures sup.
L'association des "-" et N() permet à la formule de calculer soit des heures soit des valeurs 0. Ce qui à pour bénéfice de ne pas à mettre des SI(ligne 1; "-") / SI (ligne 2; "-")....
Du coup pas de souci de cases vides, pas de souci avec des données non numériques, pas de SI à gérer.

Malheureusement dans la formule qui nous concerne la valeur 0 fausse le résultat, y aurait il une combine pour que la valeur 0 ne perturbe pas le calcul, quitte à condamner l'horaire 00:00, tant pis.


Merci Cisco.

 

[faceplate]

Pas si simple que cela. Regarde la pièce jointe et dis moi si cela convient.


_____________________________________________
стоматологическое оборудование

 

[Mathieu18]

Bonjour,

Faceplate, merci mais il n'y a pas de pièce jointe ^^ !


Pour info : j ai commence a travaille sur l imbriquement de 3 Si pour chaques Cas.

Si que ligne du haut Estnum -> calcule que la ligne du haut
Si que ligne du bas ...
Si les 2 lignes ...

Ca fonctionne et ca se cale assez facilement pour le moment, l incovenient est que dans la formule finale on passe de 10 SI à 28.
Si le tableau final reste fluide, je m en contenterai.

Si y a la solution miracle, je prends aussi ^^.


Je vous remercie.

 

[CISCO]

Bonjour

Dans les premiers fichiers, il ni avait que 3 cas à étudier. Avec les "", les "R" et les "H", cela fait 3 * 9 = 27 cas possibles, d'où tes 28 SI. Pas évident...

@ plus

 

[Mathieu18]

Oui, je travaille dessus là.

Au final il y a que les 5 cas contenant "" à retravailler, les autres ne peuvent avoir de lignes vides (exemple : si "H" et "H" forcément qu'il y aura des heures dans chaques lignes).
On sera donc sur 20 SI.

Ca se goupille bien pour le moment, je finis, je combine puis je mets à l'essaie par mes collègues, une fois assuré que cette fois ci ça tourne sans erreur, je reviens poster le résultat final ici pour partage.

Merci beaucoup beaucoup pour ton aide Cisco, vraiment et excuse moi encore pour la sur-sollicitation. Je te rendrai hommage à la présentation du nouvel outil de planning !

Merci.

 

[CISCO]

Bonjour

Est-ce que tu pourrais vérifier les résultats de la formule matricielle de la colonne I, et uniquement de cette colonne ? Tu peux mettre des "R" et des "H" dans la colonne F.

Si c'est bon, ce que j'espère, il n'est pas très difficile ensuite de faire les autres colonnes, en changeant principalement des < en >, et réciproquement.

@ plus

 

[Mathieu18]

Franchement bravo, c'est impressionnant !

Tout à l'air de se calculer parfaitement. Le seul petit hic que j'ai pu trouver c'est quand toutes les plages horaire sont en "-" cela mets #VALEUR!.
Naïvement je serai tenté de mettre un SI(ET("-";"-");"00:00". Mais peut être qu'encore ça serait trop simple !

Du coup reste J; K et L à calquer, j'essaie de m'y pencher d'ici ce soir en espérant arriver à interpréter tout cela, à moins que cela soit une formalité très rapide pour toi de terminer les 3 autres cellules, mais ne te dérange pas.

Par ailleurs, je vous partage mon travail en cours sur la méthode que j'ai pu trouver moi pour résoudre tout cela, c'est moins technique mais y a du coeur et de l'envie ! ^^ Excel 1 résultat, 10 000 chemins !


Cisco encore une fois merci mais aussi... bravo, faute d'avoir pu jouer au foot avec Zidane, j'aurai eu la chance de faire du Excel avec toi.

 

[CISCO]

Bonjour

Effectivement, je n'ai pas prévu le cas sans aucune donnée...
Tu peux faire dans I11, tout au début avec SI((MOD(LIGNE();2)=0)+((G11="")*(G12=""));""; à la place de SI(MOD(LIGNE();2)=0;"";

Cela sous-entend bien sûr que s'il ni a rien dans la colonne G, il ni a rien aussi dans la colonne H.

Pour les autres colonnes, j'essayerai ce soir. Cela ne devrait pas être très long, mais j'ai d'autres choses plus urgentes sur le feu...

@ plus

P.S : Merci pour la "comparaison" avec Zidane, mais il en a bien d'autres sur le forum qui maitrisent mieux le sujet que moi...
Surtout qu'avec une macro VBA, Excel est nettement plus efficace, polyvalent, rapide qu'avec des formules... Donc, il faut aussi essayer d'apprendre le langage VBA...

 

[CISCO]

Bonjour

Est-ce que tu peux tester le fichier en pièce jointe ?

@ plus

 

[Mathieu18]

Bonjour,

désolé je ne vois ton message que maintenant, la aussi raté le mail à priori.

J'ai pu voir 2 erreurs en cas 2 et cas 4, assurément un détail car les cas 3 et cas 5 eux fonctionnent alors qu'ils sont les mêmes mais ligne 1 et 2 inversée pour les symbole "H" et "R".

J'ai mis en rouge les cas concerné et le résultat qui devrait normalement apparaître.


Merci Cisco.