4 fonctions de recherche dans Excel pour une recherche efficace dans des feuilles de calcul

La plupart du temps, trouver une cellule dans une feuille de calcul Excel est assez facile; si vous ne pouvez pas simplement parcourir les lignes et les colonnes, vous pouvez utiliser CTRL + F pour le rechercher. Mais si vous travaillez avec une feuille de calcul vraiment volumineuse Comment diviser une feuille de calcul Excel CSV énorme en fichiers séparés Comment diviser une feuille de calcul Excel CSV en fichiers séparés L'un des inconvénients de Microsoft Excel est la taille limitée d'une feuille de calcul. Si vous devez réduire la taille de votre fichier Excel ou diviser un fichier CSV volumineux, lisez la suite! En savoir plus, vous pouvez économiser beaucoup de temps lors de l’utilisation de l’une de ces quatre fonctions de recherche. Peu importe la taille de votre document Microsoft Excel, il sera beaucoup plus efficace.

La fonction VLOOKUP

Cette fonction vous permet de spécifier une colonne et une valeur, et retournera une valeur de la ligne correspondante d'une colonne différente (si cela n'a pas de sens, cela deviendra clair dans un instant). Vous pouvez par exemple rechercher le nom de famille d'un employé par son numéro d'employé ou rechercher un numéro de téléphone en spécifiant un nom de famille. Voici la syntaxe de la fonction:

= VLOOKUP ([valeur_recherche], [tableau de table], [num_index_col], [plage_lookup])

La [valeur de recherche] est l'information que vous avez déjà. Par exemple, si vous avez besoin de savoir dans quel état se trouve une ville, ce serait le nom de la ville. [tableau_array] vous permet de spécifier les cellules dans lesquelles la fonction recherchera les valeurs de recherche et de retour. Lorsque vous sélectionnez votre plage, assurez-vous que la première colonne incluse dans votre tableau est celle qui inclura votre valeur de recherche! C'est crucial. [num_index_col] est le numéro de la colonne contenant la valeur de retour.

[range_lookup] est un argument facultatif, qui prend 1 ou 0. Si vous entrez 1 ou omettez cet argument, la fonction recherche la valeur que vous avez entrée ou le nombre immédiatement inférieur. Ainsi, dans l'image ci-dessous, un VLOOKUP recherchant un score SAT de 652 renverra 646, car il s'agit du nombre le plus proche dans la liste inférieur à 652, et [plage_lookup] par défaut à 1..

Voyons comment nous pourrions utiliser cela. Didacticiel Mini-Excel: Utiliser la logique booléenne pour traiter des données complexes Didacticiel Mini-Excel: Utiliser la logique booléenne pour traiter des données complexes Les opérateurs logiques SI, NOT, AND et OR peuvent vous aider. Excel débutant pour pouvoir utilisateur. Nous expliquons les bases de chaque fonction et montrons comment les utiliser pour obtenir des résultats optimaux. En savoir plus et comptage avancé et ajout du didacticiel Mini Excel: Utilisation des fonctions de comptage et d’ajout avancées dans Excel Tutoriel Mini Excel: Utilisation des fonctions de comptage et d’ajout avancées dans Excel Le comptage et l’ajout de formules peuvent sembler banals par rapport à des formules Excel avancées. Mais ils peuvent vous aider à gagner beaucoup de temps lorsque vous devez collecter des informations sur les données de votre feuille de calcul. En savoir plus, je vais utiliser une feuille de calcul que j'ai générée avec generateata.com. Il contient les numéros d'identification, les prénoms et noms, la ville, l'état et les scores SAT. Disons que je veux trouver le score SAT d'une personne portant le nom de famille “Les hivers.” VLOOKUP facilite les choses. Voici la formule que nous utiliserions:

= VLOOKUP ("Hivers", C2: F101, 4, 0)

