La fonction RECHERCHEV d’Excel fait partie des grands classiques incontournables de ce logiciel.
Dans cet article vous allez découvrir un exemple typique d’utilisation de cette fonction :
créer facilement une saisie semi-automatique.
Vous découvrirez aussi les différentes possibilités de la fonction RECHERCHEV d’Excel, et comment bien gérer les erreurs pour un affichage propre.
Au-delà de ça, à travers cet exemple vous comprendrez pourquoi cette fonction est essentielle pour exploiter conjointement plusieurs tableaux de données, et ainsi créer du lien entre vos données.
Un exemple concret
Voici le scénario concret qui nous servira de support :
Un artisan couvreur veut pouvoir créer rapidement des devis pour ses clients.
Pour se faciliter la tâche, il a créé dans Excel un catalogue détaillant toutes les fournitures et opérations qu’il fournit régulièrement.
Notre artisan souhaite éditer ses devis en récupérant les infos dans le catalogue pour ne pas avoir à les ressaisir à chaque nouveau devis.
Il souhaite aussi pouvoir ajouter des lignes dans le devis, qui ne font pas référence au catalogue.
Une fois son devis terminé, il l’enregistre en PDF pour l’envoyer au client et en conserver une copie non modifiable.
Description du classeur Excel
Dans tout ce qui suit, les tableaux utilisés sont des tableaux créés via Insertion \ Tableau. Si vous ne connaissez pas cet objet génial et incontournable, je vous invite instamment à lire mon livret gratuit qui vous en présentera les principaux avantages (cf. lien dans la barre supérieure ou en pied de page).
Pour faire des devis, nous avons besoin de 2 feuilles dans le classeur Excel :
La feuille Catalogue centralise dans un tableau nommé tabCatalogue, l’ensemble des fournitures utilisées couramment par notre artisan :
La feuille Devis permet d’éditer un devis :
Dans cette feuille, notre artisan saisit les coordonnées de son client et le détail des fournitures. Pour chaque ligne de fourniture, il doit :
- Reprendre les caractéristiques de la fourniture depuis
le catalogue
- La référence
- La désignation
- L’unité
- Le prix unitaire
- Le montant hors taxe
- Le taux de TVA applicable (codifié en : 1, 2… pour des taux de 5.5%, 20%…)
- Indiquer la quantité
Le montant hors taxe de la ligne est calculé automatiquement par une formule.
Pour chaque ligne, notre artisan pourrait faire un copier / coller manuel de toutes les caractéristiques de la fourniture depuis le catalogue vers le devis. Mais ce serait à la fois fastidieux et source d’erreur.
Il serait bien plus rapide et sécurisé de saisir uniquement la référence, et de récupérer automatiquement les autres informations. C’est là qu’intervient la fonction RECHERCHEV d’Excel
Mise en œuvre de la fonction RECHERCHEV d’Excel
L’artisan saisit la référence de la fourniture dans la colonne Réf. (éventuellement au moyen d’une liste déroulante).
Excel permet de récupérer automatiquement la désignation de la fourniture correspondant à la référence saisie, grâce à la formule suivante :
=RECHERCHEV([Réf];tabCatalogue;3;FAUX)
Les 4 paramètres à spécifier sont, dans l’ordre :
- La valeur à rechercher (on dit aussi valeur de recherche) dans la première colonne de la plage spécifiée en second paramètre
- La plage dans laquelle se trouve la valeur recherchée
- Le numéro de la colonne dans cette plage, qui contient l’information à récupérer
- La valeur VRAI ou FAUX. FAUX pour renvoyer une erreur si la valeur recherchée n’est pas trouvée. VRAI pour faire une recherche approximative et renvoyer le résultat le plus proche.
Nous avons ici spécifié les valeurs suivantes pour ces paramètres :
- [réf] désigne la valeur courante de la colonne Réf dans notre tableau
- tabCatalogue est le nom du tableau catalogue
- 3 est le numéro de la colonne Désignation dans ce tableau
- FAUX pour renvoyer une erreur si la référence exacte n’est pas trouvée
On utilise des formules semblables pour récupérer les autres informations. Par exemple pour la colonne Unité, la formule est :
=RECHERCHEV([Réf];tabCatalogue;4;FAUX)
…, car l’unité est dans la 4ème colonne du tableau de catalogue.
La saisie du détail du devis est donc très rapide, car notre artisan n’a besoin de saisir que la référence et le nombre d’unités de chaque fourniture. Tout le reste est ramené automatiquement par des formules. Bien entendu, il doit compléter son catalogue lorsqu’il utilise de nouveaux types de fournitures…
Cas de la recherche approximative
Si nous avions spécifié VRAI en dernier paramètre de RECHERCHEV dans les formules précédentes, Excel aurait recherché la référence la plus proche de la valeur spécifiée. Il se base pour cela sur l’ordre alphabétique ou numérique, sans dépasser la valeur spécifiée.
Par exemple, en tapant G dans la colonne référence, Excel aurait récupéré la ligne « Volige sapin… ». Car cette ligne a la référence « BVOL14100 », qui est la plus proche de G dans l’ordre alphabétique, sans dépasser le G.
Cas d’une recherche exacte sans résultat
Si le dernier paramètre passé à la fonction RECHERCHEV est FAUX, Excel ne fait pas de recherche approximative. Dans ce cas, s’il ne trouve pas dans la première colonne la valeur demandée, il affiche « #N/A », ainsi qu’un petit rectangle vert dans le coin de la cellule pour signaler l’erreur.
C’est aussi ce qui s’affiche tant qu’aucune référence n’est saisie, comme le montre la capture ci-dessous :
Visuellement, ce n’est pas terrible, n’est-ce pas ?
Pour éviter cela, on peut utiliser la fonction SIERREUR dans les formules ci-dessus. Par exemple pour la formule qui récupère le libellé :
=SIERREUR(RECHERCHEV([Réf];tabCatalogue;3;FAUX);"")
Le second paramètre de SIERREUR spécifie la valeur à afficher si l’expression spécifiée en premier paramètre renvoie une erreur (notamment #N/A). Ici, nous avons spécifié la valeur chaîne vide «», qui permet de ne rien afficher. On obtient ainsi un résultat bien plus propre, comme celui présenté en début d’article.
NB/ Une autre solution moins pratique consiste à utiliser les fonctions SI et ESTNA à la place de SIERREUR.
Cas d’une recherche ayant plusieurs résultats
Pour utiliser la fonction RECHERCHEV de façon pertinente, la première colonne de la plage de recherche ne devrait contenir que des valeurs uniques, identifiant chacune une seule ligne. Dans notre exemple, les références des fournitures sont bien des codes uniques.
Si ce n’est pas le cas, la fonction RECHERCHEV renvoie le résultat de la première ligne correspondant à la valeur recherchée.
Contraintes
Il faut garder à l’esprit les contraintes suivantes quand on utilise la fonction RECHERCHEV d’Excel (sous peine de ne pas récupérer le bon résultat) :
- RECHERCHEV ne recherche la valeur souhaitée (ici la valeur de la référence) que dans la première colonne de la plage de recherche (ici notre tableau catalogue)
- Si on met la valeur VRAIE en dernier paramètre, la plage de recherche doit être triée par ordre croissant de la première colonne (sinon le résultat est faux)
Ainsi, si dans le tableau tabCatalogue, vous mettez la référence en seconde colonne, ça ne fonctionne plus.
Solutions alternatives
Si les restrictions précédentes vous embêtent (en particulier la première), vous pouvez utiliser une solution alternative à RECHERCHEV, qui consiste à combiner les fonctions INDEX et EQUIV.
Je détaillerai cette solution dans un prochain article.
RECHERCHEV a également une sœur jumelle : RECHERCHEH
V signifie en fait « Vertical », car on spécifie la colonne dans laquelle récupérer la valeur.
RECHERCHEH fait quant à elle une recherche horizontale, c’est-à-dire qu’elle permet de spécifier la ligne dans laquelle récupérer la valeur (la première ligne contenant les identifiants). Elle est beaucoup moins utilisée, car dans un tableau Excel on met généralement les différentes informations en colonnes et non en ligne.
En synthèse
La fonction RECHERCHEV d’Excel permet de récupérer une information précise d’une ligne de tableau. Cette ligne est identifiée par la valeur de la première colonne, tandis que la nature de l’information à récupérer est précisée par le N° de colonne.
RECHERCHEV est donc d’une fonction essentielle pour exploiter conjointement plusieurs tableaux de données, et créer ainsi du lien entre vos données.
Les valeurs récupérées servent généralement à :
- créer une saisie semi-automatique, comme dans l’exemple que nous venons de voir
- faire des calculs, comme illustré dans cet article où l’on calcule le coût de la consommation électrique sur une période en utilisant le prix du kWh récupéré dans un autre tableau.
Le type de recherche (exacte ou approximative) de la valeur identifiante est spécifié en dernier paramètre. Dans le cas d’une recherche approximative, faites très attention à bien classer la plage de recherche par ordre ascendant selon la première colonne.
Vous trouverez d’autres exemples de mise en œuvre de RECHERCHEV sans objet tableau sur cette page d’aide de Microsoft.
Partager la publication "Saisie semi-automatique avec la fonction RECHERCHEV d’Excel"
