RECHERCHEV dans Excel : voici ce que la fonction peut faire

Application et définition de cette fonction Excel

Le VLOOKUP est une fonction Excel avec laquelle l'utilisateur peut rechercher et évaluer le contenu d'un tableau. Cette fonction est disponible dans les versions à partir d'Excel 2007 pour Windows et Mac.

Qu'est-ce que VLOOKUP ?

Les utilisations possibles de RECHERCHEV sont à expliquer ici à l'aide d'un exemple : Dans celui-ci vous êtes un grand fan de littérature et avez donc créé votre propre tableur Excel dans lequel vous pouvez trier soigneusement les livres que vous avez collectés. Chaque œuvre est saisie avec des informations sur les catégories suivantes :

• auteur

• Titre

• Numéro de page

• Année de parution

Maintenant, vous aimeriez donner à un ami un conseil de livre à emporter avec vous lors de votre prochaine réunion. Malheureusement, vous ne pouvez penser qu'à l'auteur, pas au titre du livre. C'est là que RECHERCHEV entre en jeu, car il peut utiliser cette valeur d'entrée pour rejeter les informations que vous recherchez d'un seul coup.

Comment VLOOKUP est-il utilisé ?

Avant même de penser à formuler des formules, il convient de déterminer où se situeront ultérieurement le champ d'entrée et les différents champs de sortie. Pour ce faire, il est judicieux de créer un tableau séparé qui est initialement vide et laisse ainsi de la place pour les informations mentionnées. Si vous concevez cette nouvelle table en vous basant sur l'exemple de la table existante, vous gagnerez du temps par la suite.

Sur cette base, la formule RECHERCHEV peut être soit créée manuellement, soit générée automatiquement par Excel. Pour les débutants, il vaut la peine d'utiliser cette dernière approche afin de connaître progressivement la structure et l'effet de la formule. Pour ce faire, le bouton « Insérer une fonction » est sélectionné dans l'onglet « Formules ». Le VLOOKUP est caché dans la fenêtre qui s'ouvre. Après confirmation, une fenêtre s'ouvre à nouveau dans laquelle les quatre paramètres de la formule peuvent être renseignés. Ceux-ci sont:

• Critère de recherche

• matrice

• Index de colonne

• Zone_référence

Le brouillon brut de la formule ressemble donc à ceci :

= RECHERCHEV (critère de recherche, matrice, index de colonne, range_link)

et dans une application possible comme celle-ci :

= RECHERCHEV (H3; A3 : E40 ; 5)

Critère de recherche

Pour que la fonction sache quelle valeur doit être utilisée comme point de départ, la ligne qui a été sélectionnée comme champ de saisie deux étapes plus tôt est notée dans le champ "Critère de recherche". Dans notre exemple, le nom de l'auteur du livre « Phillip Pulmann » y est inscrit. Cela rend la formule flexible et n'a pas besoin d'être à nouveau ajustée dès que la valeur entrée change.

matrice

Le champ de saisie "Matrice" décrit le tableau dans lequel se trouvent les informations à sortir. Cette matrice spéciale contient donc également les colonnes pour le titre du livre, le numéro de page et l'année de parution.

La matrice est entièrement sélectionnée une fois sans les en-têtes de la marge supérieure gauche à la marge inférieure droite. De cette façon, Excel sait quel contenu doit être pris en compte lors de l'évaluation.

Index de colonne

Le champ de saisie de l'"index colonne" invite l'utilisateur à définir la colonne de la matrice dans laquelle seule la valeur recherchée est répertoriée. L'affectation des colonnes est numérotée par ordre chronologique. Cela signifie que la première colonne du tableau reçoit la valeur 1, la seconde la valeur 2, etc. Dans notre exemple, cela correspond à l'index de colonne 1 pour l'auteur, à l'index de colonne 2 pour le titre, à l'index de colonne 3 pour le numéro de page et index de colonne 4 pour l'année de publication.

Afin de garder le tableau aussi flexible que possible, l'en-tête de colonne peut être lié à la place du numéro. Cela présente l'avantage que la formule peut également être transférée à d'autres lignes sans aucun problème, car l'en-tête de colonne peut être adapté de manière flexible à chaque fois.

Attention: Le VLOOKUP lit la matrice de gauche à droite, c'est pourquoi l'index de colonne doit être placé à droite de la colonne du critère de recherche afin d'être pris en compte par la fonction !

Zone_référence

