Share
UNE VLOOKUP, raccourci pour "recherche verticale" est une formule dans Microsoft Excel pour faire correspondre les données de deux listes. Au lieu de sauter entre les feuilles de calcul et de taper vos données correspondantes, vous pouvez écrire une formule VLOOKUP pour automatiser le processus..
La combinaison de deux listes est une situation idéale pour utiliser un VLOOKUP. Sur la gauche (dans l'image ci-dessus), nous avons des informations sur les employés. Nous voulons ajouter le titre du poste de l'employé aux données de l'équipe. Avec une liste séparée d'employés et leurs titres d'emploi, nous pouvons écrire une formule VLOOKUP pour extraire le titre d'une liste de recherche.
Un VLOOKUP réussi nécessite trois choses:
VLOOKUP est une formule Microsoft Excel essentielle au travail avec plusieurs ensembles de données. Dans ce tutoriel, je vais vous apprendre à maîtriser et à l'utiliser.
Exemple de formule VLOOKUP utilisée pour rechercher des données d'employé.À l'aide de l'exemple ci-dessus, j'ai maintenant écrit une formule VLOOKUP qui recherche l'ID de l'employé et insère l'intitulé du poste dans les données de l'équipe. Comme les deux feuilles ont un ID d’employé, Excel peut rechercher le titre du travail correspondant. La meilleure partie de VLOOKUP est que je peux maintenant faire glisser la même formule vers le bas pour rechercher chaque titre de travail unique..
Avant de continuer, assurez-vous de Téléchargez la pièce jointe gratuite à suivre. C'est un exemple de classeur que j'ai créé et que nous allons utiliser pour parcourir ce didacticiel..
Pour connaître le moyen le plus rapide d'apprendre les bases de la formule VLOOKUP, consultez la capture d'écran ci-dessous. La vidéo présente plusieurs exemples de la formule VLOOKUP à l'aide de l'exemple de classeur..
Continuez votre lecture pour parcourir les instructions écrites et apprenez quelques techniques supplémentaires qui ne sont pas couvertes dans le screencast..
Utilisez les onglets "Ordre des ingrédients" et "Liste des fournisseurs" pour cet exemple Excel VLOOKUP.
Disons que nous gérons un restaurant et passons nos commandes hebdomadaires aux fournisseurs. Les chefs nous ont donné une liste d'ingrédients à commander et nous avons besoin d'insérer des informations sur le fournisseur.
Il y a trois pièces à ajouter pour chaque commande:
Dans ce classeur, il y a deux onglets:
En haut, nous avons nos données de commande, qui se trouvent sous l'onglet "Commandes d'ingrédients". Vous trouverez ci-dessous la "liste des fournisseurs", qui servira de liste de recherche..Le champ commun entre les deux tables est le Ingrédient languette. Utilisons-le pour rechercher chacun des trois champs et l'ajouter à la liste de commande.
Sur le Commandes d'ingrédients onglet, cliquons dans la première cellule de fournisseur vide, F5, et appuyez sur le signe égal pour démarrer la formule VLOOKUP. Puis tapez " VLOOKUP ( " pour commencer la formule.
= VLOOKUP (
Rappelez-vous que notre clé principale - la donnée apparaissant dans les deux listes - est la ingrédient, Nous allons donc l'utiliser pour la recherche. Soit cliquer sur la cellule B5, ou tapez-le dans la formule. Ensuite, ajoutez une virgule après "B5" pour pouvoir entrer la partie suivante de la formule.
= VLOOKUP (B5,
Maintenant, nous devons donner à la formule notre liste de recherche. La formule étant encore ouverte, cliquez sur le bouton Liste de fournisseurs languette. Maintenant, cliquez sur la cellule A3, puis cliquez et faites glisser pour mettre en surbrillance et sélectionner A3 à G13, l'ensemble de la table de recherche. Assurez-vous et appuyez sur la Touche F4 de votre clavier faire de la formule une référence absolue (pour plus d'informations à ce sujet plus tard). Enfin, entrez une autre virgule.
Pointez Excel vers la liste de recherche.Après avoir entré la virgule pour la cellule de recherche, changez d'onglet et pointez Excel vers la liste de recherche. Cliquez et faites glisser entre les cellules A3 et G3 pour sélectionner les données à rechercher. Assurez-vous et appuyez sur F4 sur votre clavier au cours de cette étape pour créer une référence absolue, qui verrouille les cellules à utiliser pour la recherche.
= VLOOKUP (B5, 'Liste des fournisseurs'!! $ A $ 3: G $ 13,
Ensuite, nous devrons indiquer à Excel de quelle colonne extraire. Rappelez-vous que notre premier article à insérer est le Fournisseur name, qui se trouve dans la deuxième colonne de la liste de recherche. Ajouter le numéro 2 à la formule à tirer de la deuxième colonne de la recherche, et une autre virgule.
= VLOOKUP (B5, 'Liste des fournisseurs'!! $ A $ 3: G $ 13,2,
Enfin, nous allons ajouter FALSE pour une recherche exacte, puis fermer les parenthèses:
= VLOOKUP (B5, 'Liste des fournisseurs'!! $ A $ 3: G $ 13,2 $, FAUX)
Notre formule fonctionne parfaitement, attirant le fournisseur pour les pommes de terre. La bonne nouvelle est qu'il n'est pas nécessaire de réécrire cette même formule en double-cliquant dans le coin inférieur droit de la cellule. F5 (vous pouvez voir la petite boîte verte sur le coin de la cellule) Pour prolonger la formule vers le bas (Comme montré ci-dessus).
La formule fonctionne parfaitement! Bon, passons maintenant à la saisie des données pour les deux autres champs: le numéro de téléphone du fournisseur et le jour de livraison..
Parce que nous avons utilisé une référence absolue, nous pouvons essentiellement réutiliser la même formule que nous avons écrite avec un ajustement mineur. Ajoutons ensuite le numéro de téléphone du fournisseur.
Nous allons commencer par copier et coller une cellule F5 (notre cellule fournisseur) à la cellule G5. J'utilise généralement juste les raccourcis clavier Ctrl + C et Ctrl + V copier et coller la cellule entière.Au début, cela ne fonctionnera pas, et vous verrez un N / A dans la cellule.
Copier et coller une recherche avec des ajustements dans Excel.Il est beaucoup plus facile de copier et coller notre formule dans une autre cellule, mais cela nécessite quelques modifications. Au début, Excel examinera la cellule C5 pour la recherche, mais nous devrons l'ajuster à "B5" dans la barre de formule. Une fois que nous faisons cela, la recherche fonctionnera - presque.
Nous allons devoir aller à la barre de formule, et changer la première partie de la formule de C5 retour à B5. Quand nous avons déménagé le formule par colonne, Excel a mis à jour les autres parties de la formule. Nous obtenions un "N / A" parce qu'Excel essayait de faire correspondre la quantité (colonne C) à notre liste de recherche, mais notre liste de recherche n'inclut pas la quantité..
Jusqu'ici, notre formule devrait donc être:
= VLOOKUP (B5, 'Liste des fournisseurs'!! $ A $ 3: G $ 13,2 $, FAUX)
Cependant, notez dans la capture d'écran ci-dessus que nous extrayons le mauvais bit de données dans la colonne "Téléphone". Nous tirons toujours la deuxième colonne de la liste de recherche avec le "2 " dans la formule. Nous devons changer ceci en numéro de colonne avec le numéro de téléphone de notre fournisseur.
Revenons en arrière et consultez la liste de recherche. Vous remarquerez que le numéro de téléphone se trouve dans la 7ème colonne de la liste de recherche. Revenons à notre formule Excel et mettons à jour la recherche pour extraire de la 7ème colonne.
Nous devons mettre à jour le "2" dans notre formule Excel en un "7" à extraire de la 7ème colonne de notre liste de recherche.Tout ce que nous devons faire est de mettre à jour la section de colonne de notre liste de recherche d'un "2" à un "7" et cela fonctionne maintenant très bien.!
Notez que le téléphone du fournisseur fonctionne parfaitement maintenant que nous avons mis à jour notre formule pour utiliser la 7ème colonne de la liste de recherche. Maintenant, glissez la formule vers le bas pour la mettre à jour pour toutes les cellules.Notre formule finale pour la recherche du numéro de téléphone du fournisseur:
= VLOOKUP (B5, 'Liste de fournisseurs'!! $ A $ 3: G $ 13,7 $, FAUX)
Enfin, travaillons dans la colonne Jour de livraison du fournisseur. Copier et coller une cellule G5 à la cellule H5. Encore une fois, nous devrons corriger notre formule en changeant C5 retour à B5 d'utiliser l'ingrédient comme clé primaire. Ensuite, mettez simplement le "7" à un "5" pour utiliser la 5ème colonne de notre table de correspondance.
Formule de recherche du jour de livraison du fournisseur final appliquée.Notre formule finale pour le jour de livraison du fournisseur:
= VLOOKUP (B5, 'Liste de fournisseurs'!! $ A $ 3: G $ 13,5 $, FAUX)
C'est tout! Nous avons essentiellement écrit une formule et l'avons modifiée pour extraire toutes les données dont nous avons besoin pour passer notre prochaine commande..
Ainsi, vous avez écrit la formule VLOOKUP en suivant les instructions pas à pas, mais cela ne fonctionne toujours pas. Excel prend les choses à la lettre, nous devons donc faire attention aux données et à la formule VLOOKUP. Jetons un coup d'oeil à plusieurs idées pour corriger une formule VLOOKUP qui ne fonctionne tout simplement pas.
L'un des problèmes les plus courants avec VLOOKUP est lié au fait qu'il existe plusieurs correspondances dans votre liste de recherche. Lorsque vous utilisez VLOOKUP, cela correspond au premier élément de la liste..
Notre VLOOKUP dit que Carrie Richard est notre directrice du marketing, mais nous savons qu'elle a récemment été promue au poste de présidente de la société. Pourquoi notre recherche ne fonctionne-t-elle pas??
Oups, on dirait qu'il y avait deux entrées pour Carrie Richard dans la liste de recherche - une pour la directrice du marketing et une pour le président. Une fois que nous avons supprimé la ligne "Directeur du marketing" de la liste de recherche, nos données sont correctes..Le but de VLOOKUP est de rechercher un élément correspondant dans une liste. La liste de recherche ne doit pas contenir de doublons pour le clé primaire, l'élément que vous utilisez pour correspondre. Sinon, Excel est confus et ne vous montre que le premier match.
Un autre problème courant est que nos données risquent de ne pas correspondre aux yeux d'Excel. Les données avec un espace avant sont censées avoir un espace "en tête", tandis que les données avec un espace après un "espace de fuite".
Ce VLOOKUP est parfaitement écrit, mais il ne fonctionne pas. Vous pouvez clairement voir que le nom est dans la liste de recherche, mais Excel ne renvoie pas de correspondance. Lisez la suite pour savoir pourquoi.Si vos données correspondantes ont un espace avant ou après, Excel considère que ces deux données sont totalement différentes et ne renverra pas de correspondance. Excel considère "_Andrew", "Andrew_" et "Andrew" comme trois données uniques qui ne seront pas comparées dans VLOOKUP.
Il s'avère qu'il y a un "espace de fin" ou un seul espace après le nom dans la cellule. C'est impossible à voir, mais cela peut casser un VLOOKUP, car Excel traite différemment "Alyssa Reddall" et "Alyssa Reddall (espace)". Une fois l’espace supprimé, le VLOOKUP fonctionnera normalement..Si vos données comportent des espaces de début ou de fin, utilisez la commande RÉDUIRE fonction dans Excel pour le nettoyer. VLOOKUP devrait très bien fonctionner après son utilisation pour supprimer les espaces de début et de fin.
Comme vous le savez peut-être, vous pouvez faire glisser une cellule vers le bas de la poignée pour coller la formule dans d'autres cellules. Cependant, cela rompt parfois notre VLOOKUP car les références de cellules changent.
Dans cette capture d'écran, j'ai déplacé VLOOKUP dans la liste pour l'appliquer à d'autres cellules. Notez toutefois que la référence de recherche est passée de A2 à B15, de A16 à B29, d'où la raison pour laquelle la formule ne fonctionne pas. Ma liste de recherche est actuellement dans A2 à B15, donc la recherche est maintenant cassée.Au fur et à mesure que vous tirez les formules, la référence de la table de recherche n'est plus alignée. Soudain, il manque des lignes à la table de recherche et notre VLOOKUP ne fonctionne pas.
La solution est de faire de la formule un référence absolue, de sorte que lorsque vous faites glisser la formule vers le bas, la liste vers laquelle elle pointe ne change pas. Cliquez dans la cellule où vous avez écrit votre VLOOKUP, puis cliquez quelque part dans la référence de la liste de recherche. Puis appuyez F4 . Vous remarquerez que la formule change pour inclure les signes dollar.
Notez que la formule (affichée en haut de cette capture d'écran dans la barre de formule) inclut désormais les signes dollar dans la référence de la liste de recherche, ce qui montre qu'elle utilise un référence absolue. Maintenant, lorsque je fais glisser la formule vers le bas, il verrouille la formule pour la liste de recherche et fonctionne parfaitement.VLOOKUP est l'une de ces formules essentielles pour être un utilisateur Excel productif. Il n’ya tout simplement pas assez de temps pour rechercher manuellement des données et les ressaisir encore et encore. Il est donc important d’apprendre des formules telles que VLOOKUP..
Si vous rencontrez des problèmes lors de l'utilisation de la formule VLOOKUP, n'hésitez pas à laisser un commentaire ci-dessous pour le dépannage.
Accentsconagua