Fonctions Excel à la loupe : N°1 - RECHERCHEV()

Pierre Fauconnier

RECHERCHEV() suscite régulièrement des questions sur les forums. Cette fonction montre vite ses limites lorsqu'elle est utilisée avec des paramètres statiques. Si l'on prend le temps de la découvrir, elle offre pourtant des possibilités insoupçonnées.

Il est temps pour vous d'enfin maîtriser cette fonction d'Excel.

Commentez

- Titre : Fonctions Excel à la loupe : N°1 - RECHERCHEV()

- Auteur : Pierre Fauconnier

- Parution : 7 juin 2010

- Mise à jour : 29 mars 2011

- Licence : Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright ®  Pierre Fauconnier. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à Developpez LLC.

Sommaire

- I. Introduction

- II. Définition, syntaxe

- II-A. Définition

- II-B. Syntaxe

- III. Utilisation de base

- III-A. Explications sur les paramètres utilisés

- III-A-1. Valeur_Cherchée

- III-A-2. Plage_Données

- III-A-3. Index_Colonne

- III-A-4. [Valeur_Proche]

- III-B. Problèmes liés à l'utilisation d'une valeur "en dur" pour le paramètre Index_Colonne

- III-B-1. Insertion (ou suppression) de colonnes

- III-B-2. Obligation de créer autant de formules qu'il y a de valeurs à renvoyer

- III-B-3. Inversion de colonnes

- IV. Utilisation avancée

- IV-A. Utilisation d'une référence à la colonne source

- IV-B. Récupération des données sur une ligne

- IV-C. Récupération des données sur une colonne

- IV-D. Récupération au sein d'un formulaire (syntaxe générique de la fonction)

- IV-D-1. Lorsque les intitulés du formulaire et ceux de la source de données correspondent

- IV-D-2. Lorsque les intitulés du formulaire ne correspondent pas aux intitulés de la source de données

- IV-E. Utilisation de plages nommées (toutes versions) ou de tableaux (Excel 2007 et 2010)

- IV-E-1. Plages nommées (toutes versions, mais surtout antérieures à 2007)

- IV-E-1-a. Plages statiques

- IV-E-1-b. Plages dynamiques

- IV-E-2. Tableau Excel 2007 / 2010

- V. Problèmes rencontrés dans l'utilisation de la fonction

- V-A. RECHERCHEV() renvoie #N/A

- V-B. Incohérence des valeurs retournées

- V-B-1. La plage est ordonnée sur sa première colonne.

- V-B-2. La plage n'est pas ordonnée sur sa première colonne.

- V-B-3. Problèmes avec des valeurs numériques sous forme de texte

- VI. RECHERCHEV() en VBA

- VI-A. EVALUATE

- VI-B. Recréer la formule pour l'attribuer à une cellule

- VI-C. Utilisation de Worsheetfunction

- VII. Conclusions

- VIII. Remerciements

I - Introduction

Certaines fonctions d'Excel "parlent" d'elles-mêmes et leur utilisation ne requiert pas d'explications particulières. D'autres, pour être utilisées de façon pertinente, demandent que l'on s'y attarde, soit parce que leur syntaxe ou leur usage ne coulent pas de source, soit parce qu'elles permettent un gain non négligeable en terme de temps de développement, d'utilisation ou de maintenance.

Ce sont ces fonctions que je vous propose de découvrir dans cette collection de tutoriels.

Chaque tutoriel de cette série sera organisé selon le canevas suivant :
- définition, syntaxe ;
- utilisation basique ;
- utilisation avancée ;
- problèmes éventuels ;
- utilisation en VBA.

II - Définition, syntaxe

II-A - Définition

RECHERCHEV() cherche une valeur dans la première colonne d'un tableau et renvoie la valeur trouvée sur la même ligne dans une colonne spécifiée du tableau.

Illustrons la théorie par un exemple en considérant la plage suivante, qui servira tout au long de ce tutoriel.

La plage qui servira durant tout le tutoriel.

Le schéma théorique du fonctionnement de RECHERHEV tel que nous allons l'expliquer ci-dessous.

