Fonction RECHERCHEV dans Excel - Guide du débutant : syntaxe et exemples

Aujourd'hui, nous commençons une série d'articles décrivant l'une des fonctionnalités les plus utiles d'Excel - VPR (RECHERCHEV). Cette fonction, à la fois, est l'une des plus complexes et des moins comprises.

Dans ce tutoriel sur VPR Je vais essayer de présenter les bases aussi simplement que possible afin de rendre le processus d'apprentissage aussi clair que possible pour les utilisateurs inexpérimentés. De plus, nous étudierons plusieurs exemples avec des formules Excel qui démontreront les cas d'utilisation les plus courants de la fonction VPR.

• Description générale et syntaxe
• Exemples RECHERCHEV
  • Comment utiliser VLOOKUP pour rechercher dans une autre feuille Excel
  • Rechercher dans un autre classeur avec VLOOKUP
  • Comment utiliser une plage ou une table nommée dans des formules avec VLOOKUP
  • Utilisation de caractères génériques dans les formules VLOOKUP
• Correspondance exacte ou approximative dans la fonction VLOOKUP
• VLOOKUP dans Excel - vous devez vous en souvenir !

Fonction VLOOKUP dans Excel - description générale et syntaxe

Alors c'est quoi VPR? Eh bien, tout d'abord, c'est une fonction Excel. Ce qu'elle fait? Il recherche la valeur que vous spécifiez et renvoie la valeur correspondante de l'autre colonne. Techniquement parlant, VPR recherche la valeur dans la première colonne de la plage donnée et renvoie le résultat d'une autre colonne de la même ligne.

Dans l'application la plus courante, la fonction VPR recherche dans la base de données un identifiant unique donné et extrait certaines informations qui s'y rapportent de la base de données.

Première lettre du nom de la fonction VPR (RECHERCHEV) signifie Вverticale (Vvertical). Par lui, vous pouvez distinguer VPR de GPR (HLOOKUP), qui recherche une valeur dans la ligne supérieure d'une plage - Гhorizontal (Hhorizontalement).

Fonction VPR disponible dans Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP et Excel 2000.

Syntaxe de la fonction RECHERCHEV

Fonction VPR (RECHERCHEV) a la syntaxe suivante :

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

Comme vous pouvez le voir, une fonction VPR dans Microsoft Excel a 4 options (ou arguments). Les trois premiers sont obligatoires, le dernier est facultatif.

• valeur de recherche (lookup_value) – La valeur à rechercher. Il peut s'agir d'une valeur (nombre, date, texte) ou d'une référence de cellule (contenant la valeur de recherche), ou d'une valeur renvoyée par une autre fonction Excel. Par exemple, cette formule recherchera la valeur 40:

  =VLOOKUP(40,A2:B15,2)

  =ВПР(40;A2:B15;2)

Si la valeur de recherche est inférieure à la plus petite valeur de la première colonne de la plage recherchée, la fonction VPR signalera une erreur # AT (#N / A).

• table_array (tableau) – deux ou plusieurs colonnes de données. Rappelez-vous, la fonction VPR recherche toujours la valeur dans la première colonne de la plage donnée en argument table_array (table). La plage visible peut contenir diverses données, telles que du texte, des dates, des nombres, des booléens. La fonction est insensible à la casse, ce qui signifie que les caractères majuscules et minuscules sont considérés comme identiques. Notre formule recherchera donc la valeur 40 dans les cellules de A2 à A15, car A est la première colonne de la plage A2:B15 donnée dans l'argument table_array (tableau):

  =VLOOKUP(40,A2:B15,2)

  =ВПР(40;A2:B15;2)

• col_index_num (column_number) est le numéro de la colonne dans la plage donnée à partir de laquelle la valeur de la ligne trouvée sera renvoyée. La colonne la plus à gauche dans la plage donnée est 1, la deuxième colonne est 2, la troisième colonne est 3 etc. Vous pouvez maintenant lire toute la formule :

  =VLOOKUP(40,A2:B15,2)

  =ВПР(40;A2:B15;2)

  Formule à la recherche de valeur 40 dans le périmètre A2: A15 et renvoie la valeur correspondante de la colonne B (car B est la deuxième colonne de la plage A2:B15).

