Tous les secrets de la fonction Excel VLOOKUP pour rechercher des données dans un tableau et les extraire dans un autre

2024 Auteur: Malcolm Clapton | [email protected]. Dernière modifié: 2023-12-17 03:55

Après avoir lu l'article, vous apprendrez non seulement à rechercher des données dans une feuille de calcul Excel et à les extraire dans une autre, mais également des techniques pouvant être utilisées avec la fonction RECHERCHEV.

Lorsque vous travaillez dans Excel, il est très souvent nécessaire de rechercher des données dans une table et de les extraire dans une autre. Si vous ne savez toujours pas comment procéder, après avoir lu l'article, vous apprendrez non seulement à le faire, mais vous découvrirez également dans quelles conditions vous pouvez tirer le maximum de performances du système. La plupart des techniques très efficaces qui devraient être utilisées en conjonction avec la fonction RECHERCHEV sont prises en compte.

Même si vous utilisez la fonction RECHERCHEV depuis des années, cet article vous sera très probablement utile et ne vous laissera pas indifférent. Par exemple, en tant que spécialiste informatique, puis responsable informatique, j'utilise RECHERCHEV depuis 15 ans, mais je n'ai réussi à gérer toutes les nuances que maintenant, lorsque j'ai commencé à enseigner aux gens Excel à titre professionnel.