Il existe également la fonction RECHERCHEH qui réalise une opération similaire, mais en cherchant une valeur dans la première ligne d'une plage et en renvoyant l'élément se trouvant à la nième ligne de la colonne dans laquelle la valeur cherchée a été trouvée.

Moyennant cette différence et le fait que Index_Colonne devient Index_Ligne, tout ce qui est dit dans le présent tutoriel sur RECHERCHEV() est applicable à RECHERCHEH.

II-B - Syntaxe

RECHERCHEV(Valeur_Cherchée ; Plage_Données ; Index_Colonne ; [Valeur_Proche])

Les paramètres utilisables sont les suivants :
- valeur_Cherchée : valeur qui sera cherchée dans la première colonne de la plage Plage_Données ;
- plage_Données : Plage source de la recherche ;
- index_Colonne : Index numérique de la colonne qui contient la valeur qui sera renvoyée ;
- [Valeur_Proche] : Précise si Excel peut utiliser la valeur inférieure la plus proche (paramètre facultatif).

III - Utilisation de base

Nous allons rechercher la facture 201019 pour pouvoir connaître le client auquel cette facture a été adressée. Pour cela, nous allons rechercher la valeur 201019 dans la première colonne de la plage des factures, et lorsque nous l'aurons trouvée, nous extrairons la valeur se trouvant dans la troisième colonne de ladite plage pour l'utiliser en J4.

La fonction RECHERCHEV() pourra donc être utilisée avec les paramètres suivants :
- valeur_Cherchée : 201019 (se trouve en J2, donc nous utiliserons $J$2);
- Plage_Données : $C$2:$G$27 ;
- Index_Colonne : 3 ;
- Valeur_Proche : FAUX.

La fonction renvoie le client de la facture 201019.

Il est bien entendu utile de bien maîtriser le système des références relatives et absolues (utilisation, ou pas, du signe $ pour verrouiller une référence). Les lecteurs qui auraient besoin d'une piqûre de rappel pourront lire mon tutoriel sur les références absolues et relatives.

III-A - Explications sur les paramètres utilisés

III-A-1 - Valeur_Cherchée

Toute valeur susceptible d'être trouvée dans la première colonne de Plage_Données. Cette valeur peut être spécifiée :
- in extenso (hard coding, donc déconseillé), par exemple 201019 ;
- par référence à une cellule ou une plage nommée, par exemple $J$2. C'est ce cas qui est illustré ici ;
- par l'utilisation du résultat d'une formule ou d'une fonction, par exemple MAX($C2:$C27).

Lorsque le paramètre [Valeur_Proche] est égal à FAUX ET que vous cherchez une valeur textuelle, vous pouvez utiliser les caractères génériques ? ou *, pour remplacer un ou plusieurs caractères.

Grâce au caractère générique *, le prermier code commençant par 946 est trouvé.

Grâce aux caractères génériques ? et *, le prermier code contenant 3 en troisième position est trouvé.

Si l'on souhaite effectuer une recherche avec Valeur_Cherchée qui contient un des caractères ? ou *, il faut les faire précéder du caractère ~, comme illustré ci-dessous.

~* indique à Excel de considérer * comme un caractère normal.

? indique à Excel de remplacer ? par n'importe quel caractère...

... alors que ~? indique à Excel de considérer ? comme un caractère.

III-A-2 - Plage_Données

Toute référence à une plage de données valide. Ce paramètre peut être défini par :
- la référence à une plage de données, par exemple $c$2:$G$27 ;
- la référence à une plage nommée ;
- l'utilisation du résultat de la fonction DECALER qui renvoie une plage de cellules, par exemple DECALER($c$1;1;0;NBVAL($C:$C)-1;5) ;
- l'utilisation du résultat de la fonction INDIRECT qui renvoie une plage dont la référence est le contenu d'une cellule passée en paramètres, par exemple INDIRECT($k$1) si K1 contient le nom d'une plage valide.

III-A-3 - Index_Colonne

Position de la colonne par rapport à la colonne de gauche de Plage_Données.

