01 de 03
Atopar coincidencias aproximadas aos datos co VLOOKUP de Excel
Como funciona a función VLOOKUP
A función VLOOKUP de Excel, que representa a busca vertical , pode usarse para buscar información específica situada nunha táboa de datos ou base de datos.
VLOOKUP normalmente retorna un só campo de datos como a súa saída. Como funciona isto:
- Proporciona un nome ou valor de busca que indique a VLOOKUP en que liña ou rexistro da táboa de datos para buscar os datos desexados
- Vostede subministra o número da columna - coñecido como col_index_num - dos datos que busca
- A función busca o valor de busca na primeira columna da táboa de datos
- VLOOKUP logo localiza e devolve a información que busca desde outro campo do mesmo rexistro usando o número de columna indicado
Clasificación da información primeiro
Aínda que non sempre sexa necesario, normalmente é mellor ordenar o rango de datos que VLOOKUP busca en orde ascendente usando a primeira columna do rango para a clave de clasificación.
Se os datos non están ordenados, VLOOKUP pode devolver un resultado incorrecto.
Sintaxe e argumentos da función VLOOKUP
A sintaxe dunha función refírese ao deseño da función e inclúe o nome, parénteses e argumentos da función.
A sintaxe para a función VLOOKUP é:
= VLOOKUP (valor de busca, table_array, col_index_num, range_lookup)
_valor de busca - (necesario) o valor a buscar - como a cantidade vendida na imaxe de arriba
table_array - (requerido) esta é a táboa de datos que VLOOKUP busca para atopar a información que está despois.
- O marcador de táboa debe conter polo menos dúas columnas de datos
- A primeira columna normalmente contén o_valor de busca
col_index_num - (requerido) o número da columna do valor que desexa atopar.
- A numeración comeza coa columna search_key como columna 1
- Se col_index_num está configurado en un número maior que o número de columnas seleccionadas no argumento table_array a #REF! O erro é devuelto pola función
range_lookup - (opcional) indica se o intervalo está ordenado ou non.
- Os datos da primeira columna úsanse como a tecla de ordenación
- Un valor booleano: TRUE ou FALSE son os únicos valores aceptables
- Se se omite, o valor establécese como VERDADE por defecto
- Se se establece como TRUE ou omítese e a primeira columna do intervalo non está ordenada en orde crecente, pode producirse un resultado incorrecto
- Se se establece como VERDADEIRO ou omitido e non se atopa unha coincidencia exacta para o _valor de busca, emprégase a seguinte coincidencia máis pequena en tamaño ou valor como search_key
- Se se define como FALSE, VLOOKUP só acepta unha coincidencia exacta para o _valor de busca . Se hai varios valores coincidentes, devólvese o primeiro valor correspondente
- Se se define como FALSE e non se atopa ningún valor correspondente para o search_key , devólvese un erro de # N / A pola función
Exemplo: Atopar a tarifa de desconto para a cantidade adquirida
O exemplo na imaxe anterior usa a función VLOOKUP para atopar a taxa de desconto que varía dependendo da cantidade de elementos comprados.
O exemplo mostra que o desconto para a compra de 19 elementos é do 2%. Isto ocorre porque a columna Cantidade contén intervalos de valores. Como resultado, VLOOKUP non pode atopar unha coincidencia exacta. No seu canto, hai que atopar unha coincidencia aproximada para devolver a taxa de desconto correcta.
Para atopar coincidencias aproximadas:
- clasifique os datos na táboa_arraio en orde crecente;
- Establece o argumento range_lookup en VERDADEIRO
No exemplo, utilízase a seguinte fórmula que contén a función VLOOKUP para atopar o desconto para as cantidades de mercadorías adquiridas.
= VLOOKUP (C2, $ C $ 5: $ D $ 8,2, VERDADEIRO)
Aínda que esta fórmula pode escribirse nunha cela de folla de cálculo, outra opción, tal como se usa cos pasos que se enumeran a continuación, é usar a caixa de diálogo da función para introducir os seus argumentos.
- Usar o cadro de diálogo moitas veces facilita a entrada correctamente dos argumentos dunha función.
Apertura do cuadro de diálogo VLOOKUP
Os pasos utilizados para ingresar a función VLOOKUP que se amosa na imaxe de arriba na cela B2 son:
- Faga clic na cela B2 para facela a cela activa : a localización onde se amosan os resultados da función VLOOKUP
- Fai clic na pestana Fórmulas .
- Escolla Busca e referencia da cinta para abrir a lista despregábel da función
- Faga clic en VLOOKUP na lista para abrir o cadro de diálogo da función
02 de 03
Introducir os argumentos da función VLOOKUP de Excel
Indicando referencias de cela
Os argumentos para a función VLOOKUP introdúcense en liñas separadas da caixa de diálogo como se mostra na imaxe de arriba.
As referencias de cela que se usan como argumentos poden escribirse na liña correcta ou, como se fai nos seguintes pasos, sinalando, o que implica destacar o intervalo desexado de celas co punteiro do rato, pódese empregar para inserir no cadro de diálogo .
As vantaxes de usar o apuntamento inclúen:
- É máis rápido que escribir;
- Menos erros son feitos introducindo as referencias de celas correctas.
Usar referencias celulares relativas e absolutas con argumentos
Non é raro empregar varias copias de VLOOKUP para devolver información diferente da mesma táboa de datos. Para facer máis doado facelo, moitas veces VLOOKUP pode ser copiado dunha cela a outra. Cando se copian as funcións a outras celas, hai que ter coidado de que as referencias de celas resultantes sexan correctas dada a nova localización da función.
Na imaxe anterior, os signos de dólar ( $ ) rodean as referencias de cela para o argumento table_array que indican que son referencias absolutas de cela , o que significa que non cambiarán se a función é copiada a outra cela. Isto é desexable porque varias copias de VLOOKUP farían referencia á mesma táboa de datos que a fonte de información.
A referencia de cela empregada para o valor de busca_, por outra banda , non está rodeada por signos de dólar, o que o converte nunha referencia relativa de cela. As referencias de celas relativas cambian cando se copian para reflectir a súa nova localización en relación coa posición dos datos aos que se refiren.
Introducir os argumentos de función
- Faga clic na liña de busca _valor no cuadro de diálogo VLOOKUP
- Faga clic na cela C2 na folla de traballo para ingresar esta referencia de cela como o argumento search_key
- Fai clic na liña Table_array do cadro de diálogo
- Destaque as celas C5 a D8 na folla de cálculo para ingresar neste intervalo como o argumento Table_array : os títulos da táboa non están incluídos.
- Prema a tecla F4 no teclado para cambiar o intervalo a referencias de celas absolutas
- Fai clic na liña Col_index_num do cadro de diálogo
- Escriba un 2 nesta liña como o argumento Col_index_num , xa que as taxas de desconto están situadas na columna 2 do argumento Table_array
- Fai clic na liña Range_lookup do cadro de diálogo
- Escriba a palabra verdadeira como argumento Range_lookup
- Prema a tecla Enter no teclado para pechar a caixa de diálogo e volver á folla de cálculo
- A resposta 2% (a taxa de desconto para a cantidade adquirida) debería aparecer na cela D2 da folla de traballo
- Cando fai clic na cela D2, a función completa = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, VERDADEIRA) aparece na barra de fórmulas que se atopa sobre a folla de cálculo
Por que VLOOKUP devolveu o 2% como resultado
- No exemplo, a columna Cantidade non contén unha coincidencia exacta para o valor search_key de 19.
- Xa que o argumento is_sorted está definido como VERDADEIRO, VLOOKUP atopará unha coincidencia aproximada co valor search_key .
- O valor máis próximo en tamaño aínda máis pequeno que o valor search_key de 19 é 11.
- VLOOKUP, polo tanto, busca o porcentaxe de desconto na liña que contén 11, e, como resultado, devolve unha taxa de desconto do 2%.
03 de 03
Excel VLOOKUP Non funciona: # N / A e #REF Erros
VLOOKUP Mensaxes de erro
As seguintes mensaxes de erro están asociadas a VLOOKUP.
A # N / A ("valor non dispoñible") aparece un erro Se:
- O _valor de busca non se atopa na primeira columna do argumento de intervalo
- O argumento Table_array é incorrecto. Por exemplo, o argumento pode incluír columnas baleiras no lado esquerdo do intervalo
- O argumento Range_lookup está configurado como FALSE e non se pode atopar unha correspondencia exacta para o argumento search_key na primeira columna do intervalo
- O argumento Range_lookup está configurado como VERDADEIRO e todos os valores da primeira columna do rango son maiores que o search_key
Un #REF! ("referencia fóra do alcance") aparece un erro Se:
- O argumento Col_index_num é maior que o número de columnas no Table_array