Combinant Excel'sVLOOKUP Funciona amb laCOLUMNA funció podem crear una fórmula de cerca que us permetreu retornar diversos valors d'una única fila d'una base de dades o taula de dades.
01 de 10Retornar 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 COLUMNA La funció està amagada 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, el COLUMNA la funció s'introduirà com a índex de columna número argument 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.
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 el rang superior de dades a cel·les D1 a G1.
- Introduïu el segon interval cel·les D4 a G10.
Els criteris de cerca i la fórmula de cerca creada durant aquest tutorial es realitzaran fila 2 del full de treball.
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.
03 de 10Crear 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.
El nom del rang no inclou els encapçalaments ni els noms de camp per a les dades (fila 4) però només les mateixes dades.
- Ressaltar cel·les D5 a G10 en el full de treball per seleccionar-los.
- Feu clic al Quadre de noms situat a dalt columna A.
- Escriviu Taula al Quadre de noms.
- Premeu el botó Entra tecla al teclat.
- Cèl·lules D5 a G10 ara teniu el nom del rang de Taula. Utilitzarem el nom per a la VLOOKUP matriu de taula argument més tard en el tutorial.
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 la VLOOKUP Caixa de diàleg. 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
- Fer clic a cel·la E2 del full de treball: la ubicació on es mostraran els resultats de la fórmula de cerca bidimensional.
- Feu clic al Fitxa de fórmules del cinta.
- Feu clic al Cerca i referència opció a la cinta per obrir el menú desplegable de la funció.
- Fer clic a VLOOKUP a la llista per obrir el quadre de diàleg de la funció.
Introduir l'argument de valor de cerca
Normalment, el Cercar valor coincideix amb un camp de dades a la primera columna de la taula de dades. En el nostre exemple, el Cercar valor es refereix al nom de la part del maquinari sobre el qual volem trobar informació. Els tipus de dades permesos per al Cercar valor són dades de text, valors lògics, números i referències de cel·les.
Referències de cèl · lules absolutes
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 cel·la per a la Cercar valor, canviarà a mesura que es copiï la fórmula creant errors en cèl·lules F2 i G2.
Les referències absolutes de cel·la no canvien quan es copien les fórmules.
Per evitar els errors, convertirem la referència de la cel·la D2 en una referència de cel·la absoluta. Les referències de cel · les absolutes es creen prement la tecla F4 tecla al teclat. Si ho feu, afegiu signes de dòlar al voltant de la referència de la cel·la, com ara $ D $ 2.
- Feu clic al valor_requisició línia al quadre de diàleg.
- Fer clic a cel·la D2 per afegir aquesta referència a la cel · la valor_requisició línia: aquesta és la cel·la on escriureem el nom de la part sobre el qual estem buscant informació
- Sense moure el punt d'inserció, premeu la tecla F4 tecla al teclat per convertir D2 a la referència de cel·la absoluta $ D $ 2.
- Deixeu-ho VLOOKUP quadre de diàleg de funció obert per al següent pas del tutorial.
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), mentre que la recerca de la segona columna serà cercada per trobar la informació que especifiquem.
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.
- Feu clic al table_array línia al quadre de diàleg.
- Escriviu Taula per introduir el nom del rang per a aquest argument.
- Deixeu-ho VLOOKUP quadre de diàleg de funció obert per al següent pas del tutorial.
S'està anul·lant la funció COLUMN
Normalment VLOOKUP només retorna dades d'una columna d'una taula de dades i aquesta columna la defineix número d'índex de columna argument
En aquest exemple, però, tenim tres columnes que volem retornar dades, de manera que necessitem una manera de canviar fàcilment la número d'índex de columna sense editar la nostra fórmula de cerca: aquí és on COLUMNA la funció entra.
Introduint-lo com a número d'índex de columna argument, canviarà a mesura que es copia la fórmula de cerca cel·la D2 a cel·les E2 i F2 més endavant en el tutorial.
Funcions d'anidació
El COLUMNA La funció, per tant, actua com VLOOKUP's número d'índex de columna argument; això s'aconsegueix en nesting the COLUMNA Funciona dins de VLOOKUP a la Col_index_nom línia 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. El COLUMNA La funció, per tant, s'ha d'introduir manualment a la Col_index_nom línia El COLUMNA La funció només té un argument: el Referència argument que és una referència de cel·la.
Selecció de l'argument de referència de la funció COLUMN
El COLUMNA El treball de la funció és retornar el número de la columna donada com a Referència argument En altres paraules, converteix la lletra de la columna en un nombre amb columna A sent la primera columna, columna B la segona i així successivament.
Des del primer camp de dades, el que volem és el preu del document, que es troba a la columna dos de la taula de dades. Podem triar la referència de la cel·la en qualsevol cel·la columna B com el Referència Argument per obtenir el número 2 per a la Col_index_nom argument
- A la VLOOKUP quadre de diàleg de funció, feu clic a la icona Col_index_nom línia
- Escriviu el nom de la funció columna seguit d'un obre el braç de la ronda.
- Fer clic a cel·la B1 en el full de càlcul per entrar a la referència de la cel · la com a Referència argument
- Escriu a tancament de braç rodó per completar la COLUMNA funció
- Deixeu-ho VLOOKUP quadre de diàleg de funció obert per al següent pas del tutorial.
Introduir l'argument VLOOKUP Range Lookup
VLOOKUP's Range_lookup l'argument és un valor lògic (TRUE o FALS) que indica si voleu VLOOKUP per trobar una coincidència exacta o aproximada amb la Valor_valueu.
- Si TRUE o si s'omet aquest argument: VLOOKUP torna una coincidència exacta amb la Valor_valueuO, si no es troba una concordança exacta, VLOOKUP torna el valor més proper següent. Per fer la fórmula, les dades de la primera columna de Table_array s'han de classificar en ordre ascendent.
- Si FALS: VLOOKUP només usarà una coincidència exacta amb la Valor_valueu. Si hi ha dos o més valors a la primera columna de Table_array que coincideixin amb el valor de cerca, s'utilitza el primer valor trobat. Si no es troba una coincidència exacta, a # N / A es torna l'error.
En aquest tutorial, ja que estem buscant informació específica sobre un element de maquinari en particular, anem a configurar Range_lookup igual a Fals.
- Feu clic al Range_lookup línia al quadre de diàleg.
- Escriviu la paraula Fals en aquesta línia per indicar que volem VLOOKUP per tornar una coincidència exacta de les dades que estem cercant.
- Feu clic 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 cel·la D2 una # N / A error estarà present cel·la E2.
- Aquest error es corregirà quan afegirem els criteris de cerca en l'últim pas del tutorial.
Còpia de la fórmula de cerca
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 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 parcial com a Valor_valueu.
Atès que les dades es presenten en un patró regular del full de treball, podem copiar la fórmula de cerca cel·la E2 a cèl·lules F2 i G2.
A mesura que es copiï la fórmula, Excel actualitzarà la referència relativa de la cel · la a la finestra COLUMNA funció (cel·la 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 $ D $ 2 i el rang anomenat Taula a mesura que es copia la fórmula.
Hi ha més d'una manera de copiar dades a Excel, però probablement la forma més senzilla és utilitzar el Omple el mànec.
- Fer clic a cel·la E2, on es troba 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 per indicar l'identificador de farciment.
- Feu clic al botó esquerre del ratolí i arrossegueu l'identificador de farcit a través de cel·la G2.
- Publicació el botó del ratolí i cel·la F3 hauria de contenir la fórmula de cerca bidimensional.
- Si es fa correctament, cèl·lules F2 i G2 ara també hauria de contenir el # N / A error en què està present cel·la E2.
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 vols recuperar al Valor_valueu cel·la (D2) i premeu la tecla Entra tecla 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.
- Fer clic a cel·la D2 en el full de càlcul.
- Escriviu Widget endins cel·la D2 i premeu la tecla Entra tecla al teclat.
- La informació següent s'ha de mostrar a 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 per als widgets
- Prova el VLOOKUP Fórmula matriu escrivint el nom d'altres parts en cel·la D2 i observant els resultats cel·les E2 a G2
