Comment utiliser VLOOKUP dans Excel

VLOOKUP est l'une des fonctions les plus utiles d'Excel, et c'est aussi l'une des moins bien comprises. Dans cet article, nous démystifions VLOOKUP à l'aide d'un exemple concret. Nous allons créer un modèle de facture utilisable pour une société fictive.

VLOOKUP est une fonction Excel .Cet article supposera que le lecteur a déjà une compréhension passagère des fonctions Excel, et peut utiliser des fonctions de base telles que SUM, AVERAGE et AUJOURD'HUI.Dans son utilisation la plus courante, VLOOKUP est une fonction de la base de données , ce qui signifie qu'elle fonctionne avec les tables de base de données - ou plus simplement, répertorie les éléments dans une feuille de calcul Excel. Quel genre de choses? Eh bien, tout type de chose .Vous pouvez avoir une feuille de calcul contenant une liste d'employés, de produits, de clients ou de CD dans votre collection de CD, ou des étoiles dans le ciel nocturne. Cela n'a pas vraiment d'importance.

Voici un exemple de liste ou de base de données. Dans ce cas, c'est une liste de produits que notre société fictive vend:

Habituellement, les listes comme celles-ci ont une sorte d'identifiant unique pour chaque élément de la liste. Dans ce cas, l'identifiant unique est dans la colonne "Code article".Remarque: Pour que la fonction VLOOKUP fonctionne avec une base de données / liste, cette liste doit avoir une colonne contenant l'identifiant unique( ou "key", ou "ID"), et cette colonne doit être la première colonne de la table.Notre base de données exemple ci-dessus satisfait ce critère.

La partie la plus difficile de l'utilisation de VLOOKUP est de comprendre exactement à quoi elle sert. Voyons donc si nous pouvons d'abord le savoir:

VLOOKUP récupère des informations à partir d'une base de données / liste basée sur une instance fournie de l'identifiant unique.

Dans l'exemple ci-dessus, vous devez insérer la fonction RECHERCHEV dans une autre feuille de calcul avec un code d'article. Elle vous renvoie la description de l'article correspondant, son prix ou sa disponibilité( sa quantité "en stock").liste originale. Laquelle de ces informations vous fera-t-elle passer? Eh bien, vous devez décider cela lorsque vous créez la formule.

Si tout ce dont vous avez besoin est une information provenant de la base de données, il serait très difficile d'y construire une formule avec une fonction VLOOKUP.Généralement, vous utiliseriez ce type de fonctionnalité dans une feuille de calcul réutilisable, telle qu'un modèle. Chaque fois que quelqu'un entre un code d'article valide, le système récupère toutes les informations nécessaires sur l'article correspondant.

Prenons un exemple: un modèle de facture que nous pouvons réutiliser encore et encore dans notre société fictive.

Nous démarrons d'abord Excel, et nous créons une facture vierge:

Voici comment cela va fonctionner: La personne utilisant le modèle de facture remplira une série de codes article dans la colonne "A", et le système récupérera chaque articleDescription et prix de notre base de données de produits. Cette information sera utilisée pour calculer le total de la ligne pour chaque article( en supposant que nous entrons une quantité valide).

Dans le but de garder cet exemple simple, nous localiserons la base de données du produit sur une feuille séparée dans le même classeur:

En réalité, il est plus probable que la base de données du produit soit située dans un classeur séparé.Cela fait peu de différence pour la fonction VLOOKUP, qui ne se soucie pas vraiment si la base de données est située sur la même feuille, une feuille différente ou un classeur complètement différent.

Donc, nous avons créé notre base de données de produits, qui ressemble à ceci:

Afin de tester la formule VLOOKUP que nous sommes sur le point d'écrire, nous entrons d'abord un code valide dans la cellule A11 de notre facture vierge:

déplacez la cellule active dans la cellule dans laquelle nous voulons que les informations extraites de la base de données par VLOOKUP soient stockées. Fait intéressant, c'est l'étape que la plupart des gens se trompent. Pour expliquer davantage: Nous sommes sur le point de créer une formule VLOOKUP qui récupérera la description correspondant au code de l'article dans la cellule A11.Où voulons-nous cette description quand nous l'obtenons? Dans la cellule B11, bien sûr. Voilà où nous écrivons la formule VLOOKUP: dans la cellule B11.Sélectionnez la cellule B11 maintenant.

