01 de 03
Trobeu coincidències aproximades a les dades amb VLOOKUP d'Excel
Com funciona la funció VLOOKUP
La funció VLOOKUP d'Excel, que significa la cerca vertical , es pot utilitzar per buscar informació específica ubicada en una taula de dades o base de dades.
VLOOKUP normalment retorna un únic camp de dades com a sortida. Com ho fa això:
- Proporciona un nom o valor de cerca que indica a VLOOKUP quina fila o registre de la taula de dades busca les dades desitjades
- Proporciona el número de columna , conegut com col_index_num , de les dades que cerqueu
- La funció busca el valor_recuit en la primera columna de la taula de dades
- VLOOKUP, a continuació, localitza i retorna la informació que busca des d'un altre camp del mateix registre mitjançant el número de columna proporcionat
Classificació de les dades primer
Tot i que no sempre és obligatori, normalment és millor ordenar el rang de dades que VLOOKUP està buscant en ordre ascendent utilitzant la primera columna del rang per a la clau d'ordenació.
Si les dades no estan ordenades, VLOOKUP pot retornar un resultat incorrecte.
Sintaxi i arguments de la funció VLOOKUP
La sintaxi d' una funció es refereix al disseny de la funció i inclou el nom de la funció, claudàtors i arguments .
La sintaxi de la funció VLOOKUP és:
= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
_valor de cerca - (obligatori) del valor a buscar - com la quantitat que es ven a la imatge de dalt
table_array - (obligatori) aquesta és la taula de dades que VLOOKUP busca per trobar la informació que està després.
- El tauler de la taula ha de contenir almenys dues columnes de dades
- La primera columna normalment conté el valor_re_test
col_index_num : (obligatori) el número de columna del valor que voleu trobar.
- La numeració comença amb la columna search_key com a columna 1
- Si col_index_num s'estableix en un nombre major que el nombre de columnes seleccionades a l'argument table_array a #REF! l'error és retornat per la funció
range_lookup - (opcional) indica si el rang s'ordena o no en ordre ascendent.
- Les dades de la primera columna s'utilitzen com a clau d'ordenació
- Un valor booleà: TRUE o FALSE són els únics valors acceptables
- Si s'omet, el valor s'establirà a TRUE per defecte
- Si s'estableix TRUE o s'ha omès i la primera columna del rang no està ordenada en ordre ascendent, es pot produir un resultat incorrecte
- Si s'estableix TRUE o s'ha omès i no es troba una concordança exacta per al _valor de cerca, s'utilitza la coincidència més propera que sigui més petita o de mida com la clau search_key
- Si s'estableix en FALSE, VLOOKUP només accepta una coincidència exacta per al _valor de cerca . Si hi ha diversos valors de concordança, es torna el primer valor coincident
- Si s'estableix en FALSE i no es troba cap valor de coincidència per a search_key , es torna un error de # N / A per la funció
Exemple: Trobeu la tarifa de descompte per a la quantitat comprada
L'exemple a la imatge anterior utilitza la funció VLOOKUP per trobar la taxa de descompte que varia segons la quantitat d'articles comprats.
L'exemple mostra que el descompte per a la compra de 19 articles és del 2%. Això es deu a que la columna Quantitat conté intervals de valors. Com a resultat, VLOOKUP no pot trobar una concordança exacta. En canvi, s'ha de trobar una coincidència aproximada per retornar la taxa de descompte correcta.
Per trobar coincidències aproximades:
- Ordeneu les dades a la taula_array en ordre ascendent;
- estableixi l'argument range_lookup a TRUE
A l'exemple, s'utilitza la fórmula següent que conté la funció VLOOKUP per trobar el descompte per quantitats de productes adquirits.
= VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE)
Tot i que aquesta fórmula només es pot escriure en una cel·la de full de càlcul, una altra opció, tal com s'utilitza amb els passos que es detallen a continuació, és utilitzar el quadre de diàleg de la funció per introduir els seus arguments.
- L'ús del quadre de diàleg sovint facilita l'entrada dels arguments d'una funció correctament.
Obrir el quadre de diàleg VLOOKUP
Els passos utilitzats per entrar a la funció VLOOKUP que es mostra a la imatge anterior a la cel·la B2 són:
- Feu clic a la cel·la B2 per fer-la la cel·la activa : la ubicació on es mostren els resultats de la funció VLOOKUP
- Feu clic a la pestanya Fórmules .
- Trieu Cerca i referència de 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 de la funció VLOOKUP d'Excel
Assenyalant referències de cèl lules
Els arguments de la funció VLOOKUP s'introdueixen en línies separades del quadre de diàleg tal com es mostra a la imatge de dalt.
Les referències de les cel·les que s'utilitzen com a arguments es poden escriure a la línia correcta o, tal com es fa als passos següents, es pot utilitzar el punter del ratolí, que implica mostrar l'interval desitjat de cel·les amb el punter del ratolí per entrar al quadre de diàleg .
Els avantatges d'utilitzar l'apuntant inclouen:
- És més ràpid que escriure;
- Menys errors es fan introduint les referències de cel·les correctes.
Ús de referències de cèl lules relatives i absolutes amb arguments
No és estrany utilitzar diverses còpies de VLOOKUP per retornar informació diferent de la mateixa taula de dades. Per fer-ho més fàcil, moltes vegades VLOOKUP es pot copiar d'una cel·la a una altra. Quan es copien les funcions a altres cel·les, s'ha de tenir cura de que les referències de cel·les resultants siguin correctes donada la nova ubicació de la funció.
A la imatge anterior, els signes de dòlar ( $ ) envolten les referències de les cel·les per l'argument table_array que indica que són referències absolutes de les cel·les , el que significa que no canviaran si la funció es copia a una altra cel·la. Això és desitjable ja que diverses còpies de VLOOKUP faran referència a la mateixa taula de dades que la font d'informació.
La referència de la cel·la utilitzada per al valor de la cerca_, d'altra banda , no està envoltada de signes de dòlar, la qual cosa la converteix en una referència de cel·la relativa. Les referències de cel·les relatives canvien quan es copien per reflectir la seva nova ubicació en relació amb la posició de les dades a què fan referència.
Introduir els arguments de funció
- Feu clic a la línia de cerca _valor al quadre de diàleg VLOOKUP
- Feu clic a la cel·la C2 al full de treball per introduir aquesta referència de cel·la com l'argument search_key
- Feu clic a la línia Table_array del quadre de diàleg
- Ressalteu les cel·les C5 a D8 al full de treball per introduir aquest interval com l'argument Table_array : els encapçalaments de la taula no s'inclouen
- Premeu la tecla F4 al teclat per canviar l'interval a referències de cel·la absoluta
- Feu clic a la línia Col_index_num del quadre de diàleg
- Escriviu un 2 en aquesta línia com a argument Col_index_num , ja que les taxes de descompte es troben a la columna 2 de l'argument Table_array
- Feu clic a la línia Range_lookup del quadre de diàleg
- Escriu la paraula True com a argument Range_lookup
- Premeu la tecla Retorn al teclat per tancar el quadre de diàleg i tornar al full de treball
- La resposta del 2% (la tarifa de descompte per la quantitat adquirit) hauria d'aparèixer a la cel·la D2 del full de càlcul
- Quan feu clic a la cel·la D2, la funció completa = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, VERDRA) apareix a la barra de fórmules que hi ha a sobre del full de càlcul
Per què VLOOKUP ha retornat un 2% com a resultat
- En l'exemple, la columna Quantitat no conté una concordança exacta del valor de cerca_key de 19.
- Com que l'argument is_sorted s'estableix TRUE, VLOOKUP trobarà una coincidència aproximada amb el valor search_key .
- El valor més proper a la mida que encara és més petit que el valor search_key de 19 és 11.
- VLOOKUP, per tant, busca el percentatge de descompte en la fila que conté 11, i, en conseqüència, torna una taxa de descompte del 2%.
03 de 03
Excel VLOOKUP que no funciona: # N / A i #REF errors
VLOOKUP Missatges d'error
Els missatges d'error següents estan associats a VLOOKUP.
A # N / A ("valor no disponible") Es mostra l'error Si:
- El _valor de cerca no es troba a la primera columna de l'argument de rang
- L'argument Table_array no és exacte. Per exemple, l'argument pot incloure columnes buides al costat esquerre del rang
- L'argument Range_lookup s'estableix en FALSE i no es pot trobar una concordança exacta de l'argument search_key a la primera columna de l' interval
- L'argument Range_lookup s'estableix TRUE i tots els valors de la primera columna de l' interval són més grans que la clau search_key
Un #REF! ("referència fora de rang") Es mostra l'error Si:
- L'argument Col_index_num és major que el nombre de columnes a la Table_array