Funció VLOOKUP a Excel - Guia per a principiants: Sintaxi i exemples

contingut

Funció VLOOKUP a Excel: descripció general i sintaxi
- Sintaxi de la funció BUSCARV
Exemples de BUSCAR V
Coincidència exacta o aproximada a la funció BUSCARV
- Exemple 1: trobar una concordança exacta amb VLOOKUP
- Exemple 2: ús de VLOOKUP per trobar una coincidència aproximada
VLOOKUP a Excel: ho heu de recordar!

Avui comencem una sèrie d'articles que descriuen una de les funcions més útils d'Excel − VPR (CERCA V). Aquesta funció, alhora, és una de les més complexes i menys enteses.

En aquest tutorial sobre VPR Intentaré exposar els conceptes bàsics de la manera més senzilla possible per tal que el procés d'aprenentatge sigui el més clar possible per als usuaris sense experiència. A més, estudiarem diversos exemples amb fórmules d'Excel que demostraran els casos d'ús més habituals de la funció VPR.

Funció VLOOKUP a Excel: descripció general i sintaxi

Què és, doncs VPR? Bé, primer de tot, és una funció d'Excel. Què fa ella? Busca el valor que especifiqueu i retorna el valor corresponent de l'altra columna. Tècnicament parlant, VPR cerca el valor a la primera columna de l'interval donat i retorna el resultat d'una altra columna de la mateixa fila.

En l'aplicació més habitual, la funció VPR cerca a la base de dades un identificador únic determinat i extreu informació relacionada amb ell de la base de dades.

Primera lletra del nom de la funció VPR (CERCA V) vol dir Вvertical (Vvertical). Per ella es pot distingir VPR de GPR (RECUPA), que cerca un valor a la fila superior d'un rang − Гhoritzontal (Hhoritzontal).

function VPR disponible a Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP i Excel 2000.

Sintaxi de la funció BUSCARV

function VPR (LOOKUP) té la sintaxi següent:

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

Com podeu veure, una funció VPR a Microsoft Excel té 4 opcions (o arguments). Els tres primers són obligatoris, l'últim és opcional.

valor_cerca (lookup_value) – El valor que cal cercar. Pot ser un valor (número, data, text) o una referència de cel·la (que conté el valor de cerca) o un valor retornat per alguna altra funció d'Excel. Per exemple, aquesta fórmula buscarà el valor 40:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

Si el valor de cerca és inferior al valor més petit de la primera columna de l'interval que s'està buscant, la funció VPR informarà d'un error #AT (#N/A).

taula_array (taula): dues o més columnes de dades. Recordeu, la funció VPR sempre cerca el valor a la primera columna de l'interval donat a l'argument taula_array (taula). L'interval visible pot contenir diverses dades, com ara text, dates, números, booleans. La funció no distingeix entre majúscules i minúscules, és a dir, els caràcters en majúscules i minúscules es consideren iguals. Així que la nostra fórmula buscarà el valor 40 a les cèl·lules de A2 a A15, perquè A és la primera columna de l'interval A2:B15 donat a l'argument taula_array (taula):
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

col_index_num (número_columna) és el número de la columna de l'interval donat a partir del qual es retornarà el valor de la fila trobada. La columna més a l'esquerra de l'interval donat és 1, la segona columna és 2, la tercera columna és 3 etcètera. Ara podeu llegir la fórmula sencera:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)
Fórmula busca valor 40 a la gamma A2: A15 i retorna el valor corresponent de la columna B (perquè B és la segona columna de l'interval A2:B15).

Si el valor de l'argument col_index_num (número_columna) inferior a 1llavors VPR informarà d'un error #VALUE! (#VALOR!). I si és més que el nombre de columnes de l'interval taula_array (taula), la funció retornarà un error REF! (#ENLLAÇ!).

range_lookup (range_lookup) - determina què cal cercar:
- coincidència exacta, l'argument ha de ser igual FALS (FALS);
- concordança aproximada, argument igual CODI VERITAT (VERTADER) o no especificat en absolut.
Aquest paràmetre és opcional, però molt important. Més endavant en aquest tutorial VPR Us mostraré alguns exemples que expliquen com escriure fórmules per trobar coincidències exactes i aproximades.

Exemples de BUSCAR V

Espero la funció VPR que et faci una mica més clar. Vegem ara alguns casos d'ús VPR en fórmules amb dades reals.

Com utilitzar VLOOKUP per cercar en un altre full d'Excel

A la pràctica, fórmules amb funció VPR poques vegades s'utilitzen per cercar dades al mateix full de treball. Molt sovint, buscareu i recuperareu els valors corresponents d'un altre full.

per utilitzar VPR, cerqueu en un altre full de Microsoft Excel, Heu de tenir l'argument taula_array (taula) especifiqueu el nom del full amb un signe d'exclamació seguit d'un rang de cel·les. Per exemple, la fórmula següent mostra que l'interval A2: B15 està en un full anomenat Sheet2.

=VLOOKUP(40,Sheet2!A2:B15,2)

=ВПР(40;Sheet2!A2:B15;2)

Per descomptat, el nom del full no s'ha d'introduir manualment. Només heu de començar a escriure la fórmula, i quan es tracta de l'argument taula_array (taula), canvieu al full desitjat i seleccioneu el rang de cel·les desitjat amb el ratolí.

La fórmula que es mostra a la captura de pantalla següent cerca el text "Producte 1" a la columna A (és la primera columna del rang A1:B2) en un full de treball Preus.

=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)

