Recomposer un tableau avec la fonction recherche-v ?

[clercren]

Bonjour à tous,

Je viens à vous car je n'ai pas trouvé de piste de solution sur le forum et finis par tourner en rond sur mon problème

Je dispose d'une liste de données dont je souhaite en extraire quelques informations précises.
EX: Liste:
Ecole 1 Infirmiers
Ecole 1 Aide-Soignants
Ecole 1 Sages-Femmes
Ecole 2 Aide-Soignants
Ecole 3 Infirmiers
Ecole 3 Aide-Soignants

L'objectif est d'obtenir la liste des formations par Ecole. Le résultat attendu serait de cette forme:
Colonne A Colonne B Colonne C Colonne D
Ecole 1 Infirmiers Aide-Soig Sages-Femmes
Ecole 2 Aide-Soig
Ecole 3 Infirmiers Aide-soig

Pour arriver à cette solution, j'ai pensé à une première technique: le tableau croisé dynamique. En effet, ce qui m'importe est de recomposer un tableau par école avec la liste des formations qu'elle propose.

Une seconde solution, serait de recomposer un tableau en utilisant la fonction Recherchev ou recherche -h, mais je me trouve face à mes limites en matière d'usage de ces formules.
Ainsi, la fonction recherche-v ou h ne semblent pas pouvoir permettre la recherche à un niveau de "profondeur" ou de "hiérarchisation" suffisant pour obtenir l'ensemble des informations recherchées. J'ai beau jouer sur la dernière variable de la fonction recherche-v (XXX;XXX;XXX;0 ou 1 ou -1), je n'obtiens que les 2 premiers diplômes d'un même établissement.

Ma question:
Comment faire pour recomposer un tableau tel que demander lorsque la base de données fait appel à une référence de recherche (1e colonne = nom Ecole) qui se trouve de manière identique sur plusieurs lignes et qui sur les autres colonnes font références à des données uniques ?
A mon sens seul le Tableau croisé dynamique permettait d'y arriver, mais je ne souhaite pas en arriver à cette solution. Je préferais utiliser une ou des fonctions imbriquées.

Un fichier exemple est joint à ma demande.

Je vous remercie tous par avance de l'attention portée à ma demande.

 

[Jocelyn]

Re : Recomposer un tableau avec la fonction recherche-v ?

Bonjour le Forum,
Bonjour clercren,

En pièce jointe un essai par formule avec les explication regardes et dis nous.

Jocelyn

 

[clercren]

Re : Recomposer un tableau avec la fonction recherche-v ?

Bonjour, c'est exactement l'application que je cherchais !
Merci beaucoup car cela va vraiment me permettre d'avancer.

Cependant, et malgré les explications détaillées dans le document, je ne comprend pas la construction des formules:

1°: tu parles de plages définies par la fonction décaler en leur ayant donner un nom "Centre" ou "metier". Or je ne vois de nom défini pour ces plages... Faut-il en définir un ou pas ? Je ne connais l'usage de la fonction décaler.

2°: dans la matrice du tableau de destination pourrais tu m'expliquer la logique d'intervention.

Rappel de la matrice: =INDEX(ColA;MIN(SI((Centre<>"")*(NB.SI($F$10:F10;Centre)=0);LIGNE(Centre))))&""