Dans l'exemple illustré ici, le nom se trouve dans la colonne E, qui est la troisième colonne de la plage $C$2:$G$27. Il faut noter que RECHERCHEV() ne permet de "déborder" ni à gauche ni à droite de Plage_Données.

Dans notre exemple, Plage_Données contient cinq colonnes, donc la valeur de Index_Colonne doit être comprise entre 1 et 5, bornes comprises. Ce paramètre peut être défini
- par une valeur numérique comprise dans les limites permises, comme dans notre exemple ;
- par référence à une cellule ou une plage nommée, par exemple $J$3. Dans ce cas, il faut que la valeur contenue dans la cellule soit une valeur numérique et qu'elle soit comprise dans les limites des valeurs acceptables, sous peine de renvoyer une erreur ;
- par l'utilisation du résultat d'une formule ou d'une fonction, par exemple mois($J$4) qui renverrait à une colonne mensuelle. Dans ce cas, la valeur renvoyée doit également être comprise dans la plage de valeurs acceptables par RECHERCHEV().

III-A-4 - [Valeur_Proche]

Valeur booléenne (logique) qui détermine si RECHERCHEV() peut utiliser la valeur inférieure la plus proche de la valeur cherchée ou s'il doit utiliser la valeur exacte. Ce paramètre est optionnel. S'il n'est pas précisé, Excel utilisera la valeur VRAI ou 1. VRAI demande à Excel de rechercher la valeur exacte et, s'il ne la trouve pas, d'utiliser la valeur inférieure la plus proche de celle de Valeur_Cherchée. Pour que les résultats soient cohérents, Plage_Données devra être ordonnée par ordre croissant sur la colonne de gauche. FAUX exige qu'Excel recherche la valeur exacte. Il n'est alors pas nécessaire que Plage_Données soit triée sur la première colonne. Avec Faux, RECHERCHEV() renverra la valeur d'erreur #N/A (not available) si la valeur n'est pas trouvée.

III-B - Problèmes liés à l'utilisation d'une valeur "en dur" pour le paramètre Index_Colonne

Comme nous allons le voir ci-dessous, il faut toujours essayer d'éviter de saisir directement des valeurs comme paramètres d'une fonction puisque ces paramètres sont figés dans la formule. On préférera toujours utiliser des paramètres calculés.

III-B-1 - Insertion (ou suppression) de colonnes

Lorsque Index_Colonne est renseigné via une valeur numérique, l'insertion ou la suppression de colonnes va affecter le résultat renvoyé par RECHERCHEV().

Ainsi, l'insertion d'une colonne en D décale les colonnes Date, Client,... vers la droite. La colonne Date devient la troisième colonne du tableau, et c'est donc la date qui sera renvoyée par la fonction en K4. A ce stade, la seule solution consiste à modifier la formule "à la main" pour la mettre en adéquation avec les valeurs qui doivent être retournées.

Suite à l'insertion de la colonne, la fonction renvoie la date.

On imagine aisément les problèmes de maintenance lorsque la table est constituée d'une cinquantaine de colonnes.

III-B-2 - Obligation de créer autant de formules qu'il y a de valeurs à renvoyer

Un autre problème se pose à nous. Index_Colonne étant une valeur figée, le copier-coller de la formule devra s'accompagner de la modification manuelle de Index_Colonne pour adapter le décalage vers la droite

La fonction a dû être adaptée pour renvoyer le montant...

...car INDEX_COLONNE est spécifié 'en dur'.

Dans ce cas également, on imagine mal devoir créer autant de formules qu'il y a de données à récupérer.

III-B-3 - Inversion de colonnes

L'inversion de colonnes posera le même genre de problèmes, puisque les valeurs absolues utilisées comme Index_Colonne ne "suivent" pas les modifications structurelles de la feuille.

IV - Utilisation avancée

IV-A - Utilisation d'une référence à la colonne source

Pour supprimer le problème lié à l'insertion ou la suppression d'une colonne au sein de Plage_Données, on pourrait calculer Index_Colonne sur base du numéro absolu de la colonne (position de la colonne par rapport à la feuille) et ajuster la valeur par une simple opération arithmétique pour la faire cöincider avec Index_Colonne.