Le paramètre "Range_Lookup" complète la formule RECHERCHEV en spécifiant la précision avec laquelle la table est évaluée. Cependant, il diffère des composants mentionnés précédemment de la formule car il est facultatif. Si la valeur 0 est saisie pour "incorrect", Excel recherche uniquement la valeur qui a été spécifiée comme critère de recherche. Avec la valeur 1 pour "vrai", cependant, la recherche se poursuit pour les valeurs évidentes si la valeur exacte n'a pas pu être trouvée.

La spécification de ce paramètre est facultative car la valeur 1 est définie par défaut. Ce paramètre sera utile plus tard dans la RECHERCHEV avancée avec plusieurs critères de recherche.

La fusion

Dès que tous les paramètres nécessaires ont été configurés, RECHERCHEV peut être utilisé. Après avoir saisi le critère de recherche et validé la fonction, la valeur recherchée apparaît dans la ligne qui a été définie comme champ de sortie.

Dans notre exemple, le titre du livre « La boussole d'or » est maintenant affiché, ce qui correspond à l'auteur saisi. Afin de connaître rapidement le numéro de page et l'année de publication, il ne vous reste plus qu'à faire glisser la formule RECHERCHEV existante dans les cellules suivantes. C'est si facile car l'index de colonne de RECHERCHEV a été lié à l'en-tête de colonne du premier tableau et le deuxième tableau est également structuré dans le même ordre.

Dans le cas où les tables devraient différer les unes des autres ou qu'une erreur se produit malgré tout, la formule RECHERCHEV peut également être modifiée manuellement. Pour ce faire, l'avant-dernier chiffre de l'index de colonne doit correspondre à la colonne de la nouvelle valeur à éditer.

RECHERCHEV avec plusieurs critères de recherche

Assez souvent, il arrive qu'un seul critère de recherche ne soit pas suffisant pour évaluer avec précision un grand tableau Excel. Ensuite, il est logique d'exécuter le VLOOKUP avec plusieurs critères de recherche. Pour ce faire, la formule existante doit être complétée par une fonction SI supplémentaire. Ainsi, jusqu'à huit critères de recherche différents peuvent être pris en compte lors de la candidature.

RECHERCHEV dans plusieurs feuilles de calcul Excel

Si le critère de recherche se trouve non seulement dans une table, mais éventuellement aussi dans une autre, la formule RECHERCHEV peut être ajustée en conséquence. Pour cela, une fonction if et une fonction ISERROR doivent être placées devant la formule existante. Cinq paramètres sont nécessaires pour cela :

• Critère de recherche

• Matrix1 et Matrix 2

• Index de colonne1 et index de colonne2

Le résultat ressemble à ceci :

= SI (ESTERREUR (RECHERCHEV (critère de recherche, matrice1, index-colonne1, 0));
RECHERCHEV (critère de recherche; matrice2 ; index de colonne2,0) ; RECHERCHEV (critère de recherche; matrice1 ; index de colonne1 ;))

et dans une application possible comme celle-ci :

= SI (ESTERREUR (RECHERCHEV (E5, A5 : B9,2, 0)), RECHERCHEV (E5, A13 : B17,2, 0), RECHERCHEV (E5, A5 : B9,2, 0))

Le critère de recherche permet d'insérer la valeur à rechercher dans les deux tables. Matrix1 et Matrix2 définissent les zones de cellules respectives des deux tableaux. L'index de colonne1 et l'index de colonne2 sont utilisés pour définir plus en détail quelles colonnes des tables respectives doivent être recherchées.

Si la valeur que vous recherchez apparaît dans les deux tableaux, Excel affichera le résultat du premier tableau. Cependant, si la valeur n'est trouvée dans aucune des deux tables, un message d'erreur s'affiche. L'avantage de la formule est que les deux listes ne doivent pas nécessairement avoir la même structure ou être de la même taille.

Attribuer des valeurs aux catégories à l'aide de RECHERCHEV

Une fonction supplémentaire de RECHERCHEV permet de diviser automatiquement les valeurs répertoriées en lettres et prédicats de votre choix. Dans notre exemple précédent, une colonne de tableau supplémentaire doit être insérée pour le type de livre. Les livres d'une longueur maximale de 50 pages doivent appartenir au genre de la nouvelle, tandis que les livres de 51 à 150 pages sont affectés à la nouvelle et de 151 pages au roman. Pour rendre cela possible, aucune formule supplémentaire n'est requise dans RECHERCHEV, juste l'utilisation d'accolades « {} ». La formule finie ressemble à ceci :