Si la valeur de l'argument col_index_num (numéro_colonne) inférieur à 1puis VPR signalera une erreur #VALEUR! (#ÉVALUER!). Et si c'est plus que le nombre de colonnes dans la plage table_array (table), la fonction renverra une erreur #REF! (#LIEN!).

• range_lookup (range_lookup) – détermine ce qu'il faut rechercher :
  • correspondance exacte, l'argument doit être égal FAUX (FAUX);
  • correspondance approximative, argument égal VRAI CODE (VRAI) ou pas spécifié du tout.

  Ce paramètre est facultatif, mais très important. Plus loin dans ce tutoriel sur VPR Je vais vous montrer quelques exemples expliquant comment écrire des formules pour trouver des correspondances exactes et approximatives.

Exemples RECHERCHEV

j'espère que la fonction VPR devenir un peu plus clair pour vous. Voyons maintenant quelques cas d'utilisation VPR dans des formules avec des données réelles.

Comment utiliser VLOOKUP pour rechercher dans une autre feuille Excel

En pratique, les formules avec une fonction VPR sont rarement utilisés pour rechercher des données sur la même feuille de calcul. Le plus souvent, vous chercherez et récupérerez les valeurs correspondantes dans une autre feuille.

Pour utiliser VPR, rechercher dans une autre feuille Microsoft Excel, Vous devez en argument table_array (tableau) spécifiez le nom de la feuille avec un point d'exclamation suivi d'une plage de cellules. Par exemple, la formule suivante montre que la plage A2 : B15 se trouve sur une feuille nommée Sheet2.

=VLOOKUP(40,Sheet2!A2:B15,2)

=ВПР(40;Sheet2!A2:B15;2)

Bien sûr, le nom de la feuille n'a pas besoin d'être saisi manuellement. Commencez simplement à taper la formule, et quand il s'agit de l'argument table_array (tableau), passez à la feuille souhaitée et sélectionnez la plage de cellules souhaitée avec la souris.

La formule illustrée dans la capture d'écran ci-dessous recherche le texte "Produit 1" dans la colonne A (c'est la 1ère colonne de la plage A2: B9) sur une feuille de calcul Tarifs.

=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)

=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

N'oubliez pas que lorsque vous recherchez une valeur de texte, vous devez la placer entre guillemets (""), comme cela se fait généralement dans les formules Excel.

Pour argumenter table_array (table) il est souhaitable de toujours utiliser des références absolues (avec le signe $). Dans ce cas, la plage de recherche restera inchangée lors de la copie de la formule dans d'autres cellules.

Rechercher dans un autre classeur avec VLOOKUP

Pour fonctionner VPR travaillé entre deux classeurs Excel, vous devez spécifier le nom du classeur entre crochets avant le nom de la feuille.

Par exemple, ci-dessous est une formule qui recherche la valeur 40 sur la feuille Sheet2 Dans le livre Nombres.xlsx:

=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)

=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)

Voici le moyen le plus simple de créer une formule dans Excel avec VPRqui renvoie à un autre classeur :

1. Ouvrez les deux livres. Ce n'est pas obligatoire, mais il est plus facile de créer une formule de cette façon. Vous ne voulez pas entrer le nom du classeur manuellement, n'est-ce pas ? De plus, cela vous protégera des fautes de frappe accidentelles.
2. Commencez à taper une fonction VPRet quand il s'agit de l'argument table_array (tableau), passez à un autre classeur et sélectionnez-y la plage de recherche requise.

La capture d'écran ci-dessous montre la formule avec la recherche définie sur une plage dans le classeur Liste de prix.xlsx sur la feuille Tarifs.

Fonction VPR fonctionnera même lorsque vous fermez le classeur recherché et que le chemin d'accès complet au fichier de classeur apparaît dans la barre de formule, comme indiqué ci-dessous :

Si le nom du classeur ou de la feuille contient des espaces, il doit être entouré d'apostrophes :

=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)

=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)

Comment utiliser une plage ou une table nommée dans des formules avec VLOOKUP

