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

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.

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.

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.

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().

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.

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.

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

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

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

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.

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.

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.

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.

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

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.

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.

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.

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▲

IV-E-1-b. Plages dynamiques▲

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

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éé.

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.

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.

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

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.

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.

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.

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

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!!

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.