La funció VLOOKUP d'Excel s'utilitza per trobar i retornar informació d'una taula de dades basada en un valor de cerca que trieu.
Normalment, VLOOKUP requereix que el valor de la cerca estigui a la columna més a l'esquerra de la taula de dades, i la funció retorna un altre camp de dades ubicat a la mateixa fila a la dreta d'aquest valor.
Tanmateix, si combina VLOOKUP amb la funció CHOOSE, es pot crear una fórmula de cerca esquerra que:
- Permet que el valor de la cerca sigui de qualsevol columna de la taula de dades
- Ofereix informació localitzada en qualsevol columna a l'esquerra del valor de cerca
Nota: Aquestes instruccions s'apliquen a les versions Excel 2019, 2016, 2013, 2010 i Excel per a Office 365.
01 de 05Tutorial: Utilitzeu les funcions VLOOKUP i ELECTIR en una fórmula de cerca esquerra
Per crear la fórmula de cerca esquerra que es veu a la imatge d'exemple, utilitzeu la fórmula:
= VLOOKUP ($ D $ 2, CHOOSE ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)
En aquest exemple, la fórmula permet trobar la part subministrada per les diferents empreses que figuren a la columna 3 de la taula de dades.
La funció de la funció CHOOSE de la fórmula és enganyar a VLOOKUP per creure que la columna 3 és la columna 1. Com a resultat, el nom de l'empresa es pot utilitzar com a valor de cerca per trobar el nom de la part subministrada per cada empresa.
Introduïu les dades del tutorial
- Introduïu l'encapçalament Proveïdor a la cel·la D1.
- Introduïu l'encapçalament Part a la cel·la E1.
- Introduïu la taula de dades que es veu a la imatge anterior a les cel·les D4 a F9
- Files 2 i 3 es deixen en blanc per donar cabuda als criteris de cerca i la fórmula de cerca esquerra creada durant aquest tutorial
Obriu el quadre de diàleg VLOOKUP
Encara que és possible escriure la fórmula directament a la cel·la F1 en el full de treball, moltes persones tenen dificultats amb la sintaxi de la fórmula.
En aquest cas, és més segur 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.
- Feu clic a la cel·la E2 del full de treball. E2 és la ubicació on es mostraran els resultats de la fórmula de cerca esquerra.
- Feu clic al Fórmules pestanya de la cinta.
- Feu clic al Cerca i referència opció a la cinta per obrir la llista desplegable de la funció.
- Fer clic a VLOOKUP a la llista per mostrar el quadre de diàleg de la funció.
Introduint arguments al quadre de diàleg VLOOKUP
Els arguments d'una funció són els valors utilitzats per la funció per calcular un resultat.
En el quadre de diàleg d'una funció, el nom de cada argument es troba en una línia separada, seguit d'un camp en el qual s'introdueix un valor.
Introduïu els valors següents per a cadascun dels arguments de VLOOKUP a la línia correcta del quadre de diàleg, tal com es mostra a la imatge adjunta.
El valor de cerca
El valor de cerca és el camp d'informació que s'utilitza per cercar la matriu de taula. VLOOKUP torna un altre camp de dades de la mateixa fila que el valor de cerca.
Aquest exemple utilitza una referència de cel·la a la ubicació en què s'introduirà el nom de l'empresa en el full de treball. L'avantatge d'això és que permet canviar el nom de l'empresa sense editar la fórmula.
- Feu clic al valor_requisició línia al quadre de diàleg.
- Feu clic a la cel·la D2 per afegir aquesta referència a la cel · la valor_requisició línia
- Premeu el botó F4 tecla al teclat perquè la referència de la cel·la sigui absoluta, $ D $ 2.
Referències de cèl · lules absolutes
Les referències de cel·les absolutes s'utilitzen per al valor de cerca i els arguments de matriu de taula per evitar errors si la fórmula de cerca es copia a altres cel·les del full de treball.
Introduir la funció ELECTIR
L'argument de matriu de taula és el bloc de dades contigües a partir de les quals es recupera informació específica.
Normalment, VLOOKUP només mira cap a la dreta de l'argument de valor de cerca per trobar dades a la matriu de taules. Per aconseguir que es vegi a l'esquerra, VLOOKUP s'ha d'endevinar reorganitzant les columnes de la matriu de taula mitjançant la funció ELECCIONAR.
En aquesta fórmula, la funció CHOOSE realitza dues tasques:
- Crea una matriu de taula que només té dues columnes d'ample (columnes D i F).
- Canvia l'ordre de dreta a esquerra de les columnes a la taula, de manera que la columna F és la primera i la columna D és la segona.
Introducció de funcions
Quan introduïu les funcions manualment, cadascun dels arguments de la funció ha d'estar separat per una coma.
- Al quadre de diàleg de la funció VLOOKUP, feu clic a la icona Table_array línia
- Introdueixi la següent Tria funció:Tria ({1,2}, $ F: $ F, $ D: $ D)
Número d'índex de columna
Normalment, el número de l'índex de la columna indica quina columna de la matriu de taula conté les dades que heu seguit. Tanmateix, en aquesta fórmula, es refereix a l'ordre de les columnes establert per la funció CHOOSE.
La funció CHOOSE crea una matriu de taula que té dues columnes d'ample amb la columna F seguida primer per la columna D. Atès que la informació sol·licitada, el nom de la part, es troba a la columna D, el valor de l'argument de l'índex de columnes s'ha de definir com a 2.
- Feu clic al Col_index_nom línia al quadre de diàleg.
- Escriu a 2 en aquesta línia.
Cerca de rang
L'argument Range_lookup de VLOOKUP és un valor lògic (TRUE o FALSE) que indica si voleu que VLOOKUP trobeu una concordança exacta o aproximada al valor de la cerca.
- Si és TRUE o si s'omet aquest argument, VLOOKUP torna una concordança exacta amb el Valor de cerca o, 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 d'ordenar en ordre ascendent.
- Si FALSE, VLOOKUP només utilitza una coincidència exacta amb el valor_Verup. 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 un nom de part particular, Range_lookup està configurat Fals de manera que només la fórmula retorni les coincidències exactes.
- Feu clic al Range_lookup línia al quadre de diàleg.
- Escriviu la paraula Fals en aquesta línia per indicar que volem que VLOOKUP retorni una concordança exacta de les dades que busquem.
- Feu clic D'acord per completar la fórmula de cerca esquerra i tancar el quadre de diàleg.
- Com que encara no hem introduït el nom de l'empresa a la cel·la D2, apareix un error de # N / A a la cel·la E2.
Tornant dades amb la fórmula de cerca esquerra
Per trobar quines empreses subministren quines parts, escriviu el nom d'una empresa a la cel·la D2 i premeu la tecla ENTRAR tecla al teclat.
El nom de la peça es mostra a la cel·la E2.
- Feu clic a la cel·la D2 en el vostre full de càlcul.
- Escriviu Gadgets Plus a la cel·la D2 i premeu la tecla ENTRAR tecla al teclat.
- El text "Gadgets" - la part subministrada per l'empresa Gadgets Plus - s'hauria de mostrar a la cel·la E2.
Proveu també la fórmula de cerca escrivint altres noms d'empreses a la cel·la D2, i el nom de la part corresponent hauria d'aparèixer a la cel·la E2.
Si apareix un missatge d'error com # N / A a la cel·la E2, comproveu si hi ha errors d'ortografia a la cel·la D2.
04 de 05Creació d'una matriu de taula de dues columnes
La sintaxi de la funció CHOOSE és:
= Tria (index_number, Value1, Value2, … Value254)
La funció CHOOSE normalment retorna un valor de la llista de valors (Value1 a Value254) en funció del número d'índex que heu introduït.
Si el número d'índex és 1, la funció retorna Value1 de la llista; si el número d'índex és 2, la funció retorna Value2 de la llista i així successivament.
Quan s'introdueixen diversos nombres d'índex, la funció retorna múltiples valors en qualsevol ordre desitjat. Obtenir ELEGIR per retornar valors múltiples es fa creant una matriu.
Per introduir una matriu, envolteu els nombres introduïts amb claudàtors o claudàtors. S'introdueixen dos números per al número d'índex:{ 1,2 }.
Cal assenyalar que CHOOSE no es limita a crear una taula de dues columnes. En incloure un número addicional a la matriu, com {1,2,3}, i un interval addicional a l'argument de valor, CHOOSE crea una taula de tres columnes.
Les columnes addicionals us permeten tornar informació diferent amb la fórmula de cerca esquerra canviant l'argument del número d'índex de columnes de VLOOKUP al número de la columna que conté la informació desitjada.
05 de 05Canviar l'ordre de les columnes amb la funció CHOOSE
A la funció CHOOSE utilitzada en aquesta fórmula:
Tria ({1,2}, $ F: $ F, $ D: $ D)
el rang de la columna F apareix abans de la columna D.
Atès que la funció CHOOSE estableix la matriu de taula de VLOOKUP (la font de dades per a aquesta funció), es passa l'ordre de les columnes a la funció CHOOSE a VLOOKUP.
Pel que fa a VLOOKUP, la matriu de taula només té dues columnes d'ample, amb la columna F a l'esquerra i la columna D a la dreta. Atès que la columna F conté el nom de l'empresa que volem buscar i, ja que la columna D conté els noms de les parts, VLOOKUP pot realitzar les seves funcions de cerca regulars per trobar dades que es troben a l'esquerra del valor de cerca.
Com a resultat, VLOOKUP pot utilitzar el nom de l'empresa per trobar la part que subministren.
