Beaucoup de gens le connaissent et l’utilisent souvent. Mais en même temps, il présente deux inconvénients importants, par exemple, comment créer une « RECHERCHEV gauche ». Je n'utilise RECHERCHEV moi-même que si j'ai besoin de faire quelque chose rapidement. Dans les fichiers à usage « régulier », je réalise les constructions INDEX et SEARCH. Comment ça va mieux ?

Lors de l'ajout d'une colonne à une table de données, vous n'avez pas besoin de modifier le numéro de colonne dans la formule elle-même (comme dans une RECHERCHEV). La colonne se déplacera automatiquement
Vous pouvez faire une RECHERCHEV dans l'autre sens, c'est-à-dire faites un choix dans le tableau de droite à gauche. Dans une RECHERCHEV, la première colonne doit toujours être consultable.

Comment faire tout cela, lisez ci-dessous :)

Je vais utiliser un exemple de. Afin de mieux comprendre l'application de cette conception par rapport à RECHERCHEV :

Est-il facile de remplir si vous voyez la formule pour la première fois ?

Tout d’abord, décidez où se trouve la table initiale et où recevoir les données ! Étape par étape, que remplir.

Insérez la formule dans la zone souhaitée du tableau
À la place de $G:$G, placez les cellules dans lesquelles la valeur doit être trouvée et, par conséquent, doit apparaître comme résultat. Nous recherchons un nom de famille, recherchez donc la colonne avec les noms de famille dans le tableau initial.
Remplacez $J:$J par en fonction des valeurs qui doivent être renvoyées à la cellule. Nous avons besoin de noms de famille en fonction du véhicule - insérez les véhicules à côté desquels les valeurs doivent apparaître.
Au lieu de $H:$H, remplissez la colonne dans laquelle vous devez trouver la valeur correspondante. Ceux. Nous recherchons Nom de famille par véhicule, ce qui signifie que nous insérons une colonne avec Véhicule dans le tableau d'origine.

Si vous vous demandez à quoi servent les signes $ dans la formule, lisez

INDEX et RECHERCHE. Quelles sont ces fonctions ?

INDEX et MATCH sont des fonctions très puissantes qui, en combinaison avec d'autres, donnent d'excellents résultats.

INDEX(tableau; numéro_ligne; numéro_colonne)

Renvoie la valeur à l'intersection d'une ligne spécifiée et d'une colonne dans une plage spécifiée. Ceux. il fonctionne initialement avec des tableaux bidimensionnels.

En sélectionnant le tableau de données J1: K4 et en définissant les numéros de ligne et de colonne sur deux, nous avons obtenu la valeur correspondante.

En regardant la formule originale

INDICE($G:$G,MATCH($J:$J,$H:$H,0),1)

Nous verrons qu'à la place du deuxième argument (numéro de ligne) nous avons la formule MATCH. Qu'est ce qu'elle fait ici?

MATCH est une recherche par valeur. La fonction recherche une valeur donnée dans une ligne ou une colonne et renvoie son numéro ordinal (à partir du début de la plage). Ceux. dans le deuxième argument de la fonction INDEX on trouve le numéro de véhicule dont nous avons besoin, on obtient son numéro, par exemple 2.

Et déjà dans le tableau unidimensionnel $G:$G, nous trouvons une cellule avec un numéro de ligne = 2. Cela fonctionnera pour chaque cellule de la colonne J.

Il semble donc que ce ne soit pas une conception très compliquée, mais comme je l’ai écrit ci-dessus, elle est très efficace. Parce que vous n'avez pas besoin de changer constamment le numéro de la valeur souhaitée, comme dans RECHERCHEV, et vous pouvez rechercher à droite et à gauche :)

Écrivez des commentaires si vous avez des questions.

Comme toujours!

Partagez notre article sur vos réseaux sociaux :

Les tableaux créés dans Excel ne se caractérisent pas toujours par le fait que les noms des catégories de données doivent être définis uniquement dans les en-têtes de colonnes. Parfois, lors de l'analyse des données d'un tableau, nous avons la possibilité d'utiliser à la fois les en-têtes de colonnes et les noms de lignes qui se trouvent dans la première colonne.

Exemple de formule avec RECHERCHEV et MATCH

Un exemple de tableau de rapport de bonus est présenté ci-dessous dans la figure :

Le but de ce tableau est de trouver des valeurs de prime appropriées dans la fourchette B5:K11 en fonction d'un certain montant de revenus et de magasins avec un minimum ou tailles maximales versements de primes. La difficulté surgit lorsque détection automatique le montant de la prime sur laquelle un employé peut compter lorsqu'il dépasse une certaine limite de revenus. Puisqu’il n’existe pas de montant de prime clairement défini pour chaque montant probable de revenus. Il n'y a que des limites inférieures et supérieures sur les montants des primes pour chaque magasin.

Par exemple, nous avons besoin que le programme détermine automatiquement la prime minimale possible pour un vendeur du 3ème magasin dont les revenus ont dépassé le niveau de 370 000.

Pour ça:

Dans la cellule B14, saisissez le montant des revenus : 370 000.
Dans la cellule B15, saisissez le numéro du magasin : 3.
Dans la cellule B16, saisissez la formule suivante :

En conséquence, la limite inférieure de la prime a été déterminée pour le magasin n°3 dont le chiffre d'affaires est supérieur à >370 000, mais inférieur à<400 000.

Bonjour cher lecteur !

