Función VLOOKUP en Excel - Guía para principiantes: sintaxe e exemplos

Hoxe comezamos unha serie de artigos que describen unha das funcións máis útiles de Excel − VPR (BUSCAR V). Esta función, ao mesmo tempo, é unha das máis complexas e menos comprendidas.

Neste tutorial sobre VPR Tentarei expor os conceptos básicos o máis sinxelo posible para que o proceso de aprendizaxe sexa o máis claro posible para os usuarios sen experiencia. Ademais, estudaremos varios exemplos con fórmulas de Excel que demostrarán os casos de uso máis habituais da función VPR.

  • Descrición xeral e sintaxe
  • Exemplos de BUSCAR V
    • Como usar VLOOKUP para buscar noutra folla de Excel
    • Busca noutro libro de traballo con BUSCAR V
    • Como usar un rango ou táboa con nome en fórmulas con BUSCARV
    • Usando comodíns en fórmulas VLOOKUP
  • Coincidencia exacta ou aproximada na función BUSCAR V
  • VLOOKUP en Excel: debes lembrar isto!

Función VLOOKUP en Excel: descrición xeral e sintaxe

Entón, que é VPR? Ben, en primeiro lugar, é unha función de Excel. Que fai ela? Busca o valor que especificas e devolve o valor correspondente da outra columna. Tecnicamente falando, VPR busca o valor na primeira columna do intervalo indicado e devolve o resultado doutra columna da mesma fila.

Na aplicación máis común, a función VPR busca na base de datos un identificador único determinado e extrae algunha información relacionada con el da base de datos.

Primeira letra do nome da función VPR (BUSCAR V) significa Вvertical (Vvertical). Por ela pódese distinguir VPR de GPR (BUSCARH), que busca un valor na fila superior dun intervalo − Гhorizontal (Hhorizontalmente).

función VPR dispoñible en Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP e Excel 2000.

Sintaxe da función BUSCAR V

función VPR (BUSCARV) ten a seguinte sintaxe:

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

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

Como podes ver, unha función VPR en Microsoft Excel ten 4 opcións (ou argumentos). Os tres primeiros son obrigatorios, o último é opcional.

  • valor_valor (lookup_value) – O valor a buscar. Este pode ser un valor (número, data, texto) ou unha referencia de cela (que contén o valor de busca) ou un valor devolto por algunha outra función de Excel. Por exemplo, esta fórmula buscará o valor 40:

    =VLOOKUP(40,A2:B15,2)

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

Se o valor de busca é menor que o valor máis pequeno da primeira columna do intervalo que se busca, a función VPR informará dun erro #AT (#N / A).

  • table_array (táboa): dúas ou máis columnas de datos. Lembre, a función VPR sempre busca o valor na primeira columna do intervalo indicado no argumento table_array (táboa). O intervalo visible pode conter varios datos, como texto, datas, números, booleanos. A función non distingue entre maiúsculas e minúsculas, o que significa que os caracteres en maiúsculas e minúsculas considéranse iguais. Entón, a nosa fórmula buscará o valor 40 nas células de A2 a A15, porque A é a primeira columna do intervalo A2:B15 indicado no argumento table_array (táboa):

    =VLOOKUP(40,A2:B15,2)

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

  • col_index_num (número_columna) é o número da columna do intervalo indicado a partir do cal se devolverá o valor da fila atopada. A columna máis á esquerda do intervalo indicado é 1, a segunda columna é 2, a terceira columna é 3 etcétera. Agora podes ler a fórmula completa:

    =VLOOKUP(40,A2:B15,2)

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

    Fórmula buscando valor 40 na franxa A2: A15 e devolve o valor correspondente da columna B (porque B é a segunda columna do intervalo A2:B15).

Se o valor do argumento col_index_num (número_columna) menor que 1logo VPR informará dun erro #VALUE! (#VALOR!). E se é máis que o número de columnas do intervalo table_array (táboa), a función devolverá un erro # Ref! (#LINK!).

  • intervalo_lookup (range_lookup) – determina o que buscar:
    • coincidencia exacta, o argumento debe ser igual FALSE (FALSO);
    • coincidencia aproximada, argumento iguais CÓDIGO VERDADEIRO (VERDADEIRO) ou non se especifica.

    Este parámetro é opcional, pero moi importante. Máis adiante neste tutorial VPR Vouche mostrar algúns exemplos que explican como escribir fórmulas para atopar coincidencias exactas e aproximadas.

Exemplos de BUSCAR V