Si vous envisagez d'utiliser la même plage de recherche dans plusieurs fonctions VPR, vous pouvez créer une plage nommée et entrer son nom dans la formule comme argument table_array (tableau).

Pour créer une plage nommée, sélectionnez simplement les cellules et entrez un nom approprié dans le champ Prénom, à gauche de la barre de formule.

Vous pouvez maintenant écrire la formule suivante pour trouver le prix d'un produit Produit 1:

=VLOOKUP("Product 1",Products,2)

=ВПР("Product 1";Products;2)

La plupart des noms de plage fonctionnent pour l'ensemble du classeur Excel, il n'est donc pas nécessaire de spécifier le nom de la feuille pour l'argument table_array (tableau), même si la formule et la plage de recherche se trouvent sur des feuilles de calcul différentes. S'ils se trouvent dans des classeurs différents, vous devez spécifier le nom du classeur avant le nom de la plage, par exemple, comme ceci :

=VLOOKUP("Product 1",PriceList.xlsx!Products,2)

=ВПР("Product 1";PriceList.xlsx!Products;2)

Donc la formule semble beaucoup plus claire, d'accord ? De plus, l'utilisation de plages nommées est une bonne alternative aux références absolues, car la plage nommée ne change pas lorsque vous copiez la formule dans d'autres cellules. Cela signifie que vous pouvez être sûr que la plage de recherche dans la formule restera toujours correcte.

Si vous convertissez une plage de cellules en une feuille de calcul Excel complète à l'aide de la commande lampe de table Onglet (Tableau) Insertion (Insérer), puis lorsque vous sélectionnez une plage avec la souris, Microsoft Excel ajoute automatiquement les noms des colonnes (ou le nom du tableau si vous sélectionnez le tableau entier) à la formule.

La formule finie ressemblera à ceci :

=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)

=ВПР("Product 1";Table46[[Product]:[Price]];2)

Ou peut-être même comme ça :

=VLOOKUP("Product 1",Table46,2)

=ВПР("Product 1";Table46;2)

Lorsque vous utilisez des plages nommées, les liens pointent vers les mêmes cellules, peu importe où vous copiez la fonction VPR au sein du cahier d'exercices.

Utilisation de caractères génériques dans les formules VLOOKUP

Comme pour beaucoup d'autres fonctions, VPR Vous pouvez utiliser les caractères génériques suivants :

• Point d'interrogation (?) - remplace n'importe quel caractère unique.
• Astérisque (*) – remplace toute séquence de caractères.

Utilisation de caractères génériques dans les fonctions VPR peut être utile dans de nombreux cas, par exemple :

• Lorsque vous ne vous souvenez pas exactement du texte que vous devez rechercher.
• Lorsque vous souhaitez rechercher un mot faisant partie du contenu d'une cellule. Sache que VPR recherche par le contenu de la cellule dans son ensemble, comme si l'option était activée Faire correspondre tout le contenu de la cellule (Cellule entière) dans la recherche Excel standard.
• Lorsqu'une cellule contient des espaces supplémentaires au début ou à la fin du contenu. Dans une telle situation, vous pouvez vous creuser la tête pendant longtemps, en essayant de comprendre pourquoi la formule ne fonctionne pas.

Exemple 1 : Recherche de texte commençant ou se terminant par certains caractères

Supposons que vous souhaitiez rechercher un client spécifique dans la base de données ci-dessous. Vous ne vous souvenez pas de son nom de famille, mais vous savez qu'il commence par « ack ». Voici une formule qui fera parfaitement l'affaire :

=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)

=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)

Maintenant que vous êtes sûr d'avoir trouvé le bon nom, vous pouvez utiliser la même formule pour trouver le montant payé par ce client. Pour ce faire, il suffit de changer le troisième argument de la fonction VPR au numéro de colonne souhaité. Dans notre cas, il s'agit de la colonne C (3ème de la plage) :

=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)

=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)

Voici quelques exemples supplémentaires avec des caractères génériques :

~ Trouver un nom se terminant par "man":

=VLOOKUP("*man",$A$2:$C$11,1,FALSE)

=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)

