Effectuer une recherche dans un tableau selon un double critère [Résolu]

klendatul

XLDnaute Nouveau
Bonjour à tous,

Je cherche despérement à faire une recherche dans un tableau en utilisant un double critère pour éviter les nombreux doublons, mais là je suis dépassé. Je crois que je dois utiliser les fonctions "index" et "equiv" mais là je sèche, je n'arrive pas trop à comprendre le fonctionnement.

J'ai mis mon tableau (en simplifié au maximum, le vrai a 3000 lignes) avec toutes les explications et les commentaires dessus.


Pour résumer:

Je bosse sur des diplômes et les spécialités de ces diplômes . Par exemple Bac - scientifique (diplôme - spécialité), BTS - informatique, CAP - boulangerie, etc, etc ...

Chaque diplôme est codifié : par exemple : BAC = 03 , BTS = 28, etc ...
Chaque spécialité est codifiée aussi : par exemple pour le bac: scientifique = 01 , litteraire = 02, etc ...

Donc un bac - scientifique sera codifié 03 - 01 d'après mon exemple.

Mon souci :
Il s'agit de faire appairaitre les codifications correspondantes lorsque les utilisateurs utilisent les listes déroulantes.
Pour codifier automatiquement le diplôme, aucun souci, une simple rechercheV suffit.

En revanche, je n'arrive pas à faire apparaitre la codification de la spécialité.
Il me faudrait une formule pour dire à excel de regarder la codification de la spé d'après le nom de la spécialité ET le nom du diplôme car il y a des doublons dans les noms des spécialités pour l'ensemble des diplomes (par exemple , "boulangerie" existe aussi bien pour le "CAP" que pour le "BEP", il faut donc qu'excel regarde la codification de "boulangerie" en sachant si c'est un "cap" ou "bep".


Merci à l'âme charitable qui réussira à m'aider je désespère depuis 3 jours là ! :eek:
 

Pièces jointes

  • excel.xls
    43 KB · Affichages: 73
  • excel.xls
    43 KB · Affichages: 72
  • excel.xls
    43 KB · Affichages: 70
Dernière édition:

Dugenou

XLDnaute Barbatruc
Re : Effectuer une recherche dans un tableau selon un double critère

Bonjour,
j'ai peut-être pas tout compris : il me semble que cette formule convient :
Code:
=RECHERCHEV(A17;$E$2:$F$63;2;FAUX)
je n'avais surtout pas bien lu la demande : si l'intitulé est présent plusieurs fois avec des codif diplômes différentes :
Code:
=INDEX(F1:F63;SOMMEPROD(($D$2:$D$63=A11)*(libelle_diplome=A17)*LIGNE(libelle_diplome));)
Cordialement
 
Dernière édition:

hbenalia

XLDnaute Occasionnel
Re : Effectuer une recherche dans un tableau selon un double critère

Bonjour à tous,

Un résultat avec la formule matricielle (à valider par ctrl+maj+entree) suivante:

Code:
=INDEX(codif_libelle;EQUIV(1;(codif=$A$11)*(libelle_diplome=$A$17);0))

Le nom codif correspond à la plage Feuil1!D2:D63

Cordialement
 

Robert

XLDnaute Barbatruc
Repose en paix
Re : Effectuer une recherche dans un tableau selon un double critère

Bonjour le fil, bienvenu Klendatul, bonjour le forum,

Une proposition VBA si ça t'intéresse. Double-clique dans A9 pour lancer l'UserForm1. Choisit un type de diplôme, clique ensuite dans la ListBox1 pour voir le code du diplôme ou double-clique dans cette même ListBox1 pour reporter les données dans le tableau.
Le code :
Code:
Private pl As Range 'déclare la variable pl (PLage)

Private Sub UserForm_Initialize()
Me.ComboBox1.List = Sheets("Feuil1").Range("A2:A5").Value
End Sub


Private Sub ComboBox1_Change()
Dim dl As Integer 'déclare la variable dl (Dernière Ligne)

Me.ListBox1.Clear 'vide la ListBox1
Me.TextBox1.Value = Sheets("Feuil1").Cells(Me.ComboBox1.ListIndex + 2, 2) 'récupère le codif
Application.ScreenUpdating = False 'masque les changements à l'écran
With Sheets("Feuil1") 'prend en compte l'onglet "Feuil1"
    dl = .Cells(Application.Rows.Count, 4).End(xlUp).Row 'définit la dernière ligne éditle dl de la colonne 4 (=D)
    Set pl = .Range("D2:D" & dl) 'définit la plage pl
    .Range("D1").AutoFilter 'active le fitre automatique en D1
    .Range("D1").AutoFilter field:=1, Criteria1:=Me.TextBox1.Value 'filtre la colonne D selon le codif
    On Error Resume Next 'gestion des erreurs (si une éreur est générée, passe à la ligne suivante
    Me.ListBox1.List = pl.Offset(0, 1).SpecialCells(xlCellTypeVisible).Value 'alimente la Listbox1 (génére une erreur si il n'y a qu'une seule ligne ?)
    If Err <> 0 Then 'condition : si une erreur a été générée
        Err = 0 'annule l'erreur
        Me.ListBox1.AddItem pl.Find(Me.TextBox1.Value, , xlValues, xlWhole).Offset(0, 1).Value 'alimente la ListBox1 de l'unique ligne
    End If 'fin de la condition
    On Error GoTo 0 'annule la gestion des ereurs
    .Range("D1").AutoFilter 'désactive le filrte automatique en D1
End With 'fin de la prise en compte de l'onglet "Feuil1"
Application.ScreenUpdating = True 'affiche les changements à l'écran
End Sub


Private Sub ListBox1_Click() 'au click dans la ListBox1
Me.TextBox2.Value = pl.Offset(0, 1).Find(Me.ListBox1.Value, , xlValues, xlWhole).Offset(0, 1) 'récupère le code du diplôme
End Sub


Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) 'au double-clic dansla ListBox1
With Sheets("Feuil1") 'prend en compte l'onglet "Feuil1"
    'répercute les données dans le tableau
    .Range("A9").Value = Me.ComboBox1.Value 'place le type de diplome en A9
    .Range("A11").Value = Me.TextBox1.Value 'place le codif en A11
    .Range("A11").NumberFormat = "00" 'format de la cellule
    .Range("A17").Value = Me.ListBox1.Value 'place le diplôme en A17
    .Range("A19").Value = Me.TextBox2.Value 'place le code du diplome en A19
    .Range("A19").NumberFormat = "00" 'format de la cellule
