Affichage d'une valeur selon plusieurs critères

[Rhumcoca]

Bonjour,

je suis dans l'impasse, je n'arrive pas à trouver une solution à mon problème.
je vous joint mon fichier simplifié pour vous exposer mon problème.
je souhaite dans la colonne Subrogation (colonne Q de la feuille SUIVI_ARRET), affiché le texte correspondant aux critères qui sont dans la feuille "BD".
exemple: pour mon ouvrier "A Fabien" qui est en maladie ("MAL" dans la colonne G de la feuille SUIVI_ARRET) et qui a 11,57 années d'ancienneté (colonne P de la feuille SUIVI_ARRET) doit apparaitre dans la colonne Q le texte présent dans la cellule E9 de la feuille "BD" car correspond aux critères en A9 : D9 de la feuille "BD".

merci d'avance pour votre aide.

 

[CISCO]

Bonjour

Cf. en pièce jointe, avec une formule matricielle à valider avec les 3 touches Ctrl+maj+entrer.

Il faut un peu changer le contenu des colonnes BD!C et BD!D.

A compléter pour avoir aussi les résultats concernant le cas PAT.

@ plus

 

[Dugenou]

Bonjour,
Une tentative à base de sommeprod avec un tableau BD à remanier :

=SI([@[ANCIENNETE A LA DATE D''ARRET]]<0;"";INDEX(BD!$E$1:$E$38;SOMMEPROD((BD!$A$25:$A$38=C_NC)*(BD!$B$25:$B$38=[@[TYPE MALADIE]])*(BD!$C$25:$C$38<[@[ANCIENNETE A LA DATE D''ARRET]])*(BD!$D$25:$D$38>[@[ANCIENNETE A LA DATE D''ARRET]])*LIGNE(BD!$B$25:$B$38))))

Le si au début évite d'afficher n'importe quoi quand la ligne n'est pas complétée,
l'index doit toujours commencer à la ligne 1 car le sommeprod renvoie un N° de ligne.
le C_NC est une formule nommée (formules/gestionnaire de noms) qui permet de passer de la catégorie colonne C à celle de la colonne A de la BD avec le cas particulier de MAT qui s'applique à tous (il faut peut-être ajouter PAT)

=SI(Tableau1[@[TYPE MALADIE]]="MAT";"<>";SI(Tableau1[@[CATEGORIE PROFESSIONNELLE]]="cadre";"cadre";"<>cadre"))

Voir pj

A ta disposition pour aider à adapter à ton fichier réel
Cordialement

Edit : hello Cisco: décidément on a les mêmes centres d'intérêt !

 

[Rhumcoca]

les 2 solutions sont très intéressantes et merci à vous 2 pour vos retours, je pense opté pour la solution de Dugenou à base de sommeprod avec le remaniement de mon tableau.
pour mon information personnel, j'ai juste pas compris l'histoire du C_NC le "C" de "C_NC" represente la colonne au quel on veux faire référence dans la feuille SUIVI_ARRET ou rien à voir? est ce qu'il existe un cours sur cette "Formules/gestionnaire de nom"?
je ne comprend pas non plus la fin de la formule : "*LIGNE(BD!$B$25:$B$38)" qu'est ce qu'elle permet de faire?
est ce que tu peux me détaillé le raisonnement de la formule afin que je puisse la comprendre ça serait super pour mon enrichissement personnel sur Excel.
et encore merci à vous 2!

 

[Dugenou]

Question 1 : rien à voir : C_NC c'est juste le nom que j'ai donné à la formule (pour cadres non cadres)
elle retourne <> ou Cadre ou <>cadre qui sont les valeurs à rechercher dans ton tableau BD colonne A.
ça permet juste que la formule soit (un peu) plus lisible car on pourrait l'inclure et ça donnerait :

=SI([@[ANCIENNETE A LA DATE D''ARRET]]<0;"";INDEX(BD!$E$1:$E$38;SOMMEPROD((BD!$A$25:$A$38=SI(Tableau1[@[TYPE MALADIE]]="MAT";"<>";SI(Tableau1[@[CATEGORIE PROFESSIONNELLE]]="cadre";"cadre";"<>cadre")))*(BD!$B$25:$B$38=[@[TYPE MALADIE]])*(BD!$C$25:$C$38<[@[ANCIENNETE A LA DATE D''ARRET]])*(BD!$D$25:$D$38>[@[ANCIENNETE A LA DATE D''ARRET]])*LIGNE(BD!$B$25:$B$38))))

