Formule de recherche

Bonjour les amis du forum,

J'ai grand besoin de votre aide sur une formule qui me pose problème depuis un bon moment. Je n'arrive pas, en fait, à voir comment la construire pour obtenir le résultat que je veux. Les explications sont dans le fichier ci dessous...

https://www.excel-pratique.com/~files/doc2/Planning.xls

Merci d'avance aux spécialistes des formules qui "excellent" sur ce forum...

Fabrice,

Bonjour le forum,

Personne pour m'apporter un début de solution ?

Merci d'avance.

Fabrice,

Bonjour fabrice, forum,

Regarde le fichier joint : Fichier

Il y a peut-être une solution moins longue mais en attendant, celle-là marche

Par contre, lors de l'ouverture du classeur Excel, une erreur VBA s'affiche.

Lors de cette ouverture, excel effectue quelque chose sur une feuille nommée "Synthèse", alors que cette feuille n'existe pas.

Je n'ai pas modifié le code parce que j'ai pensé que dans le fichier excel complet, il y a une feuille "Synthèse".

Si besoin est, demande.

A+

Bonjour à tous,

1) problème à l'ouverture de ton fichier,

Private Sub Workbook_Open()
'Macro par Dan pour Fabrice69
With Sheets("Synthèse")
    .EnableOutlining = True
    .Protect userInterfaceOnly:=True
End With
End Sub

Neutralise déjà ce code, car pas de feuille "Synthèse"

2) Tu auras des problèmes avec les cellules fusionnées (colonne C dates)

3) je ne vois pas la formule non plus !

Amicalement

Claude.

Bonjour le forum,

Vba-new, Claude, merci pour votre implication dans mon projet. J'ai neutralisé la petite macro à l'ouverture du fichier (qui contient effectivement plusieurs feuilles) qui n'est qu'un morceau de mon projet.

J'avais tenté à peu près la même approche que toi Vba-new en passant par des numéros affectés aux noms mais il faut savoir qu'au total, j'ai environ 200 personnes à gérer... Donc, pour le trigramme, je ne peux pas passer par une condition SI...

Claude, les cellules fusionnées ne me posent pas de problème car, par ailleurs, je lui fais calculer des NB.SI qui fonctionnent parfaitement. Je n'aime pas non plus travailler avec des cellules fusionnées mais, je dois gérer des demi-journées de travail. De plus, j'ai 14 fichiers de ce type qui sont liés avec d'autres fichiers et je me vois mal les refaire en cours d'année.

Pensez-vous que ma demande est trop "ambitieuse" et que cela ne soit pas possible ?

Je souhaite juste pouvoir afficher le nom de la personne en maladie (M) à la date du jour.Cela parait très simple, dit comme ça, mais je tourne en rond depuis plusieurs jours pour trouver la solution...

Vous êtes mon dernier espoir.

https://www.excel-pratique.com/~files/doc2/aJF0mPlanning_modifie.xls

Merci d'avance.

Fabrice,

RE,

Ah oui, en effet, avec 200 personnes à gérer, ma formule avec des SI perd tout son intérêt!

Pour résoudre ton problème, je ne vois plus que la VBA!

Je vais creuser de mon côté mais l'avis d'un spécialiste serait le bienvenu

Bonjour

J'ai essayé hier, mais les cellules fusionnées posent effectivement problème. A EVITER

Je tenterai autre chose dans la journée si tu n'as pas eu de réponse, mais je pense qu'une macro serait plus appropriée.; et là, ce n'est pas pour moi.

vba-new, ta formule ne fait qu'indiquer les prénoms et noms équivalent aux numéros. Hors, il faut les prénoms et noms des personnes ayant la lettre M dans le tableau D10:L63 au jour indiqué en O5.

Amicalement

Nad

re,

il faut aussi prévoir dans la formule les différentes variables (maladie, congés, RTT etc...)

Bon courage !

Salut Nad,

Bonne journée

Claude.

Re-,

Nad, c'est exactement ça !!! Tu penses que ce n'est pas possible par formule ?

Sinon, même avec du VBA, je suis preneur...

Je cherche toujours de mon côté pour tenter d'éviter les cellules fusionnées mais là, en l'occurence, je fais une recherche sur la date avec une RECHERCHEV et cela ne me pose pas de problème pour l'instant. Et c'est le seul moment où je fais référence à ces cellules fusionnées...

Merci encore pour votre aide à tous.

Fabrice,

Re

Ce n'est pas que c'est impossible, c'est que ça porte à réfléchir un peu plus !

