Mise en forme conditionnelle dans tableau de congés payés
Bonsoir,
Je me tourne vers vous car je bloque et j'espère que quelqu'un pourra m'aider
J'ai réalisé dans le cadre de mon BTS un tableau de congés payés et absences.
J'ai plusieurs feuilles :
- Variables : J'indique dessus plusieurs légendes ainsi que les formules des jours fériés.
- Calendrier : Me permet d'avoir un visuel sur le calendrier mensuel.
- Suivi absences : Je sélectionne l'employé concerné, le type d'absence (congés payés, formation, maladie, congé paternité, etc...), la date de début et de fin, ainsi que le total de jours d'absences qui se calcule automatiquement.
- Solde : J'ai lié plusieurs formules à ma feuille suivi absences afin que tous mes soldes se remplissent automatiquement.
1. j'aimerai insérer une mise en forme conditionnelle de sorte à ce qu'en fonction du type de congé, du nom du salarié, et des dates de départ et d'arriver que je saisie dans ma feuille suivi absence, les cellules concernées se coloris automatiquement dans ma feuille calendrier. Je bloque dessus depuis un moment et je n'arrive pas du tout à trouver la formule qu'il me faut dans ma mise en forme.
2. J'aimerai insérer un commentaire sur le nom de chacun des salariés qui m'indique le récapitulatif des soldes que j'ai édité dans la feuille solde et qui se mette donc à jour automatiquement si possible.
Je ne veux pas utiliser de macros sur ce tableau, j'en ai déjà utilisé une dans un autre document Excel toujours dans le cadre de mon BTS et c'est pas forcément ce qui est le plus évident à expliquer devant le jury pendant l'examen ...
Voilà, j'espère vraiment que quelqu'un pourra me débloquer !
Je vous joins mon tableau afin que ce soit plus clair.
Bonne soirée,
Mélissa
Bonsoir,
je pense que sans VBA, la solution se trouve du coté de la fonction SOMMEPROD
@ bientôt
LouReeD
Bonsoir,
voilà le principe :
la fonction SOMMEPROD permet de faire la somme de plusieurs produits.
Ici sont utilisation est faite par des tests logiques qui vont renvoyés 1 si VRAI et 0 si FAUX, du coup si on obtient 1 c'est que toutes les conditions sont bonnes.
J'ai donc mis 9 formules à la suite pour tester les 9 positions d'absences.
Pour différencier une absence de CP par rapport à un "FOR" à chaque formule SOMMEPROD j'y ajoute un coefficient multiplicateur :
du 1 CP multiplier par 1 = 1 et un FOR multiplié par 9 = 9
Du coup votre planning se remplit de chiffres allant de 0 à 9 :
0 : aucune correspondance
1 : CP
9 : FOR
Il ne vous reste plus qu'à créer des MFC pour afficher la bonne couleur en fonction du chiffre présent dans la cellule.
Le fichier :
@ bientôt
LouReeD
Salut,
Pour compléter l'excellente proposition de Looreed
En décochant l'affichage des zéros tu obtiendras un affichage allégé
Cordialement,
Leakim
Re bonsoir,
après quelques cheveux en moins (parce que je ne suis pas si bon que ça...) voici un fichier avec une formule plus simple qui renvoie directement le type d'absence.
Ensuite, même principe, mettre en place la MFC qui correspond au code.
Le principe est presque le même :
un SOMMEPROD pour trouver s'il y a une absence en fonction du jour et de la personne.
Si oui = 1 alors on utilise la fonction DECALER par rapport à la cellule A1 de la feuille de suivi, et on décale cette référence du nombre de ligne qui correspond à la ligne où le Sommeprod est vrai, et d'une colonne pour arriver sur le type d'absence.
Si non on affiche rien, du coup il n'y a même plus de "0" !
Le fichier sans les MFC :
@ bientôt
LouReeD
Nota : leakim, je crois que cette proposition est Excel-lente, non ? Du moins je la préfère à la première, formule plus simple.
Mais elle est "lente" car je n'ai pas encore les automatismes bien en place dans ma tête ! Mais ça vient...
Salut Looreed,
Je te confirme que cette dernière n'est vraiment pas mal du tout
Leakim
C'est formidable, je viens d'ajouter les mises en formes conditionnelles et ça marche parfaitement exactement comme ce que je voulais ! Merci mille fois !
Par contre, je ne suis pas sûre d'avoir compris le fonctionnement de la formule est ce que vous pouvez m'éclairer ?
Bonne journée,
Mélissa
Bonjour,
pour la fonction SOMMEPROD, ici un lien vers une page où vous pourrez télécharger un fichier PDF qui vous explique cette fonction et son utilisation dans votre cas :
Dans votre fichier elle est utilisée pour une recherche multicritères :
nom de la personne, date de la colonne
cette de de colonne doit être égale ou supérieure à une date de début d'absence, tout en étant égale ou inférieure à une date de fin d'absence, ce qui donne soit 1 si tous les critères sont VRAI soit 0 si au moins un critère n'est pas bon.
Si on prend la première formule au 01/02/2018 avec Alexandre en B4 :
=Sommeprod((si Alexandre=une des cellules de la colonne A2:A10 alors 1 sinon 0) * (si la date de la colonne où se trouve la formule est égale ou supérieure à une des dates de la colonne C2:C10 alors 1 sinon 0) * (si la date de la colonne où se trouve la formule est égale ou inférieure à une des dates de la colonne D2:D10 alors 1 sinon 0))
dans notre exemple :
Alexandre = une des cellules de la colonne A2:A10 = oui en ligne 6
le reste des critères est à prendre sur cette même ligne :
date de la colonne où se trouve la formule : 01/02/2018 >= à une date de la colonne C2:C10 = non
date de la colonne où se trouve la formule : 01/02/2018 <= à une date de la colonne D2:D10 = oui (le 11/02/2018 en ligne 6 toujours)
un OUI = 1, un NON = 0 et on fait le produit : 1 x 0 x 1 = 0
Alexandre n'a pas d'absence au 01/02/2018 donc SOMMEPROD renvoie 0
Comme ce Sommeprod est un test logique d'une fonction SI, avec SOMMEPROD >0, on a bien le test négatif, alors on affiche "".
Si le Sommeprod >0 est positif, dans le cas d'Alexandre à la date du 10/02/2018, alors on exécute la fonction DECALER.
DECALER(adresse de la cellule de référence ; décalage d'un nombre de ligne ; décalage d'un nombre de colonne)
J'ai pris aléatoirement la cellule A1 comme référence, et il faut que cette fonction me renvoie la valeur inscrite en B6, soit un décalage de 5 lignes et de 1 colonne.
Vous aurez compris que le nombre de colonne sera toujours égale à 1 car pour aller de A à B il y aura toujours un décalage de 1, c'est pourquoi le 1 est écrit en dur dans la formule. Si j'avais pris la cellule B1 en référence, il n'y aurait pas eu de décalage en colonne... Et si j'avais pris C1 en référence alors il y aurait eu -1 de décalage en colonne.
Le décalage du nombre de ligne :
En gros il faut trouver dans le tableau le numéro de la ligne où tous les critères de recherche de SOMMEPROD sont VRAI; la formule suivante :
=Sommeprod((si Alexandre=une des cellules de la colonne A2:A10 alors 1 sinon 0) * (si la date de la colonne où se trouve la formule est égale ou supérieure à une des dates de la colonne C2:C10 alors 1 sinon 0) * (si la date de la colonne où se trouve la formule est égale ou inférieure à une des dates de la colonne D2:D10 alors 1 sinon 0))
renvoie 1 si tous les critères sont VRAI, ce n'est pas le numéro de la ligne où se trouve les données...
Mais on sait aussi que c'est une "multiplication", on a déjà un des éléments de la multiplication, c'est le 1, il nous manque plus qu'à le multiplier par le numéro de ligne est on aura en "sortie" le numéro de la ligne où les critères sont VRAI :
=Sommeprod((si Alexandre=une des cellules de la colonne A2:A10 alors 1 sinon 0) * (si la date de la colonne où se trouve la formule est égale ou supérieure à une des dates de la colonne C2:C10 alors 1 sinon 0) * (si la date de la colonne où se trouve la formule est égale ou inférieure à une des dates de la colonne D2:D10 alors 1 sinon 0) * LIGNE(tableau allant de la ligne 2 à la ligne 10))
Dans notre exemple d'Alexandre au 10/02/2018, tous les critères sont à VRAI (1) en Ligne 6, on a bien alors en résultat du produit 1x 6 = 6
Il faut donc décaler notre cellule de référence de 6 lignes moins 1 car la cellule de référence fait partie de ces 6 lignes :
de A1 on veut aller à A6, il faut se décaler de 5....
Donc DECALER(A1;5;1) donne valeur se trouvant à l'adresse de la cellule (A+1colonne)(1+5lignes) = B6 celle ci nous renvoie "CP"
=SI(SOMMEPROD(($A4='Suivi absences'!$A$2:$A$10)*(Calendrier!B$3>='Suivi absences'!$C$2:$C$10)*(Calendrier!B$3<='Suivi absences'!$D$2:$D$10))>0ici on vérifie si une ligne contient à "VRAI" tous les critères;
DECALER('Suivi absences'!$A$1;SOMMEPROD(($A4='Suivi absences'!$A$2:$A$10)*(Calendrier!B$3>='Suivi absences'!$C$2:$C$10)*(Calendrier!B$3<='Suivi absences'!$D$2:$D$10)*(LIGNE('Suivi absences'!$2:$10)-1))ici on trouve le numéro de ligne où tous les critères sont VRAI;1);"")
Suis-je clair ?
@ bientôt
LouReeD
Re bonjour !
Encore plus simple !
En écrivant la réponse ci dessus, je me suis dit :
si SOMMEPROD est égale à 0 car au moins un des critères de recherche est FAUX, alors le numéro de ligne serait 0, donc un décalage de 0 par rapport à la cellule de référence.
Si cette cellule de référence était en colonne B (dans notre cas c'est ce qu'il faut) avec une valeur égale "à rien" alors la formule DECALER reverrait "rien" c'est à dire 0, dans les autres cas DECALER reverrait le type d'absence. Pour cela il a juste fallu insérer une ligne au-dessus du tableau de suivi des absence...
Avec l'astuce de leakim, vous "masquez" les 0 de la feuille, et voilà, la formule est simplifiée d'un SOMMEPROD et d'une condition.
Voici la version 3 du fichier :
@ bientôt
LouReeD
Salut,
Ce qui est rigolo avec Looreed
Cette fois c'est carrément simplissime pour qui maîtrise la rédaction de des formules.
Et bravo pour ta pédagogie
Leakim
Bonsoir,
bon et bien ci joint la dernière version :
J'ai gardé le principe de la cellule qui affiche rien, mais elle correspond à la cellule B1 de la feuille "suivi des absences"
J'y aie mis un espace comme intitulé, du coup sur le calendrier il n'y aura rien de "visible" vu que l'espace est sans valeur.
L'intitulé du tableau est remplacé par une étiquette de formulaire.
Ensuite le tableau des absence, je l'ai transformé en tableau Excel afin que les nouvelles données s'y intègrent automatiquement.
Du coup j'ai modifié également les formules du calendrier.
@ bientôt
LouReeD
Bonjour,
Avec un peu de retard merci mille fois pour ton aide et tes explications qui m'ont permis d'y voir nettement plus clair !
A bientôt,
Mélissa
Bonsoir,
ravis que cela vous convienne !
N'hésitez pas si vous avez des questions...
@ bientôt
LouReeD