Espero a función VPR facerche un pouco máis claro. Agora vexamos algúns casos de uso VPR en fórmulas con datos reais.

Como usar VLOOKUP para buscar noutra folla de Excel

Na práctica, fórmulas cunha función VPR raramente se usan para buscar datos na mesma folla de traballo. Na maioría das veces, buscará e recuperará os valores correspondentes doutra folla.

A fin de utilizar VPR, busca noutra folla de Microsoft Excel, Debes no argumento table_array (táboa) especifique o nome da folla cun signo de admiración seguido dun intervalo de celas. Por exemplo, a seguinte fórmula mostra que o intervalo A2: B15 está nunha folla co nome Sheet2.

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

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

Por suposto, o nome da folla non ten que ser introducido manualmente. Só ten que comezar a escribir a fórmula, e cando se trata do argumento table_array (táboa), cambie á folla desexada e seleccione o intervalo de celas desexado co rato.

A fórmula que se mostra na captura de pantalla a continuación busca o texto "Produto 1" na columna A (é a primeira columna do rango A1: B2) nunha folla de traballo Prezos.

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

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

Lembra que ao buscar un valor de texto, debes encerralo entre comiñas (“”), como adoita facerse nas fórmulas de Excel.

Para argumentar table_array (táboa) é desexable empregar sempre referencias absolutas (co signo $). Neste caso, o intervalo de busca permanecerá inalterado ao copiar a fórmula noutras celas.

Busca noutro libro de traballo con BUSCAR V

Para funcionar VPR funcionou entre dous libros de Excel, cómpre especificar o nome do libro entre corchetes antes do nome da folla.

Por exemplo, a continuación móstrase unha fórmula que busca o valor 40 na folla Sheet2 no libro Números.xlsx:

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

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

Aquí está a forma máis sinxela de crear unha fórmula en Excel VPRque enlaza con outro libro de traballo:

  1. Abre os dous libros. Isto non é necesario, pero é máis fácil crear unha fórmula deste xeito. Non queres introducir o nome do libro manualmente, non? Ademais, protexeráche de erros tipográficos accidentais.
  2. Comeza a escribir unha función VPRe cando se trata do argumento table_array (táboa), cambie a outro libro de traballo e seleccione o intervalo de busca necesario nel.

A seguinte captura de pantalla mostra a fórmula coa busca definida nun intervalo no libro de traballo Lista de prezos.xlsx na folla Prezos.

función VPR funcionará mesmo cando peche o libro de traballo buscado e a ruta completa ao ficheiro do libro de traballo aparece na barra de fórmulas, como se mostra a continuación:

Se o nome do libro ou da folla contén espazos, debe ir encerrado entre apóstrofos:

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

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

Como usar un rango ou táboa con nome en fórmulas con BUSCARV

Se pensas usar o mesmo intervalo de busca en varias funcións VPR, pode crear un intervalo con nome e introducir o seu nome na fórmula como argumento table_array (táboa).

Para crear un intervalo con nome, simplemente seleccione as celas e introduza un nome apropiado no campo Nome, á esquerda da barra de fórmulas.

Agora podes escribir a seguinte fórmula para atopar o prezo dun produto Produto 1:

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

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

A maioría dos nomes de intervalos funcionan para todo o libro de Excel, polo que non é necesario especificar o nome da folla para o argumento table_array (táboa), aínda que a fórmula e o intervalo de busca estean en follas de traballo diferentes. Se están en libros de traballo diferentes, antes do nome do intervalo cómpre especificar o nome do libro, por exemplo, así:

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

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

Entón, a fórmula parece moito máis clara, de acordo? Ademais, usar intervalos con nome é unha boa alternativa ás referencias absolutas porque o intervalo nomeado non cambia cando copias a fórmula a outras celas. Isto significa que pode estar seguro de que o intervalo de busca na fórmula permanecerá sempre correcto.

Se converte un rango de celas nunha folla de cálculo de Excel completa usando o comando Táboa Ficha (Táboa). inserción (Inserir), entón cando seleccione un intervalo co rato, Microsoft Excel engadirá automaticamente os nomes das columnas (ou o nome da táboa se selecciona a táboa enteira) á fórmula.

A fórmula rematada terá un aspecto así:

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

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

Ou quizais mesmo así:

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

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

Ao usar intervalos con nome, as ligazóns apuntarán ás mesmas celas independentemente de onde copies a función VPR dentro do caderno de traballo.

Usando comodíns en fórmulas VLOOKUP