Nous devons localiser la liste de toutes les fonctions disponibles qu'Excel a à offrir, afin que nous puissions choisir VLOOKUP et obtenir de l'aide pour compléter la formule. Pour ce faire, cliquez d'abord sur l'onglet Formules , puis sur . Insérez la fonction :

.

Une boîte apparaît pour nous permettre de sélectionner l'une des fonctions disponibles dans Excel.

Pour trouver celui que nous cherchons, nous pourrions taper un terme de recherche comme "recherche"( parce que la fonction qui nous intéresse est une fonction de recherche ).Le système nous renvoie une liste de toutes les fonctions liées à la recherche dans Excel. VLOOKUP est le deuxième dans la liste. Sélectionnez-le un clic OK .

La zone Arguments de la fonction apparaît, nous invitant tous les arguments ( ou les paramètres ) nécessaires pour terminer la fonction VLOOKUP.Vous pouvez considérer cette case comme la fonction en nous posant les questions suivantes:

1. Quel identifiant unique recherchez-vous dans la base de données?
2. Où est la base de données?
3. Quelle information de la base de données, associée à l'identifiant unique, souhaitez-vous avoir récupérée pour vous?

Les trois premiers arguments sont affichés en gras , indiquant qu'il s'agit d'arguments obligatoires ( la fonction VLOOKUP est incomplète sans eux et ne renverra pas de valeur valide).Le quatrième argument n'est pas gras, ce qui signifie qu'il est facultatif:

Nous allons compléter les arguments dans l'ordre, de haut en bas.

Le premier argument que nous devons compléter est l'argument Lookup_value .La fonction a besoin de nous dire où trouver l'identifiant unique( le code article dans ce cas) qu'il devrait retourner la description de. Nous devons sélectionner le code d'article que nous avons entré plus tôt( en A11).

Cliquez sur l'icône du sélecteur à droite du premier argument:

Puis cliquez une fois sur la cellule contenant le code de l'article( A11), et appuyez sur Enter :

La valeur de "A11" est insérée dans le premier argument.

Nous devons maintenant entrer une valeur pour l'argument Table_array .En d'autres termes, nous devons indiquer à VLOOKUP où trouver la base de données / la liste. Cliquez sur l'icône du sélecteur à côté du second argument:

Localisez maintenant la base de données / liste et sélectionnez la liste complète - ne comprenant pas la ligne d'en-tête .Dans notre exemple, la base de données se trouve sur une feuille de travail séparée, donc nous cliquons d'abord sur cet onglet:

Ensuite, nous sélectionnons la base de données entière, sans inclure la ligne d'en-tête:

. .. et Entrez .La plage de cellules qui représente la base de données( dans ce cas "'Product Database'! A2: D7") est entrée automatiquement pour nous dans le second argument.

Maintenant, nous devons entrer le troisième argument, Col_index_num .Nous utilisons cet argument pour spécifier à VLOOKUP quel élément d'information de la base de données, associé à notre code d'article dans A11, nous souhaitons nous avoir retourné.Dans cet exemple particulier, nous souhaitons renvoyer la description de l'article .Si vous regardez sur la feuille de calcul de base de données, vous remarquerez que la colonne "Description" est la deuxième colonne dans la base de données. Cela signifie que nous devons entrer une valeur de "2" dans la case Col_index_num :

Il est important de noter que nous n'entrons pas ici un "2" parce que la colonne "Description" se trouve dans la colonne B cette feuille de travail. Si la base de données arrivait dans la colonne K de la feuille de calcul, nous entrerions toujours un "2" dans cette zone parce que la colonne "Description" est la deuxième colonne de l'ensemble de cellules que nous avons sélectionné "Table_array".

