Formule qui ne calcule pas comme il le faudrait
Bonjour à tous,
Ce soucis a déjà été exposé ici mais sans trouver l'aide nécessaire à sa résolution. La personne ayant essayée de m'aider a vite abandonnée et n'a pas prit en compte ma faible expérience sur Excel.
C'est un planning dans lequel il faut simplement entrer les heures de début et de fin et ça calcule automatiquement le total d'heures effectuées avec en plus une partie détails qui décompte le temps des vacations par périodes journalières.
Et le soucis est là justement. Les colonnes P:U (feuille "Janvier") ne calculent pas correctement les heures définies plus tôt et ce soucis n'apparait pas avec tous les horaires inscrits.
Par exemple :
01:00 - 06:00 = affiche 04:00 à partir de la colonne P alors qu'il devrait afficher les 05:00.
00:00 - 06:00 = affiche 06:00 à partir de la colonne P donc il compte bien les 6:00 effectuées.
23:00 de travail = il décompte bien les heures dans les colonnes P:U
24:00 de travail = les colonnes P:U ne décomptent pas les heures.
Bref, je suis complètement perdu avec mon planning et je ne trouve pas la solution pour régler ces soucis. Pourriez-vous svp m'aider en regardant mes formules et me dire ou se trouve mon soucis.
Le membre ayant essayé de m'aider me renvoyait vers un format en [h]:mm pour un affichage supérieur à 24h alors que je lui ai expliqué qu'il n'y avait pas plus de 24h dans mon planning.
Merci par avance pour votre aide.
PS : le mot de passe est "Ne pas toucher".
Bonsoir,
en colonne P remplacer :
=SI(OU($D10="";I10="";P$9="");"";SI($G10>$H10;MAX(;MIN(P$9;$H10+24/24)-MAX(P$8;$G10));MAX(MIN(P$9;$I10)-MAX(P$8;$G10))))
par
=SI(OU($D10="";I10="";P$9="");"";SI($G10>$H10;MAX(;MIN(P$9;$H10+24/24)-MAX(P$8;$G10));MAX(MIN(P$9;$H10)-MAX(P$8;$G10))))
ceci corrige le 01:00 - 06:00 de 04:00 à 05:00
@ bientôt
LouReeD
La réponse continue... par un peu de logique cérébrale !
Si le problème vient des 24h et que de toutes façons il ne peut y avoir plus de 24h00 de vacation, il suffit de tester si nous sommes dans ce cas :
si($I10=1;
en gros, 24h = une journée = 1 car sous Excel, les heures sont une décimales d'une journée, exemple :
3,5 veut dire 3 jour et la moitié d'une journée (0,5) soit 12 heures... CQFD, non ?
Donc si la valeur de la cellule testée en colonne I est égale à 1 on a donc une vacation de 24 heures donc dans le créneau de 00:00 à 06:00 on a 06:00 soit 1/4 de journée... Ca c'est pour la colonne P :
=SI(OU($D11="";I11="";P$9="");"";SI($G11>$H11;MAX(;MIN(P$9;$H11+24/24)-MAX(P$8;$G11));MAX(;MIN(P$9;$I11)-MAX(P$8;$G11))))
Pour ca colonne Q c'est presque pareil sauf qu'il faut 15 heures de créneau, c'est à dire (1 journée/ 24 heure)x15 heures de créneau
=SI(OU($D10="";P10="";Q$9="");"";SI($I10=1;(1/24)*15;SI($G10>$H10;MAX(;MIN(Q$9;$H10+24/24)-MAX(Q$8;$G10));MAX(;MIN(Q$9;$H10)-MAX(Q$8;$G10)))))
Pour la colonne R il en va de même mais avec un créneau d'une durée de 3 heures c'est à dire 1/8 de 24:00 donc 1/8 de 1 donc 1/8... :
=SI(OU($D10="";Q10="";R$9="");"";SI($I10=1;(1/8);SI($G10>$H10;MAX(;MIN(R$9;$H10+24/24)-MAX(R$8;$G10));MAX(;MIN(R$9;$H10)-MAX(R$8;$G10)))))
Je cherche maintenant à comprendre le fonctionnement des colonnes S T et U
De mon coté je me souviens de m'y être déjà penché sur votre problème... mais ce n'est pas mon travail de répondre ici, et j'ai également une vie de famille... alors il se peut que les aides soient ponctuelles, continues, soutenues... Bref, en règle générale je fais ce que je peux, mais ça peut prendre du temps
@ bientôt
LouReeD
Re
en s'arrachant un peu les cheveux j'arrive à ceci :
si 24 heures et que heure de départ jour 1 est égal à 00:00, les 24 heures ont lieu lors du jour 1, donc "reliquat" sur le lendemain = 0
Donc la formule en colonne S en ligne 11 est la suivante :
=SI(OU(R10="";S$9="");"";SI(ET($I10=1;$G10>0);SI($G10>=$H10;MAX(;MIN(S$9;$H10+24/24)-MAX(S$8;$G10));MAX(MIN(S$9;$H10)-MAX(S$8;$G10)));0))
Le test surligné permet s'il est positif de faire le décompte des heures du jour suivant, sinon on affiche 0.
Donc si on prend l'exemple d'une journée complète partant de 15:00 et se terminant à 15:00 on aura en cellule :
P10 : 06:00
Q10 : 15:00
R10 : 03:00
S11 : 06:00 (de minuit à 06:00 le lendemain du jour 1 de la ligne 10)
T11 : 09:00 (de 06:00 à 15:00 le lendemain du jour 1 de la ligne 10)
U11 : 00:00 (le temps de travail ne parvient pas à ce créneau car il commence à 21:00 et on s'arrête à 15:00)
Formule en colonne T ligne 11
=SI(OU(S11="";T$9="");"";SI(ET($I10=1;$G10>0);SI($G10>=$H10;MAX(;MIN(T$9;$H10+24/24)-MAX(T$8;$G10));MAX(;MIN(T$9;$H10)-MAX(T$8;$G10)));0))
Formule en colonne U ligne 11 :
=SI(OU(T11="";U$9="");"";SI(ET($I10=1;$G10>0);SI($G10>=$H10;MAX(;MIN(U$9;$H10+24/24)-MAX(U$8;$G10));MAX(;MIN(U$9;$H10)-MAX(U$8;$G10)));0))
sur le fichier joint seule les formules des lignes 10 et 11 sont modifiées...
@ bientôt
LouReeD
Bonsoir LouReed,
Quelques part, c'est le boulot de personnes de répondre ici. Je suppose qu'il n'y a que du bénévolat ici un peu comme je le fais au niveau hardware pour aider d'autres personnes sur d'autres forums informatique. Et c'est très gentil à ceux qui apportent leurs aides.
Pour en revenir à mon planning, j'ai bien testé ta formule et effectivement, ça à l'air de régler le soucis sur les heures qui ne s'affichaient pas complètement.
Sauf pour la partie 24h et là, j'avoue que j'ai pas tout compris à ce que tu as dit. Faut avouer que moi et l’arithmétique, ça a toujours fait deux.
Pourrais-tu stp faire le changement dans le planning pour la partie 24h afin que je fasse la comparaison des formules avec mon planning actuel et que je puisse mieux comprendre ce que tu voulais m'expliquer. Je t'en remercie par avance.
Pour le fonctionnement des colonnes S,T et U, c'est simple.
Si je bosse par exemple de 21:00 à 09:00 donc 12h au total. Il y aura la partie 21:00 à 00:00 dans les colonnes P,Q et R pour le jour même et 00:00 à 09:00 dans la colonne S,T et U car fait partie du lendemain de la journée commencée au niveau du travail.
Ce qui veut dire par exemple si je fais cet horaire aujourd'hui, je commence aujourd'hui (9.12.15) à 21h et fini le lendemain (10.12.15) à 09h.
Suis-je clair dans mes explications car je sais que des fois, on ne me comprend pas.
Merci en tout cas pour ton aide dans l'élaboration de mon planning
Mince, je n'avais pas vu ton troisième post. Je regarde ça tout de suite
Je viens de tester ton fichier test et je rencontre un soucis.
Dans partie que tu as coloré en vert, j'ai fait le test des 24h et je vois bien le décompte des heures sauf que ça ne décompte pas bien car ça compte 32h au lieu de 24h.
sur la ligne 11 en colonne STU il est normal sur mon fichier qu'il n'y est que 23h !
En effet la vacation à commencée à 23h00 le jour d'avant, donc il y a bien que 23h pour le lendemain...
Par contre faut-il qu'il n'y est que 1h à compter sur les colonnes PQR ?
@ bientôt
LouReeD
J'avais testé sur un 24h en 08:00-08:00 pour obtenir ce 32h.
Dans ton exemple de 23:00-23:00, oui il ne fait que 1 dans la partie P,Q et R et il faut même précisément 1:00 dans la colonne R puisque c'est une heure en soirée entre 21:00 et 24:00.
Alors là, il va falloir attendre
bonne nuit...
@ bientôt
LouReeD
Ok pas de soucis pour l'attente.
Je viens de regarder et compter plus en détails et des choses m'échappent. J'ai repris ton tableau modifié et vider le tableau.
J'ai ensuite mit en 10 un 23:00-23:00 soit 24h de travail.
J'obtiens un total de 24h de travail mais dans les détails (P:U), il y a 41h
Il devrait laisse la colonne Q vide et non avec 15:00.
Bonjour,
c'était le but de ma dernière question...
En effet la tranche 00:00 23:00 est comptée deux fois !
En colonne P il devrait rien y avoir, en colonne Q il devrait rien y avoir et en colonne R il devrait y avoir 1...
@ bientôt
LouReeD
Bonjour,
C'est tout à fait ça le soucis
Bonsoir,
pourquoi parlez vous de soucis ?!
Ha si je commence à comprendre....
Je regarde ça ce soir si j'ai un peu de temps...
@ (très) bientôt
LouReeD
Ci joint le fichier avec les formules modifiées dans les colonnes vertes (lignes 10,11 et 12 pour les colonnes STU)
A vous de voir si cela correspond à votre attente, si c'est le cas un copier coller de formules devrait suffire pour la transposée sur les lignes du dessous, en prenant garde à votre niveau en Excel, vous devez savoir faire ceci, non ?
@ bientôt
LouReeD
Bonsoir LouReed,
Merci pour avoir regardé le soucis. Je viens de télécharger le fichier et je regarde dans la nuit en faisant différents tests.
Je reviens après dire si c'est bien ça
Merci à toi en tout cas pour l'aide que tu m'apportes. Et oui, je sais maintenant comment mettre la même formule sur une colonne entière.
Je viens de faire plusieurs tests et comme souvent, un problème résolu entraîne un autre problème
Le soucis maintenant se situe sur les colonnes V:AC
Ces colonnes récupèrent les heures travaillées et les décomposent statistiquement en journée et nuit pour les jours de semaine, les dimanches et jours fériés ainsi que pour finir sur le mois entier.
Et là, ça ne compte plus comme il le faut.
Exemple :
En vidant le tableau et en mettant un 23:00-23:00 en C10, il y a donc 01:00 le jour même et 23:00 le lendemain.
V et W récupère bien les 15h de jour et les 8h de nuit soit 23h sur les 24h. Par contre, l'heure du jour même en jour férié n'est pas compté en AA.
C'est le même problème hors jour férié. Ça ne prend jamais en compte l'heure du jour même. (Suis-je assez clair dans mon exemple et mes explications ?)
J'ai beau regardé les formules et je ne vois pas ce qui cloche. La formule va bien cherché le contenu des colonnes mais au final n'arrive pas à les ramener. J'ai l'impression aussi que ce problème est lié à la colonne R. Dans mes tests, c'est les heures contenues dans la colonne R qui ne sont pas récupérées par V:AC
Bonsoir,
Un essai...
Comme avant seules les lignes en vertes sont modifiées
@ bientôt
LouReeD
Bonsoir LouReed,
Je commence déjà par te remercier pour se que tu fais pour moi.
Je vais tester ton fichier pour voir si tout fonctionne et je te tiens au courant. Je vais faire des tests sur différentes plages d'horaires possibles et te tient au courant dans le week-end.
Cordialement...
Yann
PS: j'aimerais bien comprendre ce que tu as fait exactement. Tu pourrais m'expliquer stp afin que j'essaie de comprendre.
Merci par avance
Bonjour,
En fait sur les colonnes PQR je me suis rendu compte que malgré l'absence de valeur visuellement, il existe une valeur négative... dans certaines combinaisons.
C'est ces valeurs négatives qui devaient poser problème me suis-je dis, du coup dans les formules de ces trois colonnes j'ai rajouté la condition : "si ce calcul d'heure est < à 0 donc négatif, alors j'affiche 0, sinon j'affiche le résultat du calcul"
ce qui donne :
=SI(OU($D10="";I10="";P$9="");"";SI(SI(ET($I10=1;$G10<>$H10);1/4;SI($G10>=$H10;MAX(MIN(P$9;$H10+24/24)-MAX(P$8;$G10));MAX(MIN(P$9;$H10)-MAX(P$8;$G10))))<0;0;SI(ET($I10=1;$G10<>$H10);1/4;SI($G10>=$H10;MAX(MIN(P$9;$H10+24/24)-MAX(P$8;$G10));MAX(MIN(P$9;$H10)-MAX(P$8;$G10))))))
où le test du calcul négatif est surligné...
Il y a certainement plus simple, mais je dois avouer que je suis perdu avec les fonction MIN et MAX...
Donc je suis parti de votre formule de départ en mettant des "rustines", il y a peut-être un moyen plus "simple" en reprenant tout à zéro...
Sur les colonnes STU le problème n'intervient pas car dans les formules il y a déjà des "0" de prévus... Enfin, vos tests nous le diront
Il y a aussi le "=" en plus dans la formule qui n'existait pas à l'origine :
SI($G10>=$H10;MAX(MIN(P$9;$H10+24/24)-MAX(P$8;$,
mais je ne sais plus quand il est apparu...
@ bientôt
LouReeD
Bonjour LouReed,
Merci pour tes explications même si à vrai dire, j'ai pas tout compris mais c'est normal je pense vu mon niveau en Excel.
J'ai fait quelques tests sur ton dernier fichier et je rencontre un autre soucis sur le total calculé dans différentes catégories
Je comprend rien car c'est les même formules et formats que d'autres colonnes qui elles calculent bien.
Il s'agit des cellules :
S41, W41, Y41, AC41, W7, Y7 et AC7.
Quand on regarde la première cellule qui est S41, ça ne calcule pas alors que la R41 calcule bien et c'est la même formule SOMME ainsi que le même format. Et c'est pareil pour celles citées ci-dessus.
Sauriez-vous d’où ça peut venir ce soucis ?
Bonjour,
donc en fait il faut aussi faire le test du négatif sur les colonnes ST et U !
ci-joint le fichier à tester...
Ci joint une image qui explique comment connaître le résultat d'une formule qui ne s'affiche pas...
en premier en bas de l'image on sélectionne la cellule où se trouve la formule, ensuite en de l'image on clique sur l'icône "fonction" qui affiche une fenêtre qui elle affiche la décomposition de la formule ainsi que le résultat, et on voit bien sur le dernier fichier que vous avez posté la négativité de la valeur...
@ bientôt
LouReeD