VLOOKUP no Excel, parte 2 Usando o VLOOKUP sem banco de dados
Em um artigo recente, apresentamos a função 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 pelo 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 único devo existem no banco de dados, caso contrário, o VLOOKUP retorna um erro. Neste artigo, examinaremos uma maneira de usar o VLOOKUP onde o identificador não precisa existir no banco de dados. É quase como se o VLOOKUP pudesse adotar uma abordagem “perto o suficiente é bom o suficiente” para retornar 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 números 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:
Para usar essa planilha, o vendedor insere seus números de vendas na célula B1, e a fórmula na célula B2 calcula a taxa de comissão correta que eles têm direito a receber, que é usada na célula B3 para calcular a comissão total que o vendedor deve (que é uma multiplicação simples de B1 e B2).
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:
E SE(condição, valor se verdadeiro, valor se falso)
Onde o condição é uma expressão que avalia tanto verdade ou falso. No exemplo acima, o condição é a expressão B1
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:
Então nossa planilha está funcionando.
Vamos tornar isso mais complexo. Vamos introduzir 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ê olhar de perto 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 nos aprofundar em 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 50% de comissão e, se ganharem mais de US $ 60.000, terão direito a 60% de comissão??
Agora, a fórmula na célula B2, embora correta, tornou-se praticamente ilegível. Ninguém deveria ter que escrever fórmulas onde as funções são aninhadas em quatro níveis de profundidade! Certamente deve haver uma maneira mais simples?
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 procurar 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 $ 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. Neste 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. Nesse caso, queremos a taxa de comissão, que é encontrada na segunda coluna da tabela, portanto, inserimos uma 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, este 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:
Se experimentarmos com um total de vendas diferentes, podemos nos convencer de que a fórmula está funcionando.
Conclusão
Na versão “database” 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:
Quando Pesquisa de alcance está configurado para VERDADE (ou omitido), o VLOOKUP irá procurar na coluna 1 e corresponder o valor mais alto que não é maior que a Lookup_value parâmetro.
Também é importante notar que para este 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.
