Cliquer ici pour télécharger le fichier
Rappel sur la recherche V : Elle permet de rechercher une valeur dans la première colonne d’une table de données puis de renvoyer la valeur d’une cellule qui se situe sur la même ligne que la valeur recherchée. Le problème c’est que la RECHERCHEV prend source sur un seul critère (= la valeur cherchée).
Principe : Une RECHERCHE multicritères correspond au cas où la valeur cherchée n’est pas sur une seule cellule mais qu’elle est composée de plusieurs critères ; Il faut donc prendre en compte tous les critères de la valeur cherchée pour effectuer la recherche.
Il y a 2 méthodes pour effectuer une RECHERCHE multicritères :
- Utiliser la formule RECHERCHEV
- Combiner les formules INDEX et EQUIV
Exemple : On a un tableau récapitulatif des kilomètres parcourus par plusieurs véhicules d’une entreprise et on souhaite retrouver tous les trajets effectués par le véhicule sélectionné dans la liste.
=> On a une table de recherche avec le Prénom et le NOM du patient et on souhaite retrouver la taille, le poids et l’âge en fonction du patient recherché.
1) RECHERCHE multicritères avec la formule RECHERCHEV
Étape 1 : Il faut assembler tous les critères de la recherche dans une seule cellule en concaténant les différentes informations.
= H6 &“ ”& I6
- H6 = La recherche se fait sur le Prénom + le NOM ; Il faut donc assembler ces 2 éléments pour définir la valeur à rechercher.
- &“ ”& = Le séparateur utilisé pour joindre les différents critères de recherche (un espace dans l’exemple) ; Il n’est pas obligatoire d’en utiliser un mais cela facilite la lecture de la valeur cherchée.
- H6 = La recherche se fait sur le Prénom + le NOM ; Il faut donc assembler ces 2 éléments pour définir la valeur à rechercher.
Étape 2 : Il faut créer une nouvelle colonne dans le tableau dans lequel on souhaite rechercher les informations pour avoir une correspondance avec la valeur cherchée qu’on vient de créer : Prénom + NOM.
= C4 &“ ”& D4
=> Il faut bien utiliser le même séparateur que pour la valeur cherchée (un espace dans l’exemple) pour que la formule RECHERCHEV puisse retrouver la correspondance dans le tableau.
Étape 3 : Il ne reste plus qu’à utiliser la formule RECHERCHEV à partir de la nouvelle valeur cherchée : Prénom + NOM.
= RECHERCHEV( $K$6 ; $B$4:$G$24 ; 4 ; FAUX)
- $K$6 = C’est la valeur qu’on souhaite retrouver dans la table matrice (Cellule qui contient Prénom + NOM)
- $B$4:$G$24 = C’est la table dans laquelle on veut rechercher le Prénom + NOM du patient et extraire les données situées sur la même ligne : Taille, Poids et Âge.
- 4 = C’est la colonne dans laquelle on veut extraire l’information souhaitée : Ici la taille donc en colonne 4 à partir du début de la table des patients.
- FAUX = On souhaite la correspondance exacte avec le Prénom et le NOM du patient.
= La taille du patient Julie BRUN est bien à l’intersection de la ligne correspondante à cette patiente et à la colonne qui correspond à la Taille (Colonne numéro 4 en partant de la colonne Prénom NOM).
2) RECHERCHE multicritères avec les formules INDEX et EQUIV
Ici par besoin de créer une nouvelle colonne dans la table de recherche avec le Prénom + NOM ; La recherche s’effectue en une seule formule sans avoir besoin de créer de nouveaux éléments.
- Fonction EQUIV = Permet de renvoyer le numéro de la ligne ou de colonne sur laquelle la valeur cherchée est trouvée.
- Fonction INDEX = Permet de renvoyer une valeur située dans une table de données en fonction d’un numéro de ligne et de colonne.
= On peut donc imbriquer la formule EQUIV dans la formule INDEX pour pouvoir en une seule formule renvoyer la valeur qui correspond au bon critère de recherche. Dans le cas où il y a plusieurs critères il faut créer autant de matrices de recherche qu’il y a de critères.
Dans l’exemple ces 2 formules nous permettent de retrouver le numéro de ligne de la valeur cherchée et le numéro de colonne sera lui indiqué manuellement en fonction de la donnée à renvoyer.
Étape 1 : Il faut commencer par retrouver le numéro de ligne dans laquelle se situe la valeur cherchée ; On utilise pour cela la formule Excel EQUIV.
= EQUIV( 1 ; (B4:$B$24 = $H$6) * ($C$4:$C$24 = $I$6) ; 0)
- 1 = C’est la valeur cherchée ; On recherche « 1 » car la matrice suivante renvoie 1 dans le cas où les 2 critères de recherche sont respectés (Prénom et NOM cherchés sont sur la même ligne)
- (B4:$B$24 = $H$6) = On recherche le Prénom dans la colonne Prénom.
- ($C$4:$C$24 = $I$6) = On recherche le NOM dans la colonne NOM.
- 0 = On recherche la correspondance exacte avec la valeur cherchée.
= Pour la patiente Julie BRUN la formule EQUIV recherche 1 et le 1 apparaît en 5ème position dans le tableau puisque c’est sur cette ligne que la formule retrouve le Prénom Julie en face du Nom BRUN ; La formule renvoie donc le résultat 5 qui correspond à la ligne du tableau pour laquelle est retrouvée le patient avec le Prénom Julie et le nom BRUN.
= Pour les versions hors abonnement office 365 la formule renvoie un message d’erreur car il s’agit d’une formule matricielle (on a définit une matrice avec 2 conditions : une pour le prénom et une pour le nom), il faut donc la valider avec les touches CTRL + MAJ + ENTRÉE.
Étape 2 : On peut alors imbriquer la formule EQUIV dans la formule INDEX.
=INDEX( $D$4:$F$24 ; EQUIV( 1 ; (B4:$B$24 = $H$6) * ($C$4:$C$24 = $I$6) ; 0) ; 1)
- INDEX( $D$4:$F$24 = C’est la table dans laquelle on veut rechercher le Prénom + NOM du patient et extraire les données situées sur la même ligne : Taille, Poids et Âge.
- ; EQUIV( 1 ; (B4:$B$24 = $H$6) * ($C$4:$C$24 = $I$6) ; 0) = Formule qui permet de retrouver le numéro de ligne du patient recherché.
- ; 1) = C’est la colonne dans laquelle on veut extraire l’information souhaitée : Ici la taille donc en colonne 1 à partir du début de la table de recherche inscrite dans le premier argument de la formule INDEX et qui commence à la colonne « Taille ».
= La encore il faut penser à valider la formule avec les touches CTRL + MAJ + ENTRÉE car des matrices sont utilisées dans la formule EQUIV pour retrouver le numéro de ligne.
Étape 3 : Il faut étirer la formule sur l’ensemble des informations puis modifier manuellement le numéro de colonne dans la formule INDEX.
= Pour retrouver le Poids du patient il faut indiquer le numéro de colonne 2 et pour retrouver l’âge le numéro de colonne 3 car la table de recherche de la formule INDEX débute à partir de la colonne « Taille » du tableau « LISTE PATIENTS ».
Résultat :
=> Voir aussi comment faire une RECHERCHEV avec plusieurs résultats
