Formule pour faire une somme "mobile" sur calendrier
Bonjour à tous,
je cale une fois de plus sur un de mes projets.
Je ne connais pas le procédé pour demander à excel de faire une somme de jours en se déplaçant sur un calendrier.
Je m'explique :
Pour mon employeur, un employé ne peut pas être malade plus de 365 jours sur les 3 dernières années (1095 jours) sous peine d'avoir de gros soucis administratif (possibilité d'être mis à la retraite anticipée pour incapacité de travail).... donc j'aimerais faire un outil qui comptabilise les jours.
le tableau doit faire la somme des 1095 jours précédents à partir de la date du jour.
exemple :
nous sommes le 30/11/16, il faudra que le système me donne la somme des jours de maladie depuis le 30/11/13 jusqu'à aujourd'hui.
Demain, le système devra faire glisser la période (de 1095 jours) d'un jour...et ainsi de suite pour tous les jours
évidemment se pose le problème des années bissextiles... je sais pas si l'on peut en tenir compte dans la formule ? mais bon c'est pas encore trop grave.....
le résultat de la somme doit apparaitre dans la cellule J2
je joins le fichier au message
merci d'avance
Salut
Avez-vous essayé la fonction : NB.SI
EN
=COUNTIF(C5:AN370;"MM")
FR
=NB.SI(C5:AN370;"MM")
Salut,
avec votre formule, on fait le total des jours de tout le tableau.... ce n'est pas cela que je recherche.
ce que je veux c'est que le tableau me donne le nombre de maladie sur les 1095 jours précédent à aujourd'hui et demain, cette période (de 1095 jours) doit switcher d'un jour.... Donc demain, je ne dois plus avoir le total des jours depuis le 30/11/13 mais bien depuis le 01/12/13
Salut David,
voici une première version à l'arrache de ton calcul.
Tu cliques n'importe où pour démarrer le calcul.
Il faudra évidemment adapter quelque chose de plus joli et/ou fonctionnel selon ton environnement de travail!
A+
Salut,
Moi je suis parti sur une plage dynamique sans macro avec une formule de ce type...
=NB.SI(INDIRECT(ADRESSE(EQUIV(A2;$B$5:$B$370;0)+4;EQUIV(ANNEE(A2);$D$4:$AO$4;0)+3)&":"&ADRESSE(EQUIV(A1;$C$5:$C$370;0)+4;EQUIV(ANNEE(A1);$D$4:$AO$4;0)+3));"MM")Bon, comme je me remets à excel, c'est pas probant.
Je vais passer pour deux formules décaler, une pour le bas du tableau et l'autre pour le haute.
Tu nous dis si la proposition en macro de curulis57 te conviens.
Si t'es pas à l'aise avec les macros alors je continuerai.
Cordialement,
Leakim
Re,
Voici le fichier.
J'ai ajouter deux trois truc pour que cela soit utilisable dynamiquement.
Je suis désolé cela a changé les couleurs
Leakim
Bonjour
avec une formule
=SI(ET(MOIS(A1)=1;JOUR(A1)=1);NB.SI(DECALER($B$5:$B$370;;EQUIV(ANNEE(A1);$C$4:$AN$4;0)-2;;3);"MM");NB.SI(DECALER($B$4;EQUIV(TEXTE(A1;"mmmm");$A$5:$A$370;0)+JOUR(A1)-1;EQUIV(ANNEE(A1);$C$4:$AN$4;0)-3;370-(EQUIV(TEXTE(A1;"mmmm");$A$1:$A$370;0)+JOUR(A1)-2));"MM")+NB.SI(DECALER($B$5:$B$370;0;EQUIV(ANNEE(A1)-2;$C$4:$AN$4;0);;2);"MM")+NB.SI(DECALER($B$4;;EQUIV(ANNEE(A1);$C$4:$AN$4;0);EQUIV(TEXTE(A1;"mmmm");$A$5:$A$370;0)+JOUR(A1);1);"MM"))Cordialement
curulis57 a écrit :Salut David,
voici une première version à l'arrache de ton calcul.
Tu cliques n'importe où pour démarrer le calcul.
Il faudra évidemment adapter quelque chose de plus joli et/ou fonctionnel selon ton environnement de travail!
A+
bonjour curulis,
j'ai essayé ta solution, mais ça coince.....
en réalité, il y a 90 jours de maladie sur les 1095 derniers jours, je le ai compté manuellement.
ici, on me dit qu'il en a 119.
De plus si j'ajoute un jour de maladie dans la période des 1095 jours, il ne se comptabilise pas dans le total
bien à toi
leakim a écrit :Re,
Voici le fichier.
J'ai ajouter deux trois truc pour que cela soit utilisable dynamiquement.
Je suis désolé cela a changé les couleurs
Leakim
Salut leakim,
toi par contre ta solution fonctionne comme je l'espérais. Si j'ajoute ou je retire un jour dans la période des 1095, le total varie comme espéré.
si j'ajoute un jour en dehors de la période des 1095 jours (avant ou après), ça ne varie pas, parfait !
par contre ton total n'est pas juste avec la réalité... tu en trouves 74 alors qu'il y en a 90....
bien à toi
Re,
Je suis admiratif d'Amadeus qui trouve le résultat sans rien touché au fichier
Pour ma part je trouve bien 90 dans deux echelles de dates
Leakim
Salut David,
petite erreur lors de la boucle sur la première année de la période, effectivement.
Le résultat passe alors à 90, comme espéré!
J'en ai profité pour implémenter une fonction d'encodage d'un jour de maladie sur un clic : clic, malade, clic, annulé et recalcul.
Comme je ne connais pas ta façon de travailler, la procédure devra sans doute être adaptée à tes habitudes.
A+
@curulis, leakim et amadéus,
votre solution tient-elle compte des années bissextiles ?
plus simplement, est-ce que les cellules hachurées de la ligne 64 sont-elles comptabilisées dans le décomptes des 1095 jours ?
bien à vous
Salut David,
ma version en tient compte!
A+
Salut,
Pour ma version, non
Cordialement,
Leakim
curulis57 a écrit :Salut David,
ma version en tient compte!
A+
curulis,
pense-tu que tu pourrais retirer la fonction qui écrit automatique MM (et qui colore la cellule en gris) quand on clique ?
car je trouve cela plutôt contraignant....
merci
Bonsoir David,
Bonsoir le forum,
pas de souci, je peux comprendre!
En plus, quelques initiatives qui, toutes, s'éliminent aussi sec si ça t'embête :
- pour t'en laisser la possibilité, j'ai installé un CommandButton (rouge-vert) qui autorise ou pas la saisie automatique ;
- j'affiche en haut à gauche la date de prise en compte des 3 ans en arrière ;
- j'actualise l'affichage de cette période de 3 ans en ajoutant/supprimant la couleur grise des cellules concernées.
Le déclenchement de l'actualisation de l'affichage et du calcul est pour l'instant tributaire d'un premier clic.
En fonction de ton environnement de travail (comment gères-tu les données de tous les employés?), il faudra évidemment adapter tout ça.
A+
Autres suggestions :
- créer ta BDD employés dans une seconde feuille... BDD où s'inscrivent sur une ligne toutes les dates de maladie de chaque employé ;
- chaque entrée (nouvel employé, nouvelle date) est automatiquement triée ;
- quelques ListBox pour choisir l'employé à afficher, la liste des employés "critiques", ceux "à terme",... ;
- gadget mais, pour chaque année affichée, le nb de jours de maladie ;
- masquer les années en dehors de la plage de calcul ou mieux encore, commencer ton tableau par l'année en cours et repousser ce tableau vers la droite d'année en année pour garder à gauche la période qui y'intéresse ;
- ...
Profite que j'ai encore l'envie de travailler là-dessus! Quand je serai passé à autre chose, ce sera plus difficile de me rattraper!
Salut Curulis,
merci pour l'aide.... voisin, je viens de voir qu'on habitait le même coin ! lol
curulis57 a écrit :- pour t'en laisser la possibilité, j'ai installé un CommandButton (rouge-vert) qui autorise ou pas la saisie automatique ;
c'est chouette, chacun peut ainsi choisir sa méthode
curulis57 a écrit :- j'affiche en haut à gauche la date de prise en compte des 3 ans en arrière ;
parfait !
curulis57 a écrit :- j'actualise l'affichage de cette période de 3 ans en ajoutant/supprimant la couleur grise des cellules concernées.
Au départ les cellules grisées permettaient de bien dissocier les jours MM et les jours normaux.
Mais ton idée de faire coulisser la zone grise jour par jour permet de mieux cerner la période et est bien mieux, j'adopte ! lol
curulis57 a écrit :En fonction de ton environnement de travail (comment gères-tu les données de tous les employés?), il faudra évidemment adapter tout ça.
ce fichier xls est destiné à mon usage personnel ainsi qu'à tous mes collègues qui n'ont plus, comme moi, confiance en l'administration de cette société
pas nécessaire vu que le fichier sera individuel pour chaque agent qui me le demandera.curulis57 a écrit :Autres suggestions :
- créer ta BDD employés dans une seconde feuille... BDD où s'inscrivent sur une ligne toutes les dates de maladie de chaque employé ;
- chaque entrée (nouvel employé, nouvelle date) est automatiquement triée ;
- quelques ListBox pour choisir l'employé à afficher, la liste des employés "critiques", ceux "à terme",... ;
curulis57 a écrit :- gadget mais, pour chaque année affichée, le nb de jours de maladie ;
ça c'est fait
curulis57 a écrit :- masquer les années en dehors de la plage de calcul ou mieux encore, commencer ton tableau par l'année en cours et repousser ce tableau vers la droite d'année en année pour garder à gauche la période qui y'intéresse ;
peut-être beaucoup de travail pour le confort apporté, c'est vrai que de cette manière la J2 restera visible tout le temps, mais là pour le moment je la vois jusque 2040... serai certainement mort ou retraité d'ici là
Par contre, en relisant la réglementation de la société, je suis tombé sur un autre point important concernant les maladies.
Je ne sais pas si on peut incrémenter cela dans ce que tu as déjà fait, je développe :
Après une certaine période de maladie, variable selon l'ancienneté, un agent tombe à 80% de son traitement lorsqu'il est en maladie.
Ce quota de jours revient à zéro dès que l'agent accumule une période de 90 jours consécutifs sans être en maladie.
les périodes selon l'ancienneté sont :
Trait. 80% si MM>180 j (- de 15 ans d'ancienneté.)
Trait. 80% si MM>270 j (15 à 25 ans d'ancienneté.)
Trait. 80% si MM>365 j (+ de 25 ans d'ancienneté.)
exemples concrets :
j'ai 17 ans d'ancienneté, donc MM>270 jours
1) je suis : 70 jours MM / je travaille 25 jours / 50 jours MM / je travaille 60 jours / 100 jours MM / je travaille 50 jours / 51 jours MM ------> 70 + 50 + 100 + 51 = 271 jours -----> vu que je n'ai pas eu de période d'au moins 90 jours consécutifs sans MM, je tombe à 80% de mon traitement
2) je suis : 70 jours MM / je travaille 25 jours / 50 jours MM / je travaille 60 jours / 100 jours MM / je travaille 100 jours / 75 jours MM ----> 70 + 50 + 100 = 220 jours MM, après je ne suis plus MM au moins 90 jours (100 dans cet exemple) donc le quota MM retombe à zéro le 90ème jour ----> je tombe malade 75 jours, mon nouveau quota est de 75 MM -----> mon traitement reste à 100%
Donc pour parler simplement, dès que le système détecte une zone de 90 jours (ou cellules) consécutifs sans MM ----> remise à zéro (remise à zéro rien que pour ce calcul, pas pour les 365 jours/3 dernières années)
Sur la feuille Maladie, j'ai ajouté la possibilité de mettre la date d'embauche en case N2.
De cette manière, on pourrait faire apparaitre : Trait. 80% si MM>180 j (- de 15 ans d'anc.) , Trait. 80% si MM>270 j (15 à 25 ans d'anc.) ou Trait. 80% si MM>365 j (+25 ans d'ancienneté.) dans la cellule F39 de la feuille 2017.
Toujours sur cette feuille 2017, la cellule M39 accueillerait la somme des MM en cours calculé sur la feuille Maladies
Et la cellule M40 afficherait 100% en vert et 80% en rouge lorsque le quota est dépassé (en fonction de l'ancienneté, ne pas oublier ce point)
voilà j'espère ne pas avoir été trop brouillon et que ce soit réalisable bien sûr..... sans abuser, mais cela dépasse mes compétences actuelles...
j'ai encore constaté deux choses, minimes me semble-t-il :
1) quand j'ai voulu verrouiller les cellules (autres que C5 à AN370) de la feuille Maladies, j'ai reçu un message d'erreur (peut-être à cause de la macro) au moment où je tente de vouloir modifier une cellule verrouillée ou même écrire dans une cellule non -verrouillée !!!
2) je n'arrive pas à lier le tableau de gauche (en haut) de la feuille 2017 à la colonne 2017 de la feuille Maladie
En gros quand je mets mes jours de maladie sur mon planning de la feuille 2017, j'aurais souhaité qu'il s'affiche automatiquement dans la feuille Maladie, histoire de pas devoir introduire deux fois les données et ainsi éviter les erreurs.
Maintenant cela est peut-être dû aussi à la macro de la feuille Maladie....
Mais alors quid des années suivantes.... car moi pour 2018, je compte reprendre la 2017 et la remettre vierge, mais alors j'ai peur que la feuille 2018 agisse toujours sur la colonne 2017 de la feuille Maladie.....
Bon j'en ai assez raconté et merci à tous ceux qui ont eu le temps de me lire
edit : oups j'ai oublié de joindre le fichier, le voici
ps: il faut faire abstraction de la feuille 2016, ça vaut plus la peine de faire quelque chose pour elle vu la date que l'on est...
Salut David,
ta BDD peut-être utile, même si elle ne concerne que quelques uns de tes collègues proches! Soit!
Un détail : la période de maladie prise en compte avant diminution du salaire à 80% commence quand? Depuis le premier jour de la période d'ancienneté atteinte (suffisamment de données?), depuis le premier jour des 3 ans en arrière, ... ?
Année en cours en 1ère colonne à gauche : oui, non? Pas clair!
A+
curulis57 a écrit :ta BDD peut-être utile, même si elle ne concerne que quelques uns de tes collègues proches! Soit!
en faite mon boulot n'est pas de faire de la gestion de personnel, c'est juste que j'ai pas mal de soucis administratif avec mon employeur et que je me fais une petite boite à outils pour vérifier qu'il n'y ait pas (ou plus) d'erreur. Du coup comme je suis généreux, je refilerai mon fichier à tous ceux qui le voudrait à titre personnel, donc je n'ai pas besoin d'établir une base de données avec les données des collègues, chacun gère son petit fichier. moi après je ne ferai que leur fournir les feuilles vierges pour les années suivantes
curulis57 a écrit :Un détail : la période de maladie prise en compte avant diminution du salaire à 80% commence quand? Depuis le premier jour de la période d'ancienneté atteinte (suffisamment de données?), depuis le premier jour des 3 ans en arrière, ... ?
ça c'est une très bonne question...
je réfléchis..... Chaque collègue à la possibilité de consulter l'historique de son planning une bonne année en arrière sur son lieu de travail, donc il lui suffira de compléter le tableau le plus qu'il peut... de plus, il a la possibilité de demander l'historique de ses maladies au centre médicale au moins 3 ou 4 ans en arrière. De toute façon, si le collègue veut que le calcul des 365 jours (cellule J2) fonctionne, il devra compléter le tableau au moins 3 ans en arrière.
Donc pour le départ, j'aurais imaginé un peu ce que tu as fait pour l'autre calcul : aujourd'hui - 90 jours et la zone avance tous les jours d'un jour. Dès que le système n'aperçoit plus de MM dans sa zone de 90 jours, il remet le compteur à zéro. Mais attention, il remet à zéro ce quota de jours, pas celui des 365j (cellule J2) car ça c'est une autre contrainte qui n'a rien à voir avec celle-ci des 80%
Vu que les 365 jours et les 90 jours se chevauchent, pour y voir plus clair pourrait-on imaginer les 90 derniers jours dans une couleur (rose par ex.) et les 275 autres jours les laisser en gris ?
Comme ça à la lecture du tableau, on sait que la zone rose c'est les 90 jours et que les 365 jours c'est rose+gris..... ou alors y a peut-être moyen de laisser les 365 en gris et faire la bordure des 90 dernières cellules en gras et rouge par ex ?
PS: donc la cellule que j'ai mis reprenant la date d'embauche ne sert que pour calculer la fourchette de MM autorisé avant de tomber à 80%
curulis57 a écrit :Année en cours en 1ère colonne à gauche : oui, non? Pas clair!
et bien disons que si les colonnes qui disparaissent à gauche sont récupérables en glissant le curseur vers la gauche, alors oui pas de soucis tu peux mettre la colonne de l'année en cours comme première colonne visible à gauche en regard de jours
OK, c'est parti! 8)
A+
curulis57 a écrit :OK, c'est parti! 8)
A+
Salut curulis,
penses-tu, tant qu'on y est, que ce serait possible que toutes les cases de la période 365 jours soient grises car on départ j'avais fait cela pour dissocier les MM des non-MM.... mais maintenant quand la fin de la période arrive sur une série de MM, on ne distingue pas convenablement la fin de la période... donc du coup je me demandais si ce serait pas mieux que les 365 cellules mobiles soient grises ?
merci d'avance
David