Comme les scores SAT correspondent à la quatrième colonne de la colonne du nom de famille, j'ai utilisé 4 pour l'argument index de la colonne. Notez que lorsque vous recherchez du texte, il est judicieux de définir [plage_lookup] sur 0; sans cela, vous pouvez obtenir de mauvais résultats. Voici ce que Microsoft Excel nous donne:

Il a renvoyé 651, le score SAT de l'étudiant nommé Kennedy Winters, qui figure à la ligne 92 (affiché dans l'encadré ci-dessus). Il aurait fallu beaucoup plus de temps pour faire défiler la liste à la recherche du nom que pour taper rapidement la syntaxe.!

VLOOKUP peut également être très utile si vous utilisez Microsoft Excel pour faire vos impôts Vous faites vos impôts? 5 formules Excel que vous devez savoir faire vos impôts? 5 Formules Excel à savoir Deux jours avant la date d'échéance de vos impôts, vous ne souhaitez pas payer d'autres frais de production tardive. C'est le moment de tirer parti de la puissance d'Excel pour tout mettre en ordre. Lire la suite .

Notes sur VLOOKUP

Il est bon de garder quelques points à l’esprit lorsque vous utilisez VLOOKUP. Tout d'abord, comme je l'ai mentionné précédemment, assurez-vous que la première colonne de votre plage est celle qui inclut votre valeur de recherche. Si ce n'est pas dans la première colonne, la fonction retournera des résultats incorrects. Si vos colonnes sont bien organisées, cela ne devrait pas poser de problème.

La deuxième chose à garder à l'esprit est que VLOOKUP ne renverra jamais qu'une valeur. Si nous avions utilisé “Géorgie” comme valeur de recherche, il aurait renvoyé le score du premier étudiant de Géorgie et rien n'indique qu'il y ait en fait deux étudiants de Géorgie.

La fonction HLOOKUP

Où VLOOKUP trouve les valeurs correspondantes dans une autre colonne, HLOOKUP trouve les valeurs correspondantes 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 que vous utilisiez un filtre pour trouver ce que vous recherchez, HLOOKUP est mieux utilisé lorsque vous avez vraiment grandes feuilles de calcul ou vous travaillez avec des valeurs organisées par temps. Voici la syntaxe:

= HLOOKUP ([valeur_recherche], [tableau_array], [numéro_index_ligne], [plage_lookup])

[lookup_value], encore une fois, est la valeur que vous connaissez et pour laquelle vous voulez trouver une valeur correspondante. [tableau_array] correspond aux cellules dans lesquelles vous souhaitez effectuer une recherche. [row_index_num] spécifie la ligne d'où proviendra la valeur de retour. Et [range_lookup] est le même que ci-dessus; laissez ce champ vide pour obtenir la valeur la plus proche lorsque cela est possible ou entrez 0 pour rechercher uniquement les correspondances exactes.

Pour cet exemple, j'ai créé une nouvelle feuille de calcul avec generateata.com. Celui-ci contient une ligne pour chaque état, avec un score SAT (nous dirons que c'est le score moyen pour l'état) pour les années 2000-2014. Nous utiliserons HLOOKUP pour trouver le score moyen au Minnesota en 2013. Voici comment procéder:

= HLOOKUP (2013, A1: P51, 24)

(Notez que 2013 n'est pas entre guillemets car il s'agit d'un nombre et non d'une chaîne. De plus, le 24 provient du Minnesota et se trouve sur la 24ème ligne.) Comme vous pouvez le voir sur l'image ci-dessous, le score est renvoyé:

Minnesotans en moyenne un score de 1014 en 2013.

Notes sur HLOOKUP

Comme avec VLOOKUP, la valeur de recherche doit figurer dans la première ligne de votre tableau de table. Cela pose rarement un problème avec HLOOKUP, car vous utiliserez généralement un titre de colonne pour une valeur de recherche. HLOOKUP ne renvoie également qu'une seule valeur.

Les fonctions INDEX et MATCH

