Excel VLOOKUP est puissant, mais il ne fonctionnera pas lorsque vous avez des situations spécifiques. Aujourd'hui, découvrez comment dépanner RECHERCHEV.
RECHERCHEV est ma fonction préférée dans Excel. Si vous pouvez faire RECHERCHEV, vous pouvez résoudre de nombreux problèmes dans Excel. Mais il y a des choses qui peuvent déclencher une RECHERCHEV. Ce sujet en parle de quelques-uns.
Mais d'abord, les bases de RECHERCHEV en anglais simple.
Les données en A: C provenaient du service informatique. Vous avez demandé des ventes par article et par date. Ils vous ont donné le numéro d'article. Vous avez besoin d'une description de l'article. Plutôt que d'attendre que le service informatique réexécute les données, vous trouvez le tableau figurant dans la colonne F: G.
Vous voulez VLOOKUP pour trouver l'élément dans A2 pendant qu'il recherche dans la première colonne du tableau dans $ F $ 3: $ G $ 30. Lorsque VLOOKUP trouve la correspondance dans F7, vous voulez VLOOKUP pour renvoyer la description trouvée dans la deuxième colonne du tableau. Chaque VLOOKUP qui recherche une correspondance exacte doit se terminer par False (ou zéro, ce qui équivaut à False). La formule ci-dessous est correctement configurée.
Notez que vous utilisez F4 pour ajouter quatre signes dollar à l'adresse de la table de recherche. Lorsque vous copiez la formule dans la colonne D, vous avez besoin que l'adresse de la table de recherche reste constante. Il existe deux alternatives courantes: Vous pouvez spécifier les colonnes entières F: G comme table de recherche. Ou, vous pouvez nommer F3: G30 avec un nom tel que ItemTable. Si vous utilisez =VLOOKUP(A2,ItemTable,2,False), la plage nommée agit comme une référence absolue.
Chaque fois que vous effectuez un tas de RECHERCHEV, vous devez trier la colonne de RECHERCHEV. Triez ZA, et toute erreur # N / A viendra en haut. Dans ce cas, il y en a un. L'élément BG33-9 est absent de la table de recherche. C'est peut-être une faute de frappe. C'est peut-être un tout nouvel article. S'il est nouveau, insérez une nouvelle ligne n'importe où au milieu de votre table de recherche et ajoutez le nouvel élément.
Il est assez normal d'avoir quelques erreurs # N / A. Mais dans la figure ci-dessous, exactement la même formule ne renvoie rien d'autre que # N / A. Lorsque cela se produit, je vois si je peux résoudre le premier RECHERCHEV. Vous recherchez le BG33-8 trouvé dans A2. Commencez à parcourir la première colonne de la table de recherche. Comme vous pouvez le voir, la valeur correspondante est clairement en F10. Pourquoi voyez-vous cela, mais Excel ne peut pas le voir?
Accédez à chaque cellule et appuyez sur la touche F2. Voici F10. Notez que le curseur d'insertion apparaît juste après le 8.
Voici la cellule A2 en mode Edition. Le curseur d'insertion est à quelques espaces du 8. C'est un signe qu'à un moment donné, ces données ont été stockées dans un ancien jeu de données COBOL. De retour en COBOL, si le champ Élément était défini comme 10 caractères et que vous n'en tapiez que 6 caractères, COBOL le remplirait de 4 espaces supplémentaires.
La solution? Au lieu de rechercher A2, recherchez le fichier TRIM(A2).
La fonction TRIM () supprime les espaces de début et de fin. Si vous avez plusieurs espaces entre les mots, TRIM les convertira en un seul espace. Dans la figure ci-dessous, il y a des espaces avant et après les deux noms dans A1. =TRIM(A1)supprime tous les espaces sauf un dans A3.
Au fait, que se passerait-il si le problème avait été de laisser des espaces dans la colonne F au lieu de la colonne A? Ajoutez une colonne de fonctions TRIM () à E, pointant vers la colonne F. Copiez-les et collez-les en tant que valeurs dans F pour que les recherches recommencent à fonctionner.
L'autre raison très courante pour laquelle VLOOKUP ne fonctionnera pas est indiquée ici. La colonne F a des nombres réels. La colonne A contient du texte qui ressemble à des nombres.
Sélectionnez toute la colonne A. Appuyez sur alt = "" + D, E, F. Cela fait un texte par défaut en colonnes et convertira tous les nombres de texte en nombres réels. La recherche recommence à fonctionner.
Regarder la vidéo
- RECHERCHEV résout de nombreux problèmes
- Problèmes courants de RECHERCHEV:
- Si VLOOKUP commence à fonctionner, mais # N / A devient plus important: $ oublié dans la table de recherche
- Quelques # N / A: éléments manquants dans le tableau
- Aucun des travaux de RECHERCHEV: vérifiez les espaces de fin
- Supprimer les espaces de fin avec TRIM
- Nombres et nombres stockés sous forme de texte
- Sélectionnez les deux colonnes et utilisez alt = "" + DEF
- L'épisode comprend une blague que les comptables et les informaticiens trouvent drôle, mais pour des raisons différentes
Transcription vidéo
Apprenez Excel à partir du podcast, épisode 2027 - Dépannage de VLOOKUP!
Très bien, podcastez tout ce livre, cliquez sur le «i» dans le coin supérieur droit pour accéder à la playlist!
RECHERCHEV Est ma fonction préférée dans tout Excel, et je raconte toujours cette blague dans l'un de mes séminaires, et ça fait rire que vous soyez informaticien ou non informaticien. Je dis toujours: «Eh bien, regardez, nous avons cet ensemble de données ici à gauche, et je peux regarder ces données et dire que les données proviennent du service informatique parce que c'est exactement ce que j'ai demandé, mais pas vraiment ce dont j'avais besoin. J'ai demandé la date et la quantité de l'article, et ils m'ont donné la date et la quantité du numéro d'article, mais ils n'ont pas pris la peine de me donner une description, n'est-ce pas? " Et les comptables se moquent toujours de ça, parce que ça leur arrive tout le temps, et les informaticiens sont comme "Eh bien, je vous ai donné ce que vous avez demandé, ce n'est pas ma faute!" Donc, ils pensent tous les deux que c'est drôle pour différentes raisons donc, vous savez, et le responsable informatique est occupé, il ne peut pas revenir pour réécrire la requête,nous devons donc faire quelque chose pour sauver cela nous-mêmes.
Et donc ce petit tableau que j'ai copié ailleurs dans mon ordinateur, en anglais simple, ce que fait RECHERCHEV est dit "Hé, nous avons un article numéro W25-6." Nous avons un tableau ici, je veux parcourir la première colonne du tableau jusqu'à ce que je trouve ce numéro d'article, puis renvoyer quelque chose de cette ligne. Très bien, dans ce cas, ce que je veux, c'est la deuxième colonne de cette ligne. Et puis à la fin de chaque RECHERCHEV, nous devons mettre FALSE ou 0. Maintenant, ici, après avoir choisi la plage, je vais appuyer sur la touche F4, puis je veux la 2ème colonne, puis FALSE pour un exact rencontre. Ne choisissez jamais une correspondance approximative, jamais, jamais! Ce n'est pas une correspondance approximative, c'est une chose très spéciale, cela ne fonctionne pas tout le temps. Si vous souhaitez reformuler vos chiffres à la Securities and Exchange Commission, n'hésitez pas à utiliser,TRUE ou laissez simplement le, FALSE désactivé. Mais chaque RECHERCHEV que vous créez doit se terminer par, FALSE ou, 0, 0 est plus court que FALSE, vous êtes censé y mettre un FALSE, mais un 0 est la même chose que FALSE.
Très bien maintenant, dépannage, première chose, quand vous faites un tas de RECHERCHEV, il est très normal d'avoir quelques # N / As, non? Et je trouve toujours les # N / A en allant Data, ZA, qui amène le # N / As en haut, juste là, BG33-9, soit c'est une faute de frappe, soit c'est un tout nouvel élément, d'accord, et nous avons Comprendre. Donc ici à droite, j'ai les nouvelles données, je vais couper cela, puis Alt + IED, insérer ces cellules au milieu, cela n'a pas à être alphabétique, ce tableau n'a pas besoin d'être trié. Lorsque vous utilisez la version FALSE, le tableau n'est pas - vous pouvez simplement le mettre où vous voulez, je n'ai pas mis à la fin parce que je n'avais pas à réécrire la formule, je veux juste que la formule travail. Très bien, donc quelques # N / A, extrêmement, extrêmement normaux, mais vérifiez ceci.
Lorsque vous commencez avec des réponses qui fonctionnent, mais que les # N / A commencent à apparaître légèrement fréquemment, puis finissent par apparaître tout en bas, c'est un signe certain que vous n'avez pas verrouillé la référence de table. Très bien, le tableau se déplace alors que je copie la formule vers le bas, à droite, et j'ai donc de la chance en en frappant quelques-uns qui étaient à la fin de la liste. Mais finalement j'arrive au point où il regarde ici dans des cellules complètement vides, et bien sûr rien n'est trouvé. D'accord, alors c'est la première chose, vous obtenez un couple qui fonctionne, quelques # N / A's, quelques autres qui fonctionnent, et puis tout # N / A est le reste du chemin - signe certain que vous n'avez pas mis le $ en.
Revenez simplement en arrière, F2 pour le mode d'édition, sélectionnez les deux points, appuyez sur F4, cela met tout le $ dedans, double-cliquez pour abattre cela, et les choses recommencent à fonctionner, d'accord. Ensuite, exactement la même formule, la formule est parfaite, BG33-8 voyez, nous ne faisons rien de tout cela. Bon, alors je vais chercher, BG33-8, hé, ça y est, c'est juste là, c'est en rouge, j'aurais dû le voir! Alors j'arrive à celui-ci sur le côté droit, j'appuie sur F2, et je regarde le point d'insertion clignotant, et vois, c'est juste après le 8, comme ça, et puis je viens ici et j'appuie sur F2, il y a quelques espaces de fin là-bas. C'est très, très courant à l'époque de COBOL, ils disaient: «Vous savez, regardez, nous allons vous donner 10 espaces pour le numéro d'article, et si vous ne tapez pas les 10 espaces, ils rempliront les espaces . » Et c'est donc très courant,et la grande solution ici est de se débarrasser de ces espaces de début et de fin avec la fonction TRIM. Au lieu d'A2, utilisez le TRIM (A2), double-cliquez pour l'abattre, d'accord, le BG33-9 est toujours de retour dans l'autre table.
D'accord, juste pour que vous compreniez comment fonctionne TRIM, alors j'ai tapé un tas d'espaces, John, un tas d'espaces, Durran et un tas d'espaces, puis j'ai concaténé un * avant et après pour que vous puissiez voir à quoi il ressemble . Lorsque je demande le TRIM (P4), nous nous débarrassons de tous les espaces de tête, de tous les espaces de fin, puis les multiples espaces intérieurs sont réduits à un seul espace. D'accord, donc vous pouvez voir, en quelque sorte visualiser là-bas, qu'ils se débarrassent des espaces de début et de fin, tous les espaces doublés du milieu deviennent un espace unique comme ça. Alors TRIM, super, excellent outil dans votre arsenal, d'accord, en voici un autre très courant.
Si ce ne sont pas les espaces de fin, alors la chose la plus courante que j'ai vue, c'est où ici nous avons de vrais nombres, et ici nous avons des nombres stockés sous forme de texte. Et RECHERCHEV ne verra pas cela comme une correspondance, même si 4399, c'est un nombre, c'est du texte, ne fonctionne pas. Le moyen le plus rapide de convertir une colonne de texte en nombres, sélectionnez la colonne, 3 lettres dans l'ordre, alt = "" DEF, et tout d'un coup, nos RECHERCHEV recommencent à fonctionner, excellent, excellent conseil d'il y a environ 1500 podcasts. D'accord, ce sont donc les problèmes les plus courants de RECHERCHEV, soit vous avez oublié le $, soit vous avez des espaces à la fin, soit vous avez des nombres et des nombres stockés sous forme de texte. Tous ces conseils sont dans ce livre «MrExcel XL», cliquez sur «i» dans le coin supérieur droit, vous pouvez acheter le livre.
Bon, récapitulation rapide: RECHERCHEV résout de nombreux problèmes, si une RECHERCHEV commence à fonctionner mais # N / A! devient plus important, vous avez oublié de mettre le $ dans la table de recherche. S'il y a quelques # N / A, ce ne sont que des éléments manquants dans le tableau. Si aucun des VLOOKUP ne fonctionne et que c'est du texte, vérifiez les espaces de fin, vous pouvez utiliser la fonction TRIM. Si vous avez des nombres et des nombres stockés sous forme de texte, sélectionnez l'une ou l'autre des colonnes, celle qui contient le texte, puis faites alt = "" DEF pour tous les retourner aux nombres.
Très bien hey, je tiens à vous remercier d'être passé, nous vous reverrons la prochaine fois pour une autre diffusion sur le net!
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2027.xlsx
