Robbie écrit principalement sur Skyrim, mais met également occasionnellement en lumière les chances des applications Microsoft telles qu’Excel et Outlook.
Excel 2007 et 2010 ont un nombre énorme de fonctions disponibles. Aujourd’hui, je vais examiner l’une des fonctions Excel les plus puissantes, VLOOKUP, et comment elle peut être utilisée dans les formules. Ce que VLOOKUP fait essentiellement, c’est qu’il recherche une valeur dans une table de données et renvoie des informations spécifiques liées à cette valeur. Pour illustrer comment exploiter la puissance de RECHERCHEV, nous examinerons quelques exemples :
- Tout d’abord, nous utiliserons VLOOKUP pour récupérer les informations de prix sur les produits de notre catalogue de prix afin que nous puissions déterminer la valeur d’une commande.
- Deuxièmement, VLOOKUP sera utilisé pour compléter le nom et l’adresse d’un utilisateur dans la commande en recherchant l’ID client du client dans notre base de données clients et en renvoyant ses coordonnées.
- Enfin, nous verrons comment utiliser VLOOKUP pour rechercher des données dans un autre classeur.
Exemples d’utilisation de VLOOKUP dans Excel 2007 et 2010
Supposons que vous possédiez un magasin de musique ou un site Web. Vous avez une liste des commandes des clients dans votre carnet de commandes, votre catalogue contenant tous vos prix et une base de données des coordonnées de vos clients.
Chaque client, une fois qu’il a effectué un achat, se voit attribuer un numéro de client et ses coordonnées sont saisies dans la base de données. Lorsqu’une commande arrive, vous enregistrez cette commande dans le carnet de commandes en utilisant le numéro de client dans votre feuille de calcul.
Dans notre premier exemple, nous voulons qu’Excel puisse vous indiquer la valeur d’une commande client. Vous savez que le client veut l’album Green de REM sur CD par exemple. En utilisant RECHERCHEV, Excel 2007 ou 2010 compare votre commande avec votre catalogue et revient avec le prix.
Dans notre deuxième exemple, nous souhaitons qu’Excel récupère automatiquement l’adresse d’un client lorsque nous saisissons son identifiant client dans une commande.
Examinons maintenant chaque exemple plus en détail, avant d’étudier également comment utiliser VLOOKUP pour extraire des données d’un autre classeur.
Comment utiliser VLOOKUP pour récupérer des informations sur les prix des produits dans Excel 2007 et 2010
Dans notre premier exemple, nous allons utiliser VLOOKUP pour récupérer le prix de nos produits (chaque média différent a un coût différent). Dans mon exemple ci-dessous, j’ai les détails des prix sur la droite et les détails de la commande sur la gauche. Si mon magasin de musique était un véritable magasin, il y a de fortes chances que les données se trouvent sur différentes feuilles dans le même classeur ou même dans un autre classeur entièrement, mais je les ai côte à côte pour faciliter la compréhension des formules.
Carnet de commandes et catalogue de prix utilisés par VLOOKUP pour calculer la valeur de la commande dans Excel 2007 et Excel 2010.
http://robbiecwilson.hubpages.com/
La prochaine étape est la formule elle-même. Je vais d’abord montrer la formule finie, puis expliquer chaque partie.
=RECHERCHEV(C14,$F$19:$G$22,2,FAUX)
La formule est composée de quatre parties :
- La première partie indique à Excel ce que je demande à RECHERCHEV de rechercher (dans cet exemple, je recherche un CD, donc je donne à Excel la cellule C14 pour référence).
- La deuxième partie définit la plage de cellules (qui dans ce cas est $F$19 :$G$22) Je demande à Excel de rechercher le contenu de la cellule C14 (CD).
Noter: Le $ à côté de la colonne et de la ligne de la cellule indique à Excel qu’ils ne changent pas d’une formule à l’autre, ils sont toujours constants. Ce qu’Excel utilise toujours votre plage exacte garantit la formule VLOOKUP lorsque vous le copiez dans d’autres cellules de votre classeur.
- La troisième partie est un peu compliquée, alors décomposons la formule pour montrer exactement ce que fait Excel et comment il utilise la troisième partie de la formule :
Faites défiler pour continuer
- Nous avons demandé VLOOKUP pour trouver le contenu de la cellule C14 (partie 1) dans le périmètre $F$19 :$G$22 (partie 2).
- Excel regarde dans la première colonne de la plage (19 $F-22 F$)) et trouve le CD dans la cellule F19.
- Partie 3 indique à Excel de rechercher dans la deuxième ligne lorsqu’il trouve un CD, donc lorsqu’il trouve un CD dans F19il renvoie la valeur de 10 (G19) qui se trouve dans la deuxième rangée et correspond exactement à ce que nous voulions qu’il fasse !
- La dernière partie permet à Excel de savoir si vous recherchez une correspondance exacte (Faux) ou une correspondance approximative (Vrai). Comme je cherche une correspondance exacte, dans ce cas j’ai choisi False.
Noter: Cette partie peut être compliquée et en particulier lors de l’utilisation de True, Excel peut parfois renvoyer des résultats incorrects. J’ai un article qui montre comment utiliser correctement les valeurs true et false afin que VLOOKUP renvoie un résultat correct à chaque fois. Cet article peut être trouvé ici.
Dans l’image ci-dessous, vous pouvez voir la formule complète ainsi que toutes les cellules auxquelles elle fait référence.
Formule VLOOKUP dans Excel 2007 et Excel 2010 recherchant la tarification des médias dans un catalogue de tarification.
http://robbiecwilson.hubpages.com/
Utilisation de VLOOKUP pour compléter l’adresse des clients dans une commande à l’aide de leur numéro de client dans Excel 2007 et 2010
Pour notre prochain exemple, chaque fois qu’un client commande quelque chose dans ma boutique, j’entre son numéro de client dans sa commande et j’aimerais qu’Excel remplisse automatiquement ses noms et adresses à l’aide de VLOOKUP. J’ai tous ces détails stockés dans ma base de données clients et j’aimerais qu’Excel les récupère automatiquement pour moi. J’ai les données configurées comme ci-dessous avec mon carnet de commandes à gauche et la base de données clients à droite.
Carnet de commandes et base de données client utilisés par VLOOKUP pour récupérer les détails du client dans Excel 2007 et Excel 2010.
http://robbiecwilson.hubpages.com/
La formule est configurée exactement de la même manière que dans l’exemple ci-dessus.
=RECHERCHEV(A14,$N$16:$O$25,2,FAUX)
Encore une fois, les quatre étapes sont les mêmes que dans l’exemple précédent.
- 1 (contenu dans la cellule A14) est ce que vous demandez à Excel 2007 ou 2010 de rechercher.
- $N$16 :$O$25 est la plage de cellules dans laquelle vous voulez qu’Excel regarde.
- 2 est la ligne qui contient les données que vous voulez qu’Excel renvoie.
- FAUX indique à Excel de renvoyer des données uniquement s’il trouve une correspondance exacte.
La figure ci-dessous montre la formule et les cellules auxquelles elle fait référence afin que vous puissiez voir VLOOKUP en action.
Formule VLOOKUP dans Excel 2007 et Excel 2010 récupérant les détails du client à partir de la base de données Client.
http://robbiecwilson.hubpages.com/
Utilisation de la fonction VLOOKUP dans une formule lorsque vos données se trouvent dans un autre classeur Excel 2007 ou 2010
Si certaines de vos données auxquelles RECHERCHEV fera référence se trouvent dans un autre classeur (par exemple, ma base de données client peut être gérée par un autre membre de mon équipe et entrée dans un autre classeur), il est assez simple d’ajouter cette plage à un nouveau ou formule existante.
=RECHERCHEV(E14,’C:\[Book1]Sheet1′ !$A$1 :$B$4.2,FALSE)
Le format est :
- Tout d’abord, l’emplacement du fichier (dans mon cas C:\) avec un ‘ devant comme indiqué ci-dessus
- Le nom du fichier dans [] (dans mon cas Book2.xlsx).
- La feuille sur laquelle se trouvent les données (dans mon cas, la feuille 1) suivie d’un ‘!.
- Et enfin la plage de données comme d’habitude.
Il existe une autre façon de procéder que beaucoup trouveront plus facile car vous n’avez pas à modifier les formules.
Ouvrez les deux classeurs Excel 2007 et cliquez sur le Restaurer la fenêtre bouton (comme indiqué ci-dessous) pour les documents plutôt qu’Excel lui-même afin que le classeur n’occupe qu’une partie de la fenêtre globale d’Excel.
Comment empêcher un classeur de prendre la totalité de la fenêtre Excel 2007 ou Excel 2010 pour permettre l’affichage simultané de deux classeurs.
http://robbiecwilson.hubpages.com/
Affichage d’un classeur Excel fenêtré permettant à un autre classeur d’être affiché en même temps dans Excel 2007 et Excel 2010.
http://robbiecwilson.hubpages.com/
Ouvrez le deuxième classeur et placez-le de sorte qu’il soit à côté de l’autre classeur que vous avez ouvert.
Deux classeurs côte à côte dans Excel 2007 et Excel 2010.
http://robbiecwilson.hubpages.com/
Entrez dans la première partie de la formule comme d’habitude, puis lors de la sélection de la plage pour la deuxième partie, sélectionnez les données dans le deuxième classeur comme indiqué ci-dessous.
Illustration d’une formule VLOOKUP à l’aide de deux classeurs dans Excel 2007 et Excel 2010.
http://robbiecwilson.hubpages.com/
Vous remarquerez que le chemin ne semble pas tout à fait correct dans cette formule avec les deux classeurs ouverts.
=RECHERCHEV(E14,[Book1.xlsx]Sheet1 !$A$1 :$B$4.2, FAUX)
Dès que vous fermez le classeur contenant la plage (dans mon cas, le livre 1), la formule se transforme en quelque chose qui semble plus logique.
=RECHERCHEV(E14,’C:\[Book1.xlsx]Sheet1′ !$A$1 :$B$4.2,FALSE)
Excel 2007 et 2010 traitent vos données comme un tableau lorsque vous utilisez VLOOKUP
Une fonctionnalité intéressante d’Excel 2007 et 2010 ainsi que de la fonction VLOOKUP est que lorsque vous utilisez VLOOKUP, Excel convertit vos données en un tableau. Cela ressemble toujours à des données normales, mais lorsque, par exemple, je place une nouvelle commande dans mon classeur à la ligne 24 et que j’entre le numéro de client de la personne qui a passé une nouvelle commande dans la cellule A24, Excel remplit automatiquement les données VLOOKUP (dans mon cas, il entre dans Harry James : 15 Queens Avenue dans B24 à côté des cinq que j’ai entrés dans la cellule A24). Une fois que j’ai entré ce que le client a commandé, Excel remplira automatiquement le coût unitaire et l’entrera dans la cellule F24 pour moi.
Si vous souhaitez rompre ce comportement, laissez une ligne vide, puis continuez à entrer de nouvelles données et Excel ne remplira pas automatiquement les cellules à l’aide de RECHERCHEV (si j’entre 7 dans la cellule A26, Excel ne remplira pas automatiquement car A25 est vide donc il sait que le tableau est rempli).
Et maintenant un quiz pour voir ce que nous avons appris aujourd’hui !
Pour chaque question, choisissez la meilleure réponse. La clé de réponse est ci-dessous.
- Combien de parties y a-t-il dans une formule VLOOKUP ?
- Qu’est-ce qu’un $ devant une référence de cellule indique à Excel ?
- Que les références de cellule ne changent pas lors de la copie
- Que la cellule contient des informations sur la devise
- Que la cellule contient une formule
- Que fait la quatrième partie d’une formule VLOOKUP ?
- Vérifie si la formule renverra une réponse numérique
- Vérifier si la formule contient une erreur
- Indiquez à Excel s’il faut rechercher une correspondance exacte ou approximative
Corrigé
- 4
- Que les références de cellule ne changent pas lors de la copie
- Indiquez à Excel s’il faut rechercher une correspondance exacte ou approximative
Interpréter votre score
Si vous avez 0 bonnes réponses : Oups, n’hésitez pas à relire mon hub et à vérifier vos réponses
Si vous avez 1 bonne réponse : Bravo, vous y êtes presque !
Si vous avez 2 bonnes réponses : Vous avez réussi, sympa !
Si vous avez 3 bonnes réponses : Bravo, premier de la classe, donnez-vous une tape dans le dos virtuelle !
conclusion
VLOOKUP est une formule très puissante qui peut réduire considérablement la quantité de saisie de données et les erreurs qui y sont associées, en recherchant et en récupérant automatiquement des données pour vous. En travaillant pas à pas sur la fonction VLOOKUP dans deux exemples, j’espère que j’ai réussi à vous rendre accessible cette fonction intimidante et que vous pourrez désormais l’utiliser dans vos propres feuilles de calcul et qu’elle vous fera également économiser beaucoup de temps et de frappe !
Cet article est exact et fidèle au meilleur de la connaissance de l’auteur. Le contenu est uniquement à des fins d’information ou de divertissement et ne remplace pas un conseil personnel ou un conseil professionnel en matière commerciale, financière, juridique ou technique.
© 2012 Robbie C Wilson
