Conteúdo
Recentemente dedicamos um artigo a uma das funções mais úteis do Excel chamada VPR e mostrou como ele pode ser usado para extrair as informações necessárias de um banco de dados para uma célula da planilha. Também mencionamos que existem dois casos de uso para a função VPR e apenas um deles lida com consultas de banco de dados. Neste artigo, você aprenderá outra maneira menos conhecida de usar a função VPR no Excel.
Se você ainda não fez isso, não deixe de ler o último artigo sobre a função VPR, pois todas as informações abaixo pressupõem que você já esteja familiarizado com os princípios descritos no primeiro artigo.
Ao trabalhar com bancos de dados, funções VPR é passado um identificador único, que é usado para identificar as informações que queremos encontrar (por exemplo, um código de produto ou um número de identificação do cliente). Este código único deve estar presente no banco de dados, caso contrário VPR reportará um erro. Neste artigo, veremos essa maneira de usar a função VPRquando o id não existe no banco de dados. Como se a função VPR mudou para o modo aproximado e escolhe quais dados nos fornecer quando queremos encontrar algo. Em certas circunstâncias, isso é exatamente o que é necessário.
Um exemplo da vida. Definimos a tarefa
Vamos ilustrar este artigo com um exemplo da vida real – calcular comissões com base em uma ampla gama de métricas de vendas. Começaremos com uma opção muito simples e depois a complicaremos gradualmente até que a única solução racional para o problema seja usar a função VPR. O cenário inicial para nossa tarefa fictícia é o seguinte: se um vendedor fatura mais de $ 30000 em vendas em um ano, sua comissão é de 30%. Caso contrário, a comissão é de apenas 20%. Vamos colocá-lo na forma de uma tabela:
O vendedor insere seus dados de vendas na célula B1 e a fórmula na célula B2 determina a taxa de comissão correta que o vendedor pode esperar. Por sua vez, a taxa resultante é utilizada na célula B3 para calcular a comissão total que o vendedor deve receber (simplesmente multiplicando as células B1 e B2).
A parte mais interessante da tabela está contida na célula B2 – esta é a fórmula para determinar a taxa de comissão. Esta fórmula contém uma função do Excel chamada IF (E SE). Para aqueles leitores que não estão familiarizados com esta função, vou explicar como ela funciona:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Condição é um argumento de função que recebe o valor de CÓDIGO VERDADEIRO (Verdadeiro ou FALSE (FALSO). No exemplo acima, a expressão B1
É verdade que B1 é menor que B5?
Ou você pode dizer diferente:
É verdade que o valor total das vendas do ano é inferior ao valor limite?
Se respondermos a esta pergunta SIM (VERDADEIRO), então a função retorna valor se for verdade (valor se VERDADEIRO). No nosso caso, este será o valor da célula B6, ou seja, taxa de comissão quando o total de vendas estiver abaixo do limite. Se respondermos à pergunta NÃO (FALSE) então retorna valor se for falso (valor se FALSE). No nosso caso, este é o valor da célula B7, ou seja, taxa de comissão quando o total de vendas está acima do limite.
Como você pode ver, se considerarmos as vendas totais de $ 20000, obteremos uma taxa de comissão de 2% na célula B20. Se inserirmos um valor de $ 40000, a taxa de comissão será alterada em 30%:
É assim que nossa tabela funciona.
Nós complicamos a tarefa
Vamos tornar as coisas um pouco mais difíceis. Vamos definir outro limite: se o vendedor ganhar mais de $ 40000, a taxa de comissão aumenta para 40%:
Tudo parece ser simples e claro, mas nossa fórmula na célula B2 se torna visivelmente mais complicada. Se você observar atentamente a fórmula, verá que o terceiro argumento da função IF (IF) se transformou em outra função de pleno direito IF (E SE). Essa construção é chamada de aninhamento de funções umas nas outras. O Excel felizmente permite essas construções, e elas até funcionam, mas são muito mais difíceis de ler e entender.
Não nos aprofundaremos nos detalhes técnicos – por que e como funciona, e não entraremos nas nuances de escrever funções aninhadas. Afinal, este é um artigo dedicado à função VPR, não um guia completo do Excel.
Seja qual for o caso, a fórmula fica mais complicada! E se introduzirmos outra opção para uma taxa de comissão de 50% para os vendedores que faturam mais de $ 50000 em vendas. E se alguém vendeu mais de US $ 60000, pagará 60% de comissão?
Agora a fórmula na célula B2, mesmo que tenha sido escrita sem erros, tornou-se completamente ilegível. Acho que são poucos os que querem usar fórmulas com 4 níveis de aninhamento em seus projetos. Deve haver uma maneira mais fácil?!
E existe tal maneira! A função nos ajudará VPR.
Aplicamos a função PROCV para resolver o problema
Vamos mudar um pouco o design da nossa mesa. Manteremos todos os mesmos campos e dados, mas os organizaremos de uma maneira nova e mais compacta:
Reserve um momento e certifique-se de que a nova tabela Tabela de taxas inclui os mesmos dados que a tabela de limite anterior.
A ideia principal é usar a função VPR para determinar a tarifa desejada de acordo com a tabela Tabela de taxas dependendo do volume de vendas. Observe que o vendedor pode vender mercadorias por um valor que não seja igual a um dos cinco limites da tabela. Por exemplo, ele poderia vender por $ 34988, mas não existe esse valor. Vamos ver como a função VPR pode lidar com tal situação.
Inserindo uma função PROCV
Selecione a célula B2 (onde queremos inserir nossa fórmula) e encontre PROCV (VLOOKUP) na Biblioteca de Funções do Excel: Fórmulas (fórmulas) > Biblioteca de funções (Biblioteca de Funções) > Pesquisa e Referência (Referências e matrizes).
Uma caixa de diálogo aparece Argumentos de função (Argumentos de função). Preenchemos os valores dos argumentos um a um, começando com Valor_procurado (Pesquisa_valor). Neste exemplo, este é o valor total de vendas da célula B1. Coloque o cursor no campo Valor_procurado (Lookup_value) e selecione a célula B1.
Em seguida, você precisa especificar as funções VPRonde procurar dados. No nosso exemplo, esta é uma tabela Tabela de taxas. Coloque o cursor no campo Tabela_array (Tabela) e selecione a tabela inteira Tabela de taxasexceto para cabeçalhos.
Em seguida, precisamos especificar de qual coluna extrair dados usando nossa fórmula. Estamos interessados na taxa de comissão, que está na segunda coluna da tabela. Portanto, para o argumento Col_index_num (Column_number) insira o valor 2.
E, finalmente, introduzimos o último argumento – Pesquisa de alcance (Pesquisa_intervalo).
Importante: é o uso deste argumento que faz a diferença entre as duas formas de aplicar a função VPR. Ao trabalhar com bancos de dados, o argumento Pesquisa de alcance (range_lookup) deve sempre ter um valor FALSE (FALSE) para procurar uma correspondência exata. Em nosso uso da função VPR, devemos deixar este campo em branco ou inserir um valor CÓDIGO VERDADEIRO (VERDADE). É extremamente importante escolher esta opção corretamente.
Para ficar mais claro, vamos apresentar CÓDIGO VERDADEIRO (VERDADEIRO) no campo Pesquisa de alcance (Pesquisa_intervalo). Embora, se você deixar o campo em branco, isso não será um erro, pois CÓDIGO VERDADEIRO é seu valor padrão:
Preenchemos todos os parâmetros. Agora nós pressionamos OK, e o Excel cria uma fórmula para nós com uma função VPR.
Se experimentarmos vários valores diferentes para o valor total das vendas, garantiremos que a fórmula funcione corretamente.
Conclusão
Quando a função VPR trabalha com banco de dados, argumento Pesquisa de alcance (range_lookup) deve aceitar FALSE (FALSO). E o valor inserido como Valor_procurado (Lookup_value) deve existir no banco de dados. Em outras palavras, está procurando uma correspondência exata.
No exemplo que vimos neste artigo, não há necessidade de obter uma correspondência exata. Este é o caso quando a função VPR deve mudar para o modo aproximado para retornar o resultado desejado.
Por exemplo: Queremos determinar qual taxa usar no cálculo de comissão para um vendedor com um volume de vendas de $ 34988. Função VPR nos retorna um valor de 30%, o que é absolutamente correto. Mas por que a fórmula selecionou a linha contendo exatamente 30% e não 20% ou 40%? O que se entende por pesquisa aproximada? Vamos ser claros.
Quando a discussão Pesquisa de alcance (interval_lookup) tem um valor CÓDIGO VERDADEIRO (VERDADEIRO) ou omitido, função VPR itera pela primeira coluna e seleciona o maior valor que não excede o valor de pesquisa.
Ponto importante: Para que esse esquema funcione, a primeira coluna da tabela deve ser classificada em ordem crescente.