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 très grande feuille de calcul Comment diviser une feuille de calcul CSV Excel énorme en fichiers séparés Comment diviser une feuille de calcul CSV Excel énorme en fichiers séparés L'un des défauts 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 fractionner un gros fichier CSV, lisez la suite! Lire la suite, il peut gagner beaucoup de temps pour utiliser l'une de ces quatre fonctions de recherche. Peu importe la taille de votre document Microsoft Excel, ils seront beaucoup plus efficaces.
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). Deux exemples où vous pourriez faire ceci recherchent le nom de famille d'un employé par leur numéro d'employé ou trouver un numéro de téléphone en spécifiant un nom de famille. Voici la syntaxe de la fonction:
= VLOOKUP ([lookup_value], [table_array], [col_index_num], [range_lookup])
La [valeur_recherche] est l'information que vous possédez déjà. Par exemple, si vous avez besoin de connaître l'état d'une ville, ce serait le nom de la ville. [table_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 qui contient la valeur de retour.
[range_lookup] est un argument optionnel, et 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 suivant le plus bas. Ainsi, dans l'image ci-dessous, un VLOOKUP recherchant un score SAT de 652 renvoie 646, car il s'agit du nombre le plus proche de la liste inférieur à 652, et [range_lookup] prend par défaut la valeur 1.
Jetons un coup d'oeil à la façon dont nous pourrions utiliser cela. Comme je l'ai dans mes articles sur les opérateurs booléens dans Microsoft Excel Mini Tutoriel Excel: Utiliser la logique booléenne pour traiter les données complexes Tutoriel Excel Mini: Utiliser la logique booléenne pour traiter les données complexes Les opérateurs logiques IF, NOT, AND et OR, peuvent vous aider Débutant Excel pour alimenter l'utilisateur. Nous expliquons les bases de chaque fonction et montrons comment vous pouvez les utiliser pour obtenir un maximum de résultats. Excel Plus Didacticiel: Utilisation du comptage avancé et ajout de fonctions dans Excel Le comptage et l'ajout de formules peuvent sembler banal par rapport aux formules Excel plus avancées. Mais ils peuvent vous aider à économiser beaucoup de temps lorsque vous avez besoin de collecter des informations sur les données de votre feuille de calcul. En savoir plus, je vais utiliser une feuille de calcul générée avec generatedata.com. Il contient des numéros d'identification, des prénoms et des noms, des villes, des états et des scores SAT. Disons que je veux trouver le score SAT d'une personne avec le nom de famille "Winters". VLOOKUP le rend facile. Voici la formule que nous utiliserions:
= VLOOKUP ("Winters", C2: F101, 4, 0) Parce que les scores SAT sont la quatrième colonne de la colonne de nom, j'ai utilisé 4 pour l'argument d'index de colonne. Notez que lorsque vous recherchez du texte, définir [range_lookup] sur 0 est une bonne idée; sans cela, vous pouvez obtenir de mauvais résultats. Voici ce que Microsoft Excel nous offre:
Il a retourné 651, le score SAT appartenant à l'étudiant nommé Kennedy Winters, qui est dans la rangée 92 (affiché dans l'encart ci-dessus). Il aurait fallu beaucoup plus de temps pour faire défiler 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. 5 Formules Excel Vous devez savoir faire vos impôts? 5 formules Excel que vous devez connaître Deux jours avant la date d'échéance de vos taxes et vous ne voulez pas payer d'autres frais de dépôt tardif. C'est le moment de tirer parti de la puissance d'Excel pour tout mettre en ordre. Lire la suite .
Notes sur VLOOKUP
Certaines choses sont bonnes à retenir 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 renverra 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 retournera qu'une seule valeur. Si nous avions utilisé "Georgia" comme valeur de recherche, cela aurait renvoyé le score du premier étudiant de Géorgie, et donné aucune indication qu'il y avait en fait deux étudiants de Géorgie.
La fonction HLOOKUP
Lorsque VLOOKUP trouve des valeurs correspondantes dans une autre colonne, HLOOKUP recherche les valeurs correspondantes dans une ligne différente. Parce qu'il est généralement plus facile de parcourir les en-têtes de colonnes jusqu'à ce que vous trouviez le bon et d'utiliser un filtre pour trouver ce que vous cherchez, HLOOKUP est mieux utilisé lorsque vous avez des feuilles de calcul très volumineuses ou que vous travaillez avec des valeurs . Voici la syntaxe:
= HLOOKUP ([lookup_value], [table_array], [row_index_num], [range_lookup])
[lookup_value], encore une fois, est la valeur que vous connaissez et que vous voulez trouver une valeur correspondante. [table_array] correspond aux cellules dans lesquelles vous souhaitez effectuer une recherche. [row_index_num] spécifie la ligne dont proviendra la valeur de retour. Et [range_lookup] est le même que ci-dessus; laissez le 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 generatedata.com. Celui-ci contient une ligne pour chaque état, avec un score SAT (nous dirons que c'est le score moyen pour l'état) dans les années 2000-2014. Nous allons utiliser HLOOKUP pour trouver le score moyen au Minnesota en 2013. Voici comment nous allons le faire:
= HLOOKUP (2013, A1: P51, 24)
(Notez que 2013 n'est pas entre guillemets parce que c'est un nombre, et pas une chaîne, aussi, le 24 vient du Minnesota étant dans la 24e rangée.) Comme vous pouvez le voir dans l'image ci-dessous, le score est retourné:
Minnesotans en moyenne un score de 1014 en 2013.
Notes sur HLOOKUP
Comme avec VLOOKUP, la valeur de recherche doit être dans la première ligne de votre tableau tableau; c'est rarement un problème avec HLOOKUP, car vous utiliserez généralement un titre de colonne pour une valeur de recherche. HLOOKUP ne renvoie qu'une seule valeur.
Les fonctions INDEX et MATCH
INDEX et MATCH sont deux fonctions différentes, mais lorsqu'elles sont utilisées ensemble, elles peuvent accélérer considérablement la recherche dans une grande feuille de calcul. Les deux fonctions ont des inconvénients, mais en les combinant nous allons construire sur les forces des deux. D'abord, cependant, la syntaxe:
= INDEX ([array], [row_number], [column_number]) = MATCH ([valeur de recherche], [table de recherche], [type de correspondance])
Dans INDEX, [tableau] est le tableau dans lequel vous allez chercher. [row_number] et [column_number] peuvent être utilisés pour affiner votre recherche. nous allons jeter un coup d'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 retournera que votre terme exact; et -1 renvoie 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, alors je vais exposer ici. MATCH prend un terme de recherche et renvoie 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.
D'autre part, INDEX fait le contraire: il prend une référence de cellule et renvoie la valeur qu'il contient. Vous pouvez voir ici que, lorsqu'on lui dit de retourner la sixième cellule de la colonne Ville, INDEX renvoie "Anchorage", la valeur de la ligne 6.
Ce que nous allons faire est de combiner les deux afin que MATCH renvoie une référence de cellule et INDEX utilise cette référence pour rechercher la valeur dans une cellule. Disons que vous vous souvenez qu'il y avait un étudiant dont le nom de famille était Waters, et vous voulez voir ce que le score de cet élève était. Voici la formule que nous utiliserons:
= INDEX (F: F, MATCH ("Eaux", C: C, 0)) Vous remarquerez que le type de correspondance est défini sur 0 ici; Lorsque vous cherchez une chaîne, c'est ce que vous voulez utiliser. Voici ce que nous obtenons lorsque nous exécutons cette fonction:
Comme vous pouvez le voir à partir de l'encart, Owen Waters a marqué 1720, le nombre qui apparaît lorsque nous exécutons la fonction. Cela peut ne pas sembler si utile quand vous pouvez juste regarder quelques colonnes plus, mais imaginez combien de temps vous économiserez si vous deviez le faire 50 fois sur une grande feuille de calcul de base de données Excel Vs. Accès - Une feuille de calcul peut-elle remplacer une base de données? Vs Excel Accès - Une feuille de calcul peut-elle remplacer une base de données? Quel outil devez-vous utiliser pour gérer les données? Access et Excel proposent tous deux un filtrage, une collation et une interrogation de données. Nous allons vous montrer lequel est le mieux adapté à vos besoins. Lire la suite qui contenait plusieurs centaines de colonnes!
Laisser la recherche commencer
Microsoft Excel a beaucoup de fonctions extrêmement puissantes 3 Formules folles d'Excel qui font des choses étonnantes 3 Formules folles d'Excel qui font des choses étonnantes La puissance de Microsoft Excel réside dans ses formules. Laissez-moi vous montrer les merveilles que vous pouvez faire avec les formules et le formatage conditionnel dans trois exemples utiles. Lire la suite, et les quatre énumérés ci-dessus juste gratter la surface. Même avec cette vue d'ensemble rapide, cependant, vous devriez être en mesure d'économiser beaucoup de temps lorsque vous travaillez avec de grandes feuilles de calcul. Si vous voulez voir ce que vous pouvez faire de plus avec INDEX, consultez "The Imposing Index" sur 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 image: loupe sur une feuille de calcul Via Shutterstock