Dans cet article, je souhaite décrire probablement l'une des fonctions les plus utiles d'Excel : la fonction RECHERCHEV. On peut en dire beaucoup sur la fonction, mais la toute première chose à noter est que la fonction est l’une des plus complexes et des moins comprises.

Dans cet article, je vais essayer de décrire dans un langage simple et accessible le fonctionnement de la fonction RECHERCHEV, ainsi que de montrer ses fonctionnalités, sa description et sa syntaxe à l'aide d'exemples.

Alors, qu’est-ce que cette fonction exactement, que fait-elle et comment est-elle écrite. L'explication la plus simple du fonctionnement de la fonction RECHERCHEV est qu'elle recherche dans la liste de valeurs, selon le critère que vous précisez, un identifiant unique, certaines informations qui lui sont associées.

Si vous déchiffrez le nom de la fonction RECHERCHEV, alors avec la première lettre, vous comprendrez comment cette fonction fonctionne, B - signifie "verticale", c'est-à-dire qu'il recherche des valeurs dans , mais pour les listes horizontales, nous aurons . La fonction RECHERCHEV est devenue disponible pour la première fois avec Excel 2000.
La fonction RECHERCHEV dans Excel a la syntaxe suivante :

=RECHERCHEV(_valeur de recherche_;_table_; _numéro de colonne_; _[vue par intervalle]_), Où:

valeur_recherche– c'est exactement la valeur que nous devons rechercher, et cela peut être n'importe quelle valeur : un nombre, une date, un texte, un lien vers une cellule qui contient la valeur souhaitée ou une valeur obtenue par une autre formule ;
tableau– il s'agit de deux ou plusieurs colonnes avec des données diverses, d'ailleurs la casse des caractères dans la recherche n'est pas prise en compte par la fonction ;
numéro_colonne– il s'agit du numéro de la colonne dans la plage spécifiée à partir de laquelle la valeur qui se trouve dans la ligne trouvée sera obtenue ;
vue_intervalle– ce paramètre détermine exactement ce que nous chercherons ; pour une correspondance exacte, l’argument sera égal à "FAUX" ou une correspondance approximative, l'argument devient égal à VRAI. Ce paramètre est facultatif, mais néanmoins important. Dans les exemples ci-dessous, je vais montrer comment créer des formules de correspondance exactes et approximatives.

Maintenant, à l'aide d'un exemple, voyons comment rechercher des données sur une autre feuille, car en pratique, la fonction RECHERCHEV est très rarement utilisée pour rechercher sur la feuille actuelle :

RECHERCHEV("GM";$A$5:$B$10;2)

La formule recherche le texte « GM » dans la colonne A de la feuille actuelle.
Conseil! Lors de l'utilisation de l'argument « table », il est conseillé d'utiliser une option telle que (il s'agit de l'adresse de la cellule avec le signe $). Dans ce cas, la plage de recherche deviendra fixe et ne changera pas lors de la copie de la formule.

Regardons un exemple de recherche de valeurs, comment fonctionne la fonction RECHERCHEV dans un autre classeur :

RECHERCHEV("GM";[Chemin du fichier]Base!A2:B10;2)

Comme vous pouvez le constater, il n'y a rien de compliqué, l'argumentation est juste devenue plus compliquée "tableau", il contient désormais un lien vers un fichier auquel son nom est attaché.

Conseil! Il est préférable de saisir la formule avec deux fichiers activés, sur l'un vous entrez la fonction RECHERCHEV, et lorsque vous arrivez à l'argument « table », accédez au deuxième fichier et utilisez la souris pour sélectionner la plage que vous devez rechercher.

Vous pouvez également considérer un exemple où nous ne connaissons pas la valeur exacte : nous utiliserons des caractères génériques dans la formule RECHERCHEV. Parfois, nous avons besoin de trouver non pas quelque chose de spécifique, mais quelque chose avec des caractéristiques communes ; alternativement, nous avons de nombreux canapés de différents modèles, ainsi que d'autres meubles, mais il suffit de les choisir.

La fonction RECHERCHEV, lors de la substitution, peut utiliser les caractères suivants :