Como con moitas outras funcións, VPR Podes usar os seguintes caracteres comodín:

  • Signo de interrogación (?): substitúe calquera carácter.
  • Asterisco (*): substitúe calquera secuencia de caracteres.

Usando comodíns en funcións VPR pode ser útil en moitos casos, por exemplo:

  • Cando non lembras exactamente o texto que necesitas atopar.
  • Cando queres atopar algunha palabra que forme parte do contido dunha cela. Sabe que VPR busca polo contido da cela no seu conxunto, coma se a opción estivese activada Coincidir con todo o contido da cela (Cela enteira) na busca estándar de Excel.
  • Cando unha cela contén espazos adicionais ao comezo ou ao final do contido. Nesta situación, podes meter o cerebro durante moito tempo, intentando descubrir por que a fórmula non funciona.

Exemplo 1: buscar texto que comece ou remate con determinados caracteres

Digamos que quere buscar un cliente específico na base de datos que se mostra a continuación. Non lembras o seu apelido, pero sabes que comeza por “ack”. Aquí tes unha fórmula que fará o traballo ben:

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

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

Agora que estás seguro de que atopaches o nome correcto, podes usar a mesma fórmula para atopar o importe pagado por este cliente. Para iso, basta con cambiar o terceiro argumento da función VPR ao número de columna desexado. No noso caso, esta é a columna C (3ª no rango):

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

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

Aquí tes algúns exemplos máis con comodíns:

~ Busca un nome que remate en "home":

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

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

~ Busca un nome que comece por "anuncio" e remate con "fillo":

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

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

~ Atopamos o primeiro nome na lista, formada por 5 caracteres:

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

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

Para funcionar VPR con comodíns funcionou correctamente, como o cuarto argumento que debes usar sempre FALSE (FALSO). Se o intervalo de busca contén máis dun valor que coincide cos termos de busca con comodíns, devolverase o primeiro valor atopado.

Exemplo 2: combina comodíns e referencias de cela nas fórmulas de BUSCAR V

Agora vexamos un exemplo un pouco máis complexo de como buscar usando a función VPR por valor nunha cela. Imaxina que a columna A é unha lista de claves de licenza e a columna B unha lista de nomes que posúen unha licenza. Ademais, tes unha parte (varios caracteres) dalgún tipo de clave de licenza na cela C1 e queres atopar o nome do propietario.

Isto pódese facer usando a seguinte fórmula:

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

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

Esta fórmula busca o valor da cela C1 no intervalo indicado e devolve o valor correspondente da columna B. Teña en conta que no primeiro argumento, usamos un carácter ampersand (&) antes e despois da referencia da cela para vincular a cadea de texto.

Como podes ver na seguinte figura, a función VPR devolve "Jeremy Hill" porque a súa clave de licenza contén a secuencia de caracteres da cela C1.

Teña en conta que o argumento table_array (táboa) na captura de pantalla anterior contén o nome da táboa (táboa 7) en lugar de especificar un intervalo de celas. Isto é o que fixemos no exemplo anterior.

Coincidencia exacta ou aproximada na función BUSCAR V

E, finalmente, vexamos máis de cerca o último argumento que se especifica para a función VPR - intervalo_lookup (vista_intervalo). Como se mencionou ao comezo da lección, este argumento é moi importante. Podes obter resultados completamente diferentes na mesma fórmula co seu valor CÓDIGO VERDADEIRO (VERDADEIRO) ou FALSE (FALSO).

En primeiro lugar, imos descubrir o que significa Microsoft Excel por coincidencias exactas e aproximadas.

  • Se o argumento intervalo_lookup (range_lookup) é igual a FALSE (FALSO), a fórmula busca unha coincidencia exacta, é dicir, exactamente o mesmo valor que se indica no argumento valor_valor (valor_busca). Se na primeira columna do intervalo table_array (táboa) atopa dous ou máis valores que coinciden co argumento valor_valor (search_value), entón seleccionarase o primeiro. Se non se atopan coincidencias, a función informará dun erro #AT (#N / A). Por exemplo, a seguinte fórmula informará dun erro #AT (#N/A) se non hai ningún valor no intervalo A2:A15 4:

    =VLOOKUP(4,A2:B15,2,FALSE)

    =ВПР(4;A2:B15;2;ЛОЖЬ)

  • Se o argumento intervalo_lookup (range_lookup) é igual a CÓDIGO VERDADEIRO (VERDADEIRO), a fórmula busca unha coincidencia aproximada. Máis precisamente, primeiro a función VPR busca unha coincidencia exacta e, se non se atopa ningunha, selecciona unha aproximada. Unha coincidencia aproximada é o valor máis grande que non supera o valor especificado no argumento. valor_valor (valor_busca).