Voici ce que je comprends: =Index (aller chercher la valeur dans le tableau A(dans la colonne A;donner la version la plus petite de (si le nom du centre apparaît déjà une fois, alors ne pas remettre le nom du centre; sinon mettre le nom du centre). Est-ce bien à comprendre comme cela ?

En tous cas vraiment merci pour la solution. Je vais l'utiliser par mimétisme mais j'aimerais bien comprendre la logique.

 

[Jocelyn]

Re : Recomposer un tableau avec la fonction recherche-v ?

Bonjour,

Alors un essai d'explication complémentaire,

1) la plage nommée "Centre" pour nommer une plage menu insertion => nom => définir ca je pense que tu connaissais puisque tu avais une plage apellée "liste" ta plage est directement définie par la saisie des cellules dans le champ "Fait référence à" de cette façon =Feuil1!$A$5:$A$12 le soucis étant que si tu mets une valeur en A13 elle ne sera pas prise en compte.

pour la plage nommée centre dans le champ "Fait référence à" j'ai mis une fonction decaler() : =DECALER(Feuil1!$A$5;;;NBVAL(Feuil1!$A:$A)-2)

ici on spécifie a excel de démarrer la plage en Feuil1!$A$5 ensuite les deux critere vide ;;; aurait servi si nous avions voulu la valeur d'une cellule unique endecalant la cellule de départ de X lignes et ou X colonne ensuite NBVAL(Feuil1!$A:$A)-2 permet de définir la hauteur de la plage ici le nombre de valeur contenues dans la colonne le moins -2 de fin servant a supprimer les 2 valeur de titre se trouvant en colonne A ligne 2 et 4 .

Donc si l'on ajoute une valeur en A13 la hauteur de la plage vas augmenter d'un et la valeur sera prise en compte.

derniere le champ définissant la hauteur il y a un champ définissant la largeur de la plage inutile ici mais utile si la plage est en ligne.

2) La formule : =INDEX(ColA;MIN(SI((Centre<>"")*(NB.SI($F$10:F10;C entre)=0);LIGNE(Centre))))&""

ton analyse est bonne sauf qu'il ne faut pas la définir comme étant la version la plus petite la partie LIGNE(Centre) et le MIN() combiné disent si les condition que tu a toi même trouvée sont respecter renvoyer la valeur de la colonne dont le numéro de ligne est le plus petit.

donc dans la premiere cellule ou se trouve cette formule on va retrouver la valleur de la premiere cellule de A et dans la 2° cellule ou setrouve cette formule on va mettre la caleur de la prochaine plus petite ligne correspondante aux condition et ainsi de suite le &"" de la fin permet de ne pas renvoyer de 0 quand il n'y a plus de valeur correspondant aux conditions

Dans la formule =INDEX(ColB;MIN(SI((metier<>"")*(NB.SI($F11:F11;metier)=0)*(Centre=$F11);LIGNE(metier))))&"" même fonctionnement que précédement mais avec la colonne B en ajoutant la condition que sur la ligne recherche A soit également au nom de centre pour le quel on cherche les formation disponible.

Voila j'espere avoir été clair si ce n'est pas le cas n'hésite pas.

Jocelyn

 

[clercren]

Re : Recomposer un tableau avec la fonction recherche-v ?

Merci Jocelyn,

Toutes tes explications et l'exemple soont très pédagogiques et c'est sympa d'apprendre à devenir autonome avec des sépcialistes prêts à nous aider.

Je vais essayer de finaliser mon document et en cas de blocage, je me permettrais de poster de nouveau un petit message.

Encore merci !

 

[clercren]

Re : Recomposer un tableau avec la fonction recherche-v ?

Jocelyn, ou d'autres contributeurs tout aussi bienvenus, une dernière question.

Grâce à ta fonction matricielle, j'ai pu recomposer mon tableau un peu à la manière d'un tableau croisé dynamique. Pour une école, j'ai les formations qu'il dispense.

Cependant, tout à la manière d'un tableau croisé dynamique, mais avec des formules toujours, je souhaite qu'à partir d'une liste déroulante me permettant de sélectionner l'école de mon choix, je ne puisse avoir accès qu'aux seules informations concernant cet établissement.

Dans le document joint, il me semble que j'ai résolu l'essentiel de la démarche.
Mon seul problème sur lequel je bute est que dans la fonction matricielle, je souhaiterais faire référence à la cellule dans laquelle apparaît la liste de choix... J'ai beau essayer mais cela me retourne sans cesse une erreur.

Question: peut-on faire référence à une cellule comme "critère" dans une formule matricielle ? J'utilise cette méthode avec d'autres formules mais là, je bloque

Pour mieux vous aider à comprendre ma demande, vous trouverez le fichier exemple.

Encore merci par avance à tous ceux qui pourront m'aider.

 

[Jocelyn]

Re : Recomposer un tableau avec la fonction recherche-v ?

re,

pas sur d'avoir tout compris mais un essai en ligne 56 dans le tableau 3 j'ai transposé la formule du tableau 2 pour quelle pointe sur la cellule I52 dis moi si c'est ce que tu attendais.

Jocelyn