=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

Si us plau, recordeu que quan cerqueu un valor de text, l'heu d'incloure entre cometes (“”), com sol fer-se a les fórmules d'Excel.

Per argumentar taula_array (taula) és convenient utilitzar sempre referències absolutes (amb el signe $). En aquest cas, l'interval de cerca es mantindrà sense canvis en copiar la fórmula a altres cel·les.

Cerqueu en un altre llibre de treball amb BUSCARV

Funcionar VPR treballat entre dos llibres de treball d'Excel, heu d'especificar el nom del llibre de treball entre claudàtors abans del nom del full.

Per exemple, a continuació hi ha una fórmula que cerca el valor 40 al full Sheet2 al llibre Nombres.xlsx:

=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)

=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)

Aquí teniu la manera més senzilla de crear una fórmula a Excel VPRque enllaça amb un altre llibre de treball:

Obriu els dos llibres. Això no és necessari, però és més fàcil crear una fórmula d'aquesta manera. No voleu introduir el nom del llibre de treball manualment, oi? A més, us protegirà de les errades d'ortografia accidentals.
Comenceu a escriure una funció VPRi quan es tracta de l'argument taula_array (taula), canvieu a un altre llibre de treball i seleccioneu-hi l'interval de cerca necessari.

La captura de pantalla següent mostra la fórmula amb la cerca establerta en un interval del llibre de treball PriceList.xlsx al full Preus.

function VPR funcionarà fins i tot quan tanqueu el llibre de treball cercat i el camí complet al fitxer del llibre de treball apareix a la barra de fórmules, tal com es mostra a continuació:

Si el nom del quadern o full de treball conté espais, s'haurà d'incloure entre apòstrofs:

=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)

=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)

Com utilitzar un rang o una taula amb nom en fórmules amb BUSCARV

Si teniu previst utilitzar el mateix rang de cerca en diverses funcions VPR, podeu crear un rang amb nom i introduir-ne el nom a la fórmula com a argument taula_array (taula).

Per crear un rang amb nom, només cal que seleccioneu les cel·les i introduïu un nom adequat al camp Nom, a l'esquerra de la barra de fórmules.

Ara podeu escriure la fórmula següent per trobar el preu d'un producte Producte 1:

=VLOOKUP("Product 1",Products,2)

=ВПР("Product 1";Products;2)

La majoria dels noms d'interval funcionen per a tot el llibre d'Excel, de manera que no cal especificar el nom del full per a l'argument taula_array (taula), encara que la fórmula i l'interval de cerca estiguin en fulls de treball diferents. Si es troben en llibres de treball diferents, abans del nom de l'interval haureu d'especificar el nom del llibre de treball, per exemple, com aquest:

=VLOOKUP("Product 1",PriceList.xlsx!Products,2)

=ВПР("Product 1";PriceList.xlsx!Products;2)

Així que la fórmula sembla molt més clara, d'acord? A més, utilitzar intervals amb nom és una bona alternativa a les referències absolutes perquè l'interval amb nom no canvia quan copieu la fórmula a altres cel·les. Això vol dir que podeu estar segur que l'interval de cerca de la fórmula sempre romandrà correcte.

Si convertiu un rang de cel·les en un full de càlcul Excel complet mitjançant l'ordre Taula pestanya (Taula). inserció (Insereix), després, quan seleccioneu un rang amb el ratolí, Microsoft Excel afegirà automàticament els noms de les columnes (o el nom de la taula si seleccioneu tota la taula) a la fórmula.

La fórmula acabada tindrà un aspecte semblant a això:

=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)

=ВПР("Product 1";Table46[[Product]:[Price]];2)

O potser fins i tot així:

=VLOOKUP("Product 1",Table46,2)

=ВПР("Product 1";Table46;2)

Quan utilitzeu intervals amb nom, els enllaços apuntaran a les mateixes cel·les sense importar on copieu la funció VPR dins del llibre de treball.