Mais bon, c'est le WE et la tête est plus reposée. Je regarde ce que je peux faire.

Nad

Bonjour,

Un essai par formule pour les personnes malades :

avec une formule matricielle en N8 :

=SI(LIGNE()-7>SOMME((DECALER($C$7;EQUIV($O$5;$C$8:$C$65;0);1;1;9)="M")+(DECALER($C$7;EQUIV($O$5;$C$8:$C$65;0);2;1;9)="M"));"";INDEX($Q$9:$Q$16;EQUIV(INDEX($A$5:$L$5;PETITE.VALEUR(SI(((DECALER($C$7;EQUIV($O$5;$C$8:$C$65;0);1;1;9)="M")+(DECALER($C$7;EQUIV($O$5;$C$8:$C$65;0);2;1;9)="M"))*COLONNE($D$5:$L$5)<>0;((DECALER($C$7;EQUIV($O$5;$C$8:$C$65;0);1;1;9)="M")+(DECALER($C$7;EQUIV($O$5;$C$8:$C$65;0);2;1;9)="M"))*COLONNE($D$5:$L$5));LIGNE()-7));GAUCHE($Q$9:$Q$16)&GAUCHE($R$9:$R$16;2);0))&" "&INDEX($R$9:$R$16;EQUIV(INDEX($A$5:$L$5;PETITE.VALEUR(SI(((DECALER($C$7;EQUIV($O$5;$C$8:$C$65;0);1;1;9)="M")+(DECALER($C$7;EQUIV($O$5;$C$8:$C$65;0);2;1;9)="M"))*COLONNE($D$5:$L$5)<>0;((DECALER($C$7;EQUIV($O$5;$C$8:$C$65;0);1;1;9)="M")+(DECALER($C$7;EQUIV($O$5;$C$8:$C$65;0);2;1;9)="M"))*COLONNE($D$5:$L$5));LIGNE()-7));GAUCHE($Q$9:$Q$16)&GAUCHE($R$9:$R$16;2);0)))

Formule à recopier vers le bas.

Pour faciliter (un peu ), je joins le fichier

https://www.excel-pratique.com/~files/doc2/Essai_planning.zip

@+

Edit : je viens de tester : finalement, ça ne marche pas. Mais l'espoir n'est pas perdu.

@+

re,

2ème essai qui semble un peu plus convainquant.

A tester quand même

En N8, cette formule matricielle :

=SI(LIGNE()>SOMME(((((DECALER($C$7;EQUIV($O$5;$C$8:$C$65;0);1;1;9)="M")+(DECALER($C$7;EQUIV($O$5;$C$8:$C$65;0)+1;1;1;9)="M"))*COLONNE($D$5:$L$5))>0)*1)+7;"";INDEX($Q$9:$Q$16;EQUIV(INDEX($A$5:$L$5;PETITE.VALEUR(SI((((DECALER($C$7;EQUIV($O$5;$C$8:$C$65;0);1;1;9)="M")+(DECALER($C$7;EQUIV($O$5;$C$8:$C$65;0)+1;1;1;9)="M"))>0)*COLONNE($D$5:$L$5)<>0;(((DECALER($C$7;EQUIV($O$5;$C$8:$C$65;0);1;1;9)="M")+(DECALER($C$7;EQUIV($O$5;$C$8:$C$65;0)+1;1;1;9)="M"))>0)*COLONNE($D$5:$L$5));LIGNE()-7));GAUCHE($Q$9:$Q$16)&GAUCHE($R$9:$R$16;2);0))&" "&INDEX($R$9:$R$16;EQUIV(INDEX($A$5:$L$5;PETITE.VALEUR(SI((((DECALER($C$7;EQUIV($O$5;$C$8:$C$65;0);1;1;9)="M")+(DECALER($C$7;EQUIV($O$5;$C$8:$C$65;0)+1;1;1;9)="M"))>0)*COLONNE($D$5:$L$5)<>0;(((DECALER($C$7;EQUIV($O$5;$C$8:$C$65;0);1;1;9)="M")+(DECALER($C$7;EQUIV($O$5;$C$8:$C$65;0)+1;1;1;9)="M"))>0)*COLONNE($D$5:$L$5));LIGNE()-7));GAUCHE($Q$9:$Q$16)&GAUCHE($R$9:$R$16;2);0))&" ")

Voir le fichier joint

https://www.excel-pratique.com/~files/doc2/Essai_planning_2.zip

@+

