PB sur excel, help me svp Fermé

Question

Bonjour,

Etant un débutant sur excel je n'arrive pas à réaliser la chose suivante: 
Dans le cadre de la plannification de visites médicale de mon entreprise, j'essaie de donner suivant un statut (c'est à dire absent, reprise, apte, inapte) et la date d'embauche la prochaine convocation par la visite médicale. Ainsi, j'obtenais le résultat suivant: =SI(D2="SMS";C2+730;SI(D2="SMR";C2+365;SI(D2="abs";C2+183;SI(D2="embauche";C2+15))))
sachant que D2 représente le statut, et C2 la date d'embauche.

Mais malheureusement, pour le statut "reprise" la date qui est pris en compte est celle du début de la reprise et non celle de l'embauche , ainsi je voudrais savoir s'il était possible d'ajouter à ma formule du dessus la fonction aujourd'hui auquel on rajoutera + 8jours?? 

Je vous remercie de votre aide.

Mike-31 · Answer

Salut,

En reprenant ta formule, tu peux l'écrire comme cela
=SI(D2="SMS";C2+730;SI(D2="SMR";C2+365;SI(D2="abs";C2+183;SI(D2="embauche";C2+15;C2+8))))

Mais pour être pointu avec les dates, il conviendrait de l'écrire comme cela
=SI(D2="SMS";DATE(ANNEE(C2);MOIS(C2);JOUR(C2)+730);SI(D2="SMR";DATE(ANNEE(C2);MOIS(C2);JOUR(C2)+365);SI(D2="abs";DATE(ANNEE(C2);MOIS(C2);JOUR(C2)+183);SI(D2="embauche";DATE(ANNEE(C2);MOIS(C2);JOUR(C2)+15);SI(D2="reprise";DATE(ANNEE(C2);MOIS(C2);JOUR(C2)+8))))))

Pour incérer AUJOURDHUI() dans la formule, c'est possible, il suffit de remplacer C2 par AUJOURDHUI()
L’inconvénient, si tu dois archiver, la date changera tous les jours sur l'ensemble des formules, comme d’ailleurs lorsque tu changera la date en C2, pour fixer durablement il faudra passer par le code VBA et c'est la longueur de la formule même si en la raccourcissant un peu, exemple 
=SI(D2="SMS";DATE(ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI());JOUR(AUJOURDHUI())+730);SI(D2="SMR";DATE(ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI());JOUR(AUJOURDHUI())+365);SI(D2="abs";DATE(ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI());JOUR(AUJOURDHUI())+183);SI(D2="embauche";DATE(ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI());JOUR(AUJOURDHUI())+15);SI(D2="reprise";DATE(ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI());JOUR(AUJOURDHUI())+8))))))

Parfois les longues formules ont des séparateurs qui changent, si c'était le cas je t'enverrai un fichier

A+

Raymond PENTIER · Answer

Bonjour.
Je n'ai pas compris ta question. En particulier le mélange que tu fais entre embauche, reprise et 8 jours ...
Peux-tu joindre ton fichier (en remplaçant les noms propres) et en indiquant à la main les bonnes réponses qui devraient s'afficher ?

lucho04 · Answer

Voila, j'ai un peu avancé, par contre il faut que j'ajoute le cas absent, et que j'améliore le cas reprise. sinon je  vois pas comment insérer mon fichier??

Mike-31 · Answer

Salut,

Avec ce lien et tu colles dans un post le lien généré en haut de ta page.

https://www.cjoint.com/

Sur le fichier que tu joindras veilles qu'il n'y ai pas de notes confidentielles mais n'hésites pas d'ajouter des annotations pour nous aiguiller dans ta demandes


A+

lucho04 · Answer

Merci pour l'aide,

donc voici le lien :
https://www.cjoint.com/?ffmdCEKffy

En suivant les conseils de plusieurs personnes, je suis arrivé à ce tableau là.  J'ai changer la disposition initiale car il était trop complexe d'associer certaines valeur.
Ainsi, lorsque le statut de la personne est SMR la date de la prochaine visite est de +1an suivant la date d'embauche, pour le statut SMS elle est de +2ans suivant la date d'embauche. Ensuite suivant le cas d'une reprise si la personne est malade + de 21 jour alors la prochaine visite a lieu 8 jour suivant la date de reprise.
Il me reste à rajouter le cas absent où la date de visite est décalé de 6mois, d'ajouter  la condition des accidents du travail pour les visites de reprise.

Par contre j'aimerais faire une prévision pour les prochaines années (c'est à dire prochaines visites, N+1, N+2,...) mais je ne vois pas comment faire.

Je vous remercie de votre aide et des réponses que vous m'accordé

Mike-31 · Answer

Re,

