Usando a función BUSCAR V en Excel: Fuzzy Match

Recentemente dedicamos un artigo a unha das funcións de Excel máis útiles chamadas VPR e mostrou como se pode usar para extraer a información necesaria dunha base de datos nunha cela da folla de traballo. Tamén mencionamos que hai dous casos de uso para a función VPR e só un deles trata sobre consultas de bases de datos. Neste artigo, aprenderás outra forma menos coñecida de usar a función VPR en Excel.

Se aínda non o fixeches, asegúrate de ler o último artigo sobre a función VPR, porque toda a información a continuación supón que xa está familiarizado cos principios descritos no primeiro artigo.

Cando se traballa con bases de datos, funcións VPR pásase un identificador único, que se utiliza para identificar a información que queremos atopar (por exemplo, un código de produto ou un número de identificación do cliente). Este código único debe estar presente na base de datos, en caso contrario VPR informará dun erro. Neste artigo, analizaremos esta forma de usar a función VPRcando o ID non existe na base de datos. Como se a función VPR pasou ao modo aproximado e escolle que datos nos proporciona cando queremos atopar algo. En determinadas circunstancias, isto é exactamente o que se necesita.

• Un exemplo da vida. Nós marcamos a tarefa
• Complicamos a tarefa
• Aplicamos a función BUSCAR V para resolver o problema
• Conclusión

Un exemplo da vida. Nós marcamos a tarefa

Ilustremos este artigo cun exemplo real: o cálculo de comisións en función dunha ampla gama de métricas de vendas. Comezaremos cunha opción moi sinxela, e despois irémola complicando gradualmente ata que a única solución racional ao problema sexa utilizar a función VPR. O escenario inicial para a nosa tarefa ficticia é o seguinte: se un vendedor gaña máis de 30000 dólares en vendas nun ano, a súa comisión é do 30%. En caso contrario, a comisión é só do 20%. Poñémolo en forma de táboa:

O vendedor introduce os seus datos de vendas na cela B1 e a fórmula da cela B2 determina a taxa de comisión correcta que o vendedor pode esperar. Pola súa banda, a taxa resultante utilízase na cela B3 para calcular a comisión total que debe recibir o vendedor (simplemente multiplicando as celas B1 e B2).

A parte máis interesante da táboa está contida na cela B2: esta é a fórmula para determinar a taxa de comisión. Esta fórmula contén unha función de Excel chamada IF (SI). Para aqueles lectores que non estean familiarizados con esta función, explicarei como funciona:

IF(condition, value if true, value if false)

ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)

Condición é un argumento de función que toma o valor de calquera CÓDIGO VERDADEIRO (VERDADEIRO), ou FALSE (FALSO). No exemplo anterior, a expresión B1

É certo que B1 é menor que B5?

Ou podes dicilo doutro xeito:

É certo que o importe total das vendas do ano é inferior ao valor límite?

Se respondemos a esta pregunta SI (VERDADEIRO), entón volve a función valor se é verdade (valor se é VERDADEIRO). No noso caso, este será o valor da cela B6, é dicir, a taxa de comisión cando as vendas totais están por debaixo do limiar. Se respondemos á pregunta NON (FALSO) entón volve valor se é falso (valor se é FALSO). No noso caso, este é o valor da cela B7, é dicir, a taxa de comisión cando as vendas totais están por riba do limiar.

Como podes ver, se tomamos as vendas totais de $ 20000, obtemos unha taxa de comisión do 2% na cela B20. Se introducimos un valor de $40000, a taxa de comisión cambiará nun 30 %:

Así funciona a nosa mesa.

Complicamos a tarefa

Imos facer as cousas un pouco máis difíciles. Establecemos outro limiar: se o vendedor gaña máis de 40000 dólares, a taxa de comisión aumenta ata o 40 %:

Todo parece ser sinxelo e claro, pero a nosa fórmula na cela B2 complícase notablemente. Se observas atentamente a fórmula, verás que o terceiro argumento da función IF (IF) converteuse noutra función de pleno dereito IF (SI). Esta construción chámase anidación de funcións entre si. Excel permite felizmente estas construcións, e incluso funcionan, pero son moito máis difíciles de ler e comprender.

Non afondaremos nos detalles técnicos: por que e como funciona, e non entraremos nos matices de escribir funcións aniñadas. Despois de todo, este é un artigo dedicado á función VPR, non é unha guía completa para Excel.

Sexa como for, a fórmula complícase máis! E se introducimos outra opción para unha comisión do 50 % para aqueles vendedores que gañan máis de 50000 60000 $ en vendas. E se alguén vendeu máis de 60 dólares, pagará unha comisión do XNUMX%?

Agora a fórmula da cela B2, aínda que se escribiu sen erros, volveuse completamente ilexible. Creo que son poucos os que queiran utilizar fórmulas con 4 niveis de aniñación nos seus proxectos. Debe haber un xeito máis sinxelo?!

E hai tal xeito! A función axudaranos VPR.

Aplicamos a función BUSCAR V para resolver o problema

Imos cambiar un pouco o deseño da nosa mesa. Manteremos todos os mesmos campos e datos, pero organizalos dun xeito novo e máis compacto:

Tómese un momento e asegúrese de que a nova táboa Táboa de tarifas inclúe os mesmos datos que a táboa de limiares anterior.

A idea principal é usar a función VPR para determinar o tipo de tarifa desexado segundo a táboa Táboa de tarifas dependendo do volume de vendas. Teña en conta que o vendedor pode vender bens por un importe que non sexa igual a un dos cinco limiares da táboa. Por exemplo, podería vendelo por 34988 dólares, pero non existe esa cantidade. Imos ver como funciona VPR pode facer fronte a tal situación.

Inserir unha función BUSCAR V

Seleccione a cela B2 (onde queremos inserir a nosa fórmula) e busque BUSCA (BUSCARV) na biblioteca de funcións de Excel: Fórmulas (fórmulas) > Biblioteca de funcións (Biblioteca de funcións) > Busca e referencia (Referencias e matrices).

Aparece un cadro de diálogo Argumentos da función (Argumentos de función). Enchemos os valores dos argumentos un por un, comezando por Valor_busca (Valor_busca). Neste exemplo, esta é a cantidade total de vendas da cela B1. Coloque o cursor no campo Valor_busca (Lookup_value) e seleccione a cela B1.

A continuación, cómpre especificar as funcións VPRonde buscar datos. No noso exemplo, esta é unha táboa Táboa de tarifas. Coloque o cursor no campo Matriz_táboa (Táboa) e seleccione toda a táboa Táboa de tarifasagás as cabeceiras.

A continuación, necesitamos especificar de que columna extraer os datos mediante a nosa fórmula. Interésanos a taxa de comisión, que está na segunda columna da táboa. Polo tanto, para o argumento Col_index_num (número_columna) introduza o valor 2.

E finalmente, introducimos o último argumento: Intervalo_lookup (Busca_intervalo).

Importante: é o uso deste argumento o que marca a diferenza entre as dúas formas de aplicar a función VPR. Cando se traballa con bases de datos, o argumento Intervalo_lookup (range_lookup) sempre debe ter un valor FALSE (FALSO) para buscar unha coincidencia exacta. No noso uso da función VPR, debemos deixar este campo en branco ou introducir un valor CÓDIGO VERDADEIRO (VERDADEIRO). É moi importante escoller esta opción correctamente.

Para que quede máis claro, imos presentar CÓDIGO VERDADEIRO (VERDADEIRO) no campo Intervalo_lookup (Busca_intervalo). Aínda que, se deixas o campo en branco, isto non será un erro, xa que CÓDIGO VERDADEIRO é o seu valor predeterminado:

Cubrimos todos os parámetros. Agora prememos OK, e Excel crea unha fórmula para nós cunha función VPR.

Se experimentamos con varios valores diferentes para o importe total das vendas, asegurarémonos de que a fórmula funciona correctamente.

Conclusión

Cando a función VPR traballa con bases de datos, argumento Intervalo_lookup (range_lookup) debe aceptar FALSE (FALSO). E o valor introducido como Valor_busca (Lookup_value) debe existir na base de datos. Noutras palabras, está a buscar unha coincidencia exacta.

No exemplo que analizamos neste artigo, non é necesario obter unha coincidencia exacta. Este é o caso cando a función VPR debe cambiar ao modo aproximado para devolver o resultado desexado.

Por exemplo: Queremos determinar que taxa usar no cálculo da comisión para un vendedor cun volume de vendas de $ 34988. Función VPR devólvenos un valor do 30%, que é absolutamente correcto. Pero por que a fórmula seleccionou a fila que contén exactamente o 30% e non o 20% ou o 40%? Que se entende por busca aproximada? Imos ser claros.

Cando o argumento Intervalo_lookup (interval_lookup) ten un valor CÓDIGO VERDADEIRO (VERDADEIRO) ou omitida, función VPR percorre a primeira columna e selecciona o valor máis grande que non supera o valor de busca.

Punto importante: Para que este esquema funcione, a primeira columna da táboa debe estar ordenada en orde ascendente.