INDEX et MATCH sont deux fonctions différentes, mais une fois utilisées ensemble, elles peuvent accélérer beaucoup la recherche dans une grande feuille de calcul. Les deux fonctions présentent des inconvénients, mais en les combinant, nous en tirons parti. Tout d'abord, cependant, la syntaxe:

= INDEX ([array], [row_number], [column_number]) = MATCH ([lookup_value], [lookup_array], [match_type])

Dans INDEX, [tableau] est le tableau dans lequel vous allez effectuer la recherche. [row_number] et [column_number] peuvent être utilisés pour affiner votre recherche; nous allons jeter un oeil à cela dans un instant.

La valeur [lookup_value] de MATCH est un terme de recherche qui peut être une chaîne ou un nombre; [lookup_array] est le tableau dans lequel Microsoft Excel recherchera le terme recherché. [match_type] est un argument optionnel qui peut être 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; et -1 renverra la plus petite valeur supérieure ou égale à votre terme de recherche.

La manière dont nous allons utiliser ces deux fonctions n’est peut-être pas claire. Je vais donc expliquer ici. MATCH prend un terme de recherche et retourne une référence de cellule. Dans l'image ci-dessous, vous pouvez voir que dans une recherche de la valeur 646 dans la colonne F, MATCH renvoie 4.

INDEX, en revanche, fait le contraire: il prend une référence de cellule et renvoie la valeur. Vous pouvez voir ici que, quand on vous dit de retourner la sixième cellule de la colonne Ville, INDEX retourne “Ancrage,” la valeur de la rangée 6.

Nous allons combiner les deux pour que MATCH renvoie une référence de cellule et que INDEX utilise cette référence pour rechercher la valeur dans une cellule. Supposons que vous vous souveniez qu'il y avait un élève dont le nom de famille était Waters et que vous souhaitiez connaître le score de cet élève. Voici la formule que nous allons utiliser:

= INDEX (F: F, MATCH ("Eaux", C: C, 0))

Vous remarquerez que le type de correspondance est défini sur 0 ici; quand vous cherchez une ficelle, c'est ce que vous voudrez utiliser. Voici ce que nous obtenons lorsque nous exécutons cette fonction:

Comme vous pouvez le constater dans l'encadré, Owen Waters a inscrit 1720, le nombre qui apparaît lorsque nous exécutons la fonction. Cela peut ne pas sembler très utile lorsque vous pouvez simplement parcourir quelques colonnes, mais imaginez combien de temps vous épargneriez si vous deviez le faire 50 fois sur une grande feuille de calcul Excel Vs. Accès - Une feuille de calcul peut-elle remplacer une base de données? Excel Vs. Accès - Une feuille de calcul peut-elle remplacer une base de données? Quel outil devriez-vous utiliser pour gérer les données? Access et Excel proposent tous les deux le filtrage, le classement et l’interrogation des données. Nous vous montrerons celui qui convient le mieux à vos besoins. Lire la suite contenant plusieurs centaines de colonnes!

Que la recherche commence

Microsoft Excel possède de nombreuses fonctions extrêmement puissantes. 3 Formules Crazy Excel qui font des choses étonnantes 3. Formules Crazy Excel qui font des choses étonnantes. Les formules de mise en forme conditionnelle dans Microsoft Excel peuvent faire des choses merveilleuses. Voici quelques astuces sur la productivité des formules Excel. Lisez plus et les quatre énumérés ci-dessus ne font qu'effleurer la surface. Même avec cet aperçu sommaire, vous devriez pouvoir gagner beaucoup de temps lorsque vous travaillez avec des feuilles de calcul volumineuses. Si vous voulez voir tout ce que vous pouvez faire de plus avec INDEX, consultez “L'indice imposant” chez Microsoft Excel Hero.

Comment faites-vous des recherches dans de grandes feuilles de calcul? Comment avez-vous trouvé ces fonctions de recherche utiles?? Partagez vos pensées et expériences ci-dessous!

Crédits d'image: loupe sur une feuille de calcul via Shutterstock

Explorer plus sur: Microsoft Excel, Spreadsheet.