VLOOKUP est l’une des fonctions les plus utiles d’Excel et l’une des moins bien comprise. Dans cet article, nous démystifions VLOOKUP à l'aide d'un exemple concret. Nous allons créer un Modèle de facture pour une société fictive.
VLOOKUP est un Excel une fonction. Cet article suppose que le lecteur comprend déjà parfaitement les fonctions Excel et peut utiliser des fonctions de base telles que SUM, AVERAGE et TODAY. Dans son utilisation la plus courante, VLOOKUP est un base de données fonction, ce qui signifie qu'il fonctionne avec des tables de base de données - ou plus simplement, des listes des choses dans une feuille de calcul Excel. Quel genre de choses? Bien, tout genre de chose. Vous pouvez disposer d’une feuille de calcul contenant une liste d’employés, de produits, de clients ou de CD de votre collection de CD ou d’é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, il s’agit d’une liste de produits vendus par notre société fictive:
Habituellement, les listes comme celle-ci ont une sorte d'identifiant unique pour chaque élément de la liste. Dans ce cas, l'identifiant unique se trouve dans la colonne «Code d'article». Remarque: pour que la fonction VLOOKUP fonctionne avec une base de données / liste, cette liste doit comporter une colonne contenant l'identifiant unique (ou "clé" ou "ID"), et cette colonne doit être la première colonne de la table. Notre base de données exemple ci-dessus répond à ce critère.
La partie la plus difficile de l’utilisation de VLOOKUP est de comprendre exactement à quoi il sert. Voyons donc si nous pouvons clarifier cela en premier:
VLOOKUP retrieves information from a database/list based on a supplied instance of the unique identifier.
Dans l'exemple ci-dessus, vous inséreriez la fonction VLOOKUP dans une autre feuille de calcul avec un code d'article, qui vous renverrait soit la description de l'article correspondant, son prix, soit sa disponibilité (sa quantité «en stock») comme décrit dans votre liste. Laquelle de ces informations va-t-elle vous renvoyer? Eh bien, vous devez en décider lorsque vous créez la formule.
Si tout ce dont vous avez besoin est une information de la base de données, il serait très difficile de créer une formule avec une fonction VLOOKUP. En règle générale, 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.
Créons un exemple: Modèle de facture que nous pouvons réutiliser encore et encore dans notre société fictive.
Nous commençons par démarrer Excel et nous créons nous-mêmes une facture vierge:
Pour que cet exemple reste simple, nous localiserons la base de données de produits sur une feuille distincte du même classeur:
Nous avons donc créé notre base de données de produits, qui ressemble à ceci:
Nous devons localiser la liste de toutes les fonctions disponibles offertes par Excel, de manière à pouvoir choisir VLOOKUP et à obtenir de l’aide pour compléter la formule. Ceci est trouvé en cliquant d'abord sur le Formules onglet, puis en cliquant sur Insérer une fonction:
Pour trouver celui que nous recherchons, nous pouvons taper un terme de recherche tel que "lookup" (parce que la fonction qui nous intéresse est une fonction Chercher une fonction). Le système nous renverrait une liste de toutes les fonctions liées à la recherche dans Excel. VLOOKUP est le deuxième dans la liste. Sélectionnez-le en un clic D'accord.
le Arguments de fonction Une boîte de dialogue apparaît, nous invitant à arguments (ou paramètres) nécessaires pour compléter la fonction VLOOKUP. Vous pouvez considérer cette boîte comme la fonction qui nous pose les questions suivantes:
- Quel identifiant unique recherchez-vous dans la base de données?
- Où est la base de données?
- Quelle information de la base de données, associée à l'identifiant unique, souhaitez-vous récupérer pour vous?
Les trois premiers arguments sont montrés en gras, indiquant qu'ils sont obligatoire arguments (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:
Le premier argument que nous devons compléter est le Lookup_value argument. La fonction a besoin que nous lui disions où trouver l'identifiant unique (le code de l'article dans ce cas) qu'il devrait renvoyer la description de. Nous devons sélectionner le code de poste que nous avons entré précédemment (dans A11).
Cliquez sur l'icône de sélecteur à droite du premier argument:
Cliquez ensuite une fois sur la cellule contenant le code de poste (A11) et appuyez sur Entrer:
La valeur de «A11» est insérée dans le premier argument.
Maintenant, nous devons entrer une valeur pour le Tableau_Array argument. En d'autres termes, nous devons dire à VLOOKUP où trouver la base de données / la liste. Cliquez sur l'icône de sélecteur à côté du deuxième argument:
… Et appuyez sur Entrer. 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 deuxième argument.
Maintenant, nous devons entrer le troisième argument, Col_index_num. Nous utilisons cet argument pour spécifier à VLOOKUP quelle information de la base de données, associer à notre code d’article dans A11, que nous souhaitons nous renvoyer. Dans cet exemple particulier, nous souhaitons que l’élément la description retourné à nous. Si vous consultez la feuille de calcul de la base de données, vous remarquerez que la colonne «Description» est la seconde colonne dans la base de données. Cela signifie que nous devons entrer une valeur de «2» dans le champ Col_index_num boîte:
Il est important de noter que nous ne saisissons pas un «2» ici car la colonne «Description» est dans le B colonne de cette feuille de calcul. Si la base de données se trouvait dans la colonne K de la feuille de calcul, nous entrerions quand même un «2» dans ce champ car la colonne «Description» est la deuxième colonne de l’ensemble de cellules que nous avons sélectionnées lors de la spécification de «Table_array».
Enfin, nous devons décider s’il faut entrer une valeur dans l’argument final de VLOOKUP, Range_lookup. Cet argument nécessite soit un vrai ou faux valeur, ou il devrait être laissé en blanc. Lorsque vous utilisez VLOOKUP avec des bases de données (comme cela est vrai dans 90% des cas), la façon de décider quoi mettre dans cet argument peut être envisagée comme suit:
If the first column of the database (the column that contains the unique identifiers) is sorted alphabetically/numerically in ascending order, then it’s possible to enter a value of true into this argument, or leave it blank.
If the first column of the database is not sorted, or it’s sorted in descending order, then you must enter a value of false into this argument
Comme la première colonne de notre base de données est ne pas triés, nous entrons faux dans cet argument:
C'est tout! Nous avons saisi toutes les informations nécessaires à VLOOKUP pour obtenir la valeur dont nous avons besoin. Clique le D'accord et notez que la description correspondant au code de poste «R99245» a été correctement entrée dans la cellule B11:
Si nous entrons dans un différent Code d'article dans la cellule A11, nous allons commencer à voir la puissance de la fonction VLOOKUP: La cellule de description change pour correspondre au nouveau code d'article:
Nous pouvons effectuer un ensemble similaire d’étapes pour obtenir l’élément prix retourné dans la cellule E11. Notez que la nouvelle formule doit être créée dans la cellule E11. Le résultat ressemblera à ceci:
Notez que la seule différence entre les deux formules est le troisième argument (Col_index_num) est passé de “2” à “3” (car nous voulons récupérer les données de la 3ème colonne de 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 ensuite une formule simple dans la cellule F11 pour obtenir le total de la ligne:
=D11*E11
… Qui ressemble à ceci…
Remplir le modèle de facture
Nous avons beaucoup appris sur VLOOKUP jusqu'à présent. 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 courant et pourrait être traité dans de futurs articles How-To Geek.
Notre modèle de facture n'est pas encore complet. Pour le compléter, nous ferions ce qui suit:
-
Nous supprimerions le code de l'échantillon de la cellule A11 et le «2» de la cellule D11. Nos formules VLOOKUP nouvellement créées afficheront des messages d'erreur:
Image Nous pouvons y remédier en utilisant judicieusement le logiciel Excel. SI() et ISBLANK () les fonctions. Nous changeons notre formule à partir de ça… = VLOOKUP (A11, «Base de données de produits»! A2: D7,2, FALSE) …pour ça… = IF (ISBLANK (A11), "", VLOOKUP (A11, «Base de données de produits»! A2: D7,2, FALSE))
- Nous copierions les formules des cellules B11, E11 et F11 dans le reste des lignes de poste de la facture. Notez que si nous procédons ainsi, les formules résultantes ne feront plus correctement référence à la table de base de données. Nous pourrions résoudre ce problème en modifiant les références de cellule de la base de données. absolu références de cellules. Alternativement - et même mieux - nous pourrions créer un nom de plage pour la totalité de la base de données de produits (telle que «Produits») et utilisez ce nom de plage au lieu des références de cellule. La formule changerait de cela… = IF (ISBLANK (A11), "", VLOOKUP (A11, «Base de données de produits»! A2: D7,2, FALSE)) …pour ça… = IF (ISBLANK (A11), "", VLOOKUP (A11, Produits, 2, FAUX)) …et puis copiez les formules dans le reste des lignes du poste de facturation.
- Nous allons probablement «verrouiller» les cellules qui contiennent nos formules (ou plutôt ouvrir la autre cellules), puis protégez la feuille de calcul afin de vous assurer que nos formules soigneusement élaborées ne sont pas écrasées par inadvertance lorsque quelqu'un vient remplir la facture.
- Nous enregistrerions le fichier en tant que modèle, afin qu'il puisse être réutilisé par tout le monde dans notre entreprise
Si nous nous sentions vraiment astucieux, nous créerions une base de données de tous nos clients dans une autre feuille de calcul, puis nous utiliserions l’ID client saisi dans la cellule F5 pour renseigner automatiquement le nom et l’adresse du client dans les cellules B6, B7 et B8.