Ús de comodins a les fórmules VLOOKUP

Com passa amb moltes altres funcions, VPR Podeu utilitzar els següents caràcters comodís:

Signe d'interrogació (?): substitueix qualsevol caràcter.
Asterisc (*): substitueix qualsevol seqüència de caràcters.

Ús de comodins a les funcions VPR pot ser útil en molts casos, per exemple:

Quan no recordeu exactament el text que heu de trobar.
Quan vols trobar alguna paraula que forma part del contingut d'una cel·la. Sapigueu això VPR cerca pel contingut de la cel·la en conjunt, com si l'opció estigués activada Coincideix amb tot el contingut de la cel·la (cel·la sencera) a la cerca estàndard d'Excel.
Quan una cel·la conté espais addicionals al principi o al final del contingut. En una situació així, podeu encendre el cervell durant molt de temps, intentant esbrinar per què la fórmula no funciona.

Exemple 1: cercar text que comenci o acabi amb determinats caràcters

Suposem que voleu cercar un client específic a la base de dades que es mostra a continuació. No recordes el seu cognom, però saps que comença amb “ack”. Aquí teniu una fórmula que farà la feina bé:

=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)

=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)

Ara que esteu segur que heu trobat el nom correcte, podeu utilitzar la mateixa fórmula per trobar l'import pagat per aquest client. Per fer-ho, només cal canviar el tercer argument de la funció VPR al número de columna desitjat. En el nostre cas, aquesta és la columna C (tercera de l'interval):

=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)

=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)

Aquí teniu alguns exemples més amb comodins:

~ Busca un nom acabat en "home":

=VLOOKUP("*man",$A$2:$C$11,1,FALSE)

=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)

~ Cerqueu un nom que comenci per "anunci" i acabi amb "fill":

=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)

=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)

~ Trobem el primer nom a la llista, format per 5 caràcters:

=VLOOKUP("?????",$A$2:$C$11,1,FALSE)

=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)

Funcionar VPR amb comodins va funcionar correctament, com a quart argument que hauríeu d'utilitzar sempre FALS (FALS). Si l'interval de cerca conté més d'un valor que coincideix amb els termes de cerca amb comodins, es retornarà el primer valor trobat.

Exemple 2: combina els comodins i les referències de cel·les a les fórmules BUSCAR V

Ara mirem un exemple una mica més complex de com cercar amb la funció VPR pel valor d'una cel·la. Imagineu que la columna A és una llista de claus de llicència i la columna B és una llista de noms que posseeixen una llicència. A més, teniu una part (diversos caràcters) d'algun tipus de clau de llicència a la cel·la C1 i voleu trobar el nom del propietari.

Això es pot fer mitjançant la fórmula següent:

=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)

=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)

Aquesta fórmula cerca el valor de la cel·la C1 a l'interval donat i retorna el valor corresponent de la columna B. Tingueu en compte que al primer argument, utilitzem un caràcter i (&) abans i després de la referència de la cel·la per enllaçar la cadena de text.

Com podeu veure a la figura següent, la funció VPR retorna "Jeremy Hill" perquè la seva clau de llicència conté la seqüència de caràcters de la cel·la C1.

Tingueu en compte que l'argument taula_array (taula) a la captura de pantalla anterior conté el nom de la taula (taula 7) en lloc d'especificar un rang de cel·les. Això és el que vam fer a l'exemple anterior.

Coincidència exacta o aproximada a la funció BUSCARV

I finalment, fem una ullada més de prop a l'últim argument que s'especifica per a la funció VPR - range_lookup (interval_view). Com s'ha esmentat al principi de la lliçó, aquest argument és molt important. Podeu obtenir resultats completament diferents en la mateixa fórmula amb el seu valor CODI VERITAT (VERTADER) o FALS (FALS).

Primer, anem a esbrinar què vol dir Microsoft Excel amb coincidències exactes i aproximades.

Si l'argument range_lookup (range_lookup) és igual a FALS (FALSE), la fórmula busca una coincidència exacta, és a dir, exactament el mateix valor que es dóna a l'argument valor_cerca (valor_de_cerca). Si a la primera columna del rang table_array (taula) troba dos o més valors que coincideixen amb l'argument valor_cerca (search_value), llavors se seleccionarà el primer. Si no es troben coincidències, la funció informarà d'un error #AT (#N/A). Per exemple, la fórmula següent informarà d'un error #AT (#N/A) si no hi ha cap valor a l'interval A2:A15 4:
=VLOOKUP(4,A2:B15,2,FALSE)
=ВПР(4;A2:B15;2;ЛОЖЬ)
Si l'argument range_lookup (range_lookup) és igual a CODI VERITAT (VERTADER), la fórmula busca una coincidència aproximada. Més precisament, primer la funció VPR cerca una coincidència exacta i, si no se'n troba cap, en selecciona una aproximada. Una coincidència aproximada és el valor més gran que no supera el valor especificat a l'argument. valor_cerca (valor_de_cerca).