"?" (point d'interrogation) – vous permettra de remplacer n’importe quel caractère ;
"*" (astérisque)– remplacera n’importe quel nombre et séquence de caractères.

RECHERCHEV("A*",$A$2 :$B$10,1,FALSE)

Conseil! Pour que la fonction RECHERCHEV fonctionne correctement, vous devez utiliser le paramètre « FALSE » comme quatrième argument.
Eh bien, si nous avons déjà abordé le sujet de la correspondance exacte ou approximative dans la syntaxe de la fonction RECHERCHEV, examinons-le plus en détail :

si argument "visualisation accélérée"équivaut à "MENSONGE", dans ce cas, la formule recherche une correspondance exacte de l'argument « valeur de recherche ». Si la formule rencontre deux valeurs ou plus qui correspondent à l'argument « valeur de recherche », la première de la liste sera sélectionnée ; si aucune correspondance n'est trouvée, la formule retournera ;
si l'argument "visualisation accélérée" a le sens "VRAI" la formule recherchera une correspondance approximative, ou plus précisément, la fonction recherchera d'abord une correspondance exacte, et ensuite seulement, ne la trouvant pas, en sélectionnera une approximative.

Conseil!Dans le cas où l'argument « intervalle de balayage » est égal à « VRAI » ou n'est pas spécifié, la valeur dans la première colonne est nécessaire, de la plus petite à la plus grande. Sinon, il existe une possibilité d'un résultat erroné de la fonction RECHERCHEV.

Voyons comment fonctionne la fonction RECHERCHEV lors d'une recherche exacte. Par exemple, essayons de trouver quelle voiture roule à une vitesse de 200 km/h. Je pense que cette formule ne sera pas difficile pour vous :

=RECHERCHEV(200,$A$2 :$B$15,2,FALSE)

Malgré le fait que nous ayons plusieurs valeurs de 200, nous n'en avons reçu qu'une seule, car si la fonction RECHERCHEV correspond exactement, le système utilise uniquement la première valeur trouvée dans la plage spécifiée.
Testons maintenant le fonctionnement de la fonction RECHERCHEV pour une correspondance approximative des valeurs. Découvrons quelle voiture roule à une vitesse de 260 km/h. La première chose que tu fais quand "visualisation accélérée"équivaut à "VRAI"— vous triez votre plage de valeurs par la première colonne par ordre croissant. Ceci est nécessaire et important car la fonction RECHERCHEV trouve la valeur la plus élevée suivante à partir d'une condition donnée, puis la recherche s'arrête. Si vous ne suivez pas les conseils de tri, le résultat sera un message d'erreur ou d'autres résultats étranges.

Pour effectuer une recherche, nous utilisons une RECHERCHEV de la forme suivante :

=RECHERCHEV(260,$A$2 :$B$15,2,VRAI)

Ainsi, notre formule a renvoyé la valeur trouvée de 240, bien que nous ayons également une valeur de 270 et que cette valeur semble être plus proche, mais la particularité du fonctionnement de la fonction RECHERCHEV est que lors de l'utilisation d'une correspondance exacte, elle recherche le plus grand valeur dans la liste, mais ne dépassant pas la sienne.
Je pense que les exemples dont j'ai parlé dans l'article vous aideront à tout comprendre sur la fonction RECHERCHEV, comment elle est utilisée et à quoi elle doit servir.

Bien qu'il existe de nombreux exemples plus avancés, que nous examinerons dans d'autres articles.

Et c'est tout pour moi ! J'espère vraiment que tout ce qui précède est clair pour vous. Je serais très reconnaissant pour vos commentaires, car ils sont un indicateur de lisibilité et m'inspirent pour écrire de nouveaux articles ! Partagez ce que vous lisez avec vos amis et aimez-le !

Aujourd'hui, nous commençons une série d'articles décrivant l'une des fonctions les plus utiles d'Excel - RECHERCHEV(RECHERCHEV). Cette fonction est à la fois l’une des plus complexes et des moins comprises.

Dans ce tutoriel sur RECHERCHEV J'essaierai de présenter les bases dans un langage aussi simple que possible afin de rendre le processus d'apprentissage aussi clair que possible pour les utilisateurs inexpérimentés. De plus, nous étudierons plusieurs exemples avec des formules Excel qui démontreront les utilisations les plus courantes de la fonction RECHERCHEV.

Fonction RECHERCHEV dans Excel - description générale et syntaxe

Alors c'est quoi RECHERCHEV? Eh bien, tout d’abord, c’est une fonction Excel. Que fait-elle? Il recherche la valeur que vous spécifiez et renvoie la valeur correspondante d'une autre colonne. En termes techniques, RECHERCHEV recherche la valeur dans la première colonne d'une plage donnée et renvoie le résultat d'une autre colonne de la même ligne.

Dans l'application la plus courante, la fonction RECHERCHEV recherche dans la base de données un identifiant unique donné et récupère certaines informations qui s'y rapportent dans la base de données.

Première lettre du nom de la fonction RECHERCHEV(RECHERCHEV) signifie DANS verticale ( V verticale). Grâce à cela, vous pouvez distinguer RECHERCHEV depuis GPR(HLOOKUP), qui recherche la valeur dans la ligne supérieure de la plage - g horizontal ( H horizontal).

Fonction RECHERCHEV Disponible dans Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP et Excel 2000.

Syntaxe de la fonction RECHERCHEV

Fonction RECHERCHEV(RECHERCHEV) a la syntaxe suivante :

RECHERCHEV (valeur_recherche, tableau_table, col_index_num,)
RECHERCHEV (valeur_recherche, table, numéro_colonne, [recherche_intervalle])

Comme vous pouvez le constater, la fonction RECHERCHEV dans Microsoft Excel, il y a 4 paramètres (ou arguments). Les trois premiers sont obligatoires, le dernier est facultatif.

valeur_recherche(lookup_value) – la valeur à rechercher. Il peut s'agir d'une valeur (nombre, date, texte) ou d'une référence de cellule (contenant la valeur de recherche), ou d'une valeur renvoyée par une autre fonction Excel. Par exemple, cette formule recherchera la valeur 40 :
RECHERCHEV(40,A2:B15,2)
=RECHERCHEV(40,A2:B15,2)

Si la valeur que vous recherchez est inférieure à la plus petite valeur de la première colonne de la plage que vous consultez, la fonction RECHERCHEV signalera une erreur #N / A(#N / A).

tableau_tableau(tableau) – deux colonnes de données ou plus. N'oubliez pas, fonction RECHERCHEV recherche toujours la valeur dans la première colonne de la plage donnée dans l'argument tableau_tableau(tableau). La plage visualisée peut contenir diverses données, par exemple du texte, des dates, des nombres, des valeurs logiques. La fonction n'est pas sensible à la casse, ce qui signifie que les caractères majuscules et minuscules sont considérés comme identiques. Ainsi, notre formule recherchera la valeur 40 dans les cellules de A2 avant A15 car A est la première colonne de la plage A2:B15 donnée dans l'argument tableau_tableau(tableau):
RECHERCHEV(40,A2:B15,2)
=RECHERCHEV(40,A2:B15,2)

Si la valeur de l'argument col_index_num(numéro_colonne) moins 1 , Que RECHERCHEV signalera une erreur #VALEUR!(#VALEUR!). Et s'il est supérieur au nombre de colonnes de la plage tableau_tableau(tableau), la fonction retournera une erreur #REF!(#LIEN!).

range_lookup(interval_view) – définit ce qu'il faut rechercher :
- correspondance exacte, l'argument doit être égal à FAUX(MENSONGE);
- correspondance approximative, l'argument est égal à VRAI(VRAI) ou pas spécifié du tout.
Ce paramètre est facultatif, mais très important. Plus tard dans ce tutoriel RECHERCHEV Je vais vous montrer plusieurs exemples qui expliquent comment composer correctement des formules pour trouver des correspondances exactes et approximatives.

Exemples avec la fonction RECHERCHEV

J'espère que la fonction RECHERCHEV est devenu un peu plus clair pour vous. Examinons maintenant quelques cas d'utilisation RECHERCHEV dans des formules avec des données réelles.

Comment utiliser RECHERCHEV pour effectuer une recherche sur une autre feuille Excel

En pratique, les formules avec fonction RECHERCHEV rarement utilisé pour rechercher des données sur la même feuille. Le plus souvent, vous rechercherez et récupérerez les valeurs correspondantes sur une autre feuille.

Pour, en utilisant RECHERCHEV, effectuez une recherche dans une autre feuille Microsoft Excel, vous devez en argument tableau_tableau(tableau) spécifiez le nom de la feuille avec un point d'exclamation, suivi d'une plage de cellules. Par exemple, la formule suivante montre que la plage A2:B15 est sur la feuille nommée Feuille2.

RECHERCHEV(40,Feuille2!A2:B15,2)
=RECHERCHEV(40,Feuille2!A2:B15,2)

Bien entendu, le nom de la feuille ne doit pas être saisi manuellement. Commencez simplement à taper la formule et quand il s'agit de l'argument tableau_tableau(tableau), passez à la feuille souhaitée et sélectionnez la plage de cellules souhaitée avec la souris.

La formule affichée dans la capture d'écran ci-dessous recherche le texte « Produit 1 » dans la colonne A (il s'agit de la 1ère colonne de la plage A2 : B9) dans la feuille de calcul. Des prix.

