En combinant ExcelVLOOKUP fonctionner avec leCOLONNE fonction nous pouvons créer une formule de recherche qui vous permet de renvoyer plusieurs valeurs d’une seule ligne d’une base de données ou d’un tableau de données.
01 sur 10Renvoyer plusieurs valeurs avec Excel VLOOKUP
Suivre les étapes répertoriées ci-dessous crée la formule de recherche visible dans l'image ci-dessus qui renverra plusieurs valeurs à partir d'un seul enregistrement de données.
La formule de recherche nécessite que le COLONNE la fonction est imbriquée à l'intérieur de VLOOKUP. L'imbrication d'une fonction implique d'entrer la seconde fonction comme l'un des arguments de la première fonction.
Dans ce tutoriel, le COLONNE la fonction sera entrée en tant que index de colonne nombre argument pour VLOOKUP. La dernière étape du didacticiel consiste à copier la formule de recherche dans des colonnes supplémentaires afin de récupérer des valeurs supplémentaires pour la pièce choisie.
Entrez les données du didacticiel
La première étape du didacticiel consiste à saisir les données dans une feuille de calcul Excel. Pour suivre les étapes du didacticiel, entrez les données affichées dans l'image ci-dessus dans les cellules suivantes:
- Entrez la première plage de données dans cellules D1 à G1.
- Entrez la deuxième plage dans cellules D4 à G10.
Les critères de recherche et la formule de recherche créés lors de ce tutoriel seront entrés dans rangée 2 de la feuille de calcul.
Le didacticiel n'inclut pas la mise en forme visible dans l'image, mais cela n'affectera pas le fonctionnement de la formule de recherche. Des informations sur les options de formatage similaires à celles présentées ci-dessus sont disponibles dans ce didacticiel de formatage Excel de base.
03 sur 10Création d'une plage nommée pour la table de données
Une plage nommée est un moyen facile de faire référence à une plage de données dans une formule. Plutôt que de taper les références de cellule pour les données, vous pouvez simplement taper le nom de la plage.
Le deuxième avantage de l'utilisation d'une plage nommée est que les références de cellule de cette plage ne changent jamais, même lorsque la formule est copiée dans d'autres cellules de la feuille de calcul. Les noms de plage constituent donc une alternative à l'utilisation de références de cellules absolues pour éviter les erreurs lors de la copie de formules.
Le nom de la plage n'inclut pas les en-têtes ni les noms de champs pour les données (rangée 4) mais seulement les données elles-mêmes.
- Surligner cellules D5 à G10 dans la feuille de travail pour les sélectionner.
- Clique sur le Nom Box situé au dessus colonne A.
- Type Table dans le Nom Box.
- appuyez sur la Entrer touche du clavier.
- Cellules D5 à G10 maintenant le nom de la gamme de Table. Nous allons utiliser le nom pour le VLOOKUP tableau de table argument plus tard dans le tutoriel.
Ouverture de la boîte de dialogue VLOOKUP
Bien qu'il soit possible de taper notre formule de recherche directement dans une cellule d'une feuille de calcul, de nombreuses personnes ont du mal à conserver la syntaxe exacte, en particulier pour une formule complexe telle que celle que nous utilisons dans ce didacticiel.
Une alternative, dans ce cas, consiste à utiliser le VLOOKUP boite de dialogue. Presque toutes les fonctions d'Excel ont une boîte de dialogue qui vous permet de saisir chacun des arguments de la fonction sur une ligne séparée.
Étapes du tutoriel
- Cliquer sur cellule E2 de la feuille de calcul - l'emplacement où les résultats de la formule de recherche bidimensionnelle seront affichés.
- Clique sur le Onglet Formules du ruban.
- Clique sur le Recherche et référence option dans le ruban pour ouvrir la liste déroulante des fonctions.
- Cliquer sur VLOOKUP dans la liste pour ouvrir la boîte de dialogue de la fonction.
Saisie de l'argument de valeur de recherche
Normalement, le Chercher valeur correspond à un champ de données dans la première colonne de la table de données. Dans notre exemple, le Chercher valeur fait référence au nom de la partie matérielle sur laquelle nous souhaitons trouver des informations. Les types de données autorisés pour le Chercher valeur sont des données textuelles, des valeurs logiques, des nombres et des références de cellules.
Références de cellules absolues
Normalement, lorsque les formules sont copiées dans Excel, les références de cellule changent pour refléter leur nouvel emplacement. Si ça arrive, D2, la référence de cellule pour le Chercher valeur, changera à mesure que la formule sera copiée, créant des erreurs dans cellules F2 et G2.
Les références de cellules absolues ne changent pas lorsque les formules sont copiées.
Pour éviter les erreurs, nous allons convertir la référence de cellule D2 dans une référence de cellule absolue. Les références absolues de cellules sont créées en appuyant sur la touche F4 touche du clavier. Cela ajoute des signes dollar autour de la référence de la cellule, tels que $ D $ 2.
- Clique sur le lookup_value ligne dans la boîte de dialogue.
- Cliquer sur cellule D2 d'ajouter cette référence de cellule à la lookup_value ligne - c'est la cellule où nous allons taper le nom de la pièce sur laquelle nous cherchons des informations
- Sans déplacer le point d’insertion, appuyez sur le bouton F4 touche sur le clavier pour convertir D2 dans la référence absolue de la cellule $ D $ 2.
- Quitter le VLOOKUP boîte de dialogue de fonction ouverte pour l’étape suivante du didacticiel.
Saisie de l'argument Tableau de tableaux
Le tableau de table est la table de données que la formule de recherche recherche pour trouver les informations souhaitées.Le tableau doit contenir au moins deux colonnes de données.
La première colonne contient l'argument de valeur de recherche (étape précédente du didacticiel), tandis que la seconde colonne sera recherchée par la formule de recherche pour trouver les informations spécifiées.
L'argument du tableau doit être entré comme une plage contenant les références de cellule pour la table de données ou comme un nom de plage.
- Clique sur le table_array ligne dans la boîte de dialogue.
- Type Table pour entrer le nom de la plage pour cet argument.
- Quitter le VLOOKUP boîte de dialogue de fonction ouverte pour l’étape suivante du didacticiel.
Imbrication de la fonction COLUMN
Normalement VLOOKUP renvoie uniquement les données d'une colonne d'un tableau de données et cette colonne est définie par le numéro d'index de colonne argument.
Dans cet exemple, cependant, nous souhaitons renvoyer les données à partir de trois colonnes. Nous avons donc besoin d’un moyen de modifier facilement l’affichage. numéro d'index de colonne sans modifier notre formule de recherche - c’est là que COLONNE la fonction entre.
En y entrant comme le numéro d'index de colonne argument, cela changera au fur et à mesure que la formule de recherche sera copiée cellule D2 à cellules E2 et F2 plus tard dans le tutoriel.
Fonctions d'imbrication
le COLONNE fonction agit donc comme VLOOKUPde numéro d'index de colonne argument; ceci est accompli en emboîtant le COLONNE fonctionner à l'intérieur de VLOOKUP dans le Col_index_num ligne de la boîte de dialogue.
Entrer manuellement la fonction COLUMN
Lors de l'imbrication de fonctions, Excel ne nous permet pas d'ouvrir la boîte de dialogue de la deuxième fonction pour entrer ses arguments. le COLONNE La fonction doit donc être saisie manuellement dans Col_index_num ligne. le COLONNE la fonction n'a qu'un seul argument - le Référence argument qui est une référence de cellule.
Choix de l'argument de référence de la fonction COLUMN
le COLONNE Le travail de la fonction est de renvoyer le numéro de la colonne donné comme Référence argument. En d’autres termes, il convertit la lettre de la colonne en un nombre avec colonne A étant la première colonne, colonne B le second et ainsi de suite.
Depuis le premier champ de données, nous voulons obtenir le prix de l'article, qui figure dans la deuxième colonne du tableau de données. Nous pouvons choisir la référence de cellule pour n'importe quelle cellule colonne B comme le Référence Argument afin d'obtenir le numéro 2 pour le Col_index_num argument.
- dans le VLOOKUP boîte de dialogue de fonction, cliquez sur le bouton Col_index_num ligne.
- Tapez le nom de la fonction colonne suivi d'un support rond ouvert.
- Cliquer sur cellule B1 dans la feuille de travail pour entrer cette référence de cellule en tant que Référence argument.
- Type A parenthèse de fermeture pour compléter le COLONNE une fonction
- Quitter le VLOOKUP boîte de dialogue de fonction ouverte pour l’étape suivante du didacticiel.
Entrée de l'argument de recherche de plage VLOOKUP
VLOOKUPde Range_lookup l'argument est une valeur logique (VRAI ou FAUX) qui indique si vous voulez VLOOKUP pour trouver une correspondance exacte ou approximative à la Lookup_value.
- Si VRAI ou si cet argument est omis: VLOOKUP renvoie soit une correspondance exacte à la Lookup_valueOu, si aucune correspondance exacte n'est trouvée, VLOOKUP renvoie la valeur suivante la plus grande. Pour que la formule puisse faire cela, les données de la première colonne de Tableau_Array doit être trié par ordre croissant.
- Si FAUX: VLOOKUP utilisera uniquement une correspondance exacte à la Lookup_value. S'il y a deux ou plusieurs valeurs dans la première colonne de Tableau_Array qui correspond à la valeur de recherche, la première valeur trouvée est utilisée. Si aucune correspondance exacte n’est trouvée, un #N / A l'erreur est renvoyée.
Dans ce tutoriel, étant donné que nous recherchons des informations spécifiques sur un élément matériel particulier, nous allons définir: Range_lookup égal à Faux.
- Clique sur le Range_lookup ligne dans la boîte de dialogue.
- Entrez le mot Faux dans cette ligne pour indiquer que nous voulons VLOOKUP pour renvoyer une correspondance exacte pour les données que nous recherchons.
- Cliquez sur D'accord pour compléter la formule de recherche et fermer la boîte de dialogue.
- Comme nous n’avons pas encore saisi les critères de recherche dans cellule D2 un Erreur N / A sera présent dans cellule E2.
- Cette erreur sera corrigée lorsque nous ajouterons les critères de recherche à la dernière étape du didacticiel.
Copier la formule de recherche
La formule de recherche est destinée à récupérer des données de plusieurs colonnes du tableau de données à la fois. Pour ce faire, la formule de recherche doit résider dans tous les champs à partir desquels nous voulons des informations.
Dans ce tutoriel, nous voulons récupérer les données de colonnes 2, 3, et 4 de la table de données - c’est-à-dire le prix, le numéro de pièce et le nom du fournisseur lorsque nous saisissons un nom partiel en tant que Lookup_value.
Comme les données sont présentées selon un modèle régulier dans la feuille de calcul, nous pouvons copier la formule de recherche dans cellule E2 à cellules F2 et G2.
Lors de la copie de la formule, Excel mettra à jour la référence de cellule relative dans le COLONNE une fonction (cellule B1) pour refléter le nouvel emplacement de la formule.
De plus, Excel ne modifie pas la référence de cellule absolue $ D $ 2 et la gamme nommée Table comme la formule est copiée.
Il existe plus d’un moyen de copier des données dans Excel, mais le moyen le plus simple consiste probablement à utiliser le Poignée de remplissage.
- Cliquer sur cellule E2, où se trouve la formule de recherche, pour en faire la cellule active.
- Placer le pointeur de la souris au dessus de carré noir dans le coin inférieur droit - le pointeur se changera en un signe plus pour indiquer la poignée de remplissage.
- Cliquez sur le bouton gauche de la souris et faites glisser la poignée de recopie à travers cellule G2.
- Libération le bouton de la souris et cellule F3 devrait contenir la formule de recherche bidimensionnelle.
- Si fait correctement, cellules F2 et G2 devrait maintenant contenir également la #N / A erreur présente dans cellule E2.
Saisie des critères de recherche
Une fois que la formule de recherche a été copiée dans les cellules requises, elle peut être utilisée pour extraire des informations de la table de données. Pour ce faire, tapez le nom de l'article vous souhaitez récupérer dans le Lookup_value cellule (D2) et appuyez sur le Entrer touche du clavier.
Une fois cela fait, chaque cellule contenant la formule de recherche doit contenir une donnée différente sur l’élément matériel que vous recherchez.
- Cliquer sur cellule D2 dans la feuille de calcul.
- Type Widget dans cellule D2 et appuyez sur le Entrer touche du clavier.
- Les informations suivantes doivent être affichées dans cellules E2 à G2.
- E2: 14,76 $ - le prix d'un widget
- F2: PN-98769 - le numéro de pièce pour un widget
- G2: Widgets Inc. - le nom du fournisseur pour les widgets
- Testez le VLOOKUP formule de tableau en outre en tapant le nom des autres parties dans cellule D2 et en observant les résultats dans cellules E2 à G2