End With 'fin de la prise en compte de l'onglet "Feuil1"
Unload Me 'vide et ferme l'UserForm
End Sub

Le fichier :
 

Pièces jointes

  • Klendatul_v01.xls
    60.5 KB · Affichages: 71

klendatul

XLDnaute Nouveau
Re : Effectuer une recherche dans un tableau selon un double critère

Merci pour vos réponses à tous, pour l'instant aucune de vos réponses ne marchent (sauf la proposition VBA, que je n'utiliserais pas pour des raisons de limitations au boulot).

J'essaye encore, je me suis peut être trompé en adaptant vos formules, je cherche :D

Petite question : à quoi sert de sert ctrl + maj + entré ? A quel moment doit-on le faire ?

A quoi servent les fonctions "sommeprod" et "ligne" ?

Merci je vous tiens au courant :) :)
 

Dugenou

XLDnaute Barbatruc
Re : Effectuer une recherche dans un tableau selon un double critère

Pour ma part :
sommeprod sert à faire une recherche à plusieurs critères: ici 2 critères.
Ligne() permet d'obtenir un N° de ligne comme résultat du sommeprod et donc d'appeler la ligne contenant le texte voulu dans la fonction Index
bien sur il faut adapter les plages à ton véritable classeur
Cordialement

Bravo et Merci Robert pour la démo userform !!
 