Enfin, nous devons décider si entrer une valeur dans l'argument final VLOOKUP, Range_lookup .Cet argument requiert une valeur true ou false ou doit être vide. Lors de l'utilisation de VLOOKUP avec des bases de données( comme c'est le cas 90% du temps), la façon de décider ce qu'il faut mettre dans cet argument peut être:

Si la première colonne de la base de données( la colonne contient les identifiants uniques)est trié alphabétiquement / numériquement dans l'ordre croissant, il est alors possible d'entrer une valeur de true dans cet argument, ou de le laisser vide.

Si la première colonne de la base de données est non triée ou triée par ordre décroissant, alors doit entrer une valeur de false dans cet argument

Comme la première colonne de notre base de données est non triée,nous entrons false dans cet argument:

C'est tout! Nous avons entré toutes les informations nécessaires pour VLOOKUP pour retourner la valeur dont nous avons besoin. Cliquez sur le bouton OK et notez que la description correspondant au code d'article "R99245" a été correctement entrée dans la cellule B11:

La formule qui a été créée pour nous ressemble à ceci:

Si nous saisissons un différent dans la celluleA11, nous allons commencer à voir la puissance de la fonction VLOOKUP: La cellule de description change pour correspondre au nouveau code article:

Nous pouvons effectuer un ensemble similaire d'étapes pour obtenir le prix de l'article retourné dans la cellule E11.Notez que la nouvelle formule doit être créée dans la cellule E11.Le résultat ressemblera à ceci:

. .. et la formule ressemblera à ceci:

Notez que la seule différence entre les deux formules est que le troisième argument( Col_index_num ) est passé d'un "2" à un "3"( parce quenous voulons des données extraites de la 3ème colonne dans la base de données).

Si nous décidions d'acheter 2 de ces articles, nous entrerions un "2" dans la cellule D11.Nous entrerions alors une formule simple dans la cellule F11 pour obtenir le total de ligne:

= D11 * E11

. .. qui ressemble à ceci. ..

Compléter le modèle de facture

Nous avons beaucoup appris sur VLOOKUP jusqu'à maintenant. En fait, nous avons appris tout ce que nous allons apprendre dans cet article. Il est important de noter que VLOOKUP peut être utilisé dans d'autres circonstances que les bases de données. Ceci est moins commun, et peut être couvert dans les futurs articles How-To Geek.

Notre modèle de facture n'est pas encore complet. Afin de le compléter, nous ferions ce qui suit:

1. Nous supprimerions le code de l'échantillon de la cellule A11 et le "2" de la cellule D11.Cela entraînera l'affichage des messages d'erreur dans nos formules VLOOKUP nouvellement créées:
   
   Nous pouvons y remédier en utilisant judicieusement les fonctions IF() et ISBLANK() d'Excel. Nous changeons notre formule à partir de ce. .. = VLOOKUP( A11, 'Product Database'! A2: D7,2, FALSE) . .. à ce. .. = SI( ISBLANK( A11), "", VLOOKUP( A11, 'Base de données de produits'! A2: D7,2, FALSE))
   
2. Nous copions les formules dans les cellules B11, E11 et F11 jusqu'au reste des lignes d'articles de la facture. Notez que si nous faisons cela, les formules résultantes ne se référeront plus correctement à la table de base de données. Nous pourrions résoudre ce problème en changeant les références de cellules pour la base de données références de cellules absolues. Alternativement - et même mieux - nous pourrions créer un nom de gamme pour toute la base de données du produit( comme "Produits"), et utiliser ce nom de plage à la place des références de cellules. La formule changerait de ceci. .. = SI( ISBLANK( A11), "", VLOOKUP( A11, 'Base de données de produit'! A2: D7,2, FALSE)) . .. à ceci. .. = SI( ISBLANK( A11), "", VLOOKUP( A11, Produits, 2, FALSE)) . .. et puis copier les formules vers le bas des lignes d'éléments de facture.
3. Nous verrons probablement les cellules qui contiennent nos formules( ou plutôt débloquer les autres cellules ), puis protéger la feuille de travail, afin de s'assurer que nos formules soigneusement construites ne sont pas accidentellement écrasées quand quelqu'un vient à remplirdans la facture.
4. Nous enregistrerions le fichier comme un modèle , de sorte qu'il pourrait être réutilisé par tout le monde dans notre société

Si nous nous sentions vraiment intelligent, nous créerions une base de données de tous nos clients dans une autre feuille de calcul, puis utiliser leidentifiant client entré dans la cellule F5 pour renseigner automatiquement le nom et l'adresse du client dans les cellules B6, B7 et B8.

Si vous souhaitez vous exercer avec VLOOKUP, ou simplement voir notre modèle de facture, vous pouvez le télécharger ici.