Pour réaliser cela, on va utiliser la fonction qui retourne le numéro de la colonne d'une référence, à savoir COLONNE([Référence]).

Cette fonction renvoie le numéro de la colonne de la cellule du coin supérieur gauche de la plage de données passée en Référence. Lorsque le paramètre est omis, COLONNE() renvoie le numéro de la colonne de la cellule dans laquelle COLONNE() est utilisée.

Les copies d'écran ci-dessous illustrent les possibilités d'utilisation de COLONNE([Référence]). La plage nommée Plage fait référence à la plage $D$5:$G$8 avant insertion (plage qui commence en colonne 4) et à la plage $E$8:$H$8 après insertion.

Les fonctions utilisées avec différents paramètres...

... et les valeurs renvoyées par Excel

L'insertion d'une colonne avant C...

... amène Excel à adapter les références...

... pour renvoyer des index de colonnes incrémentés.

Grâce à ces exemples, nous voyons donc que nous pouvons remplacer le numéro d'une colonne par la valeur renvoyée par la fonction COLONNE(). Bien entendu, si Plage_Données ne commence pas en colonne A, il faudra adapter cette valeur.

Dans notre exemple, nous pouvons adapter notre formule comme illustré ci-dessous.

L'insertion d'une colonne...

... amène Excel à adapter les références. RECHERCHEV() renvoie le bon résultat.

Il faut noter que COLONNE(E1) renvoie la position absolue de la colonne E, soit la cinquième colonne de la feuille. Or, RECHERCHEV() doit utiliser 3 comme valeur de Index_Colonne, et nous devons donc retrancher 2 du résultat de COLONNE(E1).

IV-B - Récupération des données sur une ligne

Lorsque les données récupérées sont présentées en ligne, et pour autant que l'ordre des "champs" soit respecté, on peut utiliser un stratagème identique à celui utilisé pour l'insertion de colonnes.

Ainsi, il sera possible de créer une seule formule et de la tirer pour récupérer les données liées à un "enregistrement" de la source. Il ne faudra pas oublier de verrouiller ce qui doit l'être, bien entendu.

En utilisant la fonction COLONNE() comme paramètre Index_Colonne ...

... il est possible de ne créer qu'une formule que l'on tire vers la droite.

IV-C - Récupération des données sur une colonne

Si les cellules de récupération sont placées verticalement, et pour autant, toujours, qu'elles récupèrent les valeurs dans l'ordre des "champs", on peut utiliser la fonction LIGNE() pour ne créer qu'une formule qui sera tirée vers le bas.

Dans notre exemple, la formule est créée en troisième ligne alors que la valeur attendue se trouve dans la deuxième colonne de la plage. Il faudra donc retrancher 1 de la valeur retournée par la fonction LIGNE()

Pour rappel, les fonctions LIGNE() et COLONNE() utilisées sans paramètres retournent la ligne ou la colonne de la cellule dans laquelle elles sont utilisées.

En utilisant comme paramètre Index_Colonne la fonction LIGNE()...

... il est possible de ne créer qu'une formule que l'on tire vers le bas.

Cette technique montre que l'on peut recopier une formule vers le bas tout en déplaçant la source vers la droite. On peut aussi, en utilisant le même principe, recopier une formule vers la droite tout en déplaçant la source vers le bas.

IV-D - Récupération au sein d'un formulaire (syntaxe générique de la fonction)

La disposition des cellules de récupération n'est pas toujours aussi simple. Souvent, les données devront être reprises dans un formulaire mis en forme, par exemple pour l'impression de fiches signalétiques devant répondre à un canevas précis.

Dans ce cas, l'utilisation des formules vues précédemment n'est pas pertinente, et il est alors utile d'utiliser une autre fonction pour repérer la colonne contenant la donnée à afficher.

IV-D-1 - Lorsque les intitulés du formulaire et ceux de la source de données correspondent

L'illustration suivante met en évidence une disposition des cellules de récupération qui ne permet pas l'utilisation des techniques vues précédemment. Pourtant, la création d'une seule formule est envisageable, et même fortement souhaitée, si l'on souhaite garantir une maintenance aisée de notre classeur.