Pièces jointes

  • klendatul.xls
    47 KB · Affichages: 58

klendatul

XLDnaute Nouveau
Re : Effectuer une recherche dans un tableau selon un double critère

Je suis désolé mais je galère à mort avec excel, je pense qu'avec la rechercheV j'ai atteint mon maximum :D

Pour que je comprenne bien afin de bien l'adapter dans mon tableau de 3000 lignes, pourrais tu me réexpliquer en détail ta formule et pourquoi tu fais les critères de recherche dans cet ordre précis, à quoi serve le symbole "*", etc ...

Le but du jeu c'est que je sache le faire tout seul la prochaine fois :)

En te remerciant par avance si tu veux bien !
 

klendatul

XLDnaute Nouveau
Re : Effectuer une recherche dans un tableau selon un double critère

Bonjour à tous,

Un résultat avec la formule matricielle (à valider par ctrl+maj+entree) suivante:

Code:
=INDEX(codif_libelle;EQUIV(1;(codif=$A$11)*(libelle_diplome=$A$17);0))

Le nom codif correspond à la plage Feuil1!D2:D63

Cordialement


Je suis désolé mais ta formule ne fonctionne pas , même dans mon tableau de test, j'ai N/A :(

Dans l'analyse d'erreur lorsqu'il en est à "codif=$A$11", il renvoit "faux" dans tous les cas.

J'ai bien fait aussi le ctrl+maj+entrée (à quoi cela sert-il d'ailleurs)

Merci quand même !
 

Dugenou

XLDnaute Barbatruc
Re : Effectuer une recherche dans un tableau selon un double critère

Salut :
=INDEX(F1:F63;SOMMEPROD(($D$2:$D$63=A11)*(libelle_diplome=A17)*LIGNE(libelle_diplome));)

