Mise en forme conditionnelle dans tableau de congés payés

Y compris Power BI, Power Query et toute autre question en lien avec Excel
-
MélissaSgn
Appréciation reçue : 1

Message par MélissaSgn » 16 février 2018, 22:19

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.
Congés.xlsx
(20.55 Kio) Téléchargé 70 fois
Bonne soirée,
Mélissa
Avatar du membre
LouReeD
Contributeur
Contributeur
Messages : 6'255
Appréciations reçues : 280
Inscrit le : 14 octobre 2014
Version d'Excel : 2013 FR, 2016 FR
Contact :
Téléchargements : Mes applications

Message par LouReeD » 16 février 2018, 22:33

Bonsoir,

je pense que sans VBA, la solution se trouve du coté de la fonction SOMMEPROD

@ bientôt

LouReeD
Quelques règles à lire ICI ;;)
______________________________________________________Vous pouvez allez faire un tour sur : Index de "Mes applications" ;;)
Avatar du membre
LouReeD
Contributeur
Contributeur
Messages : 6'255
Appréciations reçues : 280
Inscrit le : 14 octobre 2014
Version d'Excel : 2013 FR, 2016 FR
Contact :
Téléchargements : Mes applications

Message par LouReeD » 16 février 2018, 22:57

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 :
Congés_LouReeD.xlsx
(30.24 Kio) Téléchargé 53 fois
@ bientôt

LouReeD
Quelques règles à lire ICI ;;)
______________________________________________________Vous pouvez allez faire un tour sur : Index de "Mes applications" ;;)
Avatar du membre
leakim
Membre impliqué
Membre impliqué
Messages : 1'806
Appréciations reçues : 12
Inscrit le : 11 décembre 2012
Version d'Excel : 2010 - 2016 FR
Téléchargements : Mes applications

Message par leakim » 16 février 2018, 23:09

Salut,
Pour compléter l'excellente proposition de Looreed
Capture1602.JPG
En décochant l'affichage des zéros tu obtiendras un affichage allégé

Cordialement,

Leakim
On apprend par des essais/erreurs. L'erreur, serait de ne pas en faire... :noel:
Image
Avatar du membre
LouReeD
Contributeur
Contributeur
Messages : 6'255
Appréciations reçues : 280
Inscrit le : 14 octobre 2014
Version d'Excel : 2013 FR, 2016 FR
Contact :
Téléchargements : Mes applications

Message par LouReeD » 16 février 2018, 23:53

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 :
Congés_LouReeD_V2.xlsx
(23.85 Kio) Téléchargé 53 fois
@ 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... :P
Quelques règles à lire ICI ;;)
______________________________________________________Vous pouvez allez faire un tour sur : Index de "Mes applications" ;;)
Avatar du membre
leakim
Membre impliqué
Membre impliqué
Messages : 1'806
Appréciations reçues : 12
Inscrit le : 11 décembre 2012
Version d'Excel : 2010 - 2016 FR
Téléchargements : Mes applications

Message par leakim » 17 février 2018, 09:25

Salut Looreed,
Je te confirme que cette dernière n'est vraiment pas mal du tout :clap:

Leakim
On apprend par des essais/erreurs. L'erreur, serait de ne pas en faire... :noel:
Image
-
MélissaSgn
Appréciation reçue : 1

Message par MélissaSgn » 17 février 2018, 10:08

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
Avatar du membre
LouReeD
Contributeur
Contributeur
Messages : 6'255
Appréciations reçues : 280
Inscrit le : 14 octobre 2014
Version d'Excel : 2013 FR, 2016 FR
Contact :
Téléchargements : Mes applications

Message par LouReeD » 17 février 2018, 16:27

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 :
https://www.excel-pratique.com/fr/telec ... -no247.php
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
Quelques règles à lire ICI ;;)
______________________________________________________Vous pouvez allez faire un tour sur : Index de "Mes applications" ;;)
Avatar du membre
LouReeD
Contributeur
Contributeur
Messages : 6'255
Appréciations reçues : 280
Inscrit le : 14 octobre 2014
Version d'Excel : 2013 FR, 2016 FR
Contact :
Téléchargements : Mes applications

Message par LouReeD » 17 février 2018, 16:38

Re bonjour !
Encore plus simple ! :lol:

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 :
Congés_LouReeD_V3.xlsm
(22.52 Kio) Téléchargé 53 fois
@ bientôt

LouReeD
Quelques règles à lire ICI ;;)
______________________________________________________Vous pouvez allez faire un tour sur : Index de "Mes applications" ;;)
Avatar du membre
leakim
Membre impliqué
Membre impliqué
Messages : 1'806
Appréciations reçues : 12
Inscrit le : 11 décembre 2012
Version d'Excel : 2010 - 2016 FR
Téléchargements : Mes applications

Message par leakim » 17 février 2018, 19:53

Salut,
Ce qui est rigolo avec Looreed :(): c'est qu'il s'améliore tout le long du post ! :appl2:

Cette fois c'est carrément simplissime pour qui maîtrise la rédaction de des formules. :|||:

Et bravo pour ta pédagogie |k)

Leakim
On apprend par des essais/erreurs. L'erreur, serait de ne pas en faire... :noel:
Image
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message