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 database , ce qui signifie qu'elle fonctionne avec des tables de base de données - ou plus simplement, répertorie des éléments d'une feuille de calcul Excel. Quel genre de choses? Eh bien, tout genre 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, il s'agit d'une liste de produits vendus par notre société fictive:

Généralement, 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 du tableau . Notre base de données exemple ci-dessus répond à ce critère.

Le plus difficile dans l'utilisation de VLOOKUP est de comprendre exactement à quoi il sert. Voyons si nous pouvons d'abord le savoir: récupère les informations d'une base / liste basée sur une instance fournie de l'identifiant unique

Dans l'exemple ci-dessus, vous insérez la fonction VLOOKUP dans une autre feuille de calcul avec un code d'article, et il vous retournera soit la description de l'article correspondant, son prix, ou sa disponibilité (sa quantité "en stock") comme décrit dans votre liste d'origine. Lequel de ces éléments d'information vous repassera-t-il? Eh bien, vous décidez cela quand vous créez la formule.

Si tout ce dont vous avez besoin est une information provenant de la base de données, vous auriez beaucoup de difficulté à construire une formule avec une fonction VLOOKUP dans il. 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.

Créons un exemple de ceci: Un

modèle de facture que nous pouvons réutiliser encore et encore dans notre société fictive. Nous commençons par Excel, et nous créons une facture vierge:

Voici comment cela va fonctionner: La personne qui utilise le modèle de facture remplira une série de codes article dans la colonne "A", et le système récupérera la description et le prix de chaque article de notre base de données de produits. Ces informations seront utilisées pour calculer le total de la ligne pour chaque article (en supposant que nous saisissons une quantité valide).

Afin 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 de produits se trouve dans un classeur distinct. 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.

Nous avons donc créé notre base de données de produits comme 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:

Ensuite, nous déplaçons la cellule active dans la cellule 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. Vous pouvez le trouver en cliquant d'abord sur l'onglet

Formules , puis en cliquant sur Insérer la fonction : Une boîte permettant de sélectionner l'une des fonctions disponibles dans Excel s'affiche. Pour trouver celui que nous cherchons, nous pourrions taper un terme de recherche comme "lookup" (parce que la fonction qui nous intéresse est une fonction

lookup

). Le système nous renvoie une liste de toutes les fonctions liées à la recherche dans Excel VLOOKUP est le deuxième de la liste. Sélectionnez-le en cliquant OK . La boîte Arguments de la fonction

apparaît, nous invitant tous les arguments (ou paramètres ) nécessaires afin de compléter la fonction VLOOKUP. Vous pouvez considérer cette case comme la fonction en nous posant les questions suivantes: Quel identifiant unique cherchez-vous dans la base de données? Où se trouve la base de données?

1. Quelle information provenant de la base de données, associée à l'identifiant unique, voulez-vous avoir récupéré pour vous?
2. Les trois premiers arguments sont affichés
3. en gras

, indiquant qu'il s'agit d'arguments obligatoires (la fonction VLOOKUP est incomplète sans eux et ne retournera pas une 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 que nous lui indiquions 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é précédemment (en A11) Cliquez sur l'icône du sélecteur à droite du premier argument: Puis cliquez une fois sur la cellule contenant le code article (A11), et appuyez sur

Entrée

: La valeur de "A11" est insérée dans le premier argument Maintenant, nous devons 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 deuxième argument: Localisez maintenant la base de données / liste et sélectionnez la liste entière - sans la ligne d'en-tête

. Dans notre exemple, la base de données se trouve sur une feuille de travail séparée. Nous cliquons d'abord sur cet onglet: Ensuite, nous sélectionnons la base de données complète, sans la ligne d'en-tête ... et appuyez sur

>. La plage de cellules qui représente la base de données (dans ce cas "'Product Database'! A2: D7") est automatiquement entrée dans le deuxième argument.

Nous devons maintenant 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 que la

description de l'article nous soit renvoyée. Si vous regardez sur la feuille de calcul de base de données, vous remarquerez que la colonne "Description" est la colonne deuxième 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" car la colonne "Description" est en la colonne B sur cette feuille de calcul. Si la base de données devait commencer dans la colonne

K de la feuille de calcul, nous entrerions toujours un "2" dans cette zone car 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 VLOOKUP final, Range_lookup . Cet argument nécessite une valeur

true ou false , ou il doit être laissé vide. Lorsque vous utilisez 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 considérée comme suit: Si la première colonne de la base de données identifiants) est trié alphabétiquement / numériquement dans l'ordre croissant, il est alors possible d'entrer une valeur true dans cet argument, ou laissez-le vide.

Si la première colonne de la base de données est non triée ou triée par ordre décroissant, vous

devez entrer une valeur de false dans cet argument Comme la première colonne de notre base de données est pas triée, nous introduisons

false dans cet argument: il! 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 article "R99245" a été correctement entrée dans la cellule B11:

La formule qui a été créée pour nous ressemble à ceci: Si nous entrons un code d'article

différent dans la cellule A11, nous commencerons à 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 est revenu 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

) a changé de "2 "À un" 3 "(parce que nous voulons des données récupérées 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 la ligne totale: = D11 * E11 ... qui ressemble à ceci ...

Compléter le modèle de facture

Nous avons beaucoup appris sur VLOOKUP jusque là. 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 couvert dans les 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. 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 Excel

IF ()

1. et
   

   

   
   ISBLANK () . Nous changeons notre formule de cette ... = VLOOKUP (A11, 'Product Database'! A2: D7,2, FALSE) ... à ceci ... = SI (ISBLANK (A11), ", VLOOKUP (A11, 'Product Database'! A2: D7,2, FALSE)) Nous copierions les formules dans les cellules B11, E11 et F11 jusqu'au reste des rangées 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 remplaçant les références de cellule de la base de données par des références de cellule absolues . Alternativement - et même mieux - nous pourrions créer un
2. nom de plage 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 copiez les formules dans le reste des lignes d'articles de facture. Nous verrouillerait probablement les cellules qui contiennent nos formules (ou plutôt débloqueraient les
3. autres cellules ), puis protégerait la feuille de calcul, afin de s'assurer que nos formules soigneusement élaborées ne sont pas accidentellement écrasé quand quelqu'un vient remplir la facture. Nous enregistrerons le fichier en tant que modèle , afin qu'il puisse être réutilisé par tout le monde dans notre société
4. Si nous nous sentions réellementastucieux, nous créons une base de données de tous nos clients dans une autre feuille de calcul, puis utilisons l'identifiant client entré dans la cellule F5 pour remplir automatiquement le nom et l'adresse du client dans les cellules B6, B7 et B8. aimerait pratiquer avec VLOOKUP, ou simplement voir notre facture T résultante Il peut être téléchargé ici.

Guide ultime de sauvegarde et de restauration du registre Windows

Windows stocke à peu près tout ce qui le fait fonctionner dans une base de données basée sur des fichiers hiérarchiques appelée le Registre Windows. Le registre contient tous les paramètres de configuration du système d'exploitation, des programmes, des services, des composants et à peu près tout le reste. Tout de

(How-to)

Comment pomper le Minecraft Eyecandy avec Shaders

Le style simple et trapu de Minecraft est attachant à beaucoup de fans du jeu, mais si vous souhaitez un look 3D plus sophistiqué avec un éclairage réaliste Les graphismes polis sont une caractéristique importante de la plupart des jeux vidéo modernes et nous nous attendons à des ombres bien rendues, à de belles surfaces et textures, ainsi qu'à d'autres GPU.

(how-to)