01 de 03
Cerca dades a l'esquerra
Descripció general de la fórmula de cerca d'esquerra d'Excel
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 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.
En combinar VLOOKUP amb la funció triar ; no obstant això, es pot crear una fórmula d' investigació esquerrana que:
- permet triar el valor de la cerca des de qualsevol columna de la taula de dades
- informació de retorn situada en qualsevol columna a l'esquerra del valor de cerca
Exemple: Utilitzar les funcions VLOOKUP i ELECTIR en una fórmula de cerca esquerra
Els passos detallats a continuació creen la fórmula de cerca esquerra que es veu a la imatge de dalt.
La fórmula
= VLOOKUP ($ D $ 2, CHOOSE ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)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 a la fórmula és enganyar a VLOOKUP per creure que la columna 3 és en realitat la columna 1. Com a resultat, el nom de la companyia es pot utilitzar com a valor de cerca per trobar el nom de la part subministrada per cada empresa.
Passos del tutorial: introduir les dades del tutorial
- Introduïu els següents encapçalaments a les cel·les indicades: D1 - Proveïdor E1 - Part
- Introduïu la taula de dades que es mostra a la imatge anterior a les cel·les D4 a F9
- Les files 2 i 3 es deixen en blanc per tal d'acomodar els criteris de cerca i la fórmula de cerca esquerra creada durant aquest tutorial
Comença la fórmula de cerca esquerra: obriu el quadre de diàleg VLOOKUP
Encara que només es pot escriure la fórmula anterior directament a la cel·la F1 del full de treball, moltes persones tenen dificultats amb la sintaxi de la fórmula.
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 esquerra
- 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 mostrar el quadre de diàleg de la funció
02 de 03
Introduir els arguments en el quadre de diàleg VLOOKUP - Feu clic per veure la imatge més gran
Arguments de 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 localitza en una línia separada seguida d'un camp en el qual s'introdueix un valor.
Introduïu els següents valors per a cadascun dels arguments de VLOOKUP a la línia correcta del quadre de diàleg tal com es mostra a la imatge de dalt.
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.
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
- Premeu la tecla F4 al teclat per fer que la referència de la cel·la sigui absoluta: $ D $ 2
Nota: 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.
La matriu de taula: introduir la funció CHOOSE
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 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 la dreta a l'esquerra de les columnes de la taula, de manera que la columna F és la primera i la columna D és la segona
Els detalls de com funciona LA ELECCIÓ d'aquestes tasques es poden trobar a la pàgina 3 del tutorial .
Tutorial Passos
Nota: Quan s'introdueixen les funcions manualment, cadascun dels arguments de la funció s'ha de separar amb una coma "," .
- Al quadre de diàleg de la funció VLOOKUP, feu clic a la línia Table_array
- Introduïu la següent funció TANCAR
- Tria ({1,2}, $ F: $ F, $ D: $ D)
El número de l'í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. En aquesta fórmula; Tanmateix, es refereix a l'ordre de les columnes establertes per la funció OPERAR.
La funció CHOOSE crea una matriu de taula que té dues columnes d'ample amb la columna F seguida primer per la columna D. Donat que la informació buscada, 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.
Tutorial Passos
- Feu clic a la línia Col_index_num al quadre de diàleg
- Escriviu un 2 en aquesta línia
La 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_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, atès que busquem un nom de part en particular, Range_lookup s'establirà en False de manera que només la fórmula retorni les coincidències exactes.
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 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, cal que hi hagi un error N / A en la cel·la E2
03 de 03
Prova de la fórmula de cerca esquerra
Tornant dades amb la fórmula de cerca esquerra
Per trobar quines empreses proporcionen les parts, escriviu el nom d'una empresa a la cel·la D2 i premeu la tecla ENTRAR al teclat.
El nom de la peça es mostrarà a la cel·la E2.
Tutorial Passos
- Feu clic a la cel·la D2 al full de treball
- Escriviu Gadgets Plus a la cel·la D2 i premeu la tecla ENTRAR 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
VLOOKUP Missatges d'error
Si apareix un missatge d'error com # N / A a la cel·la E2, primer comproveu si hi ha errors d'ortografia a la cel·la D2.
Si l'ortografia no és el problema, aquesta llista de missatges d'error VLOOKUP us pot ajudar a determinar on està el problema.
Trencant el treball de selecció de la funció
Com s'ha esmentat, en aquesta fórmula, la funció CHOOSE té dos treballs:
- crea una matriu de taula que només té dues columnes d'ample, columnes D i F
- canvia l'ordre de la dreta a l'esquerra de les columnes de la taula, de manera que la columna F és la primera i la columna D és la segona
Crear una matriu de taula de dues columnes
La sintaxi de la funció CHOOSE és:
= Tria (index_number, Value1, Value2, ... Value254)
La funció CHOOSE normalment torna 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.
En introduir diversos nombres d'índex; Tanmateix, la funció retornarà diversos valors en qualsevol ordre desitjat. Obtenir ELEGIR per retornar valors múltiples es fa creant una matriu .
Introduir una matriu es realitza al voltant dels nombres introduïts amb claus o suports. S'han introduït 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 en l'argument de valor, es pot crear una taula de tres columnes.
Les columnes addicionals us permetran retornar informació diferent amb la fórmula de cerca esquerra simplement canviant l'argument del número d'índex de columna de VLOOKUP al número de la columna que conté la informació desitjada.
Canvi de l'ordre de les columnes amb la funció CHOOSE
A la funció CHOOSE utilitzada en aquesta fórmula: CHOOSE ({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 la taula de VLOOKUP, la font de dades d'aquesta funció, canviar l'ordre de les columnes a la funció CHOOSE es passa a VLOOKUP.
Ara, 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 podrà realitzar les seves funcions de cerca normals 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.