Utilisation de la fonction VLookup dans Excel 2007
Dans cet exemple, vous créez trois colonnes calculées en utilisant la fonction RECHERCHEV dans une variété de façons. Il utilise des informations sur les étudiants dans une université d'état hypothétique.
Ouvrir et examiner StudentInfo
Les élèves contient des informations clés sur les étudiants inscrits. Ceci est la feuille de calcul que vous allez modifier.
GPA énumère les élèves de la moyenne trimestrielle et moyenne pondérée cumulative annuelle, sur une échelle allant de 0 à 4,0.
Grades contient une table de conversion GPA à lettre. (Il contient également une interface utilisateur au fond qui peut être utilisé dans une procédure de calcul d'une année d'une valeur GPA. Cette question est abordée dans la section Utilisation RECHERCHEV de VBA.)
Ce classeur représente un échantillon d'un classeur réel qui pourrait contenir des informations sur des milliers ou des dizaines de milliers d'étudiants.
Ajout d'une colonne e-mail d'amis
Les paires universitaires de première année tous les entrants entre eux dans un système de « compagnonnage ». Actuellement dans la feuille de calcul des étudiants, la colonne de classe d'amis liste l'ID de l'étudiant correspondant. Bien que ces informations sont disponibles via une recherche manuelle dans cette même table, pour permettre un accès plus facile, une autre colonne sera ajouté l'e-mail de l'ami. Pour ce faire, vous utiliserez la fonction VLOOKUP pour rechercher dans la même table en utilisant une correspondance exacte.
Pour créer la colonne email ami
H2 est la cellule contenant la valeur (391885) à rechercher.
A2: H23 est le tableau de table pour effectuer la recherche. Notez le premier, le plus à gauche colonne, ici A (ID). représente la colonne pour rechercher la valeur de notre recherche.
7 est l'indice de la colonne dont la cellule contient la valeur renvoyée de la fonction, en supposant qu'une correspondance soit trouvée pour la valeur de référence.
FAUX précise qu'une recherche exacte doit être effectuée.
Modifier la formule de I2 de sorte que la matrice de table utilise des références de cellules absolues:
Cela ne devrait pas changer la valeur calculée I2, mais il est nécessaire pour l'étape suivante.
Copier la formule dans I2 cellulaire à des cellules I3 I23 à travers. La meilleure façon d'y arriver est de remplir automatiquement cette colonne en faisant glisser la poignée inférieure droite sur I2 jusqu'à I23. Le reste des adresses e-mail de contacts calculés devrait maintenant afficher. Cliquez plusieurs de ces cellules pour examiner la formule LOOKUP sous-jacente.
Ajout d'une colonne pour confirmer la compatibilité d'amis
Buddies sont habituellement exigés pour être dans la même classe de fin d'études. Pour aider à surveiller cette proscription, une nouvelle colonne est demandé de garder une trace de cette relation. Encore une fois, vous utiliserez VLOOKUP pour rechercher dans la même table en utilisant une correspondance exacte, mais les résultats seront ensuite utilisés dans un autre calcul.
Pour créer la colonne de validation de classe paire
Cette formule compare la classe de fin d'études de l'étudiant actuel avec celui de son copain et renvoie le résultat booléen. Notez que cette fois que la table est décrite par une série de colonnes, A: H. Ceci est plus approprié pour les tables où les lignes sont dynamiquement ajoutés et supprimés.
Copier cette formule pour le reste des cellules dans cette colonne, les cellules J3 par J23.
Notez que les deux étudiants jumelés, M. White et M. Xie, sont dans les différentes classes de fin d'études.
Ajout d'une colonne de grade
Enfin, une demande a été reçue d'étendre cette feuille de calcul avec la note moyenne des étudiants en cours. Pour ce faire, vous effectuez une recherche imbriquée à l'aide des tables sur les autres feuilles de calcul.
Une recherche est effectuée en utilisant le résultat de la recherche précédente, la recherche sur la table dans la feuille de GPA, et le retour de la sixième colonne. Notez ce qui suit:
La plage nommée GradesCnv est utilisé au lieu de Grades A2: B12. Ceci est juste une commodité raccourcie.
Le type de recherche est approximative (FAUX) et donc la première colonne (GPA) dans la feuille de Grades doit être commandé croissant.
Copier cette formule pour le reste des cellules de cette colonne, les cellules K3 par K23.
Les étudiants Résultant Feuille de travail
Après avoir ajouté ces trois nouvelles colonnes, la feuille de calcul des élèves résultant doit apparaître comme la figure 1 (sauf que dans la figure, les colonnes D-G ont été cachés pour économiser de l'espace).
Figure 1. Les élèves feuille après ajout de colonnes.
Cette fonction peut être utilisée pour trouver des informations dans une seule feuille de calcul ou, plus généralement, d'utiliser une valeur connue dans une feuille de calcul pour rechercher des données associées à une rangée d'une autre, feuille de calcul liée.
valeur_cherchée est la valeur à rechercher dans la première colonne du tableau de la table. Ce paramètre peut contenir une valeur ou une référence.
Un match de texte est insensible à la casse, mais ne considère espaces, non-impression et des caractères spéciaux. Vous pouvez utiliser les fonctions TRIM ou propre pour enlever ces caractères et EXACT pour comparer les cas.
Conditions d'erreur
Les conditions suivantes se traduira par des valeurs d'erreur retournés:
Si valeur_cherchée est inférieure à la valeur la plus faible dans la première colonne de table_array, RECHERCHEV renvoie # N / A.
Si col_index_num est inférieur à 1, #value alors! est renvoyée; si supérieur au nombre de colonnes dans table_array. #REF alors! est retourné.
Si une correspondance est introuvable, # N / A est retourné.
En utilisant RECHERCHEV de VBA
Note: Le code VBA ne fonctionne dans des feuilles de calcul les macros et les modèles. Si vous voulez ajouter et tester ce code, enregistrez le fichier compagnon StudentInfo.xlsx comme une feuille de calcul macros nommé StudentInfo.xlsm.