~ Trouvez un nom qui commence par « ad » et se termine par « son » :

=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)

=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)

~ On retrouve le prénom dans la liste, composé de 5 caractères :

=VLOOKUP("?????",$A$2:$C$11,1,FALSE)

=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)

Pour fonctionner VPR avec des caractères génériques a fonctionné correctement, comme quatrième argument, vous devez toujours utiliser FAUX (FAUX). Si la plage de recherche contient plusieurs valeurs correspondant aux termes de recherche avec des caractères génériques, la première valeur trouvée sera renvoyée.

Exemple 2 : combiner des caractères génériques et des références de cellule dans les formules VLOOKUP

Examinons maintenant un exemple légèrement plus complexe de la façon de rechercher à l'aide de la fonction VPR par valeur dans une cellule. Imaginez que la colonne A est une liste de clés de licence et que la colonne B est une liste de noms qui possèdent une licence. De plus, vous avez une partie (plusieurs caractères) d'une sorte de clé de licence dans la cellule C1, et vous souhaitez trouver le nom du propriétaire.

Cela peut être fait en utilisant la formule suivante :

=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)

=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)

Cette formule recherche la valeur de la cellule C1 dans la plage donnée et renvoie la valeur correspondante de la colonne B. Notez que dans le premier argument, nous utilisons une esperluette (&) avant et après la référence de cellule pour lier la chaîne de texte.

Comme vous pouvez le voir sur la figure ci-dessous, la fonction VPR renvoie "Jeremy Hill" car sa clé de licence contient la séquence de caractères de la cellule C1.

Notez que l'argument table_array (table) dans la capture d'écran ci-dessus contient le nom de la table (Table7) au lieu de spécifier une plage de cellules. C'est ce que nous avons fait dans l'exemple précédent.

Correspondance exacte ou approximative dans la fonction VLOOKUP

Et enfin, examinons de plus près le dernier argument spécifié pour la fonction VPR - range_lookup (intervalle_vue). Comme mentionné au début de la leçon, cet argument est très important. Vous pouvez obtenir des résultats complètement différents dans la même formule avec sa valeur VRAI CODE (Vrai ou FAUX (FAUX).

Voyons d'abord ce que Microsoft Excel entend par correspondances exactes et approximatives.

• Si l'argument range_lookup (range_lookup) est égal à FAUX (FAUX), la formule recherche une correspondance exacte, c'est-à-dire exactement la même valeur que celle donnée en argument valeur de recherche (lookup_value). Si dans la première colonne de la plage ttableau_able (table) rencontre deux valeurs ou plus qui correspondent à l'argument valeur de recherche (search_value), alors le premier sera sélectionné. Si aucune correspondance n'est trouvée, la fonction signalera une erreur # AT (#N / A). Par exemple, la formule suivante signalera une erreur # AT (#N/A) s'il n'y a pas de valeur dans la plage A2:A15 4:

  =VLOOKUP(4,A2:B15,2,FALSE)

  =ВПР(4;A2:B15;2;ЛОЖЬ)

• Si l'argument range_lookup (range_lookup) est égal à VRAI CODE (TRUE), la formule recherche une correspondance approximative. Plus précisément, d'abord la fonction VPR recherche une correspondance exacte, et si aucune n'est trouvée, sélectionne une correspondance approximative. Une correspondance approximative est la plus grande valeur qui ne dépasse pas la valeur spécifiée dans l'argument. valeur de recherche (lookup_value).

Si l'argument range_lookup (range_lookup) est égal à VRAI CODE (TRUE) ou non spécifié, les valeurs de la première colonne de la plage doivent être triées par ordre croissant, c'est-à-dire de la plus petite à la plus grande. Sinon, la fonction VPR peut renvoyer un résultat erroné.

Pour mieux comprendre l'importance du choix VRAI CODE (VÉRITÉ) ou FAUX (FAUX), regardons d'autres formules avec la fonction VPR et regardez les résultats.

Exemple 1 : Recherche d'une correspondance exacte avec VLOOKUP

Comme vous vous en souvenez, pour rechercher une correspondance exacte, le quatrième argument de la fonction VPR devrait avoir de l'importance FAUX (FAUX).

Revenons au tableau du tout premier exemple et découvrons quel animal peut se déplacer à une vitesse 50 miles par heure. Je crois que cette formule ne vous causera aucune difficulté :

=VLOOKUP(50,$A$2:$B$15,2,FALSE)

=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)