RECHERCHEV("Produit 1",Prix !$A$2 :$B$9,2,FALSE)
=RECHERCHEV("Produit 1";Prix!$A$2:$B$9,2;FALSE)

N'oubliez pas que lorsque vous recherchez une valeur de texte, vous devez la mettre entre guillemets (« »), comme c'est généralement le cas dans les formules Excel.

Pour argumenter tableau_tableau(tableau) Il est conseillé de toujours utiliser des liens absolus (avec le signe $). Dans ce cas, la plage de recherche restera inchangée lorsque vous copierez la formule dans d'autres cellules.

Rechercher dans un autre classeur à l'aide de RECHERCHEV

Pour fonctionner RECHERCHEV travaillé entre deux classeurs Excel, vous devez indiquer le nom du classeur entre crochets avant le nom de la feuille.

Par exemple, vous trouverez ci-dessous une formule qui recherche la valeur 40 sur une feuille Feuille2 dans le livre Nombres.xlsx:

RECHERCHEV(40,Feuille2!A2:B15,2)
=RECHERCHEV(40,Feuille2!A2:B15,2)

Voici la manière la plus simple de créer une formule dans Excel avec RECHERCHEV qui renvoie à un autre classeur :

Ouvrez les deux livres. Ce n’est pas nécessaire, mais cela facilite la création de la formule. Vous ne souhaitez pas saisir le nom du classeur manuellement, n'est-ce pas ? De plus, cela vous protégera des fautes de frappe accidentelles.
Commencez à taper la fonction RECHERCHEV, et quand il s'agit de l'argumentation tableau_tableau(tableau), passez à un autre classeur et sélectionnez-y la plage de recherche souhaitée.

La capture d'écran ci-dessous montre la formule dans laquelle la recherche est définie sur une plage dans le classeur. Liste de prix.xlsx sur une feuille Des prix.

Fonction RECHERCHEV fonctionnera même lorsque vous fermez le classeur dans lequel vous effectuez la recherche et que le chemin complet du fichier du classeur apparaît dans la barre de formule, comme indiqué ci-dessous :

Si le nom d'un classeur ou d'une feuille contient des espaces, il doit alors être entouré d'apostrophes :

RECHERCHEV(40,"Feuille2"!A2:B15,2)
=RECHERCHEV(40,"Feuille2"!A2:B15,2)

Comment utiliser une plage ou une table nommée dans les formules RECHERCHEV

Si vous envisagez d'utiliser une plage de recherche dans plusieurs fonctions RECHERCHEV, vous pouvez créer une plage nommée et saisir son nom dans la formule comme argument tableau_tableau(tableau).

Pour créer une plage nommée, sélectionnez simplement les cellules et entrez un nom approprié dans le champ Nom, à gauche de la barre de formule.

Vous pouvez maintenant écrire cette formule pour trouver le prix d'un produit Produit 1:

RECHERCHEV("Produit 1",Produits,2)
=RECHERCHEV("Produit 1";Produits;2)

La plupart des noms de plage fonctionnent pour l'intégralité du classeur Excel. Il n'est donc pas nécessaire de spécifier le nom de la feuille pour l'argument. tableau_tableau(tableau), même si la formule et la plage de recherche se trouvent sur des feuilles différentes du classeur. S'ils se trouvent dans des livres différents, alors avant le nom de la plage, vous devez indiquer le nom du classeur, par exemple, comme ceci :

RECHERCHEV("Produit 1",Liste de prix.xlsx!Produits,2)
=RECHERCHEV("Produit 1";Liste de prix.xlsx!Produits;2)

Cela rend la formule beaucoup plus claire, n’est-ce pas ? De plus, l'utilisation de plages nommées constitue une bonne alternative aux références absolues, car la plage nommée ne change pas lorsque vous copiez la formule dans d'autres cellules. Cela signifie que vous pouvez être sûr que la plage de recherche dans la formule restera toujours correcte.

Si vous convertissez une plage de cellules en un tableau Excel complet à l'aide de la commande Tableau Onglet (Tableau) Insérer(Insérer), puis lorsque vous sélectionnez une plage avec la souris, Microsoft Excel ajoutera automatiquement les noms de colonnes (ou les noms de tableaux si vous sélectionnez le tableau entier) à la formule.

La formule finale ressemblera à ceci :

RECHERCHEV("Produit 1",Tableau46[:],2)
=RECHERCHEV("Produit 1";Tableau46[:];2)

Ou peut-être même comme ceci :

RECHERCHEV("Produit 1",Tableau46,2)
=RECHERCHEV("Produit 1";Tableau46;2)

Lorsque vous utilisez des plages nommées, les liens pointeront vers les mêmes cellules, quel que soit l'endroit où vous copiez la fonction. RECHERCHEV dans le cahier d'exercices.

Utilisation de caractères génériques dans les formules RECHERCHEV

Comme pour de nombreuses autres fonctions, RECHERCHEV Vous pouvez utiliser les caractères génériques suivants :

Point d'interrogation (?) – remplace n'importe quel caractère.
Astérisque (*) – remplace toute séquence de caractères.

Utiliser des caractères génériques dans les fonctions RECHERCHEV peut être utile dans de nombreux cas, par exemple :

Lorsque vous ne vous souvenez pas exactement du texte que vous devez rechercher.
Lorsque vous souhaitez rechercher un mot faisant partie du contenu d’une cellule. Sache que RECHERCHEV recherche tout le contenu de la cellule, comme si l'option était activée Faire correspondre le contenu entier de la cellule(Cellule entière) dans la recherche standard Excel.
Lorsqu'une cellule contient des espaces supplémentaires au début ou à la fin du contenu. Dans une telle situation, vous risquez de vous creuser la tête pendant longtemps à essayer de comprendre pourquoi la formule ne fonctionne pas.

Exemple 1 : Recherche de texte commençant ou se terminant par certains caractères

Supposons que vous souhaitiez rechercher un client spécifique dans la base de données ci-dessous. Vous ne vous souvenez pas de son nom de famille, mais vous savez qu'il commence par « ack ». Cette formule fera parfaitement l’affaire :

RECHERCHEV("ack*",$A$2:$C$11,1,FALSE)
=RECHERCHEV("ack*",$A$2:$C$11,1,FALSE)

Maintenant que vous êtes sûr d'avoir trouvé le bon nom, vous pouvez utiliser la même formule pour trouver le montant payé par ce client. Pour ce faire, changez simplement le troisième argument de la fonction RECHERCHEV au numéro de la colonne souhaitée. Dans notre cas, il s'agit de la colonne C (3ème de la fourchette) :

RECHERCHEV("ack*",$A$2:$C$11,3,FALSE)
=RECHERCHEV("ack*",$A$2:$C$11,3,FALSE)

Voici quelques exemples supplémentaires avec des caractères génériques :

~ Trouvez un nom se terminant par « homme » :

RECHERCHEV("*homme",$A$2:$C$11,1,FALSE)
=RECHERCHEV("*homme";$A$2:$C$11,1;FALSE)

~ Trouvez un nom commençant par « annonce » et se terminant par « fils » :

RECHERCHEV("annonce*fils",$A$2:$C$11,1,FALSE)
=RECHERCHEV("annonce*fils";$A$2:$C$11,1;FALSE)

~ Recherchez le prénom dans la liste, composée de 5 caractères :

RECHERCHEV("?????",$A$2:$C$11,1,FALSE)
=RECHERCHEV("?????",$A$2:$C$11,1,FALSE)

Pour fonctionner RECHERCHEV a fonctionné correctement avec les caractères génériques, vous devez toujours utiliser FAUX(MENSONGE). Si la plage de recherche contient plusieurs valeurs correspondant aux critères de recherche génériques, la première valeur trouvée sera renvoyée.

Exemple 2 : Combiner des caractères génériques et des références de cellules dans des formules RECHERCHEV

Examinons maintenant un exemple légèrement plus complexe de recherche à l'aide de la fonction RECHERCHEV par valeur dans une cellule. Imaginez que la colonne A contient une liste de clés de licence et que la colonne B contient une liste de noms propriétaires de la licence. De plus, vous avez une partie (plusieurs caractères) d'une clé de licence dans la cellule C1 et vous souhaitez trouver le nom du propriétaire.

Cela peut être fait en utilisant cette formule :

RECHERCHEV("*"&C1&"*",$A$2:$B$12,2,FALSE)
=RECHERCHEV("*"&C1&"*";$A$2:$B$12,2;FALSE)

Cette formule recherche la valeur de la cellule C1 dans une plage donnée et renvoie la valeur correspondante de la colonne B. Notez que dans le premier argument, nous utilisons le caractère esperluette (&) avant et après la référence de cellule pour associer la chaîne de texte.

Comme vous pouvez le voir sur la figure ci-dessous, la fonction RECHERCHEV renvoie la valeur « Jeremy Hill » car sa clé de licence contient la séquence de caractères de la cellule C1.

Notez que l'argument tableau_tableau(tableau) dans la capture d'écran ci-dessus contient le nom du tableau (Tableau7) au lieu de spécifier une plage de cellules. C'est ce que nous avons fait dans l'exemple précédent.

Correspondance exacte ou approximative dans la fonction RECHERCHEV

Et enfin, examinons de plus près le dernier argument spécifié pour la fonction RECHERCHEV – range_lookup(time-lapse_view). Comme mentionné au début de la leçon, cet argument est très important. Vous pouvez obtenir des résultats complètement différents dans la même formule avec sa valeur VRAI(Vrai ou FAUX(MENSONGE).

Tout d’abord, découvrons ce que Microsoft Excel entend par correspondance exacte et approximative.

Si l'argument range_lookup FAUX(FAUX), la formule recherche une correspondance exacte, c'est-à-dire exactement la même valeur que celle spécifiée dans l'argument valeur_recherche(valeur_recherche). Si dans la première colonne de la plage t able_array(tableau) il y a deux valeurs ou plus qui correspondent à l'argument valeur_recherche(search_value), alors le premier sera sélectionné. Si aucune correspondance n'est trouvée, la fonction signalera une erreur #N / A(#N/A).Par exemple, la formule suivante signalera une erreur #N / A(#N/A) s'il n'y a aucune valeur dans la plage A2:A15 4 :
RECHERCHEV(4,A2:B15,2,FALSE)
=RECHERCHEV(4,A2:B15,2,FAUX)
Si l'argument range_lookup(timelapse) est égal à VRAI(VRAI), la formule recherche une correspondance approximative. Plus précisément, d'abord la fonction RECHERCHEV recherche une correspondance exacte et, si aucune n'est trouvée, en sélectionne une approximative. Une correspondance approximative est la plus grande valeur qui ne dépasse pas la valeur spécifiée dans l'argument valeur_recherche(valeur_recherche).

Si l'argument range_lookup(timelapse) est égal à VRAI(VRAI) ou non précisé, alors les valeurs de la première colonne de la plage doivent être triées par ordre croissant, c'est-à-dire de la plus petite à la plus grande. Sinon fonction RECHERCHEV peut renvoyer un résultat erroné.

Pour mieux comprendre l’importance du choix VRAI(Vrai ou FAUX(FAUX), regardons quelques formules supplémentaires avec la fonction RECHERCHEV et regardez les résultats.

Exemple 1 : Trouver une correspondance exacte à l'aide de RECHERCHEV

Comme vous vous en souvenez, pour trouver une correspondance exacte, le quatrième argument de la fonction RECHERCHEV devrait avoir de l'importance FAUX(MENSONGE).

Regardons à nouveau le tableau du tout premier exemple et découvrons quel animal peut se déplacer à la vitesse 50 miles par heure. Je pense que cette formule ne vous posera aucune difficulté :

RECHERCHEV(50,$A$2 :$B$15,2,FALSE)
=RECHERCHEV(50,$A$2 :$B$15,2,FALSE)

Notez que notre plage de recherche (colonne A) contient deux valeurs 50 – dans les cellules A5 Et A6. La formule renvoie la valeur de la cellule B5. Pourquoi? Parce que lors de la recherche d'une correspondance exacte, la fonction RECHERCHEV utilise la première valeur trouvée qui correspond à celle recherchée.

Exemple 2 : Utiliser RECHERCHEV pour trouver une correspondance approximative

Lorsque vous utilisez la fonction RECHERCHEV pour trouver une correspondance approximative, c'est-à-dire quand une dispute range_lookup(timelapse) est égal à VRAI(VRAI) ou manquant, la première chose à faire est de trier la plage par première colonne par ordre croissant.

Ceci est très important car la fonction RECHERCHEV renvoie la valeur la plus grande suivante après celle donnée, puis la recherche s'arrête. Si vous négligez de trier correctement, vous vous retrouverez avec des résultats très étranges ou un message d'erreur. #N / A(#N / A).

Vous pouvez maintenant utiliser l'une des formules suivantes :

RECHERCHEV(69,$A$2 :$B$15,2,TRUE) ou =RECHERCHEV(69,$A$2 :$B$15,2)
=RECHERCHEV(69,$A$2 :$B$15,2,TRUE) ou =RECHERCHEV(69,$A$2 :$B$15,2)

Comme vous pouvez le voir, je veux savoir quel animal a la vitesse la plus proche de 69 miles par heure. Et voici le résultat que la fonction m'a renvoyé RECHERCHEV:

Comme vous pouvez le voir, la formule a renvoyé le résultat Antilope(Antilope), dont la vitesse 61 miles par heure, bien que la liste comprenne également guépard(Guépard) qui court avec vitesse 70 miles par heure, et 70 est plus proche de 69 que de 61, n'est-ce pas ? Pourquoi cela arrive-t-il? Parce que la fonction RECHERCHEV lors de la recherche d'une correspondance approximative, renvoie la plus grande valeur qui n'est pas supérieure à la valeur de recherche.

J'espère que ces exemples vous éclaireront sur l'utilisation de la fonction RECHERCHEV dans Excel, et vous ne la considérez plus comme une étrangère. Maintenant, cela ne fera pas de mal de répéter brièvement les points clés du matériel que nous avons étudié afin de mieux le consolider en mémoire.

RECHERCHEV dans Excel - vous devez vous en souvenir !

Fonction RECHERCHEV Excel ne peut pas regarder vers la gauche. Il recherche toujours la valeur dans la colonne la plus à gauche de la plage spécifiée par l'argument tableau_tableau(tableau).
En fonction RECHERCHEV Toutes les valeurs ne sont pas sensibles à la casse, ce qui signifie que les lettres minuscules et majuscules sont équivalentes.
Si la valeur que vous recherchez est inférieure à la valeur minimale de la première colonne de la plage que vous consultez, la fonction RECHERCHEV signalera une erreur #N / A(#N / A).
Si 3ème argument col_index_num(numéro_colonne) moins 1 , fonction RECHERCHEV signalera une erreur #VALEUR!(#VALEUR!). S'il est supérieur au nombre de colonnes de la plage tableau_tableau(tableau), la fonction signalera une erreur #REF!(#LIEN!).
Utiliser des références de cellules absolues dans l'argument tableau_tableau(tableau) afin que lorsque vous copiez la formule, la plage de recherche correcte soit préservée. Essayez d'utiliser des plages ou des tableaux nommés dans Excel comme alternative.
Lorsque vous effectuez une recherche de correspondance approximative, n'oubliez pas que la première colonne de la plage recherchée doit être triée par ordre croissant.
Enfin, rappelons l’importance du quatrième argument. Utiliser des valeurs VRAI(Vrai ou FAUX(FAUX) de manière réfléchie et vous vous épargnerez bien des maux de tête.

Dans les articles suivants de notre tutoriel sur la fonction RECHERCHEV dans Excel, nous apprendrons des exemples plus avancés tels que l'exécution de divers calculs à l'aide de RECHERCHEV, extrayant les valeurs de plusieurs colonnes et autres. Je vous remercie d'avoir lu ce tutoriel et j'espère vous revoir la semaine prochaine !

Bonjour les amis. À quelle fréquence devez-vous rechercher une correspondance dans une feuille de calcul Excel pour une valeur ? Par exemple, vous devez rechercher l’adresse d’une personne dans un annuaire, ou le prix d’un produit dans une liste de prix. Si de telles tâches se produisent, cet article est fait pour vous !

J'effectue des procédures similaires tous les jours et sans les fonctions décrites ci-dessous, j'aurais vraiment du mal. Prenez-en note et appliquez-les dans votre travail !

Recherche dans le tableau Excel, fonctions VLOOKUP et GLOOKUP

Le rôle de ces fonctions dans la vie d'un utilisateur ordinaire ne peut guère être surestimé. Vous pouvez désormais facilement trouver une entrée appropriée dans le tableau de données et renvoyer la valeur correspondante.

La syntaxe de la fonction RECHERCHEV est : =RECHERCHEV( valeur_recherche ; recherche_table ; numéro_colonne_sortie ; [type_mapping]). Regardons les arguments :

Valeur de recherche– la valeur que l’on va rechercher. C'est un argument requis;
Tableau de recherche– le tableau de cellules dans lequel la recherche aura lieu. La colonne contenant les valeurs recherchées doit être la première de ce tableau. C'est également un argument obligatoire ;
Numéro de colonne à afficher– le numéro de série de la colonne (en commençant par la première du tableau), à partir de laquelle la fonction affichera les données si les valeurs requises correspondent. Argument requis ;
Type correspondant– sélectionnez « 1 » (ou « TRUE ») pour une correspondance faible, « 0 » (« FALSE ») pour une correspondance complète. L'argument est facultatif ; s'il est omis, une recherche sera effectuée correspondance non stricte.

Trouver une correspondance exacte à l'aide de RECHERCHEV

Regardons un exemple du fonctionnement de la fonction RECHERCHEV lorsque le type de correspondance est FALSE, recherchant une correspondance exacte. Le tableau B5:E10 indique les immobilisations d'une certaine entreprise, leur valeur comptable, leur numéro d'inventaire et leur emplacement. La cellule B2 contient le nom pour lequel vous devez trouver le numéro d'inventaire dans le tableau et le placer dans la cellule C2.

Fonction RECHERCHEV dans Excel

Écrivons la formule : =VLOOKUP(B2,B5:E10,3,FALSE) .

Ici, le premier argument indique que dans le tableau, vous devez rechercher la valeur de la cellule B2, c'est-à-dire le mot « Fax ». Le deuxième argument indique que le tableau dans lequel rechercher est dans la plage B5:E10 et que vous devez rechercher le mot « Fax » dans la première colonne, c'est-à-dire dans le tableau B5:B10. Le troisième argument indique au programme que le résultat du calcul est contenu dans la troisième colonne du tableau, c'est-à-dire D5:D10. Le quatrième argument est FAUX, c'est-à-dire une correspondance complète est requise.

Ainsi, la fonction recevra la chaîne « Fax » de la cellule B2 et la recherchera dans le tableau B5:B10 de haut en bas. Une fois qu'une correspondance est trouvée (ligne 8), la fonction renverra la valeur correspondante de la colonne D, c'est-à-dire Contenu du D8. C'est exactement ce dont nous avions besoin, le problème est résolu.

Si la valeur recherchée n'est pas trouvée, la fonction retournera .

Trouver une correspondance imprécise à l'aide de RECHERCHEV

Grâce à cette option dans RECHERCHEV, nous pouvons éviter les formules complexes pour trouver le résultat souhaité.

Le tableau B5 : C12 montre les taux d’intérêt sur les prêts en fonction du montant du prêt. Dans la cellule B2, nous indiquons le montant du prêt et souhaitons recevoir le taux pour une telle transaction en C2. La tâche est difficile dans la mesure où le montant peut être n'importe quoi et est peu susceptible de coïncider avec ceux spécifiés dans le tableau ; la recherche d'une correspondance exacte ne convient pas :

Ensuite, nous écrivons la formule d'une recherche non stricte : =RECHERCHEV(B2,B5:C12,2,VRAI). Désormais, parmi toutes les données présentées dans la colonne B, le programme recherchera la plus petite la plus proche. C'est-à-dire que pour le montant de 8 000, la valeur de 5 000 sera sélectionnée et le pourcentage correspondant sera affiché.

Recherche RECHERCHEV non stricte dans Excel

Pour que la fonction fonctionne correctement, vous devez trier la première colonne du tableau par ordre croissant. Sinon, cela pourrait donner un résultat erroné.

La fonction GLOOKUP a la même syntaxe que VLOOKUP, mais recherche le résultat en lignes plutôt qu'en colonnes. Autrement dit, il analyse les tableaux non pas de haut en bas, mais de gauche à droite et affiche le numéro de ligne spécifié, pas la colonne.

Recherche de données à l'aide de la fonction VIEW

La fonction LOOKUP fonctionne de manière similaire à VLOOKUP, mais a une syntaxe différente. Je l'utilise lorsque la table de données contient plusieurs dizaines de colonnes et que pour utiliser RECHERCHEV, vous devez en outre calculer le numéro de la colonne de sortie. Dans de tels cas, la fonction VIEW facilite la tâche. Et donc la syntaxe : =VOIR( valeur_recherche ; array_to_search; Tableau_à_affichage) :

Valeur de recherche– des données ou un lien vers les données à rechercher ;
Tableau à rechercher– une ligne ou une colonne dans laquelle on recherche une valeur similaire. Nous devons trier ce tableau par ordre croissant ;
Tableau à afficher– une plage contenant des données pour afficher les résultats. Naturellement, il doit avoir la même taille que le tableau de recherche.

En écrivant de cette façon, vous donnez une référence non relative au tableau de résultats. Et vous le montrez directement, c'est-à-dire il n'est pas nécessaire de calculer au préalable le numéro de la colonne de sortie. Nous utilisons la fonction VIEW dans le premier exemple pour la fonction RECHERCHEV (immobilisations, numéros d'inventaire) : =VUE(B2,B5:B10,D5:D10). Le problème a été résolu avec succès !

Fonction VOIR dans Microsoft Excel

Recherche par coordonnées relatives. Fonctions MATCH et INDEX

Une autre façon de rechercher des données consiste à combiner les fonctions MATCH et INDEX.

Le premier d'entre eux permet de rechercher une valeur dans un tableau et d'obtenir son numéro de série : SEARCH( valeur_recherche ; tableau_vu ; [Type correspondant] ). Arguments de la fonction :

Valeur de recherche– argument requis
Tableau à visualiser– une ligne ou une colonne dans laquelle nous recherchons une correspondance. Argument requis
Type correspondant– spécifiez « 0 » pour rechercher une correspondance exacte, « 1 » pour la plus petite correspondance la plus proche, « -1 » pour la plus grande correspondance la plus proche. Étant donné que la fonction recherche du début à la fin de la liste, lorsque vous recherchez le plus petit le plus proche, triez la colonne de recherche par ordre décroissant. Et lorsque vous en cherchez plus, triez-les par ordre croissant.

La position de la valeur recherchée a été trouvée, vous pouvez désormais l'afficher à l'écran grâce à la fonction INDICE( Tableau; numéro de ligne; [Numéro_colonne]) :

Tableau– l'argument spécifie dans quel tableau de cellules la valeur doit être sélectionnée
Numéro de ligne– indiquez le numéro de série de la ligne (à partir de la première cellule du tableau) que vous souhaitez afficher. Ici, vous pouvez écrire la valeur manuellement ou utiliser le résultat d'un autre calcul de fonction. Par exemple, RECHERCHE.
Numéro de colonne– argument facultatif, spécifié si le tableau est composé de plusieurs colonnes. Si un argument est omis, la formule utilise la première colonne du tableau.

Combinons maintenant ces fonctions pour obtenir le résultat :

Fonctions MATCH et INDEX dans Excel

Ce sont les méthodes de recherche et d’affichage des données qui existent dans Excel. De plus, vous pouvez les utiliser dans des calculs, les utiliser dans des présentations, effectuer des opérations avec eux, les spécifier comme arguments pour d'autres fonctions, etc.

Sentez-vous comment vos connaissances et vos compétences se développent et se renforcent ? Alors ne vous arrêtez pas, continuez à lire ! Dans le prochain article, nous verrons : ce sera difficile et intéressant !