= RECHERCHEV (B1; {1. « Nouvelle »; 51. « Novella »; 151. « Roman »}; 2)

Le contenu des accolades indique une matrice qui définit la zone d'un type de livre respectif. L'attribution de la longueur du côté au genre approprié est donc logée dans les accolades. La formule utilise des paires de valeurs, chacune séparée par un point. La matrice {1. « Nouvelle »; 51. « Novella »; 151. « Roman »} se lit comme suit :

"À partir de 1 spectacle une nouvelle, de 51 spectacle une nouvelle, de 151 spectacle un roman."

Cette matrice peut facilement être adaptée à différentes tâches. Cela concerne d'une part la taille et le nombre des matrices ainsi que leur désignation. Il est donc possible de sortir des chaînes ou des chiffres au lieu de lettres individuelles. Tout ce que vous avez à faire est d'ajuster les lettres dans la formule.

RECHERCHEV sur plusieurs feuilles de calcul

Une autre fonction de RECHERCHEV permet à ses utilisateurs de lier du contenu situé sur différentes feuilles de calcul. Pour notre exemple, cette option peut être utile lorsque les informations sont d'abord triées dans différentes feuilles de calcul, puis mises à jour dans un tableau récapitulatif.

Imaginez qu'en plus de vos livres, vous répertoriez également vos films collectés dans une feuille de calcul Excel. Vous combinez ensuite les deux collections dans une grande table.

L'avantage de cette procédure réside non seulement dans l'ordre accru, mais aussi dans l'évitement d'erreurs potentielles. Si vous souhaitez créer une nouvelle entrée ou mettre à jour une entrée existante, vous n'avez pas besoin de rechercher dans la grande table, mais vous pouvez accéder aux plus petites. Les valeurs sont alors automatiquement transférées dans le tableau Excel de synthèse. Cela rend superflue la réécriture dans la grande table, ce qui évite au mieux un déplacement malheureux et un enchaînement ultérieur de messages d'erreur.

A quoi ressemble la formule ?

Cette fonction est à nouveau rendue possible en insérant une autre formule. Alors que la recherche avec plusieurs critères nécessitait une formule SI supplémentaire, travailler avec plusieurs feuilles de calcul nécessite une formule INDIRECTE. Cela permet de spécifier une plage d'une autre feuille de calcul pour la matrice RECHERCHEV.

= RECHERCHEV (critère de recherche; INDIRECT (matrice ); index de colonne ; lien_plage)

Attention: Cette formule ne fonctionnera que si les tableaux individuels des différentes feuilles portent le même nom que les en-têtes de colonne du tableau général. Des tableaux entiers peuvent être nommés dans le "Champ de nom" en haut à gauche au-dessus de la grille de cellules. Les tables déjà nommées peuvent être visualisées avec la combinaison de touches Ctrl + F3.

Traiter les messages d'erreur émergents

Travailler avec des tableaux Excel liés peut entraîner des problèmes indésirables. Cela inclut en particulier la sortie de valeurs erronées. Dans le cas où la mauvaise valeur 0 est sortie, il y a un petit problème dans les paramètres d'Excel, qui peut être corrigé rapidement.

Le message d'erreur commun #NV, en revanche, est une fonction délibérée de RECHERCHEV, qui indique à l'utilisateur que la valeur requise n'est pas disponible. Cette note peut être conçue différemment à l'aide d'une formule.

RECHERCHEV - un aperçu

RECHERCHEV est une fonction Excel utile qui peut être utilisée pour rechercher et évaluer des tableaux. Ses avantages sont évidents dans son application conviviale et flexible. De cette façon, toute personne qui travaille régulièrement avec des tableaux Excel peut bénéficier de la fonction. Que ce soit le collectionneur privé qui crée ses propres petites tables, ou la grande entreprise qui traite des ensembles de données beaucoup plus volumineux.

Si, en revanche, vous avez toujours des demandes sans réponse auxquelles RECHERCHEV n'a pas pu répondre, vous pouvez vous attendre à une option Excel supplémentaire : Microsoft propose aux utilisateurs d'Excel 365 le nouveau RECHERCHEXX depuis début 2022-2023. Cela s'appuie sur les compétences de RECHERCHEV et les complète avec des fonctions supplémentaires, parfois encore plus simples. Par conséquent, une nouvelle routine d'évaluation des données s'ouvre également à ce stade.