Mise en forme tableau selon dates et test

[redF]

Salut le forum,

Ma demande initiale n'étant pas très claire, je reviens vers vous après avoir retravaillé mon classeur.

Je souhaite donc mettre en forme le tableau en pièce jointe selon des critères de dates, sexe, et type de contrat.

En fait, il faudrait pour chaque chambre, effectuer le test suivant (chambre 1 en exemple):

SI "Graphique" Ax = "Tableau" D4
ALORS Couleur de la cellule de Bx à Bx+I4 SERA :
SI F4 = H ET G4 = S : Bleu claire
SI F4 = H ET G4 = E : Bleu foncée
SI F4 = F ET G4 = S : Violet claire
SI F4 = F ET G4 = E : Violet foncée

- Ax correspondant à la date dans la colonne A de la feuille "Graphique" correspondant à la date d'arrivée de l'employé (D4)
- Bx puisque B correspond à la colonne de la chambre 1 et x étant la ligne déterminée à l'étape précédente.

Voilà, si quelqu'un pouvait me donner le petit coup de pouce nécessaire pour passer de la formulation à la programmation, je lui en serait grandement reconnaissant.

redF

 

[Celeda]

Re : Mise en forme tableau selon dates et test

Bonjour,

Tu aurais du rester sur le fil précédent !!!!!!
https://www.excel-downloads.com/threads/mise-en-forme-conditionnelle-multi-criteres.78743/

Tu copieras 1000 fois manuellement, "je ne recommencerais plus"



