En combinar la funció VLOOKUP d'Excel amb la funció COLUMN , podem crear una fórmula de cerca que us permeti retornar diversos valors d'una única fila d'una base de dades o taula de dades.
A l'exemple que es mostra a la imatge anterior, la fórmula de cerca facilita la devolució de tots els valors, com ara el preu, el número de part i el proveïdor, relacionats amb diverses peces de maquinari.
01 de 10
Retornar valors múltiples amb Excel VLOOKUP
Seguint els passos que es detallen a continuació, es crea la fórmula de cerca que es mostra a la imatge anterior que retornarà diversos valors d'un únic registre de dades.
La fórmula de cerca requereix que la funció COLUMNA sigui anida a dins de VLOOKUP.
L'anàlisi d'una funció consisteix a introduir la segona funció com un dels arguments per a la primera funció.
En aquest tutorial, la funció COLUMN s'introduirà com l'argument del número de l'índex de columnes per a VLOOKUP.
L'últim pas del tutorial consisteix a copiar la fórmula de cerca a columnes addicionals per tal de recuperar valors addicionals per a la part escollida.
Tutorials
- Introduir les dades del tutorial
- Crear un rang nomenat per a la taula de dades
- Comença la funció VLOOKUP
- Introduir l'argument de valor de cerca mitjançant referències de cel·les absolutes
- Introduir l'argument Array de taules
- S'està introduint la funció COLUMN anida
- Completar la funció VLOOKUP
- Còpia de la fórmula de cerca amb el controlador de farcit
- Recuperació de dades amb la fórmula de cerca
02 de 10
Introduïu les dades del tutorial
El primer pas del tutorial és introduir les dades en un full de càlcul d' Excel.
Per seguir els passos del tutorial, introduïu les dades que es mostren a la imatge anterior a les cel·les següents.
- Introduïu l' interval superior de dades a les cel·les D1 a G1
- Introduïu el segon interval a les cel·les D4 a G10
Els criteris de cerca i la fórmula de cerca creada durant aquest tutorial s'introdueixen a la fila 2 del full de càlcul.
El tutorial no inclou el format vist a la imatge, però això no afectarà la forma en què funciona la fórmula de cerca.
La informació sobre opcions de format similar a les que es mostren a dalt està disponible en aquest Tutorial bàsic de formatació d'Excel .
Tutorial Passos
- Introduïu les dades que es veuen a la imatge anterior a les cel·les D1 a G10
03 de 10
Crear un rang nomenat per a la taula de dades
Un rang amb nom és una manera fàcil de referir a un rang de dades en una fórmula. En lloc d'escriure les referències de les cel·les per a les dades, només podeu escriure el nom del rang.
Un segon avantatge per utilitzar un rang amb nom és que les referències de cel·les per a aquest rang mai no canvien encara que la fórmula es copiï a altres cel·les del full de treball.
Els noms de rang són, per tant, una alternativa a l'ús de referències de cel·les absolutes per evitar errors en copiar fórmules.
Nota: el nom del rang no inclou els encapçalaments ni els noms de camp per a les dades (fila 4), sinó només les dades.
Tutorial Passos
- Ressalteu les cel·les D5 a G10 al full de treball per seleccionar-les
- Feu clic al quadre de noms situat a sobre de la columna A
- Escriviu "Taula" (sense cometes) al quadre de noms
- Premeu la tecla ENTRAR al teclat
- Les cel·les D5 a G10 ara tenen el nom de rang de "Taula". Utilitzarem el nom de l'argument de matriu de la taula VLOOKUP més tard en el tutorial
04 de 10
Obrir el quadre de diàleg VLOOKUP
Encara que només es pot escriure la nostra fórmula de cerca directament en una cel·la d'un full de càlcul, moltes persones tenen dificultats per mantenir la sintaxi directament, especialment per a una fórmula complexa com la que estem utilitzant en aquest tutorial.
Una alternativa, en aquest cas, és utilitzar el quadre de diàleg VLOOKUP. Gairebé totes les funcions d'Excel tenen un quadre de diàleg que permet introduir cadascun dels arguments de la funció en una línia diferent.
Tutorial Passos
- Feu clic a la cel·la E2 del full de treball : la ubicació on es mostraran els resultats de la fórmula de cerca bidimensional
- Feu clic a la pestanya Fórmules de la cinta
- Feu clic a l'opció Cerca i referència a la cinta per obrir la llista desplegable de la funció
- Feu clic a VLOOKUP a la llista per obrir el quadre de diàleg de la funció
05 de 10
Introduir l'argument de valor de cerca mitjançant referències de cel·les absolutes
Normalment, el valor de cerca coincideix amb un camp de dades a la primera columna de la taula de dades.
En el nostre exemple, el valor de cerca fa referència al nom de la part del maquinari sobre el qual volem trobar informació.
Els tipus de dades admissibles per al valor de cerca són:
- dades de text
- un valor lògic (TRUE o FALSE només)
- un nombre
- una referència de cel·la a un valor del full de càlcul
En aquest exemple, introduirem la referència de la cel al lloc on es localitzarà el nom de la part: cel·la D2.
Referències de cèl · lules absolutes
En un pas posterior al tutorial, copiarem la fórmula de cerca a la cel·la E2 a les cel·les F2 i G2.
Normalment, quan es copien les fórmules a Excel, les referències de cel·les canvien per reflectir la seva nova ubicació.
Si això succeeix, D2 - la referència de la cel·la per al valor de la cerca - canviarà a mesura que es copiï la fórmula creant errors a les cel·les F2 i G2.
Per evitar els errors, convertirem la referència de cel·la D2 en una referència de cel·la absoluta .
Les referències absolutes de cel·la no canvien quan es copien les fórmules.
Les referències de cel·la absoluta es creen prement la tecla F4 del teclat. Si ho feu, afegiu signes de dòlar al voltant de la referència de la cel·la, com ara $ D $ 2
Tutorial Passos
- Feu clic a la línia de cerca_valor al quadre de diàleg
- Feu clic a la cel·la D2 per afegir aquesta referència de cel·la a la línia de cerca_valor . Aquesta és la cel·la on escriurem el nom de la part sobre el qual estem buscant informació
- Sense moure el punt d'inserció, premeu la tecla F4 del teclat per convertir D2 a la referència de cel·la absoluta $ D $ 2
- Deixeu obrir el quadre de diàleg de la funció VLOOKUP per al següent pas del tutorial
06 de 10
Introduir l'argument Array de taules
La matriu de taula és la taula de dades que la fórmula de cerca busca per trobar la informació que volem.
La matriu de taula ha de contenir almenys dues columnes de dades .
- la primera columna conté l'argument de valor de cerca (pas anterior al tutorial)
- el segon, i qualsevol columna addicional, serà cercat per la fórmula de cerca per trobar la informació que especifiqueu.
L'argument de la matriu de taula s'ha d'introduir com un interval que conté les referències de cel·la de la taula de dades o com a nom del rang .
Per a aquest exemple, utilitzarem el nom de rang creat al pas 3 del tutorial.
Tutorial Passos
- Feu clic a la línia table_array al quadre de diàleg
- Escriviu "Taula" (sense cometes) per introduir el nom del rang per a aquest argument
- Deixeu obrir el quadre de diàleg de la funció VLOOKUP per al següent pas del tutorial
07 de 10
S'està anul·lant la funció COLUMN
Normalment, VLOOKUP només retorna les dades d'una columna d'una taula de dades i aquesta columna està establerta per l'argument del número d'índex de columna .
En aquest exemple, però, tenim tres columnes que volem retornar dades, de manera que necessitem una manera de canviar fàcilment el número d'índex de columna sense editar la nostra fórmula de cerca.
Aquí és on entra la funció COLUMN. Introduint-lo com l'argument del número de l'índex de columna , canviarà a mesura que es copiï la fórmula de cerca de la cel·la D2 a les cel·les E2 i F2 més endavant al tutorial.
Funcions d'anidació
La funció COLUMN, per tant, actua com a argument de número d'índex de columna de VLOOKUP.
Això s'aconsegueix mitjançant l' anulació de la funció COLUMNA dins de VLOOKUP a la línia Col_index_num del quadre de diàleg.
Introduint la funció COLUMNA manualment
Quan funcions de nidificació, Excel no ens permet obrir el quadre de diàleg de la segona funció per introduir els seus arguments.
La funció COLUMN, per tant, s'ha d'introduir manualment a la línia Col_index_num .
La funció COLUMN té només un argument: l'argument de referència que és una referència de cel·la.
Selecció de l'argument de referència de la funció COLUMN
El treball de la funció COLUMN és retornar el número de la columna donada com a argument de referència .
En altres paraules, converteix la lletra de la columna en un número amb la columna A que és la primera columna, la columna B la segona i així successivament.
Com que el primer camp de dades que volem retornat és el preu de l'element, que es troba a la columna dos de la taula de dades, podem triar la referència de la cel·la per a qualsevol cel·la de la columna B com a Argument de referència per obtenir el número 2 l'argument Col_index_num .
Tutorial Passos
- Al quadre de diàleg de la funció VLOOKUP, feu clic a la línia Col_index_num
- Escriviu la columna de nom de la funció seguida d'un suport obert " ( "
- Feu clic a la cel·la B1 del full de treball per introduir la referència de la cel·la com a argument de referència
- Escriviu un clauer de tancament " ) " per completar la funció COLUMN
- Deixeu obrir el quadre de diàleg de la funció VLOOKUP per al següent pas del tutorial
08 de 10
Introduir l'argument VLOOKUP Range Lookup
L'argument Range_lookup de VLOOKUP és un valor lògic (TRUE o FALSE només) que indica si voleu que VLOOKUP trobeu una concordança exacta o aproximada al valor_Verup.
- Si és TRUE o si s'omet aquest argument , VLOOKUP torna una concordança exacta amb el valor_Veure, o si no es troba una concordança exacta, VLOOKUP torna el valor més proper. Per fer la fórmula, les dades de la primera columna de Table_array s'han d' ordenar en ordre ascendent .
- Si FALSE, VLOOKUP només utilitzarà una coincidència exacta amb el Valor_Vista. Si hi ha dos o més valors a la primera columna de Table_array que coincideixen amb el valor de cerca, s'utilitza el primer valor trobat. Si no es troba una coincidència exacta, es torna un error de # N / A.
En aquest tutorial, ja que estem buscant informació específica sobre un element de maquinari en particular, establirem Range_lookup igual a False .
Tutorial Passos
- Feu clic a la línia Range_lookup al quadre de diàleg
- Escriviu la paraula False en aquesta línia per indicar que volem que VLOOKUP retorni una concordança exacta de les dades que cerquem
- Feu clic a D'acord per completar la fórmula de cerca i tancar el quadre de diàleg
- Com que encara no hem introduït els criteris de cerca a la cel·la D2, un error N / A estarà present a la cel·la E2
- Aquest error es corregirà quan afegirem els criteris de cerca en l'últim pas del tutorial
09 de 10
Còpia de la fórmula de cerca amb el controlador de farcit
La fórmula de cerca pretén recuperar dades de diverses columnes de la taula de dades alhora.
Per fer-ho, la fórmula de cerca ha de residir en tots els camps dels quals volem informació.
En aquest tutorial, volem recuperar dades de les columnes 2, 3 i 4 de la taula de dades: aquest és el preu, el número de la part i el nom del proveïdor quan introduïm un nom de part com a valor de cerca.
Atès que les dades es presenten en un patró habitual del full de treball , podem copiar la fórmula de cerca a la cel·la E2 a les cel·les F2 i G2.
A mesura que es copia la fórmula, Excel actualitzarà la referència relativa de la cel·la a la funció COLUMNA (B1) per reflectir la nova ubicació de la fórmula.
A més a més, Excel no canvia la referència de cel·la absoluta de $ D $ 2 i la taula de rang amb el nom de la fórmula es copia.
Hi ha més d'una manera de copiar dades a Excel, però probablement la forma més senzilla és utilitzar l' identificador de farciment .
Tutorial Passos
- Feu clic a la cel·la E2 - on hi ha la fórmula de cerca - per convertir-la en la cel·la activa
- Col·loqueu el punter del ratolí sobre el quadrat negre a l'extrem inferior dret. El punter canviarà a un signe més " + " - aquest és l'identificador de farciment
- Feu clic al botó esquerre del ratolí i arrossegueu l'identificador de farcit a la cel·la G2
- Allibera el botó del ratolí i la cel·la F3 ha de contenir la fórmula de cerca bidimensional
- Si es fa correctament, les cel·les F2 i G2 també haurien d'incloure també l'error # N / A que hi ha a la cel·la E2
10 de 10
Introduir els criteris de cerca
Una vegada que la fórmula de cerca s'ha copiat a les cel·les requerides , es pot utilitzar per recuperar informació de la taula de dades.
Per fer-ho, escriviu el nom de l'element que voleu recuperar a la cel·la Lookup_value (D2) i premeu la tecla ENTRAR al teclat.
Una vegada fet, cada cel·la que conté la fórmula de cerca ha de contenir una altra informació sobre l'element de maquinari que cerqueu.
Tutorial Passos
- Feu clic a la cel·la D2 del full de treball
- Escriviu Widget a la cel·la D2 i premeu la tecla ENTRAR al teclat
- La informació següent s'ha de mostrar a les cel·les E2 a G2:
- E2 - $ 14.76 - el preu d'un widget
- F2 - PN-98769: el número de part d'un widget
- G2 - Widgets Inc. - el nom del proveïdor dels widgets
- Proveu també la fórmula de la fórmula VLOOKUP escrivint el nom d'altres parts a la cel·la D2 i observant els resultats a les cel·les E2 a G2
Si hi ha un missatge d'error com #REF! apareix a les cel·les E2, F2 o G2, aquesta llista de missatges d'error VLOOKUP us pot ajudar a determinar on està el problema.