Edit : Précision : la formule, étant matricielle, doit être validée par CTRL + MAJ + ENTREE

@+

Re-,

thibo, ta formule ne marche malheureusement pas... Je viens de tester et si je retire la maladie pour MNI, son nom reste toujours dans le carré réservé aux personnes en maladie.

Mais je suis sincèrement impressionné par la taille de cette formule et me demande si je serai capable, un jour, de comprendre tout ce qui est dedans...

Merci quand même.

Fabrice,

re,

Peux-tu m'en dire plus ?

Je viens de faire quelques essais en supprimant des maladies et la liste se met à jour.

As-tu bien pris le 2ème fichier que j'ai joint ?

Au passage, dans le fichier que tu as joint, ta Validation a "sauté" (voir dans Données - Validation).

A te (re)lire

@+

Re,

Pour la validation, c'est normal car ce n'est qu'un bout de fichier épuré de pas mal de lignes et de colonnes...

Pour le test, je retente cet aprèm car il faut que je parte maintenant mais je te tiens au courant.

A tout à l'heure.

Fabrice,

Bonjour à tous,

Fabrice, y a-t-il moyen de "clarifier" cette formule? Le but étant de permettre aux un peu moins avancés en Excel (moi par exemple) d'essayer de comprendre le fonctionnement de cette formule.

En tout cas, cette formule semble marcher à merveille

Re

Voici ma version

Fichier

Les colonnes D et N sont masquées

Amicalement

Nad

Bonjour,

à la demande de vba-new, une tentative d'explication.

Avec la fonction DECALER, je constitue 2 matrices :

une première pour la 1ère ligne de la date choisie et une deuxième pour la deuxième ligne.

La 1ère matrice est définie avec :

(DECALER($C$7;EQUIV($O$5;$C$8:$C$65;0);1;1;9)="M")

et la 2ème avec :

(DECALER($C$7;EQUIV($O$5;$C$8:$C$65;0)+1;1;1;9)="M")

J'additionne ensuite ces deux matrices (ce qui me permet d'avoir 1 ou 2 M par salarié) et cherche ensuite les éléments qui sont > 0 (permet ainsi de savoir quels salariés ont au moins 1 M.

Cette nouvelle matrice est ensuite multipliée par le n° de colonne avec :

COLONNE($D$5:$L$5)

Cela me donne une nouvelle matrice avec le rang de la colonne des salariés qui ont 1 ou 2 M.

Avec la fonction INDEX avec PETITE.VALEUR associée à SI pour tester en fait les valeurs différentes de 0, j'isole le trigramme des salariés qui m'intéressent.

Il faut ensuite faire une nouvelle recherche du trigramme en constituant une nouvelle matrice composée de la 1ère lettre du nom et des 2 premières lettre du prénoms

La longueur de la formule est surtout due au fait que je suis amené à utiliser plusieurs fois cette matrice :

(DECALER($C$7;EQUIV($O$5;$C$8:$C$65;0);1;1;9)="M")+(DECALER($C$7;EQUIV($O$5;$C$8:$C$65;0)+1;1;1;9)="M"))>0)*COLONNE($D$5:$L$5)

dans la formule.

On pourrait passer par une phase de formule nommée, ce qui permettrait de raccourcir notablement la formule finale.

Les formules matricielles ne sont pas toujours faciles à concevoir et encore moins à expliquer. POur ma part, je les pense davantage que je ne suis en mesure de les expliquer.

Pour Fabrice, l'utilisation de trigrammes sur une base de 200 salariés risque fortement de générer des doublons. Y-a-tu songé ?

Sinon, l'exercice fut très intéressant.

A Fabrice de nous dire si ça correspond pleinement à son souhait.

Bon WE à tous

@+

Salut thibo,

Merci pour cette explication détaillée!

Je vais la garder dans un coin de mon ordi, pas loin du fichier où se trouve ta formule!

Soit dit en passant, la solution que Nad a donnée a l'air pas mal non plus!

Merci à tous deux.

Bonjour

J'avais attaqué le sujet, mais je n'avais pas réussi à regler le peoblème de l'homonymie; Par exemple, en mettant un nouveau nom (MARTIAL NIVELLE)

Je vois que ni la formule de thibo, ni celle de Nad ne tiennent compte de cette possibilité.

S'il ne doit pas y avoir d'homonymie sur le trigrame j'opterai pour la solution de Nad, plus facile à mettre en oeuvre.

Ceci étant, la formule de thibo est digne d'un super-formuliste.

Cordialement