RECHERCHEV est l'abréviation de vverticale N.-É.inspection. De même, RECHERCHEV - RECHERCHE verticale. Le nom même de la fonction nous laisse entendre qu'elle recherche dans les lignes du tableau (verticalement - itération sur les lignes et correction de la colonne), et non dans les colonnes (horizontalement - itération sur les colonnes et correction de la ligne). Il est à noter que RECHERCHEV a une sœur - un vilain petit canard qui ne deviendra jamais un cygne - c'est la fonction RECHERCHEH. RECHERCHEH, par opposition à RECHERCHEV, effectue des recherches horizontales, mais le concept d'Excel (et en fait le concept d'organisation des données) implique que vos tableaux ont moins de colonnes et beaucoup plus de lignes. C'est pourquoi nous devons rechercher par lignes plusieurs fois plus souvent que par colonnes. Si vous utilisez trop souvent la fonction HLO dans Excel, il est probable que vous n'ayez pas compris quelque chose dans cette vie.

Syntaxe

La fonction RECHERCHEV a quatre paramètres:

= RECHERCHEV (;; [;]), ici:

- la valeur souhaitée (rarement) ou une référence à une cellule contenant la valeur souhaitée (la grande majorité des cas);

- référence à une plage de cellules (tableau à deux dimensions), dans la PREMIÈRE colonne (!) dont la valeur du paramètre sera recherchée;

- numéro de colonne dans la plage à partir de laquelle la valeur sera renvoyée;

- c'est un paramètre très important qui répond à la question de savoir si la première colonne de la plage est triée par ordre croissant. Si le tableau est trié, nous spécifions la valeur TRUE ou 1, sinon - FALSE ou 0. Si ce paramètre est omis, sa valeur par défaut est 1.

Je parie que beaucoup de ceux qui connaissent la fonction RECHERCHEV comme floue, après avoir lu la description du quatrième paramètre, ils peuvent se sentir mal à l'aise, car ils sont habitués à la voir sous une forme légèrement différente: ils parlent généralement d'une correspondance exacte quand recherche (FALSE ou 0) ou un balayage de plage (TRUE ou 1).

Maintenant, vous devez forcer et lire le paragraphe suivant plusieurs fois jusqu'à ce que vous ressentiez le sens de ce qui a été dit jusqu'à la fin. Chaque mot y est important. Des exemples vous aideront à le comprendre.

Comment fonctionne exactement la formule RECHERCHEV ?

• Type de formule I. Si le dernier paramètre est omis ou spécifié égal à 1, alors RECHERCHEV suppose que la première colonne est triée par ordre croissant, de sorte que la recherche s'arrête à la ligne qui précède immédiatement la ligne contenant la valeur supérieure à celle souhaitée … Si aucune chaîne de ce type n'est trouvée, la dernière ligne de la plage est renvoyée.

  

• Formule II. Si le dernier paramètre est spécifié comme 0, alors le VLOOKUP scanne la première colonne du tableau de manière séquentielle et arrête immédiatement la recherche lorsque la première correspondance exacte avec le paramètre est trouvée, sinon le code d'erreur # N / A (# N / A) est retourné.

  

Workflows de formules

VPR type I

VPR type II

Corollaires pour les formules de la forme I

1. Les formules peuvent être utilisées pour répartir les valeurs entre les plages.
2. Si la première colonne contient des valeurs en double et est triée correctement, la dernière des lignes avec des valeurs en double sera renvoyée.
3. Si vous recherchez une valeur qui est évidemment supérieure à ce que la première colonne peut contenir, vous pouvez facilement trouver la dernière ligne du tableau, ce qui peut être très utile.
4. Cette vue renverra l'erreur # N / A uniquement si elle ne trouve pas une valeur inférieure ou égale à celle souhaitée.
5. Il est assez difficile de comprendre que la formule renvoie les mauvaises valeurs si votre tableau n'est pas trié.

Corollaires pour les formules de type II

Si la valeur souhaitée apparaît plusieurs fois dans la première colonne du tableau, la formule sélectionnera la première ligne pour la récupération de données ultérieure.

Performances de RECHERCHEV

Vous avez atteint le point culminant de l'article. Il semblerait, eh bien, quelle est la différence si je spécifie zéro ou un comme dernier paramètre ? En gros, tout le monde indique bien sûr zéro, puisque c'est assez pratique: vous n'avez pas à vous soucier de trier la première colonne du tableau, vous pouvez immédiatement voir si la valeur a été trouvée ou non. Mais si vous avez plusieurs milliers de formules RECHERCHEV sur votre feuille, vous remarquerez que RECHERCHEV II est lent. En même temps, généralement tout le monde commence à penser:

• J'ai besoin d'un ordinateur plus puissant;
• J'ai besoin d'une formule plus rapide, par exemple, beaucoup de gens connaissent INDEX + MATCH, qui est censé être plus rapide de 5 à 10%.

Et peu de gens pensent que dès que vous commencez à utiliser RECHERCHEV de type I et assurez-vous que la première colonne est triée par quelque moyen que ce soit, la vitesse de RECHERCHEV augmentera 57 fois. J'écris avec des mots - CINQUANTE-SEPT FOIS ! Pas 57 %, mais 5 700 %. J'ai vérifié ce fait de manière assez fiable.

Le secret d'un travail aussi rapide réside dans le fait qu'un algorithme de recherche extrêmement efficace peut être appliqué sur un tableau trié, c'est ce qu'on appelle la recherche binaire (méthode de moitié, méthode de dichotomie). Donc VLOOKUP de type I l'applique, et VLOOKUP de type II recherche sans aucune optimisation. Il en va de même pour la fonction MATCH, qui inclut un paramètre similaire, et la fonction LOOKUP, qui ne fonctionne que sur des tableaux triés et est incluse dans Excel pour la compatibilité avec Lotus 1-2-3.

Inconvénients de la formule

Les inconvénients de RECHERCHEV sont évidents: premièrement, il recherche uniquement dans la première colonne du tableau spécifié, et deuxièmement, uniquement à droite de cette colonne. Et comme vous l'avez compris, il se peut fort bien que la colonne contenant les informations nécessaires soit à gauche de la colonne dans laquelle nous allons chercher. La combinaison déjà mentionnée des formules INDEX + MATCH est dépourvue de cet inconvénient, ce qui en fait la solution la plus flexible pour extraire des données de tableaux par rapport à VLOOKUP (VLOOKUP).

Quelques aspects de l'application de la formule dans la vraie vie

Recherche de gamme

Une illustration classique d'une recherche de gamme est la tâche de déterminer une remise par taille de commande.

Rechercher des chaînes de texte

Bien sûr, RECHERCHEV ne recherche pas seulement des nombres, mais aussi du texte. Il convient de garder à l'esprit que la formule ne fait pas de distinction entre la casse des caractères. Si vous utilisez des caractères génériques, vous pouvez organiser une recherche floue. Il y a deux caractères génériques: " ?" - remplace n'importe quel caractère dans une chaîne de texte, "*" - remplace n'importe quel nombre de n'importe quel caractère.

Espaces de combat

La question est souvent posée de savoir comment résoudre le problème des espaces supplémentaires lors de la recherche. Si la table de recherche peut toujours en être effacée, le premier paramètre de la formule RECHERCHEV ne dépend pas toujours de vous. Par conséquent, si le risque de boucher les cellules avec des espaces supplémentaires est présent, vous pouvez utiliser la fonction TRIM pour l'effacer.

Format de données différent

Si le premier paramètre de la fonction RECHERCHEV fait référence à une cellule qui contient un nombre, mais qui est stockée dans la cellule sous forme de texte, et que la première colonne du tableau contient des nombres au format correct, la recherche échouera. La situation inverse est également possible. Le problème peut être facilement résolu en traduisant le paramètre 1 dans le format requis:

= RECHERCHEV (−− D7; Produits! $ A $ 2: $ C $ 5; 3; 0) - si D7 contient du texte et le tableau contient des nombres;

= RECHERCHEV (D7 & ""); Produits!$ A$2:$C$5; 3; 0) - et vice versa.

