Em um artigo recente, apresentamos a função do Excel chamada VLOOKUP e explicou como ele poderia ser usado para recuperar informações de um banco de dados em uma célula em uma planilha local. Nesse artigo, mencionamos que havia dois usos para o VLOOKUP e apenas um deles lidava com consultas a bancos de dados. Neste artigo, o segundo e último da série VLOOKUP, examinamos esse outro uso menos conhecido para a função VLOOKUP.
Se você ainda não o fez, leia o primeiro artigo do VLOOKUP - este artigo assumirá que muitos dos conceitos explicados naquele artigo já são conhecidos do leitor.
Ao trabalhar com bancos de dados, o VLOOKUP recebe um “identificador único” que serve para identificar qual registro de dados desejamos encontrar no banco de dados (por exemplo, um código de produto ou um ID de cliente). Este identificador exclusivo devo existem no banco de dados, caso contrário, o VLOOKUP retorna um erro. Neste artigo, examinaremos uma maneira de usar o VLOOKUP no qual o identificador não precisa existir no banco de dados. É quase como se o VLOOKUP pudesse adotar uma abordagem "próximo o suficiente" para devolver os dados que estamos procurando. Em certas circunstâncias, isso é exatamente o que precisamos.
Ilustraremos este artigo com um exemplo do mundo real - o de calcular as comissões geradas em um conjunto de valores de vendas. Vamos começar com um cenário muito simples, e então progressivamente torná-lo mais complexo, até que a única solução racional para o problema seja usar o VLOOKUP. O cenário inicial em nossa empresa fictícia funciona assim: se um vendedor gera mais de US $ 30.000 em vendas em um determinado ano, a comissão que recebe nessas vendas é de 30%. Caso contrário, sua comissão é de apenas 20%. Até agora, esta é uma planilha bem simples:
A célula B2 contém a única parte interessante desta planilha - a fórmula para decidir qual taxa de comissão usar: a abaixo o limite de US $ 30.000, ou o limite acima o limiar. Esta fórmula faz uso da função do Excel chamada E SE. Para aqueles leitores que não estão familiarizados com o IF, funciona assim:
IF(condition,value if true,value if false)
Onde o condição é uma expressão que avalia tanto verdade ou falso. No exemplo acima, o condição é a expressão B1, que pode ser lido como "B1 é menor que B5?", ou, em outras palavras, "O total de vendas é menor que o limite". Se a resposta a esta pergunta for "sim" (verdadeiro), então usamos o valor se verdadeiro parâmetro da função, nomeadamente B6 neste caso - a taxa de comissão se o total de vendas foi abaixo o limiar. Se a resposta à pergunta for "não" (falso), então usamos o valor se falso parâmetro da função, nomeadamente B7 neste caso - a taxa de comissão se o total de vendas foi acima o limiar.
Como você pode ver, usar um total de vendas de US $ 20.000 nos dá uma taxa de comissão de 20% na célula B2. Se inserirmos um valor de US $ 40.000, obteremos uma taxa de comissão diferente:
Vamos torná-lo mais complexo. Vamos apresentar um segundo limite: se o vendedor ganhar mais de US $ 40.000, a taxa de comissão aumentará para 40%:
Fácil o suficiente para entender no mundo real, mas na célula B2 nossa fórmula está ficando mais complexa. Se você observar atentamente a fórmula, verá que o terceiro parâmetro da função IF original (o valor se falso) é agora toda uma função IF por si só. Isso é chamado de função aninhada (uma função dentro de uma função). É perfeitamente válido no Excel (funciona mesmo!), Mas é mais difícil de ler e entender.
Não vamos entrar em detalhes sobre como e por que isso funciona, nem vamos examinar as nuances das funções aninhadas. Este é um tutorial sobre o VLOOKUP, não sobre o Excel em geral.
De qualquer forma, fica pior! E quando decidimos que, se ganharem mais de US $ 50.000, terão direito a uma comissão de 50% e, se ganharem mais de US $ 60.000, terão direito a uma comissão de 60%?
Certamente existe. VLOOKUP para o resgate!
Vamos redesenhar a planilha um pouco. Manteremos todos os mesmos números, mas organizaremos de uma maneira nova, mais tabular caminho:
Tome um momento e verifique por si mesmo que o novo Tabela de taxas funciona exatamente da mesma forma que a série de limiares acima.
Conceitualmente, o que estamos prestes a fazer é usar o VLOOKUP para pesquisar o total de vendas do vendedor (de B1) na tabela de taxas e retornar a taxa de comissão correspondente. Observe que o vendedor pode de fato ter criado vendas que são não um dos cinco valores na tabela de taxas ($ 0, $ 30.000, $ 40.000, $ 50.000 ou $ 60.000). Eles podem ter criado vendas de US $ 34.988. É importante notar que US $ 34.988 não aparecem na tabela de taxas. Vamos ver se o VLOOKUP pode resolver nosso problema de qualquer maneira …
Selecionamos a célula B2 (o local em que queremos colocar nossa fórmula) e, em seguida, inserimos a função VLOOKUP Fórmulas aba:
o Argumentos da Função caixa para VLOOKUP aparece. Nós preenchemos os argumentos (parâmetros) um por um, começando com o Lookup_value, que é, neste caso, o total de vendas da célula B1. Colocamos o cursor no Lookup_value campo e, em seguida, clique uma vez na célula B1:
Em seguida, precisamos especificar para VLOOKUP em qual tabela procurar esses dados. Nesse exemplo, é a tabela de taxas, é claro. Colocamos o cursor no Table_array campo e, em seguida, realce toda a tabela de taxas - excluindo as rubricas:
Em seguida, devemos especificar qual coluna na tabela contém as informações que queremos que nossa fórmula retorne para nós. Neste caso, queremos a taxa de comissão, que é encontrada na segunda coluna da tabela, por isso, entramos 2 no Col_index_num campo:
Finalmente, inserimos um valor no Pesquisa de alcance campo.
Importante: É o uso desse campo que diferencia as duas formas de usar o VLOOKUP. Para usar o VLOOKUP com um banco de dados, esse parâmetro final, Pesquisa de alcance, deve sempre ser definido para FALSO, mas com este outro uso do VLOOKUP, devemos deixar em branco ou inserir um valor de VERDADE. Ao usar o VLOOKUP, é vital que você faça a escolha correta para este parâmetro final.
Para ser explícito, vamos inserir um valor de verdade no Pesquisa de alcance campo. Também seria bom deixar em branco, pois esse é o valor padrão:
Nós completamos todos os parâmetros. Agora clicamos no Está bem botão, e Excel constrói nossa fórmula VLOOKUP para nós:
Conclusão
Na versão “base de dados” do VLOOKUP, onde o Pesquisa de alcance parâmetro é FALSO, o valor passado no primeiro parâmetro (Lookup_value) devo estar presente no banco de dados. Em outras palavras, estamos procurando uma correspondência exata.
Mas neste outro uso do VLOOKUP, não estamos necessariamente procurando uma correspondência exata. Neste caso, "perto o suficiente é bom o suficiente". Mas o que queremos dizer com “perto o suficiente”? Vamos usar um exemplo: Ao pesquisar por uma taxa de comissão em um total de vendas de US $ 34.988, nossa fórmula VLOOKUP nos retornará um valor de 30%, que é a resposta correta. Por que escolheu a linha na tabela contendo 30%? O que, de fato, significa “próximo o suficiente” neste caso? Vamos ser precisos:
When Range_lookup is set to TRUE (or omitted), VLOOKUP will look in column 1 and match the highest value that is not greater than the Lookup_value parameter.
Também é importante observar que, para esse sistema funcionar, a tabela deve ser classificada em ordem crescente na coluna 1!
Se você gostaria de praticar com o VLOOKUP, o arquivo de exemplo ilustrado neste artigo pode ser baixado aqui.
