Introduction
Aviez-vous pris connaissance de l’article de blog de la semaine dernière ?
J’y abordais une astuce utilisant les plages nommées pour contourner les fonctions de recherche classiques de Microsoft Excel.
Cette semaine, pas de détour, nous allons voir ensemble le fonctionnement de deux de ces fonctions classiques de recherche : RECHERCHEV & RECHERCHEH.
Pour la suite de l’article, je vais essentiellement évoquer la fonction RECHERCHEV, mais sachez d’ores et déjà que la fonction RECHERCHEH est très similaire.
Démysthifier pour rassurer
Saviez-vous que la fonction RECHERCHEV fait partie des sujets faisant le plus l’objet de requêtes sur les supports techniques autour de Microsoft Excel ? Pourtant, on va voir dans cet article que…
• Quand le mécanisme de la fonction est compris ;
• Quand les erreurs classiques sont mises en lumière ;
… On se rend compte que cette fonction est en vérité très simple d’utilisation.
Heureusement, car c’est une fonction de recherche incontournable d’Excel !
A vrai dire, c’est même la fonction de recherche la plus souvent utilisée !
Alors, je vais vulgariser au mieux cette fonction pour la rendre la plus compréhensible possible.
Dans quel cas utiliser la fonction RECHERCHEV ?
Je vous invite tout d’abord à télécharger ce fichier Excel vierge. Cela vous permettra de mettre en pratique ce que nous allons voir dans cet article.
Dans ce fichier, vous trouverez la base de données clients d’une banque (feuille “CC_Customer_Data” ) avec quelques informations les concernant :
Nous allons voir 2 cas concrets dans lesquels la fonction RECHERCHEV peut s’avérer très utile.
• Cas 1 – Recherche par correspondance exacte
Dans ce cas de figure, nous allons pouvoir aller chercher les informations demandées (nombre de visites et d’appels) dans la base de données clients, à partir de la clé d’identification des clients (“Customer_key” : seule information commune aux deux sets de données présentés jusque-là).
• Cas 2 – Recherche par correspondance approximative
Dans ce cas de figure, nous allons d’abord rechercher, par correspondance exacte, les informations de plafond de dépenses ( “Avg_Credit_Limit” ) et de nombre de cartes de crédit ( “Total_Credit_Cards” ) des clients dans la base de données clients, à partir de leur numéro d’identification ( “Customer_Key” : seule clé commune, comme dans le cas précédent).
Dans un second temps, une fois les valeurs de ces informations renvoyées dans les colonnes du tableau, nous devrons créer des petites tables de conversion pour permettre de les rattacher à des paliers de valeurs.
Syntaxe de la fonction
Nous résoudrons ces 2 cas un peu plus loin dans l’article, pas à pas.
Mais avant cela, nous allons étudier la syntaxe de la fonction RECHERCHEV.
Cette fonction comporte 4 arguments :
1. Valeur_cherchée : Attention, le terme est ambigu, cet argument correspond en fait à la clé de recherche (le critère) ;
2. Table_matrice : La base de données qui contient l’information recherchée (le résultat à renvoyer avec cette fonction) et la clé de recherche ;
3. No_index_col : Le numéro de colonne de la Table_Matrice qui contient l’information à renvoyer ;
4. [valeur_proche] : Méthode de recherche : Par correspondance approximative (valeur VRAI) ou par correspondance exacte (valeur FAUX).
Voyons cela à l’œuvre dans nos deux études de cas décrites plus haut.
Résolution des cas pratiques
• Cas 1 – Recherche par correspondance exacte
1. Valeur_cherchée
Rappel : Il s’agit de la clé de recherche (le critère).
Dans notre cas de figure, c’est la clé d’identification client qui va permettre de rechercher nos informations dans la base de données clients.
2. Table_matrice
Rappel : Il s’agit de la base de données qui contient à la fois l’information que nous voulons renvoyer grâce à la fonction RECHERCHEV et la clé de recherche.
ATTENTION : Il convient de sélectionner cette matrice de recherche selon deux conditions :
– La première colonne de la Table_Matrice sélectionnée doit contenir la clé de recherche ;
– La Table_Matrice sélectionnée doit englober la colonne et la ligne qui contient l’information que nous souhaitons renvoyer avec la fonction.
Dans notre cas de figure :
– La première colonne de la plage sélectionnée doit donc être la colonne « Customer_key » dans notre base de données clients ;
– La plage doit s’étendre au moins jusqu’à la colonne « Total_visits_bank » et contenir les 660 lignes d’enregistrements pour être sûr de ne pas manquer la bonne ligne.
REFLEXE A PRENDRE : Dès que votre Table_Matrice a été sélectionnée, enfoncez la touche F4 du clavier pour figer cette plage, je vous expliquerai pourquoi plus loin dans cet article, quand j’aborderai les erreurs communes.
3. No_index_col
Rappel : Il s’agit du numéro de la colonne de la Table_Matrice qui contient l’information à renvoyer grâce à la fonction RECHERCHEV.
Dans notre exemple, nous voulons renvoyer la valeur de « Total_visits_bank ».
Dans la plage de recherche sélectionnée, c’est la 4ème colonne qui contient cette information.
On indique donc la valeur « 4 » en tant que 3ème argument de la fonction.
4. [valeur_proche]
Rappel : Cet argument définit la méthode de recherche. 2 choix sont possibles :
-Indiquer « VRAI » active la recherche par correspondance approximative des valeurs ;
-Indiquer « FAUX » active la recherche par correspondance exacte des valeurs.
Dans notre cas de figure, nous recherchons une correspondance exacte entre notre clé de recherche et l’une des valeurs de la 1ère colonne de notre Table_Matrice.
On indique donc la valeur « FAUX ».
Il faut savoir que la recherche par correspondance exacte est de loin la plus souvent utilisée.
ATTENTION : Cet argument est un argument facultatif, mais qui est une source d’erreur commune, que nous verrons plus loin dans cet article.
Résumé
Vous l’aurez donc compris, la fonction RECHERCHEV par correspondance exacte permet de renvoyer une information contenue dans une base de données, à partir d’une clé de recherche, en précisant dans quelle colonne est stockée l’information à renvoyer (cela sous-entend que les enregistrements de la base de données sont sous forme de lignes).
Ici, pour retrouver le nombre de visites à la banque des clients suivis par notre responsable, nous avons recherché cette information dans notre base de données clients, à partir des clés d’identification des clients fournies par notre responsable. Dans notre fichier, dans la feuille « Cas1-Corresp_exacte », cela s’est traduit par la formule suivante dans la cellule D14 :
=RECHERCHEV(C14;$K$14:$P$673;4;FAUX)
Pour information, la fonction RECHERCHEH par correspondance exacte a exactement le même fonctionnement mais de façon transposée : Elle permet de renvoyer une information contenue dans une base de données à partir d’une clé de recherche, en précisant dans quelle ligne est stockée l’information à renvoyer (cela sous-entend que les enregistrements de la base de données sont sous forme de colonnes). Voir la feuille « Cas3-RECHERCHEH » du fichier d’exemples.
Répétition de la manipulation pour les autres informations recherchées
Vous pourrez remarquer que dans cet exemple, seul le numéro de la colonne de recherche change dans les différentes formules.
• Cas 2 – Recherche par correspondance approximative
2 recherches par correspondance exacte
Nouvelles répétitions des manipulations vues dans le cas N°1 pour commencer par aller chercher les valeurs exactes de plafond moyen de dépenses ( “Avg_Credit_Limit” ) et de nombre de cartes de crédit des clients ( “Total_Credit_Cards” ).
Tableaux de conversion pour les paliers
Revoyons d’abord les paliers souhaités par notre responsable :
Voici comment traduire cela en tableaux de conversion en paliers :
Ce sont ces nouveaux tableaux de conversion qui vont être utilisés en tant que matrices de recherche (Table_Matrice) dans nos fonctions RECHERCHEV par valeur approximative.
Voyons cela dans la pratique :
1. Valeur_cherchée
Dans cette partie de notre 2ème étude de cas, notre clé de recherche est le plafond moyen de dépenses autorisées (Avg_Credit_Limit).
2. Table_matrice
Ici, notre matrice de recherche correspond à notre premier tableau de conversion.
Il contient en première colonne nos paliers de plafonds de dépenses, et en 2ème colonne les noms de ces paliers que nous souhaitons renvoyer avec notre fonction RECHERCHEV.
REFLEXE A PRENDRE : Vous vous en souvenez ? N’oubliez pas de presser la touche F4 du clavier après avoir sélectionné le tableau de conversion, afin de figer cette plage de recherche.
3. No_index_col
La dénomination du palier que nous souhaitons renvoyer avec la fonction RECHERCHEV est située dans la colonne numéro « 2 » de la plage précédemment sélectionnée.
4. [valeur_proche]
La matrice de recherche sélectionnée ne contient pas toutes les possibilités de valeurs de plafonds de dépenses autorisées pouvant être rencontrées dans notre base de données clients.
Elle ne contient en fait que les valeurs de passage aux différents paliers. Nous allons donc réaliser une recherche par correspondance approximative, en utilisant la valeur « VRAI ».
Résumé
Vous l’aurez donc compris, la fonction RECHERCHEV par correspondance approximative permet de renvoyer une information contenue dans une « table de conversion » à partir d’une clé de recherche qui est une valeur, en précisant dans quelle colonne est stockée l’information à renvoyer (cela sous-entend que les paliers de la table de conversion sont sous forme de lignes).
La clé de recherche est directement rattachée au palier correspondant, selon la règle suivante :
Pour le tableau de gauche :
– Quand 0 ≤ Valeur_cherchée < 15000 : Valeur renvoyée = “Très restreint”
– Quand 15000 ≤ Valeur_cherchée < 35000 : Valeur renvoyée = “Plafond modéré”
– Quand 35000 ≤ Valeur_cherchée < 60000 : Valeur renvoyée = “Plafond élevé”
– Quand 60000 ≤ Valeur_cherchée < 100000 : Valeur renvoyée = “Premium”
– Quand 100000 ≤ Valeur_cherchée : Valeur rénvoyée = “Premium +++”
Dans notre exemple, pour retrouver la bonne dénomination du palier de plafond de dépenses autorisées des clients suivis par notre responsable, nous avons recherché la valeur du plafond de dépenses autorisées de chaque client (Avg_Credit_Limit) dans une matrice de conversion en paliers. Dans notre fichier, dans la feuille « Cas2-Rech_approx » , cela s’est traduit par la formule suivante dans la cellule F16 :
=RECHERCHEV(D16;$K$3:$L$7;2;VRAI)
Pour information, la fonction RECHERCHEH par correspondance approximative a exactement le même fonctionnement mais de façon transposée : Elle permet de renvoyer une information contenue dans une « table de conversion » à partir d’une clé de recherche qui est une valeur, en précisant dans quelle ligne est stockée l’information à renvoyer (cela sous-entend que les paliers de la table de conversion sont sous forme de colonnes). Voir la feuille « Cas3-RECHERCHEH ».
Répétition de la manipulation pour les autres informations recherchées
Vous pourrez remarquer que dans cet exemple, seule la matrice de conversion change dans la formule pour rechercher le palier du nombre de cartes de crédit possédées par le client.
Voici le fichier du début de l’article, entièrement complété.
Les erreurs communes et comment les éviter
Pour commencer, il faut savoir que lorsque la fonction RECHERCHEV ne trouve pas de résultat, elle renvoie le code d’erreur « #N/A » (idem pour la fonction RECHERCHEH).
Voici les 5 erreurs les plus fréquemment rencontrées dans l’utilisation de ces fonctions.
1. L’espace fantôme
Un grand classique, surtout avec des données saisies à la main : Les données contiennent un ou des espaces superflus (en fin de chaine de texte, cela est invisible pour l’humain, et même pour les autres, il faut avoir un œil de lynx pour les détecter).
Cela génère une erreur car la Valeur_cherchée et la valeur présente dans Table_matrice n’ont plus une correspondance exacte.
Pour contourner ce problème, deux solutions sont envisageables :
• Utilisation de la fonction SUPPRESPACE : Attention, cette fonction a pour effet de supprimer les espaces en début et en fin de chaine de texte, et également les espaces en doublons entre les mots, mais elle laisse un espace entre chaque mot identifié, cela n’est parfois pas suffisant :
• Utilisation de la fonction SUBSTITUE : Cette fonction remplace une chaine de texte par une autre. Dans ce cas de figure, on précise que l’on souhaite remplacer les espaces par du vide pour supprimer tous les espaces (on peut aussi sélectionner les cellules, activer le raccourci CTRL + H pour ouvrir la fenêtre de remplacement de Excel, cela aura le même effet) :
2. Dernier argument de la fonction non précisé
Attention lorsque vous faites des recherches avec des valeurs numériques ! Si vous voulez faire une recherche par correspondance exacte et que vous omettez de préciser le dernier argument de la fonction RECHERCHEV (FAUX), cela aura pour effet de réaliser une recherche par valeur approximative, car le dernier argument de la fonction est paramétré par défaut sur la valeur VRAI lorsqu’il n’est pas précisé :
3. Table_matrice (plage de recherche) non figée
Vous vous souvenez du réflexe à prendre en rédigeant la fonction RECHERCHEV ? Presser la touche F4 du clavier juste après avoir sélectionné la Table_matrice : Cela a pour effet de figer cette plage (passage en référence absolue avec les dollars).
Si vous ne figez pas cette plage, lorsque vous étendez la formule vers le bas, la Table_matrice glisse également vers le bas, au risque de ne plus contenir certaines Valeurs_cherchées :
4. Mauvaise sélection de la Table_Matrice
N’oubliez jamais les deux conditions de sélection de la Table_matrice et notamment celle-ci :
« La première colonne de la matrice sélectionnée doit contenir la clé de recherche. »
Si la colonne qui contient votre Valeur_cherchée est incluse dans la Table_matrice, mais qu’elle n’est pas en 1ère position de cette Table_matrice, cela renverra une erreur #N/A:
5. Différence de format entre la Valeur_cherchée et la Table_Matrice
Ceci est un problème qui peut être rencontré lorsque vos Valeurs_cherchées sont des valeurs numériques, et d’autant plus lorsque vos données sont importées depuis une source externe.
Il est fréquent que lors d’un import de données, certaines valeurs numériques soient stockées sous forme de texte. Dès lors, si votre Valeur_cherchée est stockée sous forme de texte, et que dans votre Table_Matrice elle est stockée sous forme de nombre, cela renverra une erreur :
Mot de la fin
Vous connaissez dorénavant tout de la fonction RECHERCHEV (et de la fonction RECHERCHEH) !
Qu’en avez-vous pensé ? N’hésitez pas à vous exprimer en commentaires, cela m’aidera à améliorer le contenu du Blog, et donc à vous aider.
J’espère que cela vous aura été utile !
On se dit à très bientôt dans l’aventure !
Fabien @Exceldorado
Leave a Reply