XL 2021 SI OU ET - rajouter une condition supplémentaire

[JoBar57]

Bonjour,

Lorsque je planifie des vacances par exemple du 1er du mois jusqu'à la fin du mois la formule ci-dessous permet de les inscrire uniquement du lundi au vendredi, mais pas les samedi ni dimanche.
Par contre pour les jours fériés c'est possible de les inscrire sur un samedi ou un dimanche. J'aurais besoin de rajouter aussi les congés maternité qui se décomptent sur l'ensemble de la semaine, week-end inclus ainsi que les jours fériés.
j'ai essayé de reproduire ce qui avait été mis en place pour les jours fériés, mais cela ne fonctionne pas. Il y a toujours une erreur. Comme la formule commence à être un peu trop complexe pour moi, est-ce que quelqu'un pourrait me dépanner ? Est-ce qu'il est possible de rajouter cette modification dans la formule actuelle ou bien faut-il la remplacer ?

=SI(OU(ET(D28<7;D28>1);SIERREUR(INDEX(Tableau1;EQUIV(1;(Planning!D25>=Tableau1[Date début])*(Tableau1[Date de Fin]>=Planning!D25)*(Tableau1[Personnel]=Planning!$V$2);0);5);"")="F");SIERREUR(INDEX(Tableau1;EQUIV(1;(Planning!D25>=Tableau1[Date début])*(Tableau1[Date de Fin]>=Planning!D25)*(Tableau1[Personnel]=Planning!$V$2);0);5);"");"")

C'est la formule qui se trouve dans l'onglet "Planning" dans les lignes 19-24-29-34-39-44-49-54-59-64-69 et 74
Dans le fichier joint on peut constater que le résultat "Mat" ne s'est pas inscrit sur les week-ends du mois de mai.

Merci

 

[vgendron]

Hello

le fichier me semble bien compliqué avec beaucoup de formules qui mériteraient d'etre "simplifiées" / "clarifiées"

déjà. comme il y a des tables structurées, il faudrait leur donner un nom explicite, comme ca, dans les formules, c'est plus clair.
ensuite..dès qu'on valide une formule, la mise à jour du fichier est super longue...

exemple de formule à simplifier
dans la feuille "Entrées", colonne "Nb de jours", la formule teste si la colonne "Symbole" contient F.. sauf que la liste des congés ne propose pas ce F==> donc à priori, le test ne sert à rien

 

[vgendron]

ensuite, il me semble qu'il faudrait rassembler certaines infos sur une meme feuille
exemple
sur la feuille Entrées, tu as un tableau "Années (colonne K)
et on retrouve le meme tableau en feuille "Extractiion" colonne U qui fait juste référence au premier..
idem pour les personnel et symboles

 

[JoBar57]

Bonjour vgendron,

Merci pour les remarques, le fichier s'étant construit sur une longue période et mon niveau de compétence pas très bon, il y a en effet des "lourdeurs".

le fichier me semble bien compliqué avec beaucoup de formules qui mériteraient d'etre "simplifiées" / "clarifiées"

Pour l'instant pas "encore" (je l'espère) dans mes compétences

dans la feuille "Entrées", colonne "Nb de jours", la formule teste si la colonne "Symbole" contient F.. sauf que la liste des congés ne propose pas ce F==> donc à priori, le test ne sert à rien

En effet, cela avait été modifié dans le fichier d'origine, mais pas dans le fichier test => rectifié

sur la feuille Entrées, tu as un tableau "Années (colonne K)
et on retrouve le meme tableau en feuille "Extractiion" colonne U qui fait juste référence au premier..
idem pour les personnel et symboles

Rectifié aussi

déjà. comme il y a des tables structurées, il faudrait leur donner un nom explicite, comme ca, dans les formules, c'est plus clair.

Je ne sais pas quoi faire avec ça. ... un exemple ?

ensuite..dès qu'on valide une formule, la mise à jour du fichier est super longue...

Cela vient de quoi exactement ?

Merci de bien vouloir éclairer ma lanterne

Fichier modifié joint

 

[vgendron]

je viens de voir ta réponse à l'instant, et pas encore ouvert ton nouveau fichier

en attendant, j'avais déjà retravaillé ton fichier

pour la lourdeur et lenteur. j'ai pas vérifié, mais peut etre que la fonction pour les jours fériés est en cause..??
ou alors, le nombre important de formules..

 