Si l'argument range_lookup (range_lookup) és igual a CODI VERITAT (VERTADER) o no especificat, els valors de la primera columna de l'interval s'han d'ordenar en ordre ascendent, és a dir, del més petit al més gran. En cas contrari, la funció VPR pot retornar un resultat incorrecte.

Per entendre millor la importància de l'elecció CODI VERITAT (VERITAT) o FALS (FALS), mirem algunes fórmules més amb la funció VPR i mira els resultats.

Exemple 1: trobar una concordança exacta amb VLOOKUP

Com recordeu, per buscar una coincidència exacta, el quart argument de la funció VPR hauria d'importar FALS (FALS).

Tornem a la taula des del primer exemple i descobrim quin animal es pot moure a una velocitat 50 milles per hora. Crec que aquesta fórmula no et causarà cap dificultat:

=VLOOKUP(50,$A$2:$B$15,2,FALSE)

=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)

Tingueu en compte que el nostre interval de cerca (columna A) conté dos valors 50 - a les cèl·lules A5 и A6. La fórmula retorna el valor de la cel·la B5. Per què? Perquè quan es busca una coincidència exacta, la funció VPR utilitza el primer valor trobat que coincideix amb el que s'està cercant.

Exemple 2: ús de VLOOKUP per trobar una coincidència aproximada

Quan utilitzeu la funció VPR per cercar una coincidència aproximada, és a dir, quan l'argument range_lookup (range_lookup) és igual a CODI VERITAT (VERTADER) o omès, el primer que heu de fer és ordenar l'interval per la primera columna en ordre ascendent.

Això és molt important perquè la funció VPR retorna el següent valor més gran després del donat i, a continuació, s'atura la cerca. Si descuideu l'ordenació correcta, acabareu amb resultats molt estranys o un missatge d'error. #AT (#N/A).

Ara podeu utilitzar una de les fórmules següents:

=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)

=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)

Com podeu veure, vull esbrinar quin dels animals té la velocitat més propera 69 milles per hora. I aquí teniu el resultat que em va tornar la funció VPR:

Com podeu veure, la fórmula va retornar un resultat Antílope (Antílop), la velocitat del qual 61 milles per hora, tot i que la llista també inclou guepard (Guepard) que corre a gran velocitat 70 milles per hora, i 70 és més proper a 69 que 61, no? Per què passa això? Perquè la funció VPR quan es cerca una coincidència aproximada, retorna el valor més gran que no és més gran que el que s'està cercant.

Espero que aquests exemples aportin una mica de llum sobre el treball amb la funció VPR a Excel, i ja no la mires com una forastera. Ara no està de més repetir breument els punts clau del material que hem estudiat per tal de fixar-lo millor a la memòria.

VLOOKUP a Excel: ho heu de recordar!

function VPR Excel no pot mirar cap a l'esquerra. Sempre cerca el valor a la columna més a l'esquerra de l'interval donat per l'argument taula_array (taula).
En funció VPR tots els valors no distingeixen entre majúscules i minúscules, és a dir, les lletres minúscules i grans són equivalents.
Si el valor que cerqueu és inferior al valor mínim de la primera columna de l'interval que s'està buscant, la funció VPR informarà d'un error #AT (#N/A).
Si 3r argument col_index_num (número_columna) inferior a 1function VPR informarà d'un error #VALUE! (#VALOR!). Si és superior al nombre de columnes de l'interval taula_array (taula), la funció informarà d'un error REF! (#ENLLAÇ!).
Utilitzeu referències de cel·les absolutes a l'argument taula_array (taula) de manera que es preservi l'interval de cerca correcte en copiar la fórmula. Proveu d'utilitzar rangs o taules amb nom a Excel com a alternativa.
Quan feu una cerca de concordança aproximada, recordeu que la primera columna de l'interval que busqueu s'ha d'ordenar en ordre ascendent.
Finalment, recordeu la importància del quart argument. Utilitzar valors CODI VERITAT (VERITAT) o FALS (FALS) deliberadament i t'eliminaràs de molts mals de cap.

En els articles següents del nostre tutorial de funcions VPR a Excel, aprendrem exemples més avançats, com la realització de diversos càlculs utilitzant VPR, extreure valors de diverses columnes i molt més. Gràcies per llegir aquest tutorial i espero retrobar-nos la setmana vinent!