INDEX(F1:F63 : permettra de renvoyer les codes des spécialités : remplacer F1:F63 par la colonne qui contient les codes de spécialités en commençant à la ligne 1 même si les données ne commencent que sur la ligne 10 ou 25.
SOMMEPROD(($D$2:$D$63=A11)*(libelle_diplome=A17)*LIGNE(libelle_diplome)) : remplacer $d$2:$d$63 par la zone qui contient les codes de diplomes, adapter les zones nommées libelle_diplome et les cellules a11 et a17 aux vraies données.
Sommeprod, quand il est écrit de cette façon : sommeprod((critère1)*(critère2)*plage de valeurs) donne la somme des valeurs qui correspondent aux deux critères.
avec SOMMEPROD(($D$2:$D$63=A11)*(libelle_diplome=A17)*LIGNE(libelle_diplome)) on va donc obtenir la somme des N° de lignes qui correspondent aux critères ($D$2:$D$63=A11) et (libelle_diplome=A17) (bien sur il faut qu'il n'y ait qu'une seule réponse possible !)
 

klendatul

XLDnaute Nouveau
Re : Effectuer une recherche dans un tableau selon un double critère

Salut :
=INDEX(F1:F63;SOMMEPROD(($D$2:$D$63=A11)*(libelle_diplome=A17)*LIGNE(libelle_diplome));)

INDEX(F1:F63 : permettra de renvoyer les codes des spécialités : remplacer F1:F63 par la colonne qui contient les codes de spécialités en commençant à la ligne 1 même si les données ne commencent que sur la ligne 10 ou 25.
SOMMEPROD(($D$2:$D$63=A11)*(libelle_diplome=A17)*LIGNE(libelle_diplome)) : remplacer $d$2:$d$63 par la zone qui contient les codes de diplomes, adapter les zones nommées libelle_diplome et les cellules a11 et a17 aux vraies données.
Sommeprod, quand il est écrit de cette façon : sommeprod((critère1)*(critère2)*plage de valeurs) donne la somme des valeurs qui correspondent aux deux critères.
avec SOMMEPROD(($D$2:$D$63=A11)*(libelle_diplome=A17)*LIGNE(libelle_diplome)) on va donc obtenir la somme des N° de lignes qui correspondent aux critères ($D$2:$D$63=A11) et (libelle_diplome=A17) (bien sur il faut qu'il n'y ait qu'une seule réponse possible !)

Alors la partie "index" , ok je comprend nickel :D

La partie "sommeprod" me pose problème et je ne comprend rien :D

Sur internet, normalement on doit utiliser ";" et non pas "*", il y a une différence ??? Pourquoi veut-on additionner des matrices ? Je comprend pas que vient faire cette fonction ici !

Je n'ai pas compris non plus le but de la fonction "ligne" . Le résultat se multiplie avec le reste ?

J'ai remplacé toutes les valeurs par les bonnes colonnes dans mon tableau j'ai désespérément un N/A et je ne peux pas vraiment utiliser l'analyseur de formule avec avec plusieurs milliers de lignes il est pas ergonomique du tout.

J'ai envie de pleurer lol !

Je suis désolé si tu pouvais essayer de m'expliquer une dernière fois ! Je ne dois pas faire ctrl+maj+entré ?
 

klendatul

XLDnaute Nouveau
Re : Effectuer une recherche dans un tableau selon un double critère

Je pense qu'il y a un petit bug dans ta formule , si je rentre cette formule :
=INDEX(F1:F63;SOMMEPROD(($D$2:$D$63=A11)*(libelle_diplome=A17)*LIGNE(libelle_diplome)))

Ca marche nickel pour les 2 premiers diplômes mais pas du tout pour le "bac". J'ai beau changé la spécialité, la cellule ne varie pas.
Dans l'analyseur de formule, tout est en "faux", il n'arrive pas à retrouver la correspondance avec le "bac".

Bon, je suis à deux doigts de me jeter par la fenêtre :D

Merci !
 

hbenalia

XLDnaute Occasionnel
Re : Effectuer une recherche dans un tableau selon un double critère

Bonjour à tous,

La formule proposée plus haut est une formule matricielle à valider par CTRL+MAJ+ENTREE (CTRL+SHIFT+ENTER) après la saisie de la formule et non seulement par ENTREE (validation des formules ordinaires)... Des accolades seront ajoutées automatiquement en début et fin de cette formule...

Voir fichier attaché avec l'application de la formule proposée à ton fichier exemple...

NB: L'erreur #N/A de la formule est due par la non-validation de la formule en matricielle par les touches CTRL+MAJ+ENTREE

Cordialement
 

Pièces jointes

  • Copy of excel.xls
    38 KB · Affichages: 65
  • Copy of excel.xls
    38 KB · Affichages: 57
  • Copy of excel.xls
    38 KB · Affichages: 55
Dernière édition:

klendatul

XLDnaute Nouveau
Re : Effectuer une recherche dans un tableau selon un double critère

Bonjour à tous,

La formule proposée plus haut est une formule matricielle à valider par CTRL+MAJ+ENTREE (CTRL+SHIFT+ENTER) après la saisie de la formule et non seulement par ENTREE (validation des formules ordinaires)... Des accolades seront ajoutées automatiquement en début et fin de cette formule...

Voir fichier attaché avec l'application de la formule proposée à ton fichier exemple...

NB: L'erreur #N/A de la formule est due par la non-validation de la formule en matricielle par les touches CTRL+MAJ+ENTREE

Cordialement


Merci beaucoup mais est-ce tu pourrais m'expliquer ce que ça apporte ?! :)

Au fait la formule ne fonctionne pas lorsque l'on choisit le diplôme "bac" !
 
Dernière édition:

hbenalia

XLDnaute Occasionnel
Re : Effectuer une recherche dans un tableau selon un double critère

Bonjour à tous,

Merci beaucoup mais est-ce tu pourrais m'expliquer ce que ça apporte ?! :)

Au fait la formule ne fonctionne pas lorsque l'on choisit le diplôme "bac" !

La formule n'a pas bien fonctionnée lorsque l'on choisit le diplôme "bac" à cause des formats des nombres, ils sont parfois numériques et parfois textes (en mettant le symbole apostrophe ' devant) dans ton fichier... En choisissant l'un ou l'autre des deux formats pour tous les nombres de la feuille, la formule fonctionnera à merveille...

Explication de la formule:
Code:
=INDEX(codif_libelle;EQUIV(1;(codif=$A$11)*(libelle_diplome=$A$17);0))
cette formule donne le contenu de la colonne "codif_libelle" se trouvant à la ligne donnée par la partie de formule:
Code:
EQUIV(1;(codif=$A$11)*(libelle_diplome=$A$17);0)
qui cherche l'ordre exacte du nombre 1 dans la matrice créée selon les deux critères (codif=$A$11)*(libelle_diplome=$A$17), le symbole "*" remplace le "et" logique, donc si les deux critères sont satisfaits l'élément de la matrice est un 1 sinon c'est un 0, et une matrice (tableau) de valeurs (0 et 1) est créée, et la fonction EQUIV donne le premier Ordre (le plus petit ordre) du nombre 1 dans cette matrice qui sera le numéro de la ligne recherchée et qui sera utilisé par la fonction INDEX pour donner la valeur recherchée satisfaisant les deux conditions...

Dans le fichier attaché, le format texte a été choisi pour tous les nombres contenus dans les plages B2:B5 et D2:D63, on remarquera la saisie de l'apostrophe devant ces nombres...


Cordialement
 

Pièces jointes

  • Copy of excel.xls
    38.5 KB · Affichages: 56
  • Copy of excel.xls
    38.5 KB · Affichages: 54
  • Copy of excel.xls
    38.5 KB · Affichages: 52
Dernière édition:

klendatul

XLDnaute Nouveau
Re : Effectuer une recherche dans un tableau selon un double critère

Bonjour à tous,



La formule n'a pas bien fonctionnée lorsque l'on choisit le diplôme "bac" à cause des formats des nombres, ils sont parfois numériques et parfois textes (en mettant le symbole apostrophe ' devant) dans ton fichier... En choisissant l'un ou l'autre des deux formats pour tous les nombres de la feuille, la formule fonctionnera à merveille...

Explication de la formule:
Code:
=INDEX(codif_libelle;EQUIV(1;(codif=$A$11)*(libelle_diplome=$A$17);0))
cette formule donne le contenu de la colonne "codif_libelle" se trouvant à la ligne donnée par la partie de formule:
Code:
EQUIV(1;(codif=$A$11)*(libelle_diplome=$A$17);0)
qui cherche l'ordre exacte du nombre 1 dans la matrice créée selon les deux critères (codif=$A$11)*(libelle_diplome=$A$17), le symbole "*" remplace le "et" logique, donc si les deux critères sont satisfaits l'élément de la matrice est un 1 sinon c'est un 0, et une matrice (tableau) de valeurs (0 et 1) est créée, et la fonction EQUIV donne le premier Ordre (le plus petit ordre) du nombre 1 dans cette matrice qui sera le numéro de la ligne recherchée et qui sera utilisé par la fonction INDEX pour donner la valeur recherchée satisfaisant les deux conditions...

Dans le fichier attaché, le format texte a été choisi pour tous les nombres contenus dans les plages B2:B5 et D2:D63, on remarquera la saisie de l'apostrophe devant ces nombres...


Cordialement


Super, merci pour l'explication, j'ai presque tout compris en lisant du 1er coup.

Encore une petite question bonus (désolé ! même 2 en fait):

comment convertir tous les nombres en texte ? (mon souci vient du fait que la codification est du format xxxx par exemple 0001, 0121, 0A15, etc ... )

Toujours cette histoire de ctrl+maj+entrée que je ne comprend pas malgrés mes recherches sur le net ...

Merci en tout cas !
 

Discussions similaires

Statistiques des forums

Discussions
314 450
Messages
2 109 726
Membres
110 552
dernier inscrit
jasson