[vgendron]

Bon.. je confirme, c'est la fonction des jours fériés qui ralentit tout le fichier..

 

[vgendron]

Re

en PJ, j'ai effectué quelques modifications

sur la feuille Planning, il y en avait dans tous les sens !!
genre.. 6 cellules de calculs pour arriver à la première date..

j'ai simplifié tout ca
==> il n'y a plus de lignes cachées
==> à savoir et comprendre:
pour Excel, une date, c'est JUSTE un nombre
1 = le 01/01/1900
2 = le 02/01/1900
...
45175 = le 06/09/2023

ensuite.. le reste c'est une question d'affichage.. point barre..
donc avec le meme nombre, tu peux l'afficher sous forme date complete jj/mm/yyyy, ou juste le jour au format numéro du jour "dd" ou le NOM du jour "ddd"

à priori, sur cette feuille tu as juste à selectionner l'année en D2 (j'ai renommé cette cellule en "AnnéePlanning" pour y faire référence plus clairement dans les formules
et selectionner l'employé en V2 (idem, j'ai renommé en "PlanningEmployé"

sur la gauche, j'ai simplifié - régroupé les formules en mettant un texte pour expliquer l'information qui est récupérée..

ensuite, j'ai simplifié les formules pour former le planning
et une formule pour remplir les congés de l'employé selectionné

Feulle Extraction:
j'ai rappatrié les tables de symbole et liste des années
j'ai modifié le code (vba) pour le remplissage de la TS "t_Extraction"

Feuille "Entrées"
à part les deux tables qui ont été déplacées, je n'ai rien touché.. à part les formules pour faire référence aux noms des tables, AnnéePlanning....

après.. est ce que TOUTES les colonnes sont utiles???

Feuille Jours Fériés
==> c'est la qu'il reste du travail, car j'ai fait un copier coller special valeur pour ne plus avoir la formule TJF qui ralentit le programme..
la qestion: as tu besoin de garder la possiblité de calculer les JF d'autres pays??

 

[vgendron]

pour les jours féries, la fonction telle qu'utilisée dans le fichier te donne les Jours fériés de Suisse région Genève..==> Correct ?

 

[vgendron]

Re,

Voir en PJ
j'ai retravaillé la feuille ou tu calcules tous les nombres de jours posés, dispo restants..

j''ai regroupé les lignes par "Posés" - Dispo - Restant
et j'ai ajouté un tablo à droite pour te montrer qu'on peut tout avoir en un seul petit tablo

 

[vgendron]

Dernière version pour aujourd'hui

regarde les commentaires (bulles jaunes) dans la feuille "Jour fériés"

 

[JoBar57]

Bonjour vgendron,

Merci pour le boulot titanesque effectué, effectivement cela allège énormément, mais je vais avoir besoin de temps pour être au clair avec toutes les modifs effectuées et comprendre tout ce que tu as fait afin de le transposer sur mon fichier original.

Concernant les jours fériés, effectivement dans le cas de ce fichier il me faut juste les fériés du canton de Genève et j'ai besoin de retrouver quelque part la trace du nbre de jour en n - n-1 - n-2 et n-3 car pour quelqu'un dont l'année démarre en juin le nb de fériés change chaque année du fait du lundi de Pentecôte qui se balade entre mai et juin selon les années (Exemple pour "P").
Comment retrouver l'affichage de la liste des fériés de n-3 à n ainsi que le nb de fériés par année ?

ensuite, j'ai simplifié les formules pour former le planning
et une formule pour remplir les congés de l'employé selectionné

Effectivement ta formule est bien plus concise .
Par contre lorsque je planifie des vacances du 1er à la fin du mois je me retrouve avec des "VA" aussi sur les WE ce que je voulait justement éviter afin de garder une certaine lisibilité du planning. J'ai vu par contre que ce jours là ne sont pas comptabilisés, du coup si ça complique trop les choses on peut laisser ..... ou alors avec une mfc mettre en blanc les "VA" qui sont dans les colonnes WE ?
Pour les Fériés et les congés Mat c'est ok que cela s'inscrive aussi sur les WE

j'ai retravaillé la feuille ou tu calcules tous les nombres de jours posés, dispo restants..
j''ai regroupé les lignes par "Posés" - Dispo - Restant
et j'ai ajouté un tablo à droite pour te montrer qu'on peut tout avoir en un seul petit tablo