Se o argumento intervalo_lookup (range_lookup) é igual a CÓDIGO VERDADEIRO (VERDADEIRO) ou non se especifica, entón os valores da primeira columna do intervalo deben ordenarse en orde ascendente, é dicir, de menor a maior. En caso contrario, a función VPR pode devolver un resultado erróneo.

Para comprender mellor a importancia da elección CÓDIGO VERDADEIRO (VERDADE) ou FALSE (FALSO), vexamos algunhas fórmulas máis coa función VPR e mira os resultados.

Exemplo 1: atopar unha coincidencia exacta con VLOOKUP

Como lembras, para buscar unha coincidencia exacta, o cuarto argumento da función VPR debería importar FALSE (FALSO).

Volvamos á táboa dende o primeiro exemplo e descubramos que animal pode moverse a unha velocidade 50 millas por hora. Creo que esta fórmula non che causará dificultades:

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

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

Teña en conta que o noso intervalo de busca (columna A) contén dous valores 50 - nas células A5 и A6. A fórmula devolve o valor da cela B5. Por que? Porque cando se busca unha coincidencia exacta, a función VPR utiliza o primeiro valor atopado que coincide co que se busca.

Exemplo 2: Usando VLOOKUP para atopar unha coincidencia aproximada

Cando usas a función VPR para buscar unha coincidencia aproximada, é dicir, cando o argumento intervalo_lookup (range_lookup) é igual a CÓDIGO VERDADEIRO (VERDADEIRO) ou omitido, o primeiro que debes facer é ordenar o intervalo pola primeira columna en orde ascendente.

Isto é moi importante porque a función VPR devolve o seguinte valor máis grande despois do indicado e, a continuación, detense a busca. Se descoidas a ordenación correcta, terás resultados moi estraños ou unha mensaxe de erro. #AT (#N / A).

Agora podes usar unha das seguintes fórmulas:

=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)

Como vedes, quero saber cal dos animais ten a velocidade máis próxima 69 millas por hora. E aquí está o resultado que me devolveu a función VPR:

Como podes ver, a fórmula devolveu un resultado Antílope (Antílope), cuxa velocidade 61 quilómetros por hora, aínda que a lista tamén inclúe Cheetah (Guepardo) que corre a gran velocidade 70 millas por hora, e 70 está máis preto de 69 que de 61, non? Por que está a suceder isto? Porque a función VPR ao buscar unha coincidencia aproximada, devolve o valor máis grande que non é maior que o que se busca.

Espero que estes exemplos arroxen algo de luz sobre o traballo coa función VPR en Excel, e xa non a miras como unha allea. Agora non está de máis repetir brevemente os puntos clave do material que estudamos para fixalo mellor na memoria.

VLOOKUP en Excel: debes lembrar isto!

  1. función VPR Excel non pode mirar á esquerda. Sempre busca o valor na columna máis á esquerda do intervalo dado polo argumento table_array (táboa).
  2. En función VPR todos os valores non distinguen entre maiúsculas e minúsculas, é dicir, as letras pequenas e grandes son equivalentes.
  3. Se o valor que está a buscar é inferior ao valor mínimo da primeira columna do intervalo que se busca, a función VPR informará dun erro #AT (#N / A).
  4. Se 3o argumento col_index_num (número_columna) menor que 1función VPR informará dun erro #VALUE! (#VALOR!). Se é maior que o número de columnas do intervalo table_array (táboa), a función informará dun erro # Ref! (#LINK!).
  5. Use referencias de cela absolutas no argumento table_array (táboa) para que se conserve o intervalo de busca correcto ao copiar a fórmula. Proba a usar rangos ou táboas con nome en Excel como alternativa.
  6. Ao facer unha busca de coincidencia aproximada, lembra que a primeira columna do intervalo que buscas debe estar ordenada en orde ascendente.
  7. Finalmente, lembra a importancia do cuarto argumento. Use valores CÓDIGO VERDADEIRO (VERDADE) ou FALSE (FALSO) deliberadamente e librarás de moitas dores de cabeza.

Nos seguintes artigos do noso tutorial de funcións VPR en Excel, aprenderemos exemplos máis avanzados, como realizar varios cálculos utilizando VPR, extraendo valores de varias columnas e moito máis. Grazas por ler este tutorial e espero verte de novo a próxima semana!

Deixe unha resposta