Notez que notre plage de recherche (colonne A) contient deux valeurs 50 – dans les cellules A5 и A6. La formule renvoie la valeur de la cellule B5. Pourquoi? Parce que lors de la recherche d'une correspondance exacte, la fonction VPR utilise la première valeur trouvée qui correspond à celle recherchée.

Exemple 2 : Utilisation de VLOOKUP pour trouver une correspondance approximative

Lorsque vous utilisez la fonction VPR pour rechercher une correspondance approximative, c'est-à-dire lorsque l'argument range_lookup (range_lookup) est égal à VRAI CODE (VRAI) ou omis, la première chose à faire est de trier la plage par la première colonne dans l'ordre croissant.

Ceci est très important car la fonction VPR renvoie la valeur la plus grande suivante après celle donnée, puis la recherche s'arrête. Si vous négligez le tri correct, vous vous retrouverez avec des résultats très étranges ou un message d'erreur. # AT (#N / A).

Vous pouvez maintenant utiliser l'une des formules suivantes :

=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)

=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)

Comme vous pouvez le voir, je veux savoir lequel des animaux a la vitesse la plus proche de 69 miles par heure. Et voici le résultat que la fonction m'a renvoyé VPR:

Comme vous pouvez le voir, la formule a renvoyé un résultat Antilope (Antilope), dont la vitesse 61 miles par heure, bien que la liste comprenne également Cheetah (Guépard) qui court à grande vitesse 70 miles par heure, et 70 est plus proche de 69 que 61, n'est-ce pas ? Pourquoi cela arrive-t-il? Parce que la fonction VPR lors de la recherche d'une correspondance approximative, renvoie la plus grande valeur qui n'est pas supérieure à celle recherchée.

J'espère que ces exemples éclaireront l'utilisation de la fonction VPR dans Excel, et vous ne la considérez plus comme une étrangère. Maintenant, cela ne fait pas de mal de répéter brièvement les points clés du matériel que nous avons étudié afin de mieux le fixer en mémoire.

VLOOKUP dans Excel - vous devez vous en souvenir !

1. Fonction VPR Excel ne peut pas regarder à gauche. Il recherche toujours la valeur dans la colonne la plus à gauche de la plage donnée par l'argument table_array (tableau).
2. En fonction VPR toutes les valeurs sont insensibles à la casse, c'est-à-dire que les lettres minuscules et majuscules sont équivalentes.
3. Si la valeur recherchée est inférieure à la valeur minimale de la première colonne de la plage recherchée, la fonction VPR signalera une erreur # AT (#N / A).
4. Si 3ème argument col_index_num (numéro_colonne) inférieur à 1fonction VPR signalera une erreur #VALEUR! (#ÉVALUER!). S'il est supérieur au nombre de colonnes dans la plage table_array (tableau), la fonction signalera une erreur #REF! (#LIEN!).
5. Utiliser des références de cellule absolues dans l'argument table_array (table) afin que la plage de recherche correcte soit conservée lors de la copie de la formule. Essayez d'utiliser des plages ou des tables nommées dans Excel comme alternative.
6. Lorsque vous effectuez une recherche de correspondance approximative, n'oubliez pas que la première colonne de la plage que vous recherchez doit être triée par ordre croissant.
7. Enfin, souvenez-vous de l'importance du quatrième argument. Utiliser des valeurs VRAI CODE (VÉRITÉ) ou FAUX (FAUX) délibérément et vous vous débarrasserez de nombreux maux de tête.

Dans les articles suivants de notre tutoriel sur les fonctions VPR dans Excel, nous apprendrons des exemples plus avancés, tels que la réalisation de divers calculs à l'aide VPR, extraire des valeurs de plusieurs colonnes, etc. Merci d'avoir lu ce tutoriel et j'espère vous revoir la semaine prochaine !