Ton tableau est clairement plus facile et cool à lire que mes colonnes

Concernant le "t_Critères", apparemment ça ne fonctionne plus, lorsque je modifie un ou des critères ça m'affiche toutes les lignes du "t_Congés" en réponse.

Comment fait-on pour réorganiser l'ordre des colonnes à droite du planning (Va, V-1, V-2, V-3, Fé, RFé, F-1, etc...) ?

Fichier joint avec juste quelques modifs de présentation du planning et l'exemple des "Va"

Merci encore pour le travail "d'élagage" effectué.

 

[vgendron]

Hello

pour le remplissage du planning
==> j'ai modifié la formule pour que les we ne soient pas remplis (quelque soit le type de congés)
==> les congés sont comptabilisés HORS week end

pour l'extraction: en fait, il ne faut PAS de ligne vide dans la table
dans la PJ, il y a 3 lignes de critères
si tu veux en enlever un il faut supprimer (pas simplement effacer) la ligne de la table

Dans la feuille "Planning", comme toutes les formules en haut font référence à des cellules calculées sur la feuille Jours Fériés. et que je ne m'y retrouvais pas.. j'ai choisi de donner un nom aux différentes cellules calculées de la feuille Jours Fériés
ResteFé3 pour le nombre restant de Fé en n-3 (=cellule N19)
ResteFé1 pour le nombre restant de Fé en n-1 (=cellule N21)
....
idem pour le nombre de JoursFériés a n n-1...
le nombre RFe à n n-1...

ainsi, dans la feuille planning, les formules sont plus claires.. et .. je te laisse vérifier.. mais il me semble qu'il y a des erreurs: Exemple en E10 (Nb de V en (n))==> la formule pointe sur les RFé1

 

[JoBar57]

Bonjour,

Effectivement plus clair,

Est-il possible d'utiliser "t_Fériés" pour calculer les nb de jours fériés par année et servir de référence pour matérialiser les fériés dans le planning, ou compléter la plage qui servira alors de référence ?

Concernant E10 je me retrouve avec un chiffre bizarre lorsque j'utilise rechercheV ?

 

[vgendron]

pour les jours fériés

je cherche à remplacer la fonction "TJF" qui plombe le fichier dès que je la reutilise dans la feuille "Jours Fériés"
Note: on ne peut pas la supprimer car elle est utilisée dans une MFC

tu as besoin des Jour Fériés sur 3 ans glissants ? (n ==>n-3)
as tu besoin des deux tableaux?
celui de gauche qui donne le nom des Jours Fériés
ET celui de droite qui REcalcule les Jours fériés pour les années n n-1 et n-2 ?

pour E10: qu'as tu mis comme formule? pourquoi le résultat te semble t il bizarre?

 

[JoBar57]

pour les jours fériés

je cherche à remplacer la fonction "TJF" qui plombe le fichier dès que je la reutilise dans la feuille "Jours Fériés"
Note: on ne peut pas la supprimer car elle est utilisée dans une MFC

tu as besoin des Jour Fériés sur 3 ans glissants ? (n ==>n-3)
as tu besoin des deux tableaux?
celui de gauche qui donne le nom des Jours Fériés
ET celui de droite qui REcalcule les Jours fériés pour les années n n-1 et n-2 ?

pour E10: qu'as tu mis comme formule? pourquoi le résultat te semble t il bizarre?

Pour les jours fériés il y a besoin des dates afin d'alimenter le planning et d'autre part de compter le nombre de jours fériés en fonction de l'année glissante (tableau de droite) car il peut être différent selon l'année pour ceux qui démarrent en juin.
Et effectivement depuis n-3

La formule que j'ai utilisée en E10 : j'ai vu l'erreur, c'est le numéro de la colonne, j'avais mis 5 (=RECHERCHEV(EmployéPlanning;t_Personnel;5) au lieu de 4 .
Pourtant la colonne "v selon ancienneté en (n)" est bien la 5e colonne du tableau ??

pour le remplissage du planning
==> j'ai modifié la formule pour que les we ne soient pas remplis (quelque soit le type de congés)
==> les congés sont comptabilisés HORS week end

C'est ok, sauf pour les "Fé" et "Mat" qui doivent pouvoir s'inscrire sur les WE puisque les jours fériés peuvent tomber un samedi ou dimanche et les congés maternité se comptabilisent tous les jours, WE et jours fériés inclus