le *LIGNE(BD!$B$25:$B$38) permet à sommeprod de retourner un N° de ligne : en fait les éléments précédents sont des conditions :
(BD!$A$25:$A$38=C_NC)
(BD!$B$25:$B$38=[@[TYPE MALADIE]])
etc... : on a une matrice virtuelle de 1 et de 0 (vrai ou faux) pour chaque condition, en multipliant les conditions(matrices) avec * on obtient un tableau virtuel de 1 et de 0 selon que les conditions sont vraies ou fausses. En multipliant ce tableau (matrice) par la plage des N° de ligne LIGNE(BD!$B$25:$B$38) on obtient un numéro de ligne (à condition qu'il n'y ait qu'un seul résultat qui remplit toutes les conditions (si plusieurs on aura la somme des N° de ligne))

espérant avoir été clair : un essai d'explication en pj

Cordialement

 

[Rhumcoca]

Bonjour,

j'essaye de suivre mais je n'ai pas l'habitude d'utiliser ces formules.

Pour le début, j'ai compris:

SI([@[ANCIENNETE A LA DATE D''ARRET]]<0;"" permet de ne rien affiché si la date d'ancienneté est inférieur à zéro sinon d'utiliser la suite de la formule.
j'ai pour ma part changé ce début de formule par SI([@[ANCIENNETE A LA DATE D''ARRET]]="";"" étant donné qu'il n'est pas possible dans mon tableau que l'ancienneté soit en négatif, mais cela ne change pas grand chose.

ensuite ça commence à se compliquer, si j'ai compris ta formule, t'utilise la fonction index sous la forme référentielle en renvoyant un numéro de ligne calculé grâce à une suite de condition multiplié entre eux (la valeur fausse renvoyant un 0 alors, la suite d’argument multiplié renvoi à 0 et celle où toute les conditions sont remplis renvoi la valeur 1 multiplié par le numéro de ligne correspondant à la valeur à ressortir)

il reste cependant quelques choses que je ne maitrise pas du tout, c'est ta formule que tu as nommé C_NC, comment créer tu ce raccourci de formule?

j'ai une autre interrogation concernant mon tableau:
le numéro de matricule me permet de renvoyer, grâce à un autre tableau de suivi des contrats du personnel, les valeurs dans les colonnes "agence", "n° sécurité sociale", "catégorie du personnel", "catégorie métier", "nom", "prénom" et "date d'entrée en entreprise".
Mon souci est que dans mon tableau de suivi des contrats du personnel, je peux avoir plusieurs matricule identique, correspondant à la même personne (le nom, prénom et n° de sécurité sociale remontera toujours juste) mais avec certaine valeur qui peuvent changer (agence, catégorie professionnelle et catégorie métier). mes formule pour récupérer mes données est la suivante :
SIERREUR((SI((RECHERCHEV($A4;'Liste_RDTA'!$A$2:$AD$808;2;FAUX)="");"vérifier liste RDTA";RECHERCHEV($A4;'Liste_RDTA'!$A$2:$AD$808;2;FAUX)));"")

$A4 faisant référence à la cellule où est le matricule que je veux rechercher dans mon tableau
$A$2:$AD$808 faisant référence à la plage de donnée dans laquelle je veux chercher mon matricule
le 2 étant la colonne dans laquel se situe la valeur que je souhaite remonter.

le problème est que la valeur qui remonte est forcément la 1ère valeur rencontrée et ce n'est pas forcément celle que je souhaiterai faire remonter.
je souhaiterais mettre des conditions pour sélectionner la bonne valeur à remonter comme par exemple la date de début d'arrêt (dans tableau SUIVI_ARRÊT) supérieur à la date de début de contrat et inférieur à la date de fin de contrat ou supérieur à la date de début de contrat (si pas date de fin de contrat).

Merci pour vos retours.

 

[Rhumcoca]

j'ai trouvé réponse à ma 1ère question pour créer un "raccourci formule". dans l'onglet formule et définir un nom (à une cellule contenant la formule), je ne connaissais pas!

 

[Dugenou]

il te faudra utiliser la même méthode du sommeprod en mettant des conditions sur les dates comme tu les as expliquées date d'arrêt entre début et fin de contrat.
Cordialement

 

[Rhumcoca]

j'ai beau réflechir à cette formule, je n'arrive pas à voir comment la formuler, j'ai remis un morceau de mes tableaux pour que vous puissiez voir ma problématique.
sur SUIVI_ARRET, M. DESBROSSES Alexis (n° matricule 6276) est en congé paternité du 10/07/2017 au 20/07/2017, les données remontées sont bonnes car ma RECHERCHEV vient se reporter à la 1ère ligne rencontré, par contre M. DESBROSSES ayant évolué dans l'entreprise en passant Cadre et en changeant d'agence (9) au 24/07/2017, pour la MALADIE du 05/10/2017 au 10/10/2017, le n° matricule n°6276 devrait me remonter l'agence (9), la catégorie professionnelle (Cadre) et la catégorie métier (GESTIONNAIRE INFRASTRUCTURES).
Avez vous une solution à mon problème?
Merci d'avance.

 

[CISCO]

Bonjour

Dans quelles cellules veux tu des formules ? Je vois des listes déroulantes presque partout.

@ plus

 

[Rhumcoca]

Dans les colonnes (Agences), (Catégorie professionnelle) et (Catégorie métier) de la feuille SUIVI_ARRET, ce sont ces 3 valeurs qui sont susceptible d'évoluer dans la carrière d'un salarié.

 

[CISCO]

Bonjour

Je n'ai modifié que la formule de la colonne B. Est-ce ce qu'il te faut ?

@ plus

P.S : Formule matricielle, donc à valider avec les touches Ctrl+maj+entrer.

 

[Rhumcoca]

merci pour ton retour rapide, mais il doit y avoir un souci dans la formule, dans la ligne de la Maladie du 05/10/2017 au 10/10/2017, on devrait avoir l'agence 9 et non l'agence 5 car le salarié a changé de contrat au 24/07/2017 en changeant d'agence.

 

[CISCO]

Bonjour

La formule ne fait que lister toutes les lignes concernant le matricule concerné.

Je regarderai plus tard.

@ plus

 

[CISCO]

Bonjour

Pourquoi ne ramène t'on pas les informations concernant chacune des lignes contenant 6376 dans Liste_RDTA!A:A ? Quels critères doit-on utiliser ? Dans quelles colonnes sont-ils ?
D'accord, le 6276 a changé d'agence, mais pourquoi ne ramène t'on pas toutes les informations concernant ce matricule ?

@ plus