Ici j'ai défusionné les cellules des chambres considérant qu'il y avait deux lits et surtout pour plus de manip avec la sommeprod (j'ai eu du mal!! ouf fini de chercher !!)

Donc, on suppose les mêmes étiquettes de part et d'autre : graphique (plutôt calendrier ) et feuille de suivi des villas.

On place la sommeprod =
=SI(SOMMEPROD(($A5>=DateD)*($A5<=DateD+Duree-1)*(B$4=Chambre))=0;"";INDEX(Type;EQUIV(B$4;Chambre;0)))

On préfère nommer les plages pour plus de facilité de compréhension.
Avec l'index on récupère le type (j'ai préfèré concatener les deux infos pour plus de lisibilité pour la mefc qui s'en suit) soit HE,HS, ect...

On place une mise en force conditionnelle et là hélàs trois fois hélàs, on ne peut entrer que trois types d'info ==> il reste donc la femme stagiaire en blanc......à moins que tu récupères la mise enforme conditionnelle de MDF (c'est une redoutable macro qui te colorie tout ce que tu veux!!! mais là j'ai pas eu le temps de la programmer ..désolèe plein de choses à faire!!)

 

[JBOBO]

Re : Mise en forme tableau selon dates et test

MOI J'AI FAIS CA ! (Voir fichier joint)

En fait j'utilise une formule me renvoyant un code (HE;FE;HS ou FS) qui s'inscrit dans la feuille graphique pour chaque jour de la chambre occupé. Ensuite si c'est un Homme employé (HE), il utilise la couleur de police par défaut (bleu), puis ensuite des mise en forme conditionnelle, si il est stagiaire (hachuré bleu) si c'est une femme employée (rose) et si elle est stagiaire (hachurée rose). si tu ne veux pas afficher de texte dans tes cellules du graphiques tu dois pouvoir jouer avec les polices et leur tailles pour pouvoir colorer les cellules.
regarde et dis nous !

 

[redF]

Re : Mise en forme tableau selon dates et test

Wow! Nice job!

Celeda:
"je ne recommencerais plus manuellement"
Désolé mais ma demande initiale me paraissait tellement peu claire que je pensait qu'elle attirerait plus volontiers l'XLDnaute en la reformulant et la repostant.
Promis je ne le referais plus.

Pour remédier au problème des 3 couleurs, ma copine que j'ai eu au téléphone m'avait proposé de faire apparaitre en bleu les hommes en rose les filles (original n'est ce pas?) et de faire apparaitre la lettre E ou S pour différencier les types de contrat.
Et cela aurait l'avantage de libérer une couleur qui pourrai servir pour autre chose.

En tout cas une fois de plus, merci pour ton aide.


JBOBO:
Perso, ton fichier me plait bien, et je pense que je vais avoir moins de mal à décortiquer ta formule par rapport à celle de Celeda.

Mais ce n'est pas moi qui décide au final...

Merci beaucoup à toi aussi, c'est une bien belle formule.


Bon, je vais maintenant essayer de bidouiller un peu vos formules pour arriver à mieux les comprendre (j'aime pas recopier bétement ), et je vous tiens au courant d'ici quelques temps...

 

[Claude38]

Re : Mise en forme tableau selon dates et test

Bonjour,

Un petit raccoursisement à la formule de Celeda ,en nommant la colonne E date de départ.
=SI(SOMMEPROD(($A5>=DateD)*($A5<=DateD+Duree-1)*(B$4=Chambre))=0;"";INDEX(Type;EQUIV(B$4;Chambre;0)))
devient,
=SI(SOMMEPROD(($A5>=DateD)*($A5<DateE)*(B$4=Chambre))=0;"";INDEX(Type;EQUIV(B$4;Chambre;0)))

DateE dans cette formule
Un point à regarder.
Une chambre étant affecté a la catégorie HE, par la suite si on l'affecte à une autre catégorie c'est toujours la 1ère catégorie qui apparaît sur la feuille graphique.

Bonne journée

 

[Celeda]

Re : Mise en forme tableau selon dates et test

Bonjour,

En fait, Claude, j'y ai pensé à cette histoire d'affectation.
Si on suppose que chaque résa reste notée dans la feuille de suivi (ce qui me paraît le plus sage), quand on affectera en visualisant le planning des chambres, la chambre 1a par exemple,
on s'apercevra qu'elle est déjà occupée de telle date à telle date, tu me suis,

donc, en ajoutant une ligne dans le tableau, on affectera evidemment d'autres dates et donc la formule Type comportera la nouvelle catégorie d'homme stagiaire, ect...; il n'y aura pas de possibilités de chevauchement si on respecte la visualisation;

de plus, si on veut bien se limiter à ne pas répeter les mêmes dates, il suffit d'installer avec données validation un arrêt pour prévenir du doublon.

Enfin...c'est un peu à tout cela que j'avais pensé; mais on peut aussi éviter cette colonne type et taper directement dans les deux colonnes info F et G dans la partie Index.

 

[Celeda]

Re : Mise en forme tableau selon dates et test

Bonjour,

Oui Claude du 38 tu avais vu juste (encore une fois!!!)!! Honte sur moi!!

Donc pour résoudre ce problème de type faxu qui revient, j'ai imaginé une colonne supp comme 4ème critère ou argument de recherche.

Cela donne ceci :

=INDEX(Type;SI(SOMMEPROD((($A5>=DateD)*($A5<=DateD+Duree-1)*(B$4=Chambre))*(Nos))=0;"";SOMMEPROD(((($A5>=DateD)*($A5<=DateD+Duree-1)*(B$4=Chambre)*(Nos)))));0)

Je vais retourner la bonne valeur grâce au numéro qui départagera le doublon de la chambre; je place une mefc dans le tableau pour alerter en cas de date supérieure à 1 soit :

=SOMMEPROD((B4=Chambre)*(DateD=D4))=2


sauf sauf sauf, qu'en cas de non retour de bonne valeur dans la feuille graphique, une belle erreur de type #Valeur apparaît puisqu'elle ne trouve
aucune valeur correspondante à la somme prod des dates,de la chambre et du numéro!! et vlan et je n'arrive pas à la chasser......

si amateur ou amatrice pour régler cette petite coquine trouve qu'il ou elle n'hesite pas à poster en attendant que je trouve le temps pour le faire.
Merci à l'avance.

 

[Monique]

Re : Mise en forme tableau selon dates et test

Bonjour,

A essayer sous cette forme en B5 de la feuille « Graphique »

=SI(SOMMEPROD(($A5>=DateD)*($A5<=DateD+Duree-1)*(B$4=Chambre)*(Nos))=0;"";INDEX(Type;SOMMEPROD(($A5>=DateD)*($A5<=DateD+Duree-1)*(B$4=Chambre)*(Nos))))

Si (SommeProd = 0 ; rien ; Index(Plage ; SommeProd))

 

[Monique]

Re : Mise en forme tableau selon dates et test

Re,

Il y a plus court :

=INDEX(TypeCol;MIN(SI(($A5>=DateD)*($A5<=DateD+Duree-1)*(B$4=Chambre);LIGNE(Type))))&""
Formule matricielle, à valider par ctrl, maj et entrée.

En donnant le nom « TypeCol » à la plage J1:J22 de la feuille « Tableau »,
en laissant J1 vide

Min(Si()) renvoie le n° de ligne répondant aux conditions,
Index(TypeCol ; N° de ligne) renvoie le contenu
Si n° de ligne n’existe pas, Min(Si()) renvoie 0
Index(TypeCol ; 0) renvoie 0 (renvoie en réalité le contenu de J1 mais J1 est vide)
Index(TypeCol ; 0)&"" renvoie"" si validé par ctrl, maj et entrée

 

[Celeda]

Re : Mise en forme tableau selon dates et test

Bonjour,


arf!!!je le savais, je le savais !!!!

qu'il y avait mieux !!!! the best!!!!!

Merci Monique,
tu vois Claude du 38,

Il y a l'ignorance et la connaissance
la réminiscence de vagues souvenirs de calculs
et peut-être que dans un état d'hypnose
j'arriverai à refaire ce Min Si qui m'obsède et qui est l'hôte tout puissant
de cette méthode !!!!!!!!!! Merci Monique pour ces explications et promis
dans mon inconscient je l'installerai pour qu'il chasse toutes les zones d'ombre et de mystère; je deviendrai sa marionnette !!!!!!

 

[redF]

Re : Mise en forme tableau selon dates et test

Bon, après quelques vacances bien méritées, je reviens vers vous pour vous expliquer la solution finalement adoptée et vous remercier une fois de plus.

Je me suis rendu compte qu'il se posait un autre problème : il arrive q'une même chambre soit occupée par 2 voir 3 saisonniers différents dans l'année. Or dans les tableaux sur lesquels nous avions travaillé, une fois qu'une date de départ et d'arrivée avait été entrée, il été impossible de visualisée l'occupation future de la chambre par une autre personne.

Cette contrainte a pris le pas sur l'affichage HS, HE, FS ou FE.

Et du coup, j'ai fait 3 tableaux par villas pour pouvoir faire du prévisionnel.

Encore un grand MERCI à JBOBO dont j'ai utilisé la ligne de formule (car c'était celle que je "comprenais" la mieux) et à tous les autres qui se sont donnés du mal sur ce classeur.

redF