Sommaire
- 1 La fonction RECHERCHEV
- 2 Un exemple de RechercheV avec les #N/A masqués.
- 3 La formule en détail pour notre classeur
- 4 Pour éviter le #N/A
La fonction RECHERCHEV
La fonction RECHERCHEV (Recherche Verticale) va nous permettre de faire apparaître des données provenant d’une autre feuille ou d’un autre fichier d’Excel.
Nous pourrons ainsi sur une autre feuille, ‘afficher par exemple les coordonnées de nos fournisseurs, clients, employés, ou comme dans l’exemple ci-dessous d’établir rapidement le tableau des ventes du mois en récupérant les catégories de produits, leur désignation, leur prix, ainsi qu’une Fiche récapitulative produits. Vous trouverez maintes applications car c’est une fonction très utilisée et très intéressante.
Il nous faudra pour cela avoir une colonne commune comme ici la “Référence” du produit, celle-ci devant se trouver en première colonne dans nôtre tableau des ventes.
Après la recopie des formules, les références non encore saisies ou inexistantes provoqueront un affichage dans la cellule d’un message #N/A (Not Available). Nous donnons donc ici une solution pour masquer les #N/A dans une fonction RECHERCHEV.
Un exemple de RechercheV avec les #N/A masqués.
Sur une feuille que j’ai nommée “Table”, j’ai une base de données contenant la liste des produits informatique que je vends.
Chaque enregistrement comprend : (en simplifié pour le besoin du tutoriel !)
– La référence produit (en colonne A)
– La catégorie (en colonne B)
– La désignation (en colonne C)
– Le fournisseur (en colonne D)
– Le montant HT (en colonne E)
Je veux créer un tableau sur une seconde feuille nommée “Ventes Nov” récapitulant les ventes du mois.
Il contient en première colonne la Date, en seconde la Référence du produit, en troisième la Catégorie, en quatrième la Désignation et en dernière colonne le montant HT.
Ici je vais utiliser la fonction RECHERCHEV (VLOOKUP en anglais) qui permet au tableur de chercher une valeur dans la première colonne d’un tableau donc ici “Référence” de ma feuille “Table” et de renvoyer le contenu d’une cellule de cette même ligne en lui indiquant son numéro de colonne. (Exemple ici “Catégorie” colonne n°2 de la feuille “Table) afin que mon tableau se remplisse automatiquement avec tout ce qui existe déjà dans ma base de données “Table”.
La syntaxe de cette fonction RechercheV est la suivante :
=RECHERCHEV(valeurcherchée;table matrice;numéro index colonne, valeur proche)
La formule en détail pour notre classeur
Plaçons nous sur la feuille des « Ventes Nov » en cellule C5 et cliquons sur ” fx” en choisissant RechercheV pour créer notre formule ou saisissons là directement:
=RECHERCHEV($B5;Table;2;FAUX)
$B5 : c’est l’endroit où vous allez saisir la référence de l’article vendu (celles qui sont répertoriées en première colonne sur votre Table des produits. (avec un $ devant de manière à figer la colonne mais pas la ligne puisque l’on va la recopier vers le bas ; utilisez donc la touche de fonction F4 pour modifier B5 en $B5 par des appuis consécutifs).
Table : c’est toute la table des produits (table matrice) qui a été nommée en allant sur l’Onglet Formule, Groupe Noms Définis, Définir un nom. (Prévoyez plus de lignes si vous compter augmenter la liste des produits par la suite). Vous pouvez aussi aller la chercher en effectuant une surbrillance de votre table, si vous faite cela vous verrez le nom de l’onglet feuille suivi d’un point d’exclamation car il s’agit d’une autre feuille que celle où vous saisissez votre formule.
2 : C’est le numéro de colonne qui contient les catégories puisque nous voulons faire apparaitre la catégorie dans cette colonne.
Faux : Si l’argument “valeur proche” est FAUX, RECHERCHEV recherche exclusivement une correspondance exacte. Si plusieurs valeurs de la première colonne de table matrice correspondent à valeur cherchée, c’est la première valeur trouvée qui est utilisée. Si aucune valeur ne correspond, la valeur d’erreur #N/A est renvoyée.
Si l’argument valeur proche est FAUX, il n’est pas indispensable que les valeurs de la première colonne de table matrice soient triées.
Si l’argument valeur proche est VRAI ou omis, une donnée exacte ou proche est renvoyée. Si aucune valeur exacte n’est trouvée, la valeur immédiatement inférieure à valeur_cherchée est renvoyée.
Dans ce cas il faut mettre Faux ! Validez votre formule vous obtenez ceci :
Et, comme vous pouvez le voir ici, si vous n’avez pas encore saisi un chiffre dans la colonne référence, vous voyez un #N/A, de même lorsque vous recopiez votre formule sur plusieurs lignes non encore renseignées en colonne référence ou que votre produit n’existe pas.
Pas très joli de voir dans une cellule une valeur #N/A surtout si l’on a besoin d’imprimer notre tableau des Ventes.
Pour éviter le #N/A
Pour éviter le #N/A il suffit de modifier votre formule en utilisant :
=Si(ESTERREUR(La Formule);””;La Formule)
Ce qui donne pour notre formule :
=SI(ESTERREUR(RECHERCHEV($B5;Table;2;FAUX));”-“;(RECHERCHEV($B5;Table;2;FAUX)))
Cette fonction vérifie s’il existe une condition d’erreur dans B5. Si tel est le cas, la fonction SI renvoie le message “-“, et l’absence d’erreur, la fonction SI effectue la RECHERCHEV.
Vous pouvez donc soit mettre “” ce qui laissera une cellule vide ou “-” vous aurez un tiret dans la cellule. Sur l’image ci-dessous en C6 j’ai mis ceci dans la formule “-” et comme la référence 28 n’existe pas il ne me donne aucune catégorie et met donc un – (tiret).
En C7 j’ai mis “”, donc vide, ici pas de référence, donc pas de catégorie, donc cellule vide. Choisissez !
Si « esterreur » n’est pas rajouté dans la formule vous verriez #N/A.
Maintenant que vous l’avez ajouté à votre formule, recopiezr tout d’abord vers le bas.
Puis, recopier C5 en D5 seulement car vous devez maintenant lui demander d’afficher la désignation. Changer donc le numéro de la colonne dans la formule en notifiant 3 puisque sur la feuille table la désignation se trouve dans la troisième colonne. Puis recopier également vers le bas. Faites de même pour les autres colonnes. Voici le tableau en formules.
Vous savez maintenant que seul le numéro de la colonne sera à modifier pour faire apparaitre le Prix HT en 5 cette fois puisque le HT est en cinquième colonne.
Et voilà votre tableau est terminé.
RECHERCHEV est une fonction très utile pour vos bases de données, vos devis, votre facturation, le suivi de salariés ou de produits.
Par exemple vous souhaitez à partir d’une base de données trouver rapidement toutes les informations sur un produit en saisissant simplement une référence.
Pour cela créez sur une autre feuille une Fiche Produit (à la verticale) ou vous n’aurez que la référence à saisir. Attention cette fois il faudra figer la cellule donc il vous faut les deux $.
J’avoue ma fiche produit est à l’image de ma base simplifiée ; justement c’est là où vous verrez l’utilité de RECHERCHEV pour créer votre fiche produits car, pour Vous, il y aura beaucoup plus de colonnes, avec beaucoup de renseignements.
Pour information : Il existe également la fonction RECHERCHEH. Celle-ci réalise une opération quasi identique elle recherche une valeur dans la ligne supérieure d’une table de valeurs, puis renvoie une valeur, dans la même colonne, à partir d’une ligne que vous spécifiez dans la table ou la matrice.