La fiche d'une facture présente les cellules de récupération non alignées.

Pour réaliser cela, allons, dans un premier temps, considérer que les intitulés des cellules (cellules en bleu sur l'illustration) correspondent aux intitulés des champs de la source de données.

Dès lors, la technique va être de repérer la position d'un intitulé dans la suite des champs de la source, en commençant par la gauche.

Correspondance entre champs de la source et cellules du formulaire

Pour trouver la position (le rang) d'un intitulé dans la suite des intitulés de la source, nous allons utiliser la fonction EQUIV(), dont la syntaxe est :

EQUIV(Valeur_Cherchée, Plage_Recherche, [Type_Recherche])

La fonction EQUIV() pour récupérer la position de "Montant HTVA" dans la liste des intitulés sera donc rédigée de la manière suivante :

=EQUIV("Montant HTVA";C1:G1;0)

ce qui renvoie bien 4.

La fonction EQUIV retrouve la position de 'Montant HTVA'.

Le troisième paramètre permet de retrouver la valeur exacte.

On pourra dès lors remplacer la valeur cherchée "en dur" par la référence à une cellule.

Spécifier l'intitulé dans une cellule permet de rendre la formule générique.

Il suffit d'insérer EQUIV() dans la fonction RECHERCHEV() dans le formulaire pour pouvoir rédiger une seule formule, applicable à toutes les cellules du formulaire.

L'utilisation 'générique' de RECHERCHEV()...

... permet la création d'une seule formule, recopiable partout dans le formulaire.

IV-D-2 - Lorsque les intitulés du formulaire ne correspondent pas aux intitulés de la source de données

Parfois (souvent ?), les intitulés de la source de données sont 'informatiques', et donc peu lisibles par les humains. Ils ne sont donc pas exploitables en tant que tels dans le formulaire. Il faut alors imbriquer une fonction RECHERCHEV() dans la fonction EQUIV.

Il n'est pas possible d'assurer la correspondance des intitulés.

Dans ce cas, nous allons devoir passer par une table secondaire qui servira de passerelle entre les intitulés du formulaire et ceux de la source de données.

La plage de correspondance entre les valeurs...

... permet l'imbrication d'un deuxième RECHERCHEV() dans EQUIV().

IV-E - Utilisation de plages nommées (toutes versions) ou de tableaux (Excel 2007 et 2010)

IV-E-1 - Plages nommées (toutes versions, mais surtout antérieures à 2007)

A partir de la version 2007, Excel nous offre la possibilité de gérer des tableaux qui ne nécessitent plus la création de plages nommées pour l'usage que nous en faisons ici.

L'utilisation de plages nommées facilite grandement la rédaction et la maintenance des formules.

IV-E-1-a - Plages statiques

Nous avons besoin de deux plages nommées, l'une pour les données, l'autre pour la zone des intitulés.

La création d'une plage nommée "statique" est réalisée très simplement. Il suffit de sélectionner la plage à laquelle attribuer un nom, puis, dans la zone de noms, de saisir le nom que l'on veut attribuer.

Les noms des plages doivent commencer par une lettre. De plus, ils ne peuvent contenir que des lettres ou des chiffres. Pour des raisons de portabilité, il est préférable de n'utiliser que des caractères internationaux. L'espace étant un caractère interdit, on pourra le remplacer par le caractère de soulignement (underscore) _.

Il faut nommer la plage des factures...

... et celle des intitulés...

... pour obtenir une formule plus lisible et plus compréhensible.

L'utilité des plages nommées est encore mieux mise en évidence dans le cas pour lequel les intitulés du formulaire ne correspondent pas à ceux de la source de données.

La plage des correspondances est nommée...

... pour être utilisée au sein de la formule.

IV-E-1-b - Plages dynamiques

L'utilisation de plages dynamiques créées avec la fonction DECALER() permet de faciliter la maintenance de façon extraordinaire, puisque les dimensions du tableau s'adapteront automatiquement à l'ajout et le retrait de données, ainsi qu'à l'ajout ou au retrait de champs.

La plage nommée 'Factures' est rendue dynamique grâce à DECALER.

On peut dès lors, après avoir ajouté la colonne dans la plage de données, ajouter l'intitulé dans le formulaire et recopier la formule 'générique' pour obtenir la nouvelle information au sein du formulaire.

L'ajout du champ 'Echéance' dans la plage de données...

... permet l'ajout aisé de cette échéance dans le formulaire en insérant l'intitulé puis en recopiant une des formules du formulaire.

IV-E-2 - Tableau Excel 2007 / 2010

L'utilisation des tableaux 2007 / 2010 facilite grandement la maintenance du classeur puisque les tableaux créés sont dynamiques. De plus, Excel découpe le tableau en différentes zones toujours nommées de la même façon, ce qui automatise encore plus leur utilisation au sein des formules.

Pour créer un tableau 2007 / 2010, il suffit de se placer dans la zone de données qui servira de tableau, puis, via l'onglet 'Insertion' du ruban, on crée le tableau d'un simple clic

On crée le tableau via l'onglet 'INSERTION'...

...et on le renomme via le ruban spécifique 'OUTILS DE TABLEAU'.

Pour que le ruban spécifique des tableaux 2007 / 2010 soit accessible, il faut qu'une cellule du tableau soit active

L'aide à la saisie de la version 2007 / 2010 permet d'utiliser très aisément les zones du tableau ainsi créé.

L'aide à la saisie nous propose le nom du tableau...

...ainsi que les zones de celui-ci que nous pouvons utiliser...

...pour obtenir la formule 'générique' de RECHERCHEV().

Bien entendu, les tableaux 2007 étant dynamiques par nature, l'ajout d'un champ dans la table permettra l'ajout de la donnée dans le formulaire, comme pour une plage nommée dans les versions antérieures.

L'utilisateur souhaitant en apprendre davantage sur les tableaux 2007 lira avec intérêt le tutoriel de Silkyroad sur le sujet.

V - Problèmes rencontrés dans l'utilisation de la fonction

V-A - RECHERCHEV() renvoie #N/A

RECHERCHEV() renverra #N/A lorsque
- Le paramètre Valeur_Proche est égal à FAUX et que la valeur recherchée n'est pas présente dans la liste ;
- Le paramètre Valeur_Proche est égal à VRAI ou est omis, et que la valeur cherchée est inférieure à la plus petite valeur présente dans la liste triée par ordre croissant.

La fonction renvoie #N/A, ce qui n'est pas du plus bel effet.

Avec les versions antérieures à 2007, il faudra utiliser une fonction de test d'erreur, ESTNA() par exemple, pour contourner le problème au sein d'un SI().

Cette façon de faire impose de doubler l'utilisation de la fonction, puisqu'elle sera utilisée dans le test ET dans Valeur_Si_Vrai ou Valeur_Si_Faux.

L'utilisation d'une condition sur erreur (Versions antérieures à 2007) impose de doubler la fonction.

A partir de 2007, Excel propose la fonction SIERREUR() qui permet d'éviter de devoir énoncer deux fois la fonction RECHERCHEV() dans SI(ESTNA(...);...;...). Il faut noter que SIERREUR() ne permet pas de distinguer l'erreur renvoyée par la fonction.

La fonction SIERREUR(), utilisable à partir de la version 2007, n'utilise qu'une fois RECHERCHEV()

V-B - Incohérence des valeurs retournées

L'utilisation du paramètre [Valeur_Proche] à FAUX (ou 0) ne pose en général pas de problèmes. Soit la valeur cherchée est trouvée dans la première colonne, et RECHERCHEV() renvoie la valeur trouvée dans la colonne Index_Colonne, soit la valeur est inexistante et RECHERCHEV() renvoie #N/A.

Avec [Valeur_Proche] = FAUX, RECHERCHEV() renvoie #N/A si Valeur_Cherchée n'est pas présente en colonne 1.

Il est parfois plus délicat de comprendre la valeur renvoyée par la fonction lorsque [Valeur_Proche] est VRAI ou est omis. Deux cas peuvent se présenter :
- la plage est ordonnée par ordre croissant sur sa première colonne ;
- la plage n'est pas ordonnée sur sa première colonne.

V-B-1 - La plage est ordonnée sur sa première colonne.

Si la plage est ordonnée par ordre croissant, RECHERCHEV() utilisera la valeur inférieure la plus proche de la valeur cherchée. Illustrons cela par un RECHERCHEV() renvoyant la valeur de la première colonne.

201005 n'existe pas, RECHERCHEV() utilise la valeur inférieure la plus proche.

Si Valeur_Cherchée est inférieure à la première valeur de la liste, RECHERCHEV() renverra #N/A.

200934 est inférieure à 201001, donc RECHERCHEV() renvoie #N/A.

V-B-2 - La plage n'est pas ordonnée sur sa première colonne.

Lorsque la plage n'est pas ordonnée sur sa première colonne, la valeur renvoyée par RECHERCHEV() sera pour le moins déconcertante, comme nous allons le voir ci-dessous.

La plage est triée sur les noms, la recherche de 201021 renvoie la dernière valeur, soit 201015.

La plage est triée sur les montants HTVA, la recherche de 201015 renvoie 201015, soit la bonne valeur.

La plage est triée sur les montants HTVA, la recherche de 201021 renvoie 201014, apparemment de façon aléatoire.

La plage est triée sur les montants de TVA, la recherche de 201021 renvoie 201016, apparemment de façon aléatoire.

Ces "incohérences" résultent de l'algorithme utilisé par RECHERCHEV() avec [Valeur_Proche] = VRAI qui optimise les temps de recherche. Dans les faits, Excel recherche la valeur se trouvant à mi-chemin entre la première et la dernière valeur, évalue cette valeur et utilise la première ou la seconde moitié de la plage (selon la valeur trouvée à mi-chemin) comme nouvelle plage de recherche, et procède ainsi par découpages successifs jusqu'à épuiser toutes les possibilités.

C'est pourquoi les valeurs renvoyées sont différentes selon les ordres de tri, puisque les valeurs délimitant les zones de recherche en colonne 1 sont différentes à chaque fois.

Il faut retenir de ce qui précède que la fiabilité de la valeur renvoyée par RECHERCHEV() est NULLE lors de l'utilisation de [Valeur_Proche] à VRAI sur une plage non triée sur la première colonne.

V-B-3 - Problèmes avec des valeurs numériques sous forme de texte

Il faut être attentif lorsque Valeur_Cherchée est numérique et que les formats de Valeur_Cherchée et de la colonne A de Table_Données ne sont pas identiques, car RECHERCHEV() se comporte alors de façon bizarre.

Dans le tableau utilisé pour ce tutoriel, Valeur_Cherchée et la colonne 1 de Table_Données sont au format standard, et les valeurs sont numériques. RECHERCHEV() fonctionne correctement et renvoie la valeur souhaitée.

La concordance de formats assure le bon fonctionnement de RECHERCHEV().

Si l'on modifie le format de la cellule contenant Valeur_Cherchée et qu'on le bascule en TEXTE, la fonction renvoie toujours la bonne valeur

Le basculement au format TEXTE n'altère pas le fonctionnement de RECHERCHEV()

Par contre, si l'on édite la cellule Valeur_Cherchée, on obtient la balise active d'erreur "Nombre stocké au format TEXTE", et RECHERCHEV() renvoie alors #N/A!!

SAISIR une valeur au format TEXTE et rechercher une valeur numérique altèrent le fonctionnement de RECHERCHEV()

On devra donc porter une attention particulière aux formats des cellules lorsque l'on utilise des valeurs numériques comme Valeur_Cherchée.

Il faut noter que le comportement sera également altéré si la Valeur_Cherchée est numérique alors que la colonne 1 de Plage_Données est au format TEXTE ET que des données numériques y ont été saisies.

VI - RECHERCHEV() en VBA

La manipulation de RECHERCHEV() en VBA n'est pas compliquée. Comme pour toutes les fonctions que nous utilisons en VBA, nous privilégierons l'emploi des noms 'internationaux' pour des raisons de compatibilité.

On peut employer RECHERCHEV() via Evaluate(), WorsheetFunction ou en créant une formule qui sera affectée à une cellule.

VI-A - EVALUATE

EVALUATE() permet de récupérer la valeur d'une chaîne de caractères passée en paramètre de la fonction. Il faut garder à l'esprit que la syntaxe est internationale et il faut donc utiliser le nom et la syntaxe anglais. Il faudra donc connaître le nom anglais des fonctions utilisées par EVALUATE().

Pour connaître le nom international ainsi que la structure utilisée, il suffit de rédiger la formule dans une cellule d'une feuille, puis, en VBA, d'afficher la propriété Formula de cette cellule, par exemple dans la fenêtre d'exécution du code.

La formule utilisant RECHERCHEV() et EQUIV()...

...et sa transposition en VBA

L'évaluation de la formule avec EVALUATE() permet de récupérer au sein du code la valeur d'un champ pour un enregistrement précis.

EVALUATE() récupère le nom du client.

Voici une petite procédure qui récupère le nom du client pour une facture dont le numéro est précisé dans une variable.

Le code de la procédure...

... et le message renvoyé à l'utilisateur.

On remarque ici que l'utilisation de plages nommées simplifie grandement le code VBA qui sera utilisé. Il est donc primordial de penser à la conception du classeur avant, sous peine d'écrire du code dont la maintenance ne sera pas aisée.

On remarque également que la chaîne passée à EVALUATE ne commence pas par le signe =.

VI-B - Recréer la formule pour l'attribuer à une cellule

Lorsque l'on veut attribuer une formule à une cellule par VBA, il suffit de reconstituer la formule et de la passer à la propriété.

Le code qui crée les formules de récupération des données

Dans certains cas, il sera plus aisé d'utiliser une formule avec les références R1C1, notamment lors de la création de formules utilisant des références relatives. Il faut noter que, dans ce cas, toutes les références doivent être de type R1C1. Il n'est donc pas possible de panacher les types de références au sein d'une même formule.

La même formule avec des références de type R1C1

Il faut noter que vous pouvez utiliser Formula ou FormulaR1C1 indépendamment du système de références utilisé dans Excel.

Lors de la création en VBA de formules qui sont affectées à des cellules, il est préférable d'utiliser la formulation internationale (Formula ou FormulaR1C1) plutôt que la formulation régionale (FormulaLocal ou FormulaR1C1Local) pour une compatibilité du code quel que soit la langue de la version utilisée.

VI-C - Utilisation de Worsheetfunction

L'utilisation de RECHERCHEV() via l'objet worksheetFunction est quelque peu différente. Tant pour EVALUATE que pour la propriété FORMULA et ses dérivées, on devait reconstituer une chaîne de caractères pour "imiter" la fonction Excel.

Avec l'objet Worksheetfunction, on utilise les paramètres demandés par la fonction, comme pour une fonction intrinsèque de VBA.

Worksheetfunction.Vlookup demande les mêmes arguments qu'Excel...

... pour renvoyer la valeur trouvée.

VII - Conclusions

Comme vous le voyez, l'utilisation de RECHERCHEV(), surtout lorsqu'elle est associée à des fonctions telles que DECALER() (plages dynamiques), EQUIV() (recherche de la position d'une valeur dans une liste) et/ou des tableaux 2007 (dynamiques par nature et proposant des zones prédéfinies), permet de créer des formulaires de récupération de données dont la maintenance est facilitée et la pérennité assurée.

Bien entendu, comme pour beaucoup de situations en Excel, c'est l'utilisation conjointe de plusieurs fonctions qui permet de limiter et de sécuriser la maintenance des tableaux et des classeurs.

VIII - Remerciements

Merci à :

- Didier Gonard (ormonth) pour ses conseils techniques et didactiques ;
- Hédhili Jaïdane pour sa relecture orthographique et ses conseils bienveillants ;
- Chtulus et Jeannot45 pour leurs appréciations.