À propos, vous pouvez traduire du texte en nombre de plusieurs manières à la fois, choisissez:

• Double négation -D7.
• Multiplication par un D7 * 1.
• Zéro addition D7 + 0.
• Montée à la première puissance D7 ^ 1.

La conversion d'un nombre en texte se fait par concaténation avec une chaîne vide, ce qui oblige Excel à convertir le type de données.

Comment supprimer # N/A

C'est très pratique à faire avec la fonction IFERREUR.

Par exemple: = IFERREUR (RECHERCHEV (D7; Produits! $ A $ 2: $ C $ 5; 3; 0); "").

Si RECHERCHEV renvoie le code d'erreur # N / A, alors IFERREUR l'interceptera et substituera le paramètre 2 (dans ce cas, une chaîne vide), et si aucune erreur ne se produit, alors cette fonction prétendra qu'elle n'existe pas du tout, mais il n'y a que RECHERCHEV qui a renvoyé un résultat normal.

Déployer

Souvent, ils oublient de rendre la référence du tableau absolue et, lors de l'étirement, le tableau "flotte". N'oubliez pas d'utiliser $ A $ 2: $ C $ 5 au lieu de A2: C5.

C'est une bonne idée de placer le tableau de référence sur une feuille séparée du classeur. Il ne se met pas sous les pieds, et ce sera plus sûr.

Une idée encore meilleure serait de déclarer ce tableau en tant que plage nommée.

De nombreux utilisateurs, lorsqu'ils spécifient un tableau, utilisent une construction telle que A:C, spécifiant des colonnes entières. Cette approche a le droit d'exister, car vous n'avez pas à garder une trace du fait que votre tableau comprend toutes les chaînes requises. Si vous ajoutez des lignes à la feuille avec le tableau d'origine, la plage spécifiée comme A: C n'a pas besoin d'être ajustée. Bien sûr, cette construction syntaxique oblige Excel à faire un peu plus de travail que de spécifier exactement la plage, mais cette surcharge peut être négligée. On parle de centièmes de seconde.

Eh bien, au bord du génie - pour organiser le tableau sous la forme.

Utilisation de la fonction COLUMN pour spécifier la colonne à extraire

Si la table dans laquelle vous récupérez les données à l'aide de RECHERCHEV a la même structure que la table de recherche, mais contient simplement moins de lignes, vous pouvez utiliser la fonction COLONNE () dans RECHERCHEV pour calculer automatiquement le nombre de colonnes à récupérer. Dans ce cas, toutes les formules RECHERCHEV seront les mêmes (ajustées pour le premier paramètre, qui change automatiquement) ! Notez que le premier paramètre a une coordonnée de colonne absolue.

Création d'une clé composite avec & "|" &

S'il devient nécessaire de rechercher sur plusieurs colonnes à la fois, alors il est nécessaire de faire une clé composite pour la recherche. Si la valeur de retour n'était pas textuelle (comme c'est le cas avec le champ "Code"), mais numérique, alors la formule SUMIFS plus pratique conviendrait pour cela et la clé de colonne composite ne serait pas du tout requise.

Ceci est mon premier article pour un Lifehacker. Si vous l'avez aimé, je vous invite à visiter et à lire avec plaisir dans les commentaires vos secrets d'utilisation de la fonction RECHERCHEV et autres. Merci.:)