Dans ta formule, par exemple AUJOURDHUI()+730) pour aujourd’hui + 2ans ou + 365 cette formule te donnera le résultat décalé d’un jour toutes les années bissextiles il conviendrait d’écrire même si la formule s’allonge
DATE(ANNEE(AUJOURDHUI())+2;MOIS(AUJOURDHUI());JOUR(AUJOURDHUI()))

La même chose pour six mois par exemple

DATE(ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI())+6;JOUR(AUJOURDHUI()))

Pour tes prévisions

En supposant que tu les fasses par rapport a une date contenue dans une cellule exemple en N2 et que tu souhaites afficher la prévision a 3 ans par exemple

=DATE(ANNEE(N)+3;MOIS(N2);JOUR(N2)) 

A+

Raymond PENTIER · Answer

Bonjour, lucho04.
D'une manière générale, il convient d'être très précis et rigoureux quand on fait de l'informatique, notamment sur Excel, et en particulier quand on soumet un problème pour lequel on espère une réponse. D'accord ?
Depuis ton post initial du 30 avril, tu dis (post 5) avoir suivi les conseils de plusieurs personnes et changé la disposition du tableau ; ça peut expliquer certaines incohérences ...
Dans ton premier post tu évoquais les statuts Absent, Reprise, Apte et Inapte en ajoutant Embauche dans ta formule. 
Or, dans ton fichier joint au post 5, en colonne C tu n'as que 2 statuts (SMS et SMR). Qu'en est-il exactement ? Et dans les 23 lignes données en exemple tu n'as nulle part renseigné les colonnes J (jours maladie) et F (reprise) pour nous indiquer ce qu'on est censé trouver dans ces cas-là ...
Que vient faire la fonction AUJOURDHUI() dans tes formules ? Chaque jour où tu ouvres ce fichier, les dates de visite changent ! C'est ingérable !
Enfin, l'agent qui n'aura jamais eu d'arrêt maladie de plus de 21 jours ne bénéficiera que d'une seule visite médicale durant toute sa carrière (au bout de sa première année s'il est SMR ou de la deuxième s'il est SMS) ?
Dernière question (pour l'instant) : Pourquoi afficher le contenu des colonnes I à L ? Le tableau devrait s'arrêter à la colonne H, non ? Si les colonnes suivantes peuvent servir à déterminer la date de la prochaine visite, il faudra les masquer avant d'imprimer ou de distribuer le tableau ; c'était ton intention ?
* J'attends tes explications pour essayer d'avancer un peu.

Mike-31 · Answer

Salut,

Une référence circulaire, c'est lorsque l'on se sert d'une cellule dans une formule alors que cette cellule fait partie de la formule et fait souvent c'est une erreur de formulation et Excel la dénonce. l'utilisation des références circulaires est spécifique.

Pour en revenir à nos moutons, je pense qu'il serait utile de remettre sur le forum ton exemple définitif avec des explications comme si dans cette cellule j'ai ça, je souhaiterai avoir ce résultat dans cette cellule et si j'ai ça il me faut ça etc...
Si tu as déjà une référence circulaire c'est que ta structure à changée, et comme le signale Raymond et je t'avais déjà mis en garde sur mon post 1 la fonction AUJOURDHUI () fera que tes dates changerons chaque jour

A+

lucho04 · Answer

Bonjour,
Oui effectivement entre le premier post et le dernier il y a beaucoup d'incohérence suite à certains changement réalisé et je m'en excuse. Ainsi je serais plus minutieux pour les post futurs. 

Donc voici le tableau sous sa forme final:
http://cjoint.com/data/fhf5WSmeRh.htm

Donc dans se tableau je voudrais planifier les visites médicales de mon entreprises. Il faut que je précise avant tout qu'il existe 3 types de visites médicales:

la visite d'embauche qui a lieu au plus tard avant la fin de la période d'essai qui suit l'embauche, 

la visite périodique qui a lieu tous les 2ans pour les salariés ayant un statut SMS(Suivit Médical Simple) et tous les ans pour les salariés ayant un statut SMR(Surveillance Médicale Renforcée). La visite périodique débutant directement après la visite d'embauche.

la visite de reprise qui fait suite à un congé maternité, à une absence pour cause de maladie professionnelle, à une absence d’au moins 8 jours pour cause d’accident du travail, à une absence d’au moins 21 jours pour cause de maladie ou d’accident non professionnelle et en cas d’absence répétées pour raison de santé. Les visites de reprise ayant lieu 8 jours maximum après la reprise du travail.

Ainsi en tenant compte de ces contraintes je voulais déterminé la date de la prochaine visite médicale.

Dès lors, dans les colonnes A et B j'y est indiqué les Noms et Prénoms des salariés,
Dans la colonne C j'y ai indiqué les 2 statuts SMS et SMR, (effectivement dans le post 1 j'avais indiqués les cas Absent, Reprise, Apte et Inapte mais j'ai préféré séparé les cas Absent et Reprise afin de faciliter la compréhension du tableau, et de supprimer les cas Apte et Inapte sachant que les statut SMS et SMR sont des équivalent du cas Apte), dès lors suivant le statut je voulais déterminé la prochaine visite médicales suivant la date de l'embauche ou de la dernière visite.

Dans la colonne D y figure une donnée qui est la date d'embauche du salarié
Dans la colonne E j'y ai mis le nombre des jours de maladies, pour pouvoir mettre la contrainte des visites de reprises, c'est-à-dire "si le nombre de jours de maladie dépasser 21 jours alors la prochaine date de la visite médicale aura lieu 8jours suivant la date de reprise du salarié"
Dans la colonne F, j'y ai mis les nombre de jour d'accident du travail et de la même manière que pour les jours de maladies je voulais mettre une formule équivalente à "si le nombre de jours d'arrêt du travail du a un accident lié au travail dépassé 8jours alors la prochaine date de la visite médicale aura lieu 8 jours plus tard de la date de reprise au travail"
Dans la colonne G, j'y ait  rajouté le cas absent, répondant par oui, c'est à dire que "si le salarié ne s'était pas présenté à la dernière visite médicale alors sa prochaine visite aura lieu 6 mois plus tard que celle prévue initialement"
Dans la colonne H, j'y ai indiqué la date de reprise du travail du salarié
Dans la colonne I,j'ai mis comme donnée, la date la dernière visite
Et dans la colonne J, jeux mettre la prévision de la prochaine visite qui doit regroupé les formules indiquée précédemment dans les différentes colonnes. Ainsi grâce aux données des colonnes C,D,E,F,G,H,I je voulais établir la date de la prochaine visite.
J'ai aussi ajouté une colonne K, pour y faire figuré les dates des prochaines visites N+1, N+2, mais je n'arrive pas à mettre la bonne formule.

Concernant la formule aujourd'hui, je pensais que s'était la meilleur solution pour rajouté 8jours voir 2 ans à une date, mais effectivement si la date change tout les jours sa va être assez difficile pour planifier. Donc quel formule me conseillez-vous afin d'obtenir une date fixe?

Sinon pour les anciennes colonnes I à L s'était pour montré le détail des formules, elles étaient donc bien inutiles.

J'espère que mes explications seront suffisantes, je vous remercie de votre aide.

Raymond PENTIER · Answer

Salut.
Tu vois : C'est beaucoup plus clair (y compris pour toi même, je pense).
Reste quelques petites précisions à apporter :
1) Pourquoi la colonne D est-elle si peu renseignée ?
2) Si la colonne D indique la date de fin de période d'essai, c'est parfait. Sinon, il manque soit une colonne avec cette date, soit la durée de la période d'essai (si elle est identique pour tous). 
3) La colonne G n'est pas fonctionnelle : le salarié N°1 a manqué sa dernière visite, d'accord ; mais le 20/10/2006 de la colonne I représente la dernière convocation à une visite, ou la dernière visite effectuée ?
4) Les colonnes E et F servent à quoi ? Si ton tableau doit servir à déterminer si une visite de reprise est nécessaire, il faudrait ajouter une colonne pour la maternité, une autre pour la maladie professionnelle, une pour l'accident non professionnel, une dernière pour le cumul des absences répétées ... ça me paraît beaucoup, et il me semble qu'il vaudrait mieux gérer séparément les motifs et durées d'absences, et reporter dans ton tableau juste le résultat de ces calculs, qui se traduit par l'inscription d'une date de reprise en colonne I. Ainsi, dans ton exemple, tu n'aurais plus la date de reprise du 22/10/2007 pour le quatrième salarié.
5) Je propose une colonne de "Contrôle de saisie" pour attirer l'attention sur les salariés pour lesquels il n'y a ni date d'embauche, ni dernière visite, ce qui interdit tout calcul. En effet il est désagréable de trouver 30/12/1901 comme prochaine visite ...
6) Pour conserver ton idée des anciennes colonnes I à L pour le détail des formules, je suggère d'utiliser effectivement des colonnes pour calculer les dates de visite d'embauche, de visite de reprise et de visite périodique, ces résultats étant ensuite combinés pour obtenir la date définitive "Prochaine visite".
&#9658; J'ai donc commencé à intervenir sur ton fichier ; ne t'inquiète pas de la ligne 1 que j'ai insérée : c'est pour donner un nom aux colonnes. &#9788; https://www.cjoint.com/?fhpQK4UqKg
-- 
C'est bien, la retraite ! Surtout aux Antilles ... :-)
&#9787; Raymond &#9794;