6 fonctions de recherche de Microsoft Excel pour rechercher plus efficacement des feuilles de calcul
La recherche de feuilles de calcul Microsoft Excel semble facile. Bien que Ctrl + F puisse vous aider à trouver la plupart des éléments dans une feuille de calcul, vous aurez besoin d’outils plus sophistiqués pour rechercher et extraire des données en fonction de valeurs spécifiques. Nous vous aiderons à gagner beaucoup de temps avec notre liste de fonctionnalités de recherche avancées.
Une fois que vous savez comment utiliser Rechercher pour effectuer une recherche dans Excel, quelle que soit la taille de votre feuille de calcul, vous trouverez toujours ce que vous cherchez !
1. Fonction RECHERCHEV
La fonction VLOOKUP vous permet de rechercher une valeur spécifique dans une colonne et d’extraire la valeur de la ligne correspondante dans une colonne adjacente. Deux exemples de la façon dont vous pouvez le faire sont (1) la recherche du nom de famille d’un employé par numéro d’employé, ou (2) la recherche d’un numéro de téléphone en spécifiant son nom de famille.
Voici la syntaxe de la fonction :
=RECHERCHEV([lookup_value], [table_array], [col_index_num], [range_lookup])
- [lookup_value] est l’information que vous avez déjà. Par exemple, si vous avez besoin de savoir dans quel état se trouve une ville, c’est le nom de la ville.
- [table_array] Vous permet de spécifier la cellule dans laquelle la fonction recherchera les valeurs de recherche et de retour. Lorsque vous sélectionnez une plage, assurez-vous que la première colonne incluse dans le tableau est celle qui contient la valeur de recherche !
- [col_index_num] est le numéro de la colonne contenant la valeur de retour.
- [range_lookup] est un paramètre facultatif qui prend la valeur 1 ou 0, mais vous pouvez également saisir TRUE ou FALSE. Si vous entrez 1 ou omettez ce paramètre, la fonction recherche une approximation, mais nous trouvons qu’il s’agit d’un hasard. Dans l’exemple ci-dessous, la recherche d’un VLOOKUP avec un score de 100 renvoie 90. La recherche d’une valeur inférieure (par exemple 88) renvoie une erreur.
Faire une vidéo du jour
Voyons comment l’utiliser. Cette feuille de calcul contient les noms des élèves et les scores de quatre tests différents. Supposons que vous vouliez trouver le score #4 pour les étudiants dont le nom de famille est « Davidson ». VLOOKUP le rend facile.
Voici la formule que vous utiliserez :
=RECHERCHEV(« Davidson
Étant donné que le quatrième score est la cinquième colonne du nom de famille que nous recherchons, 5 est le paramètre d’index de colonne.Notez que lorsque vous recherchez du texte, définissez [range_lookup] Aller à 0 est une bonne idée. Sans cela, vous risquez d’obtenir de mauvais résultats.
Le résultat est le suivant :
Il renvoie 79, qui est le 4e score de l’étudiant que nous avons interrogé.
Remarques sur RECHERCHEV
Il y a quelques points à garder à l’esprit lors de l’utilisation de VLOOKUP. Assurez-vous que la première colonne de votre plage est celle qui contient votre valeur de recherche. Si ce n’est pas dans la première colonne, la fonction renverra un résultat incorrect. Cela ne devrait pas poser de problème si vos colonnes sont bien organisées.
N’oubliez pas non plus que VLOOKUP ne renverra qu’une seule valeur. Il y a un autre étudiant avec le nom de famille « Davidson », mais VLOOKUP ne renverra que les résultats pour la première entrée, il n’y a aucune indication qu’il y a plusieurs correspondances.
en relation: Comment faire une recherche VLOOKUP dans Excel
2. Fonction RECHERCHEH
VLOOKUP trouve la valeur correspondante dans une autre colonne, HLOOKUP trouve la valeur correspondante dans une ligne différente. Parce qu’il est généralement plus facile de parcourir les en-têtes de colonne jusqu’à ce que vous trouviez le bon et d’utiliser les filtres pour trouver ce que vous recherchez, HLOOKUP est mieux utilisé lorsque vous avez beaucoup de feuilles de calcul ou si vous travaillez avec des valeurs organisé par temps.
Voici la syntaxe de la fonction :
=HRECHERCHE([lookup_value], [table_array], [row_index_num], [range_lookup])
- [lookup_value] est la valeur pour laquelle vous connaissez et souhaitez trouver la valeur correspondante.
- [table_array] est la cellule dans laquelle vous souhaitez effectuer la recherche.
- [row_index_num] Spécifie la ligne d’où viendra la valeur de retour.
- [range_lookup] Comme dans VLOOKUP, si possible, laissez-le vide pour obtenir la valeur la plus proche ou entrez 0 pour trouver une correspondance exacte uniquement.
Nous utiliserons la même feuille de calcul que précédemment. Vous pouvez utiliser HLOOKUP pour trouver le score d’une ligne spécifique. Nous allons faire ceci :
=RECHERCHEH(« Score 4 »
Renvoyez le score comme indiqué dans l’image ci-dessous :
Thomas Davidson, élève de rang 6, a obtenu 68 points à son quatrième test.
Comment calculer la moyenne pondérée dans Excel
Précautions pour RECHERCHEH
Comme avec VLOOKUP, la valeur de recherche doit se trouver dans la première ligne du tableau de la table. Cela se produit rarement dans HLOOKUP, car vous utiliseriez normalement des en-têtes de colonne comme valeurs de recherche. RECHERCHEH ne renvoie également qu’une seule valeur.
3-4. Fonctions INDEX et MATCH
INDEX et MATCH sont deux fonctions différentes, mais lorsqu’elles sont utilisées ensemble, elles peuvent accélérer la recherche dans de grandes feuilles de calcul. Ces deux fonctionnalités ont des inconvénients, mais en les combinant, nous tirerons parti des deux.
Tout d’abord, la syntaxe des deux fonctions :
= indice([array], [row_number], [column_number])
- [array] est le tableau dans lequel vous allez chercher.
- [row_number] et [column_number] Peut être utilisé pour affiner votre recherche (nous y reviendrons plus tard).
=correspond([lookup_value], [lookup_array], [match_type])
- [lookup_value] est un terme de recherche qui peut être une chaîne ou un nombre.
- [lookup_array] est le tableau dans lequel Microsoft Excel recherchera les termes de recherche.
- [match_type] est un paramètre facultatif qui peut valoir 1, 0 ou -1. 1 renverra la plus grande valeur inférieure ou égale à votre terme de recherche. 0 ne renverra que votre terme exact, -1 renverra la plus petite valeur supérieure ou égale à votre terme de recherche.
Il n’est peut-être pas clair comment nous allons utiliser ces deux fonctions ensemble, je vais donc les énumérer ici. MATCH prend un terme de recherche et renvoie une référence de cellule. Dans l’image ci-dessous, vous pouvez voir que lorsque vous recherchez le nom de famille « Davidson » dans la colonne B, MATCH renvoie 2.
INDEX, en revanche, est le contraire : il prend une référence de cellule et renvoie la valeur qu’elle contient. Vous pouvez voir ici que lorsqu’on lui demande de renvoyer la deuxième ligne de la colonne B, INDEX renvoie « Davidson », la valeur de la ligne 2.
Ce que nous voulons faire, c’est combiner les deux afin que MATCH renvoie une référence de cellule que INDEX utilise pour trouver la valeur dans la cellule. Supposons que vous vous souveniez d’un élève dont le nom de famille est Townsend et que vous vouliez voir quelle était la quatrième année de l’élève. Voici la formule que nous utiliserons :
=INDEX(F:F, EQUIV(« Townsend », B:B, 0))
Vous remarquerez que le type de correspondance est défini sur 0 ici. C’est ce que vous voulez utiliser lorsque vous recherchez une chaîne. Voici ce que nous obtenons lorsque nous exécutons la fonction :
Comme vous pouvez le voir sur l’illustration, Ralph Townsend a marqué 68 lors de son quatrième test, qui est le nombre obtenu lorsque nous avons exécuté la fonction. Cela peut ne pas sembler très utile lorsque vous ne pouvez regarder que quelques colonnes, mais imaginez combien de temps vous gagneriez si vous deviez le faire 50 fois sur une grande feuille de calcul de base de données avec des centaines de colonnes !
5. Fonction de recherche
Un article sur la recherche d’éléments dans Excel serait incomplet sans la fonctionnalité FIND. Mais ce n’est peut-être pas ce que vous attendez. Vous pouvez utiliser la fonction TROUVER d’Excel pour déterminer la position d’une chaîne de texte dans une autre chaîne de texte.
Supposons que nous voulions trouver la première occurrence de la lettre « x » dans la phrase « Le renard brun a sauté par-dessus la clôture ». Ce sera notre fonction :
=TROUVER(« x », « Le renard brun a sauté par-dessus la clôture »)
Le nombre résultant représente la position de la chaîne de requête. Si vous recherchez une chaîne multi-caractères, disons que nous recherchons « renard », le résultat indiquera la position du premier caractère de la requête ; 11 dans ce cas.
Remarques pour TROUVER
Comme VLOOKUP, HLOOKUP et d’autres fonctions, FIND ne reconnaît que la première occurrence d’une chaîne. Notez que FIND est sensible à la casse. Vous pouvez l’utiliser pour trouver plusieurs caractères. Bien que nous ayons utilisé des lettres dans notre exemple, cela fonctionne également pour les chiffres.
En soi, cette fonction peut ne pas sembler très utile, mais elle entre en jeu lorsque vous démarrez des fonctions d’imbrication. Par exemple, vous pouvez utiliser le résultat FIND pour diviser une chaîne de texte à la position correspondant à la chaîne identifiée par FIND.
Trouver et rechercher
Nous ne pouvons pas couvrir FIND sans mentionner la fonction SEARCH.Eh bien, c’est essentiellement la même chose que FIND, sauf que c’est non sensible aux majuscules et minuscules. Il autorise également les caractères génériques, ce qui signifie que vous pouvez rechercher des correspondances inexactes.
Excel prend en charge trois caractères génériques :
- Astérisque
- qui est un espace réservé pour n’importe quel nombre de caractères, y compris zéro.
- Un point d’interrogation (?) peut remplacer n’importe quel caractère.
tilde (~), qui convertit les caractères génériques « astérisque » et « point d’interrogation » en caractères littéraux, ce qui signifie qu’il annule leur fonction générique. Vous pouvez l’utiliser comme ~* ou ~?. en relation:
Conseils pour l’utilisation du texte et des fonctions de texte dans Excel
6. Fonction XLOOKUP
XLOOKUP est une nouvelle fonctionnalité conçue pour remplacer VLOOKUP. Comme VLOOKUP, vous pouvez l’utiliser pour trouver du contenu dans une table ou une plage en recherchant des valeurs connues. Il diffère de VLOOKUP en ce qu’il vous permet de trouver des valeurs dans les colonnes à gauche ou à droite de la valeur de la requête ; avec VLOOKUP, vous ne pouvez trouver des données qu’à droite de la colonne de la requête.
Voici la syntaxe de la fonction : [if_not_found]=XLOOKUP(valeur de recherche, tableau de recherche, tableau de retour, [match_mode], [search_mode],
- [lookup_value] )
- [lookup_array] est la valeur que vous recherchez, c’est-à-dire votre requête.
- [return_array] est le tableau ou la plage à rechercher.
- [if_not_found] est le tableau ou la plage à renvoyer. C’est la première différence avec VLOOKUP.
- [match_mode] est un paramètre facultatif qui renvoie un message de votre choix si aucune correspondance n’est trouvée.
- [search_mode] est un autre paramètre facultatif qui vous permet de trouver une correspondance exacte (0), le prochain plus petit élément (-1), le prochain plus grand élément (1) ou une correspondance générique (2).
est facultatif et vous permet de contrôler l’ordre de recherche. La valeur par défaut (1) lance la recherche à partir du premier élément. Vous pouvez également effectuer une recherche binaire en fonction du lookup_array trié par ordre croissant (2) ou décroissant (-2), en commençant par le dernier élément (-1).
Prenons notre exemple RECHERCHEV et inversons l’ordre de recherche. Cela devrait nous permettre de trouver la partition du deuxième étudiant nommé Davidson. Voici la formule :
=XRECHERCHE(G2,B2:B25,F2:F25,,,-1)
Notez que nous extrayons le nom de la colonne G2, sans l’écrire directement dans la formule. Ci-dessous, à quoi cela ressemble.
Cette fois, la formule renvoie le score de Thomas Davidson, pas celui d’Aidan Davidson. Mais il ne peut toujours pas renvoyer plusieurs résultats. en relation:
Qu’est-ce que la fonction XLOOKUP dans Excel et comment l’utiliser ?
Que la recherche Excel commence
Microsoft Excel possède de nombreuses fonctions de manipulation de données très puissantes, et les quatre énumérées ci-dessus ne font qu’effleurer la surface. Apprendre à les utiliser vous facilitera la vie. …
