1Aug
Em um artigo recente, apresentamos a função Excel chamada VLOOKUP e explicamos como 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 bancos de dados de consulta. 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 da VLOOKUP - este artigo assumirá que muitos dos conceitos explicados nesse artigo já são conhecidos pelo leitor.
Ao trabalhar com bancos de dados, o VLOOKUP passa a ser um "identificador exclusivo" que serve para identificar qual registro de dados desejamos encontrar no banco de dados( por exemplo, um código de produto ou identificação de cliente).Este identificador exclusivo deve ser no banco de dados, caso contrário o VLOOKUP nos retornará 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 VLOOKUP pudesse adotar uma abordagem "próxima o suficiente é boa o suficiente" para retornar os dados que estamos procurando. Em determinadas circunstâncias, isto é
exatamente o que precisamos.Vamos ilustrar 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, em seguida, 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 crie mais de US $ 30.000 em vendas em um determinado ano, a comissão que ganham nessas vendas é de 30%.Caso contrário, sua comissão é de apenas 20%.Até agora, esta é uma folha de cálculo bastante simples:
Para usar esta 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 de receber, que é usado na célula B3 para calculara comissão total de que o vendedor é devido( o que é uma simples multiplicação 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 acima de o limiar. Esta fórmula faz uso da função Excel chamada IF .Para os leitores que não estão familiarizados com IF, isso funciona assim:
IF( condição , valor se verdadeiro, valor se false )
Onde a condição é uma expressão que avalia true ou false .No exemplo acima, a condição é a expressão B1 & lt; B5 , que pode ser lida como "é B1 inferior a B5?", Ou, de outra forma, "são as vendas totais inferiores ao limite".Se a resposta a esta pergunta for "sim"( verdadeiro), usamos o valor se o parâmetro verdadeiro da função, ou seja B6 neste caso - a taxa de comissão se o total de vendas for abaixo o limiar. Se a resposta à pergunta for "não"( falso), usamos o valor se o parâmetro falso da função, ou seja B7 neste caso - a taxa de comissão se o total de vendas for acima de o limiar.
Como você pode ver, o uso de um total de vendas de US $ 20.000 nos dá uma taxa de comissão de 20% na célula B2.Se inserimos um valor de US $ 40.000, obtemos uma taxa de comissão diferente:
, então nossa planilha está funcionando.
Vamos torná-lo mais complexo. Vamos apresentar um segundo limite: se o vendedor ganhar mais de US $ 40.000, sua taxa de comissão aumenta 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 atentamente para a fórmula, você verá que o terceiro parâmetro da função IF original( o valor se false ) é agora uma função IF inteira por direito próprio. Isso é chamado de função aninhada ( uma função dentro de uma função).É perfeitamente válido no Excel( até funciona!), Mas é mais difícil de ler e entender.
Nós não iremos entrar nas porcas de como e por que isso funciona, nem examinaremos as nuances das funções aninhadas. Este é um tutorial sobre o VLOOKUP, não no Excel em geral.
De qualquer forma, fica pior! E quando decidirmos se eles ganham mais de US $ 50.000, eles têm direito a comissão de 50%, e se eles ganham mais de US $ 60.000, eles têm direito a uma comissão de 60%?
Agora, a fórmula na célula B2, enquanto 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 um pouco a planilha. Vamos manter todos os mesmos números, mas organizá-lo de uma nova maneira, mais tabular caminho:
Tire um momento e verifique por si mesmo que a nova tabela de taxas funciona exatamente como a série de limiares acima.
Conceitualmente, o que estamos prestes a fazer é usar VLOOKUP para procurar o total de vendas do vendedor( de B1) na tabela de taxas e retornar a nós a taxa de comissão correspondente. Note-se que o vendedor pode ter criado vendas que são e não um dos cinco valores na tabela de taxas( $ 0, $ 30,000, $ 40,000, $ 50,000 ou US $ 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( a localização que queremos colocar a nossa fórmula) e, em seguida, insira a função VLOOKUP da Fórmulas guia:
A Argumentos da função caixa para VLOOKUP aparece. Nós preenchemos os argumentos( parâmetros) um a um, começando com o Lookup_value , que é, neste caso, o total de vendas da célula B1.Colocamos o cursor no campo Lookup_value e, em seguida, clique uma vez na célula B1:
Em seguida, precisamos especificar para o VLOOKUP em que tabela pesquisar esses dados. Neste exemplo, é a tabela de taxas, é claro. Colocamos o cursor no campo Table_array e, em seguida, destaque a tabela de taxas inteira - , excluindo as cabeçalhos :
Em seguida, devemos especificar qual coluna na tabela contém as informações que queremos que nossa fórmula devolva para nós. Neste caso, queremos a taxa de comissão, que é encontrada na segunda coluna na tabela, então, portanto, insira um 2 no campo Col_index_num :
Finalmente, inserimos um valor no campo Range_lookup .
Importante: é o uso deste campo que diferencia as duas maneiras de usar o VLOOKUP.Para usar o VLOOKUP com um banco de dados, este parâmetro final, Range_lookup , deve sempre ser definido como FALSE , mas com este outro uso do VLOOKUP, devemos deixá-lo em branco ou inserir um valor de TRUE .Ao usar o VLOOKUP, é vital que você faça a escolha correta para este parâmetro final.
Para ser explícito, entraremos um valor de verdadeiro no campo Range_lookup .Também seria bom deixá-lo em branco, pois este é o valor padrão:
Completamos todos os parâmetros. Agora, clique no botão OK e o Excel cria nossa fórmula VLOOKUP para nós:
Se experimentarmos algumas quantidades totais de vendas diferentes, podemos nos satisfazer que a fórmula está funcionando.
Conclusão
Na versão "base de dados" do VLOOKUP, onde Range_lookup parâmetro é FALSE , o valor passado no primeiro parâmetro( Lookup_value ) deve estar presente no banco de dados. Em outras palavras, estamos procurando uma correspondência exata.
Mas neste outro uso de VLOOKUP, não estamos necessariamente procurando uma correspondência exata. Neste caso, "o suficiente é suficiente".Mas o que queremos dizer com "o suficiente"?Vamos usar um exemplo: ao procurar uma taxa de comissão em um total de vendas de US $ 34.988, nossa fórmula VLOOKUP nos devolverá um valor de 30%, qual é a resposta correta. Por que ele escolheu a linha na tabela contendo 30%?O que, de fato, significa "quase o suficiente" neste caso? Vamos ser precisos:
Quando Range_lookup está configurado para TRUE ( ou omitido), o VLOOKUP procurará na coluna 1 e combinará o valor mais alto que não é maior que o parâmetro Lookup_value .
Também é importante notar que, para que este sistema funcione, , a tabela deve ser ordenada em ordem ascendente na coluna 1 !
Se você quiser praticar com o VLOOKUP, o arquivo de exemplo ilustrado neste